Document structure was changed error for Formula field | Call Field.Unlink using Java

stco · February 1, 2021, 7:15am

Hello there,

I’m trying to apply custom format for merge field of number. It works fine for normal number field. However, it doesn’t work for formula field. Please see the code snippet below and for further details please see the attachment which including test code, test template and sample output

Note: Please note that I have tried it on Aspose Word 20.3 and 20.11

public class HandleMailMergeCalculateNumbers {
    public static void main(String[] args) throws Exception {
        // The path to the documents directory.
        String dataDir = Utils.getSharedDataDir(HandleMailMergeCalculateNumbers.class) + "MailMerge/";

        // Open an existing document.
        Document doc = new Document(dataDir + "MailMergeCalculateNumbers.docx");
        //Remove merge field with empty value and if the row are empty=> remove paragraph
        doc.getMailMerge().setCleanupOptions(MailMergeCleanupOptions.REMOVE_UNUSED_FIELDS
                | MailMergeCleanupOptions.REMOVE_EMPTY_PARAGRAPHS
                | MailMergeCleanupOptions.REMOVE_UNUSED_REGIONS
                | MailMergeCleanupOptions.REMOVE_CONTAINING_FIELDS);

        doc.getMailMerge().setFieldMergingCallback(new CustomedFieldMergingCallback());

        doc.getMailMerge().setUnconditionalMergeFieldsAndRegions(true);

        // Fill the fields in the document with user data.
        doc.getMailMerge().execute(
                new String[]{"expected", "evaluation"},
                new Object[]{1840.0, 230.0});

        dataDir = dataDir + "MergeNumbers_out.doc";
        doc.save(dataDir);

        System.out.println("\nSimple Mail merge performed with array data successfully.\nFile saved at " + dataDir);
    }
}

…

Test code, template and sample output:
customed_format_not_work_on_formula_field.zip (23.2 KB)

Thanks

tahir.manzoor · February 1, 2021, 3:52pm

@stco

You are facing the expected behavior of Aspose.Words. Please note that Aspose.Words mimics the behavior of MS Word. If you perform the mail merge using MS Word, you will get the same output.

We suggest you please remove ‘fr_ch’ from number format of formula field.

stco · February 2, 2021, 2:17am

@tahir.manzoor

The ‘fr_ch’ added there as an indicator for custom field merging callback to know which region/locale of formatter (Eg. fr_ch for French Swiss) should be used. So if it’s removed how do the callback knows that?

Please note that I don’t want to use the locale of the server/JDK for the formatter because we are providing our services to customers from difference locales/regions Eg. French, Italia, etc.

Expected: 1’840.00 <++ group separator of single quote
Actual: 1,840.00 <++ group separator of comma

tahir.manzoor · February 2, 2021, 6:51am

@stco

Please share the code of CustomedFieldMergingCallback for testing. We will investigate the issue and provide you more information on it.

stco · February 2, 2021, 7:04am

@tahir.manzoor

it already provided in the attachment of the first comment.

tahir.manzoor · February 2, 2021, 6:19pm

@stco

We have tested the scenario using the latest version of Aspose.Words for Java 21.1 and have not found the shared issue with mail merge field. Please check the attached image. Formula Field.png (3.7 KB)

The second field is Formula Field. You need to use the same approach for this field to set the field result as you are doing in IFieldMergingCallback.fieldMerging method.

To format the formula field, please do not set the following cleanup options.

doc.getMailMerge().setCleanupOptions(MailMergeCleanupOptions.REMOVE_UNUSED_FIELDS
        | MailMergeCleanupOptions.REMOVE_EMPTY_PARAGRAPHS
        | MailMergeCleanupOptions.REMOVE_UNUSED_REGIONS
        | MailMergeCleanupOptions.REMOVE_CONTAINING_FIELDS);

After performing mail merge, get the formula field and replace it with desired result.

stco · February 3, 2021, 1:14am

@tahir.manzoor

I think you misunderstand my need. Let me explain it again

The custom merging callback DOES WORK on the NORMAL merging field at **Expected format/result** :...
The callback DOES NOT work on the formula field

What I need is to make it work on formula field also

Thanks

tahir.manzoor · February 3, 2021, 6:10am

@stco

