by

Excel Reports using any POCO!

In this blog post I’ll be sharing my implementation of what I call Reflective Excel Spreadsheets. That is, a short piece of code to generate an Excel Spreadsheet from any plain old CLR object.

The Problem:

As a software developer you might be asked to generate a report. This report can range from a simple text file to a full blown SSRS report. I want to get away from the ugly text files and generate nice Excel Spreadsheets, but I don’t want to have to write a bunch of code for each individual ad hoc procedure. Can’t I just write the code that generates the report once and then worry only about feeding it the data that I want to see in the report? You bet I can!

The Solution:

Using reflection it is possible to access each property of an object and glean it’s metadata. So we can generate headers for the name of each property and then access each property for it’s value and input that into the spreadsheet.

In order to run the following code it is necessary for you to have Microsoft Excel installed on your computer since I am using the Microsoft.Office.Interop.Excel assembly.

The Code:

Let’s start with a basic class:

public class ExampleClass
{
    public string Name { get; set; }
    public string Age { get; set; }
    public string Birthday { get; set; }
    public string JobTitle { get; set; }
}

And now the code that will generate a spreadsheet:

class Program
{
    static void Main(string[] args)
    {
        ExampleClass example = new ExampleClass()
        {
            Name = "Dave Hulse",
            Age = "28",
            Birthday = "07/20/1989",
            JobTitle = "Software Developer I"
        };

        List<object> objects = new List<object>();
        objects.Add(example);
        FillSpreadsheet(objects);
    }

    public static void FillSpreadsheet(List<object> objects)
    {
        Excel.Application xlApp = new Excel.Application();

        if (xlApp == null)
        {
            Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
            return;
        }
        xlApp.Visible = true;

        Excel.Workbook wb = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
        Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];

        if (ws == null)
        {
            Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
        }

        List<string> headings = GetHeadings(objects);

        for (int i = 0; i < headings.Count; i++)
        {
            ws.Cells[1, i + 1] = headings[i];
        }

        List<List<string>> cells = GetCells(objects);

        for (int y = 0; y < cells.Count; y++)
        {
            for (int x = 0; x < cells[0].Count; x++)
            {
                ws.Cells[y + 2, x + 1] = cells[y][x];
            }
        }

    }

    public static List<string> GetHeadings(List<object> objects)
    {
        PropertyInfo[] propertyInfos = objects[0].GetType().GetProperties();
        var headings = new List<string>();
        foreach (var property in propertyInfos)
        {
            headings.Add(property.Name);
        }
        return headings;
    }

    public static List<List<string>> GetCells(List<object> objects)
    {
        List<List<string>> cells = new List<List<string>>();
        for (int i = 0; i < objects.Count; i++)
        {
            PropertyInfo[] propertyInfos = objects[i].GetType().GetProperties();
            var obj = new List<string>();
            foreach (var property in propertyInfos)
            {
                obj.Add(property.GetValue(objects[i]).ToString());
            }
            cells.Add(obj);
        }
        return cells;
    }

}

Write a Comment

Comment