Exporting Generic Lists to Excel Files with NPOI in ASP.NET

NPOI is a powerful .NET library that allows developers to create and manipulate Microsoft Excel files without requiring Excel to be installed on the server. This guide demonstrates how to convert a generic list to an Excel file and save it to a specified directory.

Converting List<T> to DataTable

Before exporting, you need a utility method to transform your generic list into a DataTable structure:

public static DataTable ConvertToDataTable<T>(List<T> data)
{
    DataTable table = new DataTable();
    
    PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    
    foreach (PropertyInfo property in properties)
    {
        table.Columns.Add(property.Name);
    }
    
    foreach (T item in data)
    {
        object[] rowValues = new object[properties.Length];
        for (int index = 0; index < properties.Length; index++)
        {
            rowValues[index] = properties[index].GetValue(item, null);
        }
        table.Rows.Add(rowValues);
    }
    
    return table;
}

This method uses reflection to inspect the properties of type T and automatically creates corresponding columns in the DataTable. Each object in the list is then converted to a row with values extracted via proprety getters.

Generating Excel Files

The following code exports a DataTable to an Excel file using NPOI's HSSF (Horrible Spreadsheet Format) implementation:

DataTable sourceData = ConvertToDataTable<EmpExtension>(employeeList);

HttpResponse response = HttpContext.Current.Response;
response.ContentType = "application/vnd.ms-excel";
response.ContentEncoding = Encoding.UTF8;
response.Charset = "UTF-8";
response.AppendHeader("Content-Disposition", 
    "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8));

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet worksheet = (HSSFSheet)workbook.CreateSheet();

// Create header row
HSSFRow headerRow = (HSSFRow)worksheet.CreateRow(0);
foreach (DataColumn column in sourceData.Columns)
{
    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}

// Populate data rows
for (int rowIndex = 0; rowIndex < sourceData.Rows.Count; rowIndex++)
{
    HSSFRow dataRow = (HSSFRow)worksheet.CreateRow(rowIndex + 1);
    for (int colIndex = 0; colIndex < sourceData.Columns.Count; colIndex++)
    {
        dataRow.CreateCell(colIndex).SetCellValue(sourceData.Rows[rowIndex][colIndex].ToString());
    }
}

using (MemoryStream memoryStream = new MemoryStream())
{
    using (FileStream fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write))
    {
        workbook.Write(fileStream);
    }
    memoryStream.Flush();
    response.BinaryWrite(memoryStream.GetBuffer());
}

Key Components

HSSFWorkbook and HSSFSheet: These classes represent the Excel workbook and individual worksheets respectively. The workbook serves as the container for all sheets and data.

Header Row: The first row (index 0) is reserved for column headers, which are derived from the DataTable's column names.

Data Rows: All subsequent rows contain the actual data from the source DataTable, with each cell converted to a string representation.

File Handling: The FileStream writes the workobok directly to disk at the specified path, while the MemoryStream buffers the data for HTTP response transmission. This dual approach ensures both file persistence and browser download capability.

Content Disposition Header: The attachment disposition triggers a file download dialog in the browser, with UrlEncode handling special characters in the filename.

Alternative: Using HttpContext.Current

In scenarios where the context parameter isn't directly available, you can access the HTTP context statically:

HttpContext.Current.Response

This static accessor provides the same functionality as the instance-based context.Response property, making the code compatible with various application architectures.

Notes

• The strFileName variable should contain the complete file path including directory and filename with extension
• NPOI handles Excel 97-2003 format (.xls). For newer .xlsx formats, use the XSSFWorkbook class instead
• Ensure the application has write permissions to the target directory