Issue with saving a workbook

Issue #1170 resolved
Esper Achkar
created an issue

I ran into an the following error on a script I was working on after updating my openpyxl package to version 2.5.14:
"Exception Occured: I/O operation on closed file".
reverting back to the older version (2.5.12) solved this issue.
PS: It was the exact same code I ran in both versions

Comments (9)

  1. Pablo Ospino

    The same thing happened to me. After loading a workbook with images, I tried to save it and these error appeared to me:
    version=2.5.14

    Traceback (most recent call last):
      File "openxlsx/openxlx.py", line 12, in <module>
        wb.save('logo.xlsx')
      File "/home/pablo/.local/lib/python3.6/site-packages/openpyxl/workbook/workbook.py", line 391, in save
        save_workbook(self, filename)
      File "/home/pablo/.local/lib/python3.6/site-packages/openpyxl/writer/excel.py", line 284, in save_workbook
        writer.save(filename)
      File "/home/pablo/.local/lib/python3.6/site-packages/openpyxl/writer/excel.py", line 266, in save
        self.write_data()
      File "/home/pablo/.local/lib/python3.6/site-packages/openpyxl/writer/excel.py", line 85, in write_data
        self._write_images()
      File "/home/pablo/.local/lib/python3.6/site-packages/openpyxl/writer/excel.py", line 122, in _write_images
        self._archive.writestr(img.path[1:], img._data())
      File "/home/pablo/.local/lib/python3.6/site-packages/openpyxl/drawing/image.py", line 64, in _data
        img = _import_image(self.ref)
      File "/home/pablo/.local/lib/python3.6/site-packages/openpyxl/drawing/image.py", line 33, in _import_image
        img = PILImage.open(img)
      File "/home/pablo/.local/lib/python3.6/site-packages/PIL/Image.py", line 2638, in open
        fp.seek(0)
    ValueError: I/O operation on closed file.
    

    Code:

    import openpyxl
    from openpyxl import Workbook, load_workbook
    from openpyxl.drawing.image import Image
    
    wb = load_workbook("openxlsx/file_with_image.xlsx")
    ws = wb.active
    ws['A1'] = 'You should see three logos below'
    img = Image('openxlsx/logo.png')
    ws.add_image(img, 'A1')
    wb.save('logo.xlsx')
    

    I uninstalled the openpyxl library and installed the version 2.5.12 and it works. Same code.

    Please check the latest version.(2.5.14)

  2. CharlieC

    So the problem is related to passing in PIL objects directly? This isn't actually currently explicitly supported which is probably why I've had such trouble understanding the problems. As we don't really need a dedicated image class, just the path or data, it probably makes sense to expect PIL images directly.

    You should be able to workaround by using the openpyxl.drawing.image.Image class directly.

  3. Luke P

    In our use case it's triggered by the fact that a png image (logo of our company) is used inside of a template we use to populate with Excel data, we don't handle any of the Image loading logic directly

  4. Luke P

    This is our implementation of how the code is triggered

        def render(self, data: Iterable) -> bytes:
            template = self.template.value
            file = join(dirname(__file__), "templates", template.file_name)
    
            workbook = load_workbook(file)
            sheets = workbook.sheetnames
            sheet = sheets[template.worksheet_index]
            sheet = workbook[sheet]
    
            for x, row in enumerate(data):
                for y, value in enumerate(self.__col_iterator(row)):
                    cell = sheet.cell(
                        row=template.row_offset + x + 1,
                        column=template.col_offset + y + 1)
    
                    self.set_cell_value(cell, value)
    
            return save_virtual_workbook(workbook)  # ValueError ...
    
  5. Log in to comment