SoftArtisans OfficeWriter - Sharepoint 2010 - Word 2010

I recently had the opportunity to check out SoftArtisans OfficeWriter product. The OfficeWriter product exposes an API that allows information from custom ASP.NET applications to be consumed and used to dynamically and programmatically build Microsoft Word documents and Microsoft Excel spreadsheets.

The OfficeWriter API is a .NET library that allows you to read, manipulate and generate Microsoft Word and Microsoft Excel documents from your own applications. The OfficeWriter product can integrate with Sharepoint 2010 allowing you to export Sharepoint list data into Microsoft Word and Excel documents.

SoftArtisans provides easy to understand sample code, videos and pre-built Sharepoint solutions that make getting started with the product very trivial.

For this tutorial I'll demonstrate deploying, configuring and testing Word Export Plus in a Sharepoint 2010 environment. Word Export Plus is a SharePoint solution that demonstrates the usage of the OfficeWriter API in SharePoint 2010. This solution adds a new context menu (custom action) button to list items, allowing you to export the list data to a pre-formatted Word template that can be designed yourself in Word, or automatically generated by Word Export Plus.

Requirements for this tutorial

1. SoftArtisans OfficeWriter -  Installed and properly licensed, download the trial from Here
2. SoftArtisans Word Export Plus - Download the Sharepoint solution installer from Here
3. Sharepoint 2010  - A site in place that will be used to test the Sharepoint integration features
4. Microsoft Word 2010 - Installed on the Sharepoint 2010 server

Installation


Launch the WordExportPlus.msi executable, this will install the Sharepoint solution into the Sharepoint Farm.

After the Sharepoint solution has been installed it can be deployed

Open Sharepoint Central Administration
Navigate to System Settings |  Farm Management  | Manage Farm Solutions
Select Word Export Plus.wsp
Select Deploy Solution
Specify a deployment time
Select OK

After the solution has been deployed it can be enabled through the site features on the site where the tool is going to be used.

After Word Export Plus has been activated I'll configure it for use.

First I'll create a contacts list that will be the data source for my exports, select Site Actions | View All Site Content | Create and select Contacts

I'll name the list People and select OK.

After the list is created I'll create a couple of test contacts in the list.

Next I'll configure Word Export Plus by selecting Site Actions | Site Settings | OfficeWriter Solution: Word Export Plus

First I'll choose the list that I just created (People) in the Sharepoint List dialog, in the Word Template dialog select Create a new template file. In the Template File Location select the Shared Documents library of the current site. Enter a name for the Template File (Word Template). Enter a name for the Action (Word)

This completes the configuration, 

select Create New Action

To test the new custom action browse to the People list created previously, from one of the contacts select the dropdown next to the last name column and select the action name created previously (Word)

A dialog will open asking if I would like to open or save the WordWriter.Doc file, select Save and after the file is downloaded select Open

This is how the auto-generated template will render the Sharepoint list data, I could have created my own Word template and selected that during the configuration steps. This provides a flexible solution to allow users to get the list data out quickly and formatted nicely into Microsoft Word.

C# - Microsoft Word 2010 Automated Mail Merge Part 2 of 2

Back to Part 1

In this example I'll use a more dynamic approach that reads customer data from a SQL database and customizes the query before generating the mail merge.

The ASP.NET application pulls in all of the customer data from from the Northwind database and allows paging and sorting of the data via a Grid View Control. Checkbox controls allow selection of records that will be included in the mail merge. Clicking the Mail Merge button selects all of the checked customers and launches Microsoft Word with all of the customer data pre-populated into the mail merge template (shown below).

Download the Northwind Sample Database - Here
Download the Microsoft Mail Merge Sample Code - Here

First thing I noticed was that Microsoft no longer includes the sample databases with SQL 2008R2, after a couple of searches I found that you can still download them and run scripts or restore the database files.

After I downloaded the sql2000sampleDb.msi I ran the install. Once the install was complete I opened a command prompt and navigated to C:\SQL Server 2000 Sample Databases. Because my SQL server is installed as the default instance I typed sqlcmd -i instpubs.sql to perform the install, I verified that the Northwind database was now installed on my default SQL instance. Note: if you have a non-default SQL instance you can use the following syntax - sqlcmd -S .\InstanceName -i instpubs.sql

