EPPlus help needed - redux Topic is solved

[burque505]

I've been wrestling with EPPlus.dll as in this thread..
Although I've had some success, I've been struggling with charts and pictures. Chart types are enumerated values, I'm pretty sure, so I thought I would have luck as I did with shapes and borders. But not so far ...
The problem code for images is (more or less) like this in C#, and works perfectly if compiled in VS:

Code: Select all

            var img = Image.FromFile("image.jpg");
            var pic = ws.Drawings.AddPicture(myPicture, img);
            shape.SetPosition(3, 0, 3, 0)       //Position Row, RowOffsetPixels, Column, ColumnOffsetPixels

From the developer's page on Shapes, Pictures and Charts.
What I've tried is this, where

Code: Select all

pck := Clr_CreateObject(asm, "OfficeOpenXml.ExcelPackage")

and

Code: Select all

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

:

Code: Select all

picture := ws.Drawings.AddPicture("Picture", "Speaker.bmp")

This error is thrown:

Spoiler

Code: Select all

---------------------------
Image5.ahk
---------------------------
Error:  0x80070057 - The parameter is incorrect.

Specifically: AddPicture

	Line#
	003: lib := Clr_LoadLibrary("mscorlib")
	004: outfile := Clr_CreateObject(lib, "System.IO.FileInfo", "Image.xlsx")
	005: asm := Clr_LoadLibrary(".\EPPlus.dll")
	006: pck := Clr_CreateObject(asm, "OfficeOpenXml.ExcelPackage")
	009: filetext := "Sample"
	011: ListVars
	012: ws := pck.Workbook.Worksheets.Add("Image")
