Out of Memory Error - while writing to Excel using Xssfworkbook

Oct 1, 2013 at 11:13 AM
Hi,

I'm using the XSSFWorkbook object in my console application to create big xlsx file. I m getting error:
There was an error generating the XML document.

System.Xml

at System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces, String encodingStyle, String id)
at System.Xml.Serialization.XmlSerializer.Serialize(Stream stream, Object o, XmlSerializerNamespaces namespaces)
at NPOI.OpenXmlFormats.Spreadsheet.CT_Worksheet.Save(Stream stream)
at NPOI.XSSF.UserModel.XSSFSheet.Write(Stream out1)
at NPOI.XSSF.UserModel.XSSFSheet.Commit()
at NPOI.POIXMLDocumentPart.OnSave(List1 alreadySaved)
at NPOI.POIXMLDocumentPart.OnSave(List
1 alreadySaved)
at NPOI.POIXMLDocument.Write(Stream stream)
at IA.Framework.ReportProcessor.ExcelWriter.WriteDataToExcel()
at IA.Framework.ReportProcessor.OlapReportProcessor.WriteOlapResultToExcel(OlapResultDTO resultDto)
at IA.Framework.ReportProcessor.OlapReportWorker.workerThreadMethod(Object threadParam)

Can Anyone help?????
Oct 1, 2013 at 11:42 AM
Here is the code:
XSSFWorkbook Workbook = new XSSFWorkbook();
            ISheet worksheet = Workbook.CreateSheet("Sheet1");

            for (int rownum = 0; rownum < 5000; rownum++)
            {
                IRow row = worksheet.CreateRow(rownum);
                for (int celnum = 0; celnum < 1000; celnum++)
                {
                    ICell Cell = row.CreateCell(celnum);
                    Cell.SetCellValue("Cell: Row-" + rownum + ";CellNo:" + celnum);
                }
            }


            FileStream sw = File.Create("Template.xlsx",10000,FileOptions.WriteThrough);       


            Workbook.Write(sw);
            sw.Close();
Coordinator
Oct 8, 2013 at 7:42 AM
This is a known issue with XmlSerializer. In the final verison of NPOI 2.0, we will get rid of XmlSerializer for the sake of performance and memory issue.
Oct 17, 2013 at 11:28 AM
Is there any alternate way to get rid of this using existing dll???
Coordinator
Oct 27, 2013 at 12:35 AM
No. Btw, how much memory did you consume on your computer when running the above code?
        for (int rownum = 0; rownum < 5000; rownum++)
        {
            IRow row = worksheet.CreateRow(rownum);
            for (int celnum = 0; celnum < 1000; celnum++)
            {
                ICell Cell = row.CreateCell(celnum);
                Cell.SetCellValue("Cell: Row-" + rownum + ";CellNo:" + celnum);
            }
        }
I'm afraid even this part takes a lot of memory. It's 5000*1000=5,000,000 cells.