// import React, { useRef, useState, useEffect } from 'react'
// import { HotTable } from '@handsontable/react'
// import 'handsontable/dist/handsontable.full.min.css'
// import Handsontable from 'handsontable'
// import {
// registerControl,
// RegisterControlProps,
// RegisteredControlProperties,
// } from '../hoc/registerControl'
// import { CustomContentRenderer } from './utils/customButtonRenderer'
// import { hyperformulaInstance, sheetId } from './utils/hyperformulaConfig'
// import { reformatDate, reformatCurrency } from './utils/formatters'
// import { SpreadsheetProperties } from '@components/features/FormBuilder/FormBuilderFieldProperties/Spreadsheet/types'
// import { registerAllCellTypes } from 'handsontable/cellTypes'
// import { registerAllPlugins } from 'handsontable/plugins'
// import { useFormBuilderWorksheetGrid } from '../../hooks/useFormBuilderWorksheetGrid'
// import { createSpreadsheetDefaultValue } from '../../FormBuilderFieldProperties/Spreadsheet/constants'
// import { useFormBuilder } from '../../hooks/useFormBuilder'
// import { useFormService } from '../../hooks/useFormService'
// registerAllCellTypes()
// registerAllPlugins()
// type Properties = RegisteredControlProperties &
// SpreadsheetProperties & {
// externalVariables?: {
// [key: string]: {
// propertyName?: string
// defaultValue?: number
// }
// }
// }
// const SpreadsheetControl: React.FC<RegisterControlProps<Properties>> = (
// props
// ) => {
// const hotTableRef = useRef<any>(null)
// const containerRef = useRef<HTMLDivElement>(null)
// const { rows = 1, columns = 1 } = props.properties || {}
// const DEFAULT_COLUMN_WIDTH = 100
// const DEFAULT_ROW_HEIGHT = 25
// const defaultMeta = {
// data: createSpreadsheetDefaultValue(rows, columns),
// cellFormats: {} as { [key: string]: 'date' | 'currency' | undefined },
// mergeCellsConfig: [],
// formulas: [],
// }
// const meta = props.properties?.meta || defaultMeta
// const { actions: formBuilderWorksheetActions } = useFormBuilderWorksheetGrid()
// const { actions, data: formBuilderData } = useFormBuilder()
// const { watch } = useFormService()
// const formData = watch()
// if (!actions || !actions.getAllFields) {
// }
// const [data, setData] = useState(
// () => props?.value ?? JSON.parse(JSON.stringify(meta.data))
// )
// const [cellFormats, setCellFormats] = useState(meta.cellFormats)
// const [mergeCellsConfig, setMergeCellsConfig] = useState(
// meta.mergeCellsConfig
// )
// const [formulas, setFormulas] = useState<
// { row: number; col: number; formula: string }[]
// >(meta.formulas || [])
// useEffect(() => {
// if (props.properties?.meta) {
// const {
// data: metaData,
// formulas: metaFormulas = [],
// mergeCellsConfig: metaMergeCells,
// } = props.properties.meta
// setData(props.value ?? metaData)
// setFormulas(metaFormulas)
// setMergeCellsConfig(metaMergeCells)
// metaFormulas.forEach(({ row, col, formula }) => {
// try {
// hyperformulaInstance.setCellContents({ sheet: sheetId, row, col }, [
// [formula],
// ])
// } catch (error) {
// // Handle error if necessary
// }
// })
// hyperformulaInstance.rebuildAndRecalculate()
// }
// }, [props.properties?.meta, props.value])
// // useEffect(() => {
// // if (formBuilderData) {
// // const existingExpressions = hyperformulaInstance.listNamedExpressions()
// // Object.entries(formBuilderData).forEach(([key, value]) => {
// // const numericValue = Number(value) || 0
// // if (!existingExpressions.includes(key)) {
// // hyperformulaInstance.addNamedExpression(key, numericValue)
// // } else {
// // hyperformulaInstance.changeNamedExpression(key, numericValue)
// // }
// // })
// // try {
// // hyperformulaInstance.rebuildAndRecalculate()
// // if (sheetId !== undefined) {
// // const recalculatedData = hyperformulaInstance.getSheetValues(sheetId)
// // const adjustedData = adjustDataDimensions(
// // recalculatedData,
// // rows,
// // columns
// // )
// // setData(adjustedData)
// // updateFieldProperties({ data: adjustedData })
// // }
// // } catch (error) {}
// // }
// // }, [formBuilderData])
// // useEffect(() => {
// // if (formBuilderData && Object.keys(formBuilderData).length > 0) {
// // const existingExpressions = hyperformulaInstance.listNamedExpressions()
// // Object.entries(formBuilderData).forEach(([key, value]) => {
// // if (!existingExpressions.includes(`${key}`)) {
// // hyperformulaInstance.addNamedExpression(`${key}`, Number(value) || 0)
// // } else {
// // hyperformulaInstance.changeNamedExpression(
// // `${key}`,
// // Number(value) || 0
// // )
// // }
// // })
// // try {
// // hyperformulaInstance.rebuildAndRecalculate()
// // if (hotTableRef.current?.hotInstance && typeof sheetId === 'number') {
// // const updatedData = hyperformulaInstance.getSheetSerialized(sheetId)
// // const adjustedData = adjustDataDimensions(updatedData, rows, columns)
// // hotTableRef.current.hotInstance.loadData(adjustedData)
// // setData(adjustedData)
// // }
// // } catch (error) {}
// // }
// // }, [formBuilderData])
// // useEffect(() => {
// // if (formData && Object.keys(formData).length > 0) {
// // const existingExpressions = hyperformulaInstance.listNamedExpressions()
// // Object.entries(formData).forEach(([key, value]) => {
// // if (!existingExpressions.includes(`${key}`)) {
// // hyperformulaInstance.addNamedExpression(`${key}`, Number(value) || 0)
// // } else {
// // hyperformulaInstance.changeNamedExpression(
// // `${key}`,
// // Number(value) || 0
// // )
// // }
// // })
// // try {
// // hyperformulaInstance.rebuildAndRecalculate()
// // if (hotTableRef.current?.hotInstance && typeof sheetId === 'number') {
// // const updatedData = hyperformulaInstance.getSheetSerialized(sheetId)
// // const adjustedData = adjustDataDimensions(updatedData, rows, columns)
// // hotTableRef.current.hotInstance.loadData(adjustedData)
// // setData(adjustedData)
// // }
// // } catch (error) {}
// // }
// // }, [formData])
// useEffect(() => {
// const syncFormBuilderData = (data) => {
// if (data && Object.keys(data).length > 0) {
// const existingExpressions = hyperformulaInstance.listNamedExpressions()
// Object.entries(data).forEach(([key, value]) => {
// if (!existingExpressions.includes(`${key}`)) {
// hyperformulaInstance.addNamedExpression(
// `${key}`,
// Number(value) || 0
// )
// } else {
// hyperformulaInstance.changeNamedExpression(
// `${key}`,
// Number(value) || 0
// )
// }
// })
// try {
// hyperformulaInstance.rebuildAndRecalculate()
// if (hotTableRef.current?.hotInstance && typeof sheetId === 'number') {
// const updatedData = hyperformulaInstance.getSheetSerialized(sheetId)
// const adjustedData = adjustDataDimensions(
// updatedData,
// rows,
// columns
// )
// hotTableRef.current.hotInstance.loadData(adjustedData)
// setData(adjustedData)
// }
// } catch (error) {
// console.error('Error syncing form builder data:', error)
// }
// }
// }
// // Sync formBuilderData
// syncFormBuilderData(formBuilderData)
// // Sync formData if applicable
// syncFormBuilderData(formData)
// }, [formBuilderData, formData])
// useEffect(() => {
// const adjustedData = adjustDataDimensions(data, rows, columns)
// if (JSON.stringify(data) !== JSON.stringify(adjustedData)) {
// setData(adjustedData)
// }
// }, [rows, columns])
// const adjustDataDimensions = (
// currentData: any[][],
// rows: number,
// columns: number
// ) => {
// const adjustedData = currentData.map((row) => [...row])
// while (adjustedData.length < rows) {
// adjustedData.push(Array(columns).fill(null))
// }
// adjustedData.length = rows
// adjustedData.forEach((row) => {
// while (row.length < columns) {
// row.push(null)
// }
// row.length = columns
// })
// return adjustedData
// }
// useEffect(() => {
// if (hotTableRef.current?.hotInstance) {
// hotTableRef.current.hotInstance.render()
// }
// }, [data])
// const handleAfterChange = (changes: any, source: string) => {
// if (!changes || source === 'loadData') return
// const validSheetId = sheetId ?? 0
// let updatedFormulas = [...formulas] // Copy the current formulas array
// changes.forEach(([row, col, oldValue, newValue]: any) => {
// const formulaIndex = updatedFormulas.findIndex(
// (formula) => formula.row === row && formula.col === col
// )
// if (
// newValue &&
// typeof newValue === 'string' &&
// newValue.startsWith('=')
// ) {
// if (props.runtime) {
// // Do not process formulas in runtime mode
// // The cell value is already set to '#ERROR!' in beforeChange
// } else {
// try {
// if (formulaIndex !== -1) {
// // Update existing formula
// updatedFormulas[formulaIndex].formula = newValue
// } else {
// // Add new formula
// updatedFormulas.push({ row, col, formula: newValue })
// }
// setFormulas(updatedFormulas)
// // Set cell contents in HyperFormula
// hyperformulaInstance.setCellContents(
// { sheet: validSheetId, row, col },
// [[newValue]]
// )
// } catch (error) {
// // Handle error if necessary
// }
// }
// } else {
// if (formulaIndex !== -1) {
// // Remove formula from formulas array
// updatedFormulas.splice(formulaIndex, 1)
// setFormulas(updatedFormulas)
// }
// try {
// // Set cell contents for regular values
// hyperformulaInstance.setCellContents(
// { sheet: validSheetId, row, col },
// [[newValue]]
// )
// } catch (error) {
// // Handle error if necessary
// }
// }
// })
// try {
// // Recalculate HyperFormula
// hyperformulaInstance.rebuildAndRecalculate()
// // Get updated data from HyperFormula
// const updatedData = hyperformulaInstance.getSheetSerialized(validSheetId)
// // Adjust data dimensions if necessary
// const adjustedData = adjustDataDimensions(updatedData, rows, columns)
// setData(adjustedData)
// if (props.onChange) {
// console.log('onChange triggered with data:', adjustedData)
// props.onChange(adjustedData)
// }
// // Update field properties
// updateFieldProperties({ data: adjustedData })
// } catch (error) {
// // Handle error if necessary
// }
// }
// const handleBeforeChange = (changes: any, source: string) => {
// if (!changes || source === 'loadData') return
// changes.forEach(([row, col, oldValue, newValue]: any, index: number) => {
// if (
// props.runtime &&
// newValue &&
// typeof newValue === 'string' &&
// newValue.startsWith('=')
// ) {
// // Replace formula input with '#ERROR!' in runtime mode
// changes[index][3] = '#ERROR!'
// }
// })
// }
// const updateFieldProperties = (updatedMeta: any) => {
// if (!props.runtime) {
// formBuilderWorksheetActions?.setFieldConfigProperty(props.config?.id!, {
// meta: {
// data,
// formulas,
// mergeCellsConfig,
// ...updatedMeta,
// },
// })
// }
// }
// const cellsHandler = (row: any, col: any) => {
// const cellProperties = {} as Handsontable.CellProperties
// cellProperties.width = DEFAULT_COLUMN_WIDTH
// cellProperties.height = DEFAULT_ROW_HEIGHT
// // Determine if the cell is a formula cell
// const isFormulaCell = formulas.some(
// (formula) => formula.row === row && formula.col === col
// )
// // In runtime mode, prevent editing formula cells
// if (props.runtime && isFormulaCell) {
// cellProperties.readOnly = true
// } else {
// cellProperties.readOnly = false
// }
// cellProperties.renderer = function (
// instance,
// td,
// row,
// col,
// prop,
// value,
// cellProperties
// ) {
// td.style.border = ''
// td.style.color = ''
// td.style.backgroundColor = ''
// let cellValue = instance.getDataAtCell(row, col)
// if (
// typeof cellValue === 'string' &&
// (cellValue.startsWith('#ERROR!') ||
// cellValue.startsWith('#NAME?') ||
// cellValue.startsWith('#DIV/0'))
// ) {
// td.style.border = '1px solid red'
// td.style.color = 'red'
// }
// Handsontable.renderers.TextRenderer.call(
// this,
// instance,
// td,
// row,
// col,
// prop,
// value,
// cellProperties
// )
// }
// return cellProperties
// }
// const handleMergeCells = () => {
// const hotInstance = hotTableRef.current?.hotInstance
// const selected = hotInstance?.getSelected()
// if (selected) {
// const [startRow, startCol, endRow, endCol] = selected[0]
// const newMerge = {
// row: startRow,
// col: startCol,
// rowspan: endRow - startRow + 1,
// colspan: endCol - startCol + 1,
// }
// const updatedMergeCellsConfig = [...mergeCellsConfig, newMerge]
// hotInstance.updateSettings({ mergeCells: updatedMergeCellsConfig })
// setMergeCellsConfig(updatedMergeCellsConfig)
// updateFieldProperties({
// data,
// cellFormats,
// mergeCellsConfig: updatedMergeCellsConfig,
// })
// }
// }
// const handleUnmergeCells = () => {
// const hotInstance = hotTableRef.current?.hotInstance
// const selected = hotInstance?.getSelected()
// if (selected) {
// const [startRow, startCol] = selected[0]
// const mergeCellsPlugin = hotInstance.getPlugin('mergeCells')
// mergeCellsPlugin.unmerge(startRow, startCol)
// const updatedMergeCellsConfig = mergeCellsConfig.filter(
// (cell) => cell.row !== startRow || cell.col !== startCol
// )
// hotInstance.updateSettings({ mergeCells: updatedMergeCellsConfig })
// setMergeCellsConfig(updatedMergeCellsConfig)
// updateFieldProperties({
// data,
// cellFormats,
// mergeCellsConfig: updatedMergeCellsConfig,
// })
// }
// }
// const handleSetAsDate = () => {
// const selected = hotTableRef.current?.hotInstance.getSelected()
// if (selected) {
// const [row, col] = selected[0]
// const newFormats = { ...cellFormats, [`${row}-${col}`]: 'date' as const }
// setCellFormats(newFormats)
// updateFieldProperties({ data, cellFormats: newFormats, mergeCellsConfig })
// }
// }
// const handleSetAsCurrency = () => {
// const selected = hotTableRef.current?.hotInstance.getSelected()
// if (selected) {
// const [row, col] = selected[0]
// const newFormats = {
// ...cellFormats,
// [`${row}-${col}`]: 'currency' as const,
// }
// setCellFormats(newFormats)
// updateFieldProperties({ data, cellFormats: newFormats, mergeCellsConfig })
// }
// }
// return (
// <div
// ref={containerRef}
// className="h-[400px] w-1/2 resize-x overflow-auto border md:w-full"
// style={{ minWidth: '50%', maxWidth: '100%', resize: 'horizontal' }}
// onMouseDown={(e) => e.stopPropagation()}
// >
// <HotTable
// id="mySpreadsheet"
// data={data}
// colHeaders={true}
// rowHeaders={true}
// width="100%"
// height="100%"
// selectionMode="multiple"
// copyPaste={true}
// contextMenu={{
// items: {
// row_above: {},
// row_below: {},
// col_left: {},
// col_right: {},
// remove_row: {},
// remove_col: {},
// clear_column: {},
// mergeCells: { name: 'Merge Cells', callback: handleMergeCells },
// unmergeCells: {
// name: 'Unmerge Cells',
// callback: handleUnmergeCells,
// },
// set_as_date: { name: 'Set as Date', callback: handleSetAsDate },
// set_as_currency: {
// name: 'Set as Currency',
// callback: handleSetAsCurrency,
// },
// },
// }}
// ref={hotTableRef}
// afterChange={handleAfterChange}
// beforeChange={handleBeforeChange}
// persistentState={true}
// licenseKey="non-commercial-and-evaluation"
// manualColumnResize={false}
// manualRowResize={false}
// autoColumnSize={false}
// autoRowSize={false}
// stretchH="all"
// mergeCells={mergeCellsConfig}
// formulas={{ engine: hyperformulaInstance }}
// cells={cellsHandler}
// colWidths={DEFAULT_COLUMN_WIDTH}
// rowHeights={DEFAULT_ROW_HEIGHT}
// />
// </div>
// )
// }
// export default registerControl<Properties>(SpreadsheetControl, {
// noDisplayLabel: true,
// })
import React, { useRef, useState, useEffect } from 'react'
import { HotTable } from '@handsontable/react'
import 'handsontable/dist/handsontable.full.min.css'
import Handsontable from 'handsontable'
import {
registerControl,
RegisterControlProps,
RegisteredControlProperties,
} from '../hoc/registerControl'
import { hyperformulaInstance, sheetId } from './utils/hyperformulaConfig'
import { createSpreadsheetDefaultValue } from '../../FormBuilderFieldProperties/Spreadsheet/constants'
import { SpreadsheetProperties } from '@components/features/FormBuilder/FormBuilderFieldProperties/Spreadsheet/types'
import { registerAllCellTypes } from 'handsontable/cellTypes'
import { registerAllPlugins } from 'handsontable/plugins'
import { useFormBuilderWorksheetGrid } from '../../hooks/useFormBuilderWorksheetGrid'
import { useFormBuilder } from '../../hooks/useFormBuilder'
import { useFormService } from '../../hooks/useFormService'
registerAllCellTypes()
registerAllPlugins()
type Properties = RegisteredControlProperties &
SpreadsheetProperties & {
externalVariables?: {
[key: string]: {
propertyName?: string
defaultValue?: number
}
}
}
const SpreadsheetControl: React.FC<RegisterControlProps<Properties>> = (
props
) => {
const hotTableRef = useRef<any>(null)
const containerRef = useRef<HTMLDivElement>(null)
const { rows = 1, columns = 1 } = props.properties || {}
const DEFAULT_COLUMN_WIDTH = 100
const DEFAULT_ROW_HEIGHT = 25
const defaultMeta = {
data: createSpreadsheetDefaultValue(rows, columns),
cellFormats: {} as { [key: string]: 'date' | 'currency' | undefined },
mergeCellsConfig: [],
formulas: [],
}
const meta = props.properties?.meta || defaultMeta
const { actions: formBuilderWorksheetActions } = useFormBuilderWorksheetGrid()
const { actions, data: formBuilderData } = useFormBuilder()
const { watch } = useFormService()
const formData = watch()
const [data, setData] = useState(
() => props?.value ?? JSON.parse(JSON.stringify(meta.data))
)
const [cellFormats, setCellFormats] = useState(meta.cellFormats)
const [mergeCellsConfig, setMergeCellsConfig] = useState(
meta.mergeCellsConfig
)
const [formulas, setFormulas] = useState<
{ row: number; col: number; formula: string }[]
>(meta.formulas || [])
useEffect(() => {
if (props.properties?.meta) {
const {
data: metaData,
formulas: metaFormulas = [],
mergeCellsConfig: metaMergeCells,
} = props.properties.meta
const initialData = props.value ?? metaData
const isDataDifferent =
JSON.stringify(initialData) !== JSON.stringify(data)
const isFormulasDifferent =
JSON.stringify(metaFormulas) !== JSON.stringify(formulas)
const isMergeDifferent =
JSON.stringify(metaMergeCells) !== JSON.stringify(mergeCellsConfig)
if (isDataDifferent || isFormulasDifferent || isMergeDifferent) {
setData(initialData)
setFormulas(metaFormulas)
setMergeCellsConfig(metaMergeCells)
}
metaFormulas.forEach(({ row, col, formula }) => {
try {
hyperformulaInstance.setCellContents({ sheet: sheetId, row, col }, [
[formula],
])
} catch (error) {
// Handle error if necessary
}
})
hyperformulaInstance.rebuildAndRecalculate()
}
}, [props.properties?.meta, props.value]) // Only re-run if meta or value changes
useEffect(() => {
const syncFormBuilderData = (sourceData: any) => {
if (sourceData && Object.keys(sourceData).length > 0) {
const existingExpressions = hyperformulaInstance.listNamedExpressions()
Object.entries(sourceData).forEach(([key, value]) => {
if (!existingExpressions.includes(key)) {
hyperformulaInstance.addNamedExpression(
`${key}`,
Number(value) || 0
)
} else {
hyperformulaInstance.changeNamedExpression(
`${key}`,
Number(value) || 0
)
}
})
try {
hyperformulaInstance.rebuildAndRecalculate()
if (hotTableRef.current?.hotInstance && typeof sheetId === 'number') {
const updatedData = hyperformulaInstance.getSheetSerialized(sheetId)
const adjustedData = adjustDataDimensions(
updatedData,
rows,
columns
)
// Only update if data actually changed
if (JSON.stringify(adjustedData) !== JSON.stringify(data)) {
hotTableRef.current.hotInstance.loadData(adjustedData)
setData(adjustedData)
}
}
} catch (error) {
console.error('Error syncing form builder data:', error)
}
}
}
syncFormBuilderData(formBuilderData)
syncFormBuilderData(formData)
}, [formBuilderData, formData, data, rows, columns])
useEffect(() => {
const adjustedData = adjustDataDimensions(data, rows, columns)
if (JSON.stringify(data) !== JSON.stringify(adjustedData)) {
setData(adjustedData)
}
}, [rows, columns, data])
const adjustDataDimensions = (
currentData: any[][],
rows: number,
columns: number
) => {
const adjustedData = currentData.map((row) => [...row])
while (adjustedData.length < rows) {
adjustedData.push(Array(columns).fill(null))
}
adjustedData.length = rows
adjustedData.forEach((row) => {
while (row.length < columns) {
row.push(null)
}
row.length = columns
})
return adjustedData
}
useEffect(() => {
if (hotTableRef.current?.hotInstance) {
hotTableRef.current.hotInstance.render()
}
}, [data])
const handleAfterChange = (changes: any, source: string) => {
if (!changes || source === 'loadData') return
const validSheetId = sheetId ?? 0
let updatedFormulas = [...formulas]
changes.forEach(([row, col, oldValue, newValue]: any) => {
const formulaIndex = updatedFormulas.findIndex(
(formula) => formula.row === row && formula.col === col
)
if (
newValue &&
typeof newValue === 'string' &&
newValue.startsWith('=')
) {
if (props.runtime) {
// Runtime mode - do not process formulas
} else {
try {
if (formulaIndex !== -1) {
updatedFormulas[formulaIndex].formula = newValue
} else {
updatedFormulas.push({ row, col, formula: newValue })
}
setFormulas(updatedFormulas)
hyperformulaInstance.setCellContents(
{ sheet: validSheetId, row, col },
[[newValue]]
)
} catch (error) {}
}
} else {
// Not a formula cell
if (formulaIndex !== -1) {
updatedFormulas.splice(formulaIndex, 1)
setFormulas(updatedFormulas)
}
try {
hyperformulaInstance.setCellContents(
{ sheet: validSheetId, row, col },
[[newValue]]
)
} catch (error) {}
}
})
try {
hyperformulaInstance.rebuildAndRecalculate()
const updatedData = hyperformulaInstance.getSheetSerialized(validSheetId)
const adjustedData = adjustDataDimensions(updatedData, rows, columns)
if (JSON.stringify(adjustedData) !== JSON.stringify(data)) {
setData(adjustedData)
if (props.onChange) {
props.onChange(adjustedData)
}
updateFieldProperties({ data: adjustedData })
}
} catch (error) {}
}
const handleBeforeChange = (changes: any, source: string) => {
if (!changes || source === 'loadData') return
changes.forEach(([row, col, oldValue, newValue]: any, index: number) => {
if (
props.runtime &&
newValue &&
typeof newValue === 'string' &&
newValue.startsWith('=')
) {
changes[index][3] = '#ERROR!'
}
})
}
const updateFieldProperties = (updatedMeta: any) => {
if (!props.runtime) {
// Check if the updated meta actually differs
const newMeta = {
meta: {
data,
formulas,
mergeCellsConfig,
...updatedMeta,
},
}
// Only update if something changed
if (
JSON.stringify(props.properties?.meta?.data) !==
JSON.stringify(newMeta.meta.data) ||
JSON.stringify(props.properties?.meta?.formulas) !==
JSON.stringify(newMeta.meta.formulas) ||
JSON.stringify(props.properties?.meta?.mergeCellsConfig) !==
JSON.stringify(newMeta.meta.mergeCellsConfig)
) {
formBuilderWorksheetActions?.setFieldConfigProperty(
props.config?.id!,
newMeta
)
}
}
}
const cellsHandler = (row: any, col: any) => {
const cellProperties = {} as Handsontable.CellProperties
cellProperties.width = DEFAULT_COLUMN_WIDTH
cellProperties.height = DEFAULT_ROW_HEIGHT
const isFormulaCell = formulas.some(
(formula) => formula.row === row && formula.col === col
)
cellProperties.readOnly = props.runtime && isFormulaCell
cellProperties.renderer = function (
instance,
td,
row,
col,
prop,
value,
cellProperties
) {
td.style.border = ''
td.style.color = ''
td.style.backgroundColor = ''
let cellValue = instance.getDataAtCell(row, col)
if (
typeof cellValue === 'string' &&
(cellValue.startsWith('#ERROR!') ||
cellValue.startsWith('#NAME?') ||
cellValue.startsWith('#DIV/0'))
) {
td.style.border = '1px solid red'
td.style.color = 'red'
}
Handsontable.renderers.TextRenderer.call(
this,
instance,
td,
row,
col,
prop,
value,
cellProperties
)
}
return cellProperties
}
const handleMergeCells = () => {
const hotInstance = hotTableRef.current?.hotInstance
const selected = hotInstance?.getSelected()
if (selected) {
const [startRow, startCol, endRow, endCol] = selected[0]
const newMerge = {
row: startRow,
col: startCol,
rowspan: endRow - startRow + 1,
colspan: endCol - startCol + 1,
}
const updatedMergeCellsConfig = [...mergeCellsConfig, newMerge]
hotInstance.updateSettings({ mergeCells: updatedMergeCellsConfig })
setMergeCellsConfig(updatedMergeCellsConfig)
updateFieldProperties({
data,
cellFormats,
mergeCellsConfig: updatedMergeCellsConfig,
})
}
}
const handleUnmergeCells = () => {
const hotInstance = hotTableRef.current?.hotInstance
const selected = hotInstance?.getSelected()
if (selected) {
const [startRow, startCol] = selected[0]
const mergeCellsPlugin = hotInstance.getPlugin('mergeCells')
mergeCellsPlugin.unmerge(startRow, startCol)
const updatedMergeCellsConfig = mergeCellsConfig.filter(
(cell) => cell.row !== startRow || cell.col !== startCol
)
hotInstance.updateSettings({ mergeCells: updatedMergeCellsConfig })
setMergeCellsConfig(updatedMergeCellsConfig)
updateFieldProperties({
data,
cellFormats,
mergeCellsConfig: updatedMergeCellsConfig,
})
}
}
const handleSetAsDate = () => {
const selected = hotTableRef.current?.hotInstance.getSelected()
if (selected) {
const [row, col] = selected[0]
const newFormats = { ...cellFormats, [`${row}-${col}`]: 'date' as const }
setCellFormats(newFormats)
updateFieldProperties({ data, cellFormats: newFormats, mergeCellsConfig })
}
}
const handleSetAsCurrency = () => {
const selected = hotTableRef.current?.hotInstance.getSelected()
if (selected) {
const [row, col] = selected[0]
const newFormats = {
...cellFormats,
[`${row}-${col}`]: 'currency' as const,
}
setCellFormats(newFormats)
updateFieldProperties({ data, cellFormats: newFormats, mergeCellsConfig })
}
}
return (
<div
ref={containerRef}
className="h-[400px] w-1/2 resize-x overflow-auto border md:w-full"
style={{ minWidth: '50%', maxWidth: '100%', resize: 'horizontal' }}
onMouseDown={(e) => e.stopPropagation()}
>
<HotTable
id="mySpreadsheet"
data={data}
colHeaders={true}
rowHeaders={true}
width="100%"
height="100%"
selectionMode="multiple"
copyPaste={true}
contextMenu={{
items: {
row_above: {},
row_below: {},
col_left: {},
col_right: {},
remove_row: {},
remove_col: {},
clear_column: {},
mergeCells: { name: 'Merge Cells', callback: handleMergeCells },
unmergeCells: {
name: 'Unmerge Cells',
callback: handleUnmergeCells,
},
set_as_date: { name: 'Set as Date', callback: handleSetAsDate },
set_as_currency: {
name: 'Set as Currency',
callback: handleSetAsCurrency,
},
},
}}
ref={hotTableRef}
afterChange={handleAfterChange}
beforeChange={handleBeforeChange}
persistentState={true}
licenseKey="non-commercial-and-evaluation"
manualColumnResize={false}
manualRowResize={false}
autoColumnSize={false}
autoRowSize={false}
stretchH="all"
mergeCells={mergeCellsConfig}
formulas={{ engine: hyperformulaInstance }}
cells={cellsHandler}
colWidths={DEFAULT_COLUMN_WIDTH}
rowHeights={DEFAULT_ROW_HEIGHT}
/>
</div>
)
}
export default registerControl<Properties>(SpreadsheetControl, {
noDisplayLabel: true,
})