OutOfMemory exception when read big excel files

[Pavel Ajtkulov]

Hi.

Alas, we have to work with big excel files (size > 250 Mb). This files are plain (without charts, images, etc) and contains about 170 sheets with 130*500 numeric cells.

Is it possible to work with that big files?

On ExcelEngine.Excel.Workbooks.OpenReadOnly(tempFileName) I've got an OutOfMemoryException. 

   at Syncfusion.CompoundFile.XlsIO.Native.ILockBytes.WriteAt(UInt64 ulOffset, Byte[] pv, UInt32 cb, UInt32& pcbWritten)

   at Syncfusion.CompoundFile.XlsIO.Native.CompoundFile.Open(Stream stream)

   at Syncfusion.CompoundFile.XlsIO.Native.CompoundFile..ctor(Stream stream)

   at Syncfusion.XlsIO.Implementation.ApplicationImpl.CreateCompoundFile(Stream stream)

   at Syncfusion.XlsIO.Implementation.WorkbookImpl.ParseStream(Stream stream, String password, ExcelVersion version, ExcelParseOptions options)

   at Syncfusion.XlsIO.Implementation.WorkbookImpl..ctor(IApplication application, Object parent, Stream stream, ExcelParseOptions options, ExcelVersion version)

   at Syncfusion.XlsIO.Implementation.ApplicationImpl.CreateWorkbook(Object parent, Stream stream, ExcelVersion version, ExcelParseOptions options)

   at Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(Stream stream, ExcelVersion version, ExcelParseOptions options)

   at Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(Stream stream, ExcelOpenType openType, ExcelParseOptions options)

   at Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.OpenReadOnly(String strFileName, ExcelOpenType openType, ExcelParseOptions options)

   at Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.OpenReadOnly(String strFileName)

   at Usp.Sip.Common.ExcelData.Dao.SyncfusionSpreadsheetDao.Open(String fileName) in 

[Pavel Ajtkulov]

Syncfusion version = 10.4.0.53 

[Manikandan M]

Hi Pavel,

 

Thanks for Syncfusion products.

 

We suspect that “OutOfMemoryException is thrown when opening file” could be a defect. Could you please create the new incident with following details which would help us to analysis the issue?

      1.       System configuration.

      2.       Project scenario

      3.       Files which throws exception.

 

Please let us know if you need any clarifications.

 

Thanks,

Manikandan M.

[Pavel Ajtkulov]

Hi, Manikandan.

Excuse me for delay.

Please, tell me your email, or send it to [email protected]. There are some private datas.

Are there memory limitations on file open procedure?

I can open excel files (fileSize < 100Mb) using Syncfusion, but I've got MemoryException on fileSize=250Mb. Indeed, I need open fileSize=500Mb...

[Manikandan M]

Hi Pavel,

 

We highly regret for delay in getting back to you.

 

In Essential XlsIO, we will be using the system memory for content preservation until it is saved to the file. If we are dealing with large amount of data XlsIO component requires more system memory to preserve the details. For any workbook, XlsIO approximately uses 10 times more that file size. Whereas it depends on data’s of workbook.

So as we requested earlier, Could you please post this question through Direct Trac Developer Support System (https://www.syncfusion.com/account/login?ReturnUrl=%2fsupport%2fdirecttrac%2fincidents so that we can  analysis and provide you a solution for this problem and you can take the advantage of the expertise of a dedicated support engineer and a guaranteed response time and we hope you will take advantage of this system as well. Thank you for your participation in Syncfusion’s Community Forums”.

Also we have a NDA (Non-Disclosure agreement).  We use your documents/projects for issue reproducing and testing purpose only and we will not disclose this document to anyone else.

Please let us know if you have any questions.

 

Regards,

Manikandan M.

[Manikandan M]

Hi Pavel,

 

We highly regret for delay in getting back to you.

 

In Essential XlsIO, we will be using the system memory for content preservation until it is saved to the file. If we are dealing with large amount of data XlsIO component requires more system memory to preserve the details. For any workbook, XlsIO approximately uses 10 times more that file size. Whereas it depends on data’s of workbook.

So as we requested earlier, Could you please post this question through Direct Trac Developer Support System (https://www.syncfusion.com/account/login?ReturnUrl=%2fsupport%2fdirecttrac%2fincidents so that we can  analysis and provide you a solution for this problem and you can take the advantage of the expertise of a dedicated support engineer and a guaranteed response time and we hope you will take advantage of this system as well. Thank you for your participation in Syncfusion’s Community Forums”.

Also we have a NDA (Non-Disclosure agreement).  We use your documents/projects for issue reproducing and testing purpose only and we will not disclose this document to anyone else.

Please let us know if you have any questions.

 

Regards,

Manikandan M.

 

[Dilli Babu Nandha Gopal]

Hi, 

 

XlsIO consumes 4 times of memory that of a workbook in the latest version (14.1v). However, the memory consumption depends on the usage of unique data, styles, charts and images in a workbook. 

 

In XlsIO library, the memory consumption has been gradually improved since 12.2v. In addition, to handle larger data with less memory consumption, we recommend to use any of the following options. 

 

• ParseWorksheetsOnDemand – To parse worksheets on demand (Refer: Accessing worksheets)
• ImportOnSave – To import data while serializing the document (Refer: Importing Data Table)
• IMigrantRange – An optimal method of writing values with better memory (Refer: Range Access)
• SetText(), SetNumber(), SetFormulas(), SetBoolean() methods in Worksheet class to set the values directly.

 

To know more about these optimization technique, you can refer the following online documentation link. 

 

UG Documentation link: http://help.syncfusion.com/file-formats/xlsio/improving-performance  

 

Regards, 

Dilli babu.