I created a new Visual Studio 2010 ASP.NET project, added a new reference to the project by expanding references from the solution explorer and selecting new reference. I selected the COM tab and chose the Microsoft Word 14 reference. This will add 2 new references to the project - Microsoft.Office.Core and Microsoft.Office.Interop.Word. 



This example uses the Microsoft MSDN sample code for the basic mail merge functions.

Add a using statement for the newly added reference as follows:
using Word = Microsoft.Office.Interop.Word;





I cut the first four lines of the Microsoft code and pasted it at the beginning of the class declaration to ensure proper scope for these variables.

Next I Selected the Server Explorer tab and created a connection to my local SQL Server and Northwind database.

I added GridView and button controls to the page

Next I configured the datasource for the Gridview control and selected the Northwind connection string from the dropdown

Next A select * statement is chosen from the Northwind Customers table on the configure the Select Statement screen

This completes the Gridview datasource

Next I added a checkbox control to the Gridview to allow selecting one or more customers. Select the source view of the aspx page and add the following code to the gridview under the columns node.

Back to the design view - double click on the button on the form and add the following code to the  button click event.

The code will iterate through the gridview control and select only the rows that were checked. I built a very simple customer class to hold the data from the selected rows. This can be customized to include any of the fields from the SQL database but for now I'm only including the ID, ContactName and ContactTitle fields. A Generic Dictionary <T> Collection is used to hold the customer objects and this will be passed to  the CreateMailMergeDataFile method.

In the same button click event that you added the snippet above add all of the code in the button1_click event from the MSDN sample code (not all code shown highlighted below)

Next add the remainder of the MSDN sample code into the aspx page just under the public partial class declaration.

Replace the highlighted code in the CreateMailMergeDataFile with the code shown below

Replace highlighted code with this code

I included my project files but you will have to modify the connection string information to your SQL database if it's not on the local machine in the default SQL instance.

Download Visual Studio Solution

Back to Part 1

C# - Microsoft Word 2010 Automated Mail Merge Part 1 of 2

Continue to Part 2

I was recently tasked with building an add-on for a CRM application to allow the application to integrate with Microsoft Word and generate a mail merge.

I decided to use two examples for demonstrating how to generate the mail merge, the first will be from a Microsoft MSDN code sample and demonstrate a hard coded method of outputting data from my C# application to Microsoft Word.

For the second example, I'll demonstrate a more dynamic approach by connecting to a SQL sample database (Northwind). I'll build an ASP.NET user interface to allow selection and filtering of customers prior to sending the data to Microsoft Word for the mail merge.

Tools
Visual Studio 2010
Microsoft Word 2010
Microsoft SQL Server 2008R2

Part 1
Microsoft Mail Merge Code - Here

Microsoft created some sample code for integrating custom c# applications with Microsoft Word and automating the mail merge, the code provides the mechanism for programmatically generating the mail merge with some inline customer data.

From Visual Studio 2010 I created a new ASP.NET project, the Microsoft code was developed with a Windows Forms application in mind but I ported it into an ASP.NET application. First I added a new reference to the project by expanding references from the solution explorer and selecting new reference. I selected the COM tab and chose the Microsoft Word 14 reference. This will add 2 new references to the project Microsoft.Office.Core and Microsoft.Office.Interop.Word.

Add a using statement for the newly added reference as follows:
using Word = Microsoft.Office.Interop.Word;

I cut the first four lines of the Microsoft code and pasted it at the beginning of the class declaration to ensure proper scope for these variables.


Next I switched to the design view of the default.aspx page in my project and clicked on the toolbox and added a new button to the current page.

Next I double clicked the new button and pasted the remainder of the code from the Microsoft article into the click event handler of the new button.

I built the application with no errors and launched it for a test.

I clicked the button and it looks like everything is working as expected so far, Microsoft Word launches and displays three customer forms that are ready to be printed/mailed.

The standard form data is consistent on all of the user forms and the inline unique customer information is populated into the variable field locations.

This example can be modified to read from a text or csv file or any number of methods that would allow an automated extraction of customer data.

The data can also be kept inline in the code behind as is.

This completes the first example, this example provides a good look into the Word object model and what it takes to programmatically create and manipulate Word objects. Again this is a pretty static and simple method for demonstrating the mail merge. A more advanced approach will be described in Part 2.

Continue to Part 2