HTML導出Excel文件(相容IE及所有瀏覽器)

註意：IE瀏覽器需要以下設置：打開IE，在常用工具欄中選擇“工具” >Internet選項 >選擇"安全"標簽頁 >選擇"自定義級別" >彈出的視窗中找到ActiveX控制項和插件 >將"ActiveX控制項自動提示""對沒有標記安全的ActiveX控制項進行初始化和腳本運行""下載未簽名的Active ...

註意：IE瀏覽器需要以下設置：

打開IE，在常用工具欄中選擇“工具”--->Internet選項---->選擇"安全"標簽頁--->選擇"自定義級別"--->彈出的視窗中找到ActiveX控制項和插件--->
將"ActiveX控制項自動提示"
"對沒有標記安全的ActiveX控制項進行初始化和腳本運行"
"下載未簽名的ActiveX控制項"
三項選擇啟用即可!

HtmlExportToExcel.js 代碼

  1 /*
  2  * HTML導出Excel文件(相容IE及所有瀏覽器)
  3  * @param {any} tableid  table父元素ID
  4  * @param {any} filename 文件名稱
  5  */
  6 function HtmlExportToExcel(tableid, filename) {
  7     if (getExplorer() === 'ie' || getExplorer() === undefined) {
  8         HtmlExportToExcelForIE(tableid, filename);
  9     }
 10     else {
 11         HtmlExportToExcelForEntire(tableid, filename);
 12     }
 13 }
 14 
 15 //IE瀏覽器導出Excel
 16 function HtmlExportToExcelForIE(tableId, filename) {
 17     try {
 18         var oXL = new ActiveXObject("excel.Application");
 19         //oXL.Visible = true;
 20         //oXL.ScreenUpdating = false;
 21     } catch (e1) {
 22         try {
 23             oXL = new ActiveXObject("et.Application");
 24         } catch (e2) {
 25             alert(e2.description + "\n\n\n要使用EXCEL對象，您必須安裝Excel電子錶格軟體\n或者,需要安裝Kingsoft ET軟體\n\n同時瀏覽器須使用“ActiveX 控制項”，您的瀏覽器須允許執行控制項。");
 26             return;
 27         }
 28     }
 29     //創建AX對象excel
 30     var oWB = oXL.Workbooks.Add();
 31     //獲取workbook對象
 32     var xlsheet = oWB.Worksheets(1);
 33 
 34     var elTable = document.getElementById(tableId);
 35 
 36     //替換掉表格td中隱藏的html元素
 37     var tableHtml = ReplaceHtml(elTable.innerHTML);
 38 
 39     var newTable = document.getElementById("newData");
 40     //console.log();
 41     newTable.innerHTML = tableHtml;
 42 
 43     //激活當前sheet
 44     var sel = document.body.createTextRange();
 45     sel.moveToElementText(newTable);
 46     //把表格中的內容移到TextRange中
 47     sel.select;
 48     //全選TextRange中內容
 49     sel.execCommand("Copy");
 50     //複製TextRange中內容
 51     xlsheet.Paste();
 52     //粘貼到活動的EXCEL中
 53     oXL.Visible = true;
 54     //設置excel可見屬性
 55 
 56     newTable.innerHTML = "";
 57 
 58     try {
 59         //設置 sheet 名稱
 60         xlsheet.Name = filename;
 61         var fname = oXL.Application.GetSaveAsFilename(filename + ".xls", "Excel Spreadsheets (*.xls), *.xls");
 62     } catch (e) {
 63         print("Nested catch caught " + e);
 64     } finally {
 65         oWB.SaveAs(fname);
 66         oWB.Close();
 67         //xls.visible = false;
 68         oXL.ScreenUpdating = true;
 69         oXL.Quit();
 70     }
 71 }
 72 
 73 //非IE瀏覽器導出Excel
 74 var HtmlExportToExcelForEntire = (function () {
 75     var uri = 'data:application/vnd.ms-excel;base64,',
 76         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]-->' +
 77             /**********這部分是載入表格的樣式  沒有樣式可以省略 start**********/
 78             '<style type="text/css">' +
 79             '.tablefrom {width: 100%;border-collapse: collapse;}' +
 80             '.tablefrom, .tablefrom td, .tablefrom th {text-align: center;font: 12px Arial, Helvetica, sans-serif;border: 1px solid #fff;}' +
 81             '.tablefrom th{background:#328aa4;color:#fff;}' +
 82             '.tablefrom td{background:#e5f1f4;}' +
 83             '.tablefrom .BlueBgColor td {color: #fff;background-color: #0070c0;}' +
 84             '.tablefrom .LightBlueBgColor td {color: #000000;background-color: #bdd7ee;}' +
 85             '.tablefrom  tr .BlueBgColorTd { color: #fff;background-color: #0070c0;}' +
 86             '.tablefrom tr .LightBlueBgColorTd {color: #000000;background-color: #bdd7ee;}' +
 87             '</style>'
 88             /**********這部分是載入表格的樣式  沒有樣式可以省略 end**********/
 89             + '</head><body>{table}</body></html>',
 90         base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))); },
 91         format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }); };
 92     return function (table, name) {
 93         if (!table.nodeType) { table = document.getElementById(table); }
 94         //替換掉表格td中隱藏的html元素
 95         var strHTML = ReplaceHtml(table.innerHTML);
 96         var ctx = { worksheet: name || 'Worksheet', table: strHTML };
 97         
 98         document.getElementById("dlink").href = uri + base64(format(template, ctx));
 99         document.getElementById("dlink").download = name + ".xls";
100         document.getElementById("dlink").click();
101     };
102 })();
103 
104 //獲取當前使用瀏覽器
105 function getExplorer() {
106     var explorer = window.navigator.userAgent;
107     //ie 
108     if (explorer.indexOf("MSIE") >= 0) {
109         return 'ie';
110     }
111     //firefox 
112     else if (explorer.indexOf("Firefox") >= 0) {
113         return 'Firefox';
114     }
115     //Chrome
116     else if (explorer.indexOf("Chrome") >= 0) {
117         return 'Chrome';
118     }
119     //Opera
120     else if (explorer.indexOf("Opera") >= 0) {
121         return 'Opera';
122     }
123     //Safari
124     else if (explorer.indexOf("Safari") >= 0) {
125         return 'Safari';
126     }
127 }
128 
129 //將隱藏的HTML元素替換掉
130 function ReplaceHtml(tableHtml) {
131     var radioValue = $('input[name="bedStatus"]:checked ').val();
132     if (radioValue === 'yuan') {
133         tableHtml = tableHtml.replace(/<span class="span_wanyuan" [^<>]*?>(.*?)<\/span>/gi, "");
134     }
135     else if (radioValue === 'wanyuan') {
136         tableHtml = tableHtml.replace(/<span class="span_yuan" [^<>]*?>(.*?)<\/span>/gi, "");
137     }
138     return tableHtml;
139 }

