使用 Javascript 将 HTML 表格导出到 Excel

Question

提问by Shrinivas Pai

I am exporting HTMLtable to xlsforamt. After exporting if you open it in Libre Office, it works fine but the same opens a blank screen in Microsoft Office.

我正在将HTML表格导出到xls格式。导出后，如果您在 Libre Office 中打开它，它工作正常，但同样会在 Microsoft Office 中打开一个空白屏幕。

I don't want a jquerysolution please provide any javascriptsolution. Please help.

我不想要jquery解决方案，请提供任何javascript解决方案。请帮忙。

function fnExcelReport() {
    var tab_text = "<table border='2px'><tr bgcolor='#87AFC6'>";
    var textRange;
    var j = 0;
    tab = document.getElementById('table'); // id of table

    for (j = 0; j < tab.rows.length; j++) {
        tab_text = tab_text + tab.rows[j].innerHTML + "</tr>";
        //tab_text=tab_text+"</tr>";
    }

    tab_text = tab_text + "</table>";
    tab_text = tab_text.replace(/<A[^>]*>|<\/A>/g, ""); //remove if u want links in your table
    tab_text = tab_text.replace(/<img[^>]*>/gi, ""); // remove if u want images in your table
    tab_text = tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // reomves input params

    var ua = window.navigator.userAgent;
    var msie = ua.indexOf("MSIE ");
    if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) // If Internet Explorer
    {
        txtArea1.document.open("txt/html", "replace");
        txtArea1.document.write(tab_text);
        txtArea1.document.close();
        txtArea1.focus();
        sa = txtArea1.document.execCommand("SaveAs", true, "Say Thanks to Sumit.xls");
    } else //other browser not tested on IE 11
        sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));

    return (sa);
}

<iframe id="txtArea1" style="display:none"></iframe>

    Call this function on

        <button id="btnExport" onclick="fnExcelReport();"> EXPORT 
        </button>

    <table id="table">
  <thead>
        <tr>
            <th>Head1</th>
            <th>Head2</th>
            <th>Head3</th>
            <th>Head4</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>11</td>
            <td>12</td>
            <td>13</td>
            <td>14</td>
        </tr>
        <tr>
            <td>21</td>
            <td>22</td>
            <td>23</td>
            <td>24</td>
        </tr>
        <tr>
            <td>31</td>
            <td>32</td>
            <td>33</td>
            <td>34</td>
        </tr>
        <tr>
            <td>41</td>
            <td>42</td>
            <td>43</td>
            <td>44</td>
        </tr>
    </tbody>
    </table>

Answer 1

回答by S McCrohan

On 2016-07-12, Microsoft pushed a security updatefor Microsoft Office. One of the effects of this update was to prevent HTML files from non-trusted domains from being opened by Excel, because they cannot be opened in Protected mode.

2016 年 7 月 12 日，微软推送了Microsoft Office的安全更新。此更新的影响之一是防止来自不受信任域的 HTML 文件被 Excel 打开，因为它们无法在保护模式下打开。

There is ALSO a registry setting that prevents Excel from opening files with the .XLS file extension whose contents do not match the official XLS file format, though it defaults to 'warn', not 'deny'.

还有一个注册表设置可以防止 Excel 打开带有 .XLS 文件扩展名且内容与官方 XLS 文件格式不匹配的文件，尽管它默认为“警告”，而不是“拒绝”。

Prior to this change, it was possible to save HTML data to a file with an XLS extension, and Excel would open it correctly - possibly giving a warning firstthat the file did not match the Excel format, depending on the user's value for the ExtensionHardeningregistry key (or related config values).

在此更改之前，可以将 HTML 数据保存到具有 XLS 扩展名的文件中，并且 Excel 将正确打开它 - 可能首先发出警告，该文件与 Excel 格式不匹配，具体取决于用户的ExtensionHardening注册表值键（或相关的配置值）。

Microsoft has made a knowledge-baseentry about the new behavior with some suggested workarounds.

Microsoft 已经创建了一个关于新行为的知识库条目，并提供了一些建议的解决方法。

