How to set locale for MS Word Mail Merge fields money format

Hello,

Numbers are formatted in wrong locale, how we can set the locale that supposed to be used?

Please note we need to set the locale at template level and not certain fields.

Is it something we can do in the Word Template itself or by setting some parameters in Aspose Word SDK?

Thank you.

@infolemadk

After an initial investigation of your requirements, I have logged a ticket WORDSCLOUD-1721 in our issue tracking system for further investigation and resolution. We will keep you updated about the issue resolution progress in this forum thread.

@infolemadk

We are investigating the issue. Meanwhile, please share your sample template along with the data file. It will help us to investigate and address your issue exactly.

The issues you have found earlier (filed as WORDSCLOUD-1721) have been fixed in this update. This message was posted using Bugs notification tool by Ivanov_John

@infolemadk

As stated above we have implemented your request in Aspose.Words Cloud 22.1. We have added FieldOptions and FieldUpdateCultureName parameters to set a specific culture (locale) for Mail Merge fields. To set a template locale we need to set above two FieldOptions parameters as follows.

using Aspose.Words.Cloud.Sdk;
using Aspose.Words.Cloud.Sdk.Model;
using Aspose.Words.Cloud.Sdk.Model.Requests;
using Aspose.Words.Cloud.Sdk.Tests.Base;
using System;
using System.IO;
using System.Text;
using System.Threading.Tasks;

namespace MailMergeDemo
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var p = new Program();
            WordsApi api = p.InitApi();

            // create template with a MERGEFIELD
            var templateFilename = "template.docx";
            var fieldCode = "{ MERGEFIELD Start \\@ \"dddd\"}";
            await p.CreateTemplate(api, templateFilename, fieldCode);

            // mail merge in De-de locale
            var data = "<Data><Start>2021/12/13</Start></Data>";
            await p.MailMerge(api, templateFilename, data, "de-DE");  // output Montag
            await p.MailMerge(api, templateFilename, data, "en-US");  // output Monday
        }

        private WordsApi InitApi()
        {
            // create Words Cloud API
            var config = new Configuration { ClientId = "xxxxx-xxx-xxxx-xxxx-xxx", ClientSecret = "xxxxxxxxxxxxxxxxxx", };
            var api = new WordsApi(config);
            return api;
        }

        private async Task CreateTemplate(WordsApi api, string templateFilename, string fieldCode)
        {
            // create docx with a date field.
            var createTemplateRequest = new BatchPartRequest(new CreateDocumentRequest(templateFilename));
            var insertFieldRequest = new BatchPartRequest(new InsertFieldRequest(templateFilename, new FieldInsert { FieldCode = fieldCode }));
            await api.Batch(false, createTemplateRequest, insertFieldRequest.DependsOn(createTemplateRequest));
        }

        private async Task MailMerge(WordsApi api, string templateFilename, string data, string locale)
        {
            string mergedFilename = $"{locale}.docx";
            string textFilename = $"{locale}.txt";

            var fieldOptions = new FieldOptions
            {
                FieldUpdateCultureSource = FieldOptions.FieldUpdateCultureSourceEnum.FieldCode,
                FieldUpdateCultureName = locale
            };

            // batch with mail merge, save as text, and dowenloading result
            var mailMergeRequest = new BatchPartRequest(new ExecuteMailMergeRequest(templateFilename, data, fieldOptions, destFileName: mergedFilename));
            var saveAsRequest = new BatchPartRequest(new SaveAsRequest(mergedFilename, new TxtSaveOptionsBaseData { SaveFormat = "txt", FileName = textFilename }));
            var downloadRequest = new BatchPartRequest(new DownloadFileRequest(textFilename));
            var result = await api.Batch(false, mailMergeRequest, saveAsRequest.DependsOn(mailMergeRequest), downloadRequest.DependsOn(saveAsRequest));

            // print the result
            Console.WriteLine($"Locale {locale}, date {new StreamReader((Stream)result[0], Encoding.UTF8).ReadToEnd()}");
        }
    }
}

Hi @tilal.ahmad
I’ve upgraded the package to the latest version, but I faced some problems with the new package version.
We’ve followed the instructions mentioned earlier in the mentioned example and as you see in the debugger here
debugger.png (98.6 KB)

$fieldOptions= new FieldOptions([
‘field_update_culture_name’=>“de-DE”, “field_update_culture_source”=>FieldOptions::FIELD_UPDATE_CULTURE_SOURCE_FIELD_CODE]);
$request = new ExecuteMailMergeRequest($remoteFileName , $pXmlData , $fieldOptions, $remoteDataFolder, NULL, NULL, NULL, false, NULL, NULL, NULL, NULL, $remoteDataFolder . “/” . $remoteResultFileName );

