CODEWITHSUNDEEP
javascripthtmlformsexcel
His dudeness
His dudeness

Reputation: 1103

Copy/Paste from Excel to a web page

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?

Upvotes: 110

Views: 134740

Answers (10)

Herobrine
Herobrine

Reputation: 3163

As stated by Tatu, the cells are separated by tabs \t and rows by newlines \n.

Here is a slightly shorter code to extract the values in an array (in JS):

/* Copied values:
| A | B |
| C | D | */

const clipboardText = await navigator.clipboard.readText() ?? ""; // A\tB\nC\tD

const pasteData = clipboardText
  .split("\n")
  .map(row => row.split("\t"));

console.log(pasteData); // [ [ "A", "B" ], [ "C", "D" ] ]

Upvotes: 0

Aram
Aram

Reputation: 379

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');
    })
  })
})

Upvotes: 2

Coo
Coo

Reputation: 1882

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.

Upvotes: 9

dunderwood
dunderwood

Reputation: 579

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);
}

Upvotes: 37

SheetJS
SheetJS

Reputation: 22905

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')

Upvotes: 21

dcclassics
dcclassics

Reputation: 896

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.

Upvotes: 0

Mic
Mic

Reputation: 25154

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>

Upvotes: 8

LeWoody
LeWoody

Reputation: 3649

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

Upvotes: 0

Tatu Ulmanen
Tatu Ulmanen

Reputation: 124768

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.

Upvotes: 107

Adnan
Adnan

Reputation: 26350

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

Upvotes: -2

Related Questions