Mail Merging Into a Single Word Document Using XML Literals

re: Mail Merging Into a Single Word Document Using XML Literals

MAP — Tue, 12 Apr 2011 13:43:34 GMT

Hi,

this article is good and i am thankful to you.

but some problem i am having that... URI path is not working....

can u please suggest me.....

this part

Dim uri As New Uri("/word/document.xml", UriKind.Relative)

this URI not getting any path value....

and no styles and footer added to the newly created doc file!

Please help

Regards,

Map.

re: Mail Merging Into a Single Word Document Using XML Literals

Reddy Lutonadio — Thu, 20 Jan 2011 10:45:34 GMT

Great blog!

I have made some modifications. Instead of adding the following

<w:p w:rsidR="00622A50" w:rsidRDefault="00622A50">

<w:r>

<w:br w:type="page"/>

</w:r>

</w:p>

for the break page in the query,

I have added it in the for each statement, like this

For Each letter In letters

Dim nextbody = letter.Documents...<w:body>.First

If i = 0 Then

mainBody.ReplaceNodes(nextbody.Elements)

Else

'Add a page break if there is another letter

mainBody.Add(<w:p w:rsidR="00622A50" w:rsidRDefault="00622A50">

<w:r>

<w:br w:type="page"/>

</w:r>

</w:p>)

mainBody.Add(nextbody.Elements)

End If

i += 1

This way, a blank page is not printed after all other pages have been printed.

Good or bad? Is there a better way of doing it?

re: Mail Merging Into a Single Word Document Using XML Literals

PhilM — Fri, 05 Feb 2010 23:09:34 GMT

Yeah, the next move would be to figure out how to use XSLT to replace the placeholders with the appropriate data. Then I wouldn't have to go to string, use replace, and then go back. And I probably would not have to import System.Web (I was only looking for HTMLEncode because some of the data has special characters).

The Client Profile is new to me but I'm going to run all of this server-side where presumably the full .Net Framework will be installed.

Thanks for your help. Anything I can do to say thanks? philm@cottagecomputing.com

re: Mail Merging Into a Single Word Document Using XML Literals

Beth Massi — Fri, 05 Feb 2010 20:13:20 GMT

Hi PhilM,

Great, you could also use XSLT as well this way. Just be careful with your Replace statements because it may give you unpredictable results. If you have a placeholder name that the user is also using in the template itself it will overwrite it.

Also by using System.Web you won't be able to run on the Client Profile (but that probably doesn't matter for your case).

Cheers,

-B

re: Mail Merging Into a Single Word Document Using XML Literals

PhilM — Fri, 05 Feb 2010 03:36:09 GMT

Hey, I found an archaic way to do it!! I can now load the XML from a template docx, loop through all the orders creating letters with the data from the order in each one. It uses your code but drops the XML Literal and doesn't use LINQ. (Sorry, but LINQ seems to be great for compile time).

This may be self-evident:

Sub Main()

Console.WriteLine("Mail merging....")

Dim db As New NorthwindDataContext

'Letter query -- Create a query that will create Letter objects for each

' of the customers in Northwind that have shipping orders:

'My version

Dim strLetter As String

Dim strLetter2 As String

'Now load it dynamically

Dim sourceFile = CurDir() & "\QuickTest.docx"

'Open the Document template using the Open XML SDK

Using wordDoc = WordprocessingDocument.Open(sourceFile, False)

'get the main document part (document.xml)

Dim mainPart = wordDoc.MainDocumentPart()

Dim mainDocumentXML As XDocument

Using sr As New StreamReader(mainPart.GetStream)

mainDocumentXML = XDocument.Load(sr) 'load the document XML

End Using

'Prepend the document XML. StrLetter becomes our defacto template for each Letter.

strLetter = "<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>" + mainDocumentXML.ToString

End Using

Dim MyLetters As New List(Of Letter)

Dim MyLetter As Letter

For Each Order In db.Orders 'No filters to make it simple

MyLetter = New Letter

strLetter2 = strLetter.Replace("CUSTOMERNAME", HtmlEncode(Order.Customer.CompanyName)) 'Must Import System.Web.Httputility

MyLetter.CustomerID = Order.Customer.CustomerID

MyLetter.Document = XDocument.Parse(strLetter2)

MyLetters.Add(MyLetter)

CreateOneLetter(MyLetters)

End Sub

