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

Calculation field in pivot tables

Jan 30, 2012 at 10:02 AM

I have application which is based on Interop assembly to create pivot table and I create a calculated field using the following code.

pf.Calculation=Excel.XIPivotFieldCalculation.xlPercentageOfRow;

I am rewriting this windows app as web app and started using epplus to generate the excel file on the server side.

But I do not see any option to create xlPercentageOfRow and xlPercentageColumn fields on the pivot table using eppls.

How can I do this using eplus?

-Sudheer

 

 

 

 



Coordinator
Jan 31, 2012 at 6:43 PM

No, that's not implemented in this release.

Apr 9, 2012 at 4:04 AM

JanKallman is this functionality implemented in 3.0.0.2 release.

Apr 9, 2012 at 1:21 PM

Hey skeesara,

 

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