Invalid Excel *.xlsm file when editing a workbook containing an external sheet with comments & formatting
*Created originally on Bitbucket by [freyer (Philipp Freyer)](https://bitbucket.org/%7B59d25356-56dc-4f19-b1fe-f7909b730530%7D/)* As already discussed in [openpyxl-users](https://groups.google.com/forum/#!topic/openpyxl-users/tgasZek-qSs): 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.zip](/uploads/60037285cd7f9e06b022b4646a740296/TESTBOOKS_Comments.zip)[TESTBOOK_good.xlsm](/uploads/3452c7fdcff7758b2f5bffd044603514/TESTBOOK_good.xlsm)
issue