// 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: [],
// }
// 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(() => JSON.parse(JSON.stringify(meta.data)))
// const [cellFormats, setCellFormats] = useState(meta.cellFormats)
// const [mergeCellsConfig, setMergeCellsConfig] = useState(
// meta.mergeCellsConfig
// )
// useEffect(() => {
// if (formBuilderData?.fields) {
// const existingExpressions = hyperformulaInstance.listNamedExpressions()
// formBuilderData.fields.forEach((field) => {
// const propertyName = field.properties?.propertyName
// const defaultValue = Number(field.properties?.defaultValue || 0)
// if (propertyName) {
// if (!existingExpressions.includes(propertyName)) {
// hyperformulaInstance.addNamedExpression(propertyName, defaultValue)
// } else {
// hyperformulaInstance.changeNamedExpression(
// propertyName,
// defaultValue
// )
// }
// }
// })
// hyperformulaInstance.rebuildAndRecalculate()
// }
// }, [formBuilderData])
// useEffect(() => {
// if (props.externalVariables) {
// 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(
// `Error adding named expression ${variableName}:`,
// error
// )
// }
// } else {
// try {
// hyperformulaInstance.changeNamedExpression(
// variableName,
// value,
// sheetId
// )
// } catch (error) {
// console.error(
// `Error updating named expression ${variableName}:`,
// error
// )
// }
// }
// })
// try {
// hyperformulaInstance.rebuildAndRecalculate()
// } catch (error) {
// console.error(
// 'Error during HyperFormula rebuild and recalculation:',
// error
// )
// }
// }
// }, [props.externalVariables])
// 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)
// }
// })
// hyperformulaInstance.rebuildAndRecalculate()
// }
// }, [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) {
// console.error(
// `Error adding named expression for ${propertyName}: ${error}`
// )
// }
// } else if (propertyName) {
// try {
// hyperformulaInstance.changeNamedExpression(
// propertyName,
// defaultValue
// )
// } catch (error) {
// console.error(
// `Error updating named expression for ${propertyName}: ${error}`
// )
// }
// }
// })
// try {
// hyperformulaInstance.rebuildAndRecalculate()
// } catch (error) {
// console.error(
// 'Error during HyperFormula rebuild and recalculation:',
// error
// )
// }
// }
// }, [formBuilderData?.fields])
// 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()
// } 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
// changes.forEach(([row, col, oldValue, newValue]: any) => {
// if (
// newValue &&
// typeof newValue === 'string' &&
// newValue.startsWith('=')
// ) {
// try {
// hyperformulaInstance.setCellContents(
// { sheet: validSheetId, row, col },
// [[newValue]]
// )
// } catch (error) {
// console.error('Error setting formula:', error)
// }
// }
// })
// const updatedData = hotTableRef.current?.hotInstance?.getData()
// if (JSON.stringify(data) !== JSON.stringify(updatedData)) {
// setData(updatedData)
// updateFieldProperties({ data: updatedData })
// }
// }
// 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: Partial<SpreadsheetProperties['meta']>
// ) => {
// if (!props?.runtime) {
// const safeData = data ?? []
// formBuilderWorksheetActions?.setFieldConfigProperty(props.config?.id!, {
// meta: {
// data: [...safeData.map((row: any) => (row ? [...row] : []))],
// cellFormats: { ...cellFormats },
// mergeCellsConfig: [...mergeCellsConfig],
// ...updatedMeta,
// },
// } as Properties)
// }
// }
// const cellsHandler = (row: number, col: number) => {
// const cellProperties = {} as Handsontable.CellProperties
// cellProperties.width = DEFAULT_COLUMN_WIDTH
// cellProperties.height = DEFAULT_ROW_HEIGHT
// // Check if the cell format exists, or default to undefined
// 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,
// })
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// 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: [],
// }
// 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(() => JSON.parse(JSON.stringify(meta.data)))
// const [cellFormats, setCellFormats] = useState(meta.cellFormats)
// const [mergeCellsConfig, setMergeCellsConfig] = useState(
// meta.mergeCellsConfig
// )
// useEffect(() => {
// console.log('Initializing meta from props:', props.properties?.meta)
// const initialMeta = props.properties?.meta || defaultMeta
// setData(JSON.parse(JSON.stringify(initialMeta.data)))
// setCellFormats(initialMeta.cellFormats)
// setMergeCellsConfig(initialMeta.mergeCellsConfig)
// }, [props.properties?.meta])
// useEffect(() => {
// console.log('Data updated:', data)
// }, [data])
// useEffect(() => {
// console.log('Cell formats updated:', cellFormats)
// }, [cellFormats])
// useEffect(() => {
// console.log('Merge cells configuration updated:', mergeCellsConfig)
// }, [mergeCellsConfig])
// useEffect(() => {
// if (formBuilderData?.fields) {
// const existingExpressions = hyperformulaInstance.listNamedExpressions()
// formBuilderData.fields.forEach((field) => {
// const propertyName = field.properties?.propertyName
// const defaultValue = Number(field.properties?.defaultValue || 0)
// if (propertyName) {
// if (!existingExpressions.includes(propertyName)) {
// hyperformulaInstance.addNamedExpression(propertyName, defaultValue)
// } else {
// hyperformulaInstance.changeNamedExpression(
// propertyName,
// defaultValue
// )
// }
// }
// })
// hyperformulaInstance.rebuildAndRecalculate()
// }
// }, [formBuilderData])
// useEffect(() => {
// if (props.externalVariables) {
// const existingExpressions = new Set(
// hyperformulaInstance.listNamedExpressions()
// )
// Object.entries(props.externalVariables).forEach(([key, variable]) => {
// const typedVariable = variable as {
// propertyName?: string
// defaultValue?: number
// }
// const variableName = typedVariable.propertyName || key
// const value = Number(typedVariable.defaultValue || 0)
// if (!existingExpressions.has(variableName)) {
// hyperformulaInstance.addNamedExpression(variableName, value, sheetId)
// } else {
// hyperformulaInstance.changeNamedExpression(
// variableName,
// value,
// sheetId
// )
// }
// })
// hyperformulaInstance.rebuildAndRecalculate()
// syncSpreadsheetWithEngine()
// }
// }, [props.externalVariables])
// 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)
// }
// })
// hyperformulaInstance.rebuildAndRecalculate()
// }
// }, [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) {
// console.error(
// `Error adding named expression for ${propertyName}: ${error}`
// )
// }
// } else if (propertyName) {
// try {
// hyperformulaInstance.changeNamedExpression(
// propertyName,
// defaultValue
// )
// } catch (error) {
// console.error(
// `Error updating named expression for ${propertyName}: ${error}`
// )
// }
// }
// })
// try {
// hyperformulaInstance.rebuildAndRecalculate()
// } catch (error) {
// console.error(
// 'Error during HyperFormula rebuild and recalculation:',
// error
// )
// }
// }
// }, [formBuilderData?.fields])
// 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()
// } catch (error) {}
// }
// }, [formData])
// useEffect(() => {
// const adjustedData = adjustDataDimensions(data, rows, columns)
// if (JSON.stringify(data) !== JSON.stringify(adjustedData)) {
// setData(adjustedData)
// updateFieldProperties({ data: 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 syncSpreadsheetWithEngine = () => {
// if (hotTableRef.current?.hotInstance) {
// const updatedData = hyperformulaInstance.getSheetValues(sheetId)
// console.log('Syncing spreadsheet with engine. Updated Data:', updatedData)
// hotTableRef.current.hotInstance.loadData(updatedData)
// }
// }
// const handleAfterChange = (changes: any, source: string) => {
// console.log('After Change Triggered. Source:', source)
// console.log('Changes:', changes)
// if (!changes || source === 'loadData') return
// const validSheetId = sheetId ?? 0
// changes.forEach(([row, col, oldValue, newValue]: any) => {
// console.log(
// `Updating cell [${row}, ${col}] from ${oldValue} to ${newValue}`
// )
// if (
// newValue &&
// typeof newValue === 'string' &&
// newValue.startsWith('=')
// ) {
// hyperformulaInstance.setCellContents(
// { sheet: validSheetId, row, col },
// [[newValue]]
// )
// } else {
// hyperformulaInstance.setCellContents(
// { sheet: validSheetId, row, col },
// [[newValue || null]]
// )
// }
// })
// // Recalculate and sync data
// hyperformulaInstance.rebuildAndRecalculate()
// console.log(
// 'Recalculated HyperFormula Instance:',
// hyperformulaInstance.getSheetValues(sheetId)
// )
// syncSpreadsheetWithEngine()
// }
// 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: Partial<SpreadsheetProperties['meta']>
// ) => {
// console.log('Updating field properties with meta:', updatedMeta)
// console.log('Current data:', data)
// if (!props.runtime) {
// formBuilderWorksheetActions?.setFieldConfigProperty(props.config?.id!, {
// meta: {
// data: [...data.map((row: any) => (row ? [...row] : []))],
// cellFormats: { ...cellFormats },
// mergeCellsConfig: [...mergeCellsConfig],
// ...updatedMeta,
// },
// } as Properties)
// }
// }
// const cellsHandler = (row: number, col: number) => {
// const cellProperties = {} as Handsontable.CellProperties
// cellProperties.width = DEFAULT_COLUMN_WIDTH
// cellProperties.height = DEFAULT_ROW_HEIGHT
// // Check if the cell format exists, or default to undefined
// 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,
// })
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
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: [],
}
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(() => JSON.parse(JSON.stringify(meta.data)))
const [cellFormats, setCellFormats] = useState(meta.cellFormats)
const [mergeCellsConfig, setMergeCellsConfig] = useState(
meta.mergeCellsConfig
)
useEffect(() => {
if (props.externalVariables) {
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(
`Error adding named expression ${variableName}:`,
error
)
}
} else {
try {
hyperformulaInstance.changeNamedExpression(
variableName,
value,
sheetId
)
} catch (error) {
console.error(
`Error updating named expression ${variableName}:`,
error
)
}
}
})
try {
hyperformulaInstance.rebuildAndRecalculate()
syncSpreadsheetWithEngine()
} catch (error) {
console.error(
'Error during HyperFormula rebuild and recalculation:',
error
)
}
}
}, [props.externalVariables])
const syncSpreadsheetWithEngine = () => {
if (hotTableRef.current?.hotInstance) {
const updatedData = hyperformulaInstance.getSheetValues(sheetId)
console.log('Syncing spreadsheet with engine. Updated Data:', updatedData)
hotTableRef.current.hotInstance.loadData(updatedData)
}
}
useEffect(() => {
const syncSpreadsheetWithEngine = () => {
if (hotTableRef.current?.hotInstance) {
const updatedData = hyperformulaInstance.getSheetValues(sheetId)
console.log(
'Syncing spreadsheet with engine. Updated Data:',
updatedData
)
hotTableRef.current.hotInstance.loadData(updatedData)
}
}
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)
}
})
hyperformulaInstance.rebuildAndRecalculate()
syncSpreadsheetWithEngine()
}
}, [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) {
console.error(
`Error adding named expression for ${propertyName}: ${error}`
)
}
} else if (propertyName) {
try {
hyperformulaInstance.changeNamedExpression(
propertyName,
defaultValue
)
} catch (error) {
console.error(
`Error updating named expression for ${propertyName}: ${error}`
)
}
}
})
try {
hyperformulaInstance.rebuildAndRecalculate()
} catch (error) {
console.error(
'Error during HyperFormula rebuild and recalculation:',
error
)
}
}
}, [formBuilderData?.fields])
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()
} 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
changes.forEach(([row, col, oldValue, newValue]: any) => {
if (
newValue &&
typeof newValue === 'string' &&
newValue.startsWith('=')
) {
try {
hyperformulaInstance.setCellContents(
{ sheet: validSheetId, row, col },
[[newValue]]
)
} catch (error) {
console.error('Error setting formula:', error)
}
}
})
try {
hyperformulaInstance.rebuildAndRecalculate()
syncSpreadsheetWithEngine() // Ensure the spreadsheet reflects changes
} catch (error) {
console.error('Error recalculating formulas:', error)
}
}
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: Partial<SpreadsheetProperties['meta']>
) => {
if (!props?.runtime) {
const safeData = data ?? []
formBuilderWorksheetActions?.setFieldConfigProperty(props.config?.id!, {
meta: {
data: [...safeData.map((row: any) => (row ? [...row] : []))],
cellFormats: { ...cellFormats },
mergeCellsConfig: [...mergeCellsConfig],
...updatedMeta,
},
} as Properties)
}
}
const cellsHandler = (row: number, col: number) => {
const cellProperties = {} as Handsontable.CellProperties
cellProperties.width = DEFAULT_COLUMN_WIDTH
cellProperties.height = DEFAULT_ROW_HEIGHT
// Check if the cell format exists, or default to undefined
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,
})
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter