Click here to Skip to main content
15,867,835 members
Articles / Programming Languages / C#

Reflection Excel Report Engine

Rate me:
Please Sign up or sign in to vote.
4.29/5 (3 votes)
22 Mar 2009CPOL5 min read 28.9K   252   33   2
A class that creates a report in an Excel file with any kind of custom object.

Introduction

In a previous article, I provided a class that created an XML document that has the necessary node tags to be parsed as an Excel file.

That class came to be because at work, we needed to generate several reports in Excel with different configurations (single worksheets, multiple worksheets) and displayed in a web application.

After it was done, somebody suggested that it would be better to create a “report engine” that could receive any of the custom objects we were using and just automatically generate the report.

Background

Of course, right off the bat, we’re talking about Reflection here. With Reflection, you can, among a lot of other things, during run time, get the properties of any object without knowing what type of object you are dealing with.

But, using Reflection to solve this problem had a number of challenges:

  1. How to make it generic, and be able to display exactly the object’s properties we needed in the report.
  2. How to control the exact order in which we should display the properties each report is interested in.
  3. How to differentiate primitive properties (int, string, DateTime, etc.) from properties that were other types of custom objects among properties we wanted to display.
  4. Some property names are not user friendly so, how to be able to display a user friendly header in each of the report’s column headers based on the properties that we are displaying.

With these requirements and challenges, the solutions we came up with were:

  1. Use Excel’s capabilities to create workbooks as XML documents.
  2. Use Reflection to get the properties of any kind of custom object during run time.
  3. Use a Dictionary to map the properties we wanted to get from the object with a user friendly header. This approach addresses the issues regarding which properties to use, how to control the displaying order, and the user friendly headers.
  4. Use the previous Dictionary to fully qualify nested object properties, and use recursion to get all the objects one by one until we reach the last property we’re interested in.

Don’t worry; it will all become clearer as we take a look at the code.

Using the code

Consider the following classes:

C#
class Company
{
    public string CompanyName { get; set; }
    public Address CompanyAddress { get; set; }
    public decimal YearSales { get; set; }
    public decimal YearProfit { get; set; }
    public List<invester> Investers { get; set; }
    public List<employee> Employees { get; set; }
}

class Investor
{
    public string InvestorName { get; set; }
    public DateTime InvestmentDate { get; set; }
    public decimal Percentage { get; set; }
}

class Employee
{
    public string EmployeeName { get; set; }
    public DateTime HiringDate { get; set; }
    public decimal Salary { get; set; }
}

class Address
{
    public int StreetNumber { get; set; }
    public string StreetName { get; set; }
    public string OfficeNumber { get; set; }
    public string CountryName { get; set; }
    public string StateName { get; set; }
    public string PostalCode { get; set; }
    public PhoneNumber Telephone { get; set; }
}

class PhoneNumber
{
    public int CountryCode { get; set; }
    public int AreaCode { get; set; }
    public string PhoneNumber { get; set; }
    public int Extension { get; set; }
}

I know the class design is weird, but this is for demonstration purposes, so bear with me.

Now, let’s pretend we want to generate a report with all of a company’s data but keeping all of its financial data confidential, which would mean not to include its sales and profit numbers, its investors’ percentages, and its employees’ salaries. With this in mind, we’ll create three Dictionarys, one for each type of object.

C#
private static Dictionary<string,> CreatePublicCompanyDictionary()
{
    Dictionary<string, string> header = new Dictionary<string, string>();

    header.Add("CompanyName", "Company Name");
    header.Add("CompanyAddress.StreetNumber", "Number");
    header.Add("CompanyAddress.StreetName", "Street");
    header.Add("CompanyAddress.OfficeNumber", "Office");
    header.Add("CompanyAddress.CountryName", "Country");
    header.Add("CompanyAddress.City", "City");
    header.Add("CompanyAddress.StateName", "State");
    header.Add("CompanyAddress.Telephone.AreaCode", "Area Code");
    header.Add("CompanyAddress.Telephone.TelephoneNumber", "Telephone");
    header.Add("CompanyAddress.Telephone.Extension", "Extension");

    return header;
}

private static Dictionary<string,> CreatePublicInvestorsHeader()
{
    Dictionary<string, string> investorsHeader = new Dictionary<string, string>();

    investorsHeader.Add("InvestorName", "Investor Name");
    investorsHeader.Add("InvestmentDate", "Investment Date");

    return investorsHeader;
}

private static Dictionary<string,> CreatePublicEmployeesHeader()
{
    Dictionary<string, string> employeesHeader = new Dictionary<string, string>();

    employeesHeader.Add("EmployeeName", "Employee Name");
    employeesHeader.Add("HiringDate", "Hiring Date");

    return employeesHeader;
}

So far so good. Now, the really interesting part; we’ll call our magic class that will receive our objects and dictionaries, and will create our reports.

C#
List<company> companies = new List<company>();
ExcelReportGenerator generator = 
   new ExcelReportGenerator(@"c:\Company1Report.xml");

