Pivot table error based on size of table

[mlivingstonNE]

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.

CSharp code below and created file attached. Test2.xlsx

{ static void Main(string[] args) { String fileName = "DeliveryCompliace " + DateTime.Today.ToString("MMddyyyy") + ".xlsx"; String runTime = DateTime.Now.ToString("MM/dd/yyyy HH:mm"); String reportDate = DateTime.Today.ToLongDateString();

        // 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");
    }

}

[Pankraty]

Wrong project? =) Please, report this to https://github.com/ClosedXML/ClosedXML and be careful with filling the issue template.

[b0bi79]

Create please here https://github.com/ClosedXML/ClosedXML/issues

[mlivingstonNE]

Terribly sorry. I thought I was in the ClosedXML/ClosedXML/issues. My mistake.