Exporting a View or Stored Procedure to Excel with Entity Framework

The other day I wanted to run a Stored Procedure in Entity Framework, and dump the results into Excel.

Since Entity Framework works with Entity Objects, I needed some code that would take a list of classes like this

public class Person
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime DOB { get; set; }

and turn it into this

Id Name DOB
1 Rachel Lim 6/30/1985
2 Joe Smith 3/30/1958
3 Sarah Spencer 2/21/1964

I didn’t see anything existing which would help me with this, so came up with my own helper class. It takes a list of objects, and writes them to an excel file. Each object is on a separate row, and the object properties are in the columns.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExportObjectsToExcel
    public class ExcelHelpers
        /// <summary>
        /// Exports a list of objects to Excel. Objects go in the Rows while Object Properties go in the Columns
        /// </summary>
        /// <param name="objects">List of objects to export.</param>
        /// <param name="filePath">Location to save file to. Does not need to exist</param>
        /// <param name="fileName">Name of excel file</param>
        public static void ExportToExcel<T>(IEnumerable<T> objects, string filePath, string fileName)
            // Add \ to end of file name if it doesn't exist. Just want to be consistant
            if (!filePath.EndsWith(@"\"))
                filePath += @"\";

            // Create directory if it doesn't exist
            if (!Directory.Exists(filePath))

            // Start Excel and get Application object. 
            Excel.Application excel = new Excel.Application();

            // Set it hidden and hide alerts
            excel.Visible = false;
            excel.DisplayAlerts = false;

            // Create a new workbook. 
            Excel.Workbook workbook = excel.Workbooks.Add();

            // Get the active sheet 
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.ActiveSheet;

                // Convert the list into a rectangular array that Excel can read
                var data = GetObjectArray<T>(objects);

                // If at least one record got converted successfully
                if (data.Length > 1)
                    // Get the range of cells that the data will go into. Size matches rectangular array size
                    string xlsRange = string.Format("A1:{0}{1}",
                        new object[] { GetExcelColumn(data.GetLength(1)), data.GetLength(0) });

                    // Insert data into the specified range of cells
                    Excel.Range range = sheet.get_Range(xlsRange);
                    range.Value = data;

                    // Auto-Fit the columns

                // Save workbook
                    string.Format("{0}{1}", new object[] {filePath, fileName}), 
            catch(Exception ex)
                // Close
                sheet = null;
                workbook = null;

            // Clean up 
            // NOTE: When in release mode, this does the trick 

        /// <summary>
        /// Takes a List of objects objects and converts the objects and their properties into a rectangular array of objects
        /// </summary>
        /// <param name="objects">List of objects to flatten</param>
        /// <returns>Rectangular array where objects are stored in [0] and properties are stored in [1]</returns>
        private static object[,] GetObjectArray<T>(IEnumerable<T> objects)
            // Get list of object properties
            PropertyInfo[] properties = typeof(T).GetProperties();

            // Create rectangular array based on # of objects and # of object properties
            object[,] data = new object[objects.Count() + 1, properties.Length];

            // Loop through properties on object
            for (int j = 0; j < properties.Count(); j++)
                // Write the property name into the first row of the array
                data[0, j] = properties[j].Name.Replace("_", " ");

                // Loop through objects and write out the specified property of each one into the array
                for (int i = 0; i < objects.Count(); i++)
                    data[i + 1, j] = properties[j].GetValue(objects.ElementAt(i), null);

            // Return rectangular array
            return data;

        /// <summary>
        /// Takes an Integer and converts it into Excel's column header code.
        /// For example: 1 = A; 2 = B; 27 = AA;
        /// </summary>
        /// <param name="colNumber">Number of Column in Excel. 1 = A</param>
        /// <returns>string that Excel can use</returns>
        private static string GetExcelColumn(int colNumber)
            // If value is zero or less, return an empty string
            if (colNumber <= 0)
                return string.Empty;

            // If the value is less than or equal to 26 (Z), the column header
            // is only one character long. If it's greater, call this recursively
            // to get the first letter(s) of the column code.
            string first = (colNumber <= 26 ? string.Empty :
                GetExcelColumn((int)Math.Floor((colNumber - 1) / 26.00)));

            // Get the final letter in the column code
            int second = colNumber % 26;
            if (second == 0) second = 26;
            char finalLetter = (char)('A' + second - 1);            // Excel column header is the first part + the final character
            return string.Format("{0}{1}", new object[] { first, finalLetter });

Now all I have to do to export a list of entities is call this helper method and pass it my list of entities, along with the file name and path of the excel file to save it to.

List<MyEntity> list = GetEntityList();
ExcelHelpers.ExportToExcel<MyEntity>(list, filePath, fileName);


  • You’ll need to add a COM reference to the Microsoft Excel Object Library
  • The code was tested with 30,000 records of 53 columns each, and it ran in a couple of seconds
  • Performance for doing a single excel write is much better than looping through data and writting cell-by-cell. See this code project article for more details
  • If you don’t have .Net Framework 4.0 or greater, you’ll need to add Missing.Value parameters to the excel methods. See this article for an example.
  • If you encounter an error or stop debugging after excel has started but before it ends, you may need to go into Task Manager and close EXCEL.EXE manually

17 Responses to Exporting a View or Stored Procedure to Excel with Entity Framework

  1. Csaba Toth says:

    You can save yourself from all of Microsoft Excel COM reference or OpenXML SDK if you write the result in csv format (comma separated values). Excel, LibreOffice, OpenOffice and anything will be able to open it up natively and your solution will be lighter weight, less requirements on the clients (no installed Office is required). It’s also platform independent.

  2. Simon says:

    You might want to consider extending this by allowing various attributes on the entity properties. A few that I found useful where those used by MVC for displaying view models. DisplayName, Formatter, etc… You can then extend this to make it an importer with validation as well.

    Incidentally, I found a couple of libraries which would generate XLSX without Excel automation (I found automating office on a server can be a pain complex when there are multiple concurrent requests running/something causes a modal dialogue). Have a glance at http://epplus.codeplex.com/

    • Rachel says:

      You should post a new question, not update an existing question. Your two questions are different, and new questions get many more views than older ones.

      Also, be sure to include the specific details about what your ItemsSource is and where you’re calling this code from.

  3. ricardo says:

    I tried it like this:
    List grid = dgProjects.ItemsSource as List;

    But it unfortunately returns an empty list

  4. ricardo says:

    Hi Rachel,

    Thanks for the reply! I eventually fixed my enumeration problem by manually adding each row to the list of objects using this code:

    List list = new List();
    foreach (var items in dgProjects.Items)
    var row = dgProjects.ItemContainerGenerator.ContainerFromItem(items) as DataGridRow;

    I then call your helper class like this:

    ExportObjectsToExcel.ExportToExcel(list, dir,File_name);

    Does this seem correct? I don’t seem to get any output?

    • Rachel says:

      That code would output a list of DataGridRow objects to Excel, and you probably want to output your actual data item behind the DataGridRow.

      Does it work if you just cast your DataGrid.ItemsSource as List<MyObject>?

  5. ricardo says:

    Thanks Rachel your blog was very enlightening. The problem being is that it still does not work. This is fault I am getting. The query results can not be enumerated more than once.

    Here is the lines of code that are throwing this exception:

    var query = dgProjects.ItemsSource.Cast();
    List list = query.ToList();

    • Rachel says:

      Hi Ricardo,

      Does the error occur within the ExportObjectsToExcel method, or does it occur in one of the other two lines of code? And if it’s in the Export method, can you tell me what line of code it fails on?

      I’ve used this helper class many times in the past and have not encountered that error, so the problem might have something to do with how you’re casting your ItemsSource.

  6. DucK says:

    Have you managed a solution without a COM library, like Luc Bos said ??

    • Rachel says:

      Nope, I haven’t tried yet because all the servers I’ve worked with have Office installed on it. I’m sure it wouldn’t be that hard to setup though.

  7. Nacho says:

    Great code!.

  8. Luc Bos says:

    To optimize your example you could make use of the OpenXml SDK 2.0. It takes away the dependency of the COM library and it should run significantly faster. When the dependency of the COM library is taken away then this could is great for server side components where it’s not always feasible to install office.

    Nice blog btw🙂

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

%d bloggers like this: