Invalid Excel *.xlsm file when editing a workbook containing an external sheet with comments & formatting
Created originally on Bitbucket by freyer (Philipp Freyer)
As already discussed in openpyxl-users:
Scenario: I need to update information in a lot of Excel worksheets. They are created with Excel 2007. Editing the data works fine but I have problems if someone using these sheets inserts another worksheet into them by copying them from another workbook with Excel by right-clicking on their tab in Excel and selecting the "move or copy" option for copying the worksheet.
I am using the latest openpyxl library from PyPI (2.3.0) and Python 2.7.8 32 bit on Windows 7 (64 bit).
Here a short example script that already shows the problem:
#!python
# coding=utf-8
import openpyxl
update_sheet = u"TESTBOOK_original.xlsm"
wb = openpyxl.load_workbook(update_sheet, keep_vba=True)
wb._external_links = []
wb.worksheets[0]["A1"].comment = Comment("ab", "cd")
wb.save(update_sheet)
The script just opens and saves the file, removing the external links (reason: see previous thread) and including one comment. When I open the resulting file afterwards, I get the following error: Excel found unreadable content in 'TESTBOOK_broken.xlsm'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes. When I click "Yes", Excel repairs the file, giving me the following error log:
#!xml
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error127960_01.xml</logFileName><summary>Errors were detected in file 'C:\Users\pfreyer\Documents\TESTBOOK_bad.xlsm'</summary><removedFeatures summary="Following is a list of removed features:"><removedFeature>Removed Feature: Data validation from /xl/worksheets/sheet5.xml part</removedFeature><removedFeature>Removed Feature: Data validation from /xl/worksheets/sheet6.xml part</removedFeature></removedFeatures><removedRecords summary="Following is a list of removed records:"><removedRecord>Removed Records: Comments from /xl/comments1.xml part (Comments)</removedRecord></removedRecords></recoveryLog>
I will happily provide all the information I can, if you need more information. If you classify this as a bug, I will also create an issue in Bitbucket. What I did not notice before: Not only the comments, but also the data validation that I set are a problem for Excel. I saw in the documentation that it is not possible to read existing data validation, but should openpyxl at least be able to keep it? If not, is there a way to remove existing validation, so that I can just replace it?
Kind regards and thanks so far, Philipp
Attachments: TESTBOOKS_Comments.zipTESTBOOK_good.xlsm