Workbook cannot saved twice

Issue #766 wontfix
Jean-Francois Thuong
created an issue

In version 2.3.5, you could do the following

from openpyxl import load_workbook
template="old.xlsx"
path="new.xlsx"
wb = load_workbook(template)
wb.save(path)
wb.save(path)

However, the same code in version 2.4.2 returns the following error message

Traceback (most recent call last):
  File "C:\SVN\Scripts\Rally\test.py", line 47, in <module>
    wb.save(path)
  File "C:\Python27\lib\site-packages\openpyxl\workbook\workbook.py", line 342, in save
    save_workbook(self, filename)
  File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 268, in save_workbook
    writer.save(filename)
  File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 250, in save
    self.write_data()
  File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 81, in write_data
    self._write_worksheets()
  File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 213, in _write_worksheets
    self._write_comment(ws)
  File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 183, in _write_comment
    vml = fromstring(self.workbook.vba_archive.read(ws.legacy_drawing))
AttributeError: 'NoneType' object has no attribute 'read'

A simple workaround is to load again.

wb = load_workbook(template)
#... some code
wb.save(path)
wb = load_workbook(path)
#... some other code
wb.save(path)

Comments (18)

  1. CharlieC

    VML errors only arise if you're trying to keep macros. If this is the case then the example is wrong and loading the file again is the only solution.

  2. CharlieC

    I don't think there's a lot we can do in the code and, as the action fails, an exception is the correction response. But feel free to look at the relevant code and make improvements. VBA is a black box and full support will never be possible. Microsoft has even decided that .xlsm files aren't covered by the OOXML specification.

    The docs can always be improved.

  3. mgsoulak

    Hi, I am getting the same error in python 3.6. works fine first time. 2nd time I get the error.

    If I comment out a call to cell.comment() the error goes away.

    I am creating the file from scratch each time my function is called.

    if os.path.isfile(output_file_str):
        os.remove(output_file_str)
        #...mycode
        #  commentStr = str("{0}:{1},{2}").format(sPtr["rate"], sPtr["modulation"],sPtr["channelWidth"])
        #  c.comment = Comment(commentStr,"") 
    wb.save(output_file_str)
    
    Traceback (most recent call last):
      File "C:\Users\mgsoulak\AppData\Local\Programs\Python\Python36\lib\tkinter\__init__.py", line 1699, in __call__
        return self.func(*args)
      File "C:\Users\mgsoulak\OneDrive - Nokia\JSON Python Project\OpticalFootPrintTool 10-17.py", line 1198, in callBack_genReport
        createWorkbook()
      File "C:\Users\mgsoulak\OneDrive - Nokia\JSON Python Project\OpticalFootPrintTool 10-17.py", line 321, in createWorkbook
        wb.save(output_file_str)
      File "C:\Users\mgsoulak\AppData\Local\Programs\Python\Python36\lib\site-packages\openpyxl\workbook\workbook.py", line 345, in save
        save_workbook(self, filename)
      File "C:\Users\mgsoulak\AppData\Local\Programs\Python\Python36\lib\site-packages\openpyxl\writer\excel.py", line 266, in save_workbook
        writer.save(filename)
      File "C:\Users\mgsoulak\AppData\Local\Programs\Python\Python36\lib\site-packages\openpyxl\writer\excel.py", line 248, in save
        self.write_data()
      File "C:\Users\mgsoulak\AppData\Local\Programs\Python\Python36\lib\site-packages\openpyxl\writer\excel.py", line 81, in write_data
        self._write_worksheets()
      File "C:\Users\mgsoulak\AppData\Local\Programs\Python\Python36\lib\site-packages\openpyxl\writer\excel.py", line 211, in _write_worksheets
        self._write_comment(ws)
      File "C:\Users\mgsoulak\AppData\Local\Programs\Python\Python36\lib\site-packages\openpyxl\writer\excel.py", line 181, in _write_comment
        vml = fromstring(self.workbook.vba_archive.read(ws.legacy_drawing))
    AttributeError: 'NoneType' object has no attribute 'read'
    
  4. yorksranter

    I have just encountered this issue while looping over some data, calling an external API, and saving incrementally so as to always make progress. I see you've wontfixed this, but I think you should reconsider because it also irretrievably corrupts the original file.

    I disbelieve the advice that it is something to do with macros, because removing all macros and saving as a non-macro enabled file does not help.

  5. Robert Robert

    Hi, I encountered the same problem (versions 2.5.0 and 2.5.2) and probably found interesting (strange) pattern.

    This is a problem with saving workbook second time in case this workbook have comments.

    Pattern 1:

    1. I open workbook without comments
    2. I enter comments and save workbook
    3. I enter something else and when saving the error appears

    Workaround.

    1. Open clear workbook
    2. Enter whatever but comments and save it as many times as you like
    3. Enter comments and save workbook. And that works without errors.

    Pattern 2.

    1. Open workbook already with comments
    2. Enter whatever but comments and save as many times as you like
    3. Enter comments and save workbook. And the error appeases.

    And now I don't know how to manege.

    Interesting thing, as well, is that:

    1. When I check workbook with comments using python-magic I get Microsoft Excel 2007+ file type.
    2. When I check workbook without comments using the same method I get Microsoft OOXML file type.
  6. Maurice Mayclair

    For me it is dependent on the xlsx file size, ;

    logging.info('Save unformatted at_trip_day_report')

    wb.save(my_book)

    workbook = load_workbook(my_book)

    workbook.save(my_book)

    ## if the file my_book is large it gives a memory error and stops here, otherwise it works fine

    logging.info('Saved unformatted at_trip_day_report after opening again')

    etc

    etc does formatting etc

  7. petrprac

    I almost lost a huge & important excel file because of this isn't being handled soon enough or maybe it isn't being properly saved second time? I still lost last big update to the file though.

    I don't understand why saving is second time should be any problem.

    My file is .xlsx, has comments, has no vba and I can't save it correctly (second time) even when I use 'keep_vba=True'. The file after that is broken and can't be opened ever again.

    Thank you for otherwise great library, though this is really dangerous imho.

  8. RunDeep

    Same thing is happening to me:

    In excel.py: vml = fromstring(self.workbook.vba_archive.read(ws.legacy_drawing)) --> self.workbook.vba_archive is None

    I ran a little script to create a sheet:

    from pathlib import Path
    
    from openpyxl import Workbook, load_workbook
    
    output_folder = Path(Path.cwd()) / 'output'
    output_file = 'test.xlsx'
    
    std_file_path = output_folder / output_file
    
    wb = Workbook()
    if std_file_path.exists():
        wb = load_workbook(std_file_path)
        ws = wb['Sheet']
    
    else:
        ws = wb.create_sheet('Sheet')
    
    ws.cell(row=1, column=1,
            value='xxx')
    
    wb.save(std_file_path)
    
    ws.cell(row=2, column=1,
            value='yyy')
    
    wb.save(std_file_path)
    
    wb.close()
    

    Ran it once to create the sheet, opened the sheet in Excel, added a comment, and then ran it again to get the error. I never got an error after multiple runs until I added the comment. In my project, I am running various functions using a Tkinter GUI that update the sheet, I open it to look at the results, close and run another update. It used to work most of the time but now it reliably errors. It corrupts the file when the save fails, now I copy the file before saving/writing over it.

  9. Log in to comment