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 8 and 4 and type the answer here:
  • Post
  • i have been looking at the code i have it almost working now but i keep running into an object reference not set to an instance of an object error.

    the following is the information from the error

    System.InvalidOperationException was unhandled by user code

     Message=Failed to create claim report.

     StackTrace:

          at claimsmanagement.MyReports.RunClaimReportFixedTemplate(Claim claim)

          at SBW.SearchClaims.Button1_Execute()

          at SBW.SearchClaims.DetailsClass.MethodSetProperties._Lambda$__756(DetailsClass d, ReadOnlyCollection`1 args)

          at Microsoft.LightSwitch.Details.Framework.Internal.BusinessMethodImplementation`2.<InvokeMethod>b__6()

          at Microsoft.LightSwitch.Utilities.Internal.UserCodeHelper.CallUserCode(String userCodeName, Action action, Boolean swallowException, Exception& exception)

     InnerException: System.NullReferenceException

          Message=Object reference not set to an instance of an object.

          StackTrace:

               at claimsmanagement.MyReports.RunClaimReportFixedTemplate(Claim claim)

          InnerException:

  • And now i fixed that, im back to having no data pulled to my .docx template when it brings microsoft word up. It has all the fields just doesn't pull any of their values from my claims i'm really not sure where i messed this up, i cant even figure out where its pulling the fields from my database to fix it.

  • I must be going in circles, cause im right back to this

    System.InvalidOperationException was unhandled by user code

     Message=Failed to create claim report.

     StackTrace:

          at claimsmanagement.MyReports.RunClaimReportFixedTemplate(Claim claim)

          at SBW.SearchClaims.Button1_Execute()

          at SBW.SearchClaims.DetailsClass.MethodSetProperties._Lambda$__756(DetailsClass d, ReadOnlyCollection`1 args)

          at Microsoft.LightSwitch.Details.Framework.Internal.BusinessMethodImplementation`2.<InvokeMethod>b__6()

          at Microsoft.LightSwitch.Utilities.Internal.UserCodeHelper.CallUserCode(String userCodeName, Action action, Boolean swallowException, Exception& exception)

     InnerException: System.NullReferenceException

          Message=Object reference not set to an instance of an object.

          StackTrace:

               at claimsmanagement.MyReports.RunClaimReportFixedTemplate(Claim claim)

          InnerException:

  • This is the code for my execute... if i enable the fixed template i get the above error. However if i enable the dynamic template... i just get the word document with no information in it other then the labels with no data. This is a rather important project... i really wish LightSwitch would get on fixing this printing insanity.

    Namespace SBW

       Public Class SearchClaims

           Private Sub Button1_Execute()

               ' Write your code here.

               'claimsmanagement.MyReports.RunClaimReportFixedTemplate(Me.ClaimCollection.SelectedItem)

               'claimsmanagement.MyReports.RunClaimReportDynamicTemplate(Me.ClaimCollection.SelectedItem)

           End Sub

       End Class

    End Namespace

  • Hi Adam,

    Does my customer example code work as expected for you?

    The report method isn't pulling anything from the database, it's just looking at the fields on the entity you pass it. You have to make sure the report template control titles match the property names on your entity otherwise the XML you create won't bind properly. First set a breakpoint after the LINQ query and make sure the XML is generated properly. The elements you see in there match the property names on the entity. If that looks good then check the report template and make sure the control titles are set correctly.

    This is just a simple example of client-side reporting, you may want to look at Reporting Services if you need to create more complex reports directly against your database.

    And just so you know, Beta 1 doesn't have a Go Live licence so you can't use it for any production systems yet. This Beta is for testing purposes only. I mention this because you say "this is an important project" and I want to make sure you understand that. When we release the RC we may break your Beta 1 appplications. We're still under development over here.

    Thanks!

    -Beth

  • Beth,

    Yes the customer code works for me. im just trying to apply that same code to my current stuff. i cant seem to get the fields to populate... im not sure where i may have name complication. also i keep getting that error when i press my print button, im not sure where the null reference is pulling from.

  • Hi Adam,

    You should be able to tell what the null reference exception is if you put a breakpoint on the call to the report method and step through the code, line by line, and looking in the watch window for the value of variables. Is the LINQ query creating the correct XML? Is the report template control titles match your field names?

    Try creating a totally new screen for your claim with no other code in it except the print method. There may be some other code causing your error.

    HTH,

    -B

  • RunClaimReportDynamicTemplate Argument not specified for parameter 'claim' of 'Public Sub RunClaimReportDynamicTemplate(claim As SBW.Claim)'

    seems to be the problem with both the dynamic and the Fixed teplate. im not sure why the argument is not specified?

  • Sounds like you need to put a null check around the SelectedItem being passed to the report.

  • The selected item is in a claim collection on the left side of the screen which then shows the data on the right side of the screen going down. I figured this code here in my button would use the data of the current claim i had selected. but it finds a null reference when the claim most certainly exists. claimsmanagement.MyReports.RunClaimReportFixedTemplate(Me.ClaimCollection.SelectedItem)

    again these are the errors i'm getting when it attempts to run through the code.

    Try Expression expected.

    RunClaimReportDynamicTemplate Argument not specified for parameter 'clai' of 'Public Sub RunClaimReportDynamicTemplate(clai As Claim)'.

    RunClaimReportFixedTemplate Argument not specified for parameter 'clai' of 'Public Sub RunClaimReportFixedTemplate(clai As Claim)'.

    RunClaimReportFixedTemplate Argument not specified for parameter 'clai' of 'Public Sub RunClaimReportFixedTemplate(clai As Claim)'.

    im not sure what id have to check for null in here, nor how to go about it seeing as DBNull doesnt seem to work here.

  • When i am debugging its definitely taking the data from my page in. when i highlight clai in (clai As Claim) in my code it it taking in the data from the claim. Also when it gets down to clai.details.property.All clai still is holding all the data. It seems that because i allow some fields to be null that you are right about the null reference, im not sure how to place the if nothing in my code. The clai is definitely holding data though, only SOME of the fields are null that its holding i thought it would just replace the null fields with "-" but apparently that's only for Nothing not null. so i should check for null and replace with nothing?

  • Sorry its early not sure why i was saying change null to nothing.

  • Beth,

    Thank you for all your help so far, im still having trouble getting this to put info into my pages... and i cant figgure out how to let it pass nothing as a parameter. These are my 2 subs that do the runs

    Namespace SBW

       Namespace claimsmanagement

           Module MyReports

               Public Sub RunClaimReportDynamicTemplate(ByVal claimid As Claim)

                   If AutomationFactory.IsAvailable Then

                       Try

                           Dim templateFile = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) +

                                      "\AdamsReportTemplates\ClaimDetailReport.docx"

                           Dim myXML =

                               <ns0:root>

                                   <claim>

                                       <%= From prop In claimid.Details.Properties.All

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

                                       %>

                                   </claim>

                               </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]/claim[1]/" + ctrl.Title.ToString.ToLower(),

                                                   "xmlns:ns0=""urn:microsoft:claimsmanager:claim""",

                                                   customXMLPart)

                                   End If

                               Next

                               word.Visible = True

                           End Using

                       Catch ex As Exception

                           Throw New InvalidOperationException("Failed to create claim report.", ex)

                       End Try

                   End If

               End Sub

               Public Sub RunClaimReportFixedTemplate(ByVal claimid As Claim)

                   'If claimid Is Nothing Then

                   'Else

                   If AutomationFactory.IsAvailable Then

                       Try

                           'Create the XML data from our entity properties dynamically

                           Dim myXML = <claim>

                                           <%= From prop In claimid.Details.Properties.All

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

                                           %>

                                       </claim>

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

                               'The report template is included in the ClientGenerated project with the Build Action set to Content

                               Dim resourceInfo = System.Windows.Application.GetResourceStream(New Uri("ClaimDetailReport.docx", UriKind.Relative))

                               Dim fileName = CopyStreamToTempFile(resourceInfo.Stream, ".docx")

                               Dim doc = word.Documents.Open(fileName)

                               'Grab the existing bound custom XML in the doc

                               Dim customXMLPart = doc.CustomXMLParts("urn:microsoft:claimsmanager:claim")

                               Dim all = customXMLPart.SelectSingleNode("//*")

                               Dim replaceNode = customXMLPart.SelectSingleNode("/ns0:root[1]/claim[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 claim report.", ex)

                       End Try

                   End If

                   'End If

               End Sub

    if i run the button using the claims report fixed template.. the word document comes up but has all blank fields (strange cause stepping through the build the claimid holds all the information in it. it doesn't seem to put it anywhere though just hold it and then nothing (even though i can clearly see where it should be pushing the info out to the word doc. Also if i run the fixed template... this just seems to give me the null reference problem, i don't know why one would give me the error, while the other simply skips over it and gives me a word doc with fields and no data.  sorry if this is complicated i know you have better things to do.

  • Thank you so much Beth, without all this i don't know if i would've ever figured this out. With a bit of tweaking I am pulling from multiple tables into my report. After a few days of messing with this i feel rather accomplished, again thank you for all your help Beth. Anyone who has any questions or anything about pulling from multiple tables or anything about this really, question me at adamneiport@hotmail.com.

  • Hi Adam, glad you figured it out.

Page 3 of 6 (86 items) 12345»