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 ( 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

    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" })%>

    "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);

                //Create a header row
                var headerRow = sheet.CreateRow(0);

                //(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


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

                //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!

Similar Articles