Load a Word document into Internet Explorer and set Response.ContentType

About two years ago I wrote some code to construct a PDF document on the fly and display it in the browser. Yesterday I was trying to do the same thing for a Word document. If you've seen earlier posts this month you will see I am using Aspose Words for .Net to perform a mail merge.  I wanted the ability to preview the result before saving the output. 
 
Now when I did this previously for a PDF file I had a web service that returned a memory stream and I was able to load that into the Response.OutputStream object without difficulty. But I immediately ran into a problem with my web service which is built with .Net FW 4.0.  The memory stream I returned became a marshalled object which does not have the same properties for WriteTo() or ToArray() which meant I could not easily load it into Response.OutputStream.
 
I suppose I could have found a solution but instead I thought I would return it as a string instead. Alas that gave a new set of problems which I suspect was down to encoding when converting between the stream and the string.  This morning I tried converting to a base base64 string and that did the trick. 
 
So firstly here is the code in the web service that converts the memory stream to base64.


public string PreviewMerge(DataMergeRequest req)
{   // code to do mailmerge goes here
   MemoryStream msRawData = merge.MergeDataSet(ds, templatelocation);

   string base64;
   // ENCODE TO BASE 64
   base64 = Convert.ToBase64String(msRawData.GetBuffer(), 0, (int)msRawData.Length);

    return base64;
}

On the ASPX page you need to remove everything below the Page directive.
In the Page_Load event you need this code. Note you should NOT use Response.End - there is a known issue with it creating a Threading exception.  Use HttpContext.Current.ApplicationInstance.CompleteRequest() instead. 

Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.Charset = "";

// get results of merge as base 64 encoded stream
string strBase64 = ds.PreviewMerge(req);

// DECODE into memory stream
byte[] raw = Convert.FromBase64String(strBase64);

using (MemoryStream decoded = new MemoryStream(raw))
{
   // load the stream into the Response Output stream
   decoded.WriteTo(Response.OutputStream);
}
// set the content type for docx
Response.ContentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document";
Response.Flush();
HttpContext.Current.ApplicationInstance.CompleteRequest();




 

Downloading a Document Template from SharePoint

Since I've wasted a couple of hours on this I'll share my findings.  I'm creating a web service that will do a mail merge for me using Aspose Words. The document template is stored in a SharePoint document library as a content type and the merged document I want to store back into the document library. 

I tackled the upload first.  Since my web service and SharePoint will be installed on separate servers, I call the SharePoint Copy.asmx web service to upload the document. That works fine.  My next task was to use the document template from SharePoint which I need to retrieve as a memory stream. 

The preferred method is to use the GetItem method of the Copy.asmx service. No problem I thought as I already has a reference to it. 

But try as I might I could not manage to download the template.  When you install a content type as a template the path to the document is something like this

DocLib/Forms/Some Letter/SomeLetter.dotx

I suspect it was something to do with this location that it would not work.  If I tried the same code on a document in the library itself it worked fine.  I gave up in frustration and started Googling for alternatives.

I came up with this incredibly simple approach which I share below.  It simply uses the DownloadData method of the WebClient.  Since I know the absolute path of the template, it works a treat.


public MemoryStream DownloadSharePointDocument(string sourceUrl)
{
   string sharePointSiteUrl = ConfigurationManager.AppSettings["sharepointsiteurl"];

   if (!sharePointSiteUrl.EndsWith("/"))
   {
        sharePointSiteUrl = sharePointSiteUrl + "/";
   }
   sourceUrl = sharePointSiteUrl + sourceUrl;
    WebClient wc = new WebClient();
   wc.UseDefaultCredentials = true;

   byte[] response = wc.DownloadData(sourceUrl);
   MemoryStream ms = HelperMethods.MemoryStreamFromBytes(response);
   return ms;

}

Convert FetchXML results to XML.

With the earlier versions of Dynamics CRM you could construct FetchXML queries and the results would be returned as XML.  I liked that feature but is has been deprecated in later releases. I had a need for this again recently because I wanted to get data from CRM in a format where it was easy to mail merge.  I'm a big fan of Aspose Words for .Net as I have used it several times in the past with great success. 

Aspose has the advantage of being able to design normal mail merge templates in Word so it is easy for a user to amend the template.  Aspose uses mail merge fields just as the typical mail merge template does but you just add them manually to your template rather than from a data source.

