New and Improved Office Integration Pack Extension for LightSwitch
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: https://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:
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.
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!
Enjoy!
Comments
Anonymous
July 18, 2012
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,Paulwww.PaulSPatterson.comAnonymous
July 29, 2012
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 ?Anonymous
August 01, 2012
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,-BethAnonymous
October 15, 2012
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.RegardsThomasAnonymous
October 26, 2012
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 SubAnonymous
May 20, 2013
The comment has been removedAnonymous
May 20, 2013
@JBeit - I'm not sure if the OIP supports Booleans. There source code is provided, however.Anonymous
May 23, 2013
The comment has been removedAnonymous
September 26, 2013
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 adviseMany thanksAnonymous
September 27, 2013
@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.aspxI played with it a little and it still seems to work. I'll contact Grid Logic and see what their plans are.Anonymous
December 05, 2013
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!Anonymous
February 07, 2014
Can this works on the application hosted on IIS and running from web browser?Anonymous
February 10, 2014
@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).aspxHTH,-BethAnonymous
February 20, 2014
Just a (silly) question. This does not require Office to be installed on the server just like Interop?Anonymous
May 08, 2015
Hello Beth.I'm using Microsoft LightSwitch and azure cloud, I'm facing with the problem that office integration pack doesn't work on web client (not HTML client) just in the desktop client, is there any way to make this extension works on web client? could help using the integration pack for windows azure to make office integration pack works on the client app?Thanks in advancedAnonymous
May 08, 2015
Hello Beth.I'm using Microsoft LightSwitch and azure cloud, I'm facing with the problem that office integration pack doesn't work on web client (not HTML client) just in the desktop client, is there any way to make this extension works on web client? could help using the integration pack for windows azure to make office integration pack works on the client app?Thanks in advancedAnonymous
May 18, 2015
I have the same problem that Anna. I get error: System.security.security Exception. file open is not permittedAnonymous
May 18, 2015
@Anna, @Arturo -- you need to select the desktop client to use this extension as it uses COM interop to automate Office and it needs full trust to do so. You can still host a 3-tier desktop client app in Azure, that's not the restriction. The restriction is that the client app needs to be an out of browser app. You should try and contact Grid Logic to see if they have plans to update the extension.Anonymous
June 16, 2015
Hi Beth, Thanks for the wonderful article . I'm trying to export data to Word(Content Controls). Except Image field , rest are fine. But exporting Image field gives an error "You are not allowed to edit this selection because it is protected". I 've googled and done all the possible solution given. But Still I get this error. Could you please kindly help. Thank you. Here's my code. Dim CallReportsFields As New List(Of OfficeIntegration.ColumnMapping) CallReportsFields.Add(New OfficeIntegration.ColumnMapping("Bodyshop_Name", "Bodyshop_Name")) CallReportsFields.Add(New OfficeIntegration.ColumnMapping("Date", "Date_Of_Call")) CallReportsFields.Add(New OfficeIntegration.ColumnMapping("Technician", "UserFullName")) CallReportsFields.Add(New OfficeIntegration.ColumnMapping("Reason for visit", "Call_Reason")) CallReportsFields.Add(New OfficeIntegration.ColumnMapping("Paint", "Paint_Product")) CallReportsFields.Add(New OfficeIntegration.ColumnMapping("Comments", "Call_General_Notes")) CallReportsFields.Add(New OfficeIntegration.ColumnMapping("Paint_Logo", "Paint_Logo", StaticValue:=Convert.ToBase64String(Me.vw_Call_Reports1.SelectedItem.Paint_Logo))) Dim doc As Object = OfficeIntegration.Word.GenerateDocument(WordFile, Me.vw_Call_Reports1.SelectedItem, CallReportsFields) Best Regards, Dana