Generate Excel With (NPOI) in C#

#csharp #dotnet

Generating an Excel based on some data-set is one of these tasks that you have to do from time to time as a developer.
Normally, I don't get much of these scenarios when I have to generate an excel, and when I do, I almost forgetting about how is done partially at least.

So, I've decided to document a sample and share it out, as it might help someone out there to refresh the vagueness.

Without further due, here we go:

Obviously, the first step would be installing the NPOI library using NuGet which is straight forward process.

NPOI consist of many namespaces, but for now our focus would be on only two:



using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

We will need NPOI.HSSF.UserModel to be able to use the HSSFWorkbook,HSSFFont, HSSFCellStyle and others needed objects.
While using NPOI.SS.UserModel; will be used to define ISheet,IRow,ICell and other required objects.

The Logic of creating an excel is simple:

Define a Workbook.
Create a Sheet to the workbook.
Add Rows and Cells to the Sheet.

Now before I dive into creating rows & cells, I'll just write a function to create a cell for us, so instead of writing



ICell Cell = CurrentRow.CreateCell(CellIndex);
            Cell.SetCellValue(Value);

everytime we need to create a cell, we just create this function:



private void CreateCell(IRow CurrentRow, int CellIndex, string Value, HSSFCellStyle Style)
        {
            ICell Cell = CurrentRow.CreateCell(CellIndex);
            Cell.SetCellValue(Value);
            Cell.CellStyle = Style;
        }

So, creating a cell now is just:



 CreateCell(HeaderRow, CellIndex, "Column Value", CellStyle);

Now, let's start creating an excel based on defined headers and given data collection.



            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFFont myFont = (HSSFFont)workbook.CreateFont();
            myFont.FontHeightInPoints = 11;
            myFont.FontName = "Tahoma";


            // Defining a border
            HSSFCellStyle borderedCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            borderedCellStyle.SetFont(myFont);
            borderedCellStyle.BorderLeft = BorderStyle.Medium;
            borderedCellStyle.BorderTop = BorderStyle.Medium;
            borderedCellStyle.BorderRight = BorderStyle.Medium;
            borderedCellStyle.BorderBottom = BorderStyle.Medium;
            borderedCellStyle.VerticalAlignment = VerticalAlignment.Center;

            ISheet Sheet = workbook.CreateSheet("Report");
            //Creat The Headers of the excel
            IRow HeaderRow = Sheet.CreateRow(0);

            //Create The Actual Cells
            CreateCell(HeaderRow, 0, "Batch Name", borderedCellStyle);
            CreateCell(HeaderRow, 1, "RuleID", borderedCellStyle);
            CreateCell(HeaderRow, 2, "Rule Type", borderedCellStyle);
            CreateCell(HeaderRow, 3, "Code Message Type", borderedCellStyle);
            CreateCell(HeaderRow, 4, "Severity", borderedCellStyle);

            // This Where the Data row starts from
            int RowIndex = 1;

            //Iteration through some collection
            foreach (BatchErrorReport batchErrorReport in BatchErrorReports)
            {
                //Creating the CurrentDataRow
                IRow CurrentRow = Sheet.CreateRow(RowIndex);
                CreateCell(CurrentRow, 0, batchErrorReport.Name, borderedCellStyle);
                // This will be used to calculate the merge area
                int NumberOfRules = batchErrorReport.Rules.Count;
                if (NumberOfRules > 1)
                {
                    int MergeIndex = (NumberOfRules - 1) + RowIndex;

                //Merging Cells
                    NPOI.SS.Util.CellRangeAddress MergedBatch = new NPOI.SS.Util.CellRangeAddress(RowIndex, MergeIndex, 0, 0);
                    Sheet.AddMergedRegion(MergedBatch);
                }
                int i = 0;
                // Iterate through cub collection
                foreach (BatchDataQuality batchDataQuality in batchErrorReport.Rules)
                {
                    if (i > 0)
                        CurrentRow = Sheet.CreateRow(RowIndex);
                    CreateCell(CurrentRow, 1, batchDataQuality.RuleID, borderedCellStyle);
                    CreateCell(CurrentRow, 2, batchDataQuality.RuleType, borderedCellStyle);
                    CreateCell(CurrentRow, 3, batchDataQuality.CodeMessageType, borderedCellStyle);
                    CreateCell(CurrentRow, 4, batchDataQuality.Severity, borderedCellStyle);
                    RowIndex++;
                    i++;
                }
                RowIndex = NumberOfRules >= 1 ? RowIndex : RowIndex + 1;
            }
          // Auto sized all the affected columns
          int lastColumNum = Sheet.GetRow(0).LastCellNum;
          for (int i = 0; i <= lastColumNum; i++)
          {
              Sheet.AutoSizeColumn(i);
                GC.Collect();
          }
         // Write Excel to disk 
          using (var fileData = new FileStream(Utility.DOCUMENT_PATH + "ReportName.xls", FileMode.Create))
            {
                workbook.Write(fileData);
            }

This would produce something like this:

Top comments (2)

Idris Akbar Adyusman • Jul 5 '23

Thanks for the Guide!
Just a tip, for anyone who wants to generate excel with more than 65k rows, instead of using the HSSFWorkbook namespace replace it with XSSFWorkbook that can handle up to 1,048,576 rows!