将大量数据写入Excel C＃ [英] Write large amount of data to excel c#
问题描述
我需要将许多数据从数据库表导出到excel（xls / xlsx）文件。
可能很容易有一千万行甚至更多。
I need to export lots of data from database table to excel (xls/xlsx) file. It could be easily 10million rows and more.
我需要不需要安装Office的开源解决方案（SpreadsheetGear和interop解决方案对我不起作用）。
I need open source solution which does not require Office to be installed (SpreadsheetGear and interop solutions will not work for me).
我正在检查两个库：OpenXML SDK和EPPlus。
I am checking two libraries: OpenXML SDK and EPPlus.
对于OpenXML SDK，我发现了这种方法：
For OpenXML SDK I found this method:
private static void Write(string fileName, int numRows, int numCols)
{
using (var spreadsheetDocument = SpreadsheetDocument.Open(fileName, true))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);
WorksheetPart replacementPart = workbookPart.AddNewPart<WorksheetPart>();
string replacementPartId = workbookPart.GetIdOfPart(replacementPart);
using (OpenXmlReader reader = OpenXmlReader.Create(worksheetPart))
{
using (OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart))
{
Row row = new Row();
Cell cell = new Cell();
//CellFormula cellFormula = new CellFormula();
//cellFormula.CalculateCell = true;
//cellFormula.Text = "RAND()";
//cell.Append(cellFormula);
CellValue cellValue = new CellValue("val val");
cell.Append(cellValue);
while (reader.Read())
{
if (reader.ElementType == typeof(SheetData))
{
if (reader.IsEndElement)
continue;
writer.WriteStartElement(new SheetData());
for (int rowNumber = 0; rowNumber < numRows; rowNumber++)
{
writer.WriteStartElement(row);
for (int col = 0; col < numCols; col++)
{
writer.WriteElement(cell);
}
writer.WriteEndElement();
}
writer.WriteEndElement();
}
else
{
if (reader.IsStartElement)
{
writer.WriteStartElement(reader);
}
else if (reader.IsEndElement)
{
writer.WriteEndElement();
}
}
}
}
}
Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().First(s => s.Id.Value.Equals(origninalSheetId));
sheet.Id.Value = replacementPartId;
workbookPart.DeletePart(worksheetPart);
}
}
但是它抛出内存异常。
我需要面向批处理的方法，并且能够将数据追加到excel文档的末尾。
不幸的是，我找不到如何使用 OpenXML SDK 追加行。
But it throws Out of memory exception.
I need batch oriented approach and to be able to append data to the end of excel document.
Unfortunately I did not find how to append rows with OpenXML SDK.
此外，我检查了<带有 LoadFromCollection 的href = http://techbrij.com/export-excel-xls-xlsx-asp-net-npoi-epplus rel = nofollow> EPPlus解决方案 code>方法。
它确实支持 IDataReader 和 LoadFromDataReader ，但是我那时在代码中没有数据读取器。
Also, I checked EPPlus soluion with LoadFromCollection method.
It does support IDataReader with LoadFromDataReader but I dont have datareader at that point in code.
问题：是否有一种方法可以通过写程序将数据附加到现有的工作表xls / xlsx文件中？就像 OpenXML SDK 中的 OpenXMLWrite r。
The question: is there a way to append data to existing sheet xls/xlsx file with kind of writer? Like OpenXMLWriter in OpenXML SDK.
UPD。 Excel显然不支持1000万行。让我们坚持一百万行，并且列丢失而没有内存不足异常。
UPD. Excel clearly does not support 10 million rows. Lets stick with 1m rows and lost of columns without out of memory exception.
UPD。添加了EPPlus示例。在6分钟内导出20万行，并占用多达1GB的RAM。
UPD. Added EPPlus sample. 200k rows exports in 6 minutes and takes up to 1GB of RAM.
private const string TempFile = @"C:\Users\vnechyp\Desktop\temp.xlsx";
private static void EPPlusExport()
{
var random = new Random();
var dt = new System.Data.DataTable();
for (int i = 0; i < 15; i++)
{
dt.Columns.Add($"column_{i}");
}
var values = Enumerable.Range(0, 15).Select(val => random.Next().ToString()).ToArray();
for (int i = 0; i < 10000; i++)
{
dt.Rows.Add(values);
}
using (ExcelPackage excelPackage = new ExcelPackage())
{
var workSheet = excelPackage.Workbook.Worksheets.Add("sheet");
workSheet.Cells[1, 1].LoadFromDataTable(dt, true);
excelPackage.SaveAs(new FileInfo(TempFile));
}
for (int i = 1; i < 50; i++)
{
Console.WriteLine($"Iteration: {i}");
var updateRow = i*10000;
Console.WriteLine($"Rows: {updateRow}");
FileInfo existingFile = new FileInfo(TempFile);
using (ExcelPackage excelPackage = new ExcelPackage(existingFile))
{
// get the first worksheet in the workbook
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[1];
worksheet.Cells[updateRow, 1].LoadFromDataTable(dt, true);
excelPackage.SaveAs(new FileInfo(TempFile));
}
}
}
推荐答案
+-----------------+-----------+--------------+---------------------+
| | Max. Rows | Max. Columns | Max. Cols by letter |
+-----------------+-----------+--------------+---------------------+
| Excel 365* | 1,048,576 | 16,384 | XFD |
| Excel 2013 | 1,048,576 | 16,384 | XFD |
| Excel 2010 | 1,048,576 | 16,384 | XFD |
| Excel 2007 | 1,048,576 | 16,384 | XFD |
| Excel 2003 | 65,536 | 256 | IV |
| Excel 2002 (XP) | 65,536 | 256 | IV |
| Excel 2000 | 65,536 | 256 | IV |
| Excel 97 | 65,536 | 256 | IV |
| Excel 95 | 16,384 | 256 | IV |
| Excel 5 | 16,384 | 256 | IV |
+-----------------+-----------+--------------+---------------------+
您可以将csv文件用于1000万行
You can use csv file for 10million rows
这篇关于将大量数据写入Excel C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
