Map Data from Database Context to Excel Sheet using LINQ & Microsoft.Office.Interop.Excel

Introduction

Using Microsoft.Office.Interop.Excel library, you can bring your data from your database against LINQ query and store them into an Excel Sheet.

Background

First of all, you have to create the Entity Framework Model of the table on which you want to do the LINQ operation. It is a function, takes four arguments:

1. Excel file path name
2. Excel sheet name
3. Result against LINQ query as IQueryable
4. Entity Framework Model object

Using the Code

using System;
using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data.Objects;
using System.Data.EntityClient;
using System.Linq;

public void EntityToExcelSheet(string excelFilePath,
       string sheetName, IQueryable result, ObjectContext ctx)
{
    Excel.Application oXL;
    Excel.Workbook oWB;
    Excel.Worksheet oSheet;
    Excel.Range oRange;
    try
    {
        // Start Excel and get Application object.
        oXL = new Excel.Application();

        // Set some properties
        oXL.Visible = true;
        oXL.DisplayAlerts = false;

        // Get a new workbook.
        oWB = oXL.Workbooks.Add(Missing.Value);

        // Get the active sheet
        oSheet = (Excel.Worksheet)oWB.ActiveSheet;
        oSheet.Name = sheetName;

        // Process the DataTable
        // BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE
        DataTable dt = EntityToDataTable(result, ctx);

        int rowCount = 1;
        foreach (DataRow dr in dt.Rows)
        {
            rowCount += 1;
            for (int i = 1; i < dt.Columns.Count + 1; i++)
            {
                // Add the header the first time through
                if (rowCount == 2)
                    oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
            }
        }

        // Resize the columns
        oRange = oSheet.Range[oSheet.Cells[1, 1], oSheet.Cells[rowCount, dt.Columns.Count]];
        oRange.Columns.AutoFit();

        // Save the sheet and close
        oSheet = null;
        oRange = null;
        oWB.SaveAs(excelFilePath, Excel.XlFileFormat.xlWorkbookNormal, Missing.Value,
          Missing.Value, Missing.Value, Missing.Value,
          Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value,
          Missing.Value, Missing.Value, Missing.Value);
        oWB.Close(Missing.Value, Missing.Value, Missing.Value);
        oWB = null;
        oXL.Quit();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

public DataTable EntityToDataTable(IQueryable result, ObjectContext ctx)
{
    try
    {
        EntityConnection conn = ctx.Connection as EntityConnection;
        using (SqlConnection SQLCon = new SqlConnection(conn.StoreConnection.ConnectionString))
        {
            ObjectQuery query = result as ObjectQuery;
            using (SqlCommand Cmd = new SqlCommand(query.ToTraceString(), SQLCon))
            {
                foreach (var param in query.Parameters)
                {
                    Cmd.Parameters.AddWithValue(param.Name, param.Value);
                }
                using (SqlDataAdapter da = new SqlDataAdapter(Cmd))
                {
                    using (DataTable dt = new DataTable())
                    {
                        da.Fill(dt);
                        return dt;
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

How to call EntityToExcelSheet()

using (DebopamDBEntities db = new DebopamDBEntities())
{
    var query = db.Employees.Select(i => i).AsQueryable();
    try
    {
        EntityToExcelSheet("E:\\Employees.xls", "Employees", query, db);
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error: " + ex.Message,
          "Error Creating Excel File", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}