[XLSIO] Opening a file under heavy constraints of memory usage

[Luis B.Chicaiza]

Good morning,

I apologize if this question is not properly categorized.

Our team is evaluating if the plugin for Excel that you provide is the appropiate for our needs and so far the component is really promising.

However we have a problem. Our business logic depends heavily on loading data into files in slices of 200000+ rows per file opening. This works amazingly the first time but in the following attempts the application breaks due to memory. We are heavily limited in the requirements in terms of memory and even if we would be able to compile under x64, it wouldn't really solve the problem.

So the question is this, the application breaks exactly when trying to open a file that already has a lot of contents inside of it (250K+ with 10 rows or so (no formulas)). We don't know how your code is built but we assume that this is due to the fact that you probably load all the data in the file to memory (partly because for doing some operations in excel, all the data is required). However in our case we wont use the plugin as means of treatment of the data in the excel, but more as a writer. We would like to know if there is a mode of opening the file (maybe an append mode that we have not found in the documentation) that allows us to Import the data into the file without having to load the previous content (like the appending to FileStreams normally works).

Thank you in advance for your reply

[Dilli Babu Nandha Gopal]

Hi Luis, 

 

Thank you for contacting Syncfusion support. 

 

Memory consumption can be reduced by following the below steps. 

 

1. Closing a Workbook: 

Close the workbook object and dispose the Excel engine object once the workbook manipulation is completed. Kindly refer the below code snippet to achieve this. 

 

Code Example: 

 

// Close the instance of IWorkbook.

workbook.Close();  

// Dispose the instance of ExcelEngine. 

excelEngine.Dispose(); 

 

Refer the online documentation link for further reference. 

http://help.syncfusion.com/file-formats/xlsio/loading-and-saving-workbook#closing-a-workbook 

 

2. Open the workbook with ExcelParseOptions.ParseWorksheetsOnDemand 

 

Use ExcelParseOptions.ParseWorksheetsOnDemand in IWorkbooks.Open method to parse its worksheets only when accessed. This option can be used in a scenario where workbook contains multiple worksheets and only few worksheets are used. Kindly refer below code snippet to achieve this. 

 

Code Example: 

IWorkbook workbook = application.Workbooks.Open(fileName,ExcelParseOptions.ParseWorksheetsOnDemand);

 

If the issue exists even after applying these changes, then share us the issue reproducing file along with your machine configuration. This will be helpful us to provide a prompt solution at the earliest. 

 

Regards, 

  

Dilli babu 

[Dilli Babu Nandha Gopal]

Hi Luis, 

 

Please find the below details of XlsIO’s behavior to parse workbook contents and its memory consumption. 

 

XlsIO loads entire data of a workbook while opening and holds it in memory for further process. Also, it is not possible to append data to a sheet without parsing its contents. The workaround of using ParseWorksheetsOnDemand, which we suggested in our previous update helps to parse only the sheet in which your data need to be appended. This will help the situation to consume less memory. 

 

Regarding the data usage limit in a sheet: