React Grid Paste from Excel

The Ignite UI for React IgrGrid can read Excel data that is copied to the clipboard. In this section we will show you how to do this with some custom code.

React Paste from Excel Example

This sample demonstrates how to implement pasting from Excel into the IgrGrid Material UI table. To work with the sample open up any Excel spreadsheet, copy some rows, and paste it into the grid using the keyboard (CTRL + V, SHIFT + INSERT,CMD + V).

On the top there is a dropdown button with 2 options:

1. “Paste data as new rows” – in this mode any data copied from Excel will be appended to the grid as new rows
2. ”Paste starting from active cell” – in this mode the data in the grid will be overwritten.

The new data after the paste is decorated in Italic.

Usage

You should first bind to the grid’s rendered event to create and manage a text area element:

<IgrGrid autoGenerate={false} data={this.invoicesData} onRendered={this.webGridPasteFromExcel} ref={this.gridRef} id="grid" primaryKey="OrderID">
    <IgrGridToolbar>
        <IgrGridToolbarActions>
            <IgrGridToolbarExporter exportExcel={true} exportCSV={false}>
            </IgrGridToolbarExporter>
        </IgrGridToolbarActions>
    </IgrGridToolbar>
    <IgrColumn field="OrderID" hidden={true}></IgrColumn>
    <IgrColumn field="Salesperson" header="Name" width="200px"></IgrColumn>
    <IgrColumn field="ShipName" header="Ship Name" width="200px"></IgrColumn>
    <IgrColumn field="Country" header="Country" width="200px"></IgrColumn>
    <IgrColumn field="ShipCity" header="Ship City" width="200px"></IgrColumn>
    <IgrColumn field="PostalCode" header="Postal Code" width="200px"> </IgrColumn>
</IgrGrid>

public webGridPasteFromExcel(e: CustomEvent<any>) {
    const grid = e.target as IgrGrid;
    this.onKeyDown = this.onKeyDown.bind(this);
    grid.addEventListener("keydown", this.onKeyDown);
}
public onKeyDown(eventArgs: any): void {
    const ctrl = eventArgs.ctrlKey;
    const key = eventArgs.keyCode;
    // Ctrl-V || Shift-Ins || Cmd-V
    if ((ctrl || eventArgs.metaKey) && key === 86 || eventArgs.shiftKey && key === 45) {
        this.textArea.focus();
    }
}

private txtArea: any;

public get textArea() {
    if(!this.txtArea) {
            const div = document.createElement("div");
            const divStyle = div.style;
            divStyle.position = "fixed";
            document.body.appendChild(div);
            this.txtArea = document.createElement("textarea");
            const style = this.txtArea.style;
            style.opacity = "0";
            style.height = "0px";
            style.width = "0px";
            style.overflow = "hidden";
            div.appendChild(this.txtArea);

            this.txtArea.addEventListener("paste", (eventArgs: any) => { this.onPaste(eventArgs); });
        }
        return this.txtArea;
    }

The code creates a DOM textarea element which is used to receive the pasted data from the clipboard. When the data is pasted in the textarea the code parses it into an array.

The pasted data can then be added as new records or used to update the existing records based on the user selection. For reference see the addRecords and updateRecords methods.

API References

Additional Resources

• Excel Exporter - Use the Excel Exporter service to export data to Excel from Grid. It also provides the option to only export the selected data from the Grid. The exporting functionality is encapsulated in the ExcelExporterService class and the data is exported in MS Excel table format. This format allows features like filtering, sorting, etc. To do this you need to invoke the ExcelExporterService’s export method and pass the Grid component as first argument.

Our community is active and always welcoming to new ideas.

• Ignite UI for React Forums
• Ignite UI for React GitHub