{"id":3118,"date":"2016-03-19T12:49:14","date_gmt":"2016-03-19T12:49:14","guid":{"rendered":"http:\/\/www.ericwhite.com\/home2\/bm8qcmjy\/public_html\/blog\/?page_id=3118"},"modified":"2016-03-19T12:49:14","modified_gmt":"2016-03-19T12:49:14","slug":"screen-cast-using-open-xml-and-linq-to-xml-in-a-streaming-fashion-to-create-huge-spreadsheets","status":"publish","type":"page","link":"https:\/\/www.ericwhite.com\/blog\/screen-cast-using-open-xml-and-linq-to-xml-in-a-streaming-fashion-to-create-huge-spreadsheets\/","title":{"rendered":"Screen-Cast: Using Open XML and LINQ to XML in a Streaming Fashion to Create Huge Spreadsheets"},"content":{"rendered":"<p>Sometimes developers need to create huge spreadsheets, perhaps with 100&#8217;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.<\/p>\n<p><iframe loading=\"lazy\" title=\"StreamingSpreadsheet.wmv\" width=\"500\" height=\"281\" src=\"https:\/\/www.youtube.com\/embed\/gFjFNaYOuiw?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p>Example &#8211; <a href=\"https:\/\/www.ericwhite.com\/blog\/wp-content\/uploads\/2016\/03\/SpreadsheetWriter.zip\">Download Code<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Sometimes developers need to create huge spreadsheets, perhaps with 100&#8217;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. [&hellip;]<\/p>\n","protected":false},"author":10567,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_s2mail":"","footnotes":""},"class_list":["post-3118","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.ericwhite.com\/blog\/wp-json\/wp\/v2\/pages\/3118","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ericwhite.com\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.ericwhite.com\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.ericwhite.com\/blog\/wp-json\/wp\/v2\/users\/10567"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ericwhite.com\/blog\/wp-json\/wp\/v2\/comments?post=3118"}],"version-history":[{"count":1,"href":"https:\/\/www.ericwhite.com\/blog\/wp-json\/wp\/v2\/pages\/3118\/revisions"}],"predecessor-version":[{"id":3120,"href":"https:\/\/www.ericwhite.com\/blog\/wp-json\/wp\/v2\/pages\/3118\/revisions\/3120"}],"wp:attachment":[{"href":"https:\/\/www.ericwhite.com\/blog\/wp-json\/wp\/v2\/media?parent=3118"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}