Export to Excel functionality using NPOI dll

This article explains the functionality of Export to Excel 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!