How to get the final formatted value of a merge field after the dcoument merge

Aspose.Words Product Family
1

Hi there,

We have a Word document, with a merge field “Amount” in multiple locations, each of these amount field has different merge format, as shown below:

MERGEFIELD Amount \# #,##0; (#,##0.00) * MERGEFORMAT

MERGEFIELD Amount \# $#,##0.00; (#,##0.00) * MERGEFORMAT

The raw database value for the Amount merge field is 5000. However, the final formatted value that will be merged in the document will be based on the merge format, of each of these amount fields. We will need this final formatted value (e.g.$5,000.00) for our tracking purpose. Wondering, how do we get the final formatted amount value for each of these amount fields, after the merge. Please let us know.

PS: Tried using IFieldMergingCallback, but that has only the raw field value of 5000 and the field format (e.g# #,##0)., and couldn’t get the final formatted value.

Thank you in advance for your help!

Best Regards,

-BP

2

Hi there,

Thanks for your inquiry. In your case, we suggest you please implement IFieldMergingCallback interface and format the field’s value using .NET API.

If you still face problem, please share your input and expected output documents here for our reference. We will then provide you more information on this along with code.

3

Thank you Tahir, for your quick response. That helps. Just a couple of follow up questions:

  1. Does Aspose.Words use .NET API for data formatting during the merge? We wanted to make sure that the .NET API formatted data matches the Aspose.Words formatted data in the Word document. Please let us know.

  2. Wondering, if we could get the final formatted value, by adding starting and ending bookmarks around each merge field during the merge process by implementing IFieldMergingCallback interface. After the merge, can we loop thru the book marks of the merged document, and extract the contents between the bookmarks, expecting that would be the final formatted value. Let us know, if you have any suggestions or sample code to achieve this.

Will appreciate your help, since it is crucial for our business to accurately track the final formatted data that is merged onto the word document.

Thank you.

Best Regards,

-BP

4

Hi there,

Thanks for your inquiry.

bprabu:

1. Does Aspose.Words use .NET API for data formatting during the merge? We wanted to make sure that the .NET API formatted data matches the Aspose.Words formatted data in the Word document.

Please note that Aspose.Words mimics the same behavior as MS Word does. The formatted data will be according to number formatting mentioned in field’s code e.g. #,##0; (#,##0.00). We suggest you please use number formatting of mail merge field instead of formatting it in code.

Could you please share complete detail of your use case? We will then provide you more information on this.

bprabu:

2. Wondering, if we could get the final formatted value, by adding starting and ending bookmarks around each merge field during the merge process by implementing IFieldMergingCallback interface. After the merge, can we loop thru the book marks of the merged document, and extract the contents between the bookmarks, expecting that would be the final formatted value

In this case, you need to move the cursor to the field and insert bookmark and formatted value. Please check the following code snippet.

void IFieldMergingCallback.FieldMerging(FieldMergingArgs e)
{
    DocumentBuilder builder = new DocumentBuilder(e.Document);
    builder.MoveToMergeField(e.DocumentFieldName);
    builder.StartBookmark("bookmark name...");
    //Get the field's value using e.FieldValue
    builder.Write(" ... The formatted field's value according to your requirement");
    builder.EndBookmark("bookmark name...");
    e.Text = "";
}
5

Hi Tahir,

Totally appreciate your prompt response to our inquiry. Here’s what we are trying to achieve.

Sample Word Template, with just one merge field Amount, with field code format, as shown below:

Based on an underwriter’s review, I’m pleased to approve your Loan for

{MERGEFIELD Amount \# $#,##0.00;(#,##0.00)* MERGEFORMAT}

Sincerely,

Mr. Smith

Below method, will generate a pdf document, of the above word template, displaying the amount value as $5,000.00 in the document, though we passed a raw value of 5000 for amount.

public void GetFormattedMergedFieldValue()
{
    Document doc = new Document(dataDir + "WordTemplate.docx");
    doc.MailMerge.Execute(new string[] { "Amount" }, new object[] { "5000" });
    doc.Save(dataDir + "\\Output\\Output.pdf");
}

In the above example, we would want to get the formatted value $5,000.00 from this document for our tracking needs.

How do we extract $5,000.00 automatically, either during the merge or after the merge.

Either during the merge or after the merge, we want to get the final formatted merge field values from the document, and write that out in our database for internal tracking purpose. We don’t want to do the formatting manually, using the raw value and the field code format, since we are afraid that the Aspose formatted value may be different from our manual formatting, which will cause inconsistency. How do we achieve this?

Please let us know.

Hope this clarifies. Let me know, if you will need any other details. Thank you.

6

Hi there,

Thanks for your inquiry. Please use the following code example to achieve your requirement. Hope this helps you. We have attached the input and output documents with this post for your kind reference.

Document doc = new Document(MyDir + "in.docx");
doc.MailMerge.FieldMergingCallback = new FormatMergeField();
doc.MailMerge.Execute(new string[] { "Amount" }, new object[] { "5000" });
Console.WriteLine(doc.Range.Bookmarks["bookmark"].Text);
doc.Save(MyDir + "17.3.0.docx");

private class FormatMergeField : IFieldMergingCallback
{
    /// 
    /// This is called when merge field is actually merged with data in the document.
    /// 
    void IFieldMergingCallback.FieldMerging(FieldMergingArgs e)
    {
        if (e.DocumentFieldName.Equals("Amount"))
        {
            DocumentBuilder builder = new DocumentBuilder(e.Document);
            builder.MoveToMergeField(e.DocumentFieldName, false, false);
            builder.StartBookmark("bookmark");
            builder.MoveToMergeField(e.DocumentFieldName, true, false);
            builder.EndBookmark("bookmark");
        }
    }

    void IFieldMergingCallback.ImageFieldMerging(ImageFieldMergingArgs e)
    {
        // Do nothing.
    }
}
7

Hi Tahir,
This code snippet resolved our problem. Your help is very much appreciated.

Thank you again.

Best Regards,
-BP

8

Hi there,

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

9

Hi Tahir,

Our word templates will have the same merge field in multiple locations, in different field code format, as shown below. In this scenario, we expect the bookmarks to be created for both the Amount merge field locations, to be able to extract the final formatted values of 5,000 and $5,000, and save it in our database for tracking purpose. However, with the above code snippet, builder moves only to the first Amount merge field location in the IFieldMergingCallback and only one bookmark is created and thereby getting only the final formatted value of 5,000, when we loop thru the bookmarks, after the merge. We are not getting both the Amount values of 5,000 and $5,000.

Is there an alternate way to move the builder to a unique field ID of the merge field, instead of moving to the merge field name to cater for duplicate merge field names with different formats.Please advise.

MERGEFIELD Amount \# #,##0; (#,##0.00) * MERGEFORMAT

MERGEFIELD Amount \# $#,##0.00; (#,##0.00) * MERGEFORMAT

Code Snippet:

public void GetFormattedMergedFieldValue()
{
    Document doc = new Document(dataDir + "WordTemplate.docx");
    doc.MailMerge.FieldMergingCallback = new FormatMergeField();
    doc.MailMerge.Execute(new string[] { "Amount" }, new object[] { "5000" });
    foreach (Bookmark bm in doc.Range.Bookmarks)
    {
        Console.WriteLine(bm.Text);
    }
    doc.Save(dataDir + "\Output\Output.pdf");
}

private class FormatMergeField : IFieldMergingCallback
{
    string temp;
    void IFieldMergingCallback.FieldMerging(FieldMergingArgs e)
    {
        if (e.Field.Type.Equals(FieldType.FieldMergeField))
        {
            DocumentBuilder builder = new DocumentBuilder(e.Document);
            builder.MoveToMergeField(e.DocumentFieldName, false, false);
            builder.StartBookmark("bookmark" + e.DocumentFieldName);
            builder.MoveToMergeField(e.DocumentFieldName, true, false);
            builder.EndBookmark("bookmark" + e.DocumentFieldName);
        }
    }

    void IFieldMergingCallback.ImageFieldMerging(ImageFieldMergingArgs e)
    {
        // Do nothing.
    }
}
10

Hi there,

Thanks for your inquiry. Please note that Aspose.Words mimics the same behavior as MS Word does. MS Word allows only unique names for bookmarks. If you change the name of a bookmark to a name that already exists in the document, no error will be given and only the first bookmark will be stored when you save the document.

In this case, we suggest you please insert the bookmarks with name e.g. bookmark1, bookmark2 etc. Please check the following modified code of FormatMergeField class. Hope this helps you.

private class FormatMergeField : IFieldMergingCallback
{
    int i = 1;

    /// 
    /// This is called when merge field is actually merged with data in the document.
    /// 
    void IFieldMergingCallback.FieldMerging(FieldMergingArgs e)
    {
        if (e.DocumentFieldName.Equals("Amount"))
        {
            DocumentBuilder builder = new DocumentBuilder(e.Document);
            builder.MoveToMergeField(e.DocumentFieldName, false, false);
            builder.StartBookmark("Amount" + i);
            builder.MoveToMergeField(e.DocumentFieldName, true, false);
            builder.EndBookmark("Amount" + i);
            i++;
        }
    }

    void IFieldMergingCallback.ImageFieldMerging(ImageFieldMergingArgs e)
    {
        // Do nothing.
    }
}
11

Thank you again Tahir, for your prompt response. This code snippet of adding unique bookmarks to duplicate merge fields, fixed our problem. Totally appreciate your help!

12

Hi there,

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