I've been running at a fast pace for the past few days, so I'm going to add a few posts here to catch up.

Day 3 of the DevDays event in the Netherlands was a success. I delivered a session on "Open XML Solution Building With Word and Excel Services". Huge thanks to Zeyad Rajabi for pulling together some of the server-side demos I showed.

Among the 10 or so demos I showed I showed people how to connect to a datasource and start pumping data into a document. For example, what about connecting to a database and producing a Word document through Open XML? It's a common scenario, and there are lots of blog entries out there that show how to do this. Well, here's another one!

A common developer task to produce Word documents based on data in a database. I'm going to show you how to pump data into a table in Word with some formatting.

By following these steps you will be able to:

1. Create a new project for your Open XML Solution

2. Add the proper references

3. Connect to a database (in this case SQL Azure!)

4. Loop through LINQ query results

5. Add a new table to the document with columns and rows

6. Test the application

All mistakes herein, if any, are my own. Please alert me to potential errors.

Set Up The VS Project & Debug

Before creating this solution, make sure you have installed the Open XML SDK 2.0. Also, make sure you have access to the AdventureWorks database. You can still install this database from here: http://sqlserversamples.codeplex.com/wikipage?title=SQLServerDatabasesandSamplesOverview.

In Visual Studio, create a new C# console application project called WordDBMerge

clip_image002

Add references to:

· DocumentFormat.OpenXML

· WindowsBase

Right click References in Solution Explorer and select Add Reference.

clip_image003

In the Add Reference dialog box choose the two components for DocumentFormat.OpenXML and WindowsBase. Then, press

clip_image005

Add a LINQ To SQL class by right-clicking the project in Solution Explorer. Choose Add New Item from the context menu. In the resulting dialog box, add a LINQ to SQL class named AdventureWorks.dbml.

clip_image007

Visual Studio will display the designer for your LINQ to SQL class. From the Server Explorer drag the following tables to the designer surface:

· Contact

· Employee

· Sales Territory

· Sales Person

clip_image009

The designer will display your tables. You can close the designer after adding the tables.

clip_image011

Add a class by right-clicking the project in Solution Explorer. Choose Add New Item from the context menu. In the resulting dialog box, add a class named SimpleWord.cs.

clip_image013

Make sure your using statements include a reference to Open XML.

clip_image015

In your custom class add a procedure that kicks off the doc gen process by creating a new Open XML package.

clip_image017

Every Open XML file has a main document part. You'll add a new one of these to the newly created Open XML document. The code also calls another method to the main work of adding content from the database to a table. This procedure is GenerateMainDocumentPart2Content.

clip_image019

Add the code from this file to your class to fill out the GenerateMainDocumentPart2Content method.

File

Add the code from this file to your class to fill out the GenerateContent method.

File

Add the code from this file to your class to fill out the AddTable method.

File

Now, open the Program.cs file and add the following two lines of code to the class constructor.

clip_image021

Press F5 to debug the project.

clip_image022

Look in the Debug folder for your project and open the output.docx file.

clip_image024

Now, you can alter the code in the GenerateContent procedure to alter the formatting, add new columns etc.

Resources

· Brian Jones & Zeyad Rajabi's blog: http://blogs.msdn.com/brian_jones/

· Eric White's blog: http://blogs.msdn.com/ericwhite/

· Open XML Web Site: www.openxmldeveloper.org

· Open XML on MSDN: http://msdn.microsoft.com/en-us/office/bb265236.aspx

Here's a vide of two people I meet at the show in the Netherlands. They are both very smart and very nice!

Mirjam and Marianne at the Netherlands DevDays 2010


Rock On