Screen-Cast: Using Open XML and LINQ to XML in a Streaming Fashion to Create Huge Spreadsheets
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.
Example – Download Code