Last week I had a chance to write some interesting code samples. I missed coding a lot, and I felt a little rusty after working in different projects. But I had a lot of fun and I want to share with you one of the samples I wrote: A simple ASP.NET 2.0 solution that retrieves data from SQL Server and generates a Word 2007 document using the Office XML File formats, WinFX, and XML Mappings in Word 2007.
One of the most common requirements for applications that work with data is "data-driven document generation." No matter what the data source is -could be an Access database, SQL database, Web service, SharePoint list, Excel spreadsheet, XML file, Word document, or multiple sources. The typical "export data and create Word documents" feature is a common need.
How to do it? Two simple steps:
- The Word part: Create a Word 2007 document template.
- The Cool app part: Create an application that pulls data from a datasource and generates new documents based on the Word 2007 document template.
Word 2007 allows you to build data-driven document generation solutions. You can create a document template with a custom XML part and use Content Controls to bind to custom XML data using XML Mapping. Next, you can create a managed application to build a new document based on the template. The managed application will open the document template, retrieve data from a SQL Server database to build a new custom XML part, replace the template’s custom XML part , and save it as a new document.
This little sample will show you how to build a document template from scratch and create a server-side application that generates documents that display data stored in a SQL Server database. How did I learn to do this, well, believe it or not, I watched the following Office Dev Con session:
Presenter: Tristan Davis; 59 minutes (180 MB)
In this session, we introduce new XML capabilities, then dive into the functionality of the Office XML data store and how it can be leveraged to build solutions that will strongly tie Word documents to your business processes.
..and then asked Tristan Davis some questions related with XML Mapping.
Business Scenario: Custom Customer Letter
To demonstrate how to build and Word 2007 template and connect an item in the data store to a SQL Server database, you will first build a customer letter document template with content controls that map to an XML file. Next, you will create a server-side data-driven document generation application that will allow you to select a company name to generate a custom letter. The application will retrieve customer data from a SQL Server database and use the customer letter document template to build a new document that displays customer data based on a user selection. The document will display the following information:
- Company Name
- Contact Name
- Contact Title
To create a Word template and define the XML Mappings for each content control.
Use the following steps to create a Microsoft Office Word 2007 template.
1. Open Word 2007.
2. Create a document.
3. Create content controls to bind to a node in the data store.
What are content controls? Content controls are predefined pieces of content. There are several types of content controls, including text blocks, drop-down menus, combo boxes, calendar controls, and pictures. You can map these content controls to an element in an XML file. Using XPath expressions, you can programmatically map content in an XML file to a content control. This enables you to write a simple and short application to manipulate and modify data in a document.
4. Set the XML mapping on the content control.
XML mapping is a feature of Word 2007 that enables you to create a link between a document and an XML file. This creates true data/view separation between the document formatting and the custom XML data.
To load a custom XML part, you must first add a new data store to a Document object by using the Add method of the CustomXMLParts collection. This appends a new, empty data store to the document. Because it is empty, you cannot use it yet. Next, you must load a custom XML part from an XML file into the data store, by calling the Load method of the CustomXMLPart object, using a valid path to an XML file as the parameter.
5. Add four plain text content controls in the following order.
- Content control for Company Name
- Content control for Contact Name
- Content control for Contact Title
- Content control for Phone
6. Save the template document as C:\CustomerLetterTemplate.docx.
To set an XML mapping on a content control
Note In this procedure, you map the content control to a sample custom XML file. You will create a valid custom XML file, save it to your hard drive, and add a data store to the document that contains the information to which you want to map.
1. Create a text file and save it as c:\CustomerData.xml.
2. Paste the following into the text file and save it:
3. Now, map each content control to bind to the
4. Open the Visual Basic editor and run the following VBA code to add a data store to your template document.
This sample code demonstrates how to attach an XML file to a document, so that it becomes an available data store item.
To create an XML mapping, you use an XPath expression to the node in the custom XML data part to which you want to map a content control. After you add a data store to your document (and the data store points to a valid XML file), you are ready to map one of its nodes to a content control. To do this, pass a String containing a valid XPath to a ContentControl object by using the SetMapping method of the XMLMapping object (via the XMLMapping property of the ContentControl object).
5. Open the Visual Basic editor and run the following VBA code to bind content controls to items in the data store.
strXPath1 = "/Customer/CompanyName"
Dim strXPath2 As String
strXPath2 = "/Customer/ContactName"
Dim strXPath3 As String
strXPath3 = "/Customer/ContactTitle"
Dim strXPath4 As String
strXPath4 = "/Customer/Phone"
You have your template file and you are ready to start your document generation application. I love ASP.NET and I created a server-side application to show you how to generate documents on the server using the Office XML File Formats. You can build a document generation application using Win Forms, a console application, a shared Add-in or your preferred application depending of your business needs.
If you want to learn more about Word 2007 and document templates, read this article:
This blog entry is too long already, so I will split it here and leave you with the intrigue of what part 2 will bring. Stay connected and you will soon know the happy ending for this walkthrough.