This project has moved and is read-only. For the latest updates, please go here.

PivotTable row and column labels broken by recent change?

Jun 7, 2014 at 11:18 PM
Hi,

I just tried upgrading from 0.71.1 to 0.72.3. It's great to see OpenXml 2.5 support. (I'm using .NET 4.5.)

However, one of my spreadsheets, with a pivot table, is broken, which was working before. When opening the spreadsheet Excel says the pivot table data is corrupted. Diffing the XML, I see it is the index number of the PivotTable RowLabels that has changed.

I think the change made in the following recent revision is wrong, and has broken it. By backing out the change to the "Fields" index in a local copy, it fixes the problem for me.
https://closedxml.codeplex.com/SourceControl/changeset/1571ed8f1d75c98f7e6f0c6e2047d73f5cfe0a95

Hope this helps.
Chris
Jun 18, 2014 at 10:17 PM
Interesting,

Are you opening an existing file with a pivot table with ClosedXML? If so please attach the file to an Issue ticket (remove/replace any data that can't be shared)

If you're creating a file with a pivot table then please post a piece of code that can reproduce the problem.

Thanks
Jun 21, 2014 at 3:21 PM
I'm using code to dynamically create a spreadsheet to export data from a database.

I've taken my code, put in one row of hard-coded sample data, and removed most of the columns to leave a fairly simple reproduction scenario.

This attempts to create a pivot table using the "Category" and "Item" fields in the pivot table rows (they're the third and fourth columns in the source data), but the spreadsheet when opened gives the error as before, and the pivot table shows the date and quantity as the pivot rows (which were the first two columns in the source data).
    /// <summary>
    /// Create OpenXML spreadsheet for export
    /// </summary>
    /// <returns></returns>
    public XLWorkbook GenerateOrderLineSpreadsheet(IEnumerable<OrderLine> orderLines)
    {
      var workbook = new XLWorkbook();
      workbook.SetDocumentProperties(this, "Order lines");

      var sheet = workbook.Worksheets.Add("orderlines");

      int row = 1;

      int column = 1;
      sheet.Cell(row, column++).Value = "Date";
      sheet.Cell(row, column++).Value = "Quantity";
      sheet.Cell(row, column++).Value = "Category";
      sheet.Cell(row, column++).Value = "Item";
      sheet.Cell(row, column++).Value = "Unit price";
      sheet.Cell(row, column++).Value = "Total price";

      // Sample data row
      row++;
      column = 1;
      sheet.Cell(row, column++).Value = new DateTime(2014, 6, 21);
      sheet.Cell(row, column++).Value = 1;
      sheet.Cell(row, column++).Value = "Widgets";
      sheet.Cell(row, column++).Value = "Pro widget";
      sheet.Cell(row, column++).Value = "1.23";
      sheet.Cell(row, column++).Value = "1.23";

      var dataRange = sheet.RangeUsed();

      // Add a new sheet for our pivot table
      var pivotTableSheet = workbook.Worksheets.Add("PivotTable");

      // Create the pivot table, using the data from the "PastrySalesData" table
      var pt = pivotTableSheet.PivotTables.AddNew("PivotTable", pivotTableSheet.Cell(1, 1), dataRange);

      // The rows in our pivot table will be the names of the categories
      pt.RowLabels.Add("Category");
      pt.RowLabels.Add("Item");

      pt.Values.Add("Total price");

      return workbook;
    }
Jun 24, 2014 at 10:29 PM
Fixed
Marked as answer by chrisdenning on 6/24/2014 at 2:54 PM
Jun 24, 2014 at 10:55 PM
Great, thanks, I can confirm the latest NuGet (0.73.0) works fine in my system.