Caveman's Blog

My commitment to learning.

OpenXML + Linq = Awesome Office files

leave a comment »


Office Open XML (also informally known as OOXML or OpenXML) is a zipped, XML-based file format developed by Microsoft for representing spreadsheets, charts, presentations and word processing documents. [1] If someone is looking for software to generate MS-Office file programatically, I would highly recommend considering this library. I have recently used OpenXML to generate Excel reports from a windows service. The library was very fast in generating reports with a lot of data and the best part about this library is that it is free ūüôā Microsoft provides a SDK that can be downloaded from here and can very easy used to generate awesome Office reports. Following are some resources for OpenXML development:

Here is a utility function that converts a List of objects into a DataTable, that can be easily exported to an OpenXML file. Basically in the code below, while loop through the contents of the List object, we use reflection to read the List object properties to create the column names of a DataRow object. Data from each List object is then populated in the DataRow object which will then be used to fill our DataTable.

public DataTable ConvertToDataTable(List<EmployeeData> varlist)
{
    DataTable dtReturn = new DataTable();
    // column names
    PropertyInfo[] oProps = null;

    if (varlist == null) return dtReturn;
    foreach (EmployeeData rec in varlist)
    {
        if (oProps == null)
        {
            oProps = ((Type)rec.GetType()).GetProperties();
            foreach (PropertyInfo pi in oProps)
            {
                Type colType = pi.PropertyType;

                if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
                == typeof(Nullable)))
                {
                    colType = colType.GetGenericArguments()[0];
                }

                dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
            }
        }

        DataRow dr = dtReturn.NewRow();
        foreach (PropertyInfo pi in oProps)
        {
            dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
            (rec, null);
        }

        dtReturn.Rows.Add(dr);
    }
    return dtReturn;

References:
1. Office Open XML – Wikipedia
2. Open XML SDK 2.0

Advertisements

Written by cavemansblog

September 21, 2011 at 1:37 am

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: