Modifying Pivot Tables with Openpyxl causes crashes on Office 2013
Created originally on Bitbucket by Will Swope
Hello,
While building a spreadsheet management tool, I discovered that opening and saving a spreadsheet containing a pivot table with openpyxl is leading to a bug wherein selecting a specific cell in the pivot table causes Excel 2013 to crash. I was able to replicate the issue on two different workstations with the same configuration (Windows 7 Enterprise, Python 3.7, Openpyxl 2.6.1, MS Excel 2013 v15.0.5135.1000). However, loading the same spreadsheet in the desktop version of Office 365 on a separate PC (Win 10, same python/openpyxl versions) worked fine, which seems to imply that this issue is related to the older version of Excel.
To get a better understanding of what exactly was leading to the crash, I started swapping files, and then XML snippets, between the original and modified spreadsheet archives, and then tested to see if the issue was still present, and narrowed in on the “rowItems” tag of the pivot table XML file:
Original:
<rowItems count="4">
<i>
<x />
</i>
<i>
<x v="1" />
</i>
<i>
<x v="2" />
</i>
<i t="grand">
<x />
</i>
</rowItems>
Modified:
<rowItems count="4">
<i i="0" r="0" t="data" />
<i i="0" r="0" t="data">
<x v="1" />
</i>
<i i="0" r="0" t="data">
<x v="2" />
</i>
<i i="0" r="0" t="grand" />
</rowItems>
In particular, the first “i” tag of the modified file is empty, and lacks the inner “x” tag that the original does. Manually editing the modified version to include the inner “x” tags prevents the crash.
I’ll attach the original and modified spreadsheets I used for debugging, as well as the XML documents in question. Since the python code I used to create the modified spreadsheet is pretty short, I’ll just paste below:
from openpyxl import load_workbook
wb = load_workbook('Original.xlsx')
wb.save('Broken.xlsx')
Thanks for any and all help on this - this project is a fantastic tool and has saved me many hours of work!
Attachments: pivotTable1_-_Working.xmlpivotTable1_-_Broken.xmlBroken.xlsxOriginal.xlsx