Fun with the Office Integration Pack Extension for LightSwitch

Last week Grid Logic released a FREE LightSwitch extension called the Office Integration Pack which has quickly risen to the second most popular LightSwitch extension on VS Gallery! It lets you populate documents and spreadsheets with data, create email and appointments with Outlook, import data from Excel, create PDFs, and a bunch of other stuff from your LightSwitch desktop applications. I’ve been known to do a bit of Office development in my day ;-) so I thought I’d check this extension out myself. In this post I’ll show you a couple tips for exporting data to Excel and Word that I learned while I was playing around.

Installing the Office Integration Pack

First thing you need to do is get the Office Integration Pack installed. You’ll also want to download the sample application and documentation. (BTW, the source code is also provided for free here!) You can download and install the Office Integration Pack directly from Visual Studio LightSwitch via the Extension Manager or you can download it manually from the Visual Studio Gallery.

image

Once you install the extension, restart Visual Studio. Then you will need to enable the extension on your LightSwitch project by opening the project properties, clicking the Extensions tab, and then checking the Office Integration Pack.

image

Now let’s explore some of the things this baby can do.

Export to Excel

LightSwitch has a really nice feature on data grids that allow you to export them to Excel:

image

This gives users a basic way of getting data out of the system to create reports or do further analysis on the data using Excel. However, you can’t call this feature from your own code. One of the great features of the Office Integration Pack is it not only lets you call the Export from code, it also allows a bunch of customization. You can control exactly what fields are exported as well as specify what worksheet the data should be exported into.

For instance say I have a list of customers on my own search screen (like pictured above) and I want to provide my own export that only exports CompanyName, ContactName, ContactTitle and Phone fields. In the screen designer first add a button onto the Data Grid’s command bar, I’ll call it ExportToExcel.

image

In the property window you can then specify an image to display if you want. We can also turn off the default Excel export on the grid by selecting the Customers Data Grid and in the properties window check “Disable Export to Excel”

image

Now we need to write some code to export the fields we want. Right-click on the Export to Excel button and select “Edit Execute Code”. We can use a couple different OfficeInetgration.Excel.Export APIs to do what we want here. The way I usually learn about a new API is through IntelliSense so if we start typing “OfficeIntegration (dot) Excel (dot)” you will see the list of available methods:

image

The Export method has four overloads. The first and simplest just takes the data collection and will export the all the data and fields to a new workbook, similar to the built-in Excel export. The second overload lets us specify a particular workbook, worksheet and range. In our case we want to specify particular fields as well and there’s a couple ways we can do that. The 3rd and 4th overloads let us specify a ColumnNames parameter which can take two forms. One is just a simple List(Of String). Just fill the list with the field names you want to export.

 Private Sub ExportToExcel_Execute()
 Dim ExcelFile = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\Customers.xlsx"

    If File.Exists(ExcelFile) Then
        Dim fields As New List(Of String) From
            {"CompanyName", "ContactName", "ContactTitle", "Phone"}

        OfficeIntegration.Excel.Export(Me.Customers, ExcelFile, "Sheet1", "A1", fields)
    End If
End Sub

Another way we can do this is by specifying a List(Of OfficeIntegration.ColumnMappings). The ColumnMappings class is used in many of the APIs particularly the Import method where you could specify both the column in the workbook and the property on the entity in order to map them. In the case of an Export, this isn’t necessary, all I need to do is specify the properties (fields) on the entity I want to export.

Now when we run the application and click our export button we will see only the fields we specified exported to Excel.

image

Export to Word

We can also export data to Word as well. There are a couple methods you can take advantage of here. One is called GenerateDocument which lets you define a template of Content Controls in which the data will be exported. Content controls are a great way for capturing data inside of Word documents. Let’s create a Word document that reports all of a customer’s orders. First I’ll create a Details Screen for my customer and select to include the Customer Orders as well. This will create a one-to-many screen that has the customer detail and a grid of their orders below.

image

Next I’ll add a button to the screen, this time in the screen command bar at the top, called “Generate Document”.

image

Next we need to create the template in Word and add the content controls where we want them which will be populated with data from our customer entity. First enable the Developer tab in Word (File –> Options –> Customize Ribbon, then check to enable the “Developer” tab). Lay out simple text controls around your template and format it how you want. Then click properties to name the controls. You can name the content controls anything you want. Later we will specify the ColumnMapping between the Title of the content controls and the customer properties.

image

We also want to create a table of related orders into this document. In order to create tables, you create a table in Word and then bookmark it. You can optionally create the column headers manually or you can have the Office Integration Pack output them for you. I’ll create a nicely formatted table with two rows for this one with my own column headers in the first row. Then I’ll bookmark it “OrderTable”.

image

Finally we need to write some code to first call GenerateDocument to populate our content controls and then make a call to ExportEntityCollection to export the collection of related Orders into the bookmarked table in Word. I’ll also generate a PDF from this by calling the SaveAsPDF method. Back on the screen right-click on the GenerateDocument command and “Edit Execute Code” and write the following:

 Private Sub GenerateDocument_Execute()

    Dim MyDocs = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
    Dim WordFile = MyDocs & "\Customer.docx"

    If File.Exists(WordFile) Then

        'Map the content control tag names in the word document to the entity field names
        Dim custFields As New List(Of OfficeIntegration.ColumnMapping)
        custFields.Add(New OfficeIntegration.ColumnMapping("ContactName", "ContactName"))
        custFields.Add(New OfficeIntegration.ColumnMapping("CompanyName", "CompanyName"))
        custFields.Add(New OfficeIntegration.ColumnMapping("Phone", "Phone"))

        Dim doc As Object = OfficeIntegration.Word.GenerateDocument(WordFile, 
                                                                    Me.Customer, custFields)

        'Export specific fields to the bookmarked "OrderTable" in Word
        Dim orderFields As New List(Of String) From
            {"ShipName", "OrderDate", "ShippedDate"}

        OfficeIntegration.Word.ExportEntityCollection(doc, "OrderTable", 2, False, 
                                                      Me.Customer.Orders, orderFields)
        
        OfficeIntegration.Word.SaveAsPDF(doc, MyDocs & "\Customer.pdf", True)
    End If
End Sub

When you run it you’ll end up with a Word document and a PDF displayed on the screen with our data formatted perfectly!

image

Note that a lot of these methods return the document (or workbook) automation object so you can make additional late-bound COM calls to do anything else you want to automate with Office. If you go that route here are the Excel and Word COM developer references that you’ll want handy ;-).

Have fun using the Office Integration Pack. Thank you Grid Logic!

Enjoy!