Adding a calculated field to a PivotTable

Sep 14, 2011 at 9:31 PM

I can't find anything in the documentation about adding a calculated field to the PivotTable. I was wondering if there is anyway to do this?

Sep 14, 2011 at 11:16 PM

Nevermind, i figured out a workaround.

 

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!

Sep 15, 2011 at 1:57 AM
Edited Sep 15, 2011 at 2:22 AM

Nevermind, that solution doesn't work. In fact, you can't use LoadFromDataTable on a template, it doesn't overwrite the data that's already in there.

 

Edit: Wow, sorry for all the posts. I figured this out. All you need to do after loading the new data into the template is change the PivotTable.CacheDefinition.SourceRange to the data worksheet's Dimension. Thank god this is open source and i could actually figure that out by backtracking through the code. P.S. Keep up the great work on this project.

Apr 9, 2012 at 10:41 AM

Hi Makolyte,

 

I want to implement the same functionality in one of my project.

Can you please help me out with the method how you managed to accomplish this?

 

Thanks in advance.

Apr 9, 2012 at 12:11 PM

Hey Makolyte,

 

Just googled it a bit more and finally got it.

But thanks for your this thread, wouldnt even had known if you would not had posted this.

 

Thanks alot.