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.
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:
To export a modeled query (not on the screen):
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.
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:
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!