Once again I have to make Export of to Excel. And this time there are requirements to the template – colors, formatting and others. Not so long ago I implemented the export to Excel with XLSX package for React, and it was rendered at client. This solution is good but has many constraints. So, I started to look for another solution.

First of all, I tried to use OpenXMLSDK. Of course, it’s possible to create an Excel file, but the code is very huge. I understood, that I will spend more than a week to make what I should do. Too long. No code here.

The second, I tried NPOI. Much better. Obviously, I can work with this. The example of how to render Excel file with NPOI is below.

using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
.....

      public void CreateFile(string filename)
      {
          using (var fs = new FileStream(filename, FileMode.Create, FileAccess.Write))
          {

              IWorkbook workbook = new XSSFWorkbook();

              ISheet sheet1 = workbook.CreateSheet("Sheet1");

              sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
              var rowIndex = 0;
              IRow row = sheet1.CreateRow(rowIndex);
              row.Height = 30 * 80;
              row.CreateCell(0).SetCellValue("this is content");
              sheet1.AutoSizeColumn(0);
              rowIndex++;

              var sheet2 = workbook.CreateSheet("Sheet2");
              var style1 = workbook.CreateCellStyle();
              style1.FillForegroundColor = HSSFColor.Blue.Index2;
              style1.FillPattern = FillPattern.SolidForeground;

              var style2 = workbook.CreateCellStyle();
              style2.FillForegroundColor = HSSFColor.Yellow.Index2;
              style2.FillPattern = FillPattern.SolidForeground;

              var cell2 = sheet2.CreateRow(0).CreateCell(0);
              cell2.CellStyle = style1;
              cell2.SetCellValue(0);

              cell2 = sheet2.CreateRow(1).CreateCell(0);
              cell2.CellStyle = style2;
              cell2.SetCellValue(1);

              
              var cell3 = sheet2.CreateRow(2).CreateCell(0);
              var style3 = workbook.CreateCellStyle();
              style3.FillForegroundColor = IndexedColors.Red.Index;
              style3.FillPattern = FillPattern.SolidForeground;
              cell3.CellStyle = style3;  
              cell3.SetCellValue("Render by red color");
              
              workbook.Write(fs);
          }
      }

And finally I decided to try ClosedXML, As for me, it’s the most flexible package for Excel rendering. Features with styles are easy to apply. You can also use formulas and filters. The example of how to render Excel file with ClosedXML is below.

using ClosedXML.Excel;
....
     public void RenderXLSX(string filename)
     {
         using (var workbook = new XLWorkbook())
         {
             var worksheet = workbook.Worksheets.Add("Zetungen");
             var worksheet2 = workbook.Worksheets.Add("Übersicht");
             var worksheet3 = workbook.Worksheets.Add("Standorte");
             var style = workbook.Style;
             style.Border.LeftBorder = XLBorderStyleValues.Thin;
             style.Border.LeftBorderColor = XLColor.Black;
             style.Border.RightBorder = XLBorderStyleValues.Thin;
             style.Border.RightBorderColor = XLColor.Black;
             style.Border.TopBorder = XLBorderStyleValues.Thin;
             style.Border.TopBorderColor = XLColor.Black;
             style.Border.BottomBorder = XLBorderStyleValues.Thin;
             style.Border.BottomBorderColor = XLColor.Black;

             var range = worksheet.Range("A1:D1").Style = style;

             worksheet.Row(1).Style.Fill.BackgroundColor = XLColor.FromArgb(220, 230, 241);
             worksheet.Cell("A1").Value = "Hello World!";
             worksheet.Cell("A2").FormulaA1 = "=MID(A1, 7, 5)";


             var blueRow = worksheet.Row(5);

             int startRow = 5;
             for (int i = 0; i < 10; i++)
             {
                 int currentRow = startRow + i;
                 worksheet.Cell(currentRow, i+ 1).Value = $"Test {i}" ;
             }

             workbook.SaveAs(filename);
         }
     }