File with multiple pivots in a sheet needs recovery if I set pivots to refreshOnLoad
Created originally on Bitbucket by Mark Oakden
openpyxl 3.0.2, Python 3.7.3
Office 365, Version 1911 (Build 12228.20322 Click-to-run), (Microsoft Excel for Office 365 MSO (16.0.12228.20322) 64-bit)
If I open an existing Excel file with two worksheets one of which has two pivot tables defined against a range of data in the other sheet and then use openpyxl to insert data into the data sheet and set the pivots to refresh on file open, opeining the file in Excel afterwards generates an error dialog “We found a problem with some content in $FILE. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.”. This only seems to happen if the pivots in the source Excel file didn’t already have “Refresh data when opening the file” set.
After repair the folowing is displayed “Repaired Records: Workbook properties from /xl/workbook.xml part (Workbook)”
- create a workbook in Excel with two sheets
- insert two pivot tables to one of the sheets, both referencing the same data range in the other sheet (not sure if the same data range is significant or not)
- in the pivot table options “Data” tab, check that“Refresh data when opening the file” is not checked
- save the sheet
- open the file in openpyxl
- using openpyxl, add data to the data sheet
- using openpyxl, set the pivots in the pivots worksheetto refresh on load via setting the
- save the workbook to a different name
- open the workbook in Excel
a script that reproduces this is attached as replicate.py (inline below):-
import openpyxl input_files = ['refresh_off.xlsx', 'refresh_on.xlsx'] data = [['A', 'B', 'C'], [10, 'left', 1], [20, 'right', 2], [30, 'up', 3], [40, 'down', 4], [40, 'left', 5], [30, 'left', 6], [20, 'left', 7], [10, 'right', 8]] print(openpyxl.__version__) for file in input_files: wb = openpyxl.load_workbook(file) dataws = wb['Data'] pivotws = wb['Pivot'] for r, row in enumerate(data): for c, val in enumerate(row): dataws.cell(row=r+1, column=c+1).value = val pivot = pivotws._pivots pivot.cache.refreshOnLoad = True wb.save('output_' + file)
the input files refresh_on.xlsx and refresh_off.xls are identical apart from the “Refresh data when opening the file” property.
The output files are output_.
output_refresh_off.xlsx exhibits the “needs repair” behaviour whilst output_refresh_on.xlsx doesn’t.