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=""http://schemas.openxmlformats.org/wordprocessingml/2006/main"">
<wp:inline xmlns:wp=""http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing"">
<wp:extent cx=""" + width + @""" cy=""" + height + @""" />
<wp:docPr name=""" + imageName + @""" id=""1"" />
<a:graphic xmlns:a=""http://schemas.openxmlformats.org/drawingml/2006/main"">
<a:graphicData uri=""http://schemas.openxmlformats.org/drawingml/2006/picture"">
<pic:pic xmlns:pic=""http://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=""http://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