Web Components Grid Paste from Excel

    The Ignite UI for Web Components IgcGridComponent 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.

    Web Components Paste from Excel Example

    This sample demonstrates how to implement pasting from Excel into the IgcGridComponent 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,Command + V).

    Na parte superior, há um botão suspenso com 2 opções:

    1. "Colar dados como novas linhas" – neste modo, todos os dados copiados do Excel serão anexados à grade como novas linhas
    2. "Colar a partir da célula ativa" – neste modo, os dados na grade serão substituídos.

    Os novos dados após a colagem são decorados em itálico.

    Usage

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

    <igc-grid auto-generate="false" name="grid" id="grid" primary-key="OrderID">
    <igc-grid-toolbar>
        <igc-grid-toolbar-actions >
            <igc-grid-toolbar-exporter export-excel="true" export-csv="false"> </igc-grid-toolbar-exporter>
        </igc-grid-toolbar-actions>
    </igc-grid-toolbar>
    <igc-column field="OrderID" hidden="true"></igc-column>
    <igc-column field="Salesperson" header="Name" width="200px"></igc-column>
    <igc-column field="ShipName" header="Ship Name" width="200px"></igc-column>
    <igc-column field="Country" header="Country" width="200px"></igc-column>
    <igc-column field="ShipCity" header="Ship City" width="200px"></igc-column>
    <igc-column field="PostalCode" header="Postal Code" width="200px"> </igc-column>
</igc-grid>

    public webGridPasteFromExcel() {
    const grid = document.getElementById("grid") as any;
    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.

    public onPaste(eventArgs: any) {
    let data;
    const clData: any = "clipboardData";

    // get clipboard data - from window.cliboardData for IE or from the original event's arguments.
    if (window[clData]  as any) {
        (window.event as any).returnValue = false;
            data = (window[clData]  as any).getData("text");
        } else {
            data = eventArgs[clData].getData("text/plain");
        }

        // process the clipboard data
    const processedData = this.processData(data);
    if (this.pasteMode === "Paste data as new records") {
        this.addRecords(processedData);
    } else {
        this.updateRecords(processedData);
    }
}

public processData(data: any) {
    const pasteData = data.split("\n");
    for (let i = 0; i < pasteData.length; i++) {
        pasteData[i] = pasteData[i].split("\t");
        // Check if last row is a dummy row
        if (pasteData[pasteData.length - 1].length === 1 && pasteData[pasteData.length - 1][0] === "") {
            pasteData.pop();
        }
        // remove empty data
        if (pasteData.length === 1 &&
            pasteData[0].length === 1 &&
            (pasteData[0][0] === "" || pasteData[0][0] === "\r")) {
            pasteData.pop();
        }
    }
    return pasteData;
}

    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.

    public addRecords(processedData: any[]) {
    const grid = this.grid as any;
    const columns = grid.visibleColumns;
    const pk = grid.primaryKey;
    const addedData: any[] = [];
    for (const curentDataRow of processedData) {
        const rowData = {} as any;
        for (const col of columns) {
            const index = columns.indexOf(col);
            rowData[col.field] = curentDataRow[index];
        }
        // generate PK
        rowData[pk] = grid.data.length + 1;
        grid.addRow(rowData);
        addedData.push(rowData);
    }
    // scroll to last added row
    grid.navigateTo(grid.data.length - 1, 0, () => {
        this.clearStyles();
        for (const data of addedData) {
            const row = grid.getRowByKey(data[pk]);
            if (row) {
                const rowNative = this.getNative(row) as any;
                if (rowNative) {
                rowNative.style["font-style"] = "italic";
                rowNative.style.color = "gray";
                }
            }
        }
    });
}

public updateRecords(processedData: any[]) {
    const grid = this.grid as any;
    const cell = grid.selectedCells[0];
    const pk = grid.primaryKey;
    if (!cell) { return; }
    const rowIndex = cell.row.index;
    const columns = grid.visibleColumns;
    const cellIndex = grid.visibleColumns.indexOf(cell.column);
    let index = 0;
    const updatedRecsPK: any[] = [];
    for (const curentDataRow of processedData) {
        const rowData = {} as any;
        const dataRec = grid.data[rowIndex + index];
        const rowPkValue = dataRec ? dataRec[pk] : grid.data.length + 1;
        rowData[pk] = rowPkValue;
        for (let j = 0; j < columns.length; j++) {
            let currentCell;
            if (j >= cellIndex) {
                currentCell = curentDataRow.shift();
            }
            const colKey = columns[j].field;
            rowData[colKey] = currentCell || (dataRec ? dataRec[colKey] : null);
        }
        if (!dataRec) {
            // no rec to update, add instead
            rowData[pk] = rowPkValue;
            grid.addRow(rowData);
            continue;
        }
        grid.updateRow(rowData, rowPkValue);
        updatedRecsPK.push(rowPkValue);
        index++;
    }

    this.clearStyles();
    for (const pkVal of updatedRecsPK) {
        const row = grid.getRowByKey(pkVal);
        if (row) {
            const rowNative = this.getNative(row) as any;
            if (rowNative) {
                rowNative.style["font-style"] = "italic";
                rowNative.style.color = "gray";
            }
        }
    }
}

    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.

    Nossa comunidade é ativa e sempre acolhedora para novas ideias.