Hello everyone. I encountered a situation where, according to the technical specifications, the user had to be allowed to enter only a positive number into a table cell in the format X.XXX or X,XXX. In other words, the number is restricted to three decimal places. Moreover, if the user enters a comma, the comma must be converted into a dot so that the value stored in the cell remains valid.
And when it came time to implement this, I discovered that realizing such a simple input restriction required writing a lot of code.
What I did:
- I implemented a custom editor
CustomNumericEditorthat processes input values at the moment the cell is being edited. Inside, there is an event handlerinputHandlerthat I attach to the editor when opening it and remove when exiting the edit mode. In this handler, the value entered by the user is passed to the formatter functionformatReservoirVolumesNumericValue, which fully ensures that the output value is valid according to the requirement (removing non-numeric characters, converting a comma to a dot, removing extra dots, and limiting the number of digits after the decimal separator).
class CustomNumericEditor extends Handsontable.editors.NumericEditor {
// Create a named event handler so that it can be removed later
inputHandler = () => {
const input = this.TEXTAREA as HTMLInputElement
input.value = formatReservoirVolumesNumericValue(input.value)
}
open() {
super.open()
const input = this.TEXTAREA as HTMLInputElement
input.addEventListener('input', this.inputHandler)
}
close() {
super.close()
const input = this.TEXTAREA as HTMLInputElement
input.removeEventListener('input', this.inputHandler)
}
getValue() {
const inputValue = (this.TEXTAREA as HTMLInputElement).value
/*
If the user left a value starting with a dot (e.g., ".567"),
prepend a leading zero to ensure the cell contains a valid numeric
representation.
*/
if (inputValue.startsWith('.') && inputValue.length > 1) {
return '0' + inputValue
}
return inputValue
}
}
export const formatReservoirVolumesNumericValue = (
value: string,
isPasting = false,
): string => {
let newValue = value.replace(/[^\d.,]/g, '')
// Replace commas with dots
newValue = newValue.replace(',', '.')
// Remove all dots except for the first one
const parts = newValue.split('.')
if (parts.length > 1) {
newValue = parts[0] + '.' + parts.slice(1).join('').replace(/\./g, '')
}
// If the value is only a dot, return an empty string
if (newValue === '.') return ''
// If there is a fractional part, trim it to 3 digits
const numberParts = newValue.split('.')
if (numberParts.length > 1) {
numberParts[1] = numberParts[1].slice(0, 3)
newValue = numberParts.join('.')
}
// Logic only when pasting (CTRL+V)
if (isPasting) {
// Return an empty string for invalid values
if (isNaN(Number(newValue))) return ''
/*
If the user left a value starting with a dot (e.g., ".567"),
prepend a leading zero and return "0.567". This ensures that the cell has
a correct numeric representation.
*/
if (newValue.startsWith('.') && newValue.length > 1) {
newValue = '0' + newValue
}
}
return newValue
}
Full code for formatting backend data
type GetCellStyle = (props: {
cell: SpreadsheetBaseProps<NonNullable<unknown>, CharacteristicsSpreadsheetCell>['cell'][0]
canEdit: boolean
isSelectedDateEditable: boolean
isSelectedPlantViewOnly: boolean
message?: string
}) => string
export const getCellStyle: GetCellStyle = ({
canEdit,
isSelectedDateEditable,
isSelectedPlantViewOnly,
message,
}) => {
return classNames(
'htCenter',
{
['isNotValid']: !!message,
['disabledCell']:
!canEdit || !isSelectedDateEditable || isSelectedPlantViewOnly,
},
[],
)
}
export const formatReservoirVolumesSpreadsheetData = (
spreadsheetData: IGetSpreadsheetData<CharacteristicsSpreadsheetCell>,
canEdit: boolean,
isSelectedDateEditable: boolean,
isSelectedPlantViewOnly: boolean,
levelAllowableChange: number | string,
volumeAllowableChange: number | string,
): IWerReservoirVolumeStore['characteristicsDataSpreadsheet'] => {
const spreadsheetProps =
convertSpreadsheetResponseToComponentProps<CharacteristicsSpreadsheetCell>(
spreadsheetData,
)
// Perform validation of the original data matrix
const errors = validateCharacteristicsTableData(spreadsheetProps.data, {
0: levelAllowableChange,
1: volumeAllowableChange,
})
return {
...spreadsheetProps,
columns: spreadsheetProps.columns.map(() => ({
readonly: !canEdit || !isSelectedDateEditable || isSelectedPlantViewOnly,
editor: 'customReservoirVolumesNumericEditor' as keyof Editors,
})),
cell: spreadsheetProps.cell.map((cell) => {
// Find error for the current cell, if any
const error = errors.find(
(e) => e.row === cell.row && e.col === cell.col,
)
const message = error ? error.message : undefined
const className = getCellStyle({
cell,
canEdit,
isSelectedDateEditable,
isSelectedPlantViewOnly,
message,
})
return {
row: cell.row,
col: cell.col,
className,
editor: 'customReservoirVolumesNumericEditor',
readOnly: !canEdit || !isSelectedDateEditable || isSelectedPlantViewOnly,
value: cell.value,
column: cell.column,
comment: message ? getStyledComment(message) : undefined,
}
}),
}
}
This is where I use formatReservoirVolumesSpreadsheetData. It’s the action in MobX store that call api to get data and convert it for handsontable format with my custom editor, validation etc…
getReservoirVolumeCharacteristics: IWerReservoirVolumeStore[
'getReservoirVolumeCharacteristics'
] = async (plantId: number, date: string) => {
try {
this.isCharacteristicsLoaded = false
const data = await api.calcModelWerManager.getReservoirVolumeCharacteristics(
plantId,
date,
)
const formattedSpreadsheet = formatReservoirVolumesSpreadsheetData(
data.table,
this.rootStore.calcModelWerStore.listOfStationsStore
.characteristicsStore.editMode,
this.rootStore.calcModelWerStore.isSelectedDateEditable,
this.rootStore.calcModelWerStore.isSelectedPlantViewOnly,
data.settings.levelAllowableChange,
data.settings.volumeAllowableChange,
)
runInAction(() => {
this.currentSettings = data.settings
this.initialSettings = klona(data.settings)
this.lastFilledRow = data.lastFilledRow
this.characteristicsDataSpreadsheet = formattedSpreadsheet
this.originalCharacteristicsDataSpreadsheet = formattedSpreadsheet
this.isCharacteristicsLoaded = true
})
} catch (error) {
console.error(
'An error occurred while loading the reservoir characteristics',
error,
)
}
}
- Since adding a custom editor does not solve the problem of validating values that are pasted from the clipboard (CTRL+V), I had to add a
beforePastehook. In this hook, every value is also processed through the same formatter function —formatReservoirVolumesNumericValue. Only here the second argument is passed astruefor theisPastingflag, so that if the user pastes a value like.444, a leading zero is added (for theCustomNumericEditor, this logic is implemented in thegetValuemethod).
type GridSettings = Handsontable.GridSettings
export const ReservoirVolumesCharacteristicsSpreadsheet = observer(() => {
const { calcModelWerStore } = useStore()
const { listOfStationsStore } = calcModelWerStore
const { characteristicsStore } = listOfStationsStore
const { reservoirVolumeStore } = characteristicsStore
const {
characteristicsDataSpreadsheet,
updateCharacteristicsSpreadsheetData,
isCharacteristicsLoaded,
} = reservoirVolumeStore
const { data, cell, nestedHeaders, columns, rowHeaders } =
characteristicsDataSpreadsheet
const colHeaders = nestedHeaders.flatMap((row) =>
row.map((header) => header.label),
)
const beforePaste: GridSettings['beforePaste'] = (data, _) => {
const processedData = data.map((row) =>
row.map((cellValue) => formatReservoirVolumesNumericValue(cellValue, true)),
)
// Clear the original array and insert the processed data
data.length = 0
data.push(...processedData)
}
/**
* Styles the column headers.
* @param col - Column index.
* @param TH - The column header element.
* @param level - Nesting level.
*/
const afterGetColHeader: GridSettings['afterGetColHeader'] = (col, TH) => {
const isNumberColumn = col === -1
let headerText = isNumberColumn ? '№' : TH.textContent ?? ''
// If it's not a numbering column and the header text contains a comma, split it
// into two lines.
if (!isNumberColumn && headerText.includes(',')) {
headerText = headerText.replace(/,\s*/, ',<br/>')
}
// Reset styles before applying new ones
TH.innerHTML = ''
const div = document.createElement('div')
div.style.cssText = `
height: 60px;
line-height: normal;
white-space: normal;
word-wrap: break-word;
display: flex;
align-items: center;
justify-content: center;
text-align: center;
`
const hasError = cell.some((cellItem) => Boolean(cellItem.comment))
if (hasError) {
div.style.backgroundColor = '#ffbbbb'
}
div.innerHTML = headerText
TH.appendChild(div)
TH.classList.add(cls.bold)
}
if (!isCharacteristicsLoaded) return null
const tableSettings: Partial<GridSettings> = {
data,
columns,
rowHeaders,
colHeaders,
cell,
height: 680,
width: 273,
rowHeaderWidth: 45,
colWidths: 108,
afterGetColHeader,
afterChange: updateCharacteristicsSpreadsheetData,
beforePaste,
}
return <SpreadsheetReact settings={tableSettings} />
})
When I finished, I started doubting whether I did it correctly. Is it written in an overly imperative style? Perhaps there are better practices to satisfy such trivial requirements for allowed values in a table.
There was another situation where the table had two columns. The value requirements were similar; however, in the first column, only one decimal place is allowed. To achieve this, I created a similar formatter function but with a third argument maxDecimals, which determines how many digits after the decimal separator are allowed for the specific column.
const parts = newValue.split('.')
if (parts.length > 1) {
parts[1] = parts[1].slice(0, maxDecimals)
newValue = parts.join('.')
}
For the columns, I added a property maxDecimals that, based on the column index, determined how many digits after the decimal separator are allowed for that cell’s value.
columns: spreadsheetProps.columns.map((_, index) => ({
readonly: !canEdit || !isSelectedDateEditable || isSelectedPlantViewOnly,
editor: 'customNumericEditor' as keyof Editors,
maxDecimals: index === 0 ? 1 : 3,
})),
The custom editor looks like this:
class CustomNumericEditor extends Handsontable.editors.NumericEditor {
// Create a named event handler so that it can be removed later.
inputHandler = () => {
const input = this.TEXTAREA as HTMLInputElement
// Retrieve the required number of decimal places from cellProperties,
// defaulting to 3 if not specified.
const maxDecimals = this.cellProperties?.maxDecimals ?? 3
input.value = formatSpecificConsumptionNumericValue(
input.value,
false,
maxDecimals,
)
}
open() {
super.open()
const input = this.TEXTAREA as HTMLInputElement
input.addEventListener('input', this.inputHandler)
}
close() {
super.close()
const input = this.TEXTAREA as HTMLInputElement
input.removeEventListener('input', this.inputHandler)
}
getValue() {
const inputValue = (this.TEXTAREA as HTMLInputElement).value
/*
If the user left a value that starts with a dot (e.g., ".567"),
prepend a leading zero and return "0.567". This ensures that the cell has
a valid numeric representation.
*/
if (inputValue.startsWith('.') && inputValue.length > 1) {
return '0' + inputValue
}
return inputValue
}
}
Handsontable.editors.registerEditor('customNumericEditor', CustomNumericEditor)
But here I also had to implement an additional beforeAutofill hook because the user could copy a value by dragging from a neighboring column (where three decimal places are allowed) into a column that only allows one decimal place.
/**
* beforePaste hook
*
* Processes the pasting (CTRL+V) of data into the table.
* For each cell, depending on its column position, formats the number with the required
* number of decimal places (1 for the first column, 3 for the others).
*
* @param data - a 2D array of the data being pasted.
* @param coords - an array of objects with paste coordinates (start position, range, etc.).
*/
const beforePaste: Handsontable.GridSettings['beforePaste'] = (data, coords) => {
if (coords && coords.length > 0) {
// Assume the paste starts at a specific column.
const startCol = coords[0].startCol
const processedData = data.map((row) =>
row.map((cellValue, colIndex) => {
// Calculate the target column index for the cell.
const targetColIndex = startCol + colIndex
// Set maxDecimals to 1 for the first column, and 3 for the others.
const maxDecimals = targetColIndex === 0 ? 1 : 3
return formatSpecificConsumptionNumericValue(cellValue, true, maxDecimals)
}),
)
// Clear the original array and insert the processed data.
data.length = 0
data.push(...processedData)
}
}
/**
* beforeAutofill hook
*
* Called during autofill (dragging the fill handle) of a data range.
* Processes each value similar to beforePaste – formats the number with the proper
* number of decimal places based on its position in the target range.
*
* @param selectionData - a 2D array of the source data for autofill.
* @param _sourceRange - the source range (unused).
* @param targetRange - the target range where the data will be inserted.
*
* @returns the processed 2D array for autofill.
*/
const beforeAutofill: Handsontable.GridSettings['beforeAutofill'] = (
selectionData,
_sourceRange,
targetRange,
) => {
// Get the index of the first column in the autofill range.
const startCol = targetRange.from.col
const processedData = selectionData.map((row) =>
row.map((cellValue, colIndex) => {
const targetColIndex = startCol + colIndex
// For the first column, set maxDecimals to 1; for the others, set it to 3.
const maxDecimals = targetColIndex === 0 ? 1 : 3
return formatSpecificConsumptionNumericValue(cellValue, true, maxDecimals)
}),
)
// Overwrite the autofill data with the processed values.
selectionData.length = 0
selectionData.push(...processedData)
return selectionData
}
And after such a sheet of text, I’d like to hear if I’m moving in the right direction. Because I have a strong feeling that I’m writing too much boiler plate code and as if there are better practices.