diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 9d40111..f22eec4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -231,8 +231,8 @@ Boolean IsEmpty(); Boolean IsEmpty(Boolean includeFormats); - //IXLPivotTable CreatePivotTable(IXLCell targetCell); - //IXLPivotTable CreatePivotTable(IXLCell targetCell, String name); + IXLPivotTable CreatePivotTable(IXLCell targetCell); + IXLPivotTable CreatePivotTable(IXLCell targetCell, String name); //IXLChart CreateChart(Int32 firstRow, Int32 firstColumn, Int32 lastRow, Int32 lastColumn); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index dcfd06e..14b6c0e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -1559,18 +1559,18 @@ // return chart; //} - //IXLPivotTable IXLRangeBase.CreatePivotTable(IXLCell targetCell) - //{ - // return CreatePivotTable(targetCell); - //} + IXLPivotTable IXLRangeBase.CreatePivotTable(IXLCell targetCell) + { + return CreatePivotTable(targetCell); + } public XLPivotTable CreatePivotTable(IXLCell targetCell) { return CreatePivotTable(targetCell, Guid.NewGuid().ToString()); } - //IXLPivotTable IXLRangeBase.CreatePivotTable(IXLCell targetCell, String name) - //{ - // return CreatePivotTable(targetCell, name); - //} + IXLPivotTable IXLRangeBase.CreatePivotTable(IXLCell targetCell, String name) + { + return CreatePivotTable(targetCell, name); + } public XLPivotTable CreatePivotTable(IXLCell targetCell, String name) { return (XLPivotTable)Worksheet.PivotTables.AddNew(name, targetCell, AsRange()); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index c621695..58dfcaa 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -177,10 +177,10 @@ GenerateWorksheetPartContent(worksheetPart, worksheet, context); - //if (worksheet.PivotTables.Any()) - //{ - // GeneratePivotTables(workbookPart, worksheetPart, worksheet, context); - //} + if (worksheet.PivotTables.Any()) + { + GeneratePivotTables(workbookPart, worksheetPart, worksheet, context); + } diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index c206af6..c5f7ea1 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -13,9 +13,70 @@ static void Main(string[] args) { var wb = new XLWorkbook(); - var ws = wb.Worksheets.Add("Sheet1"); + var pts = new PivotTableScenarios(); + + pts.RunAll(wb); wb.SaveAs(@"Sandbox.xlsx"); } } + + class PivotTableScenarios + { + public void RunAll(XLWorkbook wb) + { + Add_Row_Labels_and_Sum(wb); + //Add_category_on_row_and_SubCategory_on_column(wb); //not working + } + + private void Add_Row_Labels_and_Sum(XLWorkbook wb) + { + var ws = wb.Worksheets.Add("Add_Row_Labels_and_Sum"); + + ws.Cell("A1").Value = "Category"; + ws.Cell("A2").Value = "A"; + ws.Cell("A3").Value = "B"; + ws.Cell("A4").Value = "B"; + + ws.Cell("B1").Value = "SubCategory"; + ws.Cell("B2").Value = "X"; + ws.Cell("B3").Value = "Y"; + ws.Cell("B4").Value = "Z"; + + ws.Cell("C1").Value = "Number"; + ws.Cell("C2").Value = 100; + ws.Cell("C3").Value = 150; + ws.Cell("C4").Value = 75; + + var pivotTable = ws.Range("A1:C4").CreatePivotTable(ws.Cell("E1")); + pivotTable.RowLabels.Add("Category"); + pivotTable.RowLabels.Add("SubCategory"); + pivotTable.Values.Add("Number").SetSummaryFormula(XLPivotSummary.Sum); + } + + private void Add_category_on_row_and_SubCategory_on_column(XLWorkbook wb) + { + var ws = wb.Worksheets.Add("cat_on_row_SubCat_on_col"); + + ws.Cell("A1").Value = "Category"; + ws.Cell("A2").Value = "A"; + ws.Cell("A3").Value = "B"; + ws.Cell("A4").Value = "B" +; + ws.Cell("B1").Value = "SubCategory"; + ws.Cell("B2").Value = "X"; + ws.Cell("B3").Value = "Y"; + ws.Cell("B4").Value = "Z"; + + ws.Cell("C1").Value = "Number"; + ws.Cell("C2").Value = 100; + ws.Cell("C3").Value = 150; + ws.Cell("C4").Value = 75; + + var pivotTable = ws.Range("A1:C4").CreatePivotTable(ws.Cell("E1")); + pivotTable.RowLabels.Add("Category"); + pivotTable.ColumnLabels.Add("SubCategory"); + pivotTable.Values.Add("Number").SetSummaryFormula(XLPivotSummary.Sum); + } + } }