Several web applications that previously relied on exporting HTML files as XLS have run into trouble as a result of the update - SalesForceis one example.

一些以前依赖将 HTML 文件导出为 XLS 的 Web 应用程序由于更新而遇到了麻烦——SalesForce就是一个例子。

Answers from before July 12th 2016 to this and similar questions are likely to now be invalid.

2016 年 7 月 12 日之前对此和类似问题的回答现在可能无效。

It's worth noting that files produced ON THE BROWSER from remote data do not fall afoul of this protection; it only impedes files downloaded from a remote source that is not trusted. Therefore one possible approach is to generate the .XLS-labelled HTML file locally on the client.

值得注意的是，从远程数据在浏览器上生成的文件不会违反这种保护；它只会阻止从不受信任的远程来源下载文件。因此，一种可能的方法是在客户端本地生成带有 .XLS 标签的 HTML 文件。

Another, of course, is to produce a valid XLS file, which Excel will then open in Protected mode.

当然，另一个是生成有效的 XLS 文件，然后 Excel 将在保护模式下打开该文件。

UPDATE: Microsoft has released a patch to correct this behavior: https://support.microsoft.com/en-us/kb/3181507

更新：微软已经发布了一个补丁来纠正这种行为：https: //support.microsoft.com/en-us/kb/3181507

Answer 2

回答by jlynch630

SheetJSseems perfect for this.

SheetJS似乎很适合这个。

To export your table as an excel file use the code in this link(along with SheetJS)

要将您的表格导出为 Excel 文件，请使用此链接中的代码（以及 SheetJS）

Just plug in your tableelement's id into export_table_to_excel

只需将您的table元素的 id 插入export_table_to_excel

See Demo

看演示

Answer 3

回答by ElMesa

If CSV format is good for you, here is an example.

如果 CSV 格式适合您，这里是一个示例。

Ok...I just read a comment where you explicitly say it isn't good for you. My bad for not learning to read before coding.

好的...我刚刚读到一条评论，您明确表示这对您不利。我的坏处是在编码之前不学习阅读。

As far I know, Excel can handle CSV.

据我所知，Excel 可以处理 CSV。

function fnExcelReport() {
var i, j;
var csv = "";

var table = document.getElementById("table");

var table_headings = table.children[0].children[0].children;
var table_body_rows = table.children[1].children;

var heading;
var headingsArray = [];
for(i = 0; i < table_headings.length; i++) {
  heading = table_headings[i];
  headingsArray.push('"' + heading.innerHTML + '"');
}

csv += headingsArray.join(',') + ";\n";

var row;
var columns;
var column;
var columnsArray;
for(i = 0; i < table_body_rows.length; i++) {
  row = table_body_rows[i];
  columns = row.children;
  columnsArray = [];
  for(j = 0; j < columns.length; j++) {
      var column = columns[j];
      columnsArray.push('"' + column.innerHTML + '"');
  }
  csv += columnsArray.join(',') + ";\n";
}

  download("export.csv",csv);
}

//From: http://stackoverflow.com/a/18197511/2265487
function download(filename, text) {
    var pom = document.createElement('a');
    pom.setAttribute('href', 'data:text/csv;charset=utf-8,' + encodeURIComponent(text));
    pom.setAttribute('download', filename);

    if (document.createEvent) {
        var event = document.createEvent('MouseEvents');
        event.initEvent('click', true, true);
        pom.dispatchEvent(event);
    }
    else {
        pom.click();
    }
}

<iframe id="txtArea1" style="display:none"></iframe>

Call this function on

<button id="btnExport" onclick="fnExcelReport();">EXPORT
</button>

<table id="table">
  <thead>
    <tr>
      <th>Head1</th>
      <th>Head2</th>
      <th>Head3</th>
      <th>Head4</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>11</td>
      <td>12</td>
      <td>13</td>
      <td>14</td>
    </tr>
    <tr>
      <td>21</td>
      <td>22</td>
      <td>23</td>
      <td>24</td>
    </tr>
    <tr>
      <td>31</td>
      <td>32</td>
      <td>33</td>
      <td>34</td>
    </tr>
    <tr>
      <td>41</td>
      <td>42</td>
      <td>43</td>
      <td>44</td>
    </tr>
  </tbody>
