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

Stats

36.9K views
9 bookmarked
Posted 31 Oct 2013
Licenced CPOL

Entity Model to Excel using LINQ in C#

, 31 Oct 2013
Rate this:
Please Sign up or sign in to vote.
How to bring data from Entity model to Excel file using LINQ.

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

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

 
Questionvb.net version Pin
Member 1075461114-May-14 12:18
memberMember 1075461114-May-14 12:18 

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
Web01-2016 | 2.8.181113.4 | Last Updated 31 Oct 2013
Article Copyright 2013 by Debopam Pal
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid