I am having an issue when formatting number values in nested if and formula fields with format switches in the fields. If a formula field is calculated out of two mergefields and the result is formatted with a format switch (#) the result is not formatted according to the format switch.
In our environment we use document templates for 4 languages. Every template has special formatting in the Merge-, IF- and Formulafields. User is working on machine with culture “de-CH”. The template shall be formatted in culture “en-US”. Meaning numbers should be formatted like “#,##0.00”.
I could achieve this when I would change the current thread to culture “en-US” but this is not a feasible way because at the time of merging we don’t exactly know which language the document has. Therefore I set FieldUpdateCultureSource to FieldCode but it does not work.
I also tried to set FieldOptions to UseInvariantCultureNumberFormat but it did not help either.
Last chance I handled the formatting of number values in IFieldMergingCallback.FieldMerging method.
Like this:
if (args.FieldValue != null && args.FieldValue is DateTime && !args.Field.GetFieldCode().Contains(@"\@"))
{
args.Text = ((DateTime)args.FieldValue).ToString("dd.MM.yyyy");
}
else if (args.FieldValue != null && args.FieldValue is decimal && args.Field.Format != null && !string.IsNullOrEmpty(args.Field.Format.NumericFormat))
{
args.Text = ((decimal)args.FieldValue).ToString(args.Field.Format.NumericFormat, CultureInfo.InvariantCulture);
}
The problem now is that a Formulafield obviously does not run through IFieldMergingCallback.FieldMerging method. The Formulafield looks like this: { = { MERGEFIELD Field1 } - { MERGEFIELD Field2 } \# "#,##0.00" }
If I got Fields directly formatted they work. { MERGEFIELD Field1 \# "#,##0,00" } this works.
I provided you a zip file with a small console application and the Word template. The merged document will be created in the same directory as the template is.
I am having an issue when formatting number values in nested if and formula fields with format switches in the fields. If a formula field is calculated out of two mergefields and the result is formatted with a format switch (#) the result is not formatted according to the format switch.
In our environment we use document templates for 4 languages. Every template has special formatting in the Merge-, IF- and Formulafields. User is working on machine with culture “de-CH”. The template shall be formatted in culture “en-US”. Meaning numbers should be formatted like “#,##0.00”.
I could achieve this when I would change the current thread to culture “en-US” but this is not a feasible way because at the time of merging we don’t exactly know which language the document has. Therefore I set FieldUpdateCultureSource to FieldCode but it does not work.
I also tried to set FieldOptions to UseInvariantCultureNumberFormat but it did not help either.
Last chance I handled the formatting of number values in IFieldMergingCallback.FieldMerging method.
Like this:
if (args.FieldValue != null && args.FieldValue is DateTime && !args.Field.GetFieldCode().Contains(@"\@"))
{
args.Text = ((DateTime)args.FieldValue).ToString("dd.MM.yyyy");
}
else if (args.FieldValue != null && args.FieldValue is decimal && args.Field.Format != null && !string.IsNullOrEmpty(args.Field.Format.NumericFormat))
{
args.Text = ((decimal)args.FieldValue).ToString(args.Field.Format.NumericFormat, CultureInfo.InvariantCulture);
}
The problem now is that a Formulafield obviously does not run through IFieldMergingCallback.FieldMerging method. The Formulafield looks like this: { = { MERGEFIELD Field1 } - { MERGEFIELD Field2 } \# "#,##0.00" }
If I got Fields directly formatted they work. { MERGEFIELD Field1 \# "#,##0,00" } this works.
I provided you a zip file with a small console application and the Word template. The merged document will be created in the same directory as the template is.
@dsiB70 Aspose.Words uses the current’s thread culture to format values of formula fields. I think, in your case, you can take the default document’s locale and use it as the thread’s culture:
Document doc = new Document(@"C:\Temp\in.docx");
// Use the default document culture
Thread.CurrentThread.CurrentCulture = new CultureInfo(doc.Styles.DefaultFont.LocaleId);
doc.FieldOptions.FieldUpdateCultureSource = FieldUpdateCultureSource.FieldCode;
DataTable dataTable = new DataTable();
dataTable.Columns.Add("Wertfeld100", typeof(decimal));
dataTable.Columns.Add("Wertfeld101", typeof(decimal));
dataTable.Columns.Add("Wertfeld102", typeof(decimal));
dataTable.Columns.Add("Wertfeld104", typeof(decimal));
dataTable.Columns.Add("Wertfeld106", typeof(decimal));
DataRow row = dataTable.NewRow();
row[0] = new decimal(54);
row[1] = new decimal(1249.50);
row[2] = new decimal(4114.80);
row[3] = new decimal(624.75);
row[4] = new decimal(0);
dataTable.Rows.Add(row);
doc.MailMerge.CleanupOptions = MailMergeCleanupOptions.RemoveUnusedFields | MailMergeCleanupOptions.RemoveEmptyParagraphs |
MailMergeCleanupOptions.RemoveContainingFields | MailMergeCleanupOptions.RemoveUnusedRegions |
MailMergeCleanupOptions.RemoveEmptyTableRows;
doc.MailMerge.Execute(dataTable);
doc.Save(@"C:\Temp\out.docx");
// Restore culture
Thread.CurrentThread.CurrentCulture = culture;
In this case the result is consistent, even without HandleMailMergeField: out.docx (11.3 KB)
PS: Could you please remove the license file from the shared project, since it can be leaked? In this case the license file will be blacklisted.