Copy/Paste from Excel to a web page

Question

Is there a standard way or library to copy and paste from a spreasheet to a web form? When I select more than one cell from Excel I (obviously) lose the delimiter and all is pasted into one cell of the web form. Does it have to be done in VB? or could the processing be done once the paste action is started on the web form?

Are you trying to automate something? Can you give a better example... — Dayton Brown, Jan 05 '10 at 14:22
I was afraid to ask this question. Thank you for doing it, and for being so concise. :) — Nostalg.io, Apr 16 '15 at 15:38

Tatu Ulmanen · Accepted Answer · 2010-01-05T14:19:45.243

104

You don't lose the delimiters, the cells are separated by tabs (\t) and rows by newlines (\n) which might not be visible in the form. Try it yourself: copy content from Excel to Notepad, and you'll see your cells nicely lined up. It's easy then to split the fields by tabs and replace them with something else, this way you can build even a table from them. Here's a example using jQuery:

var data = $('input[name=excel_data]').val();
var rows = data.split("\n");

var table = $('<table />');

for(var y in rows) {
    var cells = rows[y].split("\t");
    var row = $('<tr />');
    for(var x in cells) {
        row.append('<td>'+cells[x]+'</td>');
    }
    table.append(row);
}

// Insert into DOM
$('#excel_table').html(table);

So in essence, this script creates an HTML table from pasted Excel data.

edited Jan 05 '10 at 14:19

answered Jan 05 '10 at 14:14

Tatu Ulmanen

123,288
34
187
185

12

I have been playing around with a similar problem, it is worth noting, pasting to a textbox, you loose the newlines. While pasting to a text area you keep them. – wobbily_col Mar 04 '14 at 10:12
1

For practical reasons, you could make this answer a snippet for future uses. – AdrianoRR Apr 06 '16 at 03:13
2

If there's a new-line within a cell this breaks. – th3byrdm4n Jun 27 '16 at 17:18
Just a quick note on this - works brilliantly but Excel adds a trailing row when data is copied so you end up with an empty `td`. Just FYI – jg2703 Apr 10 '17 at 20:22
2

this is a quick but short-term solution, what if one of my cells contains \n in it – MeVimalkumar Aug 17 '18 at 07:04
Nice and easy solution! – Konrad G Oct 23 '19 at 20:29

score 37 · Answer 2 · answered Jul 08 '14 at 14:19

In response to the answer by Tatu I have created a quick jsFiddle for showcasing his solution:

http://jsfiddle.net/duwood/sTX7y/

HTML

<p>Paste excel data here:</p>  
<textarea name="excel_data" style="width:250px;height:150px;"></textarea><br>
<input type="button" onclick="javascript:generateTable()" value="Genereate Table"/>
<br><br>
    <p>Table data will appear below</p>
<hr>
<div id="excel_table"></div>

JS

function generateTable() {
    var data = $('textarea[name=excel_data]').val();
    console.log(data);
    var rows = data.split("\n");

    var table = $('<table />');

    for(var y in rows) {
    var cells = rows[y].split("\t");
    var row = $('<tr />');
    for(var x in cells) {
        row.append('<td>'+cells[x]+'</td>');
    }
    table.append(row);
}

// Insert into DOM
$('#excel_table').html(table);
}

And here's my addition to this answer, but with the automatic table showing, right away on paste event: http://jsfiddle.net/sTX7y/690/ Basically, it simulates the 'afterpaste' event. — userfuser, Dec 22 '17 at 00:11

score 18 · Answer 3 · answered Sep 21 '13 at 22:08

18

On OSX and Windows , there are multiple types of clipboards for different types of content. When you copy content in Excel, data is stored in the plaintext and in the html clipboard.

