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:
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:
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.
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.
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 Documentos\Visual Studio 2005\Projects\Tests\TestOOXml\Default.aspx:81 ASP.default_aspx.CreateDocument() in D:\Mis Documentos\Visual Studio 2005\Projects\Tests\TestOOXml\Default.aspx:46 ASP.default_aspx.SubmitBtn_Click(Object sender, EventArgs e) in D:\Mis Documentos\Visual Studio 2005\Projects\Tests\TestOOXml\Default.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
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,
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.
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.
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?
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!
You should read this article:
Server-Side Generation of Word 2007 Docs by Ted Pattison
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.
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?
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:
I know there are some code samples (including printing XPS documents) that you can download here:
You will find information of every single element for WordML here:
There are some translator applications that convert WordML to something else, for example:
Finally, there are more great samples for manipulating WordML here:
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?
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.
Is it possible to insert a HTMl table as the vale of Content Control
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...
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 ??