Update formula field type not working

Aspose.Words Product Family
1

We currently use Word Automation for mail merge reports, and are switching over to use Aspose.Word to replace it. The project is written in C#.

Users create bookmarks as follows:

I’ve tried using doc.MailMerge.Execute(dt) with, but the fields do not update or calculate.

I’ve also tried getting the fields with type FieldType.FieldFormula, and then replacing the field name in the formula from GetFieldCode() manually with the value from the data table, by setting field.Result to the formula and using field.Update(), but it just loads the old values.

I’ve also tried using DocumentBuilder to create a new field with the formula, and then deleting the old field and trying to insert in its place. It seems to process the formula, but there is no formatting, and I’m struggling to get it in the correct place.

When I look at field.Result, the value shows as “!Undefined Bookmark, TestAmount”.

This all seems unnecessarily complicated for what I’m trying to do. Not sure if I’m missing something obvious and there’s a better way of going about this, or if Aspose.Word just doesn’t support this functionality.

Any help would appreciated. Thanks!

2

@andrew.cousins TestAmout in your formula must be a merge field. Full field code should look like this:
{ =-ROUND({ MERGEFIELD TestAmount },2) }
You can construct such nested field in MS Word by pressing Ctrl+F9 and typing field code. note you should press Ctrl+F9 again to insert a nested field.

If you prefer using a simple text in the formula field syntax, you can use Mustache Syntax. Like this
{ =-ROUND({{TestAmount}},2) }
Where {{TestAmount}} is a simple text. In this case you should enable MailMerge.UseNonMergeFields option. For example see the following code and attached input and output documents:

Document doc = new Document(@"C:\Temp\in.docx");
doc.MailMerge.UseNonMergeFields = true;
doc.MailMerge.Execute(new string[] { "TestAmount"}, new object[] { System.Math.PI });
doc.Save(@"C:\Temp\out.docx");

in.docx (12.3 KB) out.docx (9.6 KB)
Press Alt+F9 to see field codes in MS Word.

3

Thanks, but we need to support the way user’s currently create their templates, as there would be too many to change.

I’ve managed to do it as follows using FormFields:

var formFields = _doc.Range.FormFields.Where(x => x.TextInputType == TextFormFieldType.Calculated).ToList();
var formula = formField.TextInputDefault;
// code to replace the variable with the value goes here and updates formula value
formField.TextInputDefault = formula;

This does what I’m trying to achieve.

4

@andrew.cousins Could you please attach your simple template here for testing? We will check it and provide you more information.

5

Hi @alexey.noskov - sure, see attached. Thanks.
TestTemplate.docx (16.8 KB)

6

@andrew.cousins Thank you for additional information. Quite not standard is used in your template. You are right, in your case you should work with form fields, like in the following code:

Document doc = new Document(@"C:\Temp\in.docx");
doc.Range.FormFields[0].TextInputDefault = string.Format("=-ROUND({0},2)", System.Math.PI);
doc.Save(@"C:\Temp\out.docx");