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:
- Creating Excel file using C#
- Writing data to cells
- Formatting data to cells
- Working with Excel range
1. Adding Refrences
First we need to added a reference of Microsoft.office.interop.Excel
![Adding Refrences]()
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.
![datagrid]()
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.
![modify file]()
Conclusion
The attached application can be used in projects for reporting features.