This project has moved. For the latest updates, please go here.

Calculated Field in pivot table

Apr 7, 2012 at 12:01 PM

Hi Everyone,

I am using EPPlus 3.0.0.2 library to export advance excelsheets from Asp.net C#.

Its a very nice and user friendly library.

I am facing a problem since sometime now while generating a pivot table im not able to insert any Calculated Field in the pivot table.

I've read that this functionality is avaliable in EPPlus 3.0.0.2 library.

Would be really glad if someone could help me out with this.

 

Thanks in advance.

Apr 9, 2012 at 12:22 PM

 

Hey guys,

 

You cant do that directly, i found a workaround on another thread.

 

1. Create an excel spreadsheet template containing a worksheet with the data, and a worksheet with the pivottable.

2. Setup the pivottable exactly how i need it, including the calculated field,

3. Load the ExcelPackage with the constructor (Stream newFile, Stream template)

4. Load the necessary data into the worksheet with the data, then save it.

Works like a charm!

Here's a sample

 

//Create a FileInfo object by passsing the source address of the file
FileInfo fInfo = new FileInfo(@"d:\Sample.xlsx");
using (ExcelPackage p = new ExcelPackage(fInfo))// Pass this to the constructor
{
ExcelWorksheet ws = p.Workbook.Worksheets["Sheet1"];//Find your source sheet
//Re draw the table
//Output the excel and your done 
}

Hope it helps you