Python Excel Interop - Write Picture \ Image in Footer \ Header of worksheet

Developers
, , ,
1

Good Morning everyone,

I am trying from within python to add a picture on the footer/header of an excel spreadsheet.
can anyone help me to figure out what is the correct code to do it?

Up till now I managed to:

-Write text in a range of cells,
-Merge cells,
-Create text for the footer / header (with automatic page numbering too)
-Setup the size & orientation of the layout,
-Save and open automatically the file in printing preview mode,
-Import an image in the worksheet (but not in the footer/header).

I attached a picture of my code, with marked up the ā€œproblematic zoneā€:

Capture.JPG1244Ɨ895 117 KB

I will NOT use module packages like openpyxl, xlsxwriter, xlrd, xlwt, xlutils,
BUT I would like to use only the Microsoft.Office.Interop.Excel (but I canā€™t figure out how to make it work).

Here are some links from which I drew inspiration:

http://xlsxwriter.readthedocs.io/example_headers_footers.html

https://msdn.microsoft.com/en-us/library/office/microsoft.office.interop.excel.page.centerfooter.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1

https://msdn.microsoft.com/en-us/library/office/microsoft.office.interop.excel.pagesetup.rightheaderpicture.aspx

the dynamo file here attached too:

TEST.dyn (4.5 KB)

the result would be very useful for the community to have full control over the excel formatting, together with the amazing bumblebee package!

Thanks,
Stefano

1 Like
2

You can do something like this:

def EmbedImage(ws, imagePath, origin):
	img = ws.Pictures().Insert(Filename = imagePath)
	
	range = img.ShapeRange
	range.LockAspectRatio = True
	range.Width = 75
	range.Height = 100
	
	img.Left = ws.Cells[origin[1], origin[0]].Left
	img.Top = ws.Cells[origin[1], origin[0]].Top
	img.Placement = 1
	img.PrintObject = True
	
	return ws

Where image path is a string path to image that you want to embed. Width/Height is in points, Left and Right property for image location comes from index values of a cell so for example A1 is 1,1 and then its aligned to Top/Left of that cell.

You can also use another method that accesses excelā€™s shapes library and create a new shape like this:

img = ws.Shapes.AddPicture(imagePath, True, True, 100, 100, 70, 100)

where these are the arguments:

Pasted image1279Ɨ354 23.8 KB

3

Thanks very much Konrad,
I will try asap to make the code you suggested work on my script.
Meanwhile, do you know if I can actually make work the .PageSetup.RightHeaderPicture.Filename = "C:/Usersā€¦ logo.jpg" part (with which I am struggling) , to allow the image on the Right Header (which automatically sets it to repeat in every page of the layout)?
basically it gives me an error when I run the script the way I set it up as the image aboveā€¦

Iā€™m attaching an excel file which gives an example of what I mean with RightHeaderPicture.test.xlsx (13.7 KB)

Thanks again,
Stefano

4

Sorry about that. Itā€™s relatively easy to setup the header image. Try this:


def PageSetup(ws, path, width, height):
	img = ws.PageSetup.RightHeaderPicture
	img.FileName = path
	img.Width = width
	img.Height = height
	
	ws.PageSetup.RightHeader = "&G"

ws is a Worksheet and the rest should be clear. Sorry about the original answer that was not on topic.

Hereā€™s the related MSDN documentation: https://msdn.microsoft.com/en-us/library/office/aa215490(v=office.11).aspx

5

Hi Konrad,
I tried to use your code as you suggested (I think I did it rightā€¦ and if Iā€™m not mistaken it is based on the same principle of the first code I uploaded).
But no result visible yetā€¦ (the picture is in that path, the test excel file tooā€¦ it opens without errors, but nothing on the header)

here is my code (and dyn file attached too from start5.dyn (2.6 KB)):

import clr
clr.AddReference('ProtoGeometry')
from Autodesk.DesignScript.Geometry import *

myfilepath = IN[0]

#Assign your output to the OUT variable.
OUT = 0

import clr
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel

# Instantiate the Excel Application
ex = Excel.ApplicationClass()

# Make it Visiable for us all to see
ex.Visible = True

# Disable Alerts - Errors Ignore them, they're probably not important
ex.DisplayAlerts = False

# Create a new Workbook = Spreadsheet file
workbook = ex.Workbooks.Open(myfilepath)
#Create a new Worksheet within our new Workbook
worksheet = workbook.Worksheets[1]  # Arrays of Object Start 1 in the MS Office World



def PageSetup(worksheet, logopath, width, height):
	img = worksheet.PageSetup.RightHeaderPicture
	img.FileName = "C:\Users\STEFANO\Pictures\logo.jpg"
	img.Width = 100
	img.Height = 100

	
	worksheet.PageSetup.RightHeader = "&G"
	

worksheet.PrintPreview(False)
worksheet.SaveAs(unicode(myfilepath))


# Clear off the Object references to the Com Objects
cell = worksheet = workbooks = ex = None

do you see anything wrong in it?

6

you need to actually call PageSetup() somewhere in your code. Also, add ā€˜rā€™ for raw string file path.