Using Microsoft Word to Create Reports For LightSwitch (or Silverlight)

Using Microsoft Word to Create Reports For LightSwitch (or Silverlight)

Rate This
  • Comments 86

Note: This post has been updated for Beta 2 on 3/24/2011

LightSwitch has a really nice feature on data grids that allow you to export them to Excel if running as a Desktop application (out of browser).

image

This gives you a basic way of getting data out of the system to create reports or do further analysis on the data using Excel. However, in business applications you typically want to provide some client-side reporting that also formats the data in a certain way so it can be printed easily. Microsoft Word is a great tool for this.

So the last couple days I’ve been playing around with COM automation in LightSwitch and using Word to create client-side reports based on LightSwitch entities. Ideally I’d like to use Word 2007/2010 Open XML features & Content Controls to generate these reports. I’ve written a lot about using Open XML in the past. It’s a great way to manipulate documents without having to have Office installed on the machine, which is especially handy for server-side generated documents. If you haven’t checked out the Open XML SDK yet, I highly recommend it. In a nutshell, Word, Excel and  PowerPoint documents are just .ZIP files with XML inside. The Open XML SDK wraps the .NET System.IO.Packaging classes to make it easy to access the contents.

Unfortunately the Open XML SDK and the System.IO.Packaging classes are not available in Silverlight and that’s what we’re working with when we create a LightSwitch client. Fortunately, however, Silverlight now supports calling COM components so we can use the Office object models to manipulate the documents on the client (or do anything else that this rich OM gives us). For this exercise I want to allow the user to create a “report” in Word by defining content controls on the document surface. As long as they name the controls according to the fields on the entity, we can easily add some custom XML into the document and bind the controls to that data from our LightSwitch (or Silverlight) client. This gives the end user the flexibility of creating reports, letters, memo’s or anything else you can do with Word. This is similar to a mail-merge except users can invoke this quickly right from within your LightSwitch application.

Creating the Template

To begin, I suggest downloading the Content Control Toolkit. Even though we’re going to bind the content controls in code, you can use this to play around with how you want your custom XML to look and bind. For instance, I created a template in Word that has some content controls laid out how I want them on the page in order to create a simple customer report. To add content controls to a document you first need to enable the Developer tab. You do this by opening up the File –> Options –> Customize Ribbon and making sure the developer tab is checked.

image

Now you can use the controls to lay out the fields on your document. Click Properties and enter a title that corresponds to the fields on your entity (in my case Customer). You can also lock the controls so that users cannot delete or edit them.

image

Here’s what my simple Customer report looks like in design mode:

image

Binding Data Manually with the Content Control Toolkit

Now all we need to do is add our custom XML with the actual data to the document and then bind that data to these content controls. We can do that dynamically in code (and we will), but you can also use the content control toolkit to do it manually. This is an option if you don’t want users to create the templates, instead you want to supply them with your application.

When you open the document with this tool you see a view of all the content controls on the left and any custom XML parts on the right. In fact, a document can store multiple custom XML parts separated by namespaces that you control allowing you to create some pretty complex binding if needed. First you specify the XML data that you want to bind by clicking “Create a new Custom XML Part” under Actions on the bottom right of the tool. Then you can paste in your XML into the Edit View. For this example we will use this simple set of data which correlates to fields on my Customer entity (note that I’m using all lower case in the elements, XML is case sensitive!):

<ns0:root xmlns:ns0="urn:microsoft:ordermanager:customer">
    <customer>
        <lastname>Massi</lastname>
        <firstname>Beth</firstname>
        <gender>F</gender>
        <phone>5551212</phone>
        <email>bethma@microsoft.com</email>
        <address1>1234 Main Street</address1>
        <address2></address2>
        <city>San Francisco</city>
        <state>CA</state>
        <postalcode>94115</postalcode>
    </customer>
</ns0:root>

Once you enter the custom XML into the Edit View, you can flip to the Bind View and then drag the XML elements onto the content controls to bind them (make sure you click twice on the element before dragging it). This sets up the XPath binding expressions.

image

