I need to export lots of data from database table to excel (xls/xlsx) file. It could be easily 10million rows and more.
I need open source solution which does not require Office to be installed (SpreadsheetGear and interop solutions will not work for me).
I am checking two libraries: OpenXML SDK and EPPlus.
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);
}
}
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.
Also, I checked EPPlus soluion with LoadFromCollection method.
It does support IDataReader with LoadFromDataReader but I dont have datareader at that point in code.
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 clearly does not support 10 million rows. Lets stick with 1m rows and lost of columns without out of memory exception.
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));
}
}
}
