Export a Custom List to Excel

Introduction

Exporting data to Excel is a common feature required in applications that need reporting facilities. Today, almost all grid control libraries provide an in-built feature to export the grid data to Excel. But still we need to write algorithms to do so in non-UI control based scenarios. This code snippet exports a list of objects to Excel using the Microsoft Office libraries. It uses reflection to define the column headers and extract the data from the collection.

Background

Many examples I came across converted the data to a table format before exporting it to Excel. When working in applications, we generally use List or Collections instead of "Data Tables". Hence, I wanted a solution which could export data to Excel and yet be generic to work on any type of collection.

Using the Code

To do the export, I have used the Microsoft.Office.Interop.Excel nuget packages.

Define a generic implementation for ExcelExporter class by defining it like "static class ExcelExporter<T>" . Here, T can be any class you want to export to Excel.

Add a static method which takes a List of the generic type as the input:

"public static void ExportDataToExcel(List<T> result)"

We start by creating the Excel application and adding the Workbook to it. The ActiveSheet is the sheet we will add our data to:

var excel = new Application();
var excelworkBook = excel.Workbooks.Add();
var excelSheet = (Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = "DataSheet";

We extract the properties from the List using the following reflection code:

result[0].GetType().GetProperties()

It will return us the list of properties in the object that needs to be exported to Excel. The name of the property will become our header and can be written to Excel using the following code:

foreach (var propInfo in result[0].GetType().GetProperties())
{
    excelSheet.Cells[1, col] = propInfo.Name;
    excelSheet.Cells[1, col].Font.Bold = true;
    col++;
}

For extracting the values from the properties using reflection, we use the propInfo.GetValue method.

foreach (var item in result)
{
    int j = 1;
    foreach (var propInfo in item.GetType().GetProperties())
    {
        try
        {
            excelSheet.Cells[k + 2, j].Value = propInfo.GetValue(item);
             j++;
        }
        catch (System.Runtime.InteropServices.COMException comex)
        {
            Console.WriteLine(string.Format("{0}, caused for exporting value - {1}",
                comex.Message, propInfo.GetValue(item)));
            excelSheet.Cells[k + 2, j].Value = $"'{propInfo.GetValue(item)}'";
            j++;
            continue;
        }
    }
    k++;
}

In the above snippet, we use the data extraction in a try catch block because certain values/characters are not supported in Excel and hence throw the COMException. Such values can be written to Excel by encapsulating it in single qoutes like $"'{propInfo.GetValue(item)}'".

At the end, we need to save the created Excel in the file system using the following code snippet:

var folderPath = "<YourFolderPath>";
if (!Directory.Exists(folderPath))
    Directory.CreateDirectory(folderPath);
var filePath = $"{folderPath}\\<YourFileName>.xlsx";
excelworkBook.Close(true, filePath);

The save is achieved by calling excelworkBook.Close(true, filePath) with the save parameter as true.

Below is the complete class implementation:

using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;

namespace GitHubReportGenerator
{
    static class ExcelExporter<T>
    {        
        public static void ExportDataToExcel(List<T> result)
        {
            var excel = new Application();
            var excelworkBook = excel.Workbooks.Add();
            var excelSheet = (Worksheet)excelworkBook.ActiveSheet;
            excelSheet.Name = "DataSheet";
            
            try
            {
                //create the column(s) header(s)
                int col = 1;

                foreach (var propInfo in result[0].GetType().GetProperties())
                {
                    excelSheet.Cells[1, col] = propInfo.Name;
                    excelSheet.Cells[1, col].Font.Bold = true;
                    col++;
                }

                //put the actual data
                int k = 0;

                foreach (var item in result)
                {
                    int j = 1;
                    foreach (var propInfo in item.GetType().GetProperties())
                    {
                        try
                        {
                            excelSheet.Cells[k + 2, j].Value = propInfo.GetValue(item);
                            j++;
                        }
                        catch (System.Runtime.InteropServices.COMException comex)
                        {
                            Console.WriteLine(string.Format("{0}, 
                                              caused for exporting value - {1}",
                                comex.Message, propInfo.GetValue(item)));
                            excelSheet.Cells[k + 2, j].Value = $"'{propInfo.GetValue(item)}'";
                            j++;
                            continue;
                        }
                    }
                    k++;
                }
                var folderPath = "<YourFolderPath>";
                if (!Directory.Exists(folderPath))
                    Directory.CreateDirectory(folderPath);
                var filePath = $"{folderPath}\\<YourFileName>.xlsx";
                excelworkBook.Close(true, filePath);
                Console.WriteLine($"Exported Successfully to {filePath}");
            }
            catch (Exception ex)
            {
                excelworkBook.Close(false);
                Console.WriteLine(ex.Message);
                Console.WriteLine("Export Failed.");
            }
        }
    }
}

The above ExportDataToExcel method can be invoked using the below code snippet:

var dataToExport= new List<ClassToExport>();
ExcelExporter<ClassToExport>.ExportDataToExcel(dataToExport);

History

• 18^th September, 2020: Initial version