Forums Home / Spread / SpreadJS
Posted by: juraj.jakubik on 7 September 2022, 11:56 pm EST
juraj.jakubik
Posted 7 September 2022, 11:56 pm EST
We have two culture (en, cs).
With the active culture cs the date cells are displayed with the en formatter.
Formatter changes in edit mode (after double click).
The number cell formatter works as expected.
Some missing setting or a bug?
avinash.pathak
Posted 11 September 2022, 9:12 pm EST
Hi,
For changing the date format in editmode you need to change the shortDatePatterm of culture. Please refer to the following code snippet and let me know if you face any issues.
let c = GC.Spread.Common.CultureManager.getCultureInfo("en-us")
c.DateTimeFormat.shortDatePattern= "mm-dd-yyyy";
GC.Spread.Common.CultureManager.addCultureInfo("kk-dd",c);
GC.Spread.Common.CultureManager.culture("kk-dd")
regards,
Avinash
juraj.jakubik
Posted 12 September 2022, 7:34 pm EST
Hi,
yes, in edit mode it is working (the format is correct - ‘d.m.yyyy’), but when I leave the cell, the format changes (‘mm/dd/yyyy’).
It’s expected behavior?
For us, it is confusing and we would expect the same behavior that currently works for the number cell.
avinash.pathak
Posted 13 September 2022, 8:47 pm EST
Hi,
This is expected because the cell format can be different from the editmode formatter which is why it is not recommended to change the edit mode formatter. Excel also does the same. We are sorry for the inconvenience.
If you want to show the edit mode formatter the same as the cell formatter. YOu use the following workaround.
Of course, there are some other way to achieve the customer goal, but a little bit "hack", please consider whether provided to the customer.
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 2 });
var sheet = spread.getActiveSheet();
var newForm = GC.Spread.Sheets.CellTypes.Text.prototype.setEditorValue;
var oldCmd = GC.Spread.Sheets.Commands.editCell.execute;
GC.Spread.Sheets.Commands.editCell.execute = function (context, options, isUndo) {
var sheet = context.getSheetFromName(options.sheetName);
var formatter = sheet.getFormatter(options.row, options.col);
if (options.editingFormatter && formatter) {
options.editingFormatter = new GC.Spread.Formatter.GeneralFormatter(formatter);
}
return oldCmd.apply(this, arguments);
}
GC.Spread.Sheets.CellTypes.Text.prototype.setEditorValue = function (
editor,
value,
context
) {
var sheet = context.sheet,
row = context.row,
column = context.col;
var cellValue = sheet.getValue(row, column);
var formatter = sheet.getFormatter(row, column);
if (cellValue && formatter) {
value = new GC.Spread.Formatter.GeneralFormatter(formatter).format(cellValue);
newForm.call(this, editor, value, context);
} else {
newForm.apply(this, arguments);
}
};
initSpread(spread);
function initSpread(spread) {
var sheet = spread.getActiveSheet();
sheet.setValue(0, 0, new Date(2020, 3, 31, 5, 6, 2, 1));
sheet.setFormatter(0, 0, "dd-mm-yyyy");
sheet.setColumnWidth(0, 150);
sheet.setValue(1, 0, new Date(2020, 3, 31, 5, 6, 2, 1));
sheet.setFormatter(1, 0, "dd~mmm~yyyy");
sheet.resumePaint();
}
Regards,
Avinash
juraj.jakubik
Posted 18 September 2022, 8:28 pm EST
Hi,
thank you for the answer.
To be honest, we are not very happy with the current solution/workaround.
I’m going to repeat myself, but we would expect the same behavior as in excel.
Excel keeps the same format in both normal and editing mode for the given language. It does not mix language formats for the selected language.
After all, it doesn’t even make sense. Shouldn’t the format depend on the chosen language?
avinash.pathak
Posted 19 September 2022, 9:18 pm EST - Updated 3 October 2022, 12:00 am EST
Hi,
The spreadJS match excels policy for editmode formatter. the editing format could not be customized by the user, it follows the current culture, using shortDatePatttern or shortDatePattern + longTimePattern. so if the formatter does not match the edit mode formatter may get weird. In this case, excel also changes the formatter as soon as the user goes inside the edit mode.
Please refer to the following image which shows the formatter which is shown in edit mode.
regards,
Avinash
juraj.jakubik
Posted 19 September 2022, 9:52 pm EST
Hi,
Thank you once again Avinash for your reply.
I think I understand that the formatter in non-edit mode does not change according to the language.
It is possible, but there must be a custom code (custom logic) for it.
Here is our naive implementation which is executed after the language is changed:
for (let row = 1; row <= rowCount; row++) {
for (let col = 1; col <= columnCount; col++) {
if (sheet.getCell(row, col).formatter() === enShortDatePattern && activeLang === 'cs'){
sheet.getCell(row, col).formatter(csShortDatePattern);
} else if (sheet.getCell(row, col).formatter() === csShortDatePattern && activeLang === 'en') {
sheet.getCell(row, col).formatter(enShortDatePattern);
}
}
}
Is it possible to find out the cell type? For example that it is a date cell?
avinash.pathak
Posted 20 September 2022, 8:53 pm EST
hi,
For this, you may use getValue method and if the value is date getVlaue return the Date Object. Please refer to the following code snippet and let me know if you face any issues.
let sheet = spread.getActiveSheet()
if(sheet.getValue(3,1) instanceof Date){
console.log("cell value si a date object");
}
Regards,
Avinash
juraj.jakubik
Posted 21 September 2022, 6:32 pm EST
Hi, thank you!
