How to create PivotTable

Aug 22, 2011 at 9:37 AM

Hi

Could you please provided an example of how I could create a PivotTable? And is this possible?

Greatly appreciated! 

Coordinator
Aug 22, 2011 at 3:21 PM

We're working on it (no ETA though).

Aug 22, 2011 at 3:39 PM

Thanks for the quick reply. Will wait in anticipation :) 

Apr 4, 2013 at 3:23 PM
This thread is more than a year old, any update? It looks like the pivot table classes are there, but how do you go about populating it via the API?
Apr 4, 2013 at 5:51 PM
As far as I know, pivot tables are not implemented. Normally I use ClosedXML for everything, but if I need to create a pivot table I use Aspose.Cells for .Net which is a commercial product. Maybe some other users here can comment about using OpenXML directly to create pivot tables.
Apr 17, 2013 at 11:56 PM
I'll be adding a blog on how to achieved this @ www.bluechilli.com but until then I'll copy here what I'm posting:

ClosedXML and Excel - How to add a pivot table

Pivot tables can be 90% done using ClosedXML by using a excel template as a base.

Excel template steps
  1. In excel setup a named table with appropriate header columns and data.
  2. Create a pivot table as desired using that named table as the source.
  3. Ensure in pivot options data saved is unchecked and refresh on file load is checked
  4. Blank table data leaving 1 blank row in the named table range.
In ClosedXML, expand range of table (rows and columns) and insert new data directly into spreadsheet

The missing 10% is any new columns are not shown by default, the user will need to pick the columns.

Done!

ASP.Net C# Example:

using (var wb = new XLWorkbook(HostingEnvironment.MapPath("~/App_Data/My_Template.xlsx")))
{

....

region Networth

IXLWorksheet wsNWData;
wb.Worksheets.TryGetWorksheet("NWData", out wsNWData);

var nwTable = wsNWData.Table("NWTable");
nwTable.InsertRowsBelow(exportData.Networth.Count - 1, true);
if (exportData.Dates.Count > 2) nwTable.InsertColumnsAfter(exportData.Dates.Count - 2, true); //Handle non-default columns

Then using wsNWData.Cell(x,y) set any new column headers and add data rows