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 5 and 6 and type the answer here:
  • Post
  • Hi Beth,

    The Demo at VSLive2010 was very nice especially around minute 37, where they write code with the WordDocumentHelper,

    is this code somewhere available for download?

    Regards,

    Luc

  • This is really horrible !! having watched the demo I thought integration with word would be much simpler.

  • Hi Peeters,

    I'll see if I can dig up that code.

  • Hi Bubbles,

    You can do anything you want with the Office object models, I just chose to use Custom XML bound to content controls so that you could allow users to create the report templates themselves. What do you find complex about it? How would you want it to work?

    -Beth

  • Hi

    I am reading your articles and I am making my first steps in Lightswitch. I have one question

    I have an editable datagrid with several rows. Each row has a commandbutton. When clicking on the button, the rowdat is copied to another object and then the row must be readonly. How can I achieve this ?

  • Hi Wim De Vriendt,

    I don't know how to do that offhand. Pleasew ask your question in the LightSwitch forums, there are a lot more people there that could help:

    social.msdn.microsoft.com/.../threads

    Cheers,

    -Beth

  • hello,Beth Massi ,in your way,i could show the data,but i can't save it.

  • hello,Beth Massi ,in your way,i could show the data,but i can't save it.

  • Hi Iswiner,

    What do you mean by save the data? Are you having trouble populating the XML into the document or are you having trouble writing the document to disk?

    -B

  • It would be helpful if you could post the sample project.  I can't get this to work just clipping the code segments from the article.  Many thanks for the documentation.

  • @Will, I attached a sample LightSwitch project to the end of the post. When you open it, ignore the errors in the error list, just build it and they will go away. I added one customer record to the database (me), just click on my name to open the Customer Details and you will see the print button on the top. Also check out the comments in the code.

    @PeetersL, The sample also contains those helper classes you saw at VSLive, although I am not using them directly from my code.

    Have fun,

    -B

  • Thanks for posting the code.  I am implementing in Silverlight and C# and there is always a slight typo here or there that is easier to find when you have the complete source code.  I found it an have it working.  

    The application of this technique is in the context of patient management system for a university cancer center, to print patient and physician correspondence during the treatment process.  

    Thanks again.

  • I have made a printing button based off of this articles instructions here. the only thing im confused about is where it should be pulling my data from my current claim. This only seems to print the same document with the same information no matter what claim im on. im lost as to where the bind happens that pulls the data from my database and not just what i had put in the template. im not sure if theres somewhere in my code to bind the data, or if i have to do it in the xml... but this isnt using any live data its just pulling from when i put the static examples into the word doc using the control toolkit.

    sorry if the way ive worded this is confusing. i seem to be lost myself, the whole reason i made this is so i could print the report of the current claim, not just a single claim, thats way to much work to only be able to show the 1 claim.

  • Hi Adam,

    In the example above I'm just passing the entity that is on the screen from a button that I've put on the command bar. If you pull up a different customer then that one is passed instead. I attached the code to the end of the article, is that not working for you?

  • Hi Adam,

    This binding happens automatically in LightSwitch. You should have a property on the screen named after your entity. Did you take a look at the sample code?

    -B

Page 2 of 6 (86 items) 12345»