Introduction
Suppose all information of my customers is stored on a database system or XML file and I am now requested to create hundreds of word letters, reports that will be sent to my customers. These letters have the same content but different customer name, customer address, etc.
There are some ways to make this task possible, but the new features of Office Word 2007 help me easily finish it. Creating a new document template with content controls is the new feature I'm interested in. NET Framework 3.0 also provides a new method to replicate, replacing the content of this template.
In this article, I am going to focus on how to create the document template and then automatically generate the others from this template.
Background
Open XML standardization becomes a new default format in Office 2007 based on ECMA standard. It definitely has some advantages because XML is the perfect format and it is already supported by many vendors.
You can learn more about OpenXML by clicking on the following links:
Using the Code
I use Visual Studio 2005, .NET Framework 3.0 to implement my application.
To help you get started with this article, the following steps are going to show you how to:
- Create the Word 2007 document template with Content Controls.
- Create custom XML file to map Content Controls to XML element in custom XML file.
- Create a relationship to link between the template document and Custom XML file.
- Define
OrderInformation class. - Define
GenerateDocument class. - Run application GenerateWord2007.exe.
Step 1: Create the Word 2007 Document Template with Content Controls
Imagine that here is the letter I have to design:
In new document, I insert the appropriate plain text content controls by using the Developer tab. If the Developer tab doesn't appear on Microsoft Word 2007, "Show Developer Tab in the Ribbon" on Word Options should be checked. Name this document LabelForm.docm.
Step 2: Create Custom XML File to Map Content Controls of the Document Template to XML Element in Custom XML File
Based on the plain text content controls of LabelForm.docm, I create a new custom XML file item1.xml:
The schema of this XML is entirely flexible to build, each element of Custom XML is set to each plain text content control of LabelForm.docm.
Step 3: Create a Relationship between the Template Document and Custom XML File
In order to make the content controls and custom XML file understand each other, I have to write some macros to link them. On Word 2007 screen, I create the new Macro CreateCustomXMLPart to attach item1.xml to LabelForm.docm.
///Initial CustomXMLPart
Sub CreateCustomXMLPart()
ActiveDocument.CustomXMLParts.Add
ActiveDocument.CustomXMLParts(12).Load ("\\customXml\item1.xml")
End Sub
And another macro LoadCustomXMLPart to map the content controls of LabelForm.docm:
///Load CustomXMLPart
Sub LoadCustomXMLPart()
Dim strXPath1 As String
strXPath1 = "/Customer/Address"
ActiveDocument.ContentControls(1).XMLMapping.SetMapping strXPath1
Dim strXPath2 As String
strXPath2 = "/Customer/City"
ActiveDocument.ContentControls(2).XMLMapping.SetMapping strXPath2
........
End Sub
I am ready to map one of its nodes to a content control. Run these macros, all plain text content controls of LabelForm.docm are automatically updated. Name it LetterFormTemplate.docm.
Because my goal is to read data from SQL Server and each record will be generated to the new document, I write another macro to bind data to the appropriate content controls of LetterFormTemplate.docm when this template is open.
Private Sub Document_Open()
Call BindData
End Sub
///Sub Bind each column of the record to the appropriate
content control
Sub Sub BindData()
Dim part As CustomXMLPart
Set part = ActiveDocument.CustomXMLParts.SelectByNamespace("")(1)
Dim ctlAddress As ContentControl
Dim ctlCity As ContentControl
........
Set ctlAddress = ActiveDocument.ContentControls(1)
Set ctlCity = ActiveDocument.ContentControls(2)
........
ctlAddress.XMLMapping.SetMapping "/Customer/Address", "", part
ctlCity.XMLMapping.SetMapping "/Customer/City", "", part
........
End Sub
I completely finish the document template.
Step 4: Define OrderInformation Class
The purpose of OrderInformation class is merely to build the XML structure like item1.xml so that I can bind this data to LetterFormTemplate.docm.
public string Address
{
get { return _address; }
set { _address = value; }
}
public string City
{
get { return _city; }
set { _city = value; }
}
.....
.........
public string ToXml()
{
XmlDocument xmlDoc = new XmlDocument();
XmlElement root = xmlDoc.CreateElement("Customer");
xmlDoc.AppendChild(root);
AppendChild(xmlDoc, root, "Address", _address);
AppendChild(xmlDoc, root, "City", _city);
AppendChild(xmlDoc, root, "Region", _region);
AppendChild(xmlDoc, root, "PostCode", _postcode);
AppendChild(xmlDoc, root, "Country", _country);
AppendChild(xmlDoc, root, "CustomerID", _customerID.ToString());
AppendChild(xmlDoc, root, "OrderID", _orderID.ToString());
AppendChild(xmlDoc, root, "ShippedDate", _shippedDate.ToString());
AppendChild(xmlDoc, root, "HomePhone", _homePhone);
AppendChild(xmlDoc, root, "LastName", _lastName);
AppendChild(xmlDoc, root, "FirstName", _firstName);
AppendChild(xmlDoc, root, "Title", _title);
return xmlDoc.InnerXml;
}
private void AppendChild(XmlDocument xmlDoc, XmlElement root,
string nodeName, string nodeValue)
{
XmlElement newElement = xmlDoc.CreateElement(nodeName);
newElement.InnerText = nodeValue;
root.AppendChild(newElement);
}
Step 5: Define GenerateDocument Class
A main function GenerateWord() is implemented to generate the new document named the value of CustomerID from LetterFormTemplate.docm. In this class, System.IO.Package of .NET 3.0 is used to replicate the template, replacing custom XML file and finally generating the new document.
public void GenerateWord( OrderInformation orderInfo)
{
string templateDoc = "LetterFormTemplate.docm";
string filename = string.Format("{0}.docm", orderInfo.CustomerID);
File.Copy(templateDoc, filename, true);
Package pkg = Package.Open(filename, FileMode.Open, FileAccess.ReadWrite);
Uri uri = new Uri("/customXml/item1.xml", UriKind.Relative);
PackagePart part = pkg.GetPart(uri);
XmlDocument doc = new XmlDocument();
doc.LoadXml(orderInfo.ToXml());
StreamWriter partWrt = new StreamWriter
(part.GetStream(FileMode.Open, FileAccess.Write));
doc.Save(partWrt);
partWrt.Flush();
partWrt.Close();
}
Everything is already done. Now I just implement my requirements.
* Read data from SQL Server to create the new OrderInformation instance. Assume that Northwind database is already set up. I execute a query to create the letter to Customer who owns order number 10205:
public OrderInformation GetOrderInformation()
{
string sqlQuery;
sqlQuery = "SELECT o.OrderID, o.CustomerID, o.ShippedDate, ";
sqlQuery = sqlQuery + "e.LastName, e.FirstName, e.Title,e.Address, _
e.City, e.Region, e.PostalCode,e.HomePhone,e.Country ";
sqlQuery = sqlQuery + "FROM Orders o ";
sqlQuery = sqlQuery + "INNER JOIN Employees e ON _
e.EmployeeID = o.EmployeeID ";
sqlQuery = sqlQuery + "WHERE o.OrderID = 10250";
using (SqlConnection conn = new SqlConnection_
(@"Integrated Security=SSPI;Persist Security Info=False;_
Initial Catalog=Northwind;Data Source=localhost"))
{
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
cmd.Connection.Open();
SqlDataReader sdr = cmd.ExecuteReader();
sdr.Read();
OrderInformation orderInfo = new OrderInformation();
orderInfo.OrderID = sdr.GetInt32(0);
orderInfo.CustomerID = sdr.GetString(1);
orderInfo.ShippedDate = sdr.GetDateTime(2);
orderInfo.LastName = sdr.GetString(3);
orderInfo.FirstName = sdr.GetString(4);
orderInfo.Title = sdr.GetString(5);
orderInfo.Address = sdr.GetString(6);
orderInfo.City = sdr.GetString(7);
orderInfo.Region = sdr.GetString(8);
orderInfo.PostCode = sdr.GetString(9);
orderInfo.HomePhone = sdr.GetString(10);
orderInfo.Country = sdr.GetString(11);
return orderInfo;
}
}
* Read data from XML file that contains many Customers:
Parse XML data, storing them on List of OrderInformation:
public List ListOrders()
{
XmlDocument customers = new XmlDocument();
Hashtable nodeData = new Hashtable();
customers.Load("Data.xml");
List<orderinformation /> orderList = new List<orderinformation />();
foreach (XmlNode customer in customers.DocumentElement.ChildNodes)
{
foreach (XmlElement element in customer.ChildNodes)
{
{
nodeData.Add(element.Name, element.InnerText);
}
}
if (nodeData.Count > 0)
{
OrderInformation orderInfo = new OrderInformation();
orderInfo.OrderID = Convert.ToInt32(nodeData["OrderID"]);
orderInfo.CustomerID = nodeData["CustomerID"].ToString();
orderInfo.ShippedDate = Convert.ToDateTime(nodeData["ShippedDate"]);
orderInfo.LastName = nodeData["LastName"].ToString();
orderInfo.FirstName = nodeData["FirstName"].ToString();
orderInfo.Title = nodeData["Title"].ToString();
orderInfo.Address = nodeData["Address"].ToString();
orderInfo.City = nodeData["City"].ToString();
orderInfo.Region = nodeData["Region"].ToString();
orderInfo.PostCode = nodeData["PostCode"].ToString();
orderInfo.HomePhone = nodeData["HomePhone"].ToString();
orderInfo.Country = nodeData["Country"].ToString();
orderList.Add(orderInfo);
nodeData.Clear();
}
}
return orderList;
}
Step 6: Run Application GenerateWord2007.exe
This application is implemented by two methods to generate the new documents that data is read from SQL Server or XML file.
From SQL Server
private void button1_Click(object sender, EventArgs e)
{
OrderInformation orderInfo;
GenerateDocument doc = new GenerateDocument();
orderInfo = doc.GetOrderInformation();
doc.GenerateWord(orderInfo);
}
From XML File
private void button2_Click(object sender, EventArgs e)
{
GenerateDocument doc = new GenerateDocument();
List orderList = new List();
orderList = doc.ListOrders();
foreach (OrderInformation orderInfo in orderList)
{
doc.GenerateWord(orderInfo);
}
}
Points of Interest
Open XML is definitively worth learning. Some new extensions such as *.docx for Microsoft Word, *.xlsx for Microsoft Excel or *.pptx for PowerPoint gives us the ability to read or modify them without using API. I just want to share my ideas so that you can have an overview about the concept or you may create something more efficient.
History
- 26th August, 2007: Initial post
