Fun with the Office Integration Pack Extension for LightSwitch
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.
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.
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:
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.
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”
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:
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.
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.
Next I’ll add a button to the screen, this time in the screen command bar at the top, called “Generate Document”.
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.
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”.
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!
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!
Comments
Anonymous
September 22, 2011
That's quite amazing! And for free!Do you know if this will work with earlier versions of Office? Your screen shots show Office 2010.Anonymous
September 22, 2011
Hi Mr Yossu,I haven't tried any other version. It would probably work with Office 2007 but not 2003 since that version of Word didn't have content controls.You should check it out. :-)Cheers,-BethAnonymous
September 22, 2011
Hi,Any Ideas on howto format the text e.g. date-times and currency? Or is it best to use the XML method as per the contoso demo app?Anonymous
September 23, 2011
Hi P Stinson,I guess one way to do it would be to create computed string properties on the entity that formatted the data exactly how you wanted.Have fun,-BAnonymous
September 27, 2011
Good of Grid Logic (Steve H) to provide the source of the vsix. It took me weeks to develop a similar Office extension, alas in C#, Beth! Perhaps you could include a reference to the Outlook COM reference if you wanted to programmatically send/create an email with these documents as an attachment. Even better how about creating these documents on the LS hosting server and presenting the client(s) with a link to them as well possibly using the server email them. Like the "Bookmark" trick, a bit better than the multiline custom control in the Contoso example, you learn something every day.I am having fun,-KAnonymous
September 28, 2011
Beth - is there anything you need to install/enable to get the basic export to Excel functionality per your comment of "LightSwitch has a really nice feature on data grids that allow you to export them to Excel"? My app by default doesn't have this functionality (a C# app)...Anonymous
September 29, 2011
Hi Beth,Is there any plans for this extension to work for in-browser apps? Even if it only has a subset of the features.Thanks,DaveAnonymous
September 30, 2011
Hi Beth,How would you compare using Office Integration Pack Extension in LS with automating Excel/Word with VBA from LS? Which one is better for producing Excel/Word reports?Thanks,GamonAnonymous
October 06, 2011
Hi BethIt's possible to insert Image in Word Document using the Office Integration Pack Extension?Thank you in AdviceMatteoAnonymous
December 14, 2011
Hi Beth am trying to do Office Integration in my app. but am getting errors likeSystem.security.security Exception. file open is not permittedAnonymous
December 16, 2011
@Matteo, I'd take a look at the Document Toolkit extension to do that:www.microsoft.com/.../firstfloor@Sri, Sounds like you are hitting the Silverlight security issue. You need to open files from trusted locations only - i.e. "My Documents"Anonymous
December 29, 2011
Hi Beth,Is it possible to export not only columns out of the tables but as well out of a screen.For example I have a order screen with a calculate data item showing the total amount of a order. Can I export this total to the same word report.Thank you very much for your helpAnonymous
January 13, 2012
Heey Beth,Very Nice Post you got there ..but I have a question ..How can i put multiple entities in one word document ?I can show order and order details but what if i want to add additional info about the organization, in the same document ?? How can i do something like that ?Can you please help mee ?? : )Thanks a lot in advanceKind Regards,3oonAnonymous
January 25, 2012
What if I have more than one worksheet, how to export all worksheets together in excel?Anonymous
April 11, 2012
Can you give me the usage detail of the Office Integration PackAnonymous
April 11, 2012
My EMAIL is 649981528@qq.comAnonymous
May 03, 2012
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.Anonymous
May 06, 2012
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,DarrynAnonymous
May 16, 2012
The comment has been removedAnonymous
May 21, 2012
I am Iranian and I have access to the program(LightSwitch) is impossible and so I get this program please I want to download!!!Anonymous
August 22, 2012
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.Anonymous
January 28, 2013
The comment has been removedAnonymous
January 29, 2013
Just found the answer to my problem here: www.codeproject.com/.../Using-LightSwitch-Office-Integration-Pack-with-ComNow 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?Anonymous
May 30, 2013
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 & "LsReportTemplateQuotation.docx"Please help me how can i find the document template inside my lightswitch application.Thank you in advance guys:-)Anonymous
September 25, 2013
hi Beth, and thx for this grat post, can you make a post of printing list detail ??Anonymous
April 25, 2014
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 otherAnonymous
April 25, 2014
(The content was deleted per user request)Anonymous
August 18, 2014
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-Anonymous
May 18, 2015
Beth In Decembrer 2011 you answered: "@Sri, Sounds like you are hitting the Silverlight security issue. You need to open files from trusted locations only - i.e. "My Documents" I have the same problem as Sri, and I couldn't fixed. ¿Can you give me more detailed instructions?Anonymous
June 18, 2015
Hello Everybody, But can any tell me how can i export a master details screen , to excel , i will be happy to learn that. The page header and the grid , exact like Beth shows us with word. ThanksAnonymous
October 19, 2016
Hi Beth,I'm doing some work with LIGHTSWITCH, and I’ve been facing some obstacles.When using this code you show here in the visual studio 2015, it appears like an error:Private Sub Export_Execute() ' Write your code here. Dim ExcelFile = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\IPSNE Control Worksite Information Ver1.xlsx" If File.Exists(ExcelFile) Then Dim fields As New List(Of String) From {"CompanyName", "ContactName", "ContactTitle", "Phone"} OfficeIntegration.Excel.Export(Me.ShiftsSet, ExcelFile, "Midweek", "A11", fields) End If End Sub End ClassIn "OfficeIntegration" is the place where the VS 2015 marks with red underline.Do I need to use another type of code?Cheers