EDIT: Please see below for working examples. I'll keep updating this page unless someone decides it should be moved out of "Ask for Help" Edit: July 10, 2018: See the post below on Templating for loading from, modifying, and saving to .XLSX.
Hello, after trying the code from tmplinshi's question answered by qwerty12, I have been trying to adapt that code to EPPLus.dll, for working with Excel .xlsx files without needing Excel installed. URL: EPPlus github repository.
From that page:
Create advanced Excel spreadsheets using .NET, without the need of interop.
EPPlus is a .NET library that reads and writes Excel files using the Office Open XML format (xlsx). EPPlus has no dependencies other than .NET.
So far all my efforts with running C# code samples via CLR have failed, almost always with "unable to load EPPLus or one of its dependencies." Attempts with Clr_LoadLibrary("EPPlus.dll") throw no errors, but I haven't been able to create a workbook or a worksheet. Many things are now working well. To try to delve into the inner workings of what was going, I modified qwerty12's code like this: The message box yielded this: Error GIF removed 7/10/18, no longer relevant. That was helpful, and I was encouraged. However, when I tried something as simple as this:
Non-working code removed 7/10/18
there are no errors, but the msgbox shows no cname, name clsid or VT.
EPPlus is very useable from PowerShell and C#, however. I can't for the life of me figure out why it is so difficult for me in AHK. I am very much hoping someone can point me in the right direction.
Regards,
burque505
Latest post: July 10, 2018
Last edited by burque505 on 10 Jul 2018, 11:58, edited 4 times in total.
Encouraged by tmplinshi's kind assistance above, I'm delving into this further. I will post small scripts here that illustrate different things that I can get to work with EPPlus.
There is a lot of trial and error involved. So far, using it in AHK don't quite match the docs.
Script 1 (selects a single cell, enters text from an input box, bolds it, autofits the columns of the worksheet, and saves the file).
; Thanks to tmplinshi for this!
; Eppplus test 1
#NoEnv
SetWorkingDir %A_ScriptDir%
InputBox, cellCts, Cell Contents, Enter Cell Contents:, ,400, 100
asm := Clr_LoadLibrary(".\EPPlus.dll")
; Relative and fully qualified paths work.
; You can omit ".\" if EPPLus.dll is in the script directory.
pck := Clr_CreateObject(asm, "OfficeOpenXml.ExcelPackage")
Worksheet := pck.Workbook.Worksheets.Add("MySheet")
; The line below doesn't seem to correspond to the docs, but works
; (Trial and error purely)
; The docs seem to want "Worksheet.Cells["A1"]", which bombs
; Thanks again to tmplinshi for the "LoadFromText" method,
; which I would have never found.
Worksheet.Cells.("A1").LoadFromText(cellCts)
; Style.Font.Bold follows the docs, but the cell still has to be set
; this way.
Worksheet.Cells.("A1").Style.Font.Bold := true
;***************************************;
;The following line sets the entire worksheet to autofit columns
;I don't know how to set specific range yet.
;***************************************;
Worksheet.Cells.(Worksheet.Dimension.Address).AutoFitColumns()
lib := Clr_LoadLibrary("mscorlib")
outfile := Clr_CreateObject(lib, "System.IO.FileInfo", "epp1.xlsx")
pck.SaveAs(outfile)
ExitApp
EDIT: Another script adding setting row height, formulas, text wrap, merge.
I wasn't able to get borders around cells with code from any EPPlus examples, but some straight Excel code worked, with tweaking.
Using enumerations from here I assigned consecutive numbers, i.e. pure trial and error.
1.2 seconds to create, 3kb file size. Once you open it with Excel and save it, the file size balloons to 9kb.
#NoEnv
SetWorkingDir %A_ScriptDir%
;Working on formulas, seems to work.
;(D2 = the sum of A2 through C2)
;Includes font size, style, colors, merge,
;autofit columns.
myArray := Array(1, 46, 89.5, 12)
asm := Clr_LoadLibrary(".\EPPlus.dll")
; June 25, 2018, NamedStyle partially working
; No alignment yet.
pck := Clr_CreateObject(asm, "OfficeOpenXml.ExcelPackage")
;fmt := Clr_CreateObject(asm, "OfficeOpenXml.ExcelTextFormat")
; LEAVE fmt, IT WILL WORK EVENTUALLY
;sty := Clr_CreateObject(asm, "OfficeOpenXml.Style.XmlAccess")
;showinfo(sty, "sty")
newStyle := pck.Workbook.Styles.CreateNamedStyle("purpleStyle")
;newStyle.Style.HorizontalAlignment := ExcelHorizontalAlignment.Center
newStyle.Style.Font.Size := 18
newStyle.Style.Font.Color.SetColor(0xFF00E6)
now := A_Now
FormatTime, day, now, MMMM dd, yyyy
FormatTime, time, now, hh:mm:ss
Worksheet := pck.Workbook.Worksheets.Add("Sheet1")
Worksheet.Cells.("A1").LoadFromText("El mero mero mero mero mero mero mero!!!!!")
Worksheet.Cells.(Worksheet.Dimension.Address).AutoFitColumns()
Worksheet.Cells.("A1").Style.Font.Name := "Impact"
Worksheet.Cells.("A1").Style.Font.Bold := true
Worksheet.Cells.("A1").Style.Font.Size := 16
Worksheet.Cells.("A1").Style.Font.Color.SetColor(0x0000FF)
Worksheet.Cells.("A1").Style.WrapText := true
Worksheet.Cells.("A2:C2").LoadFromText("1,2,333")
Worksheet.Cells.("E1").LoadFromText("Win a Prize if you can Center Me")
Worksheet.Cells.("E1").StyleName := "purpleStyle"
Worksheet.Cells.("E1").Style.Border.BorderAround(4)
Worksheet.Cells.("E2").Value := day . " -- " . time
Worksheet.Cells.("E2").Style.Font.Size := 18
Worksheet.Cells.("E2").Style.Font.Bold := true
Worksheet.Cells.("D2").Formula := "Sum(A2:C2)"
Worksheet.Cells.("D2").Style.Font.Size := 18
Worksheet.Cells.("D2").Style.Font.Bold := true
Worksheet.Cells.("D2").Style.Font.Underline := true
Worksheet.Cells.("D2").Style.Border.BorderAround(8)
;https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xllinestyle-enumeration-excel
; It doesn't seem to me like any of these should work but they do.
; I just treated them like an enumeration, like it says :)
Worksheet.Cells.("F1").Value := "Border type 1"
Worksheet.Cells.("F1").Style.Border.BorderAround(1)
Worksheet.Cells.("F2").Value := "Border type 2"
Worksheet.Cells.("F2").Style.Border.BorderAround(2)
Worksheet.Cells.("F3").Value := "Border type 3"
Worksheet.Cells.("F3").Style.Border.BorderAround(3)
Worksheet.Cells.("F4").Value := "Border type 4"
Worksheet.Cells.("F4").Style.Border.BorderAround(4)
Worksheet.Cells.("F5").Value := "Border type 5"
Worksheet.Cells.("F5").Style.Border.BorderAround(5)
Worksheet.Cells.("F6").Value := "Border type 6"
Worksheet.Cells.("F6").Style.Border.BorderAround(6)
Worksheet.Cells.("F7").Value := "Border type 7"
Worksheet.Cells.("F7").Style.Border.BorderAround(7)
Worksheet.Cells.("F8").Value := "Border type 8"
Worksheet.Cells.("F8").Style.Border.BorderAround(8)
Worksheet.Row(3).Height := 50
Worksheet.Row(4).Height := 50
Rng := Worksheet.Cells.("A3:C4")
Rng.Style.WrapText := true
Rng.Value := "This is a merged range, won't fit on one line."
Rng.Style.Font.Size := 20
Rng.Style.Font.Bold := true
Rng.Style.Font.Italic := true
Rng.Style.Font.Color.SetColor(0x00FF00)
Rng.Merge := true
Worksheet.Cells.(Worksheet.Dimension.Address).AutoFitColumns()
pck.Workbook.Styles.UpdateXml() ; This will remove any styles that are not used.
lib := Clr_LoadLibrary("mscorlib")
outfile := Clr_CreateObject(lib, "System.IO.FileInfo", "20180625-5.xlsx")
pck.SaveAs(outfile)
ExitApp
ShowInfo(object, objname) {
vartype := ComObjType(object)
name := ComObjType(object, "Name")
clsid := ComObjType(object, "CLSID")
cname := ComObjtype(object, "Class")
msgbox %objname%'s class is %cname%`r`nName is %name%`r`nCLSID is %clsid%`r`nVT is %vartype%
}
;~ shape := Worksheet.Drawings.AddShape("myShape", eShapeStyle.Rect);
;~ shape.SetPosition(6, 5, 12, 5) ;Position Row, RowOffsetPixels, Column, ColumnOffsetPixels
;~ shape.SetSize(400, 200); ;Size in pixels
;~ shape.Text = "This is a rectangular shape."
Thanks, tmplinshi! That's slick. I just saw transfer.sh the other day in "Other Utilities & Resources", that's great.
I have a request, too, if you have time. Without this code that you shared:
I'd still be stuck.
The last day or so I've been trying to load an existing .xlsx file. The code I'm seeing for doing that all seems to be some variation of
using (ExcelPackage p = new ExcelPackage())
{
using (FileStream stream = new FileStream("92b69c48-dda7-4544-bdcc-c6925a5f1bec.xlsx", FileMode.Open))
{
p.Load(stream);
So I'm trying to do FileStream/FileMode.Open in AHK, but my syntax so far is all wrong.
EDIT: I guess I should show that. What I've tried is this:
/// <summary>
/// Create a new instance of the ExcelPackage class based on a stream
/// </summary>
/// <param name="newStream">The stream object can be empty or contain a package. The stream must be Read/Write</param>
public ExcelPackage(Stream newStream)
Success, at least partially. I loaded test.xlsx, added a sheet, added some cells to it.
I'll keep at it. I'll try to save it as another file. Thanks again!
EDIT: tmplinshi to the rescue once again
Can now load a file and save under another name editing/adding info. You need a file "test.xlsx" in the script folder. I've included it, because it has two sheets.
If you run it you should get a file "SavedAfterLoad.xlsx" in the same folder.
#NoEnv
SetWorkingDir %A_ScriptDir%
EPPlus := Clr_LoadLibrary("EPPlus.dll")
pck := Clr_CreateObject(EPPlus, "OfficeOpenXml.ExcelPackage", FileInfo("test.xlsx"))
;outfile := Clr_CreateObject(lib, "System.IO.FileInfo", "SavedAfterLoad.xlsx")
ws1 := pck.Workbook.Worksheets.(1) ;Sheet one
ws2 := pck.Workbook.Worksheets.(2) ;Sheet two (I have no clue why I called it Crimea)
ws1.Cells.("A1").Value := 9999
ws2.Cells.("A2").LoadFromText("How now brown cow ...")
ws2.Cells.("A5").LoadFromText("The fick brown quox lumped over the dazy jog.")
ws2.Cells.(Worksheet.Dimension.Address).AutoFitColumns()
;pck.Save()
pck.SaveAs(FileInfo("SavedAfterLoad.xlsx"))
ExitApp
FileInfo(filename)
{
static lib := Clr_LoadLibrary("mscorlib")
return Clr_CreateObject(lib, "System.IO.FileInfo", filename)
}
burque505 wrote:Some success with border colors and named style:
I do not use EPPlus but your sharing is useful for excel COM,
I like to share my little function for excel borders and gonna make one for cell formatting too.
Thank you, Xeo786, that will be useful for me and I'm sure for others.
I simply ran out of gas when listing formats, and I was just adding them one-by-one, which is just nuts considering I'm using AHK.
Here's a GIF of a few more fills. I'm just going to write a script with a couple of loops and see how many formats there actually are that EPPlus (and presumably Excel) supports. I'll post that script too when I finish it, today's pretty hectic.
EDIT: EPPlus chokes if I try to add a Fill with number 19, so I guess 18 is what I have to work with.
Regards,
burque505
Preliminary shapes results with EPPlus and AHK.
There appear to be 186 different shapes available via EPPlus. There is lots more trial and error coming, as the syntax does NOT correspond very well with the EPPlus docs, especially regarding size and position.
Here's the code I used to extract them for inspection. When I get more time I will post the enumeration reference, which will be long.
EDIT: "shapeenums.txt" is attached. There is an offset, so keep that in mind (starts at 49, ends at 235). I haven't checked to see if there is one-to-one correspondence.
;#NoEnv
SetWorkingDir %A_ScriptDir%
;*************************
; This script illustrates
; the use of "From.Row", etc
; to place the chart.
; Next time, we'll calculate
; the place to put it
; using an EPP function.
;*************************
lib := Clr_LoadLibrary("mscorlib")
outfile := Clr_CreateObject(lib, "System.IO.FileInfo", "Tame Monkeys.xlsx")
asm := Clr_LoadLibrary(".\EPPlus.dll")
pck := Clr_CreateObject(asm, "OfficeOpenXml.ExcelPackage")
;create a worksheet
ws := pck.Workbook.Worksheets.Add("Monkeys")
ws.View.ShowGridLines := false ;this works, try it.
;add some data
ws.Cells.("A1").Value := "# Monkeys"
ws.Cells.("A2").Value := 10
ws.Cells.("A3").Value := 30
ws.Cells.("A4").Value := 28
ws.Cells.("B1").Value := "Tame?"
ws.Cells.("B2").Value := "Partly"
ws.Cells.("B3").Value := "Completely"
ws.Cells.("B4").Value := "Utterly intransigent"
;autofit a range of cells, takes experimenting
ws.Cells.("A1:B4").AutoFitColumns()
r1 := ws.Cells.("A2:A4")
r2 := ws.Cells.("B2:B4")
;series := chart.Series.Add(r1, r2)
;chart
chart := ws.Drawings.AddChart("Pie3D", 70, ComObject(13,0))
; The last param, thanks to lexikos, allows the chart to be added
series := chart.Series.Add(r1, r2)
chart.Title.Text := "Monkey Tameness Index"
chart.Style := 0 ; I believe there are 48 styles
; altogether, requiring enum
; *****
chart.SetSize(100) ; percent of full size
chart.From.Column := 0
chart.From.Row := 4
chart.To.Row := 11
chart.To.Column := 4
;chart.SetPosition(120, 0) ; 120 from top, 0 from left
;make a table from the data
rng := ws.Cells.("A1:B4")
tblcl := ws.Tables
tbl := tblcl.Add(rng, "Monkeys")
;try some formatting options
tbl.ShowHeader := True
tbl.TableStyle := 7
;tbl.TableStyle := 2
;tbl.ShowTotal := true
pck.SaveAs( outfile ) ; <<--- see below
;pck.SaveAs(FileInfo("Tame Monkeys 2.xlsx"))
ExitApp
; Thanks to tmplinshi for this function
; Some scripts I have added use it.
; For example, I could have written
; 'pck.SaveAs(FileInfo("Tame Monkeys 2.xlsx"))'
; instead of the first option above.
; Try it.
FileInfo(filename)
{
static lib := Clr_LoadLibrary("mscorlib")
return Clr_CreateObject(lib, "System.IO.FileInfo", filename)
}
; You can use this if you need to test Com objects.
ShowInfo(object, objname) {
vartype := ComObjType(object)
name := ComObjType(object, "Name")
clsid := ComObjType(object, "CLSID")
cname := ComObjtype(object, "Class")
msgbox %objname%'s class is %cname%`r`nName is %name%`r`nCLSID is %clsid%`r`nVT is %vartype%
}
Here are a few chart types.
Style := 0
MonkeyChartStyle0.PNG (10.25 KiB) Viewed 6912 times
Style := 21
MonkeyChartStyle21.PNG (9.92 KiB) Viewed 6912 times