Data-driven document generation with Word 2007 and the Office XML File Formats: Part 2

Erika Ehrli Cabral

16 Aug 2006 3:20 PM

Comments 49

So you are looking for a component/technology/solution to help you generate Office files (documents, workbooks, and presentations) using a server-side application, and of course, using managed code.

I can tell you that this need has been brought to my attention at some conferences, questions in DLs, customer feedback, and as a common customer requirement when I was working as a consultant. You know that using automation and interop is definitely not an option. However, the Office XML File Formats are a great option.

You can build a server-side application using Visual Studio to generate data-rich documents using the Office XML File Formats and the .NET Framework 3.0 (aka Microsoft WinFX ). Here's how...

In my previous blog entry I demonstrated how to build and Word 2007 template and connect an item in the data store. You built a customer letter document template with content controls that map to an XML file. In this blog entry, I will show you how to 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
Phone

To create a server-side application that pulls data from a SQL Server database and generates a new Word 2007 document

You can create a Web-based application that enables users to select a company name and generate a custom letter. The Web-based application retrieves customer data from a Microsoft SQL Server database, opens the customer letter document template, and creates a new document that displays customer data based on a user selection. This application does not require the use of Word 2007 or VBA. You can use your favorite managed code (Microsoft Visual Basic .NET or C#) language to build this application. To build this application, do the following:

Open Microsoft Visual Studio 2005 or Microsoft Visual Web Developer 2005.
Create an ASP.NET Web site and name it SqlServerSample.
Connect the ASP.NET Web site to a Microsoft SQL Server database.
Add a connection string to the Web.config file as follows:
<connectionStrings>
<add name="NorthwindConnectionString"
connectionString="data source=(local);database=Northwind; integrated security=SSPI;persist security info=false;"
providerName="System.Data.SqlClient" />
</connectionStrings>
Add the CustomerLetterTemplate.docx to the App_Data folder.
Download and install the Microsoft .NET Framework 3.0 (formerly Microsoft WinFX).
Configure the assembly in the Web.config file as follows:
<compilation debug="false">
      <assemblies>
        <add assembly="WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
   </assemblies>
</compilation>
Create a Web Form and replace the default.aspx code with the following sample code.

The following sample shows how to bind to a Microsoft SQL Server database to retrieve data based on a customer selection and create a new document based on the CustomerLetterTemplate.docx.

[VB.NET]

<%@ Page Language="VB" AutoEventWireup="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.IO.Packaging" %>
<%@ Import Namespace="System.Xml" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Data-Driven Document Generation - SQL Server Sample</title>
</head>

<script runat='server'>

    Private Const strRelRoot As String = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"

    Private Sub CreateDocument()
        ' Get the template file and create a stream from it
        Const TemplateFile As String = "~/App_Data/CustomerTemplate.docx"

        ' Read the file into memory
        Dim buffer() As Byte = File.ReadAllBytes(Server.MapPath(TemplateFile))
        Dim memoryStream As MemoryStream = New MemoryStream(buffer, True)
        buffer = Nothing

        ' Open the document in the stream and replace the custom XML part
        Dim pkgFile As Package = Package.Open(memoryStream, FileMode.Open, FileAccess.ReadWrite)
        Dim pkgrcOfficeDocument As PackageRelationshipCollection = pkgFile.GetRelationshipsByType(strRelRoot)
        For Each pkgr As PackageRelationship In pkgrcOfficeDocument
            If (pkgr.SourceUri.OriginalString = "/") Then

                ' Add a custom XML part to the package
                Dim uriData As Uri = New Uri("/customXML/item1.xml", UriKind.Relative)
                If pkgFile.PartExists(uriData) Then

                    ' Delete template "/customXML/item1.xml" part
                    pkgFile.DeletePart(uriData)
                End If

                ' Load the custom XML data
                Dim pkgprtData As PackagePart = pkgFile.CreatePart(uriData, "application/xml")
                GetDataFromSQLServer(pkgprtData.GetStream, ddlCustomer.SelectedValue)
            End If
        Next

        ' Close the file
        pkgFile.Close()

        ' Return the result
        Response.ClearContent()
        Response.ClearHeaders()
        Response.AddHeader("content-disposition", "attachment; filename=document.docx")
        Response.ContentEncoding = System.Text.Encoding.UTF8
        memoryStream.WriteTo(Response.OutputStream)
        memoryStream.Close()
        Response.End()
    End Sub

    Private Sub GetDataFromSQLServer(ByVal stream As Stream, ByVal customerID As String)

        'Connect to a Microsoft SQL Server database and get data
        Dim source As String = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
        Const SqlStatement As String = "SELECT CompanyName, ContactName, ContactTitle, Phone FROM Customers WHERE CustomerID=@customerID"
        Dim conn As SqlConnection = New SqlConnection(source)
        conn.Open()
        Dim cmd As SqlCommand = New SqlCommand(SqlStatement, conn)
        cmd.Parameters.AddWithValue("@customerID", customerID)
        Dim dr As SqlDataReader = cmd.ExecuteReader
        If dr.Read Then
            Dim writer As XmlWriter = XmlWriter.Create(stream)
            writer.WriteStartElement("Customer")
            writer.WriteElementString("CompanyName", CType(dr("CompanyName"), String))
            writer.WriteElementString("ContactName", CType(dr("ContactName"), String))
            writer.WriteElementString("ContactTitle", CType(dr("ContactTitle"), String))
            writer.WriteElementString("Phone", CType(dr("Phone"), String))
            writer.WriteEndElement()
            writer.Close()
        End If
        dr.Close()
        conn.Close()
    End Sub

    Protected Sub SubmitBtn_Click(ByVal sender As Object, ByVal e As EventArgs)
        CreateDocument()
    End Sub
</script>

<body>
    <form id="form1" runat="server">
        <div>
            <h1>
                Customer Letter Generator</h1>
            <table border="0" cellpadding="0" cellspacing="0" style="width: 100%; height: 12%">
                <tr>
                    <td>
                        Choose a customer:</td>
                    <td>
                        <asp:DropDownList ID="ddlCustomer" runat="server" AutoPostBack="True" DataSourceID="CustomerData"
                            DataTextField="CompanyName" DataValueField="CustomerID" Width="301px">
                        </asp:DropDownList>
                        <asp:SqlDataSource ID="CustomerData" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
                            SelectCommand="SELECT [CustomerID], [CompanyName] FROM [Customers]" ProviderName="<%$ ConnectionStrings:NorthwindConnectionString.ProviderName %>">
                        </asp:SqlDataSource>
                    </td>
                </tr>
            </table>
        </div>
        <br />
        <asp:Button ID="Button1" runat="server" OnClick="SubmitBtn_Click" Text="Create Letter"
            Width="123px" />
    </form>
</body>
</html>

[C#]

<%@ Page Language="C#" AutoEventWireup="true"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.IO.Packaging" %>
<%@ Import Namespace="System.Xml" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Data-Driven Document Generation - SQL Server Sample</title>
</head>
    <script language="C#" runat="server">

        private const string strRelRoot = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument";

        private void CreateDocument() {
            // Get the template file and create a stream from it
            const string TemplateFile = @"~/App_Data/CustomerTemplate.docx";

            // Read the file into memory
            byte[] buffer = File.ReadAllBytes(Server.MapPath(TemplateFile));
            MemoryStream memoryStream = new MemoryStream(buffer, true);
            buffer = null;

            // Open the document in the stream and replace the custom XML part
            Package pkgFile = Package.Open(memoryStream, FileMode.Open, FileAccess.ReadWrite);
            PackageRelationshipCollection pkgrcOfficeDocument = pkgFile.GetRelationshipsByType(strRelRoot);
            foreach (PackageRelationship pkgr in pkgrcOfficeDocument) {
                if (pkgr.SourceUri.OriginalString == "/") {
                                       // Add a custom XML part to the package
                    Uri uriData = new Uri("/customXML/item1.xml", UriKind.Relative);

                    if (pkgFile.PartExists(uriData)) {
                        // Delete template "/customXML/item1.xml" part
                        pkgFile.DeletePart(uriData);
                    }
                    // Load the custom XML data
                    PackagePart pkgprtData = pkgFile.CreatePart(uriData, "application/xml");
                    GetDataFromSQLServer(pkgprtData.GetStream(), ddlCustomer.SelectedValue);
                }
            }

            // Close the file
            pkgFile.Close();

            // Return the result
            Response.ClearContent();
            Response.ClearHeaders();
            Response.AddHeader("content-disposition", "attachment; filename=document.docx");
            Response.ContentEncoding = System.Text.Encoding.UTF8;

            memoryStream.WriteTo(Response.OutputStream);

            memoryStream.Close();

            Response.End();
        }

        private void GetDataFromSQLServer(Stream stream, string customerID) {
            //Connect to a Microsoft SQL Server database and get data
            String source = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
            const string SqlStatement =
                "SELECT CompanyName, ContactName, ContactTitle, Phone FROM Customers WHERE CustomerID=@customerID";

            using (SqlConnection conn = new SqlConnection(source)) {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SqlStatement, conn);
                cmd.Parameters.AddWithValue("@customerID", customerID);
                SqlDataReader dr = cmd.ExecuteReader();

                if (dr.Read()) {
                    XmlWriter writer = XmlWriter.Create(stream);
                    writer.WriteStartElement("Customer");
                    writer.WriteElementString("CompanyName", (string)dr["CompanyName"]);
                    writer.WriteElementString("ContactName", (string)dr["ContactName"]);
                    writer.WriteElementString("ContactTitle", (string)dr["ContactTitle"]);
                    writer.WriteElementString("Phone", (string)dr["Phone"]);
                    writer.WriteEndElement();
                    writer.Close();
                }
                dr.Close();
                conn.Close();
            }
        }

        protected void SubmitBtn_Click(object sender, EventArgs e) {
            CreateDocument();
        }

    </script>

<body>
    <form id="form1" runat="server">
    <div>
            <h1>Customer Letter Generator</h1>
            <table border="0" cellpadding="0" cellspacing="0" style="width: 100%; height: 12%">
                <tr>
                    <td>
                        Choose a customer:</td>
                    <td>
                        <asp:DropDownList
                           ID="ddlCustomer"
                           runat="server"
                           AutoPostBack="True"
                           DataSourceID="CustomerData"
                           DataTextField="CompanyName"
                           DataValueField="CustomerID"
                           Width="301px">
                        </asp:DropDownList>
                        <asp:SqlDataSource
                          ID="CustomerData"
                          runat="server"
                          ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
                          SelectCommand="SELECT [CustomerID], [CompanyName] FROM [Customers]" ProviderName="<%$ ConnectionStrings:NorthwindConnectionString.ProviderName %>">
                        </asp:SqlDataSource>
                    </td>
                </tr>
          </table>
        </div>
        <br />
        <asp:Button
          ID="Button1"
          runat="server"
          OnClick="SubmitBtn_Click"
          Text="Create Letter"
          Width="123px" />
    </form>
</body>
</html>

If you build and run this application, you will see something like this:

As you can see, the code is simple and optimized for performance and memory. You can start building data-driven document solutions using this code. If your data source is an Access database, Web service, Oracle database, text file, or any other data source, it doesn't matter. Bottom-line, you only need to change the data access layer code. What matters the most is that using this technology you will be able to build server-side applications that generate Office documents using managed-code. I just love the potential that the Office XML File Formats provide and many thanks to Tristan Davis for sharing his knowledge on how to do this :).

Finally, I found out today that Microsoft published a virtual lab related with the Office XML File Formats and I wanted to share this as well:

Programmatic Manipulation of the Microsoft Office Open XML Formats

Learn how to manipulate Microsoft Office system documents using the Microsoft Office Open XML Formats without the 2007 release. Work through scenarios involving programmatically manipulating documents using the Microsoft Office Open XML Formats.

Enjoy!
~Erika

Attachment:

SqlServerSample.zip