Bogus workbook generated when adding an image

Issue #462 resolved
Jean-Baptiste Quenot
created an issue

Using version 2.1.4 I could generate an Excel file with an image attached and anchored to a cell using this code:

img = openpyxl.drawing.Image(imagePath)
ws.add_image(img)

img.anchor(ws['F3'], anchortype='oneCell')
img.drawing.left = 25
img.drawing.top = 20

Using version 2.2.2 (latest version in PyPi) this leads to an Excel validity error. Upon workbook repair, Excel mentions it has stripped off the print settings.

If I comment the add_image call, the file is OK (but without the image of course).

For each use case I attached an example file, so you can see for yourself.

Comments (14)

  1. CharlieC

    Thanks for the report. Despite what Excel says the file isn't invalid and I think the error probably has nothing directly to do with the image. It looks like this file was not created by openpyxl. Is that correct?

  2. CharlieC

    The problem is a conflict in the worksheet relations. 2.2 has much better support for existing properties and preserves the relationship with the "the devMode printer settings part" from the original file. This conflicts with the id automatically assigned when you add an image or a comment (there is a similar, older bug related to having a worksheet and a chart) and Excel throws a fit because it cannot resolve the relationship.

    An easy workaround is simply to remove the link: ws.page_setup.id = None. We shouldn't be preserving such ids but managing them actively.

  3. Jean-Baptiste Quenot reporter

    I also get this warning, which may be related:

    /usr/lib/pymodules/python2.7/openpyxl/workbook/names/named_range.py:121: UserWarning: Discarded range with reserved name
      warnings.warn("Discarded range with reserved name")
    /usr/lib/pymodules/python2.7/openpyxl/writer/excel.py:126: UserWarning: Duplicate name: 'xl/drawings/drawing1.xml'
      dw.write())
    /usr/lib/pymodules/python2.7/openpyxl/writer/excel.py:128: UserWarning: Duplicate name: 'xl/drawings/_rels/drawing1.xml.rels'
      dw.write_rels(chart_id, image_id)) # TODO remove this dependency
    /usr/lib/pymodules/python2.7/openpyxl/writer/excel.py:98: UserWarning: Duplicate name: 'xl/media/image1.png'
      archive.writestr(PACKAGE_IMAGES + '/image%d.png' % image_id, buf.getvalue())
    
  4. Jean-Baptiste Quenot reporter

    Yes indeed, just load the input template like this:

    openpyxl.reader.excel.load_workbook(tmpl, keep_vba=True)
    

    Nothing special apart from this you should be able to reproduce with the new test files.

  5. CharlieC

    It's a similar but different bug so it really should be a separate issue. Unfortunately, it's still around in 2.3 but should be easier to fix there (a backport is unlikely). We've historically had trouble dealing with different kinds of relations. This is related to the fact that macros require an image and expect it to be the only one.

    2.3 is stable (contains all the fixes from 2.2.x and more). Most of the major changes affect charting where the API hasn't been finalised. There are some related but minor changes to images and packaging. If you can live with that then you should be able to use it in production (after tests with your own code). Caveat programmer! ;-)

    We hope to be releasing a 2.3 beta soon to allow for the API changes to be tested by real world code.

  6. Log in to comment