How to Export Data to Microsoft Word with the LightSwitch HTML Client – Part 1 (Elizabeth Maher)

How to Export Data to Microsoft Word with the LightSwitch HTML Client – Part 1 (Elizabeth Maher)

Rate This
  • Comments 15

First, let me thank everyone for all the wonderful ideas for blog posts. In appreciation, I have decided to write about one of the most requested topics – how  do I take data and generate a beautiful Word document? In this article, we will cover how to create Web API Controller that uses the LightSwitchServerContext and Open XML SDK for Office to send a word document to the HTML client. Using the OpenXML SDK allows us to avoid using Microsoft Word itself on the server. Instead, we can generate the document format and merge our data into it. This provides much better scalability with web applications. 

In this first installment, I’ll show you the basics of creating a document on the server using the OpenXML SDK. We’ll just generically export the fields of an entity. Future installments will cover how to create real formatted reports. Open Xml is an open source project and the Open XML SDK for Office sources are available on GitHub.

Application Setup

First, things first. Let’s setup a basic LightSwitch project. I will be using a LightSwitch HTML Application. However, this article also applies to Cloud Business App projects.

In our example, we have an Event Manager application and an EventSession table that holds all the basic details of each event.

clip_image002

I have also used the Common Screen Set to create basic browse, view and edit screens.

clip_image003

clip_image005

Setting up the Web API Controller and Router

Okay, just a little more setup and we will get to the interesting code. First, let’s create the web controller class that will respond to the requests for reports. The controller class needs to be added to the Server project. As you can see in the picture below, the controller is named ExportToWordController.cs and has been added it to the Reports folder.

clip_image007

Our controller does no good without something to send it requests. We’ll route requests using a Global.asax item. If you don’t already have one in your server project, it is listed as Global Application Class in the Add New Item dialog.

image

Add the following using statements to Global.asax.

C#:

using System;
using System.Web.Routing;
using System.Web.Http;

VB:

Imports System
Imports System.Web.Routing
Imports System.Web.Http

Use the Application_Start method to add the routing logic for our ExportToWord functionality. The following code will set up the appropriate routing for either a summary report of an entity or a detailed report of a single entity.

C#:

protected void Application_Start(object sender, EventArgs e)
{
     RouteTable.Routes.MapHttpRoute("ExportToWord", 
         "reports/{controller}/{entity}/{id}", 
          new { id = RouteParameter.Optional });
}

VB:

 Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
        RouteTable.Routes.MapHttpRoute("ExportToWord",
            "reports/{controller}/{entity}/{id}",
            New With {.id = RouteParameter.Optional})
 End Sub

The above code adds the ability for the application to handle requests like http://server/MyLSAplication/reports/ExportToWord/Events for summary reports and http://server/MyLSAplication/reports/ExportToWord/Events/1 for detailed reports. We are going to focus on the detailed report first.

Creating a Detailed Report

Now, let’s write the code to create a detailed report in ExportToWordController.cs.  Below are the imports needed for the next section of code.

C#:

using Microsoft.LightSwitch;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Reflection;
using System.Web.Http;

VB:

Imports Microsoft.LightSwitch
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.IO
Imports System.Linq
Imports System.Net.Http
Imports System.Net
Imports System.Reflection
Imports System.Web.Http

Below is the controller code for the detailed report. The Get method takes two arguments; the entity name and id.  This matches with the format items specified in the earlier routing code in Global.asax. The code creates a ServerApplicationContext to get the query object, get the values for that record and create the Word document.

C#:

public HttpResponseMessage Get(string entity, int id)
{
     IDataServiceQueryable queryable;
     using (ServerApplicationContext ctx = ServerApplicationContext.CreateContext())
     {
          //Get the queryable object for the record requested
           switch (entity)
          {
               case "Events":
                    queryable = ctx.DataWorkspace.ApplicationData.EventSessions.Where(
                    x => x.Id == id);
                    break;
               default:
                    throw new ArgumentException("Unsupported query request");
          }

          Dictionary<string, object> entityData = 
               GetDetailedData(queryable);

          MemoryStream s = 
               CreateDetailedWordDoc("Details", entityData);

          return GetWordDocResponseMessage(s, entity + " - "  + id  + ".docx");
     }
 }
VB:
Public Function GetValues(ByVal entity As String, ByVal id As Integer) As HttpResponseMessage
        Dim queryable As IDataServiceQueryable
        Using ctx As ServerApplicationContext = ServerApplicationContext.CreateContext()
            Select Case entity
                Case "Events"
                    queryable = ctx.DataWorkspace.ApplicationData.EventSessions.Where(
                        Function(x) x.Id = id)
                Case Else
                    Throw New ArgumentException("Unsupported query request")
            End Select

            Dim entityData As Dictionary(Of String, Object) = GetDetailedData(queryable)

            Dim s As MemoryStream = CreateDetailedWordDoc("Details", entityData)

            Return GetWordDocResponseMessage(s, String.Concat(entity, " - ", id, ".docx"))
        End Using
End Function

