.net - Export big amount of data from XLSX - OutOfMemoryException -

i approaching export big amount of data (115.000 rows x 30 columnd) in excel openxml format (xlsx). using libraries documentformat.openxml, closedxml, npoi.

with each of this, outofmemoryexception thrown because representation of sheet in memory causes exponential memory increase.

also closing document file every 1000rows (and releasing memory), next loading causes memory increase.

is there more performant way export data in xlsx without occupy lot of memory?

the openxml sdk right tool job need careful use sax (simple api xml) approach rather dom approach. linked wikipedia article sax:

where dom operates on document whole, sax parsers operate on each piece of xml document sequentially

this vastly reduces amount of memory consumed when handling large excel files.

there's article on here - http://polymathprogrammer.com/2012/08/06/how-to-properly-use-openxmlwriter-to-write-large-excel-files/

adapted article, here's example outputs 115k rows 30 columns:

public static void largeexport(string filename) {     using (spreadsheetdocument document = spreadsheetdocument.create(filename, spreadsheetdocumenttype.workbook))     {         //this list of attributes used when writing start element         list<openxmlattribute> attributes;         openxmlwriter writer;          document.addworkbookpart();         worksheetpart worksheetpart = document.workbookpart.addnewpart<worksheetpart>();          writer = openxmlwriter.create(worksheetpart);                     writer.writestartelement(new worksheet());         writer.writestartelement(new sheetdata());          (int rownum = 1; rownum <= 115000; ++rownum)         {             //create new list of attributes             attributes = new list<openxmlattribute>();             // add row index attribute list             attributes.add(new openxmlattribute("r", null, rownum.tostring()));              //write row start element row index attribute             writer.writestartelement(new row(), attributes);              (int columnnum = 1; columnnum <= 30; ++columnnum)             {                 //reset list of attributes                 attributes = new list<openxmlattribute>();                 // add data type attribute - in case inline string (you might want @ shared strings table)                 attributes.add(new openxmlattribute("t", null, "str"));                 //add cell reference attribute                 attributes.add(new openxmlattribute("r", "", string.format("{0}{1}", getcolumnname(columnnum), rownum)));                  //write cell start element type , reference attributes                 writer.writestartelement(new cell(), attributes);                 //write cell value                 writer.writeelement(new cellvalue(string.format("this row {0}, cell {1}", rownum, columnnum)));                  // write end cell element                 writer.writeendelement();             }              // write end row element             writer.writeendelement();         }          // write end sheetdata element         writer.writeendelement();         // write end worksheet element         writer.writeendelement();         writer.close();          writer = openxmlwriter.create(document.workbookpart);         writer.writestartelement(new workbook());         writer.writestartelement(new sheets());          writer.writeelement(new sheet()         {             name = "large sheet",             sheetid = 1,             id = document.workbookpart.getidofpart(worksheetpart)         });          // end sheets         writer.writeendelement();         // end workbook         writer.writeendelement();          writer.close();          document.close();     } }  //a simple helper column name column index. not tested! private static string getcolumnname(int columnindex) {     int dividend = columnindex;     string columnname = string.empty;     int modifier;      while (dividend > 0)     {         modifier = (dividend - 1) % 26;         columnname = convert.tochar(65 + modifier).tostring() + columnname;         dividend = (int)((dividend - modifier) / 26);     }      return columnname; }