Exporting Generic List<T> to Excel in C# Using Interop

Introduction

I would like to share a utility that can be used to export a Generic List to an Excel file using C#.

We have multiple approaches to do this.

In ASP.NET we can do this by changing the Content Type to xls but in C# we might have other approaches.

Through this article we will learn how to export a Generic List<T> to Excel using Interop.

Code and Steps

Here in this application we will use a Sample data table and then will learn how to export data to an Excel file.

We will learn the following things in this article:

1. Creating Excel file using C#
2. Writing data to cells
3. Formatting data to cells
4. Working with Excel range

1. Adding Refrences

First we need to added a reference of Microsoft.office.interop.Excel



2. Adding Sample class to Project.

public class Emp

{

    public int Empid { get; set; }

    public string Empname {get; set;}

    public string City {get; set;}

}

3. Adding Function that returns a List of employees

Sample Code

static List<Emp> GetEmpList()

{

     List<Emp> list = new List<Emp>();

     Emp e = new Emp();

     e.Empid=2001;

     e.Empname="DEVESH";

     e.City="NOIDA";

      list.Add(e);

      e = new Emp();

      e.Empid=2002;

      e.Empname="NIKHIL";

      e.City="DELHI";

      list.Add(e);

      e = new Emp();

      e.Empid=2003;

      e.Empname="AVINASH";

      e.City="NAGPUR";

      list.Add(e);

       e = new Emp();

       e.Empid=2004;

       e.Empname="SHRUTI";

       e.City="NOIDA";

       list.Add(e);

       e = new Emp();

       e.Empid = 2004;

       e.Empname = "ROLI";

       e.City = "KANPUR";

       list.Add(e);           

       return list;

 }

4. Converting Generic List to Datatable

In this code we are first converting the List to a datatable then we will export the datatable to an Excel file.

public DataTable ConvertToDataTable<T>(IList<T> data)

{

        PropertyDescriptorCollection properties =

        TypeDescriptor.GetProperties(typeof(T));

        DataTable table = new DataTable();

        foreach (PropertyDescriptor prop in properties)

table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);

        foreach (T item in data)

        {

            DataRow row = table.NewRow();

            foreach (PropertyDescriptor prop in properties)

                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;

             table.Rows.Add(row);

        }

        return table;

}

This function accepts a List as an argument and returns it as a datatable.

5. We created Separate class file for generating Excel (Excelutlity.cs)

6. Creation of Excel objects

Microsoft.Office.Interop.Excel.Application Excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;

I have attached sample code for more details.

7. Initialization of Excel objects

// Start Excel and get Application object.

excel = new Microsoft.Office.Interop.Excel.Application();

 

// for making Excel visible

excel.Visible = false;

excel.DisplayAlerts = false;

 

// Creation a new Workbook

excelworkBook = excel.Workbooks.Add(Type.Missing);

 

// Workk sheet

excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;

excelSheet.Name = "Test work sheet";

8. Writing to Excel file

excelSheet.Cells[1, 1] = “Sample test data”;
excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();

9. Working with range and formatting Excel cells

// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();

Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;

10. Coloring the cells

We added a function for formatting and coloring Excel cells.

public void FormattingExcelCells(Microsoft.Office.Interop.Excel.Range range, string HTMLcolorCode, System.Drawing.Color fontColor, bool IsFontbool)

{

    range.Interior.Color = System.Drawing.ColorTranslator.FromHtml(HTMLcolorCode);

    range.Font.Color = System.Drawing.ColorTranslator.ToOle(fontColor);

    if (IsFontbool == true)

    {

        range.Font.Bold = IsFontbool;

    }

}

11. Running the application

We are binding a datagrid at the load of a Form.



After clicking on Export to Excel we will have our Excel file as in the following screen.
You need to modify the file out path in the attached code.



Conclusion

The attached application can be used in projects for reporting features.