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

Parameter	Type
View	EntityReference
FetchXml	string
LayoutXml	string
QueryApi	string
QueryParameters	InputArgumentCollection

Code

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

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                     //Guid of the view has to be passed                     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;             }         }     } }

Advertisement

REPORT THIS AD

Closing Notes:

1. “View” parameter can accept “userquery” or “savedquery”, but they have to exist i.e. you can’t pass empty Guid.
2. The fetchxml and layoutxml can be different from what is in the “savedquery” or “userquery”. Hence, you can create a “Personal View” just so that you can use it in this message, but modify the fetchxml and layoutxml to whatever you want.
3. The name of the tab in the Excel output file will be the name of the view specified in the “View” parameter
4. This message can be executed from Javascript as well, but you will get a base64 string instead of a byte array in the response.

Please vote up my request on Connect > 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.

NOTE : this is just for demo. Don't use it in real time project as further changes in codes are not supported. You can try using EPPlus or a similar library to generate this.