We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

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

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

2 Replies

DB Dilli Babu Nandha Gopal Syncfusion Team June 27, 2016 01:01 PM UTC

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.
// Dispose the instance of ExcelEngine. 
Refer the online documentation link for further reference. 
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. 
Dilli babu 

DB Dilli Babu Nandha Gopal Syncfusion Team June 27, 2016 05:45 PM UTC

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:  

XlsIO allows to open an Excel file with a maximum limit of 500K rows and 250 columns for multiple times if the workbook and Excel engine objects are closed and disposed once the process is completed. This rows limit can be increased even more by reducing the columns count. 
Kindly try these approaches and let us know if you can achieve the requirement.  
Dilli babu. 

Live Chat Icon For mobile
Up arrow icon