Fun with the Office Integration Pack Extension for LightSwitch

Fun with the Office Integration Pack Extension for LightSwitch

  • Comments 27

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!

Leave a Comment
  • Please add 6 and 7 and type the answer here:
  • Post
  • That's quite amazing! And for free!

    Do you know if this will work with earlier versions of Office? Your screen shots show Office 2010.

  • Hi Mr Yossu,

    I haven't tried any other version. It would probably work with Office 2007 but not 2003 since that version of Word didn't have content controls.

    You should check it out. :-)

    Cheers,

    -Beth

  • Hi,

    Any Ideas on howto format the text e.g. date-times and currency? Or is it best to use the XML method as per the contoso demo app?

  • Hi P Stinson,

    I guess one way to do it would be to create computed string properties on the entity that formatted the data exactly how you wanted.

    Have fun,

    -B

  • Good of Grid Logic (Steve H) to provide the source of the vsix. It took me weeks to develop a similar Office extension, alas in C#, Beth! Perhaps you could include a reference to the Outlook COM reference if you wanted to programmatically send/create an email with these documents as an attachment. Even better how about creating these documents on the LS hosting server and presenting the client(s) with a link to them as well possibly using the server email them. Like the "Bookmark" trick, a bit better than the multiline custom control in the Contoso example, you learn something every day.

    I am having fun,

    -K

  • Beth - is there anything you need to install/enable to get the basic export to Excel functionality per your comment of "LightSwitch has a really nice feature on data grids that allow you to export them to Excel"?  My app by default doesn't have this functionality (a C# app)...

  • Hi Beth,

    Is there any plans for this extension to work for in-browser apps?  Even if it only has a subset of the features.

    Thanks,

    Dave

  • Hi Beth,

    How would you compare using Office Integration Pack Extension in LS with automating Excel/Word with VBA from LS? Which one is better for producing Excel/Word reports?

    Thanks,

    Gamon

  • Hi Beth

    It's possible to insert Image in Word Document using the Office Integration Pack Extension?

    Thank you in Advice

    Matteo

  • Hi Beth am trying to do Office Integration in my app. but am getting errors like

    System.security.security Exception. file open is not permitted

  • @Matteo, I'd take a look at the Document Toolkit extension to do that:

    www.microsoft.com/.../firstfloor

    @Sri, Sounds like you are hitting the Silverlight security issue. You need to open files from trusted locations only - i.e. "My Documents"

  • Hi Beth,

    Is it possible to export not only columns out of the tables but as well out of a screen.

    For example I have a order screen with a calculate data item showing the total amount of a order.  Can I export this total to the same word report.

    Thank you very much for your help

  • Heey Beth,

    Very Nice Post you got there ..

    but I have a question ..

    How can i put multiple entities in one word document ?

    I can show order and order details  but what if i want to add additional info about the organization, in the same document ?? How can i do something like that ?

    Can you please help mee ??  : )

    Thanks a lot in advance

    Kind Regards,

    3oon

  • What if I have more than one worksheet, how to export all worksheets together in excel?

  • Can you give me the usage detail of the Office Integration Pack

Page 1 of 2 (27 items) 12