We are investigating using EPPlus in our product but coming across this issue accessing PivotTables when the "Save source data with file" checkbox is off:
This is equivalent to:
pivotTable.CacheDefinition.SaveData = false;
Here is a repro:
static void pivotFailsWhenSaveDataFalse()
{
var filePath = $@"C:\Temp\pivottest-{DateTime.Now:HH-mm-ss}.xlsx";
using (var pkg = new ExcelPackage())
{
var sheet1 = pkg.Workbook.Worksheets.Add("Sheet1");
sheet1.Cells["A1"].LoadFromText("""
Column1,Column2,Column3
1,2,3
4,5,6
7,8,9
""", new ExcelTextFormat(), OfficeOpenXml.Table.TableStyles.Medium1, FirstRowIsHeader: true);
var table1 = sheet1.Tables[0];
// add pivot table
var sheet2 = pkg.Workbook.Worksheets.Add("Sheet2");
var pivotTable = sheet2.PivotTables.Add(sheet2.Cells["A1"], table1, "PivotTable1");
pivotTable.RowFields.Add(pivotTable.Fields[0]);
pivotTable.ColumnFields.Add(pivotTable.Fields[1]);
pivotTable.DataFields.Add(pivotTable.Fields[2]);
// 👇 Equivalent to "Save source data with file" in PivotTable Options
pivotTable.CacheDefinition.SaveData = false;
pkg.SaveAs(filePath);
}
// re-open
using (var pkg = new ExcelPackage(filePath))
{
var sheet1 = pkg.Workbook.Worksheets.First();
// 🚨fails
var pivotTable = pkg.Workbook.Worksheets[1].PivotTables[0];
/*
System.NullReferenceException: Object reference not set to an instance of an object.
at OfficeOpenXml.Packaging.ZipPackage.PartExists(Uri partUri)
at EPPlusTest.Table.PivotTable.PivotTableCacheRecords..ctor(PivotTableCacheInternal cache)
at OfficeOpenXml.Table.PivotTable.PivotTableCacheInternal.LoadFields()
at OfficeOpenXml.Table.PivotTable.ExcelPivotTable.LoadFields()
at OfficeOpenXml.Table.PivotTable.ExcelPivotTable..ctor(ZipPackageRelationship rel, ExcelWorksheet sheet)
at OfficeOpenXml.Table.PivotTable.ExcelPivotTableCollection..ctor(ExcelWorksheet ws)
at OfficeOpenXml.ExcelWorksheet.get_PivotTables()
*/
}
}
EPPlus usage
Noncommercial use
Environment
Windows
Epplus version
7.2.1
Spreadsheet application
Excel
Description
We are investigating using EPPlus in our product but coming across this issue accessing PivotTables when the "Save source data with file" checkbox is off:
This is equivalent to:
Here is a repro: