Version: Infragistics.Documents.Excel V12.2 as Excel2007 format
Scenario:
In our trading app, there is a requirement to save all application related data (All Screens in the app) to be saved onto an Excel file so that the users can refer to it at a later date as to what data was exactly used for valuation and if required change it re-run it through the application to see the results.
So the output file cant be any other format other than excel as per the requirement.
So to save the application data, we use the Workbook object, create worksheets and add data by looping through the data object and set the cell value of the worksheet.
Problem:
Some of the screens in the application contain trade sensitivities data which is around approx 700 thousand rows which need to be written to the excel worksheet.
The issue we face is that the Workbook object contains all the worksheets and the Workbook.Save() method requires all sheets to be in the workbook object at the point of save, we get a out of memory exception when writing the huge number of records.
We are not able to write sheet by sheet to the workbook by subsequently clearing the worksheets.
Is there a way to get around this issue by not having all the worksheets in the workbook to be able to save or can we append worksheets to an existing file?
Please suggest a way out to deal with out of memory issue in this case.
Hello Kannan,
Thank you for your post.
I have been looking into it. I created a short sample application based on your scenario. In it I created a workbook that contained one worksheet with 700 000 rows and the application save the data in .xlsx file. If you have more worksheets with a big data, at some point, the memory that the application will consume will be more than the available memory and an exception might appear. What I can suggest is submitting a new product idea for having the ability to load on demand the content of a Workbook, which will allow to load the workbook without loading all the data in it and you will be able to save the document sheet by sheet and avoid this issue.
Steps to create your idea:
1. Log into the Infragistics Product Ideas site at http://ideas.infragistics.com (creating a new login if needed).
2. Navigate to the product / platform channel of your choice (e.g. WPF, Windows Forms, ASP.NET, HTML5 / Ignite UI, iOS / NucliOS, etc.)
3. Add your product idea and be sure to be specific and provide as much detail as possible.
• Explain the context in which a feature would be used, why it is needed, why it can’t be accomplished today, and who would benefit from it. You can even add screenshots to build a stronger case. Remember that for your suggestion to be successful, you need other members of the community to vote for it. Be convincing!
• Include a link to this thread in your idea so product management will be able to look back at this case.
The benefits of submitting the product idea yourself include:
- Direct communication with our product management team regarding your product idea.
- Notifications whenever new information regarding your idea becomes available.
Additional benefits of the Product Idea system include:
- Ability to vote on your favorite product ideas to let us know which ones are the most important to you. You will have ten votes for this and can change which ideas you are voting for at any time.
- Allow you to shape the future of our products by requesting new controls and products altogether.
- You and other developers can discuss existing product ideas with members of our Product Management team.
The product ideas site allows you to track the progress of your ideas at any time, see how many votes it got, read comments from other developers in the community, and see if someone from the product team has additional questions for you.
Please let me know if you need any further assistance on this matter.
I have been further investigating this and after contacting with our development team and you can reduce the memory that the application is consuming, by using the SetCellValue method of the WorksheetRow, instead of using WorksheetCell.Value property:
worksheetRow.SetCellValue(++currentCell, i.Value);
This will reduce the memory allocated for the WorksheetCell objects and can improve the overall memory usage. Also if most of the Cells in column using the same format you can change the format of the column instead of the Cells for example
’worksheet.Columns[0].CellFormat.Alignment = HorizontalCellAlignment.Left’
If you require any further assistance please do not hesitate to ask.