Help with EPPlus.dll and AHK Topic is solved

[Klarion]

hmm
thanks Guys
this post will be Big help with me

Regards

[Klarion]

I do not know the reason
But
It is not working
Just emits error

Code: Select all

myAssembly := CLR_LoadLibrary("EPPlus.dll")
myPackage := CLR_CreateObject(myAssembly, "OfficeOpenXml.ExcelPackage")
myWorkSheet := myPackage.Workbook.Worksheets.Add("myWorkSheet001")

at 3rd line, AHK pops error window
Error: 0x80131509 -
Specifically: Worksheets

I am so sick and tired

[burque505]

This may help. Please look at tmplinshi's FileInfo() function in the code.
You need a spreadsheet called "App 2.xlsx" in your script directory the way it's written.
More soon.
Regards,
burque505

Code: Select all

/*
EPPlus - TEMPLATING - Part One
This very simple script shows how to add rows to
existing XLSX files and save the result.
The script at https://stackoverflow.com/questions/9571581/epplus-how-to-use-a-template
was helpful (see the VB example by JamesCBaird).
The FileInfo() function is by tmplinshi.
EPPlus.dll needs to be in your script directory.
You'll also need "App 2.xlsx" (may be blank.)
Run the script several times to verify rows have been added.
*/


lib := Clr_LoadLibrary("mscorlib")

infile := FileInfo("App 2.xlsx")
outfile := infile
; modify the above if you want
; a different file to result.

asm := Clr_LoadLibrary(".\EPPlus.dll")
pck := Clr_CreateObject(asm, "OfficeOpenXml.ExcelPackage", infile)
ws := pck.Workbook.Worksheets.(1)
;grab the first worksheet
ws.Name := "AppTest"
;name or rename it.
If !(ws.Dimension.End.Row)
{
	nr := 1
}
Else
{
	nr := ws.Dimension.End.Row + 1
}
; 'nr' is be concatenated with the column
; letter to get the cell address.
ws.Cells.("A"nr).Value := "Hello World!"
ws.Cells.("A"nr).Style.Font.Size := 24
ws.Cells.("A"nr).Style.Font.Color.SetColor(0xFF0000)
ws.Cells.("A"nr).AutoFitColumns()

pck.SaveAs(outfile)


FileInfo(filename)
{
	static lib := Clr_LoadLibrary("mscorlib")
	return Clr_CreateObject(lib, "System.IO.FileInfo", filename)
}

[burque505]

@Klarion, this works for me here. The key is that FileInfo() reference. Hope this helps.

Code: Select all

lib := Clr_LoadLibrary("mscorlib")


myAssembly := CLR_LoadLibrary("EPPlus.dll")
myPackage := CLR_CreateObject(myAssembly, "OfficeOpenXml.ExcelPackage")
myWorkSheet := myPackage.Workbook.Worksheets.Add("myWorkSheet001")
myPackage.SaveAs(FileInfo("Klarion.xlsx"))
myPackage := ""

ExitApp


FileInfo(filename)
{
	static lib := Clr_LoadLibrary("mscorlib")
	return Clr_CreateObject(lib, "System.IO.FileInfo", filename)
}

[Klarion]

thanks

I tried just like you said
Got the same error

[burque505]

Maybe a different EPPlus.dll is needed. Try the one from the post at the bottom.

[Klarion]

I have solve the Error
The version was the Evil (version 411)

Note
EPPlus version 4531 (https://www.nuget.org/packages/EPPlus/4.5.3.1)
EPPlus.dll should be located at script folder

Code: Select all

myFile := "myTesting.xlsx"				
myPackage := CLR_CreateObject(CLR_LoadLibrary("EPPlus.dll"), "OfficeOpenXml.ExcelPackage")    
myWorkSheet := myPackage.Workbook.Worksheets.Add("mySheet1")
myWorkSheet.Column(2).Width := 100
myRow := 2
myColumn := 2
myWorkSheet.SetValue(myRow, myColumn, "Hello Excel  !!")  
myWorkSheet.Cells.("B2").Style.HorizontalAlignment := 2
myPackage.SaveAs(CLR_CreateObject(CLR_LoadLibrary("mscorlib"), "System.IO.FileInfo", myFile))	

Thanks burque505

[burque505]

You bet, your code works for me too

[Tigerlily]

Just wanted to say - this is really neat everyone - nice work - keep it up!

[Kobaltauge]

@Invitro, it seems to me like you don't have CLR.ahk in your path anywhere, which might be at fault. I'm attaching a copy just in case. Otherwise it looks like your code should work. Let me know.
You can put CLR.ahk in the same folder and add

Code: Select all

#Include CLR.ahk

at the top.
Regards,
burque505
CLR.ahk

Hi burque505.
Could you please provide the CLR.ahk again? I have the same error.

[burque505]

Sure, here you go.

[Kobaltauge]

Sure, here you go.

worked! Thank you so much!