The right way (that doesn't get tripped up by delimiter issues) is to parse the HTML. http://jsbin.com/uwuvan/5 is a simple demo that shows how to get the HTML clipboard. The key is to bind to the onpaste event and read

event.clipboardData.getData('text/html')

answered Sep 21 '13 at 22:08

SheetJS

22,470
12
65
75

The jsbin did not work as expected for me (Firefox 42 on Win 7, Excel 2013). Has something important changed since you posted this answer, which makes this approach no longer work? In the browser console, I see the text/plain content logged, but the text/html content is empty. – Christian Semrau Dec 08 '15 at 22:47
This works with chrome either on windows with excel or on ubuntu with libreOffice. Best solution IMO, since it does keep all the formatting from the original sheets. – BiAiB Feb 18 '16 at 10:28
this quite relevant answer but need more detailing – MeVimalkumar Aug 24 '18 at 04:25

score 9 · Answer 4 · answered Nov 05 '15 at 17:57

For any future googlers ending up here like me, I used @tatu Ulmanen's concept and just turned it into an array of objects. This simple function takes a string of pasted excel (or Google sheet) data (preferably from a textarea) and turns it into an array of objects. It uses the first row for column/property names.

function excelToObjects(stringData){
    var objects = [];
    //split into rows
    var rows = stringData.split('\n');

    //Make columns
    columns = rows[0].split('\t');

    //Note how we start at rowNr = 1, because 0 is the column row
    for (var rowNr = 1; rowNr < rows.length; rowNr++) {
        var o = {};
        var data = rows[rowNr].split('\t');

        //Loop through all the data
        for (var cellNr = 0; cellNr < data.length; cellNr++) {
            o[columns[cellNr]] = data[cellNr];
        }

        objects.push(o);
    }

    return objects;
}

Hopefully it helps someone in the future.

This answer is bulletproof especially for the cells that include long strings including escaping characters. I — Suat Atan PhD, Jan 04 '22 at 10:55

score 8 · Answer 5 · answered Jan 05 '10 at 14:59

8

The same idea as Tatu(thanks I'll need it soon in our project), but with a regular expression.
Which may be quicker for large dataset.

<html>
<head>
    <title>excelToTable</title>
    <script src="../libs/jquery.js" type="text/javascript" charset="utf-8"></script>
</head>
<body>
    <textarea>a1    a2  a3
b1  b2  b3</textarea>
    <div></div>
    <input type="button" onclick="convert()" value="convert"/>
    <script>
        function convert(){
            var xl = $('textarea').val();
            $('div').html( 
                '<table><tr><td>' + 
                xl.replace(/\n+$/i, '').replace(/\n/g, '</tr><tr><td>').replace(/\t/g, '</td><td>') + 
                '</tr></table>'
            )
        }
    </script>
</body>
</html>

answered Jan 05 '10 at 14:59

Mic

24,812
9
57
70

Hi Mic, this is great code! Do you have any insight as to how I could take this table conversion into a submitable form? – BvilleBullet Jan 23 '13 at 15:04
it depends on the form you want to submit. I would make a stringified json out of that, and post it as a single field to the server. – Mic Jan 23 '13 at 17:01
By the way, this works for a Word table, too (one of my users' legacy "forms" that we're trying to convert). Thanks. – Hugh Seagraves Jun 14 '16 at 03:17

score 2 · Answer 6 · edited Jul 03 '22 at 05:59

UPDATE: This is only true if you use ONLYOFFICE instead of MS Excel.

There is actually a flaw in all answers provided here and also in the accepted one. The flaw is that whenever you have an empty cell in excel and copy that, in the clipboard you have 2 tab chars next to each other, so after splitting you get one additional item in array, which then appears as an extra cell in that row and moves all other cells by one. So to avoid that you basically need to replace all double tab (tabs next to each other only) chars in a string with one tab char and only then split it.

An updated version of @userfuser's jsfiddle is here to fix that issue by filtering pasted data with removeExtraTabs

http://jsfiddle.net/sTX7y/794/

function removeExtraTabs(string) {
  return string.replace(new RegExp("\t\t", 'g'), "\t");
}

function generateTable() {
  var data = removeExtraTabs($('#pastein').val());
  var rows = data.split("\n");
  var table = $('<table />');

  for (var y in rows) {
    var cells = rows[y].split("\t");
    var row = $('<tr />');
    for (var x in cells) {
      row.append('<td>' + cells[x] + '</td>');
    }
    table.append(row);
  }

  // Insert into DOM
  $('#excel_table').html(table);
}

$(document).ready(function() {
  $('#pastein').on('paste', function(event) {
    $('#pastein').on('input', function() {
      generateTable();
      $('#pastein').off('input');
    })
  })
})

I actually found out that this happens only if you copy from ONLYOFFICE software's spreadsheet. Hmm.. Leaving this here in case someone stumbles upon it. — Aram, Feb 21 '18 at 03:07

score 0 · Answer 7 · answered Jan 27 '13 at 18:50

Digging this up, in case anyone comes across it in the future. I used the above code as intended, but then ran into an issue displaying the table after it had been submitted to a database. It's much easier once you've stored the data to use PHP to replace the new lines and tabs in your query. You may perform the replace upon submission, $_POST[request] would be the name of your textarea:

$postrequest = trim($_POST[request]);
$dirty = array("\n", "\t");
$clean = array('</tr><tr><td>', '</td><td>');
$request = str_replace($dirty, $clean, $postrequest);

Now just insert $request into your database, and it will be stored as an HTML table.

score 0 · Answer 8 · answered Jan 05 '10 at 14:28

0

Excel 2007 has a feature for doing this under the "Data" tab that works pretty nicely.

answered Jan 05 '10 at 14:28

LeWoody

3,593
4
25
31

score -2 · Answer 9 · answered Jan 05 '10 at 14:13

-2

Maybe it would be better if you would read your excel file from PHP, and then either save it to a DB or do some processing on it.

here an in-dept tutorial on how to read and write Excel data with PHP:
http://www.ibm.com/developerworks/opensource/library/os-phpexcel/index.html

answered Jan 05 '10 at 14:13

Adnan

25,882
18
81
110

That's is an absolutely terrible solution. I came to this thread as I am currently doing this and it is incredibly error prone. Excel sometimes spits out numbers as integers, sometimes as floats sometimes as strings. – wobbily_col Feb 27 '14 at 13:01
also user scenarios apply, where you need to paste from unreliable sources, like your bank, whose xls/csv files are full of crap that need to be manually sieved – ZJR Nov 26 '17 at 12:58

Copy/Paste from Excel to a web page

9 Answers9

Linked