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

Communication between ViewModels with MVVM

Communication between ViewModels can be tricky at first glance, but there are some easy frameworks and patterns that can help you out.

For example, lets say you had a ShellViewModel that controlled your entire application, and it has an Exit command. Now lets say one of the sub-view-models in the application would like to call this exit command.

The easiest way is through a Relative Binding to the Window’s DataContext.ExitCommand.

<Button Command="{Binding 
    RelativeSource={RelativeSource AncestorType={x:Type Window},
    Path=DataContext.ExitCommand}}

Of course, this only works well if you can find the control containing the command you want through a binding.

Enter the Event System

Another way is through a loosely coupled event system. I would recommend either MVVM Light’s Messenger or Microsoft Prism’s EventAggregator. You could always build your own event system as well if you really wanted to.

Both event systems makes me think of a paging system. Any ViewModel can broadcast a message, and any ViewModel can subscribe to receive broadcasted messages. Messages are often packaged into different types, so a ViewModel can subscribe to only listen for specific message types such as CloseApplicationMessages.

This kind of loosely coupled event system is ideal for MVVM because the ViewModels don’t need to know about each other to be able to do their job.

Using either of these systems, your ViewModel that handles the event would subscribe to receive a specific message type. It would tell the event system that if a message of type X is broadcasted, send it to a specified delegate method. The ViewModel that wishes to raise the event simply has to broadcast the message and include any parameters needed in the body of the message.

Example using Prism’s EventAggregator

// Subscribe
eventAggregator.GetEvent<CloseAppliactionMessage>().Subscribe(ExitMethod);

// Broadcast
eventAggregator.GetEvent<CloseAppliactionMessage>().Publish();

Example using MVVM Light’s Messenger

//Subscribe
Messenger.Default.Register<CloseAppliactionMessage>(ExitMethod);

// Broadcast
Messenger.Default.Send<CloseAppliactionMessage>()