I have really started to like working with FetchXML (at first I was not so sure). It takes a lot less code to execute a query than other methods and the fact I can load it into an XML document is an added bonus. With the addition of a few wrapper functions a simple FetchXML statement can quickly be turned into a DataTable, XML document, or an Excel worksheet.
The first step for any of these activities is to load the results of the query into an XML document. This makes the results easier to work with acts as the basis for all other activities. In this example, we are using a SavedQuery object as the template for an Excel export:
XmlDocument doc = new XmlDocument();
doc.Load(new StringReader(CrmService.Fetch(query.FetchXml)));
The next step is to iterate through the XML document and build the Excel workbook. Rather than use the Excel object model, we simply generate an Excel XML document and return it as the XLS extension:
// Worksheet header
result += "<Worksheet ss:Name=\"" + this.name + "\">";
// Table header
result += "<Table ss:ExpandedColumnCount=\"" + this.Layout.Columns.Length + "\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:StyleID=\"s15\">";
// Column definitions
foreach (Column column in this.Layout.Columns)
{
result += "<Column ss:Width=\"" + column.Width + "\"/>";
}
// Column headers
result += "<Row ss:StyleID=\"s64\">";
foreach (Column column in this.Layout.Columns)
{
result += "<Cell><Data ss:Type=\"String\">";
result += column.Name;
result += "</Data></Cell>";
}
result += "</Row>";
XmlDocument doc = Utilities.Fetch.GetXml(this.fetchxml, parentId, this.Layout.Jump, filter, order);
foreach (XmlNode node in doc.SelectNodes("//resultset/result"))
{
result += "<Row>";
foreach (Column column in this.Layout.Columns)
{
result += "<Cell><Data ss:Type=\"String\">";
result += this.GetValue(node, column, this.Metadata);
result += "</Data></Cell>";
}
result += "</Row>";
}
// Footers
result += "</Table></Worksheet>";
Note, the above code snippet is taken from a function within our SavedQuery class. To implement this code you would need to replace the references to "this" with your own references.
Now all is left is to add our generated worksheet to an Excel template and (optionally) deliver the file to the user. The template is saved as a resource file and looks like this:
<?xml version="1.0" ?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Arial"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s15">
<Font ss:FontName="Arial"/>
</Style>
<Style ss:ID="s64">
<Font ss:FontName="Arial" x:Family="Swiss" ss:Color="#FFFFFF" ss:Bold="1"/>
<Interior ss:Color="#333399" ss:Pattern="Solid"/>
</Style>
</Styles>
</Workbook>
This was created by executing an Excel export in CRM, saving the results as an XML spreadsheet, and finally using notepad to determine the XML required for the template. Again using an XML document our output and the template are combined:
XmlDocument xlsDoc = new XmlDocument();
xlsDoc.LoadXml(Properties.Resources.ExcelTemplate);
xlsDoc.DocumentElement.InnerXml += result;
To stream the result to the browser, it just takes a few more lines:
context.Response.ContentType = "application/vnd.ms-excel";
context.Response.AddHeader("content-disposition", "attachement; filename=" + savedQuery.name + ".xls");
context.Response.Write(result);
context.Response.Flush();
context.Response.End();
Voila! FetchXML to Excel XML!!
tags: Microsoft CRM, Dynamics CRM, Fetch, FetchXML, Excel
Home
