Pushing Data from a Database into a Word Document

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.

Scenario – Generating a Product Catalog as a Word Document

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.

Solution

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.

Step 1 – Create a Template

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.

Step 2 – Create a Table with Style

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:

using (WordprocessingDocument myDoc = WordprocessingDocument.Open(docName, true)) { MainDocumentPart mainPart = myDoc.MainDocumentPart; Document doc = mainPart.Document; //Create new table with predefined table style Table table = new Table(); TableProperties tblPr = new TableProperties(); TableStyleId tblStyle = new TableStyleId(); tblStyle.Val = "PredefinedTableStyle"; tblPr.AppendChild(tblStyle); table.AppendChild(tblPr); string[] headerContent = new string[] { "Name", "Subcategory", "Price", "Image" }; //Create header row TableRow header = CreateRow(headerContent, null); table.AppendChild(header); ... }

Step 3 – Create a Table Row that Contains Text and an Image

My table will contain two types of rows:

1. My header row, which is four cells with just text
2. My content row, which is three cells of text and one cell with an image

I wrote a method called CreateRow, which allows me to create either of these rows types:

TableRow CreateRow(string[] cellText, Drawing d) { TableRow tr = new TableRow(); //create cells with simple text foreach (string s in cellText) { TableCell tc = new TableCell(); Paragraph div = new Paragraph(); Run r = new Run(); Text t = new Text(s); r.AppendChild(t); div.AppendChild(r); tc.AppendChild(div); tr.AppendChild(tc); } //if a drawing is specified, add it to the last cell if (d != null) { TableCell tcDrawing = new TableCell(); Paragraph pDrawing = new Paragraph(); Run rDrawing = new Run(); rDrawing.AppendChild(d); pDrawing.AppendChild(rDrawing); tcDrawing.AppendChild(pDrawing); tr.AppendChild(tcDrawing); } return tr; }

Step 3 – Create a Drawing Object that Represents an Image

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:

• Relationship id – points to the image part that represents the image
• Image name – specifies the name of the image
• Width – width of the image in units of Emu
• Height – height of the image in units of Emu

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:

string CreateImageXml(string relId, string imageName, int width, int height) { //Looking to add an image to WordprocessingML? Creating the xml is the easiest way. string img = @" <w:drawing xmlns:w=""https://schemas.openxmlformats.org/wordprocessingml/2006/main""> <wp:inline xmlns:wp=""https://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing""> <wp:extent cx=""" + width + @""" cy=""" + height + @""" /> <wp:docPr name=""" + imageName + @""" id=""1"" /> <a:graphic xmlns:a=""https://schemas.openxmlformats.org/drawingml/2006/main""> <a:graphicData uri=""https://schemas.openxmlformats.org/drawingml/2006/picture""> <pic:pic xmlns:pic=""https://schemas.openxmlformats.org/drawingml/2006/picture""> <pic:nvPicPr> <pic:cNvPr id=""0"" name=""" + imageName + @""" /> <pic:cNvPicPr /> </pic:nvPicPr> <pic:blipFill> <a:blip r:embed=""" + relId + @""" xmlns:r=""https://schemas.openxmlformats.org/officeDocument/2006/relationships"" /> <a:stretch> <a:fillRect /> </a:stretch> </pic:blipFill> <pic:spPr> <a:xfrm> <a:off x=""0"" y=""0"" /> <a:ext cx=""" + width + @""" cy=""" + height + @""" /> </a:xfrm> <a:prstGeom prst=""rect"" /> </pic:spPr> </pic:pic> </a:graphicData> </a:graphic> </wp:inline> </w:drawing>"; return img; }

Step 4 – Putting it all Together

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:

//Connect to database Adventure_WorksDataContext db = new Adventure_WorksDataContext(); var productQuery = from div in db.Products group div by div.ProductID; int i = 1; //Will use this to create unique relationships //For every product in my database create a new row in my table foreach (var product in productQuery) { foreach (var item in product) { string price = "$"; int widthInEmu; int heightInEmu; if (item.ListPrice != 0) { price += Math.Round(item.ListPrice, 2); string imgId = "rIdImg" + i; //Create unique relationship id i++; string[] content = new[] { item.Name, item.ProductSubcategory == null ? null : item.ProductSubcategory.Name, price }; //Add an imagepart with data to the package for every image found ImagePart imagePart = mainPart.AddImagePart(ImagePartType.Gif, imgId); imagePart.FeedData(new MemoryStream(item.ProductProductPhotos.First().ProductPhoto.LargePhoto.ToArray())); //Before we can add the xml for the image we need to know the proper dimensions in Emus Bitmap bitmap = new Bitmap(new MemoryStream(item.ProductProductPhotos.First().ProductPhoto.LargePhoto.ToArray())); CalculateImageEmus(bitmap, out widthInEmu, out heightInEmu); //Build up xml that represents image string img = CreateImageXml(imgId, item.ProductProductPhotos.First().ProductPhoto.LargePhotoFileName, widthInEmu, heightInEmu); //Create drawing object based on image xml Drawing d = new Drawing(img); TableRow tr = CreateRow(content, d); table.AppendChild(tr); } } }

End Result

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:

Zeyad Rajabi

Comments

• Anonymous
  January 19, 2009
  PingBack from http://blog.a-foton.ru/index.php/2009/01/20/pushing-data-from-a-database-into-a-word-document/

• Anonymous
  January 19, 2009
  No disrespect intended, but the major posts in the blog had not been written by him for the past three months at least. His contribution seems to be ISO process update.

• Anonymous
  January 20, 2009
  :-) none taken... Actually, my long term goal is to start adjusting the content of the blog a bit as I've shifted my ownership from file formats specifically over to the more general area of Office extensibility. Zeyad is on my team and has been focused on the Open XML SDK for a couple years now. Since we're not yet ready to fully discuss Office 14, I thought it would be most useful to have Zeyad discuss the various solutions he's built using the SDK. We've had him present a couple times now on the SDK and he's built a number of compelling demos (as you may have noticed). We're also going to pull together a set of videos over the next month or so that help describe in more detail the goals we have around the SDK, and how we envision folks building Office solutions leveraging it. I'll take your comments as a good kick in the pants though, and aim to spend more time writing up my own posts as well. -Brian

• Anonymous
  January 25, 2009
  I'm at Sea-Tac waiting to board my flight to the WG4/WG5 meetings in Okinawa this week, and decided to

• Anonymous
  January 25, 2009
  I was wondering when the SDK will move from CTP status?  I am wrestling with the best way to generate excel spreadsheets serverside with charts and I have looked at the openxml sdk 2.0.  It seems great but since it hasn't been fully released I am hesitant to do anything with it.  Do you have any examples with SDK 1.0 to do this?  Or any recommendations? Thanks

• Anonymous
  January 26, 2009
  Les derniers liens ce cette semaine sur Open XML : Technique : Zeyad Rajabi sur comment créer un catalogue

• Anonymous
  January 26, 2009
  Don, Great question. We are aiming to release version 2 of the SDK around the same time as O14 ships. Considering we still have some time before that happens I would recommend building your solution with version 1 of the SDK. Version 1 of the SDK can do everything version 2 can, but requires some more work. I would recommend looking at Eric White’s blog for some more examples on how to build solutions using version 1 of the SDK: http://blogs.msdn.com/ericwhite/default.aspx Thanks, Zeyad Rajabi

• Anonymous
  February 24, 2009
  In addition to posting my own content, I will from time to time post links to the great new Open XML