Word Mailmerge using XLSx spreadsheet as data source

[david_foley_azyra_co]

Hi All
I am in the process of evaluating Aspose.Words and Cells version 16.11.0.0 for .Net 4.0 and was trying to get Word Mailmerge running using Excel as a datasource. I found the following code on one of the posts, but I am having a problem getting it to work. The final Doc2_Out.docx has not mailmerged the data. When I try to open it I get the attached message, Message.jpg.
Anyone any ideas what I am missing here.
Am I correct in saying the Save method of the Document Class will execute the Mailmerge to the specified new document.
I have attached the Word Document and Excel file I am using for the mailmerge. If I perform the mailmerge with these files, manually or via automation, using Microsoft word, the mailmerge works without any issues.
I put the following code in the click method of a windows forms C# sample application.

Document doc = new Document(@"C:\Temp\Doc2.docx");

string excelDS = @"c:\Temp\Book2.xlsx";

MailMergeSettings mms = doc.MailMergeSettings;
mms.DataSource = excelDS;
mms.MainDocumentType = MailMergeMainDocumentType.FormLetters;
mms.DataType = MailMergeDataType.Spreadsheet;
mms.LinkToQuery = true;
mms.ViewMergedData = true;
mms.Query = "SELECT * FROM " + mms.DataSource;

doc.Save(@"c:\Temp\Doc2_out.docx");

Any help would be greatly appreciated
Regards,
David

[tahir.manzoor]

Hi David,

Thanks for your inquiry. You can connect an Excel spreadsheet as an external data source to your input Word template document via the Dynamic Data Exchange (DDE) system by using the code something like below. Hope this helps you.

Document doc = new Document(MyDir + @"Doc2.docx");
string excelDS = @"c:\Temp\Book2.xlsx";
MailMergeSettings mms = doc.MailMergeSettings;
mms.DataSource = excelDS;
mms.MainDocumentType = MailMergeMainDocumentType.FormLetters;
mms.DataType = MailMergeDataType.Native;
mms.LinkToQuery = true;
mms.ViewMergedData = true;
mms.Query = "SELECT * FROM 'Sheet1$'";
doc.Save(MyDir + @"Out v16.11.0.docx");

[david_foley_azyra_co]

Hi Tahir
Thanks for your response.
I have amended the code as you suggest and I am still having the same problem with the final MailMerged output document, where the data from the spreadsheet has not been mailmerged into the document.
I am using the Word Document and Excel spreadsheet I supplied in the original post in this thread.
Any idea where I am going wrong.
My code looks as follows. I have also attached the final Output document that I am getting.

Document doc = new Document(@"C:\Temp\Doc2.docx");

string excelDS = @"c:\Temp\Book2.xlsx";

MailMergeSettings mms = doc.MailMergeSettings;
mms.DataSource = excelDS;
mms.MainDocumentType = MailMergeMainDocumentType.FormLetters;
mms.DataType = MailMergeDataType.Native;
mms.LinkToQuery = true;
mms.ViewMergedData = true;
mms.Query = "SELECT * FROM ‘Sheet1$’";

doc.Save(@"c:\Temp\Doc2_out.docx");

Could it be anything to do with using the evaluation version?
Regards,
David

[tahir.manzoor]

Hi David,

Thanks for your inquiry.
david_foley_azyra_com:
The final Doc2_Out.docx has not mailmerged the data. When I try to open it I get the attached message, Message.jpg.
Please note that Aspose.Words mimics the same behavior as MS Word does. If you perform the same scenario using MS Word, you will get the same output.

We have not found any issue with shared output “Doc2_out.docx”. Please check the attached image of output document. Could you please share some more detail about your issue? We will then provide you more information on this.

[david_foley_azyra_co]

