pqGrid Paste Issue with Indian Number Format (#VALUE! Error) - Reactjs

[Punit]

I am facing an issue while copying values from Excel and pasting them into pqGrid.

Issue Description:

When I copy a number in Indian format from Excel (e.g., 3,69,92,674.00) and paste it into pqGrid, the value is getting converted to #VALUE! instead of the correct numeric value.

Current Implementation:

I have implemented the following pqGrid events to track the pasted data:

paste: function (evt, ui) {
},

beforePaste: function (evt, ui) {
    console.log("Before Paste (raw data): ", ui.clipboardData);
    console.log("Parsed rows: ", ui.rows);
},

change: function (evt, ui) {
    if (ui.source === 'paste') {
        console.log("After Paste (final data): ", ui.updateList);

        ui.updateList.forEach(row => {
            console.log("Row Data:", row.newRow);
        });
    }
}

In all console logs (paste, beforePaste, and change events), the value is already coming as #VALUE!.
The actual Excel value (3,69,92,674.00) is not being received at any stage.

Kindly suggest the resolution for this issue.

[paramvir]

Thanks for sharing the details.

The format you’re using is currently not supported in pqGrid. However, support for this format is planned and will be included in the upcoming release.

In the meantime, if you’re able to modify the Excel file, please use the equivalent custom format: "#,##0.00". This format is fully supported and should work as expected.


The solution is provided in my next post.

[Punit]

From our side, the pqGrid column data type is set to string.

Could you please confirm if it is possible to convert (copied from Excel in Indian number format)  the pasted values into string during the paste process??

[paramvir]

Yes it's possible to paste the numbers in a string column or any type of column in pqgrid / spreadsheet with a small workaround.

Following is the solution for "Standard" number format used in your Excel file

Step 1. First monkey patch the pq.isDateFormat function, this ensures that the correct number formatting is called.

Code: [Select]

const _isDateFormat = pq.isDateFormat;
pq.isDateFormat = function(fmt){
return (fmt == 'Standard')? false: _isDateFormat.call(this, fmt);
}

Step 2.

Code: [Select]

beforePaste(evt, ui){
debugger
ui.rows.forEach(row =>{
row.forEach(cell=>{
if(cell.prop?.format == "Standard"){
                                //either
cell.prop.format = "#,##0.00";
                                //or
cell.prop.format = "";
}
})
})
},


Please let me know if you need furtehr assistance.