2

We are using ClosedXML to generate .XLSX spreadsheets because it is small, nimble, and quick. It appears that all the pivot table classes and methods are there in the API, but there is no documentation or examples on how to go about (in the correct sequence) creating pivot tables. Our attempts at guessing have produced files that when opened, give an error about XML corruption, and Excel prompts you to delete the pivot table.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Jay Imerman
  • 4,475
  • 6
  • 40
  • 55

1 Answers1

3

I'm not sure at what version ClosedXML implemented Pivot Tables, but as of at least v0.70 they have been implemented. An example can be found in the ClosedXML Documentation. Below is an C# example I'm using in my own project. 

IXLWorksheet xlPvSheet = xlBook.Worksheets.Add("Pivot");
IXLPivotTable xlPvTable = xlPvSheet.PivotTables.AddNew("PivotTable", xlPvSheet.Cell(1,1), xlForecastSheet.Range(1, 1, r, 11));
xlPvTable.RowLabels.Add("Box Type");
xlPvTable.RowLabels.Add("Box Color");
xlPvTable.ColumnLabels.Add("Request Date");
xlPvTable.Values.Add("Total Boxes");
Drew Chapin
  • 7,779
  • 5
  • 58
  • 84
  • Thanks! Too bad they didn't have it in the timeframe for my project, but I may use it in the future. – Jay Imerman Aug 09 '14 at 11:23
  • I know this is old, but does `xlForecastSheet.Range` include the title of the table? So `(1, 1) until `(r, 11)` is the entire range of the table including the column titles? – Jimenemex Apr 23 '18 at 19:44
  • @Jimenemex, I'm not sure I understand your question. If you mean column headers as opposed to title, then yes the range includes the headers. – Drew Chapin Apr 23 '18 at 21:07
  • @DrewChapin Yes, that's what I was looking for thanks. – Jimenemex Apr 24 '18 at 13:07
  • @Jimenemex, If the index being `1,1` is what threw you off, ClosedXML indexes are 1 based instead of 0 to stay consistent with Excel. You can see in the [source code](https://github.com/ClosedXML/ClosedXML/blob/develop/ClosedXML/Excel/Ranges/XLRange.cs) it will throw an `IndexOutOfRangeException` if you specify an index `<= 0`. – Drew Chapin Apr 24 '18 at 14:10
  • @DrewChapin No, my problem is I'm trying to add a PivotTable based off another table. Excel opens but repairs and removes my PivotTable because of unreadable content. Only happens when I go from `(1, 1, end, 6)`. It'll work for `(1, 1, end - 1, 6)`. The table is unreadable when going from `beginning` to `end` of the table range, but is readable when going from `beginning` to `end - 1`. I thought it was because I was including the column headers, but It's not that. The only differences of my version and the example is I'm adding `RowLabels` only. Also I populate my table from a `DataTable` – Jimenemex Apr 24 '18 at 15:10
  • @Jimenemex, if you can share your code on a new question, I can try to help. – Drew Chapin Apr 24 '18 at 16:33
  • @DrewChapin Only if you have time, [here](https://stackoverflow.com/questions/50007717/creating-pivot-table-using-closedxml) it is. Thanks. – Jimenemex Apr 24 '18 at 17:35