Click here to Skip to main content
14,548,224 members

Export to Excel using NPOI - C# and WEB API

Rate this:
4.79 (8 votes)
Please Sign up or sign in to vote.
4.79 (8 votes)
12 Aug 2019CPOL
"How to export the List into an Excel file" in a generic way using NPOI with .NET/.NET Core WEB API


This article will walk you through the generic export to Excel feature which is tried and well tested, using C# in WEB API and by using NPOI.

This article is specific to .NET Framework, whereas, I have tried in .NET Core, it works perfectly fine. So hold on and let's jump over it.

Before We Begin the Journey

The main concept of this article is to develop generic Excel to export functionality by importing NPOI and add the below explained 2 cs files included in the sample and then jet set go.

Explore the Code

I have created a GitHub repository for you to explore the code with ASP.NET MVC, which is right here:


We are using NPOI DLL for this export which is free to use, refer to NPOI NuGet for more details.

More often, we need to develop an export to Excel feature in our applications, many of us usually create boring string builder, then convert it to Excel or use Interop or ITextSharp or NPOI or something else to achieve the same result.

All the above-listed ways are absolutely correct in their own context and fine, but what if there could be a way to export to Excel as simple as passing an object and getting the required output, our life would have been easier, isn't it?

This is what I am going to explain in this article.

Using the Code

First of all, there is a utility function called Export() which simply converts your C# List object to NPOI object and then finally provides to HttpResponseMessage type, which can be used in your WEB API Action.

You need 2 files to achieve it - refer to the solution attached in this article for a better understanding >> ExcelExport folder in Root directory:

  1. AbstractDataExport.cs - contains common code
  2. AbstractDataExportBridge.cs - converts List to NPOI Excel object

What does AbstractDataExport.cs do?

Refer to Export(List exportData, string fileName, string sheetName = DefaultSheetName)

Let's begin with our first file, AbstractDataExport.cs

Creating a New Excel object - _workbook = new XSSFWorkbook();

  1. Creating a New Excel Sheet object - _sheet = _workbook.CreateSheet(_sheetName);
  2. Invokes WriteData() - explained later
  3. Finally, creating and returning MemoryStream object
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;

namespace GenericExcelExport.ExcelExport
    public interface IAbstractDataExport
        HttpResponseMessage Export(List exportData, string fileName, string sheetName);

    public abstract class AbstractDataExport : IAbstractDataExport
        protected string _sheetName;
        protected string _fileName;
        protected List _headers;
        protected List _type;
        protected IWorkbook _workbook;
        protected ISheet _sheet;
        private const string DefaultSheetName = "Sheet1";

        public HttpResponseMessage Export
              (List exportData, string fileName, string sheetName = DefaultSheetName)
            _fileName = fileName;
            _sheetName = sheetName;

            _workbook = new XSSFWorkbook(); //Creating New Excel object
            _sheet = _workbook.CreateSheet(_sheetName); //Creating New Excel Sheet object

            var headerStyle = _workbook.CreateCellStyle(); //Formatting
            var headerFont = _workbook.CreateFont();
            headerFont.IsBold = true;

            WriteData(exportData); //your list object to NPOI excel conversion happens here

            var header = _sheet.CreateRow(0);
            for (var i = 0; i < _headers.Count; i++)
                var cell = header.CreateCell(i);
                cell.CellStyle = headerStyle;
                // It's heavy, it slows down your Excel if you have large data                

            using (var memoryStream = new MemoryStream()) //creating memoryStream
                var response = new HttpResponseMessage(HttpStatusCode.OK)
                    Content = new ByteArrayContent(memoryStream.ToArray())

                response.Content.Headers.ContentType = new MediaTypeHeaderValue
                response.Content.Headers.ContentDisposition = 
                       new ContentDispositionHeaderValue("attachment")
                    FileName = $"{_fileName}_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"

                return response;

        //Generic Definition to handle all types of List
        public abstract void WriteData(List exportData);

Now, let's proceed towards our second and final file, i.e., AbstractDataExportBridge.cs. Below is the explanation for WriteData(List exportData):

  1. Converts List to DataTable
  2. Reflection to read property name, your column header will be coming from here
  3. Loop through DataTable to create Excel Rows

There are areas of improvement here, you can make necessary changes like removing DataTable completely.

using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text.RegularExpressions;

