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

public class UserManager
{
    public string Id { get; set; }
    public string Username { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Password { get; set; }
    public DateTime CreatedOn { get; set; }
}

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.

public static List<UserManager> SeedData()
{
 return new List<UserManager>
 {
  new UserManager
  {
   Id = Guid.NewGuid().ToString(),
   FirstName = "Test 1",
   LastName = "Test 1",
   Username = "TestUsername1",
   Password = "123",
   CreatedOn = DateTime.UtcNow
  },
  new UserManager
  {
   Id = Guid.NewGuid().ToString(),
   FirstName = "Test 2",
   LastName = "Test 2",
   Username = "TestUsername2",
   Password = "123",
   CreatedOn = DateTime.UtcNow
  },
  new UserManager
  {
   Id = Guid.NewGuid().ToString(),
   FirstName = "Test 3",
   LastName = "Test 3",
   Username = "TestUsername3",
   Password = "123",
   CreatedOn = DateTime.UtcNow
  },new UserManager
  {
   Id = Guid.NewGuid().ToString(),
   FirstName = "Test 4",
   LastName = "Test 4",
   Username = "TestUsername4",
   Password = "123",
   CreatedOn = DateTime.UtcNow
  }
 };
}

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).

// T is a generic class
static DataTable ConvertToDataTable<T>(List<T> models)
{

 // 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) ..

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

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.

public static void GenerateExcel(DataTable dataTable, string path)
{ 
 DataSet dataSet = new DataSet();
 dataSet.Tables.Add(dataTable);
}

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

using Excel = Microsoft.Office.Interop.Excel;
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

// excelWorkBook.Save(); -> this will save to its default location
excelWorkBook.SaveAs(path); // -> this will do the custom
excelWorkBook.Close();
excelApp.Quit();

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.

static void Main(string[] args)
{
 var userList = UserManager.SeedData();
 try
 {
  Console.WriteLine("Please select a operation to do ..");
  Console.WriteLine("1. Export Data as EXCEL");
  int command = Convert.ToInt32(Console.ReadLine());
  switch (command)
  {
   case 1:
    string fileName = "UserManager.xlsx";
    Console.WriteLine("Please give a location to save :");
    string location = Console.ReadLine();
    string customExcelSavingPath = location + "\\" + fileName;
    ExcelExport.GenerateExcel(ConvertToDataTable(userList), customExcelSavingPath);
    break;
   default:
    break;
  }
 }
 catch (Exception ex)
 {
  throw ex;
 }
}

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

I have uploaded all of this to this repository.

Happy Coding.