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 a Excel Sheet.

Background:

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

If you've any doubt, please post your questions. If you really like this article, please share it.

Don’t forget to Vote or Comment about my writing.

About these ads

One thought on “Map data from Database Context to Excel Sheet using LINQ & Microsoft.Office.Interop.Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s