malloryCode.com

Dynamically Generated Excel in ASP.Net

Your users need to download data in Excel format, or they need their data plus some formulas built in, for data that is dynamically queried from the database. In this article I will take a list of some custom object, serialize it into XML, then use XSLT to generate a real live Excel (xml format) file, and send it to the browser. The XSLT file will start out as a blank xml formatted Excel file and it will be transformed into an XSLT with fields for your data and formulas on that data.

To get started, fire up Excel, and select Save As | Other Formats, and in the format dropdown select 'XML Spreadsheet 2003 (.xml)' and save the file. If you like, you could add titles, or other data, but usually start with an empty document and manually change it. You can open it in a text editor and see it is a normal XML file with several Microsoft specific namespaces. The documentation for these file types could be a bit better, but it's allowed me to figure out how to do most everything I've needed to. And here's a link to the reference schemas for a deep dive into the schemas.

Now put that document into your c# project and rename it to .xslt. Open it up and actually transform it to an XSLT file. Add:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                xmlns:o="urn:schemas-microsoft-com:office:office"
                xmlns:x="urn:schemas-microsoft-com:office:excel"
                xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
    <xsl:output method="xml" indent="yes" encoding="utf-8"/>
    <xsl:template match="/">
        <xsl:processing-instruction name="mso-application">
            <xsl:text>progid="Excel.Sheet"</xsl:text>
        </xsl:processing-instruction>

just before the <workbook> tag, and:

    </xsl:template>
</xsl:stylesheet>

after the ending workbook tag to end the document. You now have a valid XSLT document and if you pass in an XML formatted list, it would run. However, it would not be very interesting. What we will do is loop through the data, outputting a row for each record of data. We'll need a for each loop:

<Worksheet ss:Name="Sheet3">
  <Table x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">
    <xsl:for-each select="./ArrayOfProjectExport">
        <Row ss:AutoFitHeight="0">
            <Cell ss:StyleID="sGeneral">
                <Data ss:Type="String">
                    <xsl:value-of select="Id"/>
                </Data>
            </Cell>
            <Cell ss:StyleID="sGeneral">
                <Data ss:Type="String">
                    <xsl:value-of select="ProjectName"/>
                </Data>
            </Cell>
        </Row>
    </xsl:for-each>
  </Table>
</Worksheet>

Make sure to remove the ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" expanded attributes of the Table tag that will be in there, or you will probably get an error when opening the workbook. In the above example, I've removed them.

That's it for the XSLT file. Of course there is a lot more you can do with this file, but I'm going to keep it simple for this post, and will cover more complex tasks in future posts. The example project, in the Code for Post link above, has several formulas, so you can see how they work.

Now we need to get data from the data source into XML format and run the XSLT transform to get the Excel workbook. First, XML serialization. I typically use generics here because I normally am exporting many different data sets.

public byte[] FileContents<T, Tvm>( List<T> list )
    where T : class
    where Tvm : class, new()
{
    string typeName = typeof( Tvm ).Name;

    // get view model list
    MethodInfo toViewModel = typeof( ModelToViewModel ).GetMethod( typeName, BindingFlags.Public | BindingFlags.Static );
    List<Tvm> viewModelList = (List<Tvm>)toViewModel.Invoke( null, new object[] { list } );

When exporting to Excel, I always use a view model. It's almost always the case that the data needs to be flattened or transformed in some way. You need to check for nulls at a minimum, and I usually convert dates to strings. The above code uses a class ModelToViewModel to translate a List<T> into a List<Tvm> (T view model), by grabbing the method name that is the same as the view model type. When setting this up, I simply name my methods this way. The full method is described below. Once I have my list, I serialize it:

// convert list to xml
XmlDocument listXml = (XmlDocument)GetXML<Tvm>( viewModelList );

where GetXML is a custom method that does the serializing. It's pretty straightforward; the full code is in the example project.

It's also straightforward to send in parameters to the XSLT file. Here, I'm sending in the current date.

// prepare arguments for xslt
XsltArgumentList args = new XsltArgumentList();
args.AddParam( "current-date", "", DateTime.Now.ToShortDateString() );

// load xslt into transform
XslCompiledTransform transform = new XslCompiledTransform();
using( StreamReader sr = new StreamReader( Server.MapPath( "~/Xslt/Template.xslt" ) ) )
{
    StringBuilder sbXslt = new StringBuilder( sr.ReadToEnd() );
    XmlDocument xsltDoc = new XmlDocument();
    xsltDoc.LoadXml( sbXslt.ToString() );
    transform.Load( xsltDoc );
}

This is followed by actually running the XSLT tranform. I read the file into a stream, then turn that into a string, which gets loaded into an XMLDocument. Then the XslCompiledTransform class does the real work. Below, the transform is run, with the output from the transform going into a MemoryStream. I learned, by trial and error, that I had to skip past the byte order mark (BOM), or Excel would choke when opening the document.

// put together and return output
using( MemoryStream output = new MemoryStream() )
{
    transform.Transform( listXml, args, output );
    output.Position = 3; // skip the BOM
    return output.ToArray();
}

So GetFileContent() gets the document as a byte array. Here's how to use it and return a file to the browser:

public ActionResult Template()
{
    // get data. note: the level of detail of the excel export is on the object 'Activity',
    // so our list must be of that type. when transformed to a viewmodel, the data will be 
    // grouped appropriately
    var activities = Data.Projects.SelectMany( p => p.Activities).ToList();

    // get file
    byte[] fileContents = FileContents<Activity, ProjectExport>( activities );

    // send to browser
    return File( fileContents, "application/vnd.ms-excel", "Template_" + DateTime.Today.ToString( "yyyy-MM-dd" ) + ".xml" );
}

When the browser used is Firefox, Excel will throw up a message about the document being a different type than advertised, but clicking 'OK' will open the document no problem. This doesn't happen on other browsers.

Finally, here's the model to view model mapping. For this example, the most detailed data being output was on a child object (my model is Project.Activities), so that is the Type that the list must be. I flattened the data by grouping on my output fields, then output my view model type, doing routine checks on the data.

public class ModelToViewModel
{
    public static List<ProjectExport> ProjectExport( List<Activity> list )
    {
        return ( from x in list
                 group x by new
                 {
                     x.ActivityNumber,
                     x.Status,
                     x.Project.Id,
                     x.Project.ProjectNumber,
                     x.Project.Title,
                     x.Project.CheckOutDate
                 } into grouped  
                 select new ProjectExport
                 {
                     ProjectId = grouped.Key.Id,
                     ProjectTitle = grouped.Key.Title,
                     ProjectNumber = grouped.Key.ProjectNumber,
                     CheckOutDate = grouped.Key.CheckOutDate.HasValue ? Convert.ToDateTime( grouped.Key.CheckOutDate ).ToString( "D" ) : string.Empty,
                     ActivityNumber = grouped.Key.ActivityNumber,
                     ActivityStatus = grouped.Key.Status==null?String.Empty:grouped.Key.Status.Name
                 } ).ToList();
    }
}

Of course, using LINQ, you can easily do a lot of complex data transformations. In future posts I'll cover some of the techniques I've used along those lines.