Export to Excel using NPOI - C# and WEB API

• Download sample - 650.6 KB

Introduction

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:

• https://github.com/ansaridawood/.NET-Generic-Excel-Export-Sample

Background

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;
            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;
                // It's heavy, it slows down your Excel if you have large data                
                //_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();
                //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 cellvalue = Convert.ToString(table.Rows[i][j]);
                    
                    // TODO: move it to switch case

                    if (string.IsNullOrWhiteSpace(cellvalue))
                    {
                        Row1.SetCellValue(string.Empty);
                    }
                    else if (_type[j].ToLower() == "string")
                    {
                        Row1.SetCellValue(cellvalue);
                    }
                    else if (_type[j].ToLower() == "int32")
                    {
                        Row1.SetCellValue(Convert.ToInt32(table.Rows[i][j]));
                    }
                    else if (_type[j].ToLower() == "double")
                    {
                        Row1.SetCellValue(Convert.ToDouble(table.Rows[i][j]));
                    }
                    else if (_type[j].ToLower() == "datetime")
                    {
                        Row1.SetCellValue(Convert.ToDateTime
                             (table.Rows[i][j]).ToString("dd/MM/yyyy hh:mm:ss"));
                    }
                    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

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