blocks|key|1367577|text|您不会丢失分隔符，单元格用制表符(\t)和换行符(\n)分隔，这在表单中可能看不到。自己尝试一下:将内容从Excel复制到记事本，你会看到你的单元格排列得很整齐。然后很容易将字段按制表符拆分，并用其他内容替换它们，这样您甚至可以用它们构建一个表。下面是一个使用jQuery的示例：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1367578|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>'%2Bcells[x]%2B'</td>');
++++}
++++table.append(row);
}

//+Insert+into+DOM
$('#excel_table').html(table);|code-block|syntax|javascript|1367579|因此，本质上，此脚本从粘贴的Excel数据创建一个HTML表。|1367580|entityMap^0|H|2|P|2|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

<p>You don't lose the delimiters, the cells are separated by tabs (<code>\t</code>) and rows by newlines (<code>\n</code>) 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:</p>

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

var table = $('&lt;table /&gt;');

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

// Insert into DOM
$('#excel_table').html(table);
</code></pre>

<p>So in essence, this script creates an HTML table from pasted Excel data.</p>


blocks|key|1367622|text|Excel2007在“数据”选项卡下有一个功能，可以很好地完成这项工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1367623|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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


blocks|key|1367662|text|与Tatu的想法相同(谢谢，我很快就会在我们的项目中用到它)，但使用正则表达式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1367663|这对于大数据集来说可能更快。|1367664|<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>'+%2B+
++++++++++++++++xl.replace(/\n%2B$/i,+'').replace(/\n/g,+'</tr><tr><td>').replace(/\t/g,+'</td><td>')+%2B+
++++++++++++++++'</tr></table>'
++++++++++++)
++++++++}
++++</script>
</body>
</html>|code-block|syntax|javascript|1367665|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

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


blocks|key|1152616|text|把它挖出来，以防将来有人遇到它。我按照预期使用了上面的代码，但在将表提交到数据库后，在显示表时遇到了问题。一旦存储了数据，使用PHP替换查询中的新行和制表符就容易多了。您可以在提交后执行替换，$_POSTrequest将是您的文本区域的名称：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1152617|$postrequest+=+trim($_POST[request]);
$dirty+=+array("\n",+"\t");
$clean+=+array('</tr><tr><td>',+'</td><td>');
$request+=+str_replace($dirty,+$clean,+$postrequest);|code-block|syntax|javascript|1152618|现在只需将$request插入到您的数据库中，它将被存储为HTML表。|1152619|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

<p>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:</p>

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

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


blocks|key|1367791|text|在OSX和Windows上，有多种类型的剪贴板用于处理不同类型的内容。在Excel中复制内容时，数据以纯文本和html剪贴板的形式存储。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1367792|正确的方法(不会因分隔符问题而出错)是解析HTML。http://jsbin.com/uwuvan/5是一个简单的演示，它展示了如何获取超文本标记语言剪贴板。关键是绑定到onpaste事件并读取|1367793|event.clipboardData.getData('text/html')|code-block|syntax|javascript|1367794|entityMap|0|LINK|mutability|MUTABLE|url|https://developer.apple.com/library/ios/documentation/UIKit/Reference/UIPasteboard_Class/Reference.html#//apple_ref/occ/instm/UIPasteboard/dataForPasteboardType%253a|1|http://msdn.microsoft.com/en-us/library/windows/desktop/ms649013%2528v=vs.85%2529.aspx|2|http://jsbin.com/uwuvan/5^0|1|3|0|5|7|1|0|Q|P|2|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]|$A|10|B|11|1|12]]|C|$]]|$1|D|3|E|5|6|7|13|8|@]|9|@$A|14|B|15|1|16]]|C|$]]|$1|F|3|G|5|H|7|17|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|18|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]|S|$5|N|O|P|C|$Q|T]]|U|$5|N|O|P|C|$Q|V]]]]

<p>On <a href="https://developer.apple.com/library/ios/documentation/UIKit/Reference/UIPasteboard_Class/Reference.html#//apple_ref/occ/instm/UIPasteboard/dataForPasteboardType%3a" rel="noreferrer">OSX</a> and <a href="http://msdn.microsoft.com/en-us/library/windows/desktop/ms649013%28v=vs.85%29.aspx" rel="noreferrer">Windows</a> , 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.  </p>

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