Aspose uses data tables as the data source and I wanted a way to convert the results of a FetchXML query into a data table so it was ready to mail merge.  In fact because Aspose can merge multiple data tables I need to create a dataset which I will convert to an XML string using GetXml().

A key factor was I wanted the code to be entirely ignorant of the columns in the result set.  I want to be able to select any number of columns from a CRM entity, use lookup values and pick lists and columns from related tables.  Unfortunately when querying pick lists you only get the value and not the text. You will need another method to get the text equivalent. 

I am not detailing how I do the Aspose merge in this post. It should be easy enough if you follow their samples.  Just remember for this approach to work, the field names in the data table need to be unique. 

To get started I used the CRMSVCUTL.EXE utility to generate the classes of the custom entities.

crmsvcutil /url:http://localhost:5555/Org1/XRMServices/2011/Organization.svc
/out:Xrm.cs  /namespace:Xrm  /serviceContextName:XrmServiceContext

I added the Xrm.cs to my project and references to Microsoft.Xrm.Sdk.dll and Microsoft.Xrm.Client.dll which can be found in the SDK\bin directory.  I also added System.RunTime.Serialization and System.Data.  The connection string for CRM I added to the app.config. I have tried this code on Dynamics CRM 2011 and CRM 2013 and it works fine.


using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Client;
using Microsoft.Xrm.Client.Services;
using Xrm;
 
       static IOrganizationService _service;
        static OrganizationService _orgService;
        static string FetchExpression()
        {
           
            String connectionString = ConfigurationManager.AppSettings["xrmConnectionString"];
            // Establish a connection to the organization web service using CrmConnection.
            Microsoft.Xrm.Client.CrmConnection connection = CrmConnection.Parse(connectionString);
            // Obtain an organization service proxy.
            // The using statement assures that the service proxy will be properly disposed.
            using (_orgService = new OrganizationService(connection))
            {
                string fetchquery = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
                      <entity name='contact'>
                        <attribute name='fullname' />
                        <attribute name='new_referencenumber' />
                        <attribute name='new_locationid' />
                        <attribute name='contactid' />
                        <attribute name='new_contactmechanism' />
                        <order attribute='fullname' descending='false' />
                        <filter type='and'>
                          <condition attribute='statecode' operator='eq' value='0' />
                          <condition attribute='fullname' operator='not-null' />
                        </filter>
                        <link-entity name='systemuser' from='systemuserid' to='owninguser' visible='false' link-type='outer' alias=user'>
                          <attribute name='domainname' />
                        </link-entity>
                      </entity>
                    </fetch>";
                _service = (IOrganizationService)_orgService;
                RetrieveMultipleRequest req = new RetrieveMultipleRequest();
                
                FetchExpression fetch = new FetchExpression(fetchquery);
                req.Query = fetch;
                RetrieveMultipleResponse resp = (RetrieveMultipleResponse)_service.Execute(req);
               
                // Create a dataset and datatable
                DataSet results = new DataSet("Results");
                DataTable Table1 = new DataTable("SingleRowTable");
                DataColumn column;
                DataRow workRow;

                foreach (Entity entity in resp.EntityCollection.Entities)
                {
                    //create the columns in the data table
                    foreach (KeyValuePair<String, Object> attribute in entity.Attributes)
                    {
                        column = new DataColumn();
                        switch (attribute.Value.GetType().Name)
                        {
                            case "AliasedValue":
                                column.DataType = entity.GetAttributeValue<AliasedValue>(attribute.Key).Value.GetType();
                                break;
                            case  "EntityReference":
                                column.DataType = entity.GetEntityReferenceValue<EntityReference>(attribute.Key).Name.GetType();
                                break;
                            case "OptionSetValue":
                                column.DataType = entity.GetAttributeValue<OptionSetValue>(attribute.Key).Value.GetType();
                                break;
                            default :
                                column.DataType = attribute.Value.GetType();
                                break;
                        }
                        column.ColumnName = attribute.Key;
                        Table1.Columns.Add(column);
                    }
                    // add the values to the row
                    workRow = Table1.NewRow();
                    foreach (KeyValuePair<String, Object> attribute in entity.Attributes)
                    {
                        switch (attribute.Value.GetType().Name)
                        {
                            case "AliasedValue":
                                workRow[attribute.Key] = entity.GetAttributeValue<AliasedValue>(attribute.Key).Value;
                                break;
                            case "EntityReference":
                                workRow[attribute.Key] = entity.GetEntityReferenceValue<EntityReference>(attribute.Key).Name;
                                break;
                            case "OptionSetValue":
                                workRow[attribute.Key] = entity.GetAttributeValue<OptionSetValue>(attribute.Key).Value;
                                break;
                            default:
                                workRow[attribute.Key] = attribute.Value;
                                break;
                        }
                    }
                    Table1.Rows.Add(workRow);
                    // only one row expected so exit
                    break;
                }
                results.Tables.Add(Table1);
                return results.GetXml();
            }
        }
 