In your document the mail merge field switches (# fr_ch:#,##0.00) is only in first field as shown below.

{ MERGEFIELD expected \# fr_ch:#,##0.00 * MERGEFORMAT }

The fr_ch: exists in formula field instead of mail merge field. Please check the following field codes.

{= { MERGEFIELD evaluation * MERGEFORMAT } *8 \# fr_ch:#,##0.00 }

So, your CustomedFieldMergingCallback does not work for second mail merge field (evaluation).

Secondly, if you add fr_ch in mail merge field (evaluation), the code of CustomedFieldMergingCallback is executed and Double.parseDouble returns 230.00 without number separator. When this value is multiplied by 8 in MS Word formula field, the value is formatted according to field switches of formula field i.e. \# fr_ch:#,##0.00 and output value is fr_ch:1,840.00.

You need to perform the same operation for formula field in your code.

You can use following code example to format the formula field using same approach. Hope this helps you.

Document doc = new Document(MyDir + "MailMergeCalculateNumbers.docx");
DocumentBuilder builder = new DocumentBuilder(doc);
//Remove merge field with empty value and if the row are empty=> remove paragraph
/*doc.getMailMerge().setCleanupOptions(MailMergeCleanupOptions.REMOVE_UNUSED_FIELDS
| MailMergeCleanupOptions.REMOVE_EMPTY_PARAGRAPHS
| MailMergeCleanupOptions.REMOVE_UNUSED_REGIONS
| MailMergeCleanupOptions.REMOVE_CONTAINING_FIELDS);*/

doc.getMailMerge().setFieldMergingCallback(new CustomedFieldMergingCallback());
doc.getMailMerge().setUnconditionalMergeFieldsAndRegions(true);

// Fill the fields in the document with user data.
doc.getMailMerge().execute(
        new String[]{"expected", "evaluation"},
        new Object[]{1840.0, 230.0});

doc.updateFields();
//The first field is formula field in the document, so you can get it from field collection.
Field fma = doc.getRange().getFields().get(0);

String languageTag = LanguageCode.FRENCH.getCode();
String formatString = fma.getFormat().getNumericFormat();
String formatPattern = formatString;
if (formatString.contains(":")) {
    String[] formatParts = formatString.split(":");
    languageTag = formatParts[0];
    formatPattern = formatParts[1];
}
DecimalFormatSymbols formatSymbol = new DecimalFormatSymbols(Locale.forLanguageTag(languageTag));
if (Locale.forLanguageTag(languageTag).getLanguage().isEmpty()) {
    formatSymbol = new DecimalFormatSymbols(Locale.forLanguageTag(LanguageCode.FRENCH.getCode()));
}
// This is to handle for Swiss French and undefined case
if ("fr_ch".equalsIgnoreCase(languageTag)) {
    formatSymbol.setGroupingSeparator('\'');
    formatSymbol.setDecimalSeparator('.');
}
DecimalFormat formatter = new DecimalFormat(formatPattern, formatSymbol);
System.out.println(fma.getResult());

Double value = Double.parseDouble(fma.getResult().toString().replace("fr_ch:", "").replace(",", ""));
builder.moveTo(fma.remove());
builder.write(formatter.format(value));
System.out.println(formatter.format(value));

doc.save(MyDir + "output 21.1.docx");

tahir.manzoor · February 3, 2021, 6:51am

@stco

Further to my previous post, the FieldOptions.ResultFormatter property allows to control how the field result is formatted. You can implement IFieldResultFormatter interface to control how the field result is formatted.

Following code example shows how to automatically apply a custom format to field results as the fields are updated.

Document doc = new Document(MyDir + "MailMergeCalculateNumbers.docx");
DocumentBuilder builder = new DocumentBuilder(doc);
doc.getMailMerge().setFieldMergingCallback(new CustomedFieldMergingCallback());
doc.getMailMerge().setUnconditionalMergeFieldsAndRegions(true);

// Fill the fields in the document with user data.
doc.getMailMerge().execute(
        new String[]{"expected", "evaluation"},
        new Object[]{1840.0, 230.0});

doc.getFieldOptions().setResultFormatter(new FieldResultFormatter("\\# fr_ch:#,##0.00", null));
doc.updateFields();
doc.getRange().getFields().get(0).unlink();

doc.save(MyDir + "FormatFieldResult_out.docx");

import java.util.ArrayList;
import java.util.Date;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.util.Locale;
/**
 * Created by Home on 5/29/2017.
 */
//ExStart:FieldResultFormatter
public class FieldResultFormatter implements IFieldResultFormatter {

    private final String mNumberFormat;
    private final String mDateFormat;

    private final ArrayList mNumberFormatInvocations = new ArrayList();
    private final ArrayList mDateFormatInvocations = new ArrayList();

    public FieldResultFormatter(String numberFormat, String dateFormat) {
        mNumberFormat = numberFormat;
        mDateFormat = dateFormat;
    }

    public FieldResultFormatter() {
        mNumberFormat = null;
        mDateFormat = null;
    }

    public String format(String arg0, int arg1) {
        // TODO Auto-generated method stub
        return null;
    }

    public String format(double arg0, int arg1) {
        // TODO Auto-generated method stub
        return null;
    }

    public String formatNumeric(double value, String format) {
        // TODO Auto-generated method stub

        mNumberFormatInvocations.add(new Object[]{value, format});

        if(mNumberFormat.contains(LanguageCode.FRENCH.getCode()))
        {
            String languageTag = LanguageCode.FRENCH.getCode();
            String formatString = format;
            String formatPattern = formatString;
            if (formatString.contains(":")) {
                String[] formatParts = formatString.split(":");
                languageTag = formatParts[0];
                formatPattern = formatParts[1];
            }
            DecimalFormatSymbols formatSymbol = new DecimalFormatSymbols(Locale.forLanguageTag(languageTag));
            if (Locale.forLanguageTag(languageTag).getLanguage().isEmpty()) {
                formatSymbol = new DecimalFormatSymbols(Locale.forLanguageTag(LanguageCode.FRENCH.getCode()));
            }
            // This is to handle for Swiss French and undefined case
            if ("fr_ch".equalsIgnoreCase(languageTag)) {
                formatSymbol.setGroupingSeparator('\'');
                formatSymbol.setDecimalSeparator('.');
            }
            DecimalFormat formatter = new DecimalFormat(formatPattern, formatSymbol);
            Double value1 = Double.parseDouble(value+"");
            return formatter.format(value1);
        }


        return (mNumberFormat.isEmpty() || mNumberFormat == null) ? null
                : String.format(mNumberFormat, value);
    }

    public String formatDateTime(Date value, String format, int calendarType) {
        mDateFormatInvocations
                .add(new Object[]{value, format, calendarType});

        return (mDateFormat.isEmpty() || mDateFormat == null) ? null : String
                .format(mDateFormat, value);
    }
}

stco · February 3, 2021, 7:09am

@tahir.manzoor

Thanks for your feedback. it works for formula field now. However, I still want to remove unused fields, empty paragraphs, unused regions and containing fields from the output after all. Can you show me how to do that?

Thanks

tahir.manzoor · February 3, 2021, 4:49pm

@stco

Yes, you can use the cleanup options in your code. However, you need to remove the MailMergeCleanupOptions.REMOVE_CONTAINING_FIELDS from the code. For IF field or Formula field, you need to unlink them after mail merge using Field.Unlink method.

//Remove merge field with empty value and if the row are empty=> remove paragraph
doc.getMailMerge().setCleanupOptions(MailMergeCleanupOptions.REMOVE_UNUSED_FIELDS
| MailMergeCleanupOptions.REMOVE_EMPTY_PARAGRAPHS
| MailMergeCleanupOptions.REMOVE_UNUSED_REGIONS);
//| MailMergeCleanupOptions.REMOVE_CONTAINING_FIELDS);

stco · February 4, 2021, 4:25am

@tahir.manzoor

Thanks for your feedback.

Regarding the Field.Unlink it work fines for such a simple template as provided previously. However, as I try to apply it to our system I got the error of “Document structure was changed”. Do you have any idea/suggestion for this problem. Please see the full log in attachment. Below is a code snippet from our system.

    // The formatting number process in HandleMergeFieldInsert does not work on the formula field
    // then it need to handle after merging process with ResultFormatter
    // Ref: https://forum.aspose.com/t/custom-format-for-formula-field-doesnt-work/224707/10
    // This formatter callback will be invoked as calling template.updateFields
    document.getFieldOptions().setResultFormatter(new FieldResultFormatter());
    try {
        document.updateFields();
        // This is to remove conditional field Eg. IF and formula field
        // This is an alternative to the clean option of MailMergeCleanupOptions.REMOVE_CONTAINING_FIELDS
        for (Field field : document.getRange().getFields()) {
            field.unlink();
        }
    } catch (Exception e) {
        e.printStackTrace();
        throw new MailMergeException(ComponentErrorCodes.CMPT_014.name(), e.getMessage(), e.getCause());
    }

    mappingFields(document);

    return document;

Full error log:
error_using_Field.Unlink.txt.zip (40.1 KB)

tahir.manzoor · February 4, 2021, 1:17pm

@stco

Please make sure that you are moving the cursor to the correct position and inserting the correct values.

If you still face problem, please attach the following resources here for testing:

Your input Word document.
Please attach the output Word file that shows the undesired behavior.
Please attach the expected output Word file that shows the desired behavior.
Please create a simple Java application (source code without compilation errors) that helps us to reproduce your problem on our end and attach it here for testing.

As soon as you get these pieces of information ready, we will start investigation into your issue and provide you more information. Thanks for your cooperation.

PS: To attach these resources, please zip and upload them.