Copy/Paste from Excel to a web page
JavascriptHtmlFormsExcelJavascript Problem Overview
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?
Javascript Solutions
Solution 1 - Javascript
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.
Solution 2 - Javascript
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);
}
Solution 3 - Javascript
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')
Solution 4 - Javascript
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.
Solution 5 - Javascript
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>
Solution 6 - Javascript
UPDATE: This is only true if you use ONLYOFFICE instead of MS Excel.
There is actually a flow in all answers provided here and also in the accepted one. The flow 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');
})
})
})
Solution 7 - Javascript
Excel 2007 has a feature for doing this under the "Data" tab that works pretty nicely.
Solution 8 - Javascript
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.
Solution 9 - Javascript
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