EPPlus help needed - redux Topic is solved

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

EPPlus help needed - redux

28 Jun 2018, 18:19

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
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
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: EPPlus help needed - redux

28 Jun 2018, 18:25

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
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
burque505
Posts: 1463
Joined: 22 Jan 2017, 19:37

Re: EPPlus help needed - redux

28 Jun 2018, 18:28

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
Last edited by burque505 on 29 Jun 2018, 07:45, edited 1 time in total.
tmplinshi
Posts: 1600
Joined: 01 Oct 2013, 14:57

Re: EPPlus help needed - redux  Topic is solved

29 Jun 2018, 00:23

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)
}
:beer:
burque505
Posts: 1463
Joined: 22 Jan 2017, 19:37

Re: EPPlus help needed - redux

29 Jun 2018, 07:37

:beer: :beer: :beer: :bravo: :beer: :beer: :beer:
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
Posts: 1463
Joined: 22 Jan 2017, 19:37

Re: EPPlus help needed - redux

01 Jul 2018, 08:36

Re charts: I've been getting consistent errors trying to use the AddChart method. :headwall: 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
Posts: 1600
Joined: 01 Oct 2013, 14:57

Re: EPPlus help needed - redux

01 Jul 2018, 09:32

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
There are some other strange errors, such as ws.SetValue("B2", "TEST") didn't work, but ws.SetValue(2, 2, "TEST") works.
burque505
Posts: 1463
Joined: 22 Jan 2017, 19:37

Re: EPPlus help needed - redux

01 Jul 2018, 11:42

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
Posts: 1463
Joined: 22 Jan 2017, 19:37

Re: EPPlus help needed - redux

04 Jul 2018, 11:04

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
ChartObject.PNG (6.65 KiB) Viewed 1888 times
tmplinshi
Posts: 1600
Joined: 01 Oct 2013, 14:57

Re: EPPlus help needed - redux

04 Jul 2018, 15:33

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
Posts: 1463
Joined: 22 Jan 2017, 19:37

Re: EPPlus help needed - redux

04 Jul 2018, 17:08

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

Return to “Ask For Help”

Who is online

Users browsing this forum: Google [Bot], Savage O Press and 49 guests