Excel Report Generation in MVC Using NPOI
In this article, I will explain how to create an Excel Report using NPOI DLL. NPOI is open source DLL which facilitates to generate Excel. It is the .NET version of POI project in JAVA. You can explore its rich features for your purposes.
NPOI
Referring NPOI on Codeplex, it gives us following definition:-
This project is the .NET version of POI Java project. It is an open source project which can help you read/write xls, doc, ppt files. It has a wide application.
For example, you can use it to
a. generate a Excel report without Microsoft Office suite installed on your server and more efficient than call Microsoft Excel ActiveX at background;
b. extract text from Office documents to help you implement full-text indexing feature (most of time this feature is used to create search engines).
c. extract images from Office documents
d. generate Excel sheets that contains formulas
For more info on NPOI visit following link.https://npoi.codeplex.com/
So let's start, creating XLS file by first downloading NPOI DLL from above link and using it in your MVC project.
Suppose you want to generate a report in excel which can be any data, then you can easily do it with NPOI.
I will take an example of simple list which i will be extracting it in XLS file.
For eg refer following 3 figures
Fig 1 shows the simple MVC View of User list in browser and Export to excel icon.
On clicking on it, a dialog opens displaying generated excel named "User.xls".
Fig 3 shows the excel file with data.
Lets look how to achieve this scenario.
View Page
In MVC cshtml file, create an anchor tag, which will map to an action in a controller which will generate excel file for us.
Following fig describes the above mentioned scenario.
Here "UserListDT" method will be called, in GenerateXLS controller.
Controller Page
Firstly you need to give reference to NPOI dll which you downloaded.
after that, add following lines in your using part to access features of NPOI, as shown in below figure.
Now create method name UserList whose return type will be an "ActionResult"
Refer following figures for code flow.
Code Explianation
lstUser
This variable gets data from data base which we want to export in excel.
DataTable dt
Now we will convert our list variable to data table as it gives us more flexibility then list when we create excel.
add static column names to data table and then write a for loop to bind date of list to data table.
HSSFWorkbook book
This var instantiate object to create a workbook.
In excel, collection of sheets is workbook. so first create a workbook and then sheet.
HSSFSheet sheet
This var instantiate object to create a work sheet inside workbook.
headerCellStyle & dataCellStyle
These are 2 diff variable which we will use to give styles to sheet. if your header and data row has same style then you can work with one variable.
hRow
var hRow = sheet.CreateRow(0);
this will create your header row at 0 index. note here just row is created but there are no cells in it.
Excel Data
for (int j = 0; j < dt.Columns.Count; j++)
{
HSSFRow r = (HSSFRow)sheet.GetRow(0);
r.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName.ToString());
r.GetCell(j).CellStyle = headerCellStyle;
}
This for loop will create cells in zeroth row one by one.
Then will set its value and its style. Notice style variable is "headerCellStyle".
for (int i = 0; i < dt.Rows.Count; i++)
{
var dRow = sheet.CreateRow(i + 1);
HSSFRow dr = (HSSFRow)sheet.GetRow(i + 1);
for (int k = 0; k < dt.Columns.Count; k++)
{
dr.CreateCell(k).SetCellValue(dt.Rows[i][k].ToString());
dr.GetCell(k).CellStyle = dataCellStyle;
}
}
the above for loop will create rows one by one and inner loop will generate cells inside that row. the logic of value and style is same as header row. The style variable here is "dataCellStyle".
MemoryStream output = new MemoryStream();
book.Write(output);
string filenm = "User.xls";
return File(output.ToArray(), "application/vnd.ms-excel", filenm);
The above lines simply write the out put to book variable and it will return it to user. you can change name of excel by specifying in filenm variable.
Hope you will explore more with NPOI.
Regards,
Shakil Sama