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

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.