Click Save and then you can open it back up in Word to see the report filled out with data. So what exactly did this do? I mentioned that Word, Excel and PowerPoint files are just .ZIP packages with XML inside. To see our custom XML part in the package just rename the .docx file to .zip, then look inside the customXML folder and open the item1.xml to see the custom XML we entered above.

image

Next we’ll set up a class to handle generating the data for this report. I’ll first show how we can distribute this already bound template with our LightSwitch application and populate it with customer data then I’ll show how we can populate a user-supplied Word document and bind the content controls dynamically in code.

Creating the Report Module

First we’ll need to write a class that encapsulates the report logic. Then we can call this from a button on our Customer screen to generate the report. To add your own class to the LightSwitch client you’ll first need to switch to File View in the Solution Explorer and then you can right-click on the Client project and add your class. I named mine MyReports.vb.

image

First thing I’m going to do is rename the Class to a Module since I’m using Visual Basic and put it in the same namespace as my application.This creates a Shared/static class for you so that it’s easy to call the report methods. I’m also going to need to import a couple namespaces, one for the LightSwitch COM automation and the XML namespace that we used in the custom XML part above. (We’ll need the XML namespace when we create the custom XML and bind to it dynamically later.)

Imports System.Runtime.InteropServices.Automation
Imports <xmlns:ns0="urn:microsoft:ordermanager:customer">

Namespace LightSwitchApplication
Module MyReports End Module End Namespace

Generating a Report from the Fixed Template

Now that we have our report module set up we can write our code that creates the XML data and then stuffs it into the document. In this first example we’re going to use the template we created above that already has the content controls bound to the custom XML part.

First we need to check if the COM automation is available – meaning that the application is running out-of-browser with elevated permissions. Next we create an XElement with our customer data. In this case it’s really easy to use LINQ to XML and XML Literals to loop through all the properties of the customer entity dynamically. That way if we add new fields to our Customer entity, we don’t need to change this code at all. (If you’re new to LINQ to XML you can read a few articles I’ve written before here, particularly Getting Started with LINQ to XML.) The query uses the Details property on the entity to get at the collection of properties (fields) on the Customer. Then I create the XML element based on the property name, and then write out the value. If the value is blank then I want to put a dash (-) in the report.

Next thing we do is create the Word automation object using the LightSwitch System.Runtime.InteropServices.Automation.AutomationFactory (if you are using plain Silverlight then you would use System.Windows.Interop.ComAutomationFactory). We call CreateObject passing it the name of the registered COM object to instantiate, in this case Word.Application. Then we can open the document and find the custom XML part by its namespace. Please note that this will throw an exception if it’s not found so you better wrap all your automation code in a Try…Catch block. COM is all about late binding so you won’t get any intellisense on the members either so the MSDN documentation on the Word object model will be close by. When you’re doing COM programming from .NET it’s helpful to set debugging breakpoints and then explore the members dynamically using the immediate and watch windows.

Once we get the custom XML part we can replace the <customer> node with our data. We do this using XPath to select the node we want to replace. (BTW, I am totally lame at XPath that’s why I use the Content Control toolkit to help me by looking in the bindings.)

Once we replace the custom XML with our data we can show the document to the user so they can print it.

Public Sub RunCustomerReportFixedTemplate(ByVal cust As Customer)
    If AutomationFactory.IsAvailable Then
        Try
            'Create the XML data from our entity properties dynamically
            Dim myXML = <customer>
                            <%= From prop In cust.Details.Properties.All
                                Select <<%= prop.Name.ToLower %>><%= If(prop.Value, "-") %></>
                            %>
                        </customer>

            Using word = AutomationFactory.CreateObject("Word.Application")
                Dim doc = word.Documents.Open("C:\Reports\CustomerDetails.docx")
'Grab the existing bound custom XML in the doc Dim customXMLPart = doc.CustomXMLParts("urn:microsoft:ordermanager:customer") Dim all = customXMLPart.SelectSingleNode("//*") Dim replaceNode = customXMLPart.SelectSingleNode("/ns0:root[1]/customer[1]") 'replace the <customer> node in the existing custom XML with this new data all.ReplaceChildSubtree(myXML.ToString, replaceNode) word.Visible = True End Using Catch ex As Exception Throw New InvalidOperationException("Failed to create customer report.", ex) End Try End If End Sub

