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 [count, setCount] = useState(0)
const [formulas, setFormulas] = useState<
{ row: number; col: number; formula: string }[]
>(meta.formulas || [])
useEffect(() => {
if (props.runtime && 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) {}
})
hyperformulaInstance.rebuildAndRecalculate()
}
}, [props.runtime, props.properties?.meta])
console.log('Meta:', props)
// useEffect(() => {
// if (
// !props.externalVariables ||
// Object.keys(props.externalVariables).length === 0
// ) {
// return
// }
// const existingExpressions = new Set(
// hyperformulaInstance.listNamedExpressions()
// )
// Object.entries(props.externalVariables).forEach(([key, property]) => {
// const typedProperty = property as {
// propertyName?: string
// defaultValue?: number
// }
// const variableName = typedProperty.propertyName || key
// const value = Number(typedProperty.defaultValue || 0)
// if (!existingExpressions.has(variableName)) {
// try {
// hyperformulaInstance.addNamedExpression(variableName, value, sheetId)
// existingExpressions.add(variableName)
// } catch (error) {
// console.error(
// `Failed to add named expression: ${variableName}`,
// error
// )
// }
// } else {
// try {
// hyperformulaInstance.changeNamedExpression(
// variableName,
// value,
// sheetId
// )
// } catch (error) {
// console.error(
// `Failed to update named expression: ${variableName}`,
// error
// )
// }
// }
// })
// try {
// hyperformulaInstance.rebuildAndRecalculate()
// syncSpreadsheetWithEngine()
// } catch (error) {
// console.error('Error recalculating HyperFormula instance:', error)
// }
// }, [props.externalVariables])
// console.log('External Variable:', props.externalVariables)
const syncSpreadsheetWithEngine = () => {
if (hotTableRef.current?.hotInstance && typeof sheetId === 'number') {
const updatedData = hyperformulaInstance.getSheetValues(sheetId)
console.log('UpdatedData:', updatedData)
try {
hotTableRef.current.hotInstance.loadData(updatedData)
updateFieldProperties({ data: updatedData })
} catch (error) {}
}
}
// 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)
// setData(recalculatedData)
// updateFieldProperties({ data: recalculatedData })
// } else {
// }
// } catch (error) {}
// }
// }, [formBuilderData])
// useEffect(() => {
// if (formBuilderData?.fields) {
// const existingExpressions = new Set(
// hyperformulaInstance.listNamedExpressions()
// )
// formBuilderData.fields.forEach((field) => {
// const propertyName = field.properties?.propertyName
// const defaultValue = Number(field.properties?.defaultValue || 0)
// if (propertyName && !existingExpressions.has(propertyName)) {
// try {
// hyperformulaInstance.addNamedExpression(propertyName, defaultValue)
// existingExpressions.add(propertyName)
// } catch (error) {}
// } else if (propertyName) {
// try {
// hyperformulaInstance.changeNamedExpression(
// propertyName,
// defaultValue
// )
// } catch (error) {}
// }
// })
// try {
// hyperformulaInstance.rebuildAndRecalculate()
// } catch (error) {}
// }
// }, [formBuilderData?.fields])
// useEffect(() => {
// const handler = (changes: any) => {
// console.log('changes', changes)
// hyperformulaInstance.rebuildAndRecalculate()
// }
// hyperformulaInstance.on('valuesUpdated', handler)
// return () => {
// hyperformulaInstance.off('valuesUpdated', handler)
// }
// }, [])
useEffect(() => {
if (formData && Object.keys(formData).length > 0) {
console.log('Form Data:', formData)
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
)
}
hyperformulaInstance.getNamedExpressionValue(key)
console.log(
`get named ${key}`,
hyperformulaInstance.getNamedExpressionValue(key)
)
// hyperformulaInstance.setCellContents(
// { sheet: sheetId, row: 0, col: 0 },
// [[Number(value) || 0]]
// )
})
hyperformulaInstance.rebuildAndRecalculate()
// setCount((prev) => prev + 1)
try {
// setTimeout(() => {
// hyperformulaInstance.rebuildAndRecalculate()
// }, 100)
} catch (error) {}
}
}, [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 newData = currentData.map((row) => [...row])
if (newData.length < rows) {
for (let i = newData.length; i < rows; i++) {
newData.push(Array(columns).fill(null))
}
} else if (newData.length > rows) {
newData.length = rows
}
newData.forEach((row) => {
if (row.length < columns) {
row.push(...Array(columns - row.length).fill(null))
} else if (row.length > columns) {
row.length = columns
}
})
return newData
}
const handleAfterChange = (changes: any, source: string) => {
if (!changes || source === 'loadData') return
const validSheetId = sheetId ?? 0
const updatedFormulas = [...formulas]
changes.forEach(([row, col, oldValue, newValue]: any) => {
if (
newValue &&
typeof newValue === 'string' &&
newValue.startsWith('=')
) {
try {
updatedFormulas.push({ row, col, formula: newValue })
setFormulas(updatedFormulas)
hyperformulaInstance.setCellContents(
{ sheet: validSheetId, row, col },
[[newValue]]
)
} catch (error) {}
}
})
const updatedData = hotTableRef.current?.hotInstance?.getData?.()
setData(updatedData)
if (props.onChange) {
props.onChange(updatedData)
}
try {
hyperformulaInstance.rebuildAndRecalculate()
// setData(hyperformulaInstance.getSheetValues)
// console.log(
// 'Get Sheet Value:',
// hyperformulaInstance.getSheetValues(sheetId!)
// )
} catch (error) {}
}
console.log('hotable', hotTableRef.current?.hotInstance?.getData?.())
// const handleAfterChange = (changes: any, source: string) => {
// if (!changes || source === 'loadData') return
// const validSheetId = sheetId ?? 0
// const updatedFormulas = [...formulas]
// changes.forEach(([row, col, oldValue, newValue]: any) => {
// if (
// newValue &&
// typeof newValue === 'string' &&
// newValue.startsWith('=')
// ) {
// try {
// updatedFormulas.push({ row, col, formula: newValue })
// setFormulas(updatedFormulas)
// // hyperformulaInstance.setCellContents(
// // { sheet: validSheetId, row, col },
// // [[newValue]]
// // )
// } catch (error) {
// console.error('Error setting cell contents:', error)
// }
// }
// })
// try {
// hyperformulaInstance.rebuildAndRecalculate()
// const updatedData = hotTableRef.current?.hotInstance?.getData?.()
// setData(updatedData)
// if (props.onChange) {
// props.onChange(updatedData)
// }
// } catch (error) {
// console.error('Error rebuilding and recalculating formulas:', error)
// }
// }
console.log('hotable', hotTableRef.current?.hotInstance?.getData?.())
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 })
}
}
const updateFieldProperties = (updatedMeta: any) => {
formBuilderWorksheetActions?.setFieldConfigProperty(props.config?.id!, {
meta: {
data,
formulas,
mergeCellsConfig,
...updatedMeta,
},
})
}
const cellsHandler = (row: number, col: number) => {
const cellProperties = {} as Handsontable.CellProperties
cellProperties.width = DEFAULT_COLUMN_WIDTH
cellProperties.height = DEFAULT_ROW_HEIGHT
const format = cellFormats?.[`${row}-${col}`]
if (format === 'date') {
cellProperties.renderer = (
instance,
td,
row,
col,
prop,
value,
cellProperties
) =>
CustomContentRenderer(instance, td, row, col, () => {
const span = document.createElement('span')
span.innerText = reformatDate(
instance.getDataAtCell(row, col) as string
)
return span
})
} else if (format === 'currency') {
cellProperties.renderer = (
instance,
td,
row,
col,
prop,
value,
cellProperties
) =>
CustomContentRenderer(instance, td, row, col, () => {
const span = document.createElement('span')
span.innerText = reformatCurrency(
instance.getDataAtCell(row, col) as string
)
return span
})
}
return cellProperties
}
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}
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,
})