Blazor Grid Paste from Excel

The Ignite UI for Blazor IgbGrid 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.

Blazor Paste from Excel Example

This sample demonstrates how to implement pasting from Excel into the IgbGrid 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).

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:

<IgbGrid  AutoGenerate="false" Data="InvoicesData" RenderedScript="WebGridPasteFromExcel" @ref="grid" Id="grid" PrimaryKey="OrderID">
    <IgbGridToolbar>
        <IgbGridToolbarActions>
            <IgbGridToolbarExporter ExportExcel="true" ExportCSV="false"> </IgbGridToolbarExporter>
        </IgbGridToolbarActions>
    </IgbGridToolbar>

    <IgbColumn Field="OrderID" Hidden="true"></IgbColumn>
    <IgbColumn Field="Salesperson" Header="Name" Width="200px"></IgbColumn>
    <IgbColumn Field="ShipName" Header="Ship Name" Width="200px"></IgbColumn>
    <IgbColumn Field="Country" Header="Country" Width="200px"></IgbColumn>
     <IgbColumn Field="ShipCity" Header="Ship City" Width="200px"></IgbColumn>
    <IgbColumn Field="PostalCode" Header="Postal Code" Width="200px"></IgbColumn>
</IgbGrid>

// In JavaScript
igRegisterScript("WebGridPasteFromExcel", (evtArgs) => {
    const grid = document.getElementById("grid");
    grid.addEventListener("keydown", onWebGridPasteFromExcelKeyDown);
}, false);

function onWebGridPasteFromExcelKeyDown(eventArgs) {
    const ctrl = eventArgs.ctrlKey;
    const key = eventArgs.keyCode;
    // Ctrl-V || Shift-Ins || Cmd-V
    if ((ctrl || eventArgs.metaKey) && key === 86 || eventArgs.shiftKey && key === 45) {
        textArea.focus();
    }
}

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

        txtArea.addEventListener("paste", (eventArgs) => { onPaste(eventArgs); });
    }
    return 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.

function onPaste(eventArgs) {
    let data;
    const clData = "clipboardData";

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

    // process the clipboard data
    const processedData = processData(data);
        if (pasteMode === "Paste data as new records") {
            addRecords(processedData);
        } else {
            updateRecords(processedData);
        }
}
function processData(data) {
    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.

function addRecords(processedData) {
    const grid = document.getElementById("grid");
    const columns = grid.visibleColumns;
    const pk = grid.primaryKey;
    const addedData = [];
    for (const curentDataRow of processedData) {
        const rowData = {};
        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, () => {
        clearStyles();
        for (const data of addedData) {
            const row = grid.getRowByKey(data[pk]);
            if (row) {
                const rowNative = getNative(row);
                if (rowNative) {
                    rowNative.style["font-style"] = "italic";
                    rowNative.style.color = "gray";
                }
            }
    }
    });
}

function updateRecords(processedData) {
    const grid = document.getElementById("grid");
    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 = [];
    for (const curentDataRow of processedData) {
        const rowData = {};
        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++;
    }

API References

• IgbGrid

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 Blazor Forums
• Ignite UI for Blazor GitHub