Generate Excel With (NPOI) in C#

M.T — Wed, 29 Jan 2020 10:26:26 +0000

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:

First Hand "Bad Experience"

M.T — Wed, 25 Dec 2019 10:13:15 +0000

Back when I was on the verge of finishing college as a Software Developer, I used to fantasize about working with a behemoth software company specialized in system software (for some reasons, I was fond to OS and Compilers Architect). For me, any other form of software wasn't worth it (Web, Mobile...etc), which goes without saying that was an amateur & ignorant view.
Time Passed & finally graduated, then the journey for job hunting started. It was tiring, depressing & just pure horror. It took 8 months to get a job & with every month my standards (remember, the ignorant views) goes lower & lower, 'till finally I found a position of junior ASP.NET Developer (I used to hate web technologies the most).

The company was a small firm specialized in Software & Security but mainly security. The Development "Team" was of 2 two members & I was the third & and then a "Senior" joined.
The Team was dysfunctional & every member has his own projects & we rarely work together as a team, on top of that there was no defined stack. Some members would use PHP (Either WordPress Or Vanilla), Others would use .NET. Obviously, this was Chaos. Deadlines weren't met, and if happened and managed to respect the deadline, the quality was down to earth.

I went on Learning frenzy (to learn web technologies) as I was mainly into C++ before accepting the truth and delve into the web realm.
From the first week, I got a new project and it's PHP and the week after, a big client account was handed to me (completing the development from where the last developer stopped => It was hell).
In just couple of months, the team went from 4 members to only 2 and after a year became just one (ME !).
With every member leaving the team, all his/her projects was automatically directed to me, regardless to the technology used. It was frustrating, annoying, unrealistic, very stressful & actually was perfect.

You see, this actually was a perfect set for me as a junior & a fresh graduated (I didn't view it that way at that time to be honest). The knowledge I got, the amount of self study I have to do within a very limited time & working on other developers codes helped me (One way or another) a lot.

After 6 years with the firm, my skills were versed & learning new technologies became a breeze. I used to handle all the projects phases from requirements gathering, analysis, database design till front end, deployment & support.

Obviously, it wasn't the best company or the best experience in the world (if there is such !), as at times the stress was soul crushing & the experience I was getting was more of breadth (C#, JavaScript,TypeScript, PHP....etc) more than depth (Didn't became C# Guru nor JavaScript Ninja).
Now, this is rightfully doesn't look so good, but I believe this was an amazing roller coaster. I came to conclusion that there is no such a thing as perfect experience out there, everyone has his own unique experience that's is the by-product of the environment he got exposed to.
So, if I could do it again, would I do it the same way ? Probably not, but time isn't going back ('till now at least) & the way this experience shaped me isn't that bad also.

My intake is, no matter how de-organized or stressful your company is, there is always a good side of it, there is always something that will shape you in a direction you didn't see yourself in before.

DEV Community: M.T

Generate Excel With (NPOI) in C#

First Hand "Bad Experience"