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))
                Directory.CreateDirectory(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;

            try
            {
                // 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
                    range.EntireColumn.AutoFit();
                }

                // Save workbook
                workbook.SaveAs(
                    string.Format("{0}{1}", new object[] {filePath, fileName}), 
                    Excel.XlFileFormat.xlWorkbookNormal);
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                // Close
                sheet = null;
                workbook.Close();
                workbook = null;
                excel.Quit();
            }

            // Clean up 
            // NOTE: When in release mode, this does the trick 
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
        }

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

Notes

• 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