Export to Excel using Dynamics 365 SDK

With the new Dynamics 365 release, a new message has been added that making exporting FetchXML results really simple. This message is not documented, hence is technically unsupported. With that word of warning, I will show you how to utilise this new message to export data from Dynamics 365 to an Excel file.

ExportToExcel message definition

Code

using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Linq;
using System.ServiceModel;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Client;
using Microsoft.Xrm.Client.Services;
using Microsoft.Xrm.Sdk;

namespace Experiments
{
    class Program
    {
        private static OrganizationService _orgService;
        static void Main(string[] args)
        {
            try
            {
                CrmConnection connection = CrmConnection.Parse(
                    ConfigurationManager.ConnectionStrings["CRMConnectionString"].ConnectionString);

                using (_orgService = new OrganizationService(connection))
                {

                    var exportToExcelRequest = new OrganizationRequest("ExportToExcel");
                    exportToExcelRequest.Parameters = new ParameterCollection();
                    //Has to be a savedquery aka "System View" or userquery aka "Saved View"
                    //The view has to exist, otherwise will error out
                    exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object> ("View",
                        new EntityReference("userquery", new Guid("{0B915102-24A7-E611-8101-1458D05B1178}"))));
                    exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("FetchXml", @"
                    <fetch distinct='false' no-lock='false' mapping='logical' returntotalrecordcount='true'>
	                    <entity name='contact'>
		                    <attribute name='fullname' />
	                    </entity>
                    </fetch>"));
                    exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("LayoutXml", @"
                    <grid name='resultset' object='2' jump='fullname' select='1' icon='1' preview='1'>
	                    <row name='result' id='contactid'>
		                    <cell name='fullname' width='300' />
	                    </row>
                    </grid>"));
                    //need these params to keep org service happy
                    exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("QueryApi", ""));
                    exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("QueryParameters",
                        new InputArgumentCollection()));
                    var exportToExcelResponse = _orgService.Execute(exportToExcelRequest);
                    if (exportToExcelResponse.Results.Any())
                    {
                        File.WriteAllBytes("Active Contacts.xlsx", exportToExcelResponse.Results["ExcelFile"] as byte[]);
                    }
                }
            }
            catch (FaultException<OrganizationServiceFault> ex)
            {
                string message = ex.Message;
                throw;
            }
        }
    }
}

Please vote up my request on Connect (logged Feb 2015) -> https://connect.microsoft.com/site687/feedback/details/1127874/export-to-excel-sdk-message so that this message can be made available as an unbound WebAPI action.