Export very large data to Excel file

Question

I want to write data into four sheets in single excel.I am facing problem,for large amount of data,it gives system out of memory exception I have more than 300 columns and around 28000 rows in one table,there are total 4 tables.My application works fine for around 12000 rows,but throws exception after this. Is there any way this could be resolved,tried everything but not found any solution.Please do let me know about the same. Here is my code which creates excel with 4 sheets string AppLocation = ConfigurationManager.AppSettings["Path"].ToString(); string file = AppLocation + "\DataFile.xlsx"; using (XLWorkbook wb = new XLWorkbook()) { wb.AddWorksheet(ds.Tables[0],"Claim_register_Self_Funds_2015"); wb.AddWorksheet(ds.Tables[1],"Claim_register_Self_Funds_2016"); wb.AddWorksheet(ds.Tables[2],"Claim_register_Self_Funds_2017"); wb.AddWorksheet(ds.Tables[3],"Claim_register_Self_Funds_2018"); wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; wb.Style.Font.Bold = true; wb.SaveAs(file); }

Vishwakarma Vijay · Answer

Hi, you can try Spire.XLS to export large data to Excel and see if this componenet solves your problem https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html

Amit Gupta · Answer

Instead of adding worksheets directly, add a batch of 1000 rows or any feasible count to your new worksheet. Alternatively, you can clear your datatable[0] and dispose the datatable, as soon as work done, I am not sure that whether it will solve the issue but you can try from your end. If you are processing other work, try to reduce that work part. Feel free to ask any clarification on the statement. Good Luck

Aniket Narvankar · Answer

Amit Gupta,not getting it exactly how to do the same

Amit Gupta · Answer

This error occurs when you are processing huge data processing in memory. Try to add data in chunks or on a batch of 1000 that will reduce the workload in RAM.