As you see I’m passing the function the remoteFileName, xml_file_content, fieldOptions and folder.

I’ve used the FieldOptions and passed the locale value however when executing mailMerge I got an error ( 404 ).
After some debugging, I got that exception that was being thrown by the new library

Server error: PUT https://api.aspose.cloud/v4.0/words/uploaded.docx/MailMerge?folder=contract%2F1537_92610222&encryptedPassword=false&destFileName=contract%2F1537_92610222%2FKontrakt_1730.docx resulted in a 500 Internal Server Error response:
{
“Error”: {
“Message”: "The input is not a valid Base-64 string as it contains a non-base 64 character, more th (truncated…)

This is the xml_file
Kontrakt_1730.docx (19.0 KB)

Looking forward to hearing from you soon!.

@b5digital

Please share your template as well. We will try to replicate the issue at our end and investigate it.

Hi @tilal.ahmad
Thanks for your cooperation.
Here you go, an example of a used template

finansiel_privat_tilbud_ny-62776044.docx (46.9 KB)

Thanks

@b5digital

I have tested the scenario using your sample documents and the latest version of Aspose.Words Cloud SDK for PHP. However, I am unable to replicate the issue. Please find my sample code and output file for reference.
MailMerge_output.docx (24.4 KB)

<?php

require_once('D:\xampp\htdocs\aspose-words-cloud-php-master\vendor\autoload.php');

//TODO: Get your AppSID and AppKey at https://dashboard.aspose.cloud (free registration is required).

$ClientId="xxxxx-xxxxx-xxxxx-xxxx-xxxxxxxx";
$ClientSecret="xxxxxxxxxxxxxxxxxxxxxxxxx";


$wordsApi = new Aspose\Words\WordsApi($ClientId,$ClientSecret);

$templateName = "finansiel_privat_tilbud_ny-62776044.docx";
$outputName = "ExecuteMailMerge_output.docx";
$remoteFolder="Temp";
#read mailmerge data
$data = file_get_contents("Kontrakt_1730.xml");

$fieldOptions= new Aspose\Words\Model\FieldOptions([
'field_update_culture_name'=>"de-DE", "field_update_culture_source"=>Aspose\Words\Model\FieldOptions::FIELD_UPDATE_CULTURE_SOURCE_FIELD_CODE]);

##upload template file to storage
$uploadRequest = new Aspose\Words\Model\Requests\UploadFileRequest($templateName,$remoteFolder."/".$templateName,null);
$wordsApi->uploadFile($uploadRequest);

##Execute MailMerge
$request = new Aspose\Words\Model\Requests\ExecuteMailMergeRequest($templateName, $data,$fieldOptions, $remoteFolder,NULL,NULL,NULL,NULL,false,NULL,NULL,NULL, $remoteFolder."/".$outputName);
$result = $wordsApi->executeMailMerge($request);
		
##Download file 
$request = new Aspose\Words\Model\Requests\DownloadFileRequest($remoteFolder."/".$outputName,NULL,NULL);
$result = $wordsApi->downloadFile($request);
copy($result->getPathName(),"MailMerge_output.docx");
    
} catch (Exception $e) {
    echo  "Something went wrong: ",  $e->getMessage(), "\n";
    PHP_EOL;
}

?>

Hi @tilal.ahmad
We have upgraded to the latest version and following the mentioned steps as you stated above.
I have used the culture name as “de-DE” and culture source as:

$fieldOptions= new FieldOptions([‘field_update_culture_name’=>“de-DE”, “field_update_culture_source”=>FieldOptions::FIELD_UPDATE_CULTURE_SOURCE_FIELD_CODE]);
$request = new ExecuteMailMergeRequest($localTemplateName, $pXmlData,$fieldOptions, $remoteFolder,NULL,NULL,NULL,NULL,false,NULL,NULL,NULL, $destinationName);
$result = $this->api->executeMailMerge($request);

However, When sending Numbers to aspose, Numbers are not formatted to the requested locale ( de-DE ) As you see in this image Screenshot-20220608031239-518x298.png (16.3 KB)

Expected numbers should be formatted like this:

Product Amount Excel Vat Cents : -78.627,34
Product Amount Inc Vat Cents : -98.284,18
Product Vat Amount Cents : -19.656,84
Product Cost Amount Excel Vat Cents : 0,00

I have provided a zip file containing the template used and xml data passed to mailMerge function.

template_And_XmlData.zip (11.7 KB)

@b5digital

We are investigating your issue with your shared Template/data file and will share our findings with you soon.

@b5digital

Please note to get a specific numeric format of MailMerge field, you need to set formatting in the MailMerge Template. Please check the following post for help. Hopefully, it will help you to accomplish the task.

Define Date and Numeric Formats for Mail Merge Fields