I have a table with sort(applied on first column) enabled and second column is type dropdown menu with filter enabled. If I filter something on second column and then add one row, weird behavior happened to the table as below described
1 Sometimes, the empty row move to top or bottom automatically after inserting.
2 If the new empty row in it’s right position and enters something in first column then it copy the content of previous row in that position.
All what I want is with filter and sort applied, insert rows working properly.
Below is my code and please help!!!
import React, { useState, useEffect, useRef, useCallback } from 'react';
import { Prompt } from 'react-router-dom';
import PropTypes from 'prop-types';
import { Button, notification } from 'antd';
import { HotTable } from '@handsontable/react';
import requirePermissions from '../../../common/auth/hoc/requirePermissions';
import { BASE_URL } from '../../../consts';
// HEADER_HEIGHT is from manual testing so the table doesn't scroll, as `.wtHider` in HotTable is often too tall.
const HEADER_HEIGHT = 80;
const CELL_HEIGHT = 22.4;
function HotTableView({
loadData,
transformData,
validateData,
saveData,
columns,
hiddenColumns,
headers,
handleChange,
initCell,
columnWidths = null,
}) {
const [hotData, setHotData] = useState([{}]);
const [readOnly, setReadOnly] = useState(false);
const [dirty, setDirty] = useState(false);
const [tableHeight, setTableHeight] = useState('600');
const [columnSorting, setColumnSorting] = useState({
initialConfig: {
// Ignore any hidden columns
column: hiddenColumns
? (() => {
const hiddenSet = new Set(hiddenColumns.columns);
for (let i = 0; ; i++) {
if (!hiddenSet.has(i)) return i;
}
})()
: 0,
sortOrder: 'asc',
// sortEmptyCells: false,
},
});
const [filterValue, setFilterValue] = useState({ column: -1, value: "" });
const hot = useRef(null);
const removedData = useRef([]);
const columnWidth = 200;
const initData = useCallback(() => {
const _getMore = async () => {
let nextUrl = BASE_URL;
let totalData = [];
let count = 0;
while (nextUrl) {
const url = new URL(nextUrl);
const response = await loadData(url.searchParams.get('page')).catch(error => {
notification.error({ message: `Unable to fetch data: ${error}` });
});
count = response.count;
if (response.results.length > 0) {
totalData = transformData
? totalData.concat(transformData(response.results))
: totalData.concat(response.results);
}
nextUrl = response.next;
}
if (count) {
setTableHeight((HEADER_HEIGHT + (count + 20) * CELL_HEIGHT).toString());
} else {
totalData = [{}];
}
setHotData(totalData);
};
return _getMore();
}, [loadData, transformData]);
const save = useCallback(() => {
setReadOnly(true);
const allEmptyRows = () => {
for (const d of hotData) {
if (Object.keys(d).length > 0) {
return false;
}
}
return true;
};
for (let row = 0; row < hotData.length; row++) {
for (const column of columns.map(({ data }) => data)) {
const cell = hot.current.hotInstance.getCell(
row,
hot.current.hotInstance.propToCol(column)
);
// getCell() returns null if the cell is not rendered on screen https://handsontable.com/docs/7.1.1/Core.html#getCell
if (!cell) {
break;
}
if (cell.classList.contains('htInvalid')) {
notification.warning({ message: "Can't save invalid or empty data" });
setReadOnly(false);
return;
}
}
}
if (allEmptyRows() && removedData.current.length === 0) {
return;
}
saveData(hotData, removedData.current)
.then(response => {
if (response.error) {
notification.error({ message: response.error });
setReadOnly(false);
return;
}
initData().then(() => {
setReadOnly(false);
setDirty(false);
notification.success({ message: 'Saved' });
});
})
.catch(() => {
notification.error({ message: 'Failed to save, please check your input' });
setReadOnly(false);
hot.current.hotInstance.validateCells();
});
}, [hot, hotData, initData, columns, saveData]);
useEffect(() => {
initData();
}, [initData]);
const onBeforeRemoveRow = useCallback(
(index, amount, physicalRows) => {
for (const row of physicalRows) {
if ('id' in hotData[row]) {
removedData.current = [...removedData.current, hotData[row].id];
}
}
},
[hotData]
);
const onAfterCreateRow = useCallback(
(index, amount) => {
if (filterValue.column !== -1 && filterValue.value !== '') {
hot.current.hotInstance.setDataAtCell(index, filterValue.column, filterValue.value);
}
// setTimeout makes sure we validate data when inserting a new row.
setTimeout(() => {
const { hotInstance } = hot.current;
setTimeout(() => {
hotInstance.validateRows(Array.from(Array(amount), (e, i) => i + index));
});
});
},
[hot, filterValue]
);
const onAfterRemoveRow = useCallback(() => {
setTimeout(() => {
hot.current.hotInstance.validateCells();
setDirty(true);
});
}, [hot]);
const onAfterValidate = useCallback(
(isValid, value, row, prop, source) => {
if (validateData) {
return validateData(isValid, value, row, prop, source, hot);
}
if (!isValid || (isValid && value !== 0 && !value)) {
return false;
}
return true;
},
[validateData]
);
const onAfterPaste = (data, coords) => {
for (let row = coords[0].startRow; row <= coords[0].endRow; row++) {
// col & dataCol are separate, as data excludes hidden columns, whilst setDataAtCell does not
for (
let col = coords[0].startCol, dataCol = 0;
col <= coords[0].endCol;
col++, dataCol++
) {
hot.current.hotInstance.setDataAtCell(
row,
col,
data[row - coords[0].startRow][dataCol].trim()
);
}
}
};
// set dirty has to be in beforeChange() instead of afterChange() which causes the first
// change not been validated and we do not know reason, it's handsontable internal thing.
const onBeforeChange = useCallback(
(changes, source) => {
if (changes === null && source === 'loadData') {
return;
}
if (handleChange) {
handleChange(changes, hot);
}
// calling setTimeout is required, otherwise when we insert a new row and hit tab, another row's values will be copied to the new row
setTimeout(() => setDirty(true));
},
[handleChange]
);
const onAfterRender = useCallback(() => {
if (!hot.current) {
return;
}
if (initCell) {
initCell(hot);
}
}, [initCell]);
const onBeforeFilter = conditionsStack => {
if (conditionsStack.length > 0 &&
conditionsStack[0].conditions &&
conditionsStack[0].conditions.length > 0 &&
conditionsStack[0].conditions[0].args &&
conditionsStack[0].conditions[0].args.length > 0 &&
conditionsStack[0].conditions[0].args[0].length > 0
) {
setFilterValue({
column: conditionsStack[0].column,
value: conditionsStack[0].conditions[0].args[0][0],
});
}
else {
setFilterValue({
column: -1,
value: '',
});
}
}
return (
<div id="hot-app">
<Prompt
when={dirty}
message="You have unsaved changes. Navigating away will destroy these changes. Proceed anyway?"
/>
<Button onClick={save}>Save</Button>
<HotTable
ref={hot}
data={hotData}
beforeChange={onBeforeChange}
beforeFilter={onBeforeFilter}
beforeRemoveRow={onBeforeRemoveRow}
afterRender={onAfterRender}
afterRemoveRow={onAfterRemoveRow}
afterCreateRow={onAfterCreateRow}
afterValidate={onAfterValidate}
afterPaste={onAfterPaste}
colHeaders={headers}
colWidths={
columnWidths
? columnWidths.map(c => c * columnWidth)
: new Array(columns.length).fill(columnWidth)
}
rowHeaders={false}
undo={false}
contextMenu={{
items: {
row_above: {
name: 'Insert row above',
},
row_below: {
name: 'Insert row below',
},
row_above_5: {
name: 'Insert 5 rows above',
callback: () => {
hot.current.hotInstance.alter(
'insert_row',
hot.current.hotInstance.getSelected()[0][0],
5
);
},
},
row_below_5: {
name: 'Insert 5 rows below',
callback: () => {
hot.current.hotInstance.alter(
'insert_row',
hot.current.hotInstance.getSelected()[0][0] + 1,
5
);
},
},
remove_row: {
name: 'Remove row',
},
copy: {
name: 'Copy',
},
},
}}
columnSorting={columnSorting}
afterLoadData={() => {
if (!hot.current) {
return;
}
// Restore user's custom sort after data is refreshed
// const columnSortPlugin = hot.current.hotInstance.getPlugin('columnSorting');
// columnSortPlugin.sort(columnSortPlugin.getSortConfig());
}}
afterColumnSort={() => {
// Remove the initial sort config, otherwise after saving, it goes back to it, and loses the column the user custom-sorted
setColumnSorting(true);
}}
readOnly={readOnly}
columns={columns}
hiddenColumns={hiddenColumns}
licenseKey="non-commercial-and-evaluation"
width={
columnWidth *
(columnWidths
? columnWidths.reduce((acc, currVal) => {
return acc + currVal;
}, 0)
: columns.length - (hiddenColumns ? hiddenColumns.columns.length : 0))
}
height={tableHeight}
filters
dropdownMenu={['filter_by_value', 'filter_action_bar']}
/>
</div>
);
}
HotTableView.propTypes = {
loadData: PropTypes.func.isRequired,
saveData: PropTypes.func.isRequired,
columns: PropTypes.array.isRequired,
hiddenColumns: PropTypes.object,
headers: PropTypes.array.isRequired,
validateData: PropTypes.func,
transformData: PropTypes.func,
handleChange: PropTypes.func,
initCell: PropTypes.func,
columnWidths: PropTypes.arrayOf(Number),
};
export default requirePermissions({ perm: 'is_admin' })(HotTableView);