Forums Home / Wijmo / General Discussion
Posted by: Fabrice.Mainguene on 13 December 2023, 2:07 am EST
Fabrice.Mainguene
Posted 13 December 2023, 2:07 am EST - Updated 14 December 2023, 12:18 am EST
Hi,
I try to find a way that when my customers use the formatter “[h]:mm:ss”, if they write 8 in the editor, the cell displays 8:00:00 and not “192:00:00”.
Is it a way to modify the “editingText” value during the EditEnding or the EditEnded event?
Or is there a way to use setEditorValue function on the current opened editor? I can’t get the context…
Or perhaps have you a better solution?
Thanks
vivek.pannu
Posted 13 December 2023, 11:39 pm EST
Hi Fabrice,
Sorry but we are unable to understand the exact issue you are facing, could you please share some more details about your requirements? As per our understanding, you are using Wijmo’s FlexGrid control, which contains a column with dates, and you are using some custom format for that column to format the dates in your desired format, but we are not sure about the implementation on your side and the exact issue you are facing. Could you please update the below sample to replicate your issue and share it with us, so that we can have a better understanding of your requirements and assist you accordingly?
Sample link - https://stackblitz.com/edit/js-hc2ji3?file=index.js
Regards
Fabrice.Mainguene
Posted 14 December 2023, 12:30 am EST - Updated 14 December 2023, 12:35 am EST
Hi,
I don’t know what is Wijmo’s FlexGrid control so I don’t use it.
I just apply the “[h]:mm:ss” formatted on a column:
My goal is that if a user enter an integer in this column and valid it, the result display in the cell is “8:00:00” and not “192:00:00”.
So I try to reformat it with the EditEnding event and a setValue() but the result “192:00:00” is visible for a few seconds before being rewritten correctly with “8:00:00”.
So I want to know if I can modify the editingText during the EditEnding event so that it is written directly in the correct format by spreadjs
ankit.kumar
Posted 14 December 2023, 11:43 pm EST
Hi,
Based on my understanding, you are utilizing the formatter “[h]:mm:ss” in a column. When you input the value 8 in a cell, it displays as 192:00:00. However, your preference is to have it displayed as 8:00:00.
when using the “[h]:mm:ss” formatter in Excel, it will provide the same value. Therefore, the outcome you are observing in designer aligns with the expected result.
For your specific requirement, you could use the “d:mm:ss” formatter, which functions correctly in Excel. However, when using it in Spread Designer, there seems to be an issue. We have escalated this to the developers for further investigation. The internal tracking id is SJS-21920.
As a workaround, you can define a custom formatter. I have provided some articles and a demo sample for your reference.
Demo sample: https://developer.mescius.com/spreadjs/demos/features/cells/formatter/custom-formatter/purejs
References:
Cell format: https://developer.mescius.com/spreadjs/docs/features/cells/cellformat
Best regards,
Ankit
Fabrice.Mainguene
Posted 17 December 2023, 7:48 pm EST
Hi,
What is “d:mm:ss” formatter? When I test it on my Excel, it doesn’t seem to work. And if “d” is for day, I think that it is not what I need.
Please answer my question: Is there a way to modify the editingText by programming during the EditEnding event ?
ankit.kumar
Posted 18 December 2023, 9:48 pm EST
Hi,
The “d:mm:ss” format typically represents a duration or time interval in the format of days, minutes, and seconds. It is working in excel in my end. Refer attached video and region settings.
You cannot alter the editingText provided in the editEnding event. However, you can use the editEnded event and modify the cell’s value using the setValue method. Please refer to the provided snippet and sample for reference.
spread.bind( GC.Spread.Sheets.Events.EditEnded, (e, info) => {
// change cell value
info.sheet.setValue(info.row, info.col, info.editingText + ":00:00");
})As you were using “[h]:mm:ss” but desired a different result from what was expected, you might want to consider using a custom formatter to meet your requirements. I’ve included a sample and attached relevant articles for your reference.
However, if your issue is not resolved yet using the custom formatter, kindly do let us know the issue you are facing. Also, explain your use case in detail so that we could have a better understanding of your use case and could assist you accordingly.
Cell format: https://developer.mescius.com/spreadjs/docs/features/cells/cellformat
Best regards,
Ankit
Fabrice.Mainguene
Posted 20 December 2023, 3:56 am EST
Hi,
spread.bind( GC.Spread.Sheets.Events.EditEnded, (e, info) => {
// change cell value
info.sheet.setValue(info.row, info.col, info.editingText + ":00:00");
})ankit.kumar
Posted 21 December 2023, 1:58 pm EST
Hi,
SpreadJS automatically formats itself when you attempt to set the value through ui, but when setting the value manually, this auto-formatting doesn’t occur. To achieve your requirements, you can utilize the editCell command in the EditEnded event. This command is used to apply a new value to a cell on the worksheet with auto format. Please refer to the snippet and sample provided below.
spread.bind( GC.Spread.Sheets.Events.EditEnded, (e, info) => {
if(info.editingText && !isNaN(info.editingText) ){
spread.commandManager().execute({cmd: "editCell", sheetName: info.sheetName, row: info.row, col: info.col, newValue: `${info.editingText}:00:00`, autoFormat: true});
}
})References:
editCell: https://developer.mescius.com/spreadjs/api/modules/GC.Spread.Sheets.Commands#editcell
Best regards,
Ankit
Fabrice.Mainguene
Posted 21 December 2023, 9:44 pm EST
Exactly what I need! Thanks for your help!
ankit.kumar
Posted 25 December 2023, 3:45 pm EST
Hi,
We are happy that your issue has been resolved. However, for the escalated case SJS-21920, the devs have mentioned that the mentioned issue was actually a bug in the Microsoft Excel. You may refer to the following workaround by handling the HandleExcelSpecialDate method.
Kindly refer to the following workaround sample: https://jscodemine.grapecity.com/share/yMntwvGFuUmBpV4QpNOSrw/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}
Regards,
Ankit
