js导入导出excel(实例代码)
1 <!DOCTYPE html> 2 <html> 3 4 <head lang="en"> 5 <meta charset="UTF-8"> 6 <title>html 表格导出道</title> 7 <script src="jquery-3.2.1.min.js"></script> 8 <script src="jquery.table2excel.min.js"></script> 9 <script language="JavaScript" type="text/javascript"> 10 //第一种方法 11 function method1(tableid) { 12 13 var curTbl = document.getElementById(tableid); 14 var oXL = new ActiveXObject("Excel.Application"); 15 var oWB = oXL.Workbooks.Add(); 16 var oSheet = oWB.ActiveSheet; 17 var sel = document.body.createTextRange(); 18 sel.moveToElementText(curTbl); 19 sel.select(); 20 sel.execCommand("Copy"); 21 oSheet.Paste(); 22 oXL.Visible = true; 23 24 } 25 //第二种方法 26 function method2(tableid) { 27 28 var curTbl = document.getElementById(tableid); 29 var oXL = new ActiveXObject("Excel.Application"); 30 var oWB = oXL.Workbooks.Add(); 31 var oSheet = oWB.ActiveSheet; 32 var Lenr = curTbl.rows.length; 33 for (i = 0; i < Lenr; i++) { 34 var Lenc = curTbl.rows(i).cells.length; 35 for (j = 0; j < Lenc; j++) { 36 oSheet.Cells(i + 1, j + 1).value = curTbl.rows(i).cells(j).innerText; 37 38 } 39 40 } 41 oXL.Visible = true; 42 } 43 //第三种方法 44 function getXlsFromTbl(inTblId, inWindow) { 45 46 try { 47 var allStr = ""; 48 var curStr = ""; 49 if (inTblId != null && inTblId != "" && inTblId != "null") { 50 51 curStr = getTblData(inTblId, inWindow); 52 53 } 54 if (curStr != null) { 55 allStr += curStr; 56 } 57 58 else { 59 60 alert("你要导出的表不存在"); 61 return; 62 } 63 var fileName = getExcelFileName(); 64 doFileExport(fileName, allStr); 65 66 } 67 68 catch (e) { 69 70 alert("导出发生异常:" + e.name + "->" + e.description + "!"); 71 72 } 73 74 } 75 76 function getTblData(inTbl, inWindow) { 77 78 var rows = 0; 79 var tblDocument = document; 80 if (!!inWindow && inWindow != "") { 81 82 if (!document.all(inWindow)) { 83 return null; 84 } 85 86 else { 87 tblDocument = eval(inWindow).document; 88 } 89 90 } 91 92 var curTbl = tblDocument.getElementById(inTbl); 93 var outStr = ""; 94 if (curTbl != null) { 95 for (var j = 0; j < curTbl.rows.length; j++) { 96 for (var i = 0; i < curTbl.rows[j].cells.length; i++) { 97 98 if (i == 0 && rows > 0) { 99 outStr += " t"; 100 rows -= 1; 101 } 102 103 outStr += curTbl.rows[j].cells[i].innerText + "t"; 104 if (curTbl.rows[j].cells[i].colSpan > 1) { 105 for (var k = 0; k < curTbl.rows[j].cells[i].colSpan - 1; k++) { 106 outStr += " t"; 107 } 108 } 109 if (i == 0) { 110 if (rows == 0 && curTbl.rows[j].cells[i].rowSpan > 1) { 111 rows = curTbl.rows[j].cells[i].rowSpan - 1; 112 } 113 } 114 } 115 outStr += "rn"; 116 } 117 } 118 119 else { 120 outStr = null; 121 alert(inTbl + "不存在 !"); 122 } 123 return outStr; 124 } 125 126 function getExcelFileName() { 127 var d = new Date(); 128 var curYear = d.getYear(); 129 var curMonth = "" + (d.getMonth() + 1); 130 var curDate = "" + d.getDate(); 131 var curHour = "" + d.getHours(); 132 var curMinute = "" + d.getMinutes(); 133 var curSecond = "" + d.getSeconds(); 134 if (curMonth.length == 1) { 135 curMonth = "0" + curMonth; 136 } 137 138 if (curDate.length == 1) { 139 curDate = "0" + curDate; 140 } 141 142 if (curHour.length == 1) { 143 curHour = "0" + curHour; 144 } 145 146 if (curMinute.length == 1) { 147 curMinute = "0" + curMinute; 148 } 149 150 if (curSecond.length == 1) { 151 curSecond = "0" + curSecond; 152 } 153 var fileName = "table" + "_" + curYear + curMonth + curDate + "_" 154 + curHour + curMinute + curSecond + ".csv"; 155 return fileName; 156 157 } 158 159 function doFileExport(inName, inStr) { 160 var xlsWin = null; 161 if (!!document.all("glbHideFrm")) { 162 xlsWin = glbHideFrm; 163 } 164 else { 165 var width = 6; 166 var height = 4; 167 var openPara = "left=" + (window.screen.width / 2 - width / 2) 168 + ",top=" + (window.screen.height / 2 - height / 2) 169 + ",scrollbars=no,width=" + width + ",height=" + height; 170 xlsWin = window.open("", "_blank", openPara); 171 } 172 xlsWin.document.write(inStr); 173 xlsWin.document.close(); 174 xlsWin.document.execCommand('Saveas', true, inName); 175 xlsWin.close(); 176 177 } 178 179 //第四种 180 function method4(tableid) { 181 182 var curTbl = document.getElementById(tableid); 183 var oXL; 184 try { 185 oXL = new ActiveXObject("Excel.Application"); //创建AX对象excel 186 } catch (e) { 187 alert("无法启动Excel!\n\n如果您确信您的电脑中已经安装了Excel，" + "那么请调整IE的安全级别。\n\n具体操作：\n\n" + "工具 → Internet选项 → 安全 → 自定义级别 → 对没有标记为安全的ActiveX进行初始化和脚本运行 → 启用"); 188 return false; 189 } 190 var oWB = oXL.Workbooks.Add(); //获取workbook对象 191 var oSheet = oWB.ActiveSheet;//激活当前sheet 192 var sel = document.body.createTextRange(); 193 sel.moveToElementText(curTbl); //把表格中的内容移到TextRange中 194 sel.select(); //全选TextRange中内容 195 sel.execCommand("Copy");//复制TextRange中内容 196 oSheet.Paste();//粘贴到活动的EXCEL中 197 oXL.Visible = true; //设置excel可见属性 198 var fname = oXL.Application.GetSaveAsFilename("将table导出到excel.xls", "Excel Spreadsheets (*.xls), *.xls"); 199 oWB.SaveAs(fname); 200 oWB.Close(); 201 oXL.Quit(); 202 } 203 204 205 //第五种方法 206 var idTmr; 207 function getExplorer() { 208 var explorer = window.navigator.userAgent; 209 //ie 210 if (explorer.indexOf("MSIE") >= 0) { 211 return 'ie'; 212 } 213 //firefox 214 else if (explorer.indexOf("Firefox") >= 0) { 215 return 'Firefox'; 216 } 217 //Chrome 218 else if (explorer.indexOf("Chrome") >= 0) { 219 return 'Chrome'; 220 } 221 //Opera 222 else if (explorer.indexOf("Opera") >= 0) { 223 return 'Opera'; 224 } 225 //Safari 226 else if (explorer.indexOf("Safari") >= 0) { 227 return 'Safari'; 228 } 229 } 230 function method5(tableid) { 231 if (getExplorer() == 'ie') { 232 var curTbl = document.getElementById(tableid); 233 var oXL = new ActiveXObject("Excel.Application"); 234 var oWB = oXL.Workbooks.Add(); 235 var xlsheet = oWB.Worksheets(1); 236 var sel = document.body.createTextRange(); 237 sel.moveToElementText(curTbl); 238 sel.select(); 239 sel.execCommand("Copy"); 240 xlsheet.Paste(); 241 oXL.Visible = true; 242 243 try { 244 var fname = oXL.Application.GetSaveAsFilename("Excel.xls", "Excel Spreadsheets (*.xls), *.xls"); 245 } catch (e) { 246 print("Nested catch caught " + e); 247 } finally { 248 oWB.SaveAs(fname); 249 oWB.Close(savechanges = false); 250 oXL.Quit(); 251 oXL = null; 252 idTmr = window.setInterval("Cleanup();", 1); 253 } 254 255 } 256 else { 257 tableToExcel(tableid) 258 } 259 } 260 function Cleanup() { 261 window.clearInterval(idTmr); 262 CollectGarbage(); 263 } 264 var tableToExcel = (function () { 265 var uri = 'data:application/vnd.ms-excel;base64,', 266 template = '<html><head><meta charset="UTF-8"></head><body><table>{table}</table></body></html>', 267 base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }, 268 format = function (s, c) { 269 return s.replace(/{(\w+)}/g, 270 function (m, p) { return c[p]; }) 271 } 272 return function (table, name) { 273 if (!table.nodeType) table = document.getElementById(table) 274 var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML } 275 window.location.href = uri + base64(format(template, ctx)) 276 } 277 })() 278 279 </script> 280 <script language="JavaScript" type="text/javascript"> 281 $(document).ready(function () { 282 $("#btnExport").click(function () { 283 $("#tableExcel").table2excel({ 284 exclude: ".noExl", //过滤位置的 css 类名 285 filename: "沟通记录" + new Date().getTime() + ".xls", //文件名称 286 name: "Excel Document Name.xlsx", 287 exclude_img: true, 288 exclude_links: true, 289 exclude_inputs: true 290 291 }); 292 }); 293 }); 294 295 </script> 296 </head> 297 298 <body> 299 300 <div> 301 <button type="button" onclick="method1('tableExcel')">导出Excel方法一</button> 302 <button type="button" onclick="method2('tableExcel')">导出Excel方法二</button> 303 <button type="button" onclick="getXlsFromTbl('tableExcel','myDiv')">导出Excel方法三</button> 304 <button type="button" onclick="method4('tableExcel')">导出Excel方法四</button> 305 <button type="button" onclick="method5('tableExcel')">导出Excel方法四</button> 306 <button type="button" id="btnExport">导出Excel方法五</button> 307 </div> 308 <div id="myDiv"> 309 <table id="tableExcel" width="100%" border="1" cellspacing="0" cellpadding="0"> 310 <tr> 311 <td colspan="5" align="center">html 表格导出道Excel</td> 312 </tr> 313 <tr> 314 <td>列标题1</td> 315 <td>列标题2</td> 316 <td>类标题3</td> 317 <td>列标题4</td> 318 <td>列标题5</td> 319 </tr> 320 <tr> 321 <td>aaa</td> 322 <td>bbb</td> 323 <td>ccc</td> 324 <td>ddd</td> 325 <td>eee</td> 326 </tr> 327 <tr> 328 <td>AAA</td> 329 <td>BBB</td> 330 <td>CCC</td> 331 <td>DDD</td> 332 <td>EEE</td> 333 </tr> 334 <tr> 335 <td>FFF</td> 336 <td>GGG</td> 337 <td>HHH</td> 338 <td>III</td> 339 <td>JJJ</td> 340 </tr> 341 </table> 342 </div> 343 </body>
转至:https://blog.csdn.net/aa122273328/article/details/50388673
有些朋友在使用中遇到了数字型的值被输出成了科学记数法的问题，例如身份证号，导致数字不正确，我们可以给容器增加内联样式来解决这个问题，使用方式如
<td style="mso-number-format:'\@';"> 数值 </td>
