Crash when adding picture to workbook that has moved & hidden sheets from openpyxl

[blairfrandeen]

OS Windows 10 Enterprise 22H2

Versions of xlwings, Excel and Python (e.g. 0.11.8, Office 365, Python 3.7)

• xlwings 0.30.4
• python 3.10.11
• Office 365

Describe your issue (incl. Traceback!)

When trying to use worksheet.pictures.add(filepath) on a workbook that has had sheets copied and moved using openpyxl, xlwings crashes with the following stack trace:

raceback (most recent call last):
  File "C:\Users\frandeen\os_mp\fill_report.py", line 486, in main
    fill_config_renderings(report_path)
  File "C:\Users\frandeen\os_mp\fill_report.py", line 472, in fill_config_renderings
    anchor_row += add_config_renderings(template_wb, cfg, anchor_row)
  File "C:\Users\frandeen\os_mp\fill_report.py", line 65, in add_config_renderings
    new_image = worksheet.pictures.add(
  File "C:\Users\frandeen\os_mp\venv\lib\site-packages\xlwings\main.py", line 4442, in add
    impl=self.impl.add(
  File "C:\Users\frandeen\os_mp\venv\lib\site-packages\xlwings\_xlwindows.py", line 2262, in add
    xl=self.xl.Parent.Shapes.AddPicture(
  File "C:\Users\frandeen\os_mp\venv\lib\site-packages\xlwings\_xlwindows.py", line 121, in __call__
    v = self.__method(*args, **kwargs)
  File "C:\Users\frandeen\AppData\Local\Temp\gen_py\3.10\00020813-0000-0000-C000-000000000046x0x1x9.py", line 36335, in AddPicture
    ret = self._oleobj_.InvokeTypes(1723, LCID, 1, (9, 0), ((8, 1), (3, 1), (3, 
1), (4, 1), (4, 1), (4, 1), (4, 1)),Filename
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)

Include a minimal code sample to reproduce the issue (and attach a sample workbook if required!)

Start with a workbook that has some hidden sheets. Execute the following code in openpyxl:

import openpyxl
wb = openpyxl.load_workbook('workbook.xlsx')
template = wb['some_template']
new_sheet = wb.copy_worksheet(template)
new_sheet.title = 'add images here'
wb.move_sheet(new_sheet, -3)
wb.save('broken_workbook.xlsx')

Then execute the following using xlwings:

import xlwings as xw
import os
xl_app = xw.App(visible=False)
template_wb: xw.Book = xl_app.books.open('broken_workbook.xlsx')
rendering_worksheet = template_wb.sheets['add images here']
img_path = os.path.join(os.getcwd(),'file.png')
rendering_worksheet.pictures.add(img_path)
template_wb.save('broken_workbook.xlsx')

Note that I can't fully reproduce the issue with the code above - there is something about the template I'm using (which I'm unable to share) that may be part of the root of the problem.

Investigation & workaround.

I believe that this bug originates in openpyxl. However, the workaround I did works in xlwings. Simply un-hiding and re-hiding all of the worksheets seems to fix the problem:

  hidden_sheets = [index for index, sheet in enumerate(template_wb.sheets) if sheet.visible == 0]
  for sheet in hidden_sheets:
      template_wb.sheets[sheet].visible = -1
      template_wb.sheets[sheet].visible = 0

My guess is that somehow openpyxl screws up the sheet indexing. Adding a simple operation like what's above to worksheet.pictures.add() may be a good safety measure to avoid the crash that I'm getting.

[blairfrandeen]

Here is a workbook that will cause the crash described above. Part of what causes this may be having named ranges in the workbook. workbook.xlsx

[fzumstein]

yes, this sounds more like an issue you should open with openpyxl. If you can reproduce this issue when doing the same action manually, it's definitely nothing to do with xlwings.