I need to export a large amount of data to Excel. The data originates from a list of objects. The data varies in size, but it could be up to 7000 columns, and 20,000 rows.
I am using WPF in Visual Studio 2017 on a laptop that has 24 gigs of ram.
The below code generates a system out of memory exception. how can I alter the code below to handle the large data export to excel?
public static void LargeExportTest(List<ExcelData> arrExport, string sDefaultPath)
{
string filename = sDefaultPath + "\\" + Path.GetRandomFileName() + ".xlsx";
UInt32 sheetId = 1;
try
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
//this list of attributes will be used when writing a start element
List<OpenXmlAttribute> attributes;
OpenXmlWriter writer;
document.AddWorkbookPart();
WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart<WorksheetPart>();
writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.Worksheet());
writer.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
int batchCount = 0;
for (int rowNum = 0; rowNum <= arrExport.Count-1; ++rowNum)
{
int u = Convert.ToInt32(rowNum.ToString());
batchCount++;
//create a new list of attributes
attributes = new List<OpenXmlAttribute>();
// add the row index attribute to the list
attributes.Add(new OpenXmlAttribute("r", null, (rowNum + 1).ToString()));
//write the row start element with the row index attribute
writer.WriteStartElement(new Row(), attributes);
var MaxCol = arrExport[u].ColumnData.Max(x => x.ColumnNumber);
attributes = new List<OpenXmlAttribute>();
// add data type attribute - in this case inline string (you might want to look at the shared strings table)
attributes.Add(new OpenXmlAttribute("t", null, "str"));
//add the cell reference attribute
attributes.Add(new OpenXmlAttribute("r", "", string.Format(arrExport[u].Name)));
//write the cell start element with the type and reference attributes
writer.WriteStartElement(new Cell(), attributes);
//write the cell value
writer.WriteElement(new CellValue(string.Format(arrExport[u].Name)));
// write the end cell element
writer.WriteEndElement();
foreach (var col in arrExport[u].ColumnData)
{
//reset the list of attributes
attributes = new List<OpenXmlAttribute>();
// add data type attribute - in this case inline string (you might want to look at the shared strings table)
attributes.Add(new OpenXmlAttribute("t", null, "str"));
//add the cell reference attribute
attributes.Add(new OpenXmlAttribute("r", "", string.Format("{0}{1}", GetColumnName(col.ColumnNumber + 1), (rowNum + 1))));
//write the cell start element with the type and reference attributes
writer.WriteStartElement(new Cell(), attributes);
//write the cell value
writer.WriteElement(new CellValue(col.ColumnData.ToString()));
// write the end cell element
writer.WriteEndElement();
}
// write the end row element
writer.WriteEndElement();
if(batchCount > 150)
{
// write the end SheetData element
writer.WriteEndElement();
// write the end Worksheet element
writer.WriteEndElement();
// writer.Close();
//writer.WriteElement(new Sheet()
//{
// Name = "Large Sheet" + sheetId.ToString(),
// SheetId = sheetId,
// Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
//});
sheetId++;
//////////////////////////////////////
writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.Worksheet());
writer.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
batchCount = 0;
}
}
// write the end SheetData element
writer.WriteEndElement();
// write the end Worksheet element
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(document.WorkbookPart);
writer.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.Workbook());
writer.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
writer.WriteElement(new Sheet()
{
Name = "Large Sheet" + sheetId.ToString(),
SheetId = sheetId,
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
// End Sheets
writer.WriteEndElement();
// End Workbook
writer.WriteEndElement();
writer.Close();
document.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
I expect an Excel file to be generated without errors, but getting out of memory exception
EDIT: I altered the code in an attempt to create a new worksheet if more than 150 rows have been processed. It is not creating a new worksheet, so the code is not working. New to OpenXml, and not completely sure how to add a new worksheet. Suggestions?
