Manipulate Microsoft Excel with Python

Aug 6 2022 code #python

Just some script used at work, mainly about inserting pictures into Excel sheets.
Below are something might be useful for other tasks.

#Open file & Switch to sheet

* The library used here is xlwings

Note that the file name and the sheet name should both be in string format.

1
2
3

import xlwings as xw
wb = xw.Book("main.xlsx")
sht = wb.sheets["sheet1"]

#Insert pictures into cells

The function is quite easy to find in the official documentation. Actually if you've got the patience to read the document or even search for things relevant for a while, things I write in this post are just garbage. ^.^

1	add(image, link_to_file=False, save_with_document=True, left=None, top=None, width=None, height=None, name=None, update=False, scale=None, format=None, anchor=None, export_options=None)

#Find cell

We can tell from the function above that in order to insert a picture, we need to know the left and top position of the target cell.

The very first thing to be clear is that in Excel, pictures are not on the same layer as cells or texts inside of cells. So when I say "inserting", I actually mean "paste" a picture onto the given position.

Now the question is, what is the position?

1 2	cell = sht.range("B1:F2") topPos, leftPos = cell.top, cell.left

Simple as that.

One thing only. If the target cell is a merged cell, put the full range inside sht.range() method. -> link

#Resize

When inserting a specific image file into cells, it's rather common that the sizes don't match. Resizing is a must here, well for me at least.

You can simply resize the picture into the target cell size, in spite of the aspect ratio. Or you can scale the picture up or down to match the cell size and keep the aspect ratio as it is. Either way, sizes, are needed, for both cell and image.

1	cellW, cellH = cell.width, cell.height

As for image size, there're millions of ways to get access to it.

#Insert text into cells

Compared with inserting images, inserting text is way easier.

1	sht.range(f"{cell_range}").value = f"{your_text}"

Voila!

#Ending

Yeah, don't forget to save and close the Excel file in the end. Or just save the file so as to make sure pictures are placed just fine.

1 2	wb.save() wb.close()

#python