Microsoft DevDays 2010 Day #3 with Open XML

Article
04/03/2010

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: https://sqlserversamples.codeplex.com/wikipage?title=SQLServerDatabasesandSamplesOverview.

In Visual Studio, create a new C# console application project called WordDBMerge
Add references to: · DocumentFormat.OpenXML · WindowsBase Right click References in Solution Explorer and select Add Reference.
In the Add Reference dialog box choose the two components for DocumentFormat.OpenXML and WindowsBase. Then, press
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.
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
The designer will display your tables. You can close the designer after adding the tables.
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.
Make sure your using statements include a reference to Open XML.
In your custom class add a procedure that kicks off the doc gen process by creating a new Open XML package.
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.
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.
Press F5 to debug the project.
Look in the Debug folder for your project and open the output.docx file.