Customize Excel Export PivotData C# examples

This article describes how to customize Excel export produced by PivotTableExcelWriter.

Excel writer uses EPPlus library for generating output in XLSX format. By default it accepts Stream and produces Excel file with one worksheet, but output may be customized in the following way:

1. Create Excel package with EPPlus in your code:

var excelPkg = new ExcelPackage();
var workSheet = excelPkg.Workbook.Worksheets.Add("Report");

2. Use PivotTableExcelWriter constructor that accepts worksheet as input:

var pvtExcelWr = new PivotTableExcelWriter(workSheet);

3. Change default starting cell where table is rendered, for example shift table on one row down:

pvtExcelWr.StartCellRow = 2;  //1 by default
pvtExcelWr.StartCellColumn = 1;
pvtExcelWr.Write(pvtTbl);

4. Add any content to Excel worksheet with pivot table data, for example:

// add row with heading
workSheet.Cells[1,1].Value = "Sample Header";

5. Save produced excel:

excelPkg.SaveAs(context.Response.OutputStream);