This project has moved. For the latest updates, please go here.

Code examples on creating Pivot Table?

Apr 2, 2013 at 8:02 PM
I found some threads that end with "...and we will implement it" - but they are old. Any clues or news? I see the classes and methods there to create Pivot Tables, but my attempts at unravelling the steps have ended up with workbooks that open up in Excel to the friendly error "we will delete the pivot table for you if you wish because it doesn't work." I don't see any way of specifying the row and column source other than by string name, but it obviously doesn't work.
Editor
Apr 18, 2013 at 4:30 AM
I'm using the latest from NuGet, and PivotTables do seem to work, though it's definitely possible to create one that Excel thinks is invalid. I was running into the same issue (Excel offering to delete the PivotTable on startup) when I just used the PivotTables.AddNew() method. However, if I then set at least one RowLabel and one Value, it does work.

Here's what I'm doing:
var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("Report");

var source = sheet.Cell(1, 1).InsertTable(Items, true);

var pt = sheet.PivotTables.AddNew("PivotTable", sheet.Cell(1, 5), sheet.Range("A1", "C66"));

pt.RowLabels.Add("Division");
pt.Values.Add("Number");
Where "Items" is an IEnumerable of a class of report data stuff I'm trying to make the table from. Note the manual Range in AddNew() - I started by using source.Range, but that didn't work because it didn't include the table header row. Anyway, this code results in a file with a functional PivotTable; if I leave out either of the last two lines I get an error when I open the file.
Apr 22, 2013 at 4:31 PM
Please add a full coding sample to the documentation section. Thanks.
Editor
May 7, 2013 at 8:11 PM
I'd like to, but I can't seem to contribute to the wiki.
Editor
May 8, 2013 at 2:59 AM
Okay, a pivot table code example has been added to the documentation: https://closedxml.codeplex.com/wikipage?title=Pivot%20Table%20example
May 10, 2013 at 5:14 PM
Thank you. It's a very good example. I wish I had know that ClosedXML could be used to create Pivot tables, because I ended up using Aspose.Cells to do mine. It's a pretty good product, but I still find the API from ClosedXML simpler and closer to the way I think of Excel files. Thanks again.