We copied data from an Excel sheet and pasted it into PQGrid in React.
Cell values such as:
108,196,453 , (7,797,168)
80,275,926 , 106,509,820
188,472,379 , 98,712,652
are pasted correctly into the grid cells.(Excel sheet to pqGrid)
However, decimal values like 0.06 are not being pasted into the cells, although the value is printed in the console during the paste event (#value showing in console).
The issue occurs even though PQGrid has been properly integrated into the React project.
Below is the code that handles the paste validation and formatting logic.
beforeValidate: function (evt, ui) {
const { updateList } = ui;
if (ui.source === "paste") {
const grid = this;
// only keep updates to existing rows, ignore rows flagged as newRow:true
if (ui.source === "paste") {
ui.addList.length = 0; // Block all new row additions on paste
}
console.log(ui ,"ui-paste data")
if (!modifiedRowsRef.current) {
modifiedRowsRef.current = {};
}
const changedCols = [...new Set(
updateList?.flatMap(item => Object.keys(item.newRow))
)];
console.log(updateList , "updateList")
for (let i = 0; i < updateList.length; i++) {
const { newRow, rowData } = updateList;
// console.log(newRow , "newRow")
// console.log(rowData , "rowData")
const rowId = rowData?.Id;
for (const dataIndx in newRow) {
if (!newRow.hasOwnProperty(dataIndx)) continue;
const col = grid.getColumn({ dataIndx });
// console.log(rowData , "=rowData-paste")
const elementType = rowData?.ElementType
let val = newRow[dataIndx];
console.log( newRow[dataIndx] , "=paste-11")
if (rowData?.isReadOnly === true || rowData?.IsAbstract === true || elementType == "xbrli:booleanItemType") {
newRow[dataIndx] = "";
continue;
}
// Only apply for monetary/decimal types
if (
elementType !== "xbrli:monetaryItemType" &&
elementType !== "xbrli:decimalItemType"
) {
if (rowId !== null && rowId !== "" && rowId !== undefined) {
const columnIndex = Number(dataIndx.replace(/[^\d]/g, "")); // Extract last number from col name
if (!modifiedRowsRef.current[rowId]) {
modifiedRowsRef.current[rowId] = [];
}
modifiedRowsRef.current[rowId].push(columnIndex);
modifiedRowsRef.current[rowId] = [...new Set(modifiedRowsRef.current[rowId])];
}
continue;
}
if (val === "" || val === null) {
newRow[dataIndx] = "";
continue;
}
// If object → extract value
if (typeof val === "object" && val !== null) {
val = val.text || val.value || "";
}
val = String(val).trim();
let str = String(val).trim();
if (!str) {
newRow[dataIndx] = "";
continue;
}
if (!str) continue;
console.log(str , "paste data")
let clean = str.replace(/[^\d.\-]/g, "");
clean = clean.replace(/(?!^)-/g, ""); // Keep only first minus
clean = clean.replace(/(\..*)\./g, "$1"); // Keep only first dot
// Parse to number
const num = parseFloat(clean);
if (isNaN(num)) {
newRow[dataIndx] = "";
rowData[dataIndx] = null;
continue;
}
// ─── STEP 4: Store number for calculations ───
rowData[dataIndx] = num;
// ─── STEP 5: Format only DISPLAY value (US commas + 2 decimals) ───
const formatted = num.toLocaleString("en-US", {
minimumFractionDigits: 2,
maximumFractionDigits: 2
});
newRow[dataIndx] = formatted;
console.log("paste-4" , rowId )
if (rowId !== null && rowId !== "" && rowId !== undefined) {
const columnIndex = Number(dataIndx.replace(/[^\d]/g, "")); // Extract last number from col name
if (!modifiedRowsRef.current[rowId]) {
modifiedRowsRef.current[rowId] = [];
}
modifiedRowsRef.current[rowId].push(columnIndex);
modifiedRowsRef.current[rowId] = [...new Set(modifiedRowsRef.current[rowId])];
}
}
}
if (extendedLinkName.trim()?.includes("510000") || extendedLinkName.trim()?.includes("520000")) {
// grid.commit(); // 🔥 critical line - updates dataModel
grid.refresh(); // UI update
setTimeout(() => {
console.log("Paste complete → Recalculating columns:");
// Run recalc for each changed column
changedCols.forEach(colKey => {
recalcCashFlow(grid, colKey);
});
// Final UI refresh (only once!)
grid.refresh();
}, 50); // 10ms is more reliable than 0 in heavy grids
}
return true; // allow validation to proceed
}
