First off I want to thank everyone for leaving comments suggesting future posts about the Open XML SDK. Looking at some of the comments from previous posts, I noticed there were several requests around generating a Wordprocessing document from external data. I have showed you how to accomplish this type of scenario within SpreadsheetML and within PresentationML. Today, I am going to show you how to build a repeating table within WordprocessingML based on data within a database.
Imagine a scenario where I'm a developer for a fictional company called Adventure Word (based on the sample company on MSDN, which can be found here). My company stores all their products within a database. The Marketing team would like to create a product catalog based on all our products. They have asked me to build a solution that is able to easily generate this product catalog as a Word document. An additional request is they would like this solution to work in both client and server environments.
Before I get into the details of my solution I want to call out that I am using the freely available Adventure Works database built for SQL Server 2005. Okay, we're ready to get started. If you just want to jump straight into the code, feel free to download this solution here.
For the sake of simplicity, the template I created is an empty document that contains a predefined table style. From this template, I will create a table, apply my predefined table style, and then add the appropriate data from the database. I should note that there are other approaches to solving this scenario. For example, I could have had an empty table surrounded by a content control, where the content control would be used for easy locating, much in the same way content controls were used in my document assembly post.
Now that I have setup the template, I need to programmatically add a table to my document and apply my predefined table style, which in this case is called "PredefinedTableStyle." Once I have applied the style to the table, I can then add my header row, which simply contains the column headers of my table. In this simplified scenario, my table will contain a row for each product in my database, where each row will have details on the name, subcategory, price, and image of a specific product. The following code accomplishes these set of tasks:
My table will contain two types of rows:
I wrote a method called CreateRow, which allows me to create either of these rows types:
Up to this point, all my posts have been around creating or manipulating Open XML via objects within the SDK. Well, there is another way you can create nodes within the Open XML format...through XML. That's right; you can instantiate SDK objects directly from XML. In this solution, I use the XML that represents an image to instantiate a Drawing object by calling the following code: Drawing d = new Drawing(img), where img is just a string that represents the XML for a Drawing node.
There are a few pieces of information you need to create a Drawing object that represents an inline image:
Take a look at my sample project for details on how to calculate the width and height of an image in units of Emu.
The following method creates a string of XML that represents a given image:
Now that I have all the basics in place I can put everything together. I need to go through every product in my database and create a row in my product catalog table. Each row will contain specific product information such as price and an image representing the product. The following code accomplishes these set of tasks:
Running my code, I will end up with a sixty one page document that has a table, which contains my product catalog. Here is a screenshot of the generated document: