Sometimes developers need to create huge spreadsheets, perhaps with 100's of thousands of rows, and many columns. A worksheet that contains 1,000,000 rows and 10 columns will contain upwards of 20,000,000 nodes in the worksheet part. This presents a problem when using a DOM approach. Both XmlDocument and LINQ to XML allocate too much memory. It is not possible to keep the entire XML tree in memory. However, it is straightforward to use a streaming approach. In the example that I present with this screen-cast, the working set (the amount of memory consumed by the application) stabilizes at about 20MB even as the sample application creates a spreadsheet with more than 300,000 rows. Code is attached.
Can you please guide me how to create huge Excel sheet with dataset/datatable using the same approach as used here?Would prefer if you provide me the source code as well for conversion of huge datatable containing millions of records to Xlsx file with openxml api