Struggling with numeric formatting / picture switch processing in merge fields with locale settings

Aspose.Words Product Family
1

Hello,

I’ve had a lot of European users that have not reported any problem in formatting currency in Word merge fields using picture switches, until yesterday. My user is located in Denmark and the currency value he’s trying to display in a Word merge is in kroners.

The problem is that it seems like Aspose.Words is ignoring the decimal place in the field. For example, the merge field has the value “69025.0”, but the output after merging is “690250,00”.

Here’s my code and the template. Thanks so much for taking a look.

protected void Button1_Click(object sender, EventArgs e)
{
    // 2007-09-03: This sets the users locale information… critical for proper number formatting by Aspose
    string UserLocale = "da-DK";
    System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(UserLocale);

    // Sample Data
    DataTable dt = new DataTable("Main");
    dt.Columns.Add(new DataColumn("OPPORTUNITY\_AMOUNT", Type.GetType("System.String")));
    DataRow dr = dt.NewRow();
    dr["OPPORTUNITY\_AMOUNT"] = "69025.0";
    dt.Rows.Add(dr);

    // Use Aspose to grab the mail merge template
    string templateFileName = Server.MapPath(@"App\_Data\Thor.doc");

    // Perform the merge. Notice the numeric formatting on the second field
    Document doc = new Document(templateFileName);
    doc.MailMerge.Execute(dt);
    doc.Save(""" + "output.doc" + """, SaveFormat.Doc, Aspose.Words.SaveType.OpenInWord, Response);
}

Here are the values displayed in the output Word document:

Unformatted field value:
69025.0

Formatted field value:
kr 690.250,00

2

Here’s the template.

3

Hi

Thanks for your request. I managed to reproduce this problem. It seems that this occurs because you use String value. I tried to use decimal and all works fine on my side. I modified your code.

// 2007-09-03: This sets the users locale information… critical for proper number formatting by Aspose
string UserLocale = "da - DK";
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(UserLocale);
// Sample Data
DataTable dt = new DataTable("Main");
dt.Columns.Add(new DataColumn("OPPORTUNITY_AMOUNT", typeof(decimal)));
DataRow dr = dt.NewRow();
dr["OPPORTUNITY_AMOUNT"] = 69025.0;
dt.Rows.Add(dr);
// Use Aspose to grab the mail merge template
string templateFileName = @"294_99936_DenverMike\in.doc";
// Perform the merge. Notice the numeric formatting on the second field
Document doc = new Document(templateFileName);
doc.MailMerge.Execute(dt);
doc.Save(@"294_99936_DenverMike\out.doc");

I hope that this twill help you.

Best regards.

4

Thank you, Alexey.

That would be such a simple solution for this… It’s a little more complicated than that because the data table is handed to me by another library, so I don’t have a lot of control over the data type ahead of time. Perhaps I might use a Merge Event Handler to see if the merge field has a numeric picture switch, then change the datatype at the moment the merge is performed.

Thanks again for your help… You and all of the Aspose team always provide such excellent service.

5

Hi again,

This problem occurs because da-DK culture uses the decimal format as 123.456.789,00.

You can try to solve this problem using the following code.

Document doc = new Document(templateFileName);
doc.MailMerge.MergeField += new MergeFieldEventHandler(MailMerge\_MergeField\_99936);
doc.MailMerge.Execute(dt);
doc.Save(@"294\_99936\_DenverMike\out.doc");

void MailMerge_MergeField_99936(object sender, MergeFieldEventArgs e)
{
    if (e.Field.GetFieldCode().Contains("kr #,##0.00"))
    {
        decimal test = Convert.ToDecimal(e.FieldValue.ToString().Replace(".", ","));
        e.Text = test.ToString("kr #,##0.00");
    }
}

I hope that it will help you.

Best regards.

6

Thanks so much for putting this together for me! It’s perfect!

7

Hi Alexey,

One more quick question on the Merge Event Handler. I’m actually thinking of something more generic than what you proposed. For example:

if (e.Field.GetFieldCode().Contains(@" # "))
{
    try
    {
        double convertedValue = Convert.ToDouble(e.FieldValue);

        //DocumentBuilder builder = new DocumentBuilder(e.Document);
        //builder.MoveToMergeField(e.DocumentFieldName);
        //builder.in(convertedValue);
        //e.Text = ??

        // How can I set the FieldValue? Must I use the DocumentBuilder?
        //e.FieldValue = convertedValue; // This won’t work… FieldValue is read-only
    }
    catch
    {
        // If the conversion fails, just leave the field value as it was
    }
}

The question I have, is how best to set the FieldValue?

8

Nevermind… this seems to work fine:

if (e.Field.GetFieldCode().Contains(@" # "))
{
    try
    {
        decimal convertedValue = Convert.ToDecimal(e.FieldValue.ToString().Replace(".", ","));
        e.Text = convertedValue.ToString();
    }
    catch
    {
        // If the conversion fails, just leave the field value as it was
    }
}
9

Hi

Yes, It seems that this works fine, but you get incorrect result in your document. You should get 69.025,00 but you get 69025,0. Switch is ignored.

Best regards.

10

I’ve improved the code a lot and now I get the correct results.

Another question is, has something changed in Words related to the processing of merge field picture formats? I ask, because I’m pretty sure that numeric formatting has been working well for the last two months. I was suprised to hear one of my users telling me that this wasn’t working anymore.

Previously, by utilizing the CultureInfo object, Aspose.Words took care of everything else. I never tested it with a user from Denmark before, though. I doubt that has anything to do with it, but I suppose it’s possible.

11

Hi

No, nothing has changed in Aspose.Words related to the processing of merge field picture formats. As I said earlier this occurs because Denmark culture uses the decimal format as 123.456.789,00. That’s why 123.456 = 123456 and 123.456 != 123,456.

Best regards.