Mailmerge and MS Word mergefields with custom formatting and IF statements

jens.churchill · March 4, 2010, 4:09am

When MailMerging fields with custom formatting, and CultureInfo settings that include
Culture.NumberFormat.Number/CurrencyDecimalSeparator = ‘,’ (Comma)
Culture.NumberFormat.Number/CurrencyGroupSeparator = ‘.’ (Dot)
A MergeField such as

{ MERGEFIELD "fieldName1" \# "#.##0,00" }

where fieldName1 = 0, a MailMerge produces a result of ,00000 where 0,00 was expected.
A workaround is to pre-process the template, and replace the formatting.

if (fieldCode.Text.IndexOf("#.#")> 0)
{
    fieldCode.Text = fieldCode.Text.Replace("#.#", "#,#");
}
if (fieldCode.Text.IndexOf("0,0")> 0)
{
    fieldCode.Text = fieldCode.Text.Replace("0,0", "0.0");
}

This correctly produces a result of 0,00 after the mailmerge.

-

Now if we add an IF statement to this, such as

{ IF { MERGEFIELD fieldName1 } <> 0 "{ MERGEFIELD "fieldName1" \# "#.##0,00" }" "{MERGEFIELD "fieldName2" \# "#.##0,00" }" }

Where fieldName1 = 0 and fieldName2 = 500,00

The result is an “un-flattened” Mergefield with a value of

{ IF 0 <> 0 "0,00" "500,00" }

where a “flattened” value of 500,00 was expected.

-

Finally, if the merge adds arithmetic and complexity, it breaks. ie.:

{ ={ MERGEFIELD "fieldName1" }+{ MERGEFIELD "fieldName2" }+{ IF { MERGEFIELD fieldName3 } <> 0 "{MERGEFIELD "fieldName3" }" "{ MERGEFIELD "fieldName4" }" } \# "#.##0,00" }

Where FieldName1 = 1.000,00, FieldName2 = 500,00, FieldName3 = 0 and FieldName4 = 500,00
Produces a result of

{ =1.000,00+500,00+{ IF 0 <> 0 "0" "500,00" } \# "#.##0,00" }

Which, when printed produces

!Undefined Bookmark, 1.000,00

While stripping the Grouping char from the datasource, does produce a printable result.
Such a solution would however be unacceptable, as not all the templates define custom formatting rules, and sometimes merely reference the fieldName value as text.

Also, this solution produces 2000,00000 where 2.000,00 would be expected.

In short, how/when will we be able to inform Aspose (.Words) of which CultureInfo to use, so these “problems” can be avoided?

Respectfully, Jens Churchill.

alexey.noskov · March 4, 2010, 10:27am

Hi

Thanks for your inquiry.

Aspose.Words requires picture format switches in merge fields to be in the US (InvariantCulture) format. So, please try using the following switch.

{ MERGEFIELD test \# #,##0.00 }
Here is code I used for testing:

CultureInfo currentCulture = Thread.CurrentThread.CurrentCulture;
// Set gerna culture. Number format in Germany is 123.456.789,00
// Exactly as you need.
Thread.CurrentThread.CurrentCulture = new CultureInfo("de-DE");
Document doc = new Document(@"Test001\in.doc");
doc.MailMerge.Execute(new string[]
{
    "fieldName1"
}, new object[]
{
    0
});
doc.Save(@"Test001\out.doc");
Thread.CurrentThread.CurrentCulture = currentCulture;

In the output document you will see the expected value 0,00.

Use the following code for testing and IF filed is evaluated properly:

CultureInfo currentCulture = Thread.CurrentThread.CurrentCulture;
// Set gerna culture. Number format in Germany is 123.456.789,00
// Exactly as you need.
Thread.CurrentThread.CurrentCulture = new CultureInfo("de-DE");
Document doc = new Document(@"Test001\in.doc");
doc.MailMerge.Execute(new string[]
{
    "fieldName1",
    "fieldName2"
}, new object[]
{
    0,
    500
});
doc.Save(@"Test001\out.doc");
Thread.CurrentThread.CurrentCulture = currentCulture;

Here is field code in my template:
{ IF { MERGEFIELD fieldName1 } <> 0 "{ MERGEFIELD "fieldName1" \# "#,##0.00" }" "{ MERGEFIELD "fieldName2" \# "#,##0.00" }" }

Also cannot reproduce the problem. Most likely, you put numbers as string into Execute method. I used the following code for testing:

CultureInfo currentCulture = Thread.CurrentThread.CurrentCulture;
// Set gerna culture. Number format in Germany is 123.456.789,00
// Exactly as you need.
Thread.CurrentThread.CurrentCulture = new CultureInfo("de-DE");
Document doc = new Document(@"Test001\in.doc");
doc.MailMerge.Execute(new string[]
{
    "fieldName1",
    "fieldName2",
    "fieldName3",
    "fieldName4"
}, new object[]
{
    1000,
    500,
    0,
    500
});
doc.Save(@"Test001\out.doc");
Thread.CurrentThread.CurrentCulture = currentCulture;

The output document shows 2.000,00 as expected. Here is field code:
{ ={ MERGEFIELD "fieldName1" }+{ MERGEFIELD "fieldName2" }+{ IF { MERGEFIELD fieldName3 } <> 0 "{ MERGEFIELD "fieldName3" }" "{ MERGEFIELD "fieldName4" }" } \# "#,##0.00" }

Best regards.

jens.churchill · March 5, 2010, 4:29am

Dear Sir,

I realize that Aspose currently requires a US style format, eg. #,##0.00; This is why I try to pre-process the template in code. I do not have control or access to the actual template (.dot) file itself, and cannot edit it manually.

I consider this a hack, as ideally, Aspose should handle a data sources field values and format codes in the template according to the CultureInfo of the thread in which it is running (eg. CurrentCulture).

Secondly, in my case the data source and template are third party provided via a .csv file and .dot template. Here the data source is handed over as pre-formatted text strings. This is because the templates often reference the data directly, without any formatting applied eg. { MERGEFIELD fieldName } should still show 1.000,00; However people still want the ability of evaluating the field, eg. { = {MERGEFIELD "fieldName" + 500} \# #.##0,00 } = 1.500,00.

This is not a problem in word itself, it correctly parses the internal value of the field according to the international settings of the PC on which it is running, Aspose it appears does not.

So, could you please help to propose a solution that allows none US formatted text values such as “1.234,56” to be handled in templates which are none US formatted, and of which neither data source nor template is editable before processing, that is, is end-user created and provided?

I have created a test case to illustrate the scenario and attached it, I am sure you can grasp the gist of it, however an example never hurts; You are more than welcome to edit it however you see fit.
Only 2 rules:

You can’t manually edit the data source or template
The result should match the supplied MS Word mail merged equalivant, Result.doc.

Respectfully, humbly.
Jens Churchill.

alexey.noskov · March 5, 2010, 7:33am

Hi

Thank you for additional information. You can try using the following code as workaround:

public void Start()
{
    DataTable Data = Parse("data.csv");
    CultureInfo currentCulture = Thread.CurrentThread.CurrentCulture;
    // Set gerna culture. Number format in Germany is 123.456.789,00
    // Exactly as you need.
    Thread.CurrentThread.CurrentCulture = new CultureInfo("de-DE");
    Document Template = new Document("template.dot");
    PreProcess(Template);
    Template.MailMerge.MergeField += new Aspose.Words.Reporting.MergeFieldEventHandler(MailMerge_MergeField);
    Template.MailMerge.Execute(Data);
    Template.UpdateFields();
    Template.Save("Output.doc", SaveFormat.Doc);
    Thread.CurrentThread.CurrentCulture = currentCulture;
}
void MailMerge_MergeField(object sender, Aspose.Words.Reporting.MergeFieldEventArgs e)
{
    double value;
    if (double.TryParse(e.FieldValue.ToString(), out value))
        e.Text = value.ToString();
}

I highlighted code, which I added. In this case strings like “1.234,56” will be considered as doubles and formulas are calculated properly.
But anyways, picture switches of IF and formula fields should be in InvarianCulture format.
Hope this helps.
Best regards.

jens.churchill · March 6, 2010, 3:54am

Thank you for your reply sir,

Unfortunately, this really doesn’t help the situation.

Parsing the value, and replacing the e.Text property manually solves the !Undefined bookmark problem to an extend. The output format is still wrong because of the none-invariant format switch; which I simply cannot expect to be invariant; These templates are supplied from none US countries, where they are used within MS word itself (and work as expected).

However worse still is the fact that using your proposed solution, simple mailmerge fields now loose the formatting the original csv file provided.

See the very first example line in the previously attached template file. The supplied data source value of 1.234,56 should end up as 1.234,56 (No format switch is provided in this field). Your proposed workaround displays it as 1234,56 (No grouping).

The only real solution as I see it, is that your field evaluation respects culture, instead of using invariant as a base for all operation.

I remain sir,
Respectfully
Jens Churchill.

alexey.noskov · March 6, 2010, 4:48am

Hi

Thank you for your suggestion. We will consider such option. Your request has been linked to the appropriate issue. You will be notified as soon as it is resolved.
Nest regards.

jens.churchill · March 17, 2010, 2:42am

> Your request has been linked to the appropriate issue.

Can I track the progress of this “appropriate issue” somewhere?

alexey.noskov · March 17, 2010, 2:58am

Hi

Thanks for your request. Once there is some progress or issue is resolved, we will let you know in this forum thread.
Best regards.

aspose.notifier · April 7, 2010, 2:38am

The issues you have found earlier (filed as 14916) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.

alexey.noskov · March 6, 2024, 12:37pm

A post was split to a new topic: MERGEFIELD number formatting