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)
Good example for NPOI, we can also use EPPlus which is a good alternative.
You can also check this example
Create Excel file in C# without Interop (MVC (EPPlus) and Console (NPOI ) example)
Thanks
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!