How to copy a JavaScript 2D array to clipboard to paste it in excel?

Question

Example 2D array:

var arr = [
  ["Mike", "Cane", 23],
  ["Jeff", "Meyers", 46],
  ["Thomas", "Bush", 67]
]

How do I copy a 2D array to the clipboard to then paste it in an excel spreadsheet? The rows and columns should be preserved as it is in the array. It should work the same way as if I would mark a range of cells in excel and then paste it back in.

Answer 1

It's pretty easy, you just have to add a tab character \t to separate columns and a new line \n to seperate rows.

Use this code:

const excelData = arr
  .map(lines => lines.join("\t"))
  .join("\n");

const arr = [
  ["Mike", "Cane", 23],
  ["Jeff", "Meyers", 46],
  ["Thomas", "Bush", 67]
];

document.getElementById('convert').addEventListener('click', () => {
  const excelData = arr
    .map(lines => lines.join("\t"))
    .join("\n");
 
  document.getElementById('excel').textContent = excelData;
});

<textarea id="excel" cols="30" rows="6"></textarea><br>
<button id="convert">Make Excel Clipboard data</button>

Run code snipet, hit the button and copy paste text to Excel to test it out.

Answer 2

This solution works amazing. It uses (CSV like) line breaks for a new row and tab for a new cell. Pasting text like this in an Excel, OpenOffice / LibreOffice Calc spreadsheet, it will detect it as multiple cells. It also works with Google Docs.

function copy2DToClipboard(array) {
  var csv = '', row, cell;
  for (row = 0; row < array.length; row++) {
    for (cell = 0; cell < array[row].length; cell++) {
      csv += (array[row][cell]+'').replace(/[\n\t]+/g, ' ');
      if (cell+1 < array[row].length) csv += '\t';
    }
    if (row+1 < array.length) csv += '\n';
  }
  copyTextToClipboard(csv);
}

// copied from https://stackoverflow.com/questions/400212/how-do-i-copy-to-the-clipboard-in-javascript
function fallbackCopyTextToClipboard(text) {
  var textArea = document.createElement("textarea");
  textArea.value = text;

  // Avoid scrolling to bottom
  textArea.style.top = "0";
  textArea.style.left = "0";
  textArea.style.position = "fixed";

  document.body.appendChild(textArea);
  textArea.focus();
  textArea.select();

  try {
    var successful = document.execCommand('copy');
    var msg = successful ? 'successful' : 'unsuccessful';
    // console.log('Fallback: Copying text command was ' + msg);
  } catch (err) {
    console.error('Fallback: Oops, unable to copy', err);
  }

  document.body.removeChild(textArea);
}
function copyTextToClipboard(text) {
  if (!navigator.clipboard) {
    fallbackCopyTextToClipboard(text);
    return;
  }
  navigator.clipboard.writeText(text).then(function() {
    // console.log('Async: Copying to clipboard was successful!');
  }, function(err) {
    console.error('Async: Could not copy text: ', err);
  });
}

Test:

function onTest() {
  const arr = [
    ["Mike", "Cane", 23],
    ["Jeff", "Meyers", 46],
    ["Thomas", "Bush", 67]
  ];
  copy2DToClipboard(arr);
  document.getElementById('test').innerText = 'Copied!';
}

function copy2DToClipboard(array) {
  var csv = '', row, cell;
  for (row = 0; row < array.length; row++) {
    for (cell = 0; cell < array[row].length; cell++) {
      csv += (array[row][cell]+'').replace(/[\n\t]+/g, ' ');
      if (cell+1 < array[row].length) csv += '\t';
    }
    if (row+1 < array.length) csv += '\n';
  }
  copyTextToClipboard(csv);
}

// copied from https://stackoverflow.com/questions/400212/how-do-i-copy-to-the-clipboard-in-javascript
function fallbackCopyTextToClipboard(text) {
  var textArea = document.createElement("textarea");
  textArea.value = text;
  
  // Avoid scrolling to bottom
  textArea.style.top = "0";
  textArea.style.left = "0";
  textArea.style.position = "fixed";

  document.body.appendChild(textArea);
  textArea.focus();
  textArea.select();

  try {
    var successful = document.execCommand('copy');
    var msg = successful ? 'successful' : 'unsuccessful';
    // console.log('Fallback: Copying text command was ' + msg);
  } catch (err) {
    console.error('Fallback: Oops, unable to copy', err);
  }

  document.body.removeChild(textArea);
}
function copyTextToClipboard(text) {
  if (!navigator.clipboard) {
    fallbackCopyTextToClipboard(text);
    return;
  }
  navigator.clipboard.writeText(text).then(function() {
    // console.log('Async: Copying to clipboard was successful!');
  }, function(err) {
    console.error('Async: Could not copy text: ', err);
  });
}

<button onclick="onTest()" id="test">Copy to clipboard.</button>

Edit:

This solution does not support cells with internal line breaks or tabs. Non-ascii unicodes can cause problems with some spreadsheet programs.

See also How do I copy to the clipboard in JavaScript?

Answer 3

I wanted to mention that there is now a simpler method to do the copying to the clipboard - you no longer need to create a textarea just so you can copy the data.

This example uses @christos-lytra's array mapping.

  const arr = [
    ['Mike', 'Cane', 23],
    ['Jeff', 'Meyers', 46],
    ['Thomas', 'Bush', 67],
  ];

  const excelData = arr.map(lines => lines.join('\t')).join('\n');

  navigator.clipboard.writeText(excelData);

Answer 4

You have to save your array as JSON file then you can import to your JSON data to the excel sheet. Follow the below steps to import different forms of data to the excel sheet.

https://support.office.com/en-us/article/import-data-from-external-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a