Hi Tahir
I am just getting back to this issue again. Sorry for the long delay in replying but had to move to another project for a considerable length of time.
We have just received out Licences for the Aspose Total product so I am hoping to get this working asap.
What I am looking to achieve is a final Word document with the data mailmerged. The XLS data source will only ever have 1 record. The final Word document will have no connection to the data source, but is the output of merging the Original Mailmerge Document and the XLS Data Source.
I hope I have explained myself clearly.
At the moment Doc2_out.docx seems to be the Mailmerge document. When I open in word is still has a connection to the Mailmerge Data source and I have to click Mailings / Finish & Merge to view the merged data.
Hope this helps
Regards,
David

[tahir.manzoor]

Hi David,

Thanks for your inquiry. You are using Document.MailMergeSettings. The MailMergeSettings class specifies all of the mail merge information for a document.

Please make sure that “Book2.xlsx” exists in “c:\Temp” folder. Open the “Doc2_out.docx” in MS Word. You will get the message box as shared in “Message.jpg”. Press “Yes” botton to get the output.
david_foley_azyra_com:
At the moment Doc2_out.docx seems to be the Mailmerge document. When I open in word is still has a connection to the Mailmerge Data source and I have to click Mailings / Finish & Merge to view the merged data.
We suggest you following solution.

1. Please export data from worksheet to DataTable using Aspose.Cells
2. Perform the mail merge from a DataTable into the document using Aspose.Words.

Please refer to the following mail merge article.
How to Execute Mail Merge

Following code example shows how to perform mail merge using Aspose.Words.

Document doc = new Document(MyDir + "input.docx");
// Field values from the table are inserted into the mail merge fields found in the document.
// Export data to DataTable Using Aspose.Cells
doc.MailMerge.Execute(datatable);
doc.Save(MyDir + "MailMerge.ExecuteDataTable Out.docx");

[david_foley_azyra_co]

Hi Tahir
Thanks for your help. That did exactly what I was looking for and the mailmerge is now working ok.
I have just one small outstanding problem.
In the Datasource I have a DateTime fields which would never contain a time value. This Mailmerges to a mailmerge field in the document and the values merges as follows “12/01/2017 00:00:00”. I know I can update the Mailmerge field in the Word document to include formatting as follows @“dd/MM/YYYY”. I have tried this and it works fine. I was wondering if I can apply this extra formatting via code in Aspose.Words to any Mailmerge fields in the document where the Datasource column for the Mailmerge field is type DateTime before executing the Mailmerge in Aspose.Words.
I hope I have explained myself clearly.
Regards,
David

[tahir.manzoor]

Hi David,

Thanks for your inquiry. In your case, we suggest you please implement IFieldMergingCallback interface to achieve your requirements. Following code example shows how format date field inside IFieldMergingCallback.FieldMerging. Hope this helps you.

private class HandleDateField : IFieldMergingCallback
{
    /// 
    /// This is called when merge field is actually merged with data in the document.
    /// 
    void IFieldMergingCallback.FieldMerging(FieldMergingArgs e)
    {
        if (e.DocumentFieldName.Equals("datefieldname"))
        {
            // Insert the date using DocumentBuilder.
            DocumentBuilder builder = new DocumentBuilder(e.Document);
            builder.MoveToMergeField(e.DocumentFieldName);
            builder.Write(Convert.ToDateTime(e.FieldValue).ToString("MMMM dd, yyyy"));
            // We have already inserted the field's value
            e.Text = "";
        }
    }
    void IFieldMergingCallback.ImageFieldMerging(ImageFieldMergingArgs e)
    {
        // Do nothing.
    }
}

Document doc = new Document(MyDir + "in.docx");
// Add a handler for the MergeField event.
doc.MailMerge.FieldMergingCallback = new HandleDateField();
doc.MailMerge.Execute(datatable);

[david_foley_azyra_co]

Hi Tahir
Thanks for your prompt reply. That worked perfectly. All good to go now.
Regards.
David

[tahir.manzoor]

Hi David,

Thanks for your feedback. Please feel free to ask if you have any question about Aspose.Words, we will be happy to help you.