<pre><code>event.clipboardData.getData('text/html')
</code></pre>


blocks|key|1152740|text|为了响应Tatu的回答，我创建了一个快速jsFiddle来展示他的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1152741|http://jsfiddle.net/duwood/sTX7y/|offset|length|1152742|HTML|1152743|<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>|code-block|syntax|javascript|1152744|JS|1152745|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>'%2Bcells[x]%2B'</td>');
++++}
++++table.append(row);
}

//+Insert+into+DOM
$('#excel_table').html(table);
}|1152746|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|X|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@$D|Z|E|10|1|11]]|A|$]]|$1|F|3|G|5|6|7|12|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|13|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|14|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|15|8|@]|9|@]|A|$K|L]]|$1|Q|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|C]]]]

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

<p><a href="http://jsfiddle.net/duwood/sTX7y/">http://jsfiddle.net/duwood/sTX7y/</a></p>

<p>HTML</p>

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

<p>JS</p>

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

    var table = $('&lt;table /&gt;');

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

// Insert into DOM
$('#excel_table').html(table);
}
</code></pre>


blocks|key|1367903|text|对于像我这样的未来谷歌用户，我使用了@tatu的概念，并将其转换为一个对象数组。这个简单的函数接受一串粘贴的excel+(或Google+sheet)数据(最好是从textarea中)，并将其转换为一个对象数组。它使用第一行作为列/属性名称。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1367904|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%2B%2B)+{
++++++++var+o+=+{};
++++++++var+data+=+rows[rowNr].split('\t');

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

++++++++objects.push(o);
++++}

++++return+objects;
}|code-block|syntax|javascript|1367905|希望它能在未来帮助某些人。|1367906|entityMap^0|2A|8|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

<p>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 <code>textarea</code>) and turns it into an array of objects. It uses the first row for column/property names.</p>

<pre><code>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 &lt; rows.length; rowNr++) {
        var o = {};
        var data = rows[rowNr].split('\t');

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

        objects.push(o);
    }

    return objects;
}
</code></pre>

<p>Hopefully it helps someone in the future.</p>


blocks|key|1152849|text|更新:只有在使用ONLYOFFICE而不是MS+Excel时才是这样。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1152850|实际上，在这里提供的所有答案以及接受的答案中都有一个流程。流程是，每当你在excel中有一个空单元格并复制时，在剪贴板中你有两个相邻的制表符，所以在拆分后，你在数组中得到一个额外的项，然后它在该行中显示为一个额外的单元格，并将所有其他单元格移动一个。因此，为了避免这种情况，您基本上需要将字符串中的所有双重制表符(仅相邻的制表符)替换为一个制表符字符，然后才对其进行拆分。|1152851|@userfuser的jsfiddle的更新版本可以通过使用removeExtraTabs过滤粘贴的数据来解决这个问题|1152852|http://jsfiddle.net/sTX7y/794/|offset|length|1152853|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>'+%2B+cells[x]+%2B+'</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');
++++})
++})
})|code-block|syntax|javascript|1152854|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|U|0|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|X|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Y|8|@]|9|@$H|Z|I|10|1|11]]|A|$]]|$1|J|3|K|5|L|7|12|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|G]]]]

<p>UPDATE: This is only true if you use ONLYOFFICE instead of MS Excel.</p>

<p>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.</p>

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

<p><a href="http://jsfiddle.net/sTX7y/794/" rel="nofollow noreferrer">http://jsfiddle.net/sTX7y/794/</a></p>

<pre><code>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 = $('&lt;table /&gt;');

  for (var y in rows) {
    var cells = rows[y].split("\t");
    var row = $('&lt;tr /&gt;');
    for (var x in cells) {
      row.append('&lt;td&gt;' + cells[x] + '&lt;/td&gt;');
    }
    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');
    })
  })
})
</code></pre>


<p>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?</p>


Copy/Paste from Excel to a web page

数据库

大数据

jQuery

有没有一种标准的方法或者库可以从电子表格复制粘贴到web表单？当我从Excel中选择多个单元格时，我(显然)丢失了分隔符，所有单元格都被粘贴到web表单的一个单元格中。一定要在VB中完成吗？或者，是否可以在web表单上启动粘贴操作后即可完成处理？

问从Excel复制/粘贴到网页
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从Excel复制/粘贴到网页EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从Excel复制/粘贴到网页
EN