Creating an Excel document without excel installed … in one line of code

Yes, we can: code a complete excel document in only one line of code. The secret: the fluent interface of Linq2XML. Excel document are (since Excel 2003) XML documents, so you can create them without having Excel installed – e.g. on a web-server, where you definitely don’t want to have Com-Interop and a running Excel process.

In my project Sem.Sync I’m writing excel documents containing flatened contact information using this approach. have a look at the following code to see how easy it is:


public static string ExportToWorksheet(List listToConvert)
{
    XNamespace oo = "urn:schemas-microsoft-com:office:office";
    XNamespace ox = "urn:schemas-microsoft-com:office:excel";
    XNamespace ss = "urn:schemas-microsoft-com:office:spreadsheet";
   
    var properties = Tools.GetPropertyList(string.Empty, typeof(T));

    var nresult = new XDocument(
        new XProcessingInstruction("mso-application", "progid=\"Excel.Sheet\""),
        new XElement(ss + "Workbook",
            new XElement(oo + "DocumentProperties",
                new XElement(oo + "Version", "14.00")
                ),
            new XElement(oo + "OfficeDocumentSettings",
                new XElement(oo + "AllowPNG")
                ),
            new XElement(ox + "ExcelWorkbook",
                new XElement(ss + "WindowHeight", "13000"),
                new XElement(ss + "WindowWidth", "15000"),
                new XElement(ss + "WindowTopX", "0"),
                new XElement(ss + "WindowTopY", "0"),
                new XElement(ss + "ProtectStructure", "False"),
                new XElement(ss + "ProtectWindows", "False")
                ),
            new XElement(ss + "Styles",
                new XElement(ss + "Style",
                    new XAttribute(ss + "ID", "Default"),
                    new XAttribute(ss + "Name", "Normal"),
                    new XElement(ss + "Alignment",
                        new XAttribute(ss + "Vertical", "Bottom")
                        )
                    )
                ),
            new XElement(ss + "Worksheet",
                new XAttribute(ss + "Name", "SyncData"),

                // this will create the data area for the sheet
                new XElement(ss + "Table",
                    new XAttribute(ss + "ExpandedColumnCount", properties.Count.ToString()),
                   
                    // the row count is the number of elements + 1 because we will add the
                    // paths of the properties as the heading (= one additional row)
                    new XAttribute(ss + "ExpandedRowCount", (listToConvert.Count + 1).ToString()),
                    new XAttribute(ox + "FullColumns", "1"),
                    new XAttribute(ox + "FullRows", "1"),
                    new XAttribute(ss + "DefaultColumnWidth", "120"),

                    // this will include the paths of the properties as the first row
                    new XElement(ss + "Row",
                        from propertPath in properties
                        select
                            new XElement(ss + "Cell",
                                new XElement(ss + "Data",
                                    new XAttribute(ss + "Type", "String"),
                                    propertPath
                                )
                            )
                    ),
                   
                    // create a list of rows
                    from element in listToConvert
                    select
                        new XElement(ss + "Row",
                           
                            // create a list of cells for this row
                            from propertPath in properties
                            select
                                new XElement(ss + "Cell",
                                    new XElement(ss + "Data",
                                        new XAttribute(ss + "Type", "String"),
                                        Tools.GetPropertyValueString(element, propertPath)
                                    )
                                )
                        )
                    )
                )
            )
        );

    // Excel does need this little string at the beginning of the file... so we simply add it as a string
    return "<!--l version=\"1.0\-->" + nresult;
}

You will need to fix the last line to include “?xml version=\”1.0\”?” between the opening and closing angle-bracket.

The GetPropertyList method creates a list of strings with “paths” to the properties and GetPropertyValueString gets a string representation of such properties from an object. As you can see the rows and cells are created inside a Linq-expression and you can compile the excel document in really just one line of code – but you should think about maintainence, if you do so ;-).

Advertisements

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

Sylvio's Infobox

Aktuelle Themen rund um SQL Server, BI, Windows, ...

Meredith Lewis

Professional Digital Portfolio

Vittorio Bertocci

Just another WordPress.com weblog

ScottGu's Blog

Just another WordPress.com weblog

AJ's blog

Thoughts and informations I think worthwhile to share...

Outlawtrail - .NET Development

Architecture & Design

SDX eXperts Flurfunk

Just another WordPress.com weblog

%d bloggers like this: