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.

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.

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.

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.

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:

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.

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

H Gupta

30 Jul 2012 3:59 AM

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 ?

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.

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

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.

@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.

John V

26 Sep 2013 9:27 AM

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

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