companies.Add(CreateCompany1());
companies.Add(CreateCompany2());

generator.AddStringStyle(TABLE_HEADER_FORMAT_NAME, "Arial", 10, 
                         "#FFFFFF", "#C0C0C0", false);
generator.AddStringStyle(SMALL_FONT_FORMAT, "Arial", 8, "#000000", false);

try
{
    generator.CreateSheet("Company 1");
    generator.CreateReportTable(companies[0], 
              CreatePublicCompanyDictionary(), 
              TABLE_HEADER_FORMAT_NAME);
    generator.AddRow();
    generator.AddRow();
    generator.CreateReportTable(companies[0].Investors, 
              CreatePublicInvestorsHeader(), 
              TABLE_HEADER_FORMAT_NAME);
    generator.AddRow();
    generator.AddRow();
    generator.CreateReportTable(companies[0].Employees, 
              CreatePublicEmployeesHeader(), 
              TABLE_HEADER_FORMAT_NAME);

    generator.SaveDocument();
}
catch(Exception ex)
{
    Console.WriteLine(ex.Message);
    Console.ReadLine();
}

Run this code from the demo project and open your new report. Looks great, doesn’t it? And, just by writing so little code!

Play along with it. Add and remove properties in your dictionaries, and see how they are included or excluded from the report. You can even try adding a property that does not exist in the object, and an ArgumentException exception is thrown telling you the property does not exist in the object you are parsing.

Points of Interest

Everything looks great, doesn’t it? But, how is this working? How can I use Reflection to accomplish this kind of behavior?

It all comes down at knowing which type of object I’m dealing with in order to get its properties and their values. Also, remember we talked about recursion to get the nested object’s properties? Well, everything happens in this code snippet:

C#
Type dataObjectType;

if (propertyName.Contains("."))
{
    dataObjectType = dataObject.GetType();
    PropertyInfo innerProperty = 
      dataObjectType.GetProperty(propertyName.Substring(0, 
                                 propertyName.IndexOf(".")));

    if(innerProperty == null)
    {
        throw new ArgumentException("Object of type " + dataObjectType.FullName + 
              " does not contain a definition for property " + propertyName);
    }
    internalObject = innerProperty.GetValue(dataObject, null);

    string newLabel = propertyName.Substring(propertyName.IndexOf(".") + 1);

    GetObjectProperty(newLabel, internalObject, out property, 
                      out internalObject, out isEmptyCellNeeded);
}
else
{
    internalObject = dataObject;
    dataObjectType = dataObject.GetType();
    property = dataObjectType.GetProperty(propertyName);

    if (property == null)
    {
        throw new ArgumentException("Object of type " + dataObjectType.FullName + 
              " does not contain a definition for property " + propertyName);
    }
}

Basically, I use the object’s GetType method to get the object’s type. Once I have it, I can use the GetProperty method of the type using the property name as its parameter to get a PropertyInfo object. For nested objects, if the PropertyInfo object is not null, I use the GetValue method to get the nested object of this property.

The newLabel string is created by removing the left most section of the fully qualified property, and the method is called again in order to get the next property in the hierarchy, and so we keep going until we get to the last one for which we can get the value to be displayed.

In our example, for the telephone number, what happens is this:

  • On the first run: propertyName = “CompanyAddress.Telephone.TelephoneNumber” and dataObject = Company.
  • On the second run: propertyName = “Telephone.TelephoneNumber” and dataObject = Address.
  • On the third run: propertyName = “TelephoneNumber” and dataObject = PhoneNumber.

And, it is on this third run where we actually get the TelephoneNumber property and return it to the calling method.

Conclusion

To be perfectly honest, even though I had played a lot with Eeflection, this was the very first time I had the opportunity to use it in production code. Some people even avoid it because it is known to affect the application’s performance.

Regarding the performance issue, once a guy I used to work with mentioned an application in which depending on the type of document (Invoice, Purchase Order, Delivery Receipt, etc.), they would instantiate the object getting the type and namespace as a string from a database, and from another table, they would get the list of methods to be invoked in the specific order in which they should be invoked. That is a huge use of Reflection, and in every single call in the application. When I asked him about the application’s performance, he claimed it was lightning fast.

So, there you go. Reflection can be a very useful tool under your belt. Whether it is slow or fast, I guess you can build your own prototypes and get your own conclusions.

Have fun.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Mexico Mexico
Sergio has been working as a software developer since 2002 in several industries such as Insurance, Health, and Oil. He is an MCAD and most likely will be looking to upgrade his certification soon.

He currently lives in Montreal, Canada.

Comments and Discussions

 
Generalmake it 100x faster Pin
sa222222226-Mar-09 3:31
sa222222226-Mar-09 3:31 
GeneralRe: make it 100x faster Pin
Sergio Romero28-Mar-09 8:09
Sergio Romero28-Mar-09 8:09 
You are absolutely right. Those are not needed.

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.