Correct currency and decimal separator on interface and then when exporting to Excel
We are having difficulties while setting the language and currency of the spreadsheet in the interface and then when exporting to Excel.
On the interface we are setting the correct language, and we do get the € as symbol, but the decimal separator is wrong. It only appear correctly after we switch to another sheet.
function loadCultureFiles(name) {
var files = ['ca-gregorian.json', 'numbers.json', 'timeZoneNames.json', 'currencies.json'];
var loadCulture = function (prop) {
var val, ajax;
ajax = new ej.base.Ajax(location.origin + '/scripts/cldr-data/main/' + name + '/' + files[prop], 'GET', true);
ajax.onSuccess = function (value) {
val = value;
ej.base.loadCldr(JSON.parse(val));
};
ajax.send();
ej.base.setCulture(name); //set culture file globally
ej.base.setCurrencyCode('EUR'); //set currency code globally
};
for (var prop = 0; prop < files.length; prop++) {
loadCulture(prop);
}
}
We set the currency format on cells after calling this method, on the Created event.
Where must we do this so that it get correct? We also tried to do a goTo the first sheet in the end but the layout gets all messed up.
This we would always need to do, so the user is not by default on the last sheet, but cannot seem to be able to do it. We have a script that does the goto on each sheet to set its layout configurations, then the last goto does a go to the first sheet but this happens.
Then, when we get to the server Save Excel MVC method and the Excel generated, nothing is in the right format. We are always getting the Excel with the default language settings: $, etc. The code is pretty straightforward:
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
Stream fileStream = Workbook.Save<Stream>(saveSettings);
The saveSettings has the language as pt-PT. We do not see any other settings we could do. We already forced the thread culture.
How can this be accomplished server side?
Thank you
Hi ShareValue ,
We have checked your reported issue. While replicating, we have found that the format you have used in the value is in the custom format (containing dollars – attached image for reference) in the attached excel file. As you have used a custom format that holds a dollar, it gets saved in the same format while exporting on the server side. We have changed the format to currency and checked your issue. It works fine on our side.
We insist you ensure the format applied doesn’t contain dollars and is in the currency format before exporting. It may work in the proper way.
Note: Ensure the system's culture too.
If you are still facing the same issue, kindly provide the importing excel file for validating in-order to provide better solution - regarding
Regards,
Vasanth R
I'm sorry, but your answer doesn't make sense, because I am not using a custom format.
You can see in the video I've sent that the format in the interface appears with euro, and not dollars.
The format setting is done on the Created event of the spreadsheet with code like this:
this.numberFormat(ej.spreadsheet.getFormatFromType("Currency"), "C4:J17");
So, we never set $ as the currency. It is inferred by the language setting and the
ej.base.setCurrencyCode('EUR') call.
But on the save as Excel button event, when we call
var ssObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
ssObj.saveAsJson().then((response) => {
JSON.stringify(response.jsonObject.Workbook)
}
the response.jsonObject.Workbook is with the $ format you indicated, which is incorrect.
That means that the only fix would be to replace the format on the json object with what we want, but that shouldn't be the right fix. The saveAsJson method should set the correct format.
We are even calling the load the culture files before calling the save as json, but it doesn't change the behavior.
So, how can I set the currency and format so that it is recognized by the saveAsJson method? We were hopping not to hardcode the format, and let it load from the user language.
I cannot send an Excel with the import because all the Excel sheets are generated from a database model, and loaded into the spreadsheet control.
