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


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:



  1. Open Microsoft Visual Studio 2005 or Microsoft Visual Web Developer 2005.
  2. Create an ASP.NET Web site and name it SqlServerSample.
  3. Connect the ASP.NET Web site to a Microsoft SQL Server database.
  4. 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>

  5. Add the CustomerLetterTemplate.docx to the App_Data folder.
  6. Download and install the Microsoft .NET Framework 3.0 (formerly Microsoft WinFX).
  7. 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>

  8. 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 ObjectByVal 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

SqlServerSample.zip

Comments (49)

  1. imtiyaz says:

    Thanks Eri, Both posts are very useful.

    If anybody is trying to build windows application instead of web-based application, then add reference DLL “Windowsbase.dll” from "C:Program FilesReference AssembliesMicrosoftFrameworkv3.0" folder for “System.IO.Packaging” namespace.

    (If you have installed .NET Framework 3.0)

    -Imtiyaz

  2. erikaehrli says:

    Hi Imi!

    It’s always nice to see your comments :).

  3. I’ve been planning to start pulling together some real world examples around the ways in which people…

  4. Jes&#250;s says:

    Hello,

    I tried to execute your sample code but it fires an exception when it closes the XmlWriter at rutine ‘GetDataFromSQLServer’. The exception says something like ‘Sequence couldn’t be expanded’.

    Could you orient to me?

    Thanks a lot.

    —- The stack at this point is: —-

    NotSupportedException: No se puede expandir la secuencia de memoria.]
      System.IO.__Error.MemoryStreamNotExpandable() +54
      System.IO.MemoryStream.set_Capacity(Int32 value) +33
      System.IO.MemoryStream.EnsureCapacity(Int32 value) +1986396
      System.IO.MemoryStream.Write(Byte[] buffer, Int32 offset, Int32 count) +1986476
      System.IO.BinaryWriter.Write(UInt16 value) +54
      MS.Internal.IO.Zip.ZipIOCentralDirectoryFileHeader.Save(BinaryWriter writer) +47
      MS.Internal.IO.Zip.ZipIOCentralDirectoryBlock.Save() +748
      MS.Internal.IO.Zip.ZipIOBlockManager.SaveContainer(Boolean closingFlag) +659
      MS.Internal.IO.Zip.ZipIOBlockManager.SaveStream(ZipIOLocalFileBlock blockRequestingFlush, Boolean closingFlag) +100
      MS.Internal.IO.Zip.ZipIOFileItemStream.Flush() +45
      MS.Internal.IO.Zip.ProgressiveCrcCalculatingStream.Flush() +31
      MS.Internal.IO.Zip.ZipIOModeEnforcingStream.Flush() +31
      System.Xml.XmlUtf8RawTextWriter.Flush() +48
      System.Xml.XmlWellFormedWriter.Close() +57
      ASP.default_aspx.GetDataFromSQLServer(Stream stream, String customerID) in D:Mis DocumentosVisual Studio 2005ProjectsTestsTestOOXmlDefault.aspx:81
      ASP.default_aspx.CreateDocument() in D:Mis DocumentosVisual Studio 2005ProjectsTestsTestOOXmlDefault.aspx:46
      ASP.default_aspx.SubmitBtn_Click(Object sender, EventArgs e) in D:Mis DocumentosVisual Studio 2005ProjectsTestsTestOOXmlDefault.aspx:88
      System.Web.UI.WebControls.Button.OnClick(EventArgs e) +96
      System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +116
      System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31
      System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
      System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +72
      System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3840

    Jesus:

    I compiled my solution with B2TR and it works. I attached the code to the blog entry (SQLServerSample.zip). The only thing you need to change is the connection string.

     

    I hope this helps,

    -Erika

  5. erikaehrli says:

    I accidentally deleted a comment while cleaning spam. But I rememeber that someone hinted me that I didn’t need this line of code:

    // Get the root part

                       PackagePart pkgpRoot = pkgFile.GetPart(new Uri("/" + pkgr.TargetUri.ToString(), UriKind.Relative));

    You are absolutely right, I was following a different approach before I decided to replace the CustomXMLPart and forgot to delete that line.

    Great catch! Thanks a lot :).

    I updated the samples and attached the source files.

  6. Alberto says:

    Hi Erika,

    I have to tell you that I have the same issue as Jesus getting an exception at the same line ("’Sequence couldn’t be expanded’"). I am using the Beta2TR and have WinFx 3.0

    Please let me know.

    Thanks.

  7. Karl Godtliebsen says:

    Hi Erika.

    How do I open a Word 2007 document (template) add some data (to a contentplaceholder) and print the document, from serverside code, without automating word, like in the bad old days?

  8. erikaehrli says:

    Karl,

    You can download the attached project, this code uses System.IO.Packaging and the File Formats to open aWord 2007 document template, replace data in content controls with server-side code. No automation!

    http://blogs.msdn.com/erikaehrli/attachment/703088.ashx

    You should read this article:

    Server-Side Generation of Word 2007 Docs by Ted Pattison

    http://msdn.microsoft.com/msdnmag/issues/06/11/BasicInstincts/default.aspx

    It’s quite helpful.

    ———————————–

    Jesus and Alberto.

    I run the sample using C#, but didn’t test the VB.NET sample with B2TR, are you using VB.NET? I can’t reproduce, but seems to me by reading the error trace that the problem has something to do with the memory stream and the size of the document. The problem happens at the moment of creating the document.

  9. karl@godtliebsen.dk says:

    Hi Erika.

    Thank you for the answer. I am quite aware of the posibilites for manipulating a word document (great), and I have also read the material you refer to. But, I am still puzzled regarding the actual printing process. It is not a problem to print a fixed document XPS file. The question is, how do I do exactly the same with a words Office Open XML document, without involving Word itself. Whenever I try to print (using XpsDocumentWriter) I get an error because the document is not a fixed document? Mqyb I am missing the obvious?

    Could you show a code sample?

  10. erikaehrli says:

    Karl!

    Seems I forgot the "printing" part of your question.

    Printing server-side is an interesting problem so I will have split my answer since there’s two things to consider.

    Software – hardware?

    Proposing a server-side printing software solution goes hand-in-hand with the hardware infrastructure that the company has. Printing is probably the slowest thing to do in computer land. Say you have a top end printer, you will be able to print at most 50 pages/minute. If you were to print 1,000 documents of 10 pages each, the operation would take you 200 minutes. The least thing that should worry you (considering the times and resources involved to print) is using COM automation. By the time the printer prints the documents, Word automation will be done. Some banks have printers connected in a network to do load balancing, problem is print spoolers have a limited memory, so you would also need some program to enqueue (some kind of hard disk spooler program) documents. I know some banks use special hardware and software to handle massive printing. Some of this programs either print text files (use a driver to print in certifiers) or translate documents and generate a graphic (in memory) and send to the printer (most solutions involving pdf files use this approach). This programs also take care of the document queueing process.

    Printing Word documents…

    To print Word documents you need to run Word. Only Word understands it’s formatting tags. Every single format (bold, underline, colors, spaces, fonts) that you see on the screen are rendered by Word and the same happens with a printer. So the only program that will print you a WYSIWYG Word document is Word. I know it’s not recommended to use server-side automation, but at this point, it’s your best shot.

    Now, there’s an option. It IS possible to go the XPS way with the only problem that you would need to write some kind of parser that reads the WordML formatting elements and transform documents to XPS format (it’s also XML). I don’t know of any code sample to do this, but if you were to build this solution, probably the Windows SDK documentation can help:

    http://windowssdk.msdn.microsoft.com/en-us/library/ms771525.aspx

    I know there are some code samples (including printing XPS documents) that you can download here:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=ea97a39d-6812-4904-8226-85f3da99996b&displaylang=en

    You will find information of every single element for WordML here:

    http://www.ecma-international.org/news/TC45_current_work/TC45-2006-50_final_draft.htm

    There are some translator applications that convert WordML to something else, for example:

    http://openxmldeveloper.org/articles/OpenXMLtoXHTMLinJava.aspx

    Finally, there are more great samples for manipulating WordML here:

    http://openxmldeveloper.org/archive/category/1003.aspx

    I hope this helps,

    -Erika

  11. pete hughes says:

    that is a great example, I’m currently working on a live system that used code like yours.

    but have you found a way to bind a part of the word document to a repeating element to construct a table?

    EG you have a list of items in an order and the output document requires a table to show them in (a item per row).

    i’ve not seen anyway to due this so I have started looking at directly manipulation the word XML for the table. is there a simple way using custom XML?

  12. Dan Nova says:

    You know, you could save yourself a lot of trouble and use the FOR XML clause in SQL Server to build your XML document for you quite painlessly.

    -Dan

  13. Anand says:

    Is it possible to insert a HTMl table as the vale of Content Control

  14. Martin Stockwell says:

    HELP!!!!!!

    Will this kind of setup work with the Business Contact Manager database on a server….

    basically, the Office development team kind of forgot to put support in word mailmerge for the userdefined fields that you can create in BCM…. and all i wont to do is create a mail merge letter template to include some of the user-defined fields…

    im not a happy bunny about this, as we have just spent 4k on 23 office 2007’s with BCM to replace Act… its like taking several steps back in development…

  15. Laughing John says:

    Very useful post and comments Erika. Thanks a lot. Continuing from Karl’s question, it was the case with Word 2003 that Microsoft did not recommend nor support the running of Word on a server. Which is not to say that it couldn’t be done of course.

    Do you know if this has changed with Office 2007 ??

  16. laughing john says:

    In answer to my own question – it appears not. Office 2007 has been added to the list in this page about server side Office use:

    http://support.microsoft.com/kb/257757

    Basically it says that MS do not recommend or support Server usage of Office when using ASP or a windows service.

    The most notable point is that Office could attempt to display a modal dialog which would obviously be bad when running as a service.

  17. Markus says:

    Hi Erika! Thank you for your post. I’m quite new but I hope my question is not too bad – it belongs to the formatting of the text (which from the database):

    You created a document using a template and then filled in the data e.g. Company Name: "The Big Cheese".

    What if we need to write e.g. "Big" italic or bold? So just a part of the data which comes from the database should be formatted anyhow?

    So overall I want to be able to read from a docx some formatted text – save it in a database with the formatting data – and write it also formatted back to e.g. another document (so not just plain text).

    i really hope you know what i mean and i would really be happy if you could give me some hints how to do this best??

    thank you!

    markus

  18. Steve Weinberger says:

    Hi Erika,

    I have created Letter Templates using Word 2007. I have added the mapped the content controls to the tags using Word 2007 Content Control Toolkit. My question is that when I use your code, the item1.xml file is replaced with the values from my Oracle Table. The document.docx appears to be generated correctly as I can see the new values in item1.xml. But when I try to open the .docx file I continually get an error saying the Office Open XML file document.docx cannot be opened because there are problems with the contents. Any ideas on what is causing this? Thanks Steve

  19. steveweinberger says:

    Erika,

    I changed my file to .docx and I was able to fix the problem. Is there any way to save the file to a server folder without prompting the user to save the file? I am thinking there should be a way to save the pkgFile as we can close it.

    Thanks again. Steve

  20. JLuis says:

    nice code!

    I need to code something like this, but I also need to save the doc in some older versions of MS Word.

    Is there a way to do that?

  21. Schaffer says:

    Hi Erika

    i have tried the C# sample code its giving me an exception as "MEMORY STREAM IS NOT EXPANDABLE", this exception is thrown only when the size of the document is big or there r lots of content controls in it. This exception is occured while writing to the Memory i.e during Stream.Close.

    Erika will u please help me out some solution for this.

    Thanks

  22. Joe Sweeney says:

    I only had to make two modifications:

    1. I had to manually add the App_Data folder after publishing the solution.

    2. I had to rename the docx file to CustomerTemplate.docx.

    Otherwise, this worked like a charm.

  23. Fergal Boden says:

    Great code. Thanks. Now how do we print it on the server using managed code?

  24. Jordan says:

    I think that there are more than a few developers scouring the net looking for how to print a docx (or XPS) from managed code.  It would be very helpful if some one could post such code…

    Thanks,

    Jordan

  25. John M. says:

    Erika,

    Thanks for the great example. I have images stored in a sqlserver database that I would would like to display in a .docx. Do you have any idea how to do that? I am able to write code to directly generate the tags I need to display text. This works fine. But so far I am unabe to generate the markup for images.

    Any help would be much appreciated.

    Thanks,

    John

  26. salvatore Sorrentino says:

    Dear erika, like some other users here around I have the well known problem that Schaffer said:

    "i have tried the C# sample code its giving me an exception as "MEMORY STREAM IS NOT EXPANDABLE", this exception is thrown only when the size of the document is big or there r lots of content controls in it. This exception is occured while writing to the Memory i.e during Stream.Close."

    It would be nice to have an answer about this problem.

    Salvatore Sorrentino

  27. MikeSoft says:

    I’m arriving fairly late to this discussion, but after a few hours of puzzling, I think I must be missing something really obvious, and I hope someone could provide a pointer.

    I’ve downloaded Erika’s sample, changed the connection string (which I know is working, because the dropdown displays the correct data), and then click ‘Generate document’, but the only Word document I ever see is one containing the test data in the template provided by Erika (CompanyName Alfreds Futterkiste etc.).

    I never see any of my ‘real’ Northwind data.

    I’ve not altered anything in the sample – only the connection string. What am I missing?

  28. Sanja says:

    Hi Erika,

    This example is great but I can’t get it work just as

    I can’t get rid of the error

    ‘Sequence couldn’t be expanded’

    Would you be so kind to check the code again.

    Thanks in advance.

  29. steveweinberger says:

    Erika,

    Can you save the docx file as a Word 2003 file so the users can open the .doc file using Word 2003? Reason I ask is that we have 2007 on server, we generate the files in .docx. When I do a copyto and save as .doc it can’t be opened by 2003.

    Thanks

    Steve

  30. Steve Turner says:

    When I print a word doc using automation it takes longer to send the print data to the print queue ( 2 minutes for 1 page) than it does to print it. This doesnt happen using word 2003. The same doc prints almost immediately when loaded manually in word and

    selecting print.

    Public Sub objFilePrint(obj As Object, iNumCopies As Integer, Optional vPrinter As Variant)

       Dim sOldPrinter As String

       On Error GoTo CleanupAndExit

       ‘remember the current printer and change to the one we are asked to

       If Not IsMissing(vPrinter) Then

           sOldPrinter = objGetPrinter(obj)

           objSetPrinter obj, vPrinter

       End If

       obj.PrintOut filename:="", Range:=wdPrintAllDocument, Item:= _

           wdPrintDocumentContent, Copies:=iNumCopies, Pages:="", PageType:=wdPrintAllPages, _

           Collate:=True, Background:=False, PrintToFile:=False

    CleanupAndExit:

       ‘Put the old printer back when we have finished

       If sOldPrinter <> "" Then objSetPrinter obj, sOldPrinter

    End Sub

  31. Erika:

    I am developing a form for an application.

    It works fine, except when i try to send some 20 – 25 + strings to as many plain text content controls.

    Then I get the error: Memory stream is not expandable.

    I have been reading stuff for several days but cannot find a solution to my problem. I’m using your code (except tha data layer) but somehow it appears to set the size of the memorystream and that size is very limited.

    I really hope you could help me or tell me wich other technology I need to use. Thanks for any help.

    Rafael

  32. Adam George says:

    Getting the same error as the others and I’m wondering if it has to do with this bug:

    http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=298646

    The error occurs when one of the database fields SELECTed has a large amount of text.

    The error:

    [NotSupportedException: Memory stream is not expandable.]

      System.IO.__Error.MemoryStreamNotExpandable() +54

      System.IO.MemoryStream.set_Capacity(Int32 value) +2845374

      System.IO.MemoryStream.EnsureCapacity(Int32 value) +48

      System.IO.MemoryStream.Write(Byte[] buffer, Int32 offset, Int32 count) +124

      System.IO.BinaryWriter.Write(UInt16 value) +55

      MS.Internal.IO.Zip.ZipIOCentralDirectoryFileHeader.Save(BinaryWriter writer) +168

      MS.Internal.IO.Zip.ZipIOCentralDirectoryBlock.Save() +721

      MS.Internal.IO.Zip.ZipIOBlockManager.SaveContainer(Boolean closingFlag) +473

      MS.Internal.IO.Zip.ZipIOBlockManager.SaveStream(ZipIOLocalFileBlock blockRequestingFlush, Boolean closingFlag) +64

      MS.Internal.IO.Zip.ZipIOFileItemStream.Flush() +24

      MS.Internal.IO.Zip.ProgressiveCrcCalculatingStream.Flush() +17

      MS.Internal.IO.Zip.ZipIOModeEnforcingStream.Flush() +17

      System.Xml.XmlUtf8RawTextWriter.Flush() +26

      System.Xml.XmlWellFormedWriter.Close() +41

      generate_IAR.GetDataFromSQLServer(Stream stream, Int32 customerID) in c:WebsitesSOCWorkTrackersitegenerate_IAR.aspx.cs:106

      generate_IAR.CreateDocument() in c:WebsitesSOCWorkTrackersitegenerate_IAR.aspx.cs:58

      generate_IAR.Page_Load(Object sender, EventArgs e) in c:WebsitesSOCWorkTrackersitegenerate_IAR.aspx.cs:27

      System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15

      System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33

      System.Web.UI.Control.OnLoad(EventArgs e) +99

      System.Web.UI.Control.LoadRecursive() +47

      System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436

  33. Adam George says:

    Figured out the Memory stream is not expandable error. The reason is "Memory streams created with an unsigned byte array provide a non-resizable stream view of the data…"

    http://msdn2.microsoft.com/en-us/library/system.io.memorystream.aspx

  34. I added another content control and try to change from database. It does not work. Can you please tell me how to do that.

  35. Barry says:

    ugh! what if my template has a footer in it, and I want to vary the text value for a field in the footer? I don’t want to create a new footer, I just want to change a content control that is within the footer.

    you example is great, but I’m not able to take it to level that I need…can you please advise me on how I would create a template with a content control in a footer, and how to programmatically change the field via an asp.net app?

    thanks alot!

    Barry Cavanaugh

    cavanaugh_barry@emc.com

  36. >Adam George said:

    >"Figured out the Memory stream is not expandable error"

    Adam (or anyone else), so you figured out the problem, but did you find a solution?

    Can you share the code you used?

    I am also getting the "Memory stream is not expandable" error, but I don’t see the easy fix from the MSDN documentation you linked to.

  37. Dating says:

    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…

  38. I too am getting the "Memory stream is not expandable" error. Where are you Erika?

  39. MickerdyMike says:

    Might have found a solution for the ‘Memory stream is not expandable’ problem:

    Been messing about with it for hours.

    After having set the "CompressionOption" to "CompressionOption.Maximum" at pkgFile.CreatePart it worked for me!!!

    my code:

    PackagePart pkgprtData = pkgFile.CreatePart(uriData, "application/xml", CompressionOption.Maximum);

    Hope it helps in your cases as well….

  40. Wiman says:

    Fix for "Memory stream is not expandable"!

    // Read the file into memory

    byte[] buffer = File.ReadAllBytes(templateDoc);

    // Create in-memory stream as buffer

    MemoryStream stream = new MemoryStream();

    stream.Write(buffer, 0, buffer.Length);

    buffer = null;

  41. Jose says:

    Excelent work Wiman,

    i had Memory stream is not expandable error too.

    Now it works fine.

    Thanks

  42. K Mohan Kumar says:

    Hi Erika,

    <?xml version="1.0"?>

    <Customer>

    <CompanyName>Alfreds Futterkiste</CompanyName>

    <ContactName>Maria Anders</ContactName>

    <ContactTitle>Sales Representative</ContactTitle>

    <Phone>030-0074321</Phone>

    </Customer>

    In the above code we mapped the plain text to the content controls? Is there anyway to input the image on the sample xml file?

    Thanks,

    Mohan

  43. Cooper says:

    I want to create a report. How do I add another control? I want to add data that is related to this person?

  44. Terry Holland says:

    Erika

    I wonder if you could give some pointers on a problem that I have

    Im porting a VB6 application to ASP.Net.  The exisinting app has approx 200 MSWord mailmerge templates and uses automation to merge data, initially exported from a sql database into CSV files, for bulk letter printing.

    I am aware that having word installed on the web/app server is not an option recommended or supported by MS, so I am looking for alternative solutions that will enable me to utilise the existing word (2003) templates.

    I have downloaded the OpenXMLSDK 2.0 and am trying to work my way

    through some ideas.

    I have found the MailMerge class in the

    DocumentFormat.OpenXml.Wordprocessing Namespace but Im finding it impossible

    to find any examples of how I might use this class on an existing word

    mailmerge template document.

    In pseudo code Im trying to do something like this

     Using doc(MyWord2003MailMergeTemplate.doc)

               Dim oMM As New MailMerge (doc)

               oMM.DataSource = MyExcelWorksheetContainingMyData

               oMM.Destination = NewWordDocument

               oMM.MergeData

        oMM.Print

     End Using

  45. TangledInMemoryStream says:

    Wiman, that solution is just sublime! You are a genius!

  46. limo says:

    Is there a way by which I can generate word documents as reports like get rid of Crystal reports to be honest.

  47. CableDOC says:

    We use an XML export from SQL with 20+ tables and often many rows within 1/2 of these tables for each project.  Is there an effective way to link Word 2007 directly to that XML rather than create another solution?

  48. Limo Hire says:

    Excellent info, works great for me!

  49. Chit Min Maung says:

    Hi, Your example is very work well for me. also very nice explanation. But I don't know how can I implement for Header/ Footer. If you have any idea please explain me. Thanks.