Entity Model to Excel using LINQ in C#

Introduction

It brings all/selected data (specified by LINQ query) of entity model into specific 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.

Using the code

It is a function, takes four arguments: Excel file path name, Excel sheet name, Result against LINQ query as IQueryable and Entity Framework Model object.

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 the method:

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);
    }
}