New and Improved Office Integration Pack Extension for LightSwitch

New and Improved Office Integration Pack Extension for LightSwitch

Rate This
  • Comments 14

I’ve been a big fan of Office development for a few years and have been keeping tabs on the free Office Integration Pack extension for LightSwitch from Grid Logic since it released almost a year ago. In fact, I used it in the latest VS 2012 version of my Contoso Construction sample application. Back in May they moved the source code onto CodePlex and a couple weeks ago they released a new version 1.03. I finally had some time to play with it yesterday and WOW there are a lot of cool new features.

Monday I was glued to the computer watching the Office preview announcements and keynote and boy am I impressed where Office and SharePoint development is headed! But realizing that many developers are building business apps for the “here and now” with Office 2010 (and earlier) I decided to take a tour of the new Office Integration Pack which allows you to automate Excel, Word and Outlook in a variety of ways to import and export data, create documents and PDFs, as well as work with email and appointments. The extension works with Office 2010 and LightSwitch desktop applications in both Visual Studio 2010 as well as Visual Studio 2012.

Get it here: http://officeintegration.codeplex.com/

 

First off, since they moved to CodePlex, everything is much more organized including the Documentation and the Sample Application (available in both VB and C#). It’s easy to pinpoint a current release and download the extension VSIX all from here. Running the sample application is quick and easy to learn from and they even improved the sample since prior releases as well. There are now a series of separate screens that demonstrate each of the features, from simple importing and exporting of data, to more complex reporting scenarios.

There are a ton of features so I encourage you to download the sample and play around. Here’s some of my favorite features I’d like to call out:

1. Import data from any range in Excel into LightSwitch screens

Similar to our Excel Importer sample extension, the Office Integration Pack will allow you to import data from Excel directly into LightSwitch screens. It lets the user pick a workbook and looks on Sheet1 for data. If the Excel column names (the first row) are different than the LightSwitch entity property names, a window will pop up that lets the user map the fields.

 image

The Office Integration Pack can do this plus a lot more. You can automate everything. You can map specific fields to import into entity properties and you can specify the specific workbook and range all in code.

Dim map As New List(Of ColumnMapping)
map.Add(New ColumnMapping("Name", "LastName"))
map.Add(New ColumnMapping("Name2", "FirstName"))

Excel.Import(Me.Customers, "C:\Users\Bethma\Documents\Book1.xlsx", "Sheet2", "A1:A5", map)

2. Export any collection of data to any range in an Excel Worksheet

When I say “any collection of data” I mean any IEnumerable collection of any Object. This means you can use data collections from screen queries, modeled server-side queries, or any in memory collection like those produced from LINQ statements. This makes exporting data super flexible and really easy. You also have many options to specify the workbook, sheet, range, and columns you want to export.

To export a screen collection:

Excel.Export(Me.Books)

To export a modeled query (not on the screen):

Excel.Export(Me.DataWorkspace.ApplicationData.LightSwitchBooks)

To export a collection from an in-memory LINQ query:

Dim results = From b In Me.Books
              Where b.Title.ToLower.Contains("lightswitch")
              Select Author = b.Author.DisplayName, b.Title, b.Price
              Order By Author

Excel.Export(results)

This is super slick! Of course you can specify which fields you want to export specifically but if you don’t, it will reflect over the objects in the collection and output all the properties it finds. In the case of the LINQ query above, this results in an output of Excel columns “Author” “Title” and “Price”. Notice how you can traverse up the navigation path to get at the parent properties as well.

 image

3. Format data any way you want on Export

Not only can you export raw data from collections, you can also format it as it’s being exported by specifying a format delegate. For example, to format the title as upper case and the price as money you create a couple lambda expressions (fancy name for a function without a name) and specify that in the ColumnMapping class.

Dim formatPrice = Function(x As Decimal) As String
                      Return Format(x, "c2")
                  End Function

Dim formatTitle = Function(x As String) As String
                      Return x.ToUpper()
                  End Function


Dim map As New List(Of ColumnMapping)
map.Add(New ColumnMapping("Author", "Author"))

map.Add(
New ColumnMapping("Title", "Title", FormatDelegate:=formatTitle)) map.Add(New ColumnMapping("Price", "Price", FormatDelegate:=formatPrice))

Excel.Export(Me.Books, "C:\Users\Bethma\Documents\Book1.xlsx", "Sheet2", "C5", map)

4. Export hierarchical data, including images, to Word to provide template-based reports

With these enhancements we can now navigate the relationship hierarchy much easier through our data collections in order to create complex template-based reports with Word. In addition to adding formatting support, you can also export static values. Data ends up into content controls and bookmarked tables that you define in the document in the specific locations you want it to appear. They also added the ability to export image data into image content controls. This enables you to create a complex reports using a “data merge” directly from LightSwitch. Here’s a snippet from the sample application which demonstrates creating a book report from hierarchical data:

'Book fields = Content Controls (See BookReport.docx)
Dim mapContent As New List(Of ColumnMapping)
mapContent.Add(New ColumnMapping("Author", "Author"))
mapContent.Add(New ColumnMapping("Title", "Title", FormatDelegate:=formatTitle))
mapContent.Add(New ColumnMapping("Description", "Description"))
mapContent.Add(New ColumnMapping("Price", "Price", FormatDelegate:=formatPrice))
mapContent.Add(New ColumnMapping("Category", "Category", FormatDelegate:=formatCategory))
mapContent.Add(New ColumnMapping("PublicationDate", "PublicationDate", FormatDelegate:=formatDate))
mapContent.Add(New ColumnMapping("FrontCoverThumbnail", "FrontCoverThumbnail"))
'Author (parent) fields
mapContent.Add(New ColumnMapping("Email", "Email", StaticValue:=Me.Books.SelectedItem.Author.Email))

Dim goodReviews = From b In Me.Books.SelectedItem.BookReviews
                  Where b.Rating > 3

'Book reviews (child collection) = Bookmarked Tables
Dim mapTable As New List(Of ColumnMapping)
mapTable.Add(New ColumnMapping("Rating", "Rating"))
mapTable.Add(New ColumnMapping("Comment", "Comment", FormatDelegate:=formatTitle))

Dim doc As Object = Word.GenerateDocument("BookReport.docx", Me.Books.SelectedItem, mapContent)
Word.Export(doc, "ReviewTable", 2, False, Me.Books.SelectedItem.BookReviews, mapTable)
Word.Export(doc, "GoodReviewTable", 1, True, goodReviews, mapTable)

'Save as PDF and open it
Word.SaveAsPDF(doc, "BookReport.pdf", True)

And here’s the resulting PDF:

image

As you can see the Office Integration Pack has got a lot of great features in this release. It’s one of the most downloaded extensions on the VS gallery so others definitely agree that it’s a useful extension – and it’s FREE.

THANK YOU Grid Logic for supporting the LightSwitch community! If any of you have questions or feedback, start a discussion on the CodePlex project site. And if you want to help build the next version, join the development team!

Enjoy!

Leave a Comment
  • Please add 8 and 2 and type the answer here:
  • Post
  • Thanks Beth,

    +1 to Grid Logic!

    This is a great extension that offers so many opportunities. I've already implemented it in a number of projects with a great deal of success. I love the Word integration, especially mapping tables via bookmarks.

    Cheers,

    Paul

    www.PaulSPatterson.com

  • hello Beth massi ,

    This is really good extension . i am using this extension . but when i ma unable to save this in pdf format . it generates an error message that provide the correct path as a FullName . why is this happen ?

  • Hi Gupta,

    Sounds like you need to provide the full path to the file you want to generate. Make sure you are saving the PDF to a trusted location. I'd post a message on the CodePlex site with the exact repro steps for them to investigate.

    Cheers,

    -Beth

  • Hi Beth,

    i treid this Extension and it works fine with Excel, Outlook and smtp. But when i Export a simple word-doc, i get an ComException Error.

    Regards

    Thomas

  • Thanks Beth for the article its really easy to understand and you're just Awesome!!!!!

    I need your help....

    Using the above method I'm trying to build a report which shows the customer details (First Name, Last Name, Address, etc), the order headers (only the selected item) and order details for the selected order headers.

    I have designed a screen to show customer details, order headers & order details. But when i run the report i get all the ordeheaders corresponding to the customer (as we show all in datagrid) but only the order details for selected orderheader.

    This is the code I'm using..maybe you have a better way to handle this situation :D

     Private Sub PDFReport_Execute()

               Dim custFields As New List(Of ColumnMapping)

               custFields.Add(New ColumnMapping("CustomerName", "CustomerName"))

               custFields.Add(New ColumnMapping("Address1", "Address1"))

               custFields.Add(New ColumnMapping("Address2", "Address2"))

               custFields.Add(New ColumnMapping("City", "City"))

               custFields.Add(New ColumnMapping("PinCode", "PinCode"))

               custFields.Add(New ColumnMapping("Phone", "Phone"))

               custFields.Add(New ColumnMapping("EmailAddress", "EmailAddress"))

               Dim doc As Object = Word.GenerateDocument(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\Customer.docx",

                                                                               Me.Customer, custFields)

               Dim orderFields As New List(Of String) From

                {"Product", "Quantity", "Price", "Amount"}

               OfficeIntegration.Word.Export(doc, "OrderDetails", 2, False,

                                                         Me.OrderDetails, orderFields)

               Dim orderFields2 As New List(Of String) From

               {"OrderDate", "InvoiceNo", "OrderTotal"}

               OfficeIntegration.Word.Export(doc, "OrderHeader", 2, False,

                                                         Me.OrderHeaders, orderFields2)

               OfficeIntegration.Word.SaveAsPDF(doc, Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\Customer.pdf", True)

           End Sub

  • Okay, I'm crazy late to this party, but I'm desperate for some help with the OIP.

    Is it possible to map boolean data to Word checkbox content controls? I can't get it to work. My controls and Word docs are unprotected, and I've tried nullable boolean, 3-state, yes/no, True/False, 0/1 and nothing seems to work. All raise a COM error, that warns  "selection can't be edited because it's protected". Again, though, the controls and the docs are definitely not protected.

  • @JBeit - I'm not sure if the OIP supports Booleans. There source code is provided, however.

  • @Beth Thanks for the response. The boolean maps to text controls, showing as a "true/false" string. But when mapped to a Word checkbox control nothing happens. Thanks for the tip on the source code. Probably over my head but I'll weed through it. Probably have to go back to some tedious wall of com automation code to deal with the checkboxes in the Word doc.

  • Hi Beth, do we have any new office extension supported for Visual Studio Lightswitch 2013, I tried to download and install the latest office extension but it says it's not supported. Please advise

    Many thanks

  • @John V - not sure what the status is of their extension but you can use these instructions to get it to load into VS2013.

    blogs.msdn.com/.../quick-tip-getting-lightswitch-extensions-to-install-into-visual-studio-2013-preview.aspx

    I played with it a little and it still seems to work. I'll contact Grid Logic and see what their plans are.

  • Hi, is there a way to support this in lightswitch web app?  Last time I looked into it, there was only support for  the desktop app.

    thanks!

  • Can this works on the application hosted on IIS and running from web browser?

  • @Running on web browser lightswitch -

    Yes you can, however the Silverlight application will need to run as full trust because the Office Integration Pack uses COM automation to Office. This is supported already with out-of-browser, but you can enable it for in-browser on PCs. See: msdn.microsoft.com/.../ee721083(v=vs.95).aspx

    HTH,

    -Beth

  • Just a (silly) question. This does not require Office to be installed on the server just like Interop?

Page 1 of 1 (14 items)