</table>

Answer 4

回答by Tarek Salah uddin Mahmud

try this

尝试这个

<table id="exportable">
<thead>
      <tr>
          //headers
      </tr>
</thead>
<tbody>
         //rows
</tbody>
</table>

Script for this

为此编写脚本

var blob = new Blob([document.getElementById('exportable').innerHTML], {
            type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"
        });
saveAs(blob, "Report.xls");

Answer 5

回答by Connor Meeks

add this to your head:

将此添加到您的头上：

<meta http-equiv="content-type" content="text/plain; charset=UTF-8"/>

and add this as your javascript:

并将其添加为您的 javascript：

<script type="text/javascript">
var tableToExcel = (function() {
  var uri = 'data:application/vnd.ms-excel;base64,'
    , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--><meta http-equiv="content-type" content="text/plain; charset=UTF-8"/></head><body><table>{table}</table></body></html>'
    , base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
    , format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) }
  return function(table, name) {
    if (!table.nodeType) table = document.getElementById(table)
    var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
    window.location.href = uri + base64(format(template, ctx))
  }
})()
</script>

Jfiddle: http://jsfiddle.net/cmewv/537/

Jfiddle：http: //jsfiddle.net/cmewv/537/

Answer 6

回答by Anuruddh Mishra

<hrml>
  <head>
     <script language="javascript">
      function exportF() {
  //Format your table with form data
  document.getElementById("input").innerHTML = document.getElementById("text").value;
   document.getElementById("input1").innerHTML = document.getElementById("text1").value;
  var table = document.getElementById("table");
  var html = table.outerHTML;

  var url = 'data:application/vnd.C:\Users\WB-02\desktop\Book1.xlsx,' + escape(html); // Set your html table into url 
  var link = document.getElementById("downloadLink");
  link.setAttribute("href", url);
  link.setAttribute("download", "export.xls"); // Choose the file name
  link.click(); // Download your excel file   
  return false;
}
    </script>
 </head>
 <body>
<form onsubmit="return exportF()">
  <input id="text" type="text" />
  <input id="text1" type="text" />
  <input type="submit" />
</form>

<table id="table" style="display: none">
  <tr>
    <td id="input">
    <td id="input1">
    </td>
  </tr>
</table>
<a style="display: none" id="downloadLink"></a>
</body>
</html>

Answer 7

回答by Aman Kumar Gupta

You can use tableToExcel.jsto export table in excel file.

您可以使用tableToExcel.js导出 Excel 文件中的表格。

This works in a following way :

这以下列方式工作：

1). Include this CDN in your project/file

1）。在您的项目/文件中包含此 CDN

<script src="https://cdn.jsdelivr.net/gh/linways/[email protected]/dist/tableToExcel.js"></script>

2). Either Using JavaScript:

2）。要么使用 JavaScript：

<button id="btnExport" onclick="exportReportToExcel(this)">EXPORT REPORT</button>

function exportReportToExcel() {
  let table = document.getElementsByTagName("table"); // you can use document.getElementById('tableId') as well by providing id to the table tag
  TableToExcel.convert(table[0], { // html code may contain multiple tables so here we are refering to 1st table tag
    name: `export.xls`, // fileName you could use any name
    sheet: {
      name: 'Sheet 1' // sheetName
    }
  });
}

3). Or by Using Jquery

3）。或者通过使用 Jquery

<button id="btnExport">EXPORT REPORT</button>

$(document).ready(function(){
    $("#btnExport").click(function() {
        let table = document.getElementsByTagName("table");
        TableToExcel.convert(table[0], { // html code may contain multiple tables so here we are refering to 1st table tag
           name: `export.xls`, // fileName you could use any name
           sheet: {
              name: 'Sheet 1' // sheetName
           }
        });
    });
});

