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
            }


    ExpExl1.gif
     

  6. Run the application.

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

    ExpExl2.gif

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

Happy Learning!