re: Mail Merging Into a Single Word Document Using XML Literals

PhilM — Fri, 05 Feb 2010 01:55:36 GMT

Yeah, I'm actually headed for a server side solution with no MSOffice Installed. So that's why the VSTO solution is out.

Thanks, I'll check often to see if you have new ideas. What I'll do for now is recompile when the user changes the letter layout (or anything about it). There must be a way..... Thanks again

re: Mail Merging Into a Single Word Document Using XML Literals

Beth Massi — Fri, 05 Feb 2010 01:15:40 GMT

You could definitely do this with a VSTO add-in or template. That way you could give the user the ability to drag placeholders onto the actual letter and then you could just use VSTO to query the data and populate the document directly.

I'm assuming you don't like this idea though because you're trying to automate and scale the generation process. But VSTO is a great end-user solution.

I'll think about your scenario, I think there's a way I just need to focus on it for a bit (and I have a million things running around attm ;-))

re: Mail Merging Into a Single Word Document Using XML Literals

PhilM — Fri, 05 Feb 2010 00:40:01 GMT

Thanks, you've been quite helpful. This is close to being another dead end I guess. At least I learned how it worked. I thought when I saw that you hoisted the XML out of the Word template, put it in the program and modified it, that I could do the same at run time. That way the user could modify the layout of the template without a recompile of the program. No soap I guess. Now what, I'll look over your 'reverse' idea but I really want to have the user have control of the template yet still be able to run a merge using it. There's gotta be a way... sigh.

re: Mail Merging Into a Single Word Document Using XML Literals

Beth Massi — Thu, 04 Feb 2010 19:54:58 GMT

Hi PhilM,

You need to realize that the use of embedded expressions is ONLY going to work if you are doing it in the VB editor because these expressions are compiled just like any other code. It's not script. Meaning that if you have <%= %> in your XML "string" these will not be compiled magically by the Parse method, you'll end up with invalid XML. Does that make sense?

Maybe you could try to add your own attributes to the WordProcessingML? I'll try to play with this when I have some free time.

You may want to think about it in reverse. You can use embedded expressions to actually create the XML itself, not just the values. That way you could construct the template on the fly. i.e. you could do

Dim myElementName = "MyElement"

Dim myValue = "Value"

Dim x = <<%= myElementName %>><%= myValue %></>

Console.WriteLine(x.ToString)

Console.ReadLine()

Not sure if that helps you but it's a thought.

re: Mail Merging Into a Single Word Document Using XML Literals

PhilM — Thu, 04 Feb 2010 19:25:20 GMT

There doesn't seem to be any rational way to have repeated data bind to the same content control (e.g. Content control for customer name in the template appears once, but needs to repeatedly bound to new custom xml elements as in a loop. One only has to bing 'repeating content controls' to find out how big a problem this is.

I've played a lot with the OpenXML SDK and it offers nothing for this either. At least not that I have found.

Your code is SO CLOSE!!! I want to change the last statement in the linq SELECT to use a variable which I have loaded from the template, and modified to insert the <%=CustomerName variable that you showed. But my documents are coming out invalid in Word, not sure why. My code follows (from Module 1) where in I repalced the XML literal with a variable that I can't seem to get right:

Using sr As New StreamReader(mainPart.GetStream)

mainDocumentXML = XDocument.Load(sr) 'Note change from Xelement

End Using

Dim mainDocumentXMLString As String = mainDocumentXML.ToString

'Perform magic (Please the placeholders with the <% = variables

mainDocumentXML = XDocument.Parse(mainDocumentXMLString)

End Using

'Letter query -- Create a query that will create Letter objects for each

' of the customers in Northwind that have shipping orders:

Dim letters = _

From Order In db.Orders _

Join Employee In db.Employees _

On Order.EmployeeID Equals Employee.EmployeeID _

Where Order.OrderDate IsNot Nothing AndAlso _

Order.ShippedDate IsNot Nothing AndAlso _

Order.ShippedDate.Value.Date >= #1/1/1998# _

Let DateOrder = Order.OrderDate.Value.ToShortDateString _

Let DateShip = Order.ShippedDate.Value.ToShortDateString _

Select New Letter With { _

.CustomerID = Order.CustomerID, _

.OrderID = Order.OrderID, _

.EmployeePhoto = If(Employee.Photo IsNot Nothing, Employee.Photo.ToArray, Nothing), _

.Document = mainDocumentXML}