JavaScript Guide: How To Copy Paste From Excel To A Web Form

The Goal: What We Are Trying To Make

We are going to create a HTML web form input. When you copy paste from excel into the web form 2 things will happen:

1. A table will be generated: To give visual feedback to the user
2. A text area will get the JSON version of the copy pasted table: This will be used to send the table data to the server

Lets look at this in action..

This kind of user input can come in handy when the user needs to paste things directly from an excel sheet.

How This Works..

You see, when any block is copy pasted from excel (or google sheets or open office or LibreOffice) it comes with “tab” characters between each cell. And at the end of each line is a “new line” character as you would expect.

In the above image, I have highlighted the tab char in sublime text.

By using this, we can create some JS functions to transform the raw text pasted into a text area into a HTML table.

Converting Copy Pasted Text From Excel Into A HTML Table (For User Feedback)

let constructTableFromPastedInput = (pastedInput) => {
    let rawRows = pastedInput.split("\n");
    let headRow = '';
    let bodyRows = [];
    rawRows.forEach((rawRow, idx) => {
        let rawRowArray = rawRow.split("\t");
        if (idx == 0) {
            headRow = `<tr><th>${rawRowArray.join("</th><th>")}</th></tr>`;
        } else {
            bodyRows.push(`<tr><td>${rawRowArray.join("</td><td>")}</td></tr>`);
        }
    })
    return `
        <table class="boostrap4_table_head_dark_striped_rounded_compact">
            <thead>
                ${headRow}
            </thead>
            <tbody>
                ${bodyRows.join("")}
            </tbody>
        </table>
    `;
}

On line 2, above I have split the lines. And on line 6 I have split the tables cells into individual values.

BTW, to draw a pretty table with CSS, I have used the copy paste-able CSS styles from my article: 12 HTML Table Style Examples (CSS Snippets To Copy Paste)

You can take the result from this function and show it to the user wherever you need to.

Converting Copy Pasted Text From Excel Into A JSON Object (To Send To A Sever)

let constructJSONFromPastedInput = (pastedInput) => {
    let rawRows = pastedInput.split("\n");
    let headersArray = rawRows[0].split("\t");
    let output = []
    rawRows.forEach((rawRow, idx) => {
        if ( idx > 0 ) {
            let rowObject = {};
            let values = rawRow.split("\t");
            headersArray.forEach((header, idx) => {
                rowObject[header] = values[idx];
            });
            output.push(rowObject);
        }
    })
    return output;
}

The result of this function looks something like..

[
  {
    "Name": "Bugs Bunny",
    "Favourite Thing": "Carrots",
    "Image": "Bugs.png"
  },
  {
    "Name": "Elmer Fudd",
    "Favourite Thing": "Chasing Rabbits",
    "Image": "Elmer.png"
  },
  {
    "Name": "Porky Pig",
    "Favourite Thing": "Acting",
    "Image": "Porky.png"
  }
]

Small Thing To Note

This will not work if the user pastes the data into a simple text input. The text inputs eat up the new lines etc. So, the code does not work. The place where the user pastes the data has to be a “text area”.

A Working Demo You Can Play With..

See the Pen usefull
Excel To Web Form by Khoj Badami (@livefiredev)
on CodePen.

Hope you found this article useful!