Adding multiple PivotTable worksheets to an existing xlsx workbook can cause file corruption

[shathaway]

Adding multiple PivotTable worksheets to an existing xlsx workbook can cause file corruption. The workaround is to ensure that the target workbook has no PivotTable worksheets. Then add all PivotTable worksheets in a single Open-ExcelPackage instance.

Workaround for issue 1185

$excelPkg = Open-ExcelPackage $PkgPath Add-PivotTable -PivotTableName ‘PivotTable-P1’ Add-PivotTable -PivotTableName ‘PivotTable-P2’ Add-PivotTable -PivotTableName ‘PivotTable-P3’ Close-ExcelPackage $excelPkg

This workaround is OK if you do not close and reopen the package on the same workbook between invocations of Add-PivotTable. The current functionality requires that all PivotTable/Charts in a workbook are created in a single session. After the xlsx session has been closed, no further Add-PivotTable can be applied to the workbook without causing corruption. Copying a Pivot/Chart worksheet between workbooks can also cause excel file corruption.

[shathaway]

I have a need to group multi-level labels on the category axis and apply data labels to bar charts. This requires opening the .xlsx file as a -ComObject Excel.Application. I have not tried this edit while an Open-ExcelPackage session is active.

Note: Visible = $True eliminates some race conditions when editing workbooks.

$ex = New-Object -ComObject Excel.Application $ex.Visible = $true $wb = $ex.Workbooks.Open(‘Fullpath-to-workbook.xlsx’) # Open the workbook $ws = $wb.Worksheets[‘PivotTable-P1’] $pc = $ws.ChartObjects(‘ChartPivotTable-P1’) $pc.Chart.ApplyDataLabels(2) $pc.Chart.Axes(1).TickLabels.MultiLevel = True $wb.Save() $wb.Close() $ex.Quit()

[dfinke]

Thanks for the update. My first guess is the EPPlus library is causing the issue. I have not looked at issues it may have with this.