Export model list to Excel in C# (Using Interop) — The easy way!

There are many ways to generate excel file in c# using third party library but I am going to show you to export excel file from your entity without using any third party, with the help of Interop.

For reference we are going to use Microsoft.Office.Interop.Excel packge.

In this demo , I have created a c# console app and to separate Excel Export Operation I have created another class called excelexport.cs

To work on I need a model class and a list of objects to that model. So I have created a Model folder and inside that folder I have created a class called UserManager.cs

But I need to generate a list of objects for this class as I am not using any database for this. So I have created a seed method to generate a sets of dummy data.

So for now my UserManager class looks like this.

Our data is ready and now we need to make our Datatable converter to convert our list to a data table instance.

You can also gather some knowledge about Interop and how to access all the objects. interop and Reflection can help you I think.

I am going to make this as a generic but you can typed as your need. (Also read all the comments in the code, I have tried to write what’s happening on each line).


// creating a data table instance and typed it as our incoming model
// as I make it generic, if you want, you can make it the model typed you want. DataTable dataTable = new DataTable(typeof(T).Name);
//Get all the properties of that model
PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
// Loop through all the properties
// Adding Column name to our datatable
foreach (PropertyInfo prop in Props)
{
//Setting column names as Property names
dataTable.Columns.Add(prop.Name);
}
// Adding Row and its value to our dataTable
foreach (T item in models)
{
var values = new object[Props.Length];
for (int i = 0; i < Props.Length; i++)
{
//inserting property values to datatable rows
values[i] = Props[i].GetValue(item, null);
}
// Finally add value to datatable
dataTable.Rows.Add(values);
}
return dataTable;
}

So as of now our function look like this

Now the fun part begins. To export this datatables into excel object we have to add office interop reference to our project.

Microsoft.Office.Interop.Excel is part of Dotnet assemblies so you just need to attach to your project.

Reference > Assemblies > Extentions > Search Excel > Add the latest version

Now turn over to ExcelExport.cs file and write a static method. I have named it as Generate Excel And take two parameter. The first one is the data table instance that we have just created and a path that we are going to save our excel file(We are going to deal with it later) ..

We are going to use datasets and add our datatable instance to its Table property. If you want to know more about DataSet you can find it here.

after this the process is quite simple. We have create a excel app , excel worksheet, workbook and add all of our row and colums to it.

To keep it short we have to renamed this as

public static void GenerateExcel(DataTable dataTable, string path)
{

DataSet dataSet = new DataSet();
dataSet.Tables.Add(dataTable);
// create a excel app along side with workbook and worksheet and give a name to it Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelWorkBook = excelApp.Workbooks.Add();
Excel._Worksheet xlWorksheet = excelWorkBook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
foreach (DataTable table in dataSet.Tables)
{
//Add a new worksheet to workbook with the Datatable name
Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
excelWorkSheet.Name = table.TableName;
// add all the columns
for (int i = 1; i < table.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
}
// add all the rows
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
}
}
}
}

Now its time for save. So for this we can save it as its default excel saved location that would be -> c:/users/{user-name}/documents/

but for this I have choose to save it my custom location

So our excel export class looks like this

Now we have to call this method from our main method. To make this prettier I have included some operation to it.

And thats it ! So our main program.cs file should look like this

I have uploaded all of this to this repository.

Happy Coding.

if you like the content, you know what to do 🎉✔