Exporting large amounts of data from XLSX - OutOfMemoryException

Question

I am going to export a large amount of data (115,000 rows x 30 columns) in Excel OpenXML (xlsx) format. I use some libraries, such as DocumentFormat.OpenXML, ClosedXML, NPOI.

With each of these, an OutOfMemoryException is thrown because representing the sheet in memory causes an increase in exponential memory.

Also, closing the document file every 1000 times (and freeing up memory), the next load causes an increase in memory.

Is there a more efficient way to export data to xlsx without consuming much memory?

Answer 1

OpenKML SDK , , DOM. wikipedia SAX:

, DOM , SAX XML-

, Excel.

- http://polymathprogrammer.com/2012/08/06/how-to-properly-use-openxmlwriter-to-write-large-excel-files/

, , 115k 30 :

public static void LargeExport(string filename)
{
    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 Worksheet());
        writer.WriteStartElement(new SheetData());

        for (int rowNum = 1; rowNum <= 115000; ++rowNum)
        {
            //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.ToString()));

            //write the row start element with the row index attribute
            writer.WriteStartElement(new Row(), attributes);

            for (int columnNum = 1; columnNum <= 30; ++columnNum)
            {
                //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(columnNum), rowNum)));

                //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("This is Row {0}, Cell {1}", rowNum, columnNum)));

                // write the end cell element
                writer.WriteEndElement();
            }

            // write the end row element
            writer.WriteEndElement();
        }

        // write the end SheetData element
        writer.WriteEndElement();
        // write the 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 to get the column name from the column index. This is not well 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;
}

Answer 2

Excel , . ...

99% , , .

, Spout, , . , . , ( PHP), . , - Spout - open-source - .

Answer 3

, , . , . , . . : Excel , ?

, , , , , , , XLSX. , , :

• ( 1000 10000 )

• (vb.net ( vba), #, python, java, XML-), . :

  • XML XLSX ( file.xlsx\xl\worksheets\sheet1.xml file.xlsx\xl\worksheets\sharedStrings.xml)
  • XML- ( OutOfMemoryException, )
  • XLSX ( XLSX)

, . Excel . , / .