Thanks to some code from teammate Burt Bielicki, the call to GetDetailedData gets the printable columns and data for the record in which we are interested. Code for GetDetailedData and related functions is below.

C#:

private Dictionary<string, object> GetDetailedData(IDataServiceQueryable queryable)
{
     Dictionary<string, object> dictionary = new Dictionary<string, object>();
     System.Collections.IEnumerator enumerator = queryable.GetEnumerator();

     //Get column headers
     PropertyInfo[] columnHeaders = null;
     if (enumerator.MoveNext())
     {
          columnHeaders = enumerator.Current.GetType().GetProperties(
               BindingFlags.Public | BindingFlags.Instance |
               BindingFlags.Static);
          columnHeaders = RemoveUnreadableProperties(columnHeaders);

          //write detailed values
           foreach (var heading in columnHeaders)
           {
                dictionary.Add(heading.Name,
                heading.GetValue(enumerator.Current));
            }
     }

     return dictionary;
}
private PropertyInfo[] RemoveUnreadableProperties(PropertyInfo[] columnProperties)
{
     return columnProperties.Where(p =>
                !p.PropertyType.IsArray
                && !p.PropertyType.IsGenericType
                && !"Microsoft.LightSwitch.Framework.Server.PersonInfo".Equals(
                    p.PropertyType.FullName)
                && !p.PropertyType.FullName.StartsWith("LightSwitchApplication")
                ).ToArray();
}

VB:

Private Function GetDetailedData(queryable As IDataServiceQueryable) _
As Dictionary(Of String, Object)
        Dim dictionary As New Dictionary(Of String, Object)()
        Dim enumerator As System.Collections.IEnumerator = queryable.GetEnumerator()

        'Get column headers
        Dim columnHeaders() As PropertyInfo = Nothing
        If enumerator.MoveNext() Then
            columnHeaders = enumerator.Current.GetType().GetProperties(
                BindingFlags.Public Or BindingFlags.Instance 
                Or BindingFlags.Static)
            columnHeaders = RemoveUnreadableProperties(columnHeaders)

            'Write detail values
            For Each heading In columnHeaders
                dictionary.Add(heading.Name, heading.GetValue(enumerator.Current))
            Next
        End If

        Return dictionary
End Function

Private Function RemoveUnreadableProperties(columnHeaders() As PropertyInfo) As PropertyInfo()
        Return (From p In columnHeaders
               Where Not p.PropertyType.IsArray AndAlso
               Not p.PropertyType.IsGenericType AndAlso
               Not "Microsoft.LightSwitch.Framework.Server.PersonInfo".Equals(
                   p.PropertyType.FullName) AndAlso
               Not p.PropertyType.FullName.StartsWith("LightSwitchApplication")).ToArray()
End Function

Putting the data in a Dictionary object is not necessary for the example, but it does make the code for creating the Word document easier to understand.

Creating a Word Document

Okay, time for the OpenXml code! To use OpenXml, the OpenXml SDK has to be installed. References to the DocumentFormat.OpenXml and WindowsBase must be added to the Server project.

Add the following imports to ExportToWordController.cs

C#:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;

VB:

Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Wordprocessing

The detailed report for the specified title will print out each property name and property value for that entity. The function returns a MemoryStream which will be used later when sending the HttpResponseMessage back to the client.

C#:

private MemoryStream CreateDetailedWordDoc(string title, Dictionary<string, object> entityData)
{
     MemoryStream s = new MemoryStream();

     //Create word document
          using (WordprocessingDocument wordDocument = 
                WordprocessingDocument.Create(s, WordprocessingDocumentType.Document, true))
           {
                    //Create the main document part, which is the part that holds the text.
                     wordDocument.AddMainDocumentPart();
                     wordDocument.MainDocumentPart.Document = new Document(new Body());
                     Body body = wordDocument.MainDocumentPart.Document.Body;
       
                     //Add Title to Document
                     body.Append(
                         new Paragraph(
                           new Run(new Text(title))
                        )
                    );

                     //Add Each property
                     foreach (var entry in entityData)
                     {
                         body.Append(
                            new Paragraph(new Run(new Text(entry.Key))),
                            new Paragraph(new Run(new Text(entry.Value.ToString()))),
                            new Paragraph(new Run(new Text("")))
                         );
                }
              
                wordDocument.MainDocumentPart.Document.Save();
                wordDocument.Close();
            }

            return s;
        }

VB:

 Private Function CreateDetailedWordDoc(title As String, entityData _
                                           As Dictionary(Of String, Object)) As MemoryStream
        Dim s As New MemoryStream()

        'Create Word document
        Using wordDocument As WordprocessingDocument =
            WordprocessingDocument.Create(s, WordprocessingDocumentType.Document, True)
            wordDocument.AddMainDocumentPart()
            wordDocument.MainDocumentPart.Document = New Document(New Body())
            Dim docBody As Body = wordDocument.MainDocumentPart.Document.Body

            'Add Title to Document
            docBody.Append(New Paragraph(New Run(New Text(title))))

            'Add Each property
            For Each entry In entityData
                docBody.Append( _
                    New Paragraph(New Run(New Text(entry.Key))),
                    New Paragraph(New Run(New Text(entry.Value.ToString()))),
                    New Paragraph(New Run(New Text("")))
                )
            Next
        End Using

        Return s
