Help with EPPlus.dll and AHK Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Help with EPPlus.dll and AHK

20 Jun 2018, 11:40

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.

I have used dnspy as suggested by qwerty12 to try to determine the right objects to create, but so far no luck.

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.
tmplinshi
Posts: 1487
Joined: 01 Oct 2013, 14:57

Re: Help with EPPlus.dll and AHK  Topic is solved

22 Jun 2018, 13:45

Working example:

Code: Select all

#NoEnv
SetWorkingDir %A_ScriptDir%

asm := Clr_LoadLibrary(".\epplus\lib\net40\EPPlus.dll")
pck := Clr_CreateObject(asm, "OfficeOpenXml.ExcelPackage")
Worksheet := pck.Workbook.Worksheets.Add("Sheet1")
Worksheet.Cells.LoadFromText("a,b,c")

lib := Clr_LoadLibrary("mscorlib")
outfile := Clr_CreateObject(lib, "System.IO.FileInfo", "out.xlsx")
pck.SaveAs(outfile)
Download EPPlus Test.7z
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Re: Help with EPPlus.dll and AHK

22 Jun 2018, 14:04

Thank you!!!!!!!! I doubt very seriously I would have ever found that reference to mscorlib on my own.
You are the best. 你是最棒的。
Regards,
burque505
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Re: Help with EPPlus.dll and AHK

22 Jun 2018, 18:00

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).

Code: Select all

; 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.
Spoiler
Result (D2 is the calculated sum of A2 through C2)
11.PNG
11.PNG (7.71 KiB) Viewed 2266 times
EDIT: Font colors and families.
Spoiler
Colors.png
Colors.png (10.61 KiB) Viewed 2260 times
Regards,
burque505
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Re: Help with EPPlus.dll and AHK

25 Jun 2018, 12:29

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.
Spoiler
Partial GIF of sheet in Excel:
Borders.PNG
Borders.PNG (5.6 KiB) Viewed 2246 times
Regards, burque505
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Re: Help with EPPlus.dll and AHK

25 Jun 2018, 19:10

Some success with border colors and named style:
Spoiler
Borders2.png
Borders2.png (7.06 KiB) Viewed 2224 times
tmplinshi
Posts: 1487
Joined: 01 Oct 2013, 14:57

Re: Help with EPPlus.dll and AHK

26 Jun 2018, 01:22

Thanks for sharing the examples. I'm going to add some. :)

Create excel content then upload directly without saving to file:

Code: Select all

#NoEnv
SetWorkingDir %A_ScriptDir%

asm := Clr_LoadLibrary("EPPlus.dll")
pck := Clr_CreateObject(asm, "OfficeOpenXml.ExcelPackage")
Worksheet := pck.Workbook.Worksheets.Add("Sheet1")
Worksheet.Cells.LoadFromText("a,b,c")

body := pck.GetAsByteArray()

whr := ComObjCreate("WinHttp.WinHttpRequest.5.1")
whr.Open("PUT", "https://transfer.sh/test.xlsx", true)
whr.Send(body)
whr.WaitForResponse()

MsgBox % resultUrl := whr.ResponseText
Run, % resultUrl
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Re: Help with EPPlus.dll and AHK

26 Jun 2018, 07:15

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:

Code: Select all

lib := Clr_LoadLibrary("mscorlib")
outfile := Clr_CreateObject(lib, "System.IO.FileInfo", "Borders.xlsx")
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

Code: Select all

var package = new ExcelPackage(new FileInfo("..\\..\\..\\sample.xlsx"))
.
EDIT: Or this -

Code: Select all

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:

Code: Select all

lib := Clr_LoadLibrary("mscorlib")
infile := Clr_CreateObject(lib, "System.IO.FileStream", "test.xlsx", "FileMode.Open")
and then loading with "pck.Load(infile)". But I get this:
Spoiler
Do you have any thoughts on that?
Best regards,
burque505
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Re: Help with EPPlus.dll and AHK

26 Jun 2018, 08:21

Alignment works horizontally, at least :)
Spoiler
HorizontalAlignment.PNG
HorizontalAlignment.PNG (17.62 KiB) Viewed 2164 times
tmplinshi
Posts: 1487
Joined: 01 Oct 2013, 14:57

Re: Help with EPPlus.dll and AHK

26 Jun 2018, 09:53

Tried with FileStream but no luck.
Open existing file using System.IO.FileInfo:

Code: Select all

#NoEnv
SetWorkingDir %A_ScriptDir%

EPPlus := Clr_LoadLibrary("EPPlus.dll")
pck := Clr_CreateObject(EPPlus, "OfficeOpenXml.ExcelPackage", FileInfo("test.xlsx"))

ws := pck.Workbook.Worksheets.(1)
ws.Cells.("A1").Value := 50

pck.Save()
; pck.SaveAs( FileInfo("new.xlsx") )

ExitApp

FileInfo(filename)
{
	static lib := Clr_LoadLibrary("mscorlib")
	return Clr_CreateObject(lib, "System.IO.FileInfo", filename)
}
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Re: Help with EPPlus.dll and AHK

26 Jun 2018, 10:40

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 :bravo:
Spoiler
Last edited by burque505 on 26 Jun 2018, 11:22, edited 1 time in total.
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Re: Help with EPPlus.dll and AHK

26 Jun 2018, 10:42

Vertical alignment:
Spoiler
Looks like this:
VerticalAlignment.PNG
VerticalAlignment.PNG (3.93 KiB) Viewed 2139 times
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Re: Help with EPPlus.dll and AHK

