如何使用javascript将html表格导出为ex​​cel [英] How to export html table to excel using javascript

问题描述

我的表的格式为

 < table id =mytable> 
< thead> 
< tr> 
< th>名称< / th> 
< th>地方< / th> 
< / tr> 
< / thead> 
< tbody> 
< tr> 
< td> adfas< / td> 
< td> asdfasf< / td> 
< / tr> 
< / tbody> 
< / table> 
  

我在网上找到了以下代码。但是，如果我使用thead和tbody标记，则它不起作用。

 函数write_to_excel（）{
 
 str =; 
 
 var mytable = document.getElementsByTagName（table）[0]; 
 var rowCount = mytable.rows.length; 
 var colCount = mytable.getElementsByTagName（tr）[0] .getElementsByTagName（td）。length; 
 
 var ExcelApp = new ActiveXObject（Excel.Application）; 
 var ExcelSheet = new ActiveXObject（Excel.Sheet）; 
 ExcelSheet.Application.Visible = true; （var i = 0; i  for（var j = 0; j  str = mytable）为
 
。的getElementsByTagName（ TR）[I] .getElementsByTagName（ TD）[J] .innerHTML; 
 ExcelSheet.ActiveSheet.Cells（i + 1，j + 1）.Value = str; 
} 
} 
  

解决方案

原因您在互联网上找到的解决方案无效是因为 var colCount 开头的行。变量 mytable 只有两个元素< thead> 和< tbody> 。 var colCount 这一行正在寻找 mytable 中< tr> 。你可以做的最好的事情是给你的< thead> 和< tbody> 一个id，然后抓取所有基于此的值。假设您有< thead id ='headers'> ：

  function write_headers_to_excel（）
 {
 str =; 
 
 var myTableHead = document.getElementById（'headers'）; 
 var rowCount = myTableHead.rows.length; 
 var colCount = myTableHead.getElementsByTagName（tr）[0] .getElementsByTagName（th）。length; 
 
 var ExcelApp = new ActiveXObject（Excel.Application）; 
 var ExcelSheet = new ActiveXObject（Excel.Sheet）; 
 ExcelSheet.Application.Visible = true; （var j = 0; j  {
 
为（var i = 0; i  {
 $ b str = myTableHead.getElementsByTagName（tr）[i] .getElementsByTagName（th）[j] .innerHTML; 
 ExcelSheet.ActiveSheet.Cells（i + 1，j + 1）.Value = str; 
} 
} 
 
} 
  

然后对< tbody> 标签做同样的事情。

编辑：我也强烈推荐使用jQuery。它可以缩短为：

 函数write_to_excel（）
 {
 var ExcelApp = new ActiveXObject Excel.Application）; 
 var ExcelSheet = new ActiveXObject（Excel.Sheet）; 
 ExcelSheet.Application.Visible = true; 
 
 $（'th，td'）。each（function（i）{
 ExcelSheet.ActiveSheet.Cells（i + 1，i + 1）.Value = this.innerHTML; 
}）; 
} 
  

当然，这会给你一些格式问题，但你可以计算出你想如何在Excel中格式化。

编辑：要回答你关于如何为 n 表格数目做这个问题，jQuery会这样做已经。要在原始Javascript中执行此操作，请抓取所有表格，然后更改函数以便能够将表格作为参数传递。例如：

  var tables = document.getElementsByTagName（'table'）; 
 for（var i = 0; i< tables.length; i ++）
 {
 write_headers_to_excel（tables [i]）; 
 write_bodies_to_excel（表[i]）; 
} 
  

然后更改函数write_headers_to_excel（）到函数write_headers_to_excel（table）。然后更改 var myTableHead = document.getElementById（'headers'）; to var myTableHead = table.getElementsByTagName（'thead'）[0]; 。与你的 write_bodies_to_excel（）一样，或者你想设置它。

My table is in format

<table id="mytable">
<thead>
  <tr>
    <th>name</th>
    <th>place</th>
  </tr>
</thead>
<tbody>
<tr>
   <td>adfas</td>
   <td>asdfasf</td>
</tr>
</tbody>
</table>

I found the following code online. But it doesn't work if i use "thead" and "tbody" tags

function write_to_excel() {

    str = "";

    var mytable = document.getElementsByTagName("table")[0];
    var rowCount = mytable.rows.length;
    var colCount = mytable.getElementsByTagName("tr")[0].getElementsByTagName("td").length;

    var ExcelApp = new ActiveXObject("Excel.Application");
    var ExcelSheet = new ActiveXObject("Excel.Sheet");
    ExcelSheet.Application.Visible = true;

    for (var i = 0; i < rowCount; i++) {
        for (var j = 0; j < colCount; j++) {
            str = mytable.getElementsByTagName("tr")[i].getElementsByTagName("td")[j].innerHTML;
            ExcelSheet.ActiveSheet.Cells(i + 1, j + 1).Value = str;
        }
    }

解决方案

The reason the solution you found on the internet is no working is because of the line that starts var colCount. The variable mytable only has two elements being <thead> and <tbody>. The var colCount line is looking for all the elements within mytable that are <tr>. The best thing you can do is give an id to your <thead> and <tbody> and then grab all the values based on that. Say you had <thead id='headers'> :

function write_headers_to_excel() 
{
  str="";

  var myTableHead = document.getElementById('headers');
  var rowCount = myTableHead.rows.length;
  var colCount = myTableHead.getElementsByTagName("tr")[0].getElementsByTagName("th").length; 

var ExcelApp = new ActiveXObject("Excel.Application");
var ExcelSheet = new ActiveXObject("Excel.Sheet");
ExcelSheet.Application.Visible = true;

for(var i=0; i<rowCount; i++) 
{   
    for(var j=0; j<colCount; j++) 
    {           
        str= myTableHead.getElementsByTagName("tr")[i].getElementsByTagName("th")[j].innerHTML;
        ExcelSheet.ActiveSheet.Cells(i+1,j+1).Value = str;
    }
}

}

and then do the same thing for the <tbody> tag.

EDIT: I would also highly recommend using jQuery. It would shorten this up to:

function write_to_excel() 
{
 var ExcelApp = new ActiveXObject("Excel.Application");
 var ExcelSheet = new ActiveXObject("Excel.Sheet");
 ExcelSheet.Application.Visible = true; 

  $('th, td').each(function(i){
    ExcelSheet.ActiveSheet.Cells(i+1,i+1).Value = this.innerHTML;
  });
}

Now, of course, this is going to give you some formatting issues but you can work out how you want it formatted in Excel.

EDIT: To answer your question about how to do this for n number of tables, the jQuery will do this already. To do it in raw Javascript, grab all the tables and then alter the function to be able to pass in the table as a parameter. For instance:

var tables = document.getElementsByTagName('table');
for(var i = 0; i < tables.length; i++)
{
  write_headers_to_excel(tables[i]);
  write_bodies_to_excel(tables[i]);
}

Then change the function write_headers_to_excel() to function write_headers_to_excel(table). Then change var myTableHead = document.getElementById('headers'); to var myTableHead = table.getElementsByTagName('thead')[0];. Same with your write_bodies_to_excel() or however you want to set that up.

这篇关于如何使用javascript将html表格导出为ex​​cel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！