EPPlus /如何从数据透视表中获取数据？还是如何轻松处理数据？ [英] EPPlus / How to get data from pivot table? Or how to manipulate data easily?

问题描述

我在处理报表中的数据上浪费了很多时间。使用数据透视表是一个好主意，但是如何呢？我尝试了一些免费的 PivotTable 类，但它们没有小计。

然后，另一种方法。对于 excel 的报告输出，我使用的是 EPPlus 。它还支持数据透视表。问题是我们的某些客户没有办公室（OpenOffice，MicrosoftOffice等），因此仅创建和保存 xlsx 文件不起作用。我可以尝试使用 EPPlus 的唯一方法是创建 ExcelPackage ，用数据填充工作表，然后创建数据透视表和数据。

我有几个问题；

1）从该数据透视表对象可以访问数据透视表字段和值的输出。 （到目前为止，我还不能。）

2）与上述问题有关... xlsx 文件包含有关数据透视表的数据，或仅包含创建数据透视表的规则（例如表名，sourceRange，rowFields， columnFields，dataFields，聚合选项等）。我对此做了一个小测试。步骤如下：

• 打开一个新的excel文件。


• 插入一些原始数据。


• 使用数据创建数据透视表。


• 更改了一些数据值。 （不刷新数据透视表）


• 保存并关闭文件。


• 重新打开文件。
  

实际上我的猜测是数据透视表将根据新数据进行更新，但我错了。它没有更新。这可能证明 xlsx文件不仅包含数据透视表的规则，还包含其所有值。如果是这样，我希望在不保存文件的情况下访问该数据（并且我不需要任何办公程序）。

3）任何其他方法都值得赞赏。 / p>

预先感谢

解决方案

我绝不是专家EPPlus，但在过去的几个月中一直在使用它，希望可以阐明您的问题。

如果您在EEP中创建全新的xlsx，请向一个工作表，创建一个指向数据/工作表的数据透视表，然后将其保存-数据透视表不包含任何数据。它仅包含在excel中打开文件时PT应该如何切片数据的定义（如您在一个问题中提到的那样）。

当您实际在excel中打开文件并保存它时，excel所做的是复制PT依赖的所有数据并将其放入在数据透视表缓存中。因此，您可以删除包含数据的原始单元格，保存文件，然后在excel中重新打开它（可能必须消除一些错误），并且仍然可以看到包含数据的PT。您甚至可以双击PT中的一个数据单元，excel会将相关数据的部分或全部（取决于您单击的是哪个单元）重新生成到新的工作表中。

是的，由于此数据透视表缓存，您的猜测实际上是错误的。您必须告诉excel在适当的功能区中更新数据源（假设数据仍然存在）以查看新数据。

因此，要访问您可以通过进入PivotTable.WorkSheet对象并从中拉出数据来弄清楚它的位置。您可以在这里创建的扩展方法中看到我的操作方式：

使用EPPLUS创建数据透视表过滤器

另一种选择是提取实际的worksheet.xml xlsx中的文件。 xlsx文件（以及任何其他MS Office。??? x文件）只是重命名的ZIP文件。因此，您可以使用标准的.NET方法从zip中获取xml文件，并使用类似LinqToXml的方法来提取数据。像这样：

  var zip = new ExcelPackage（file）.Package; 
 var recordspart = zip.GetPart（new Uri（ / xl / worksheets / sheet1.xml，UriKind.Relative））; 
 var recordsxml = XDocument.Load（recordspart.GetStream（））; 
  

做所有的XML操作不会很漂亮，但是如果XLSX的最终格式不起作用，可能是您最好的选择。

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

解决方案

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.

这篇关于EPPlus /如何从数据透视表中获取数据？还是如何轻松处理数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！