{"id":4220,"date":"2016-09-03T15:14:57","date_gmt":"2016-09-03T15:14:57","guid":{"rendered":"http:\/\/putridparrot.com\/blog\/?p=4220"},"modified":"2016-09-03T15:14:57","modified_gmt":"2016-09-03T15:14:57","slug":"npoi-saves-the-day","status":"publish","type":"post","link":"https:\/\/putridparrot.com\/blog\/npoi-saves-the-day\/","title":{"rendered":"NPOI saves the day"},"content":{"rendered":"<p><strong>Introduction<\/strong><\/p>\n<p>NPOI is a port of POI for .NET. You know how we in the .NET side like to prefix with N or in the case of JUnit, change J to N for our versions of Java libraries.<\/p>\n<p>NPOI allows us to write Excel files without Excel needing to be installed. By writing files directly it also gives us, speed, less likelihood or us leaving a Excel COM\/Automation object in memory and basically a far nicer API.<\/p>\n<p><em>So how did NPOI save the day?<\/em><\/p>\n<p>I am moving an application to WPF and in doing so the third party controls also moved from WinForms to WPF versions. One, a grid control, used to have a great export to Excel feature which output the data in a specific way, unfortunately the WPF version did not write the Excel file in the same format. I was therefore tasked with re-implementing the Excel exporting code. I began with Excel automation which seemed slow and I found it difficult getting the output as we wanted. I then tried a couple of Excel libraries for writing the BIFF format (as used by Excel). Unfortunately these didn&#8217;t fully work and\/or didn&#8217;t do what I needed. Then one of my Java colleagues mentioned POI and checked for an N version of POI, and there it was NPOI. NPOI did everything we needed, thus saving the day.<\/p>\n<p><strong>Let&#8217;s see some code<\/strong><\/p>\n<p>Okay usual prerequisites are<\/p>\n<ul>\n<li>Create a project or whichever type you like<\/li>\n<li>Using NuGet add the NPOI package<\/li>\n<\/ul>\n<p>Easy enough.<\/p>\n<p>Logically enough, we have workbooks at the top level with worksheet&#8217;s within a workbook. Within the worksheet we have rows and finally cells within the rows, all pretty obvious. <\/p>\n<p>Let&#8217;s take a look at some very basic code <\/p>\n<pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\r\nvar workbook = new XSSFWorkbook();\r\nvar worksheet = workbook.CreateSheet(&quot;Sheet1&quot;);\r\n\r\nvar row = worksheet.CreateRow(0);\r\nvar cell = row.CreateCell(0);\r\n\r\ncell.SetCellValue(&quot;Hello Excel&quot;);\r\n\r\nusing (var stream = new FileStream(&quot;test.xlsx&quot;, FileMode.Create, FileAccess.Write))\r\n{\r\n   workbook.Write(stream);\r\n}\r\n\r\nProcess.Start(&quot;test.xlsx&quot;);\r\n<\/pre>\n<p>The above should be pretty self explanatory, after creating the workbook etc. we write the workbook to a file and then using Process, we get Excel to display ht file we&#8217;ve created.<\/p>\n<p><strong>Autosizing columns<\/strong><\/p>\n<p>By default you might feel the columns are too thin, we can therefore iterate over the columns after setting our data and run<\/p>\n<pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\r\nfor (var c = 0; c &lt; worksheet.GetRow(0).Cells.Count; c++)\r\n{\r\n   worksheet.AutoSizeColumn(c);\r\n}\r\n<\/pre>\n<p>The above code is simply looping over the columns (I&#8217;ve assumed row 0 holds headings for each column &#8211; as it were#) and telling the worksheet o auto-size them.<\/p>\n<p><strong>Grouping rows<\/strong><\/p>\n<p>One thing we have in our data is a need to show parent child relationships in the Excel spreadsheet. Excel allows us to do this by &#8220;grouping&#8221; rows. For example, if we have<\/p>\n<p>Parent<br \/>\nChild1<br \/>\nChild2<\/p>\n<p>We&#8217;d like to show this in Excel in collapsible rows, like a treeview. As such we want the child curves to be within the group so we&#8217;d see something like this<\/p>\n<p>+Parent<\/p>\n<p>or expanded<\/p>\n<p>-Parent<br \/>\n Child1<br \/>\n Child2<\/p>\n<p>to achieve this in NPOI (assuming Parent is row 0) we would group row&#8217;s 1 and 2, i.e. <\/p>\n<pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\r\nworksheet.GroupRow(1, 2);\r\n\/\/if we want to default the rows to collapsed use\r\nworksheet.SetRowGroupCollapsed(1, true);\r\n<\/pre>\n<p>finally for grouping, the +\/- button by default displays at the bottom of the grouping which I always found a little strange, so to have this display at the top of the group we set this via<\/p>\n<pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\r\nworksheet.RowSumsBelow = false;\r\n<\/pre>\n<p><strong>Date format<\/strong><\/p>\n<p>You may wish to customise the way DateTime&#8217;s are displayed, in which case we need to apply a style to the cell object, for example, let&#8217;s display the DateTime in the format dd mm yy hh:mm<\/p>\n<pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\r\nvar creationHelper = workbook.GetCreationHelper();\r\n\r\nvar cellStyle = workbook.CreateCellStyle();\r\ncellStyle.DataFormat = creationHelper\r\n   .CreateDataFormat()\r\n   .GetFormat(&quot;dd mmm yy hh:mm&quot;);\r\ncellStyle.Alignment = HorizontalAlignment.Left;\r\n\r\n\/\/ to apply to our cell we use\r\ncell.CellStyle = cellStyle;\r\n<\/pre>\n<p><strong>References<\/strong><\/p>\n<p>https:\/\/github.com\/tonyqus\/npoi<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction NPOI is a port of POI for .NET. You know how we in the .NET side like to prefix with N or in the case of JUnit, change J to N for our versions of Java libraries. NPOI allows us to write Excel files without Excel needing to be installed. By writing files directly [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[54,130],"tags":[],"class_list":["post-4220","post","type-post","status-publish","format-standard","hentry","category-excel","category-npoi"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/4220","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/comments?post=4220"}],"version-history":[{"count":5,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/4220\/revisions"}],"predecessor-version":[{"id":4233,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/4220\/revisions\/4233"}],"wp:attachment":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/media?parent=4220"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/categories?post=4220"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/tags?post=4220"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}