Insert header in Python worksheet

Question

I need to insert an image into the header of the xlsx file that is generated by Python with a routine of capturing database data and turning it into report. I’m using this alternative method, where you insert in the first line, however, when I put the file to print it does not look like it should, which in this case, would be in the page header.

wb = openpyxl.load_workbook(r'C:\Users\daymon.rebac\Rotinas\ARQUIVO.xlsx')

ws = wb ["Sheet1"]

img_logo = r"C:\Users\daymon.rebac\Rotinas\CROSS.jpg"

imagem_logo = openpyxl.drawing.image.Image(img_logo)

img_hpf = r"C:\Users\daymon.rebac\Rotinas\FPH_VZ_AR.jpg"

imagem_hpf = openpyxl.drawing.image.Image(img_hpf)

ws.add_image(imagem_logo, 'A1')

ws.add_image(imagem_hpf, 'D1')


wb.save(r'C:\Users\daymon.rebac\Rotinas\ARQUIVO.xlsx')


wb.close()

Answer 1

I believe that neither the openpyxl, nor any other XLSX file creation library (or ODS, which are also spreadsheets) provides a level of control over image positioning and resizing and style-they all focus more on the contents of cells.

You can try some experiments with the (style) style of the cell where you put the image - but the Openpyxl documentation doesn’t help much - https://openpyxl.readthedocs.io/en/stable/styles.html

You have two other approaches to try to evolve your problem

1 - Fix worksheet XML "manually"

Both "xlsx" and "ods" files are composed of a "zip" of XML files, where the content really is. These XML will have several specific attributes and tags where are stored the parameters of positioning and formatting of items, which are usually edited manually in the worksheet program (in this case, Excel).

Then the suggestion would be, after creating a spreadsheet as you do above, open it in excel, position the image as you want, and save as another spreadsheet - decompress the two worksheets, side by side, (in windows you can decompress with the same program that treats zip files - be it Winrar, Winzip, etc..., you may need to rename the worksheet to change the extension), and, with the worksheets uncompressed, try to locate the difference in XML for formatting your image - may be one or more tags, and several attributes -

at that point, if you can identify the attributes that excel uses to position and give the image size it may be possible to use the features of style from Openpyxl to format the image (as it is above). This certametne would be the most viable path of all!

Otherwise, check if manipulating xml through Python can recreate and change the tags in XML as it is generated by Openpyxl to arrive at the same values as Excel (use the library zipfile to "open" the xlsx and xml.etree to read and change the xml, and then zipfile to recreate the xlsx.

It won’t be an easy task!

2 - Programmatically interact with Excel

Generate the spreadsheet with Openpyxl normally, and then use direct interaction of Python with Excel opened through win32con - which allows you to call "functions" of the Excel interface from Python, to have excel open your spreadsheet, position the image as needed, and save her back.

It might be less work - but it’s gonna be pretty boring too.

Here is a list of resources and libraries to work with integrating Python and Excel - some of them may even allow image placement more directly:

https://www.pyxll.com/blog/tools-for-working-with-excel-and-python/

Answer 2

I found how to make even much easier and practical than I thought:

Ws.PageSetup.Centerheaderpicture.Filename = (r'C: Users daymon.rebac Routines IMAGES Excel Horizontal header.png') Ws.PageSetup.Centerheader = "&G" Ws.PageSetup.Centerfooterpicture.Filename = (r'C: Users daymon.rebac Routines IMAGES Footer Excel Horizontal.png') Ws.PageSetup.Footer = "&G"

And then just adjust the page margins:

Ws.PageSetup.Leftmargin = 50.0 Ws.PageSetup.Topmargin = 70.0 Ws.PageSetup.Rightmargin = 50.0 Ws.PageSetup.Zoom = False