EPPlus / How to get data from pivot table? Or how to manipulate data easily?

Question

I am wasting very much time on manipulating data in reports. Using pivot table is a good idea but how? I tried some free PivotTable classes but they were lacking subtotals.

Then, another approach. For excel output of reports I am using EPPlus. It also supports pivottable. The problem is some of our customers do not have office(OpenOffice, MicrosoftOffice etc.), so just creating and saving an xlsx file does not work. The only thing I can try with EPPlus is creating an ExcelPackage, filling a worksheet with data, and then creating a PivotTable with data.

I have several questions;

1) From that PivotTable object can I access the output of PivotTable fields and values. (Up to now I could not).

2) Related to the above question... Does an xlsx file contains data about the PivotTables or just the rules of creating PivotTable(Like name of table, sourceRange, rowFields, columnFields, dataFields, aggregate options etc). I have made a small test about this. Steps as following:

• Opened a new excel file.
• Inserted some raw data.
• Created pivot table with the data.
• Changed some values of data. (without refreshing pivot table)
• Saved and closed the file.
• Opened the file back.

In fact my guess was "pivot table would update according to new data", but I was wrong. It did not update. This may be a proof for "xlsx file contains not only rules for a pivot table but also all the values of it". If this is so I have a hope to access that data without saving the file (and I do not need any office programs).

3) Any other approach appreciated.

Thanks in advance

Answer 1

I am by no means an expert on EPPlus but have been working with it for the past few months and can hopefully shed some light on your questions.

If you create a brand new xlsx in EEP, add data to a worksheet, create a pivot table pointed at the data/worksheet, and save it - then the PivotTable does NOT contain any data. It merely contains the definition of the how the PT should slice the data when the file is opened in excel (as you mentioned in one of your questions).

When you actually open the file in excel and SAVE IT what excel does is copies all of the data that the PT relies on and puts it in the pivot table cache. This is why you can then delete the original cells that contained the data, save the file, and then reopen it in excel (might have to dismiss some errors), and still see the PT with data. You can even double click on one of the data cells in the PT and excel will regenerate some or all (depending on which cell you clicked) of the associated data into a new sheet.

Yes, your guess was in fact wrong because of this pivot table cache. You have to tell excel to update the data source in the proper Ribbon (assuming the data is still there) to see the new data show up.

So, to access the data you can figure out where it sits by going into the PivotTable.WorkSheet object and pulling the data out from that. You can see how I did it in the extension method i created here:

Create Pivot Table Filters With EPPLUS

Another option would be to extract the actual worksheet.xml file from the xlsx. An xlsx file (and any other MS Office .???x files) are just ZIP files renamed. So you can use the standard .NET methods to get the xml files out of the zip and use something like LinqToXml to extract the data. So something like this:

var zip = new ExcelPackage(file).Package;
var recordspart = zip.GetPart(new Uri("/xl/worksheets/sheet1.xml", UriKind.Relative));
var recordsxml = XDocument.Load(recordspart.GetStream());

It wont be pretty doing all the XML manipulation but if a final format of XLSX will not work it may be your best option.