The results are in a very simple XML structure and already to use for the mail merge. 

<Results>
  <SingleRowTable>
    <fullname>Adam Ant</fullname>
    <new_referencenumber>PSN-00000286</new_referencenumber>
    <new_locationid>Brighton</new_locationid>
    <contactid>2d31f962-5f44-e211-ae45-00155d010a10</contactid>
    <new_contactmechanism>100000009</new_contactmechanism>
    <user.domainname>MSCRM2011\Administrator</user.domainname>
  </SingleRowTable>
</Results>

Converting XML to PDF

I was looking for an application that would convert an XML file into a PDF document. In fact I was trying to create an invoice as a PDF file were the invoice had originated from Great Plains (an application from the Microsoft Dynamics suite that claims to be an accounts package).

A colleague recommended Aspose.Words for .NET to me as he was using it for another project. It uses a Word document as a template and sets it up as a mailmerge. Now this made me a bit wary. I've had first hand expereince of programming a Word mailmerge and it wasn't a pleasant outcome. So the thought of doing a mailmerge in Word wasn't appealing. But it turns out that Aspose.Words for .Net has a single line API call that does the mailmerge for you. Brilliant!

doc.MailMerge.ExecuteWithRegions(GetTestOrderTotals());

But the second problem is a failing in Word 2007 - it doesn't support mailmerge with XML documents as a datasource. So I started looking at the examples that ship with Aspose.Words and stumbled on a Sales Invoice demo. In this case the Word document was using the NorthWinds Access database as the mailmerge source. In fact it was using 3 different views for the order header, the order line items and the order totals.

So how was I going to make that work with my XML source? A few minutes Googling gave me the answer. Word is simply using DataTables as the datasource so all I had to do was create a new DataTable with the same name as the one the Word document expected and create the appropriate columns with the correct data types. Then create a new row in the datatable and pass in the values extracted from the XML document. Bingo.

string Total = GetFieldFromXml(xmldoc, "eConnect/SOPTransactionType/taSopHdrIvcInsert/DOCAMNT");
// other strings declared and assigned here
ds.Tables.Add("OrderTotals");
ds.Tables[0].Columns.Add("OrderID");
ds.Tables[0].Columns.Add("Subtotal");
ds.Tables[0].Columns.Add("Freight");
ds.Tables[0].Columns.Add("Total");
ds.Tables[0].Rows.Add(OrderID, Subtotal, Freight, Total);

The order line items was easy to solve - I just created a DataRow for each node within my list of line items in the XML message.

What about creating the PDF document? Easy, just one line of code in Aspose.Words to specify that a PDF version is required and to open in the Web browser. I just did the usual trick of specifying the Response.ContentHeader to be application/pdf.

HttpResponse resp = Context.Response;
System.Text.Encoding enc = System.Text.Encoding.GetEncoding("ISO-8859-1");
resp.ContentEncoding = enc;
resp.ContentType = "application/pdf";
// now display PDF in browser
doc.Save("Aspose.Words.Demos.pdf", SaveFormat.Pdf, SaveType.OpenInBrowser, resp);

I was amazed how simple it was to create a PDF invoice. The great benefit of Aspose.Words is the use of the Word document as a template. It's a snip to modify the layout to get what you want. If you want to add extra fields you'll need to modify the views in Access to produce extra columns. No problem for me because I'm an old hand at Access but if you are unfamiliar with it then you might want to reconsider this option. But this is a great solution for my needs. Watch out though there is license cost to pay!