Click here to Skip to main content
13,764,207 members
Click here to Skip to main content
Add your own
alternative version

Stats

7.4K views
3 bookmarked
Posted 16 Nov 2013
Licenced CPOL

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

, 16 Nov 2013
Rate this:
Please Sign up or sign in to vote.
Using Microsoft.Office.Interop.Excel library, you can bring your data from your database against LINQ query and store them into an Excel Sheet.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Debopam Pal
Software Developer National Informatics Centre (NIC)
India India
Hello! Myself Debopam Pal. I've completed my MCA degree from West Bengal University of Technology at 2013. I'm from India. I’ve started to work with MS Technologies in 2013 specially in C# 4.0, ASP.NET 4.0. I've also worked in PHP 5. Now I work in JAVA/J2EE, Struts2. Currently I'm involved in a e-Governance Project since Jan, 2014. In my leisure time I write Blog, Articles as I think that every developer should contribute something otherwise resource will be finished one day. Thank you for your time.

Visit: Linkedin Profile | Facebook Profile | Google+ Profile | CodeProject Profile

You may also be interested in...

Pro
Pro

Comments and Discussions

 
Questionalmost OK =) Pin
davethewave197720-Nov-13 22:49
professionaldavethewave197720-Nov-13 22:49 
GeneralRe: almost OK =) Pin
Debopam Pal27-Nov-13 4:34
professionalDebopam Pal27-Nov-13 4:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05-2016 | 2.8.181113.4 | Last Updated 16 Nov 2013
Article Copyright 2013 by Debopam Pal
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid