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
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;
}
}
}
}
Closing Notes:
- “View” parameter can accept “userquery” or “savedquery”, but they have to exist i.e. you can’t pass empty Guid.
- 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.
- The name of the tab in the Excel output file will be the name of the view specified in the “View” parameter
- 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 (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.
[…] that is not documented in Microsoft Dynamics 365 SDK, but works perfectly fine (like for example ExportToExcel message). I decided to prepare a list of all those messages, together with input and output […]
Hi,
I know this is kind of an old post, but I was wondering if you ever got this to work through the web api?
I’m completely stuck ATM.
As far as I can tell from the error messages and trace files, there is an issue with my requestbody, but seeing as this is an undocumented action, I can’t seem to find any other post regarding this.
Any help would be greatly appreciated!
Like you said, since it is not documented is most likely won’t even work with the WebAPI endpoint.
But in your post you mentioned that it’s possible to execute this from javascript. What did you mean by that then if not through the webapi?
I did not save the script and I don’t have a copy of it. It was something to do with Xrm.Internal.messages.exportToExcel, which is unsupported.
Where the excel file will store
It is just saved to the disk.
Hi,
I am using this message as you have used above in a workflow step to create an excel attachment to an email. I am working in a Multilingual environment, I wanted to know if a user language parameter can be passed to the request to create excel sheets in different languages.
Any help would be appreciated.
Since this an undocumented message it is unsupported. Try using an external library to generate the Excel file e.g https://github.com/rajyraman/mscrmexporttoexcel
im trying the same code as a workflow but when i try to run the process im facing an issue i.e,The request ExportToExcel cannot be invoked from the Sandbox.
here is my code snippet
QueryExpression query = new QueryExpression(“savedquery”);
query.ColumnSet = new ColumnSet(true);
query.Criteria.AddCondition(“returnedtypecode”, ConditionOperator.Equal, entityName);
query.Criteria.AddCondition(“querytype”, ConditionOperator.Equal, 0);
query.Criteria.AddCondition(“name”, ConditionOperator.Equal, viewName);
EntityCollection collection = service.RetrieveMultiple(query);
if (collection != null && collection.Entities.Any())
{
OrganizationRequest exportToExcelRequest = new OrganizationRequest(“ExportToExcel”);
exportToExcelRequest.Parameters = new ParameterCollection();
exportToExcelRequest.Parameters.Add(new KeyValuePair(“View”, new EntityReference(“savedquery”, collection.Entities.FirstOrDefault().Id)));
exportToExcelRequest.Parameters.Add(new KeyValuePair(“FetchXml”, HelperFunctions.GetAttributeValue(“fetchxml”, collection.Entities.FirstOrDefault())));
exportToExcelRequest.Parameters.Add(new KeyValuePair(“LayoutXml”, HelperFunctions.GetAttributeValue(“layoutxml”, collection.Entities.FirstOrDefault())));
exportToExcelRequest.Parameters.Add(new KeyValuePair(“QueryApi”, “”));
exportToExcelRequest.Parameters.Add(new KeyValuePair(“QueryParameters”, new InputArgumentCollection()));
OrganizationResponse exportToExcelResponse = service.Execute(exportToExcelRequest);
if (exportToExcelResponse != null && exportToExcelResponse.Results.Any())
{
Entity attachment = new Entity(“activitymimeattachment”);
attachment[“objectid”] = emailId;
attachment[“objecttypecode”] = emailId.LogicalName;
//attachment[“subject”] = “sub”;
attachment[“body”] = Convert.ToBase64String(exportToExcelResponse.Results[“ExcelFile”] as byte[]);
attachment[“filename”] = string.Format(“{0}.xlsx”, viewName);
attachment[“mimetype”] = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”;
service.Create(attachment);
Since this is a undocumented message it is unsupported and basically suitable only for demos/PoCs. You can try using EPPlus or a similar library to generate this.