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,
-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”:
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:
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!
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, origin].Left
img.Top = ws.Cells[origin, origin].Top
img.Placement = 1
img.PrintObject = True
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:
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)
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
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)):
from Autodesk.DesignScript.Geometry import *
myfilepath = IN
#Assign your output to the OUT variable.
OUT = 0
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=126.96.36.199, 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 # 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"
# Clear off the Object references to the Com Objects
cell = worksheet = workbooks = ex = None
do you see anything wrong in it?
you need to actually call PageSetup() somewhere in your code. Also, add ‘r’ for raw string file path.