OpenXML/DocumentFormat.OpenXml set excel as DataSource for MailMerge in word (Letter series)

Question

I try to set an excel file as Address-List-Source in word (as recipient), so that i can use MailMerge to create a word letter series (for example for 100 addresses).

I wrote the following code using DocumentFormat.OpenXml, but when i open my DocX file, there is no data source.

I use the following code:

        using (WordprocessingDocument wordDocument = WordprocessingDocument.Open("Microsoft Word-Dokument (neu).docx", true))
        {
            var settingsPart = wordDocument.MainDocumentPart.GetPartsOfType<DocumentSettingsPart>().First();

            var mailMerge = new MailMerge();

            mailMerge.MainDocumentType = new MainDocumentType();
            mailMerge.MainDocumentType.SetAttribute(new DocumentFormat.OpenXml.OpenXmlAttribute("val", null, "formatLetters"));

            string excel = @"C:\test.xlsx";

            mailMerge.LinkToQuery = new LinkToQuery();

            mailMerge.DataType = new DataType();
            mailMerge.DataType.SetAttribute(new DocumentFormat.OpenXml.OpenXmlAttribute("val", null, "native"));

            mailMerge.ConnectString = new ConnectString();
            mailMerge.ConnectString.SetAttribute(new OpenXmlAttribute("val", null, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";"));

            mailMerge.Query = new Query();
            mailMerge.Query.SetAttribute(new OpenXmlAttribute("val", null, "SELECT * FROM `Tabelle1$`"));



            mailMerge.ViewMergedData = new ViewMergedData();

            mailMerge.DataSourceObject = new DataSourceObject();
            mailMerge.DataSourceObject.UdlConnectionString = new UdlConnectionString();
            mailMerge.DataSourceObject.UdlConnectionString.SetAttribute(new OpenXmlAttribute("val", null, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";"));

            mailMerge.DataSourceObject.DataSourceTableName = new DataSourceTableName();
            mailMerge.DataSourceObject.DataSourceTableName.SetAttribute(new OpenXmlAttribute("val", null, "Tabelle1$"));

            mailMerge.DataSourceObject.ColumnDelimiter = new ColumnDelimiter();
            mailMerge.DataSourceObject.ColumnDelimiter.SetAttribute(new OpenXmlAttribute("val", null, "9"));

            settingsPart.Settings.RemoveAllChildren<MailMerge>();
            settingsPart.Settings.InsertAt<MailMerge>(mailMerge, 0);

            foreach (var relationship in wordDocument.ExternalRelationships.Where(Rel => Rel.RelationshipType == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/mailMergeSource"))
            {
                wordDocument.DeleteExternalRelationship(relationship);
            }

            string DataPath = excel;
            var dsRelationship = wordDocument.MainDocumentPart.DocumentSettingsPart.AddExternalRelationship("http://schemas.openxmlformats.org/officeDocument/2006/relationships/mailMergeSource", new Uri(string.Format("file:///{0}", DataPath)));

            if (mailMerge.DataSourceReference == null)
            {
                mailMerge.DataSourceReference = new DataSourceReference();
            }

            mailMerge.DataSourceReference.Id = dsRelationship.Id;
            mailMerge.ViewMergedData.Val = true;
        }

Does any one solved this problem before?

EDIT

I added the relationship logic, but now word say, that it could not found C:\test.xlsx. But it exists at the correct place und C:\.

There is also the SQL-Statement missing:

EDIT 2

if i create the query like this:

mailMerge.Query = new Query()
{
    Val = "SELECT * FROM `Tabelle1$`"
};

The select-query will be embedded correctly in the docx.

But the error with the missing Test.xlsx still exists...

Answer 1

There are at least two problems:

1. The attributes are not correctly qualified. In the .docx, they appear as val="whatever", but they need to be w:val="whatever".

You should be able to fix that by using e.g.

string xmlnsw = @"http://schemas.openxmlformats.org/wordprocessingml/2006/main";

then changing all the SetAttribute statments so that you have, e.g.

mailMerge.MainDocumentType.SetAttribute(new DocumentFormat.OpenXml.OpenXmlAttribute("val", xmlnsw, "formatLetters"));

(I would guess there is a better way to code that that draws on existing constant definitions in the Open XML SDK).

2. You also need to (re-)create at least one of the Relationships specified in the w:mailMerge XML. (But you have already changed your code to reflect that).

Some additional comments...

As far as I can tell, your existing code only modifies one of the relationships. Since Microsoft's documentation suggests that Word never "uses" the "src" attribute of the odso element, that is probably OK. Word does, however, create and populate the src element, so I am not certain that it never "uses" it.

In fact, for this type of data source, I think you could probably remove the entire odso element and the associated relationship anyway. For a text type data source you might need the odso element if only to specify the field delimiter character.

Your code seems to be trying to remove the existing relationships. It did not do so here, but I have not tried to discover why. I do not think that will cause a problem except that the number of unreferenced relationships will increase every time you run this code against a given file.

My guess is that you could also either omit the ConnectString attribute or leave it empty (it depends on what the .docx schemas require). In recent versions of Word you only usually need to specify the file name and the query for Word to make a successful connection. If specifying the ConnectString actually causes Word to use the ConnectString (rather than generate its own), there could be a problem if, for example, you were opening with Word 2003 (which used the Jet provider rather than the ACE provider). It is interesting that Word itself creates and stores a COnnectString with an Engine Type number (37 in this case, I think) rather than a name ("Excel 12.0 Xml") in the Extended Properties.