6

I have an "Excel template" that:

  1. Opens existing file
  2. Edits cell values without modifying the styles (font, cell width and height, cell merge, etc.)
  3. Adds or inserts an image (jpg or png) to save it in a new file.

I have tried 4 options at the moment.

  1. XLWT/XLRD - This baby does what it seems to be the most efficient work, it opens the xlsx pretty well keeping all styles, it is very easy to edit cells (with some tricks to keep the style) BUT it only inserts Bitmap (bmp) images, and when it inserts the image, it becomes stretched, not keeping aspect radio nor size, I would like to know if it has solution.
  2. XLSXWritter - It is very functional and easy to use to CREATE NEW FILES. It can't open existing ones. This is not an option. :(
  3. openpyxl - It appears to insert images with PIL (jpg, png, gif, etc.), but when I open the existing "template" all the styles are gone, everything!! so it is not an option.
  4. pythonexcels - It seems to work with Microsoft Office Excel and win32com so maybe it is not a crossed platform option, and I can't find a way to insert images with this.

I think I could use some PDF creator as well but I need to keep the page size because I need to send the created file to a printer, but not with a letter or A4 size, I need to print in adhesive labels of different sizes.

Question: Is there any other solution to xlwt?

csgroen
  • 2,511
  • 11
  • 28
Alex Lord Mordor
  • 2,890
  • 7
  • 27
  • 47
  • [This is how you do it in VBA.](https://i.stack.imgur.com/8orQ4.png).Are there any similar properties available in your Python library? Also if XLSXWriter has the functionality - why not use 2 libraries at once - one for reading one for writing - use a bit of script to rename old one or delete it - and write back new version. – JGFMK Feb 27 '19 at 12:42

1 Answers1

0

We had just recently a similar problem where it was the goal to keep the format intact and our solution based on openpyxl. So I know that openpyxl supports in the meantime formatting.

Here my simple example which solves the questioned points:

# working dir
|- test.xlsx
|- test42.png
|- test.py

test.xlsx:

enter image description here

test.py:

import openpyxl

path = "test.xlsx"
wb_obj = openpyxl.load_workbook(path)
sheet_obj = wb_obj.active
cell_obj = sheet_obj.cell(row=2, column=1)
cell_obj.value = "Earth"

img = openpyxl.drawing.image.Image('test42.png')
img.anchor = 'B2'
sheet_obj.add_image(img)

wb_obj.save("test_new.xlsx")

test_new.xlsx:

enter image description here

MaKaNu
  • 762
  • 8
  • 25