Copy paste from excel, difference between line break in cell and row delimiter

Question

We need to offer to our customers a way of copy pasting an excel to a table in a web application. We split rows with line breaks, and columns with tabulation.

The problems is that in a single cell you can have line break so the split(\n) will simply understand that there is a new row.

    const rows = event.target.value.split('\n');
    for (let row of rows) {
      const values = row.split('\t');
    }

With the code above, when there are no line break in cells we can get a nice table of the copy paste, but as soon as there are linebreaks in cells, everything is move to one row and break everything.

How can I differentiate line breaks for delimiting rows and line breaks in cells ?

score 1 · Accepted Answer · answered Jun 04 '21 at 07:00

Consider the small example where I've copy-pasted the Excel data into Notepad++ and we can see that cat\nfish is quoted:

So you can treat the content being pasted to the web application as tab separated values (tsv) and parse them as such.

In the snippet below tsvStringToArray is adapated from here (see here too). Note that an extra row is being returned because of that final CR LF after dog. In real life you should probably look for a library to do this for you. You might look at D3 as something that can run in the browser.

For arrToTable, I assume you must have a better effort of arrToTable. However, you should consider if you need to recreate the newline in the HTML.

const target = document.getElementById("input");
const output = document.getElementById("output");

const tsvStringToArray = (data) => {
  const re = /(\t|\r?\n|\r|^)(?:"([^"]*(?:""[^"]*)*)"|([^\t\r\n]*))/gi
  const result = [[]]
  let matches
  while ((matches = re.exec(data))) {
    if (matches[1].length && matches[1] !== "\t") result.push([])
    result[result.length - 1].push(
      matches[2] !== undefined ? matches[2].replace(/""/g, '"') : matches[3]
    )
  }
  //console.log(result);
  return result
}

const arrToTable = arr => {
  let tbl = document.createElement("table");
  for (row of arr) {
    let tr = document.createElement("tr");
    for (cell of row) {
      let td = document.createElement("td");
      let test = /\n/.test(cell);
      if (test) {
        let items = cell.split(/\n/);
        for ([idx, item] of items.entries()) {
          td.appendChild(document.createTextNode(item));
          if (idx < items.length) {
            td.appendChild(document.createElement("br"));
          }
        }
      } else {
        td.appendChild(document.createTextNode(cell));    
      }
      tr.appendChild(td);
    }
    tbl.appendChild(tr);
  }
  return tbl;
}

target.addEventListener("paste", e => {
  const text = event.clipboardData.getData("text");
  const arr = tsvStringToArray(text).slice(0, -1);
  const el = arrToTable(arr);
  output.appendChild(el);
});

div#input {
  height: 90px;
  width: 320px;
  background-color: powderblue;
}

table, tr, td {
  border: 1px solid black;
}

Paste in blue box:
<div id="input" contenteditable="true"></div>
<p>
<div id="output"></div>

Copy paste from excel, difference between line break in cell and row delimiter

1 Answers1