You may refer to this github link for any other information

您可以参考此 github 链接以获取任何其他信息

https://github.com/linways/table-to-excel/tree/master

or for referring the live example visit the following link

或参考现场示例访问以下链接

https://codepen.io/rohithb/pen/YdjVbb

This will download the export.xls file

这将下载 export.xls 文件

Hope this will help someone :-)

希望这会帮助某人:-)

Answer 8

回答by travellerguy

?f you have a too much column , try to use this code. You can split easily.

?如果您有太多列，请尝试使用此代码。您可以轻松拆分。

function iterate( tab,  startIndex , rowCount){

    var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
    var textRange; var j=0;
    J=startIndex;

    for(j = startIndex ; j < rowCount ; j++) 
    {   
 
        tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
        //tab_text=tab_text+"</tr>";
    }

    tab_text=tab_text+"</table>";
    tab_text= tab_text.replace(/<A[^>]*>|<\/A>/g, "");//remove if u want links in your table
    tab_text= tab_text.replace(/<img[^>]*>/gi,""); // remove if u want images in your table
    tab_text= tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // reomves input params

    var ua = window.navigator.userAgent;
    var msie = ua.indexOf("MSIE "); 

    if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // If Internet Explorer
    {
        txtArea1.document.open("txt/html","replace");
        txtArea1.document.write(tab_text);
        txtArea1.document.close();
        txtArea1.focus(); 
        sa=txtArea1.document.execCommand("SaveAs",true,"Say Thanks to Sumit.xls");
    }  
    else                 //other browser not tested on IE 11
        sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text)); 
  
  
} 
 
function fnExcelReport()
{
    var indirilecekSayi = 250;
  
    var toplamSatirSayisi = 0;

    var baslangicSAyisi = 0;

    var sonsatirsayisi = 0;
   
    tab = document.getElementById('myTable'); // id of table
    var maxRowCount = tab.rows.length;
    toplamSatirSayisi = maxRowCount;

  
  
    sonsatirsayisi=indirilecekSayi;
    
  
    
    var kalan = toplamSatirSayisi % indirilecekSayi;

    var Kalans?zToplamSatir=ToplamSatirSayisi-kalan;
    var kacKati=Tsh / indirilecekSayi;



 alert(maxRowCount);
    alert(kacKati);


    for (let index = 0; index <= kacKati; index++) {
        
        if (index==kacKati) {
           
            baslangicSAyisi =sonsatirsayisi;
           
            sonsatirsayisi=sonsatirsayisi+kalan;
           
            iterate(tab, baslangicSAyisi, sonsatirsayisi);
      
        }else{

           
           
            iterate(tab , baslangicSAyisi , sonsatirsayisi);
          
            baslangicSAyisi=sonsatirsayisi;
           
            
            sonsatirsayisi=sonsatirsayisi+indirilecekSayi;
            if(sonsatirsayisi>ToplamSatirSayisi){
                sonsatirsayisi=baslangicSAyisi;
            }

        }
            

    }

}

使用 Javascript 将 HTML 表格导出到 Excel

提问by Shrinivas Pai

回答by S McCrohan

回答by jlynch630

回答by ElMesa

回答by Tarek Salah uddin Mahmud

回答by Connor Meeks

回答by Anuruddh Mishra

回答by Aman Kumar Gupta

回答by travellerguy

相关推荐

最近更新

标签

使用 Javascript 将 HTML 表格导出到 Excel

提问by Shrinivas Pai

回答by S McCrohan

回答by jlynch630

回答by ElMesa

回答by Tarek Salah uddin Mahmud

回答by Connor Meeks

回答by Anuruddh Mishra

回答by Aman Kumar Gupta

回答by travellerguy

相关推荐

Javascript `return await promise` 和 `return promise` 之间的区别

JavaScript 计算更亮的颜色

Javascript 为什么 requestAnimationFrame 比 setInterval 或 setTimeout 更好

检查对象是否是文本框 - javascript

相关推荐

最近更新

标签