Distributing the Report Template with the LightSwitch Client

Notice that in the above code we are hard-coding the path to where the template is located “C:\Reports\CustomerDetails.docx”. Another option would be to include the report template in the client directly. LightSwitch creates a client XAP file located in your <project>\bin\Release\Web folder and this is what is deployed & running on a user’s machine. To add the CustomerDetails.docx report template, right-click on the ClientGenerated project, then select “Add Existing Item” to select the document and then set the Build Action property to “Content” as shown below:

image

Now we can change our code above to read the file using GetResourceStream:

Dim resourceInfo = System.Windows.Application.GetResourceStream(
New Uri("CustomerDetails.docx", UriKind.Relative)) Dim fileName = CopyStreamToTempFile(resourceInfo.Stream, ".docx") Dim doc = word.Documents.Open(fileName)

We just need a couple helper methods in our MyReports module to write the resourceInfo.Stream to disk:

Private Function CopyStreamToTempFile(ByVal stream As System.IO.Stream, ByVal ext As String) As String
    Dim path = GetTempFileName(ext)
    'Create the temp file
    Dim file = System.IO.File.Create(path)
    file.Close()
    'Write the stream to disk
    Using fileStream = System.IO.File.Open(path,
                                           System.IO.FileMode.OpenOrCreate,
                                           System.IO.FileAccess.Write,
                                           System.IO.FileShare.None)

        Dim buffer(0 To stream.Length - 1) As Byte
        stream.Read(buffer, 0, stream.Length)
        fileStream.Write(buffer, 0, buffer.Length)

        fileStream.Close()
    End Using
    Return path
End Function

Private Function GetTempFileName(ByVal ext As String) As String
    'Return a uinuqe file name in My Documents\Reports based on a guid
    Dim path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\Reports"
    If Not Directory.Exists(path) Then
        Directory.CreateDirectory(path)
    End If

    Dim filename = Guid.NewGuid().ToString() & ext
    path = System.IO.Path.Combine(path, filename)
          
    Return path
End Function

Generating a Report from a User-Defined Template

The above works nicely if we have fixed templates but I want to allow the user to create these templates themselves. However, I don’t want to have make them do any of the binding. All I want them to do is to lay out the content controls where they want them on the document surface and then set their Title to the field name they want to appear. This means that our code will need to add the custom XML to the document and then dynamically bind it to controls it finds.

To do that we first create the custom XML, but instead of starting with the <customer> element like the above, we need to create the entire tree starting with the <root>. By importing the XML namespace at the top of our code file, Visual Basic will automatically handle putting this XML into that namespace when it is generated. We just specify the namespace on the root element like <ns0:root> and the rest is the same as before.

Now after we open the report template document we need to make a copy of it, I’m doing that in My Documents\Reports. Then we can add the custom XML part and loop through all the content controls that aren’t already bound in order to set the binding to the correct XPath expressions. These are the same XPath expressions that you see in the Content Control toolkit (that’s why it’s handy to install it). So if I find a content control with the title “LastName” then it will bind to the XPath /ns0:root[1]/customer[1]/lastname. We also need to specify the namespace and the part to bind to in the call to XMLMapping.SetMapping.

Public Sub RunCustomerReportDynamicTemplate(ByVal cust As Customer)
    If AutomationFactory.IsAvailable Then

        Try
            Dim templateFile = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) +
                               "\Reports\CustomerDetails.docx"

            'Create the XML data from our entity properties dynamically
            Dim myXML = <ns0:root>
                            <customer>
                                <%= From prop In cust.Details.Properties.All
                                    Select <<%= prop.Name.ToLower %>><%= If(prop.Value, "-") %></>
                                %>
                            </customer>
                        </ns0:root>

            Using word = AutomationFactory.CreateObject("Word.Application")

                Dim tempFile = GetTempFileName(".docx")
                File.Copy(templateFile, tempFile)

                Dim doc = word.Documents.Open(tempFile)

                'Add the new custom XML part to the document
                Dim customXMLPart = doc.CustomXMLParts.Add(myXML.ToString())

                'bind any content controls that we find based on the title of the control
                For i = 1 To doc.ContentControls.Count
                    Dim ctrl = doc.ContentControls(i)

                    If Not ctrl.XMLMapping.IsMapped Then

                        ctrl.XMLMapping.SetMapping(
"/ns0:root[1]/customer[1]/" + ctrl.Title.ToString.ToLower(), "xmlns:ns0=""urn:microsoft:ordermanager:customer""",
customXMLPart) End If Next word.Visible = True End Using Catch ex As Exception Throw New InvalidOperationException("Failed to create customer report.", ex) End Try End If End Sub