--->	013: picture := ws.Drawings.AddPicture("Picture", "Speaker.bmp")
	014: picture.SetPosition(200, 200)  
	015: picture.SetSize(200, 200)  
	018: pck.SaveAs(outfile)  
	020: ExitApp
	037: {
	038: vartype := ComObjType(object)
	039: name := ComObjType(object, "Name")

Continue running the script?
---------------------------
Yes   No   
---------------------------

I've tried replacing the path "Speaker.bmp" with ImageFromFile("Speaker.bmp"), where that function is (not that it works):

Code: Select all

ImageFromFile(filename)
{
	static lib := Clr_LoadLibrary("System.Drawing")
	return Clr_CreateObject(drawing, "Image.FromFile", filename)
}

All help and suggestions greatly appreciated.

Regarding charts, I should be able to add one with

Code: Select all

chart := ws.Drawings.AddChart("Sample", eChartType.Pie)

, but it throws an error saying the interface isn't supported (but eChartType is a public enum). Using

Code: Select all

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

, where the enum for "Pie" should be 5, the "interface" error isn't thrown, but instead "the parameter is incorrect".

Again, all help and suggestions greatly appreciated.
Regards,
burque505

[jeeswg]

I thought I'd mention this just in case. When using Excel with COM, sometimes you have to pass numbers as objects e.g.:
ComObject
https://lexikos.github.io/v2/docs/comma ... Object.htm

Code: Select all

ComObject(0xB, -1) ;VT_BOOL := 0xB

ComObjType()
https://autohotkey.com/docs/commands/ComObjType.htm#vt

[burque505]

Thanks, jeeswg, although this doesn't use Excel per se (just creates .xlsx and .xlsm), I bet you're on to something. I'll see if I can do something with that.
Regards,
burque505

[tmplinshi]

Code: Select all

#NoEnv
SetWorkingDir %A_ScriptDir%

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

ws := pck.Workbook.Worksheets.(1)

drawing := Clr_LoadLibrary("System.Drawing")
img := Clr_CreateObject(drawing, "System.Drawing.Bitmap", "D:\Desktop\input.jpg")

picture := ws.Drawings.AddPicture("myPicture", img)
; picture.SetPosition(60, 40)
picture.From.Column := 5
picture.From.Row := 5
picture.SetSize(100) ; 100%

pck.SaveAs( FileInfo("output.xlsx") )

ExitApp

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

[burque505]

Thank you!
I see you managed not only to get the picture in there, but to load an .xlsx on creating the object!!! Beautiful! Inspired.
You also got the .From.Column and .From.Row working.
I had previously gotten as far as creating a System.Drawing object, but I never would have thought of the "System.Drawing.Bitmap" approach.
EDIT & P.S.: Any thoughts on those charts?
Regards,
burque505

[burque505]

Re charts: I've been getting consistent errors trying to use the AddChart method. This is sample C# code:

Code: Select all

var myChart = myWorksheet.Drawings.AddChart("chart", eChartType.Pie)

Translated into AHK, this becomes

Code: Select all

chart = ws.Drawings.AddChart("chart", eChartType.Pie)

which I thought probably wouldn't work anyway, because AHK (for EPPlus.dll, anyway) seems to require the integer value for an enumeration rather than its name.
The error I get is

Code: Select all

Error:  0x80004002 - No such interface supported

For 'eChartType.Pie', the corresponding integer is 5.
So I tried

Code: Select all

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

. The error I get is:

Code: Select all

Error:  0x80070057 - The parameter is incorrect.

Looking at this MSDN page about MSXML DOM I see these same two errors are the probable result of mixing different versions of DOM objects.
Does anyone have any clues how I work around these errors? I have no idea which versions of the MSXML DOM are in use in EPPlus.dll, but considering its OfficeOpenXml, there could very well be a couple of versions.
Regards,
burque505

[tmplinshi]

I also tried this but didn't work:

Code: Select all

eChartType := EPPlus.CreateInstance("OfficeOpenXml.Drawing.Chart.eChartType")
myChart := ws.Drawings.AddChart("chart1", eChartType.Pie)

Spoiler

Code: Select all

#NoEnv
SetWorkingDir %A_ScriptDir%

EPPlus := Clr_LoadLibrary("EPPlus.dll")

pck := EPPlus.CreateInstance("OfficeOpenXml.ExcelPackage")
; MsgBox, % pck.ToString() ; -> OfficeOpenXml.ExcelPackage

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

eChartType := EPPlus.CreateInstance("OfficeOpenXml.Drawing.Chart.eChartType")
; MsgBox, % eChartType.ToString()
; MsgBox, % eChartType.Pie

myChart := ws.Drawings.AddChart("chart1", eChartType.Pie)
; myChart := ws.Drawings.AddChart("chart1", 5)
series := myChart.Series.Add("C2:C4", "B2:B4")
myChart.Border.Fill.Color := 0x00ff00 ;System.Drawing.Color.Green
myChart.Title.Text := "My Chart"
myChart.SetSize(400, 400)
myChart.SetPosition(6, 6)

pck.SaveAs( FileInfo("AddChart.xlsx") )

ExitApp

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

There are some other strange errors, such as ws.SetValue("B2", "TEST") didn't work, but ws.SetValue(2, 2, "TEST") works.

[burque505]

Looks like both of us have been looking at this page.
Yes, you're absolutely right.
Another oddity is that red and blue are reversed in the hex code order:

Code: Select all

newStyle.Style.Font.Color.SetColor(0xFF0000)

should be red, but it's blue!

Code: Select all

newStyle.Style.Font.Color.SetColor(0x0000FF)

should be blue, but it's red.
Also,

Code: Select all

Worksheet.Cells.("A1").Value := "General"

works, but

Code: Select all

Worksheet.Cells["A1"].Value := "General"

does not, and neither does

Code: Select all

Worksheet.Cells("A1").Value := "General"

.
Curiouser and curiouser . . .

[burque505]

Able to create a ChartSheet object, apparently because that can be done directly from the ExcelPackage object. The object (with no content, so far) displays on the second sheet of the chart in the code below.
Still need to be able to create an OfficeOpenXml.Drawing.Chart object, I think.

Code: Select all

#NoEnv
SetWorkingDir %A_ScriptDir%

#Include CLR_c.ahk

;Clr_Start("4.0.30319")
lib := Clr_LoadLibrary("mscorlib")

outfile := Clr_CreateObject(lib, "System.IO.FileInfo", "ChartSheetTest.xlsx")

asm := Clr_LoadLibrary(".\EPPlus.dll")
pck := Clr_CreateObject(asm, "OfficeOpenXml.ExcelPackage")

wb := pck.Workbook
ws := wb.Worksheets.Add("ChartSheet")
cs := wb.Worksheets.AddChart("PieChart", 5)
;showinfo(cs, "cs")

ws.Cells.("C2").Value := 10
ws.Cells.("C3").Value := 40
ws.Cells.("C4").Value := 30
 
ws.Cells.("B2").Value := "Yes"
ws.Cells.("B3").Value := "No"
ws.Cells.("B4").Value := "NA" ;works
ws.SetValue(5, 2, "Test")

pck.SaveAs(outfile)

ExitApp

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


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's what the blank object looks like on the sheet so far. If anyone knows how to add data to this, I'd love to see it.

ChartObject.PNG (6.65 KiB) Viewed 2632 times

[tmplinshi]

Based on your work, I searched for epplus "Worksheets.AddChart". Working example:

Code: Select all

#NoEnv
SetWorkingDir %A_ScriptDir%

EPPlus := Clr_LoadLibrary("EPPlus.dll")
pck := EPPlus.CreateInstance("OfficeOpenXml.ExcelPackage")

sheet1 := pck.Workbook.Worksheets.Add("Sheet1")
sheet1.Cells.("C2").Value := 10
sheet1.Cells.("C3").Value := 40
sheet1.Cells.("C4").Value := 30
 
sheet1.Cells.("B2").Value := "Yes"
sheet1.Cells.("B3").Value := "No"
sheet1.Cells.("B4").Value := "NA"

ChartSheet := pck.Workbook.Worksheets.AddChart("ChartSheet", 5)
chart := ChartSheet.Chart

r1 := sheet1.Cells.("C2:C4")
r2 := sheet1.Cells.("B2:B4")
series := chart.Series.Add(r1, r2)

; chart.Border.Fill.Color := 0x0000FF ; not working
chart.Title.Text := "My Chart"
; chart.Title.Font.Size := 55
; chart.Title.Font.Bold := true

pck.SaveAs( FileInfo("AddChart.xlsx") )

ExitApp

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

[burque505]

tmplinshi, I thank you yet again!
Very nice indeed.
Regards,
burque505