I have a simple application that creates a Excel file, a table, and a pivot table based on the table. The table columns are static at 10, and rows vary but a generally between 35,000 and 40,000. In this case, if the row count exceeds 32,895 then the pivot table isn't created correctly and I get the old error saying "We found a problem with some content in [file name]. Do you want to recover..." and the repairs log:
Removed Feature: PivotTable report from /pivotCache/pivotCacheDefinition1.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable.xml part (PivotTable view)
Removed Records: Workbook properties from /xl/workbook.xml part (Workbook).
If I manually keep the rows to 32,895 in the PivotTables.Add definition, it creates the files correctly without the error.
// Create Workbook
XLWorkbook wb = new XLWorkbook();
// Create Worksheet
IXLWorksheet ws = wb.Worksheets.Add("Data");
// Fill table from stored procedure
SqlConnection sqlConnection = new SqlConnection(Properties.Settings.Default.conString);
SqlCommand sqlCommand = new SqlCommand("dbo.cex_DScanCompData", sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add("@dDate", SqlDbType.DateTime).Value = reportDate;
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
// Insert Datatable data
IXLTable table = ws.Cell(1, 1).InsertTable(dataTable, "Data", true);
// Fix OrderTrackingID format in ws
ws.Column(5).CellsUsed().Style.NumberFormat.Format = "#.000000";
// Adjust column widths in ws
ws.Columns().AdjustToContents();
// Pivot Table
// Add new worksheet
IXLWorksheet ps = wb.Worksheets.Add("Report");
// Create pivot table
var totalRows = ws.LastRowUsed().RowNumber() - 5;
var totalColumns = ws.LastColumnUsed().ColumnNumber();
IXLPivotTable pt = ps.PivotTables.Add("PivotTable", ps.Cell(3, 1), ws.Range(1,1,totalRows,totalColumns)); // Max rows 32,895!!
pt.RowLabels.Add("TerminalName").SetCollapsed();
pt.RowLabels.Add("Driver").SetCollapsed();
pt.RowLabels.Add("ConsCompany", "Client").SetCollapsed();
pt.Values.Add("PkgCount").SetSummaryFormula(XLPivotSummary.Sum);
pt.Values.Add("DScan").SetSummaryFormula(XLPivotSummary.Sum);
pt.Values.Add("MScan").SetSummaryFormula(XLPivotSummary.Sum);
pt.Values.Add("NScan").SetSummaryFormula(XLPivotSummary.Sum);
//For testing purposes
IXLWorksheet ws3 = wb.Worksheets.Add("Test");
ws3.Cell("A1").Value = totalRows;
ws3.Cell("A2").Value = totalColumns;
// Save workbook
wb.SaveAs(@"C:\Users\CEsolutions\Documents\Test2.xlsx");
}
}