Export to Excel functionality using NPOI dll

This article explains the functionality of Export to Excel using NPOI dll

DLL download link - http://npoi.codeplex.com/releases/view/49524 

Also you can find NPOI.dll by downloading the attached source code - inside the bin folder.

The best thing I found using NPOI.dll is that it works for both Open Office and MS Excel.

Below are the 9 steps we follow for Export to Excel using NPOI DLL

• Create new Excel workbook.
• Create new Excel sheet.
• (Optional) set the width of the columns.
• Create a header row.
• Set the column names in the header row.
• (Optional) freeze the header row so it is not scrolled.
• Populate the sheet with values.
• Write the workbook to a memory stream.
• Return the result to the end user.

So we will be coding for all the above 9 steps using NPOI dll.

To show it working I will be using my - Telerik Grid View in ASP.NET MVC2: Part 2 - source code and add Export to Excel functionality for the data displayed in Telerik Grid.

1. Download NPOI dll from this link - http://npoi.codeplex.com/releases/view/49524
   
   I have used NPOI dll (1.2.3.0) for this demo app.
    
2. Now open the downloaded Project - First step would be to place the NPOI dll to the local bin folder of our project.
    
3. Now Add a reference to above dll - Solution explorer -> Right Click References -> Add Reference -> Browse -> Look in -> Local bin folder of your project where you have placed the dll in step 2 -> Select NPOI.dll -> Click Ok
    
4. Views
   
   Open Home -? Index .aspx which contains Telerik Grid View. On the right top of Grid -> Let's place the Export to Excel link.
   
   <p align="right">
        <%= Html.ActionLink("Export to Excel", "Articles_Export_ToExcel", new { page = 1, orderBy = "~", filter = "~" }, new { id = "exportLink" })%>
        </p>
   
   "Export to Excel" - Text to be displayed over the Grid
   "Articles_Export_ToExcel"- Action name which we will code in our Controller Class(HomeController.cs)
    
5. Controller - Open HomeController.cs - Add the action name( Articles_Export_ToExcel) which we have provided for Export to Excel in the View
   
   -> 9 steps which I have mentioned in beginning of the article has been coded down in this function.
   -> Add the below 2 using statements
   
   using NPOI.HSSF.UserModel;//For using NPOI dll
   using System.IO;//For memory stream

   //Export to Excel Action
           public ActionResult Articles_Export_ToExcel(int page, string orderBy, string filter)
           {
               var tempArticles = objRepository.MyArticles_DisplayRecords();

               //Create new Excel Workbook
               var workbook = new HSSFWorkbook();

               //Create new Excel Sheet
               var sheet = workbook.CreateSheet();

               //(Optional) set the width of the columns
               sheet.SetColumnWidth(0, 20 * 256);//Title
               sheet.SetColumnWidth(1, 20 * 256);//Section
               sheet.SetColumnWidth(2, 20 * 256);//Views
               sheet.SetColumnWidth(3, 20 * 256);//Downloads
               sheet.SetColumnWidth(4, 20 * 256);//Status

               //Create a header row
               var headerRow = sheet.CreateRow(0);
               headerRow.CreateCell(0).SetCellValue("Title");
               headerRow.CreateCell(1).SetCellValue("Section");
               headerRow.CreateCell(2).SetCellValue("Views");
               headerRow.CreateCell(3).SetCellValue("Downloads");
               headerRow.CreateCell(4).SetCellValue("Status");

               //(Optional) freeze the header row so it is not scrolled
               sheet.CreateFreezePane(0, 1, 0, 1);

               int rowNumber = 1;

               //Populate the sheet with values from the grid data

               foreach (IndexTelerikGridViewModel objArticles in tempArticles)
               {
                   //Create a new Row
                   var row = sheet.CreateRow(rowNumber++);

                   //Set the Values for Cells
                   row.CreateCell(0).SetCellValue(objArticles.sTitle);
                   row.CreateCell(1).SetCellValue(objArticles.sSection);
                   row.CreateCell(2).SetCellValue(objArticles.iViews);
                   row.CreateCell(3).SetCellValue(objArticles.iDownloads);
                   row.CreateCell(4).SetCellValue(objArticles.sStatus);

               }

               //Write the Workbook to a memory stream
               MemoryStream output = new MemoryStream();
               workbook.Write(output);

               //Return the result to the end user
               return File(output.ToArray(),   //The binary data of the XLS file
                "application/vnd.ms-excel", //MIME type of Excel files
                "ArticleList.xls");     //Suggested file name in the "Save as" dialog which will be displayed to the end user
           }
   
   
    

6. Run the application.
   
   Once we click Export to Excel link- we will be provided option to open the file with MSOffice/OpenOffice
   
   
   

So in this article we have seen how to use NPOI dll for implementing Export to Excel functionality.

Happy Learning!