View Code

另外，我的功能需要替換掉td中隱藏的html元素，所以需要將新的table複製到另一個隱藏的div中。

頁面html代碼：

 1 <div id="divData">
 2     <table class="tablefrom" id="tbData" style="width: 100%; border-collapse: collapse;" border="0" cellspacing="0">
 3         <tbody>
 4             <tr>
 5                 <th rowspan="2" colspan="2" scope="col">序號</th>
 6                 <th style="min-width: 70px;" rowspan="2" scope="col">項目</th>
 7                 <th style="min-width: 50px;" rowspan="2" scope="col">維度</th>
 8                 <th style="min-width: 50px;" rowspan="2" scope="col">本月資金</th>
 9                 <th rowspan="2" colspan="1" scope="col">某部門</th>
10                 <th colspan="2" scope="col">支付</th>
11             </tr>
12             <tr>
13                 <th style="min-width: 50px;" scope="col">執行</th>
14                 <th style="min-width: 50px;" scope="col">餘額</th>
15             </tr>
16             <tr>
17                 <td style="color: rgb(0, 0, 0); font-weight: bold; background-color: rgb(189, 215, 238);" rowspan="163" scope="col">經營費用類</td>
18                 <td style="min-width: 50px;" rowspan="3" scope="col">1</td>
19                 <td rowspan="3" scope="col">銷售退款</td>
20                 <td scope="col">實際</td>
21                 <td style="color: rgb(0, 0, 0); background-color: rgb(189, 215, 238);" scope="col"><span class="span_yuan">100000</span><span class="span_wanyuan" style="display: none;">10</span></td>
22                 <td scope="col"><span class="span_yuan">100000</span><span class="span_wanyuan" style="display: none;">10</span></td>
23                 <td scope="col"><span class="span_yuan">100000</span><span class="span_wanyuan" style="display: none;">10</span></td>
24                 <td scope="col"><span class="span_yuan">900000</span><span class="span_wanyuan" style="display: none;">90</span></td>
25             </tr>
26             <tr>
27                 <td scope="col">計劃</td>
28                 <td style="color: rgb(0, 0, 0); background-color: rgb(189, 215, 238);" scope="col"><span class="span_yuan">1000000</span><span class="span_wanyuan" style="display: none;">100</span></td>
29                 <td scope="col"><span class="span_yuan">1000000</span><span class="span_wanyuan" style="display: none;">100</span></td>
30                 <td scope="col">-</td>
31                 <td scope="col">-</td>
32             </tr>
33             <tr>
34                 <td scope="col">差額</td>
35                 <td style="color: rgb(0, 0, 0); background-color: rgb(189, 215, 238);" scope="col"><span class="span_yuan">900000</span><span class="span_wanyuan" style="display: none;">90</span></td>
36                 <td scope="col"><span class="span_yuan">900000</span><span class="span_wanyuan" style="display: none;">90</span></td>
37                 <td scope="col">-</td>
38                 <td scope="col">-</td>
39             </tr>
40         </tbody>
41     </table>
42 </div>
43 <div id="newData" style="display: none;"></div>

View Code