26 Jun 2018, 11:06

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.
Spoiler
test.7z
(5.61 KiB) Downloaded 26 times
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Re: Help with EPPlus.dll and AHK

26 Jun 2018, 15:34

Background fill is working (enums yet again ...)
Spoiler
Fill.PNG
Fill.PNG (11.38 KiB) Viewed 2127 times
Regards,
burque505
User avatar
Xeo786
Posts: 193
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Help with EPPlus.dll and AHK

27 Jun 2018, 03:33

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.

Code: Select all

XlApp := ComObjActive("Excel.Application")
xlRange := XlApp.Worksheets(1).Range("a1:c13")
xlBorder(xlRange,7,2,10,0xff00ff)
return

/*
;;;;;;;;;;;;;;;;;;
;;;; xlBorder ;;;;
;;;;;;;;;;;;;;;;;;
Borders are 
		1 = left
		2 = right
		3 = up
		4 = down
		5 = Leftcut
		6 = rightcut
		7 = all borders
		8 = all border with cross
		9 = left right borders
		10 = up and down border
		11 = only corss

Weight are 1 to 4

Line Style are 1 to 10

color 0x00000 to 0xffffff
*/

xlBorder(cell,borders=1, Weight=1, LineStyle=1, lineColor=0x000000){
	if ( weight > 4) or ( weight < 1)
		weight := 1
	
	if ( LineStyle > 10) or ( LineStyle < 1)
		LineStyle := 1

	if ( borders > 11) or ( borders < 1)
		borders := 1
	
		if( borders = 7)
			loop 4 {
				cell.Borders(a_index).Weight := Weight
				cell.Borders(a_index).LineStyle := LineStyle
				cell.Borders(a_index).Color := lineColor
				}
		else if( borders = 8)
			loop 6 {
				cell.Borders(a_index).Weight := Weight
				cell.Borders(a_index).LineStyle := LineStyle
				cell.Borders(a_index).Color := lineColor
				}
		else if( borders = 9)
			loop 2 {
				cell.Borders(a_index).Weight := Weight
				cell.Borders(a_index).LineStyle := LineStyle
				cell.Borders(a_index).Color := lineColor
				}
		else if( borders = 10)
			loop 2 {
				cell.Borders(a_index+2).Weight := Weight
				cell.Borders(a_index+2).LineStyle := LineStyle
				cell.Borders(a_index+2).Color := lineColor
				}
		else if( borders = 11)
			loop 2 {
				cell.Borders(a_index+4).Weight := Weight
				cell.Borders(a_index+4).LineStyle := LineStyle
				cell.Borders(a_index+4).Color := lineColor
				}
		else 
		{
		cell.Borders(borders).Weight := Weight
		cell.Borders(borders).LineStyle := LineStyle
		cell.Borders(borders).Color := lineColor
		}
	}		
RETURN
"When there is no gravity, there is absolute vacuum" -Game changer theory
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Re: Help with EPPlus.dll and AHK

27 Jun 2018, 07:34

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
Fill2.png
Fill2.png (15.94 KiB) Viewed 2091 times
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Re: Help with EPPlus.dll and AHK

27 Jun 2018, 11:03

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.

Code: Select all

#NoEnv
SetWorkingDir %A_ScriptDir%

asm := Clr_LoadLibrary(".\EPPlus.dll")

pck := Clr_CreateObject(asm, "OfficeOpenXml.ExcelPackage")
;chrt := Clr_CreateObject(pck, "Drawing.Chart")
;draw := Clr_CreateObject(asm, "OfficeOpenXml.ExcelDrawings")
;ShowInfo(draw, "draw")

shapenum := 1
x := 100
shapesize := 20

ws := pck.Workbook.Worksheets.Add("Chart")

loop, 186
{
shape := ws.Drawings.AddShape("Shape" . shapenum, shapenum)
shape.SetPosition[x, 100]
shape.SetSize(shapesize)
shapenum += 1
x += 50
}

;chart := ws.Drawings.Chart.AddChart("Chart", 5)


lib := Clr_LoadLibrary("mscorlib")
outfile := Clr_CreateObject(lib, "System.IO.FileInfo", "ChartTest.xlsx")


pck.SaveAs(outfile)

ExitApp
You can (sort of) see the shapes here:
EPPlusShapes.gif
EPPlusShapes.gif (94.77 KiB) Viewed 2074 times
shapeenums.txt
(8.93 KiB) Downloaded 30 times
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Re: Help with EPPlus.dll and AHK

27 Jun 2018, 17:27

Commenting:
Spoiler
EPPlusComments.gif
EPPlusComments.gif (10.5 KiB) Viewed 2055 times
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Re: Help with EPPlus.dll and AHK

08 Jul 2018, 11:39

Charts available now.
Spoiler
MonkeyChart.PNG
MonkeyChart.PNG (26.3 KiB) Viewed 1980 times
burque505
Posts: 1005
Joined: 22 Jan 2017, 19:37

Re: Help with EPPlus.dll and AHK

09 Jul 2018, 14:52

This version uses built-in EPP functions to set the row and column placement for the chart.
Spoiler
Here are a few chart types.
Style := 0
MonkeyChartStyle0.PNG
MonkeyChartStyle0.PNG (10.25 KiB) Viewed 1960 times
Style := 21
MonkeyChartStyle21.PNG
MonkeyChartStyle21.PNG (9.92 KiB) Viewed 1960 times

Return to “Ask For Help”

Who is online

Users browsing this forum: boiler, Google [Bot], MannyKSoSo, pureby and 151 guests