Hello team, I have code running like below since 2007. Changing the culture of the thread to Invariant then was the way to go to have a consistent culture and working numeric formats on each machine. The goal was to always have numeric format having a comma as decimal separator. The worked by setting the NumberDecimalSeparator. Users always use the format "= 20000 \# 0.00" which results in 20000,00.
This format is therefore embedded in a great number of user-defined word layouts, so I have to be careful not to change that behaviour.
The problem now is that I need to make the thousands separator visible, like 20.000,00
I cannot seem to get that to work without changing the culture to dutch, for example. If I would do that I think all existing layouts (that use "= 20000 \# 0.00") will not work consistently anymore.
With my invariantculture with NumberDecimalSeparator = ","
"= 20000 \# #.##0,00" gives this result “20000, 0.00”
CultureInfo currentCulture = Thread.CurrentThread.CurrentCulture;
CultureInfo tempCulture = (CultureInfo)CultureInfo.InvariantCulture.Clone();
tempCulture.NumberFormat.NumberDecimalSeparator = ",";
Thread.CurrentThread.CurrentCulture = tempCulture;
Document doc = new Document();
DocumentBuilder builder = new DocumentBuilder(doc);
{
Field field = builder.InsertField(@"= 20000 \# 0.00");
field.Result.Dump(); // result is 20000,00
}
{
Field field = builder.InsertField(@"= 20000 \# #.##0,00");
field.Result.Dump(); // result is 20000, 0.00
}
Thread.CurrentThread.CurrentCulture = currentCulture;
Would you have some advice for this? Setting the currencygroupseparator to ‘.’ does not have a good result.
I am using version 19.9.
Thanks, Ed
@EdAspose In your case you should also specify NumberFormat.NumberGroupSeparator to dot. But you should not that in this case dot in this format string \# 0.00 will be considered as group separator, and not as decimal separator. So it is required to change the format string to this: \# 0,00:
CultureInfo currentCulture = Thread.CurrentThread.CurrentCulture;
CultureInfo tempCulture = (CultureInfo)CultureInfo.InvariantCulture.Clone();
tempCulture.NumberFormat.NumberDecimalSeparator = ",";
tempCulture.NumberFormat.NumberGroupSeparator = ".";
Thread.CurrentThread.CurrentCulture = tempCulture;
Document doc = new Document();
DocumentBuilder builder = new DocumentBuilder(doc);
Field field1 = builder.InsertField(@"= 20000 \# 0,00");
Console.WriteLine(field1.Result); // result is 20000,00
builder.Writeln();
Field field2 = builder.InsertField(@"= 20000 \# #.##0,00");
Console.WriteLine(field2.Result); // result is 20.000,00
Thread.CurrentThread.CurrentCulture = currentCulture;
@alexey.noskov thank you. So there is not a way around that. I would love to change the picture in all existing layout documents, but that is a no go i for my case I think, because the layouts can be stored in different places.
I guess the only way then for me is to use a handler based on IFieldMergingCallback that replaces “# 0.00” with “# 0,00” so it is dynamically changed. Would that (IFieldMergingCallback ) be the only way that makes that possible?
@EdAspose You can preprocess the document before executing mail merge. For example see the following code:
CultureInfo currentCulture = Thread.CurrentThread.CurrentCulture;
CultureInfo tempCulture = (CultureInfo)CultureInfo.InvariantCulture.Clone();
tempCulture.NumberFormat.NumberDecimalSeparator = ",";
tempCulture.NumberFormat.NumberGroupSeparator = ".";
Thread.CurrentThread.CurrentCulture = tempCulture;
Document doc = new Document(@"C:\Temp\in.docx");
// Get all mergefields in the document.
List<FieldMergeField> mergeFields = doc.Range.Fields.Where(f => f.Type == FieldType.FieldMergeField)
.Cast<FieldMergeField>().ToList();
// Chnage number format of the mergefields.
foreach (FieldMergeField f in mergeFields)
{
if (f.Format.NumericFormat == "0.00")
f.Format.NumericFormat = "0,00";
if (f.Format.NumericFormat == "#,##0.00")
f.Format.NumericFormat = "#.##0,00";
}
doc.MailMerge.Execute(new string[] { "test" }, new object[] { 20000d });
doc.Save(@"C:\Temp\out.docx");
Thread.CurrentThread.CurrentCulture = currentCulture;
Of change format during executing mail merge in IFieldMergingCallback :
private class MyFieldMergingCallback : IFieldMergingCallback
{
public void FieldMerging(FieldMergingArgs args)
{
FieldMergeField mf = (FieldMergeField)args.Field;
if (mf.Format.NumericFormat == "0.00")
mf.Format.NumericFormat = "0,00";
if (mf.Format.NumericFormat == "#,##0.00")
mf.Format.NumericFormat = "#.##0,00";
}
public void ImageFieldMerging(ImageFieldMergingArgs args)
{
}
}
1 Like
Thank you for the useful samples!
I will use the first method (preprocessing) then.
1 Like
I have implemented it and it works great.
In my system I also support layouts for the linq reporting engine. So that also runs under the tempCulture setting.
I now notice that that seems to work different; see the sample below.
If I use the comma as decimal and point as thousand separator, the result is not the same.
The first two should give the right result; however, the last two are right.
Do you know why this is, and if there is a way to make the first two work the same like the mailmerge format?
public void Test3()
{
CultureInfo currentCulture = Thread.CurrentThread.CurrentCulture;
CultureInfo tempCulture = (CultureInfo)CultureInfo.InvariantCulture.Clone();
tempCulture.NumberFormat.NumberDecimalSeparator = ",";
tempCulture.NumberFormat.NumberGroupSeparator = ".";
Thread.CurrentThread.CurrentCulture = tempCulture;
Document doc = new Document();
DocumentBuilder builder = new DocumentBuilder(doc);
builder.Writeln(@"<<[Edsfield]:""0,00"">>"); // result is 20.000
builder.Writeln(@"<<[Edsfield]:""#.##0,00"">>"); // result is 20000,00000
builder.Writeln(@"<<[Edsfield]:""0.00"">>"); // result is 20000,00
builder.Writeln(@"<<[Edsfield]:""#,##0.00"">>"); // result is 20.000,00
var data = new TestData { Edsfield = 20000d };
ReportingEngine engine = new ReportingEngine();
engine.BuildReport(doc, data);
doc.Save(@"C:\Temp\outlinq.docx");
Thread.CurrentThread.CurrentCulture = currentCulture;
}
public class TestData
{
public double Edsfield { get; set; }
}
@EdAspose Thank you for reporting the problem to us. I have logged an issue WORDSNET-24802 about number formatting inconsistency in LINQ Reporting Engine and in Mail Merge. Our development team will further investigate the issue and determine in which module the problem should be resolved.
For now the different number formats should be used in LINQ Reporting Engine and in Mail Merge templates.
Aha, that is a pity. But good to know and thank you for confirming.
1 Like
@EdAspose
It is to inform you that WORDSNET-24802 is closed as Not a Bug after analysis.
As pointed out by @alexey.noskov, format strings for MS Word merge fields are locale-sensitive. However, LINQ Reporting Engine uses the same format strings as would be passed to IFormattable.ToString method implementors (see Outputting Expression Results for details). The latter format strings are locale-insensitive as per the official reference. Hence, when using mail merge and LINQ Reporting Engine, different format strings should be applied in your case.
1 Like
The issues you have found earlier (filed as WORDSNET-24802) have been fixed in this Aspose.Words for .NET 23.2 update also available on NuGet.