EPPlus and Excel / add picture to Footer or Header

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
Ahk_fan
Posts: 237
Joined: 31 Aug 2018, 14:34
Contact:

EPPlus and Excel / add picture to Footer or Header

Post by Ahk_fan » 24 Sep 2021, 07:02

Hello,

I'm working with EPPlus.dll / v4.5.3 a long time. Now I've an update on my ducument and have to add a picture to header of my Excel-File.
I've tried a lot of versions, but without success.

Thats ist my code:

Code: Select all

	asm := Clr_LoadLibrary(A_ScriptDir "\lib\epplus\lib\net40\EPPlus.dll")
	pck := Clr_CreateObject(asm, "OfficeOpenXml.ExcelPackage")
	ws := pck.Workbook.Worksheets.Add("Sheet1")
	drawing := Clr_LoadLibrary("System.Drawing")
	pic := Clr_CreateObject(drawing, "System.Drawing.Bitmap", "C:\Windows\Web\Wallpaper\Windows\img0.jpg")
	ws.HeaderFooter.OddHeader.CenteredText := " After"
	;img := ws.HeaderFooter.EvenFooter.InsertPicture(new FileInfo(A_ScriptDir "\99_Konfig\Logo.jpg"), PictureAlignment.Left) 
	img := ws.HeaderFooter.OddHeader.LeftAlignedText.InsertPicture(pic, PictureAlignment.Left)
	img.Title := "Test: "
	lib := Clr_LoadLibrary("mscorlib")
	outfile := Clr_CreateObject(lib, "System.IO.FileInfo", "ttt.xlsx")
	pck.SaveAs(outfile)
ExitApp
I've toggled between line 7 and 8, tested different kind of properties, but without success.

Inserting Picture into Sheet ist possible with:

Code: Select all

	drawing := Clr_LoadLibrary("System.Drawing")
	img := Clr_CreateObject(drawing, "System.Drawing.Bitmap", "C:\Windows\Web\Wallpaper\Windows\img0.jpg")
	picture := Worksheet.Drawings.AddPicture("myPicture", img)
	picture.SetPosition(60, 40)
	picture.From.Column := 5
	picture.From.Row := 5
	picture.SetSize(100) ; 100%
My links:
https://searchcode.com/codesearch/raw/28145283/
https://stackoverflow.com/questions/11588704/adding-images-into-excel-using-epplus
and a lot of other.

I would be grateful, if someone could help
regards,
AHK_fan :)
https://hr-anwendungen.de

Ahk_fan
Posts: 237
Joined: 31 Aug 2018, 14:34
Contact:

Re: EPPlus and Excel / add picture to Footer or Header

Post by Ahk_fan » 27 Sep 2021, 10:55

nobody here with an idea? perhaps @burque505 ? or @tmplinshi ?
regards,
AHK_fan :)
https://hr-anwendungen.de

User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: EPPlus and Excel / add picture to Footer or Header

Post by FanaticGuru » 27 Sep 2021, 13:22

Ahk_fan wrote:
24 Sep 2021, 07:02
I'm working with EPPlus.dll / v4.5.3 a long time. Now I've an update on my ducument and have to add a picture to header of my Excel-File.
I've tried a lot of versions, but without success.

I know nothing about EPPlus.dll but this is how you add an image to the header in Excel using COM.

Code: Select all

; Add image to Header or Footer ("LeftFooter","CenterFooter","RightFooter", "LeftHeader","CenterHeader","RightHeader")
xlApp := ComObjActive("Excel.Application")
xlApp.ActiveSheet.PageSetup.RightHeader := "&G"
xlApp.ActiveSheet.PageSetup.RightHeaderPicture.FileName := A_Desktop "\Test\c.png"

Here is an example of adding an image to a sheet.

Code: Select all

; ...Shapes. AddPicture( Filename , LinkToFile , SaveWithDocument , Left , Top , Width , Height )
; Left Top, Width, Height is points, -1 is auto default, can ust Range.{Left|Top|Width|RowHeight} to get positons based on range
xlApp := ComObjActive("Excel.Application")
FileName := A_Desktop "\Test\c.png"
xlRng := xlApp.Range("C2")
xlShape := xlApp.ActiveSheet.Shapes.AddPicture(FileName, false, true, xlRng.Left, xlRng.Top, -1, -1)

Maybe that will add some insight to how to do it with EPPlus.dll if it uses similar commands as COM.

After a picture is added in either place, it can be formatted with lots of properties.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks

Ahk_fan
Posts: 237
Joined: 31 Aug 2018, 14:34
Contact:

Re: EPPlus and Excel / add picture to Footer or Header

Post by Ahk_fan » 27 Sep 2021, 14:14

Hi @FanaticGuru ,

Thanks for your time.
I know this possibility in Excel VBA. The advantage of EPPlus is that you don't need Excel to do it.
The PageSetup function does not exist in EPPlus. The headers and footers are set with the function HeaderFooter and its properties. In the documentation and the examples it is only superficially described.
regards,
AHK_fan :)
https://hr-anwendungen.de

Post Reply

Return to “Ask for Help (v1)”