Fun with the Office Integration Pack Extension for LightSwitch

Fun with the Office Integration Pack Extension for LightSwitch

  • Comments 28

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 4 and 4 and type the answer here:
  • Post
  • My EMAIL is 649981528@qq.com

  • Hi Beth - I downloaded the source code and am pretty impressed with the capabilities. But, as always, the user wants so much more than is offered. The assumption the designers made is that the user would interact with the document and then save it under some new name with the changes. I am looking for a higher degree of automation - create a new document, populate it and save it (for archival purposes) and then mail it as an attachment. A copy would be cc'd to the user. So, I will continue with my search for Excel automation (what methods and properties are exposed through the interop interface, but thanks for the great writeup.

  • Hi Beth,

    Thanks for the awesome work. Will it be possible to close the word document as soon as the pdf file is created? I don't want to interact with MS Word at all, only to be able to view the pdf file?

    Thanks,

    Darryn

  • I downloaded the office integration pack & was working with the code above, but unfortunately the above code does not seem to work,

    the following line

    Dim doc As Object = OfficeIntegration.Word.GenerateDocument(WordFile,

                                                                       Me.Customer, custFields)

    gives exception as Me.Customer cannot be converted to type IEntityObject.

    am I missing something, please help.

  • I am Iranian and I have access to the program(LightSwitch) is impossible and so I get this program please I want to download!!!

  • Hi Beth,

    Love the article. I am using OfficeIntegration.Word.GenerateDocument to create a Word document fine. The question is I just want it to print rather than display the document but I can't seem to get set the visible property.

    Help appreciated.

  • Hi Beth, great article! I have one question: I am using the OfficeIntegration.Word.Export method to fill my table in my word document with contents of the table in the silverlight/lightswitch application. This is working fine except for the one relation in my table: The table consists of a column "article", where the user can choose an article for each line. However, usint "article" in the list (orderFields called in your example) does not work, also something like "article.name" or "article.id" does not work. In the generated table, these fields are just empty. How can I access the fields of my relation? Thanks in advance!

  • Just found the answer to my problem here: www.codeproject.com/.../Using-LightSwitch-Office-Integration-Pack-with-Com

    Now I have another question: I would like to generate the PDF directly instead of showing the word document and then generating the PDF. Is this possible somehow and if so how?

  • This was a great post. I am playing with this extension and bump into this situation.

    Suppose i want to upload my LS application as a Desktop 3 tier application and I want to include everything inside my project. My problem is how can i find the document template if I put in inside Client Folder in my lightswitch project?

    How can I replace this code to be a reference for the Document Template in side my project?

               'this code works fine if the document template location is inside MyDocuments in my local machine

               Dim MyDocs = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

               Dim WordFile = MyDocs & "\LsReportTemplate\Quotation.docx"

    Please help me how can i find the document template inside my lightswitch application.

    Thank you in advance guys

    :-)

  • hi Beth, and thx for this grat post, can you make a post of printing list detail ??

  • For those using c #, and for me in case the forget:

    var ExcelFile = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\ExpToExcel.xlsx";

               var fields = new List<String>() {"Column1", "Column2"};

               OfficeIntegration.Excel.Export(this.Search, ExcelFile, "Sheet1", "A1", fields);

    Thank You Beththis article, and also other

  • again for the quick location.

    For those using c #, and for me in case the forget:

    var ExcelFile = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"\ExpToExcel.xlsx";

              var fields = new List<String>() {"Column1", "Column2"};

              OfficeIntegration.Excel.Export(this.Search, ExcelFile, "Sheet1", "A1", fields);

    Thank You Beth for this article, and also other

  • Would any one please provide more info about the C# Code?

    Please post here :

    social.msdn.microsoft.com/.../office-intergration-pack-please-help-with-code-officeintegrationexcelexport-

Page 2 of 2 (28 items) 12