Pivot Tables - DON-PHAM/EPPlus GitHub Wiki

EPPlus supports both creating new and updating existing pivot tables in a workbook. Pivot tables are created and altered via the PivotTables collection in the ExcelWorksheet class. Here is an example of how a pivot table can be added to a workbook:

var wsPivot = pck.Workbook.Worksheets.Add("PivotSimple");
var pivotTable1 = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], dataRange, "PerCountry");

pivotTable1.RowFields.Add(pivotTable1.Fields["Country"]);
var dataField = pivotTable1.DataFields.Add(pivotTable1.Fields["OrderValue"]);
dataField.Format="#,##0";
pivotTable1.DataOnRows = true;

In this example a pivot table will be created starting from cell A1. One row field and one data field is added. You can also add column fields and page fields.

When you have created a pivot table you can easily create a pivot chart on the pivot table:

var chart = wsPivot.Drawings.AddPieChart("PivotChart", ePieChartType.PieExploded3D, pivotTable1);
chart.SetPosition(1, 0, 4, 0);
chart.SetSize(800, 600);
chart.Legend.Remove();
chart.Series[0].DataLabel.ShowCategory = true;
chart.Series[0].DataLabel.Position = eLabelPosition.OutEnd;
chart.StyleManager.SetChartStyle(ePresetChartStyle.Pie3dChartStyle6);

Here we create a 3D exploded pie chart on the data, setting the style to preset style 6. Pivot charts are added as normal charts via the AddChart method in the Drawings Collection as described here. The pivot table is added as argument three of this method.

Images/Pivottable.PNG

See Sample 18-.NET Framework or Sample 18-.NET Core

Restrictions of pivot tables sources.

EPPlus only supports updating worksheet internal data sources like ranges or tables.
EPPlus do not update connections to external workbooks, data models or connections.