Calling the Report Module from the Customer Screen

All that’s left is calling this baby from a button on our Customer screen. I want to put this on the screen’s command bar (the ribbon across the top) so in the Screen Designer expand the Screen Command Bar at the top, Click Add to add a new button and name it Print. Then right-click on it and select “Edit Execute Code”

image

Then we can write code that calls the report and passes it the Customer entity on the screen. We can also edit the CanExecute code so that the button is only enabled for out-of-browser deployments.

Private Sub Print_Execute()
    ' Write your code here.
    MyReports.RunCustomerReportDynamicTemplate(Me.Customer)
End Sub

Private Sub Print_CanExecute(ByRef result As Boolean)
    ' Write your code here.
    result = System.Runtime.InteropServices.Automation.AutomationFactory.IsAvailable
End Sub 

Now when we run the application, users can click on the Print button on the Customer screen and generate reports that they created in Word.

image

With COM automation available in Silverlight a lot of possibilities open up for business applications that need to interact with Office. I hope I’ve showed you one practical way to get simple client-side reporting into a LightSwitch or Silverlight application. (UPDATE: I've attached a sample to the bottom of this post that demonstrates these techniques. You'll need to install LightSwitch Beta 2 to run the sample. )

To download LightSwitch & access instructional videos and articles please visit the LightSwitch Developer Center.

Enjoy!

Attachment: LightSwitchReportSample.zip
Leave a Comment
  • Please add 4 and 8 and type the answer here:
  • Post
  • Beth thank you for your reply.

    I can only assume that I will need to create a custom control to accomodate what I am looking to implement.  

    User needs a checkboxlist of all the documents (word templates) in a folder.  They will need to select only the documents needed to print at a given time for a specific person's record.

    thank you.

  • Hi,

    Does anyone have a C# version of this.  Sorry but I dont understand VB.

  • Great work Beth , is there a easy way to do this form an in browser web application ?

  • I have a DATETIME field for inserts, but when I query the field I need to display as only DATE.  Have any ideas?  

    Have: 2011-09-11T05:40:00

    Need: 11/09/2012

  • Hi Beth,

    That code Select <<%= prop.Name.ToLower %>><%= If(prop.Value, "-") %></>

    get prop.Value, it's ok.

    But in my database value is "Customer Name Samir     "

    I need return value out of spaces, "Customer Name Samir"

    How can I do this?

    Thanks

  • @marybeth - You can check IsDate and format the string appropriately with the Format() function.

    @SamirMMBr - You can just Trim the values: Trim(If(prop.Value, "-"))

  • @marybeth - You can check IsDate and format the string appropriately with the Format() function.

    @SamirMMBr - You can just Trim the values: Trim(If(prop.Value, "-"))

  • Dear Beth

    Thanks alot Dear i like your article

  • I agree with other posts, too complicated for LightSwitch.  Would love to see an easier way that's built into LS to export a simple printable report, or export to a PDF.

  • Hi guys! I wrote this post over a year ago and since then there are a couple LightSwitch extensions that make working with Office documents really easy. I recommend this free one: officeintegration.codeplex.com

    I've written about it before (blogs.msdn.com/.../fun-with-the-office-integration-pack-extension-for-lightswitch.aspx) and it has the same functionality as above as well as exports to PDFs.

    You can download more LightSwitch extensions on the VS gallery:

    visualstudiogallery.msdn.microsoft.com/.../search

    Cheers,

    -Beth

  • Hi Beth, i am new to lightswitch. I would be very grateful if you could make a video of this and upload it to youtube. my email is:      godwinkasare@yahoo.com

Page 6 of 6 (86 items) «23456