namespace GenericExcelExport.ExcelExport
    public class AbstractDataExportBridge : AbstractDataExport
        public AbstractDataExportBridge()
            _headers = new List<string>();
            _type = new List<string>();

        public override void WriteData<T>(List<T> exportData)
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));

            DataTable table = new DataTable();

            foreach (PropertyDescriptor prop in properties)
                var type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? 
                string name = Regex.Replace(prop.Name, "([A-Z])", " $1").Trim();
                //name by caps for header

            foreach (T item in exportData)
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;

            IRow sheetRow = null;

            for (int i = 0; i < table.Rows.Count; i++)
                sheetRow = _sheet.CreateRow(i + 1);

                for (int j = 0; j < table.Columns.Count; j++)
                    ICell Row1 = sheetRow.CreateCell(j);
                    string cellvalue = Convert.ToString(table.Rows[i][j]);
                    // TODO: move it to switch case

                    if (string.IsNullOrWhiteSpace(cellvalue))
                    else if (_type[j].ToLower() == "string")
                    else if (_type[j].ToLower() == "int32")
                    else if (_type[j].ToLower() == "double")
                    else if (_type[j].ToLower() == "datetime")
                             (table.Rows[i][j]).ToString("dd/MM/yyyy hh:mm:ss"));

Points of Interest

I came across this solution when I had over 20 forms to provide Excel export feature, and I wasn't willing to use a traditional approach which will be lengthy in my case.

There are always areas of improvement in all things. If you see any areas of improvement here, please update in the comments.


  • 27th April, 2018: Initial draft
  • 12th August, 2019: Performance improvement based on new findings in my applications


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

No Biography provided

Comments and Discussions

QuestionHow to write passwors protect excel file using npoi. Net Pin
zaki8228-Nov-19 10:29
Memberzaki8228-Nov-19 10:29 
QuestionHow can we generate multiples sheets using a large list of data? Pin
Member 1189865529-Aug-19 23:03
MemberMember 1189865529-Aug-19 23:03 
AnswerRe: How can we generate multiples sheets using a large list of data? Pin
Mohammed Dawood Ansari8-Sep-19 2:58
MemberMohammed Dawood Ansari8-Sep-19 2:58 
Questionsource code Not available Pin
Member 1452810313-Jul-19 3:11
MemberMember 1452810313-Jul-19 3:11 
AnswerRe: source code Not available Pin
Mohammed Dawood Ansari8-Sep-19 2:57
MemberMohammed Dawood Ansari8-Sep-19 2:57 
GeneralCode unavailable Pin
Member 1390005213-May-19 6:50
MemberMember 1390005213-May-19 6:50 
GeneralRe: Code unavailable Pin
Mohammed Dawood Ansari8-Sep-19 2:57
MemberMohammed Dawood Ansari8-Sep-19 2:57 
QuestionUsage Pin
Member 1278947525-Oct-18 4:59
MemberMember 1278947525-Oct-18 4:59 
QuestionWrite Multiple List Pin
Ahmad Aldali24-Oct-18 5:14
MemberAhmad Aldali24-Oct-18 5:14 
AnswerRe: Write Multiple List Pin
Mohammed Dawood Ansari8-Sep-19 2:53
MemberMohammed Dawood Ansari8-Sep-19 2:53 
Generaltry Pin
Member 139707472-Sep-18 21:48
MemberMember 139707472-Sep-18 21:48 
GeneralRe: try Pin
Mohammed Dawood Ansari8-Sep-19 4:30
MemberMohammed Dawood Ansari8-Sep-19 4:30 
QuestionWhy abstract class Pin
Mou_kol13-May-18 22:27
MemberMou_kol13-May-18 22:27 
AnswerRe: Why abstract class Pin
Mohammed Dawood Ansari8-Sep-19 2:49
MemberMohammed Dawood Ansari8-Sep-19 2:49 
QuestionNew NPOI location on GetHub Pin
cvogt6145730-Apr-18 8:17
Membercvogt6145730-Apr-18 8:17 
SuggestionImprove the codes Pin
Paulus Koshivi28-Apr-18 13:52
MemberPaulus Koshivi28-Apr-18 13:52 
GeneralRe: Improve the codes Pin
Mohammed Dawood Ansari11-May-18 1:29
MemberMohammed Dawood Ansari11-May-18 1:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Posted 28 Apr 2018


32 bookmarked