js导出Excel
项目中遇到了使用js导出Excel，常规表格（不要求样式、合并单元格）的好做，但是遇到要求携带样式、合并单元格的应该怎么办，我把找到的方法记录了下来：
1、导出常规表格
参考地址：https://blog.csdn.net/hhzzcc_/article/details/80419396，(我使用的是第二种方法)
项目中代码：
1 // 点击\'导出\' 2 This.$box.off(\'click\', \'.exportBtn\').on(\'click\', \'.exportBtn\', function() { 3 //列标题，逗号隔开，每一个逗号就是隔开一个单元格 4 let str = `考核月份,电厂名称,管理考核,短期考核分,超短期考核分,可用功率考核分,上报率考核分,考核总分\n`; 5 //增加\t为了不让表格显示科学计数法或者其他格式 6 for(let i = 0 ; i < This.resultData.length ; i++ ){ 7 str += `${This.resultData[i].PMONTH},`; 8 str += `${This.resultData[i].PLANTID},`; 9 str += `${This.resultData[i].GLMARK},`; 10 str += `${This.resultData[i].KYLSBRATE},`; 11 str += `${This.resultData[i].DQMARK},`; 12 str += `${This.resultData[i].CDQMARK},`; 13 str += `${This.resultData[i].SBZMARK},`; 14 str += `${This.resultData[i].KHMARK},`; 15 str+=\'\n\'; 16 } 17 //encodeURIComponent解决中文乱码 18 let uri = \'data:text/csv;charset=utf-8,\ufeff\' + encodeURIComponent(str); 19 //通过创建a标签实现 20 let link = document.createElement("a"); 21 link.href = uri; 22 //对下载的文件命名 23 link.download = "月考核结果.xlsx"; 24 document.body.appendChild(link); 25 link.click(); 26 document.body.removeChild(link); 27 });
View Code
2、导出合并单元格的（利用插件：jquery.table2excel.js）
参考地址：https://www.jianshu.com/p/980fc3f7c83f
插件地址：https://github.com/rainabba/jquery-table2excel
以防插件地址出问题，jquery.table2excel.js代码如下：
1 /* 2 * jQuery table2excel - v1.1.2 3 * jQuery plugin to export an .xls file in browser from an HTML table 4 * https://github.com/rainabba/jquery-table2excel 5 * 6 * Made by rainabba 7 * Under MIT License 8 */ 9 //table2excel.js 10 (function ( $, window, document, undefined ) { 11 var pluginName = "table2excel", 12 13 defaults = { 14 exclude: ".noExl", 15 name: "Table2Excel", 16 filename: "table2excel", 17 fileext: ".xls", 18 exclude_img: true, 19 exclude_links: true, 20 exclude_inputs: true, 21 preserveColors: false 22 }; 23 24 // The actual plugin constructor 25 function Plugin ( element, options ) { 26 this.element = element; 27 // jQuery has an extend method which merges the contents of two or 28 // more objects, storing the result in the first object. The first object 29 // is generally empty as we don\'t want to alter the default options for 30 // future instances of the plugin 31 // 32 this.settings = $.extend( {}, defaults, options ); 33 this._defaults = defaults; 34 this._name = pluginName; 35 this.init(); 36 } 37 38 Plugin.prototype = { 39 init: function () { 40 var e = this; 41 42 var utf8Heading = "<meta http-equiv=\"content-type\" content=\"application/vnd.ms-excel; charset=UTF-8\">"; 43 e.template = { 44 head: "<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\">" + utf8Heading + "<head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets>", 45 sheet: { 46 head: "<x:ExcelWorksheet><x:Name>", 47 tail: "</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>" 48 }, 49 mid: "</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body>", 50 table: { 51 head: "<table>", 52 tail: "</table>" 53 }, 54 foot: "</body></html>" 55 }; 56 57 e.tableRows = []; 58 59 // Styling variables 60 var additionalStyles = ""; 61 var compStyle = null; 62 63 // get contents of table except for exclude 64 $(e.element).each( function(i,o) { 65 var tempRows = ""; 66 $(o).find("tr").not(e.settings.exclude).each(function (i,p) { 67 68 // Reset for this row 69 additionalStyles = ""; 70 71 // Preserve background and text colors on the row 72 if(e.settings.preserveColors){ 73 compStyle = getComputedStyle(p); 74 additionalStyles += (compStyle && compStyle.backgroundColor ? "background-color: " + compStyle.backgroundColor + ";" : ""); 75 additionalStyles += (compStyle && compStyle.color ? "color: " + compStyle.color + ";" : ""); 76 } 77 78 // Create HTML for Row 79 tempRows += "<tr style=\'" + additionalStyles + "\'>"; 80 81 // Loop through each TH and TD 82 $(p).find("td,th").not(e.settings.exclude).each(function (i,q) { // p did not exist, I corrected 83 84 // Reset for this column 85 additionalStyles = ""; 86 87 // Preserve background and text colors on the row 88 if(e.settings.preserveColors){ 89 compStyle = getComputedStyle(q); 90 additionalStyles += (compStyle && compStyle.backgroundColor ? "background-color: " + compStyle.backgroundColor + ";" : ""); 91 additionalStyles += (compStyle && compStyle.color ? "color: " + compStyle.color + ";" : ""); 92 } 93 94 var rc = { 95 rows: $(this).attr("rowspan"), 96 cols: $(this).attr("colspan"), 97 flag: $(q).find(e.settings.exclude) 98 }; 99 100 if( rc.flag.length > 0 ) { 101 tempRows += "<td> </td>"; // exclude it!! 102 } else { 103 tempRows += "<td"; 104 if( rc.rows > 0) { 105 tempRows += " rowspan=\'" + rc.rows + "\' "; 106 } 107 if( rc.cols > 0) { 108 tempRows += " colspan=\'" + rc.cols + "\' "; 109 } 110 if(additionalStyles){ 111 tempRows += " style=\'" + additionalStyles + "\'"; 112 } 113 tempRows += ">" + $(q).html() + "</td>"; 114 } 115 }); 116 117 tempRows += "</tr>"; 118 119 }); 120 // exclude img tags 121 if(e.settings.exclude_img) { 122 tempRows = exclude_img(tempRows); 123 } 124 125 // exclude link tags 126 if(e.settings.exclude_links) { 127 tempRows = exclude_links(tempRows); 128 } 129 130 // exclude input tags 131 if(e.settings.exclude_inputs) { 132 tempRows = exclude_inputs(tempRows); 133 } 134 e.tableRows.push(tempRows); 135 }); 136 137 e.tableToExcel(e.tableRows, e.settings.name, e.settings.sheetName); 138 }, 139 140 tableToExcel: function (table, name, sheetName) { 141 var e = this, fullTemplate="", i, link, a; 142 143 e.format = function (s, c) { 144 return s.replace(/{(\w+)}/g, function (m, p) { 145 return c[p]; 146 }); 147 }; 148 149 sheetName = typeof sheetName === "undefined" ? "Sheet" : sheetName; 150 151 e.ctx = { 152 worksheet: name || "Worksheet", 153 table: table, 154 sheetName: sheetName 155 }; 156 157 fullTemplate= e.template.head; 158 159 if ( $.isArray(table) ) { 160 Object.keys(table).forEach(function(i){ 161 //fullTemplate += e.template.sheet.head + "{worksheet" + i + "}" + e.template.sheet.tail; 162 fullTemplate += e.template.sheet.head + sheetName + i + e.template.sheet.tail; 163 }); 164 } 165 166 fullTemplate += e.template.mid; 167 168 if ( $.isArray(table) ) { 169 Object.keys(table).forEach(function(i){ 170 fullTemplate += e.template.table.head + "{table" + i + "}" + e.template.table.tail; 171 }); 172 } 173 174 fullTemplate += e.template.foot; 175 176 for (i in table) { 177 e.ctx["table" + i] = table[i]; 178 } 179 delete e.ctx.table; 180 181 var isIE = navigator.appVersion.indexOf("MSIE 10") !== -1 || (navigator.userAgent.indexOf("Trident") !== -1 && navigator.userAgent.indexOf("rv:11") !== -1); // this works with IE10 and IE11 both :) 182 //if (typeof msie !== "undefined" && msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) // this works ONLY with IE 11!!! 183 if (isIE) { 184 if (typeof Blob !== "undefined") { 185 //use blobs if we can 186 fullTemplate = e.format(fullTemplate, e.ctx); // with this, works with IE 187 fullTemplate = [fullTemplate]; 188 //convert to array 189 var blob1 = new Blob(fullTemplate, { type: "text/html" }); 190 window.navigator.msSaveBlob(blob1, getFileName(e.settings) ); 191 } else { 192 //otherwise use the iframe and save 193 //requires a blank iframe on page called txtArea1 194 txtArea1.document.open("text/html", "replace"); 195 txtArea1.document.write(e.format(fullTemplate, e.ctx)); 196 txtArea1.document.close(); 197 txtArea1.focus(); 198 sa = txtArea1.document.execCommand("SaveAs", true, getFileName(e.settings) ); 199 } 200 201 } else { 202 var blob = new Blob([e.format(fullTemplate, e.ctx)], {type: "application/vnd.ms-excel"}); 203 window.URL = window.URL || window.webkitURL; 204 link = window.URL.createObjectURL(blob); 205 a = document.createElement("a"); 206 a.download = getFileName(e.settings); 207 a.href = link; 208 209 document.body.appendChild(a); 210 211 a.click(); 212 213 document.body.removeChild(a); 214 } 215 216 return true; 217 } 218 }; 219 220 function getFileName(settings) { 221 return ( settings.filename ? settings.filename : "table2excel" ); 222 } 223 224 // Removes all img tags 225 function exclude_img(string) { 226 var _patt = /(\s+alt\s*=\s*"([^"]*)"|\s+alt\s*=\s*\'([^\']*)\')/i; 227 return string.replace(/<img[^>]*>/gi, function myFunction(x){ 228 var res = _patt.exec(x); 229 if (res !== null && res.length >=2) { 230 return res[2]; 231 } else { 232 return ""; 233 } 234 }); 235 } 236 237 // Removes all link tags 238 function exclude_links(string) { 239 return string.replace(/<a[^>]*>|<\/a>/gi, ""); 240 } 241 242 // Removes input params 243 function exclude_inputs(string) { 244 var _patt = /(\s+value\s*=\s*"([^"]*)"|\s+value\s*=\s*\'([^\']*)\')/i; 245 return string.replace(/<input[^>]*>|<\/input>/gi, function myFunction(x){ 246 var res = _patt.exec(x); 247 if (res !== null && res.length >=2) { 248 return res[2]; 249 } else { 250 return ""; 251 } 252 }); 253 } 254 255 $.fn[ pluginName ] = function ( options ) { 256 var e = this; 257 e.each(function() { 258 if ( !$.data( e, "plugin_" + pluginName ) ) { 259 $.data( e, "plugin_" + pluginName, new Plugin( this, options ) ); 260 } 261 }); 262 263 // chain jQuery functions 264 return e; 265 }; 266 267 })( jQuery, window, document );
View Code
项目中代码：
1 // 点击\'导出\'，通过使用插件：jquery.table2excel.js 2 This.$box.off(\'click\', \'.exportBtn\').on(\'click\', \'.exportBtn\', function() { 3 $(\'#newsTable\').table2excel({ 4 exclude: ".noExl", 5 filename: "结算汇总情况" + new Date().toISOString().replace(/[\-\:\.]/g, "") + ".xls", 6 }); 7 });
View Code
版权声明：本文为carriezhao原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。