Export Model List To Excel In C# (Using Interop) - The Easy Way!

There are many ways to generate Excel files in C# using a  third party library but I am going to show you how to export Excel files from your entity without using any third party, with the help of Interop.

 

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

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 set of dummy datas. 

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.

 

We 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 use strongly 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 looks like this,

 

 

Now the fun part begins. To export these datatables into an 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. Take two parameters. 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 created an Excel app , Excel worksheet, workbook and added all of our rows and colums to it.

 

To keep it short we have to rename 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 it's time for saving. 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 is 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 operations in 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 that's it!

 

So our main program.cs file should look like this.

 

 

I have uploaded all of this to this repository.

 

Happy Coding.