End Function

Sending a Document to the Client

The last piece of our ExportToWord controller is sending the document to the client. This can be done by crafting our own HttpResponseMessage. The first parameter is the document contents, including styles and text.  The second parameter is the name of the document to be created on the client’s machine.

C#:

private HttpResponseMessage GetWordDocResponseMessage(MemoryStream s, String suggestDocName)
{
     HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
            
     //write the document
      result.Content = new StreamContent(new MemoryStream(s.ToArray()));
     
       //Set the content type to .docx
        result.Content.Headers.ContentType = 
             new System.Net.Http.Headers.MediaTypeHeaderValue(
                    "application/vnd.openxmlformats-officedocument.wordprocessingml.document");
     
       //Set the name of the file
        result.Content.Headers.ContentDisposition = 
                new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment");
        result.Content.Headers.ContentDisposition.FileName = suggestDocName;
        return result;
}

VB:

Private Function GetWordDocResponseMessage(s As MemoryStream, suggestedDocName As String) _
        As HttpResponseMessage

        Dim result As New HttpResponseMessage(HttpStatusCode.OK)

        'Write the document
        result.Content = New StreamContent(New MemoryStream(s.ToArray()))

        'Set the content type to .docx
        result.Content.Headers.ContentDisposition = New Headers.ContentDispositionHeaderValue("attachment")
        result.Content.Headers.ContentDisposition.FileName = suggestedDocName

        Return result
End Function

Try it out

We are all set. To test our controller, F5 and add an EventSession to the application. After that is done, typing http://localhost:[debuggingPort]/reports/ExportToWord/Events/1 will generate a report for the event we created. It should look something like the following screen shot.

image

The next article in the series will cover how to add styling to the report.  I will also show how to add the ability to launch the report from a LightSwitch Html Client page.

Until next time,

-Elizabeth Maher, Senior SDET, Cloud Business Apps Team

Leave a Comment
  • Please add 4 and 4 and type the answer here:
  • Post
  • Thank you for this!

  • Thanks. Great. Can't wait for the next post on this.

    //Sven

  • Great article, thanks! Keep them coming...

  • Elisabeth, this is outstanding material, really !

    Both applicable for CBA as well as LightSwitch classic and really the type of enterprise application integration where LightSwitch is like a fish to water.

    Can't wait to read the next posts.

  • This is great!!  I would also love to see a post on using Open Xml to build a spreadsheet on the server such that you could export 100k+ entities from a Web api POCO getting data from ServerApplicationContext.

  • Great work!

    Love these new LS posts :)

    PS: why are you creating a memorystream over an existing memorystream? ;-)

  • Oops, meant to fix that before I published. :-)

    Correct code (for VB.NET) is

       s.Position = 0

       result.Content = New StreamContent(s)

  • Thank you!

  • Very encouraging. Will this solution let me use my computed field in the Word Report? Having to rework the computed field calculations in SQL to use Reporting Services is very unproductive.

    Yes, an Excel and PDF solution would be very useful as well.

  • Can OpenXML SDK use Word Templates?  It would be nice to design the .dotx in Word using styles, tables, etc.  instead of build\style it from scratch in code.

  • @Josh Booker- Yes, there is a to use OpenXml with templates.  I'm not the OpenXml expert, but I believe you can read the in the template and pass it in when creating the Word Document object.  I found some interesting articles regarding templates and OpenXml:

    blogs.msdn.com/.../creating-a-template-open-xml-document-in-memory.aspx

    blogs.msdn.com/.../accessing-openxml-document-parts-with-the-openxml-sdk.aspx

    blogs.msdn.com/.../oba-part-3-storing-and-reading-data-in-word-documents.aspx

  • Many thanks Elizabeth, nice writing, keep it coming!

  • Good article, when referring to "entity", is this utilizing a connection to the entity framework?

  • Great How To!

    There is a problem with the dictionary in function [CreateDetailedWordDoc], when you have an empty value (in the example the database field Description can be empty) the function will throw an exception.

    I've changed the code a bit to check if the dictionary key actually holds a value:

               'Add Each property

               For Each entry In entityData

                   If entry.Value <> vbNullString Then

                       docBody.Append(

                           New Paragraph(New Run(New Text(entry.Key))),

                           New Paragraph(New Run(New Text(entry.Value.ToString()))),

                           New Paragraph(New Run(New Text("")))

                       )

                   Else

                       docBody.Append(

                           New Paragraph(New Run(New Text(entry.Key))),

                           New Paragraph(New Run(New Text("")))

                       )

                   End If

               Next

    if the dictionary key is there, but it's value is empty, it will only return the key and not the value.

  • Thank you for showing C# as well as VB code!

Page 1 of 1 (15 items)