使用EPPlus加载大量Excel数据 [英] Load large amount of excel data with EPPlus
问题描述
我有一个基本的winforms应用程序，用户可以上传excel文件（.xlsx），并且我想读取该文件的内容，因此我使用的是EPPlus。
<问题是，我正在尝试加载一个非常大的excel文件的内容，它具有7个选项卡，其中一个选项卡具有超过200k的行，另一个选项卡具有70k的行。其他5个总数大约为5万。
这些文件也只会继续变大。（最终目标）由于我要导入数据，因此请读取数据，并根据我拥有的规则/数据，我需要将数据写回到excel文件并导出。
我在使用EPPlus进行加载时遇到问题数据。
这是代码
var file = new FileInfo（filePath）;
using（var package = new ExcelPackage（file））
{
try
{
//在文件中获取工作簿
ExcelWorkbook workBook = package.Workbook; //在此处停留约2分钟
if（workBook！= null）
{
if（workBook.Worksheets.Count> 0）
{
//获取第一个工作表
ExcelWorksheet currentWorksheet = workBook.Worksheets.First（）;
//获取currentWorksheet，但不进行任何评估...
}
}
}
catch（例外）
{
前掷；
}
}
该代码挂在package.workbook行上大约2分钟。然后，它进入如果获取currentWorkSheet的位置，并且如果我在手表中查看该变量的内容，则由于显示以下内容，因此没有真正加载：
功能评估被禁用，因为先前的功能评估超时。您必须继续执行才能重新启用功能评估。
我看着这个链接，它显示了加载大文件的唯一问题是从上到下，从左到右加载，并且它们说超过5,000个。我有更多的办法，所以我只是想知道EPPlus是否可以实现？
此外，我已经做了一些Google搜索，大多数问题都可以做到。 t在服务器上可以在本地打开大型excel文件...
我也已经开始研究Open XML SDK，这似乎是更好的性能选择，但是
这些听起来像是很大的数据集，所以您可能需要阅读以下内容：
基本上，您可以使用更大的数据集来用尽RAM。但是，增加行大小的不仅是行数，还包括每个单元格的列和内容。字符串通常比数字占用更多的空间，因此有时很难预测EPP何时开始出现内存问题。谣言说它是最新版本的EPP更好，但我自己还没有测试过。
似乎您是根据自己的评论来运行的，虽然不错，但保留了内存限制心里。我同意您的意见-在Open XML中进行操作并非易事。
I have a basic winforms app that a user can upload an excel file (.xlsx) and I want to read the contents of this file so I am using EPPlus.
The problem is, I am trying to load the contents of a very large excel file, it has 7 tabs, with one tab haveing more then 200k rows, and another tab with 70k. The other 5 total to about 50k.
These file will also only continue to get bigger.(The end goal) Since I want to import the data, read the data, and depending on the rules/data I have, I need to write data back on the excel file and export it.
I am having issues though with EPPlus in achieving the loading of the data.
here is code
var file = new FileInfo(filePath);
using (var package = new ExcelPackage(file))
{
try
{
// Get the work book in the file
ExcelWorkbook workBook = package.Workbook; //Hangs here for about 2 mins
if (workBook != null)
{
if (workBook.Worksheets.Count > 0)
{
// Get the first worksheet
ExcelWorksheet currentWorksheet = workBook.Worksheets.First();
// gets the currentWorksheet but doesn't evaluate anything...
}
}
}
catch (Exception ex)
{
throw ex;
}
}
The code hangs on the package.workbook line for roughly 2 minutes. Then it gets down into the if where it gets the currentWorkSheet, and if I look at the contents of that variable in my watch, nothing is really loaded since it displays:
Function evaluation disabled because a previous function evaluation timed out. You must continue execution to reenable function evaluation.
I looked at this link from EPPlus and it shows the only issue with loading large files is load from top to bottom and left to right, and they are saying more than 5,000. I have way more than though so I am just wondering if this could even be possible with EPPlus?
Additionally, I have did some google searching and most issues are they can't open the large excel files on server where they could locally...
I have also started looking at Open XML SDK and it seems to be better performance wise, but also much more difficult to use code wise.
Those sounds like pretty big datasets so you might want to read this:
EPPlus Large Dataset Issue with Out of Memory Exception
Basically, you can run out of RAM with "larger" dataset. But its not just the number of rows that adds to the size but also the columns AND the content of each cell. Strings will generally take up a lot more room than numbers so it is sometime difficult to predict when EPP will start to have memory issues. Rumor has it the newest version of EPP is better but I havent tested it myself.
Seems like you got it working based on your comments and thats good but keep the memory limitations in mind. I aggree with you - doing it in Open XML would not be a simple exercise.
这篇关于使用EPPlus加载大量Excel数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
