Click here to Skip to main content
13,828,551 members
Click here to Skip to main content
Add your own
alternative version

Stats

17.1K views
29 bookmarked
Posted 28 Apr 2018
Licenced CPOL

Export to Excel using NPOI - C# and WEB API

, 15 Nov 2018
Rate this:
Please Sign up or sign in to vote.
"How to export the List into an Excel file" in a generic way using NPOI

Introduction

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

Before We Begin the Journey

The main concept of this article is for you to import NPOI and add below explained 2 cs files included in the sample and then jet set go.

Download the source code GenericExcelExport.7z

Background

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

Regularly, we require 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 to achieve the same result.

All the above listed ways are absolutely correct and fine, but what if there could be a way to export to Excel as simple as passing an object and getting 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 more better understanding >> ExcelExport Folder in Root Directory:

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

Let's begin with our first file, AbstractDataExport.cs - What does it do? Refer to Export(List exportData, string fileName, string sheetName = DefaultSheetName)

  1. Creating New Excel object - _workbook = new XSSFWorkbook();
  2. Creating New Excel Sheet object - _sheet = _workbook.CreateSheet(_sheetName);
  3. Invokes WriteData() - explained later
  4. 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;
            headerStyle.SetFont(headerFont);

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

            //Header
            var header = _sheet.CreateRow(0);
            for (var i = 0; i < _headers.Count; i++)
            {
                var cell = header.CreateCell(i);
                cell.SetCellValue(_headers[i]);
                cell.CellStyle = headerStyle;
            }

            for (var i = 0; i < _headers.Count; i++)
            {
                _sheet.AutoSizeColumn(i);
            }

            using (var memoryStream = new MemoryStream()) //creating memoryStream
            {
                _workbook.Write(memoryStream);
                var response = new HttpResponseMessage(HttpStatusCode.OK)
                {
                    Content = new ByteArrayContent(memoryStream.ToArray())
                };

                response.Content.Headers.ContentType = new MediaTypeHeaderValue
                       ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                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;
                _type.Add(type.Name);
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? 
                                  prop.PropertyType);
                string name = Regex.Replace(prop.Name, "([A-Z])", " $1").Trim(); //space separated 
                                                                           //name by caps for header
                _headers.Add(name);
            }

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

            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 type = _type[j].ToLower();
                    var currentCellValue = table.Rows[i][j];

                    if (currentCellValue != null && 
                        !string.IsNullOrEmpty(Convert.ToString(currentCellValue)))
                    {
                        if (type == "string")
                        {
                            Row1.SetCellValue(Convert.ToString(currentCellValue));
                        }
                        else if (type == "int32")
                        {
                            Row1.SetCellValue(Convert.ToInt32(currentCellValue));
                        }
                        else if (type == "double")
                        {
                            Row1.SetCellValue(Convert.ToDouble(currentCellValue));
                        }
                    }
                    else
                    {
                        Row1.SetCellValue(string.Empty);
                    }
                }
            }
        }
    }
}

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.

History

  • 27th April, 2017: Initial draft

License

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

Share

About the Author

No Biography provided

You may also be interested in...

Comments and Discussions

 
QuestionUsage Pin
Member 1278947525-Oct-18 5:59
memberMember 1278947525-Oct-18 5:59 
QuestionWrite Multiple List Pin
Ahmad Aldali24-Oct-18 6:14
memberAhmad Aldali24-Oct-18 6:14 
Generaltry ZetExcel.com Pin
Member 139707472-Sep-18 22:48
memberMember 139707472-Sep-18 22:48 
QuestionWhy abstract class Pin
Mou_kol13-May-18 23:27
memberMou_kol13-May-18 23:27 
QuestionNew NPOI location on GetHub Pin
cvogt6145730-Apr-18 9:17
membercvogt6145730-Apr-18 9:17 
SuggestionImprove the codes Pin
Paulus Koshivi28-Apr-18 14:52
memberPaulus Koshivi28-Apr-18 14:52 
GeneralRe: Improve the codes Pin
Mohammed Dawood Ansari11-May-18 2:29
memberMohammed Dawood Ansari11-May-18 2: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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05 | 2.8.190114.1 | Last Updated 15 Nov 2018
Article Copyright 2018 by Mohammed Dawood Ansari
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid