Javascript 从 Excel 复制/粘贴到网页
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2006468/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy/Paste from Excel to a web page
提问by His dudeness
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?
是否有标准方法或库可以从电子表格复制和粘贴到 Web 表单？当我从 Excel 中选择多个单元格时，我（显然）丢失了分隔符，所有内容都被粘贴到 Web 表单的一个单元格中。它必须在VB中完成吗？或者是否可以在 Web 表单上开始粘贴操作后进行处理？
回答by Tatu Ulmanen
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:
您不会丢失分隔符，单元格由制表符 ( \t)分隔，行由换行符 ( \n)分隔，这在表单中可能不可见。亲自尝试：将内容从 Excel 复制到记事本，您会看到单元格排列整齐。然后很容易按选项卡拆分字段并将它们替换为其他内容，这样您甚至可以从它们构建一个表。下面是一个使用 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.
所以本质上，这个脚本从粘贴的 Excel 数据创建一个 HTML 表格。
回答by dunderwood
In response to the answer by Tatu I have created a quick jsFiddle for showcasing his solution:
为了回应 Tatu 的回答，我创建了一个快速的 jsFiddle 来展示他的解决方案：
http://jsfiddle.net/duwood/sTX7y/
http://jsfiddle.net/duwood/sTX7y/
HTML
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
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);
}
回答by SheetJS
On OSXand 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.
在OSX和Windows 上，针对不同类型的内容有多种类型的剪贴板。当您在 Excel 中复制内容时，数据将存储在纯文本和 html 剪贴板中。
The right way (that doesn't get tripped up by delimiter issues) is to parse the HTML. http://jsbin.com/uwuvan/5is a simple demo that shows how to get the HTML clipboard. The key is to bind to the onpaste event and read
正确的方法（不会被分隔符问题绊倒）是解析 HTML。 http://jsbin.com/uwuvan/5是一个简单的演示，展示了如何获取 HTML 剪贴板。关键是绑定到onpaste事件并读取
event.clipboardData.getData('text/html')
回答by Mic
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.
与 Tatu 相同的想法（感谢我很快会在我们的项目中用到它），但使用正则表达式。
对于大型数据集，这可能更快。
<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>
回答by Coo
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.
对于任何像我一样在这里结束的未来 googlers，我使用了 @tatu Ulmanen 的概念，并将其变成了一组对象。这个简单的函数接受一串粘贴的 excel（或 Google 表格）数据（最好来自 a textarea）并将其转换为一个对象数组。它使用第一行作为列/属性名称。
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.
希望它可以帮助将来的某个人。
回答by Aram
UPDATE: This is only true if you use ONLYOFFICE instead of MS Excel.
更新：这仅适用于您使用 ONLYOFFICE 而不是 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.
实际上，此处提供的所有答案以及已接受的答案中都有一个流程。流程是，每当你在 excel 中有一个空单元格并复制它时，在剪贴板中你有 2 个彼此相邻的标签字符，所以在拆分后你会在数组中获得一个额外的项目，然后在该行中显示为一个额外的单元格并将所有其他单元格移动一个。因此，为了避免这种情况，您基本上需要用一个制表符字符替换字符串中的所有双制表符（仅彼此相邻的制表符）字符，然后才将其拆分。
An updated version of @userfuser's jsfiddle is here to fix that issue by filtering pasted data with removeExtraTabs
@userfuser 的 jsfiddle 的更新版本在这里通过使用 removeExtraTabs 过滤粘贴的数据来解决该问题
http://jsfiddle.net/sTX7y/794/
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');
})
})
})
回答by dcclassics
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:
把它挖出来，以防将来有人遇到它。我按预期使用了上面的代码，但是在将表提交到数据库后显示该表时遇到了问题。存储数据后，使用 PHP 替换查询中的新行和制表符会容易得多。您可以在提交时执行替换， $_POST[request] 将是您的文本区域的名称：
$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.
现在只需将 $request 插入到您的数据库中，它就会被存储为一个 HTML 表格。
回答by LeWoody
Excel 2007 has a feature for doing this under the "Data" tab that works pretty nicely.
Excel 2007 在“数据”选项卡下有一个功能可以很好地执行此操作。
回答by Adnan
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.
如果您从 PHP 读取 excel 文件，然后将其保存到数据库或对其进行一些处理，也许会更好。
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
这里有一个关于如何使用 PHP 读取和写入 Excel 数据的内部教程：http:
//www.ibm.com/developerworks/opensource/library/os-phpexcel/index.html
