【js-xlsx和file-saver插件】前端html的table导出数据到excel的表格合并显示boder
最近在做项目，需要从页面的表格中导出excel，一般导出excel有两种方法：一、习惯上是建模版从后台服务程序中导出；二、根据页面table中导出；综合考虑其中利弊选择二、根据页面table中导出excel，前段有用table的也有用vue的，结佣file-saver和xlsx插件进行导出excel。
没有做封装，直接改的源码
/* generate workbook object from table */ var defaultCellStyle = { font: { name: 'Times New Roman', sz: 16, color: { rgb: "#FF000000" }, bold: false, italic: false, underline: false }, alignment: { vertical: "center", horizontal: "center", indent: 0, wrapText: true }, border: { top: { style: "thin", color: { "auto": 1 } }, right: { style: "thin", color: { "auto": 1 } }, bottom: { style: "thin", color: { "auto": 1 } }, left: { style: "thin", color: { "auto": 1 } } } }; var cell = {defaultCellStyle: defaultCellStyle}; var wb = XLSX.utils.table_to_book(document.querySelector('.el-table__fixed'),cell) /* get binary string as output */ //设置表格的样式 var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary',cellStyles: true, defaultCellStyle: defaultCellStyle, showGridLines: true }); var s2ab=function(s) { let buf = new ArrayBuffer(s.length); let view = new Uint8Array(buf); for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF; return buf; }; saveAs(new Blob([s2ab(wbout)], { type: 'application/octet-stream' }), '报表.xlsx')
页面中需要引入文件
<script type="text/javascript" src="shim.min.js"></script>
<script type="text/javascript" src="jszip.js"></script>
<script type="text/javascript" src="xlsx.full.js"></script>
<script type="text/javascript" src="Blob.js"></script>
<script type="text/javascript" src="FileSaver.js"></script>
此处的xlsx.full.js是由https://github.com/SheetJS/js-xlsx下载的源文件修改的
修改主要参考了https://github.com/xSirrioNx资源
1 var StyleBuilder = function (options) { 2 3 var customNumFmtId = 164; 4 5 6 var table_fmt = { 7 0: 'General', 8 1: '0', 9 2: '0.00', 10 3: '#,##0', 11 4: '#,##0.00', 12 9: '0%', 13 10: '0.00%', 14 11: '0.00E+00', 15 12: '# ?/?', 16 13: '# ??/??', 17 14: 'm/d/yy', 18 15: 'd-mmm-yy', 19 16: 'd-mmm', 20 17: 'mmm-yy', 21 18: 'h:mm AM/PM', 22 19: 'h:mm:ss AM/PM', 23 20: 'h:mm', 24 21: 'h:mm:ss', 25 22: 'm/d/yy h:mm', 26 37: '#,##0 ;(#,##0)', 27 38: '#,##0 ;[Red](#,##0)', 28 39: '#,##0.00;(#,##0.00)', 29 40: '#,##0.00;[Red](#,##0.00)', 30 45: 'mm:ss', 31 46: '[h]:mm:ss', 32 47: 'mmss.0', 33 48: '##0.0E+0', 34 49: '@', 35 56: '"上午/下午 "hh"時"mm"分"ss"秒 "' 36 }; 37 var fmt_table = {}; 38 39 for (var idx in table_fmt) { 40 fmt_table[table_fmt[idx]] = idx; 41 } 42 43 44 // cache style specs to avoid excessive duplication 45 _hashIndex = {}; 46 _listIndex = []; 47 48 return { 49 50 initialize: function (options) { 51 52 this.$fonts = XmlNode('fonts').attr('count', 0).attr("x14ac:knownFonts", "1"); 53 this.$fills = XmlNode('fills').attr('count', 0); 54 this.$borders = XmlNode('borders').attr('count', 0); 55 this.$numFmts = XmlNode('numFmts').attr('count', 0); 56 this.$cellStyleXfs = XmlNode('cellStyleXfs'); 57 this.$xf = XmlNode('xf') 58 .attr('numFmtId', 0) 59 .attr('fontId', 0) 60 .attr('fillId', 0) 61 .attr('borderId', 0); 62 63 this.$cellXfs = XmlNode('cellXfs').attr('count', 0); 64 this.$cellStyles = XmlNode('cellStyles') 65 .append(XmlNode('cellStyle') 66 .attr('name', 'Normal') 67 .attr('xfId', 0) 68 .attr('builtinId', 0) 69 ); 70 this.$dxfs = XmlNode('dxfs').attr('count', "0"); 71 this.$tableStyles = XmlNode('tableStyles') 72 .attr('count', '0') 73 .attr('defaultTableStyle', 'TableStyleMedium9') 74 .attr('defaultPivotStyle', 'PivotStyleMedium4') 75 76 77 this.$styles = XmlNode('styleSheet') 78 .attr('xmlns:mc', 'http://schemas.openxmlformats.org/markup-compatibility/2006') 79 .attr('xmlns:x14ac', 'http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac') 80 .attr('xmlns', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main') 81 .attr('mc:Ignorable', 'x14ac') 82 .prefix('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>') 83 .append(this.$numFmts) 84 .append(this.$fonts) 85 .append(this.$fills) 86 .append(this.$borders) 87 .append(this.$cellStyleXfs.append(this.$xf)) 88 .append(this.$cellXfs) 89 .append(this.$cellStyles) 90 .append(this.$dxfs) 91 .append(this.$tableStyles); 92 93 94 // need to specify styles at index 0 and 1. 95 // the second style MUST be gray125 for some reason 96 97 var defaultStyle = options.defaultCellStyle || {}; 98 if (!defaultStyle.font) defaultStyle.font = { name: 'Calibri', sz: '12' }; 99 if (!defaultStyle.font.name) defaultStyle.font.name = 'Calibri'; 100 if (!defaultStyle.font.sz) defaultStyle.font.sz = 11; 101 if (!defaultStyle.fill) defaultStyle.fill = { patternType: "none", fgColor: {} }; 102 if (!defaultStyle.border) defaultStyle.border = {}; 103 if (!defaultStyle.numFmt) defaultStyle.numFmt = 0; 104 105 this.defaultStyle = defaultStyle; 106 107 var gray125Style = JSON.parse(JSON.stringify(defaultStyle)); 108 gray125Style.fill = { patternType: "gray125", fgColor: {} } 109 110 this.addStyles([defaultStyle, gray125Style]); 111 return this; 112 }, 113 114 // create a style entry and returns an integer index that can be used in the cell .s property 115 // these format of this object follows the emerging Common Spreadsheet Format 116 addStyle: function (attributes) { 117 118 var hashKey = JSON.stringify(attributes); 119 var index = _hashIndex[hashKey]; 120 if (index == undefined) { 121 122 index = this._addXf(attributes); //_listIndex.push(attributes) -1; 123 _hashIndex[hashKey] = index; 124 } 125 else { 126 index = _hashIndex[hashKey]; 127 } 128 return index; 129 }, 130 131 // create style entries and returns array of integer indexes that can be used in cell .s property 132 addStyles: function (styles) { 133 var self = this; 134 return styles.map(function (style) { 135 return self.addStyle(style); 136 }) 137 }, 138 139 _duckTypeStyle: function (attributes) { 140 141 if (typeof attributes == 'object' && (attributes.patternFill || attributes.fgColor)) { 142 return { fill: attributes }; // this must be read via XLSX.parseFile(...) 143 } 144 else if (attributes.font || attributes.numFmt || attributes.border || attributes.fill) { 145 return attributes; 146 } 147 else { 148 return this._getStyleCSS(attributes) 149 } 150 }, 151 152 _getStyleCSS: function (css) { 153 return css; //TODO 154 }, 155 156 // Create an <xf> record for the style as well as corresponding <font>, <fill>, <border>, <numfmts> 157 // Right now this is simple and creates a <font>, <fill>, <border>, <numfmts> for every <xf> 158 // We could perhaps get fancier and avoid duplicating auxiliary entries as Excel presumably intended, but bother. 159 _addXf: function (attributes) { 160 161 162 var fontId = this._addFont(attributes.font); 163 var fillId = this._addFill(attributes.fill); 164 var borderId = this._addBorder(attributes.border); 165 var numFmtId = this._addNumFmt(attributes.numFmt); 166 167 var $xf = XmlNode('xf') 168 .attr("numFmtId", numFmtId) 169 .attr("fontId", fontId) 170 .attr("fillId", fillId) 171 .attr("borderId", borderId) 172 .attr("xfId", "0"); 173 174 if (fontId > 0) { 175 $xf.attr('applyFont', "1"); 176 } 177 if (fillId > 0) { 178 $xf.attr('applyFill', "1"); 179 } 180 if (borderId > 0) { 181 $xf.attr('applyBorder', "1"); 182 } 183 if (numFmtId > 0) { 184 $xf.attr('applyNumberFormat', "1"); 185 } 186 187 if (attributes.alignment) { 188 var $alignment = XmlNode('alignment'); 189 if (attributes.alignment.horizontal) { 190 $alignment.attr('horizontal', attributes.alignment.horizontal); 191 } 192 if (attributes.alignment.vertical) { 193 $alignment.attr('vertical', attributes.alignment.vertical); 194 } 195 if (attributes.alignment.indent) { 196 $alignment.attr('indent', attributes.alignment.indent); 197 } 198 if (attributes.alignment.readingOrder) { 199 $alignment.attr('readingOrder', attributes.alignment.readingOrder); 200 } 201 if (attributes.alignment.wrapText) { 202 $alignment.attr('wrapText', attributes.alignment.wrapText); 203 } 204 if (attributes.alignment.textRotation != undefined) { 205 $alignment.attr('textRotation', attributes.alignment.textRotation); 206 } 207 208 $xf.append($alignment).attr('applyAlignment', 1) 209 210 } 211 this.$cellXfs.append($xf); 212 var count = +this.$cellXfs.children().length; 213 214 this.$cellXfs.attr('count', count); 215 return count - 1; 216 }, 217 218 _addFont: function (attributes) { 219 220 if (!attributes) { 221 return 0; 222 } 223 224 var $font = XmlNode('font') 225 .append(XmlNode('sz').attr('val', attributes.sz || this.defaultStyle.font.sz)) 226 .append(XmlNode('name').attr('val', attributes.name || this.defaultStyle.font.name)) 227 228 if (attributes.bold) $font.append(XmlNode('b')); 229 if (attributes.underline) $font.append(XmlNode('u')); 230 if (attributes.italic) $font.append(XmlNode('i')); 231 if (attributes.strike) $font.append(XmlNode('strike')); 232 if (attributes.outline) $font.append(XmlNode('outline')); 233 if (attributes.shadow) $font.append(XmlNode('shadow')); 234 235 if (attributes.vertAlign) { 236 $font.append(XmlNode('vertAlign').attr('val', attributes.vertAlign)) 237 } 238 239 240 if (attributes.color) { 241 if (attributes.color.theme) { 242 $font.append(XmlNode('color').attr('theme', attributes.color.theme)) 243 244 if (attributes.color.tint) { //tint only if theme 245 $font.append(XmlNode('tint').attr('theme', attributes.color.tint)) 246 } 247 248 } else if (attributes.color.rgb) { // not both rgb and theme 249 $font.append(XmlNode('color').attr('rgb', attributes.color.rgb)) 250 } 251 } 252 253 this.$fonts.append($font); 254 255 var count = this.$fonts.children().length; 256 this.$fonts.attr('count', count); 257 return count - 1; 258 }, 259 260 _addNumFmt: function (numFmt) { 261 if (!numFmt) { 262 return 0; 263 } 264 265 if (typeof numFmt == 'string') { 266 var numFmtIdx = fmt_table[numFmt]; 267 if (numFmtIdx >= 0) { 268 return numFmtIdx; // we found a match against built in formats 269 } 270 } 271 272 if (/^[0-9]+$/.exec(numFmt)) { 273 return numFmt; // we're matching an integer against some known code 274 } 275 numFmt = numFmt 276 .replace(/&/g, '&') 277 .replace(/</g, '<') 278 .replace(/>/g, '>') 279 .replace(/"/g, '"') 280 .replace(/'/g, '''); 281 282 var $numFmt = XmlNode('numFmt') 283 .attr('numFmtId', (++customNumFmtId)) 284 .attr('formatCode', numFmt); 285 286 this.$numFmts.append($numFmt); 287 288 var count = this.$numFmts.children().length; 289 this.$numFmts.attr('count', count); 290 return customNumFmtId; 291 }, 292 293 _addFill: function (attributes) { 294 295 if (!attributes) { 296 return 0; 297 } 298 299 var $patternFill = XmlNode('patternFill') 300 .attr('patternType', attributes.patternType || 'solid'); 301 302 if (attributes.fgColor) { 303 var $fgColor = XmlNode('fgColor'); 304 305 //Excel doesn't like it when we set both rgb and theme+tint, but xlsx.parseFile() sets both 306 //var $fgColor = createElement('<fgColor/>', null, null, {xmlMode: true}).attr(attributes.fgColor) 307 if (attributes.fgColor.rgb) { 308 309 if (attributes.fgColor.rgb.length == 6) { 310 attributes.fgColor.rgb = "FF" + attributes.fgColor.rgb /// add alpha to an RGB as Excel expects aRGB 311 } 312 313 $fgColor.attr('rgb', attributes.fgColor.rgb); 314 $patternFill.append($fgColor); 315 } 316 else if (attributes.fgColor.theme) { 317 $fgColor.attr('theme', attributes.fgColor.theme); 318 if (attributes.fgColor.tint) { 319 $fgColor.attr('tint', attributes.fgColor.tint); 320 } 321 $patternFill.append($fgColor); 322 } 323 324 if (!attributes.bgColor) { 325 attributes.bgColor = { "indexed": "64" } 326 } 327 } 328 329 if (attributes.bgColor) { 330 var $bgColor = XmlNode('bgColor').attr(attributes.bgColor); 331 $patternFill.append($bgColor); 332 } 333 334 var $fill = XmlNode('fill') 335 .append($patternFill); 336 337 this.$fills.append($fill); 338 339 var count = this.$fills.children().length; 340 this.$fills.attr('count', count); 341 return count - 1; 342 }, 343 344 _getSubBorder: function (direction, spec) { 345 346 var $direction = XmlNode(direction); 347 if (spec) { 348 if (spec.style) $direction.attr('style', spec.style); 349 if (spec.color) { 350 var $color = XmlNode('color'); 351 if (spec.color.auto) { 352 $color.attr('auto', spec.color.auto); 353 } 354 else if (spec.color.rgb) { 355 $color.attr('rgb', spec.color.rgb); 356 } 357 else if (spec.color.theme || spec.color.tint) { 358 $color.attr('theme', spec.color.theme || "1"); 359 $color.attr('tint', spec.color.tint || "0"); 360 } 361 $direction.append($color) 362 } 363 } 364 return $direction; 365 }, 366 367 _addBorder: function (attributes) { 368 if (!attributes) { 369 return 0; 370 } 371 372 var self = this; 373 374 var $border = XmlNode('border') 375 .attr("diagonalUp", attributes.diagonalUp) 376 .attr("diagonalDown", attributes.diagonalDown); 377 378 var directions = ["left", "right", "top", "bottom", "diagonal"]; 379 380 directions.forEach(function (direction) { 381 $border.append(self._getSubBorder(direction, attributes[direction])) 382 }); 383 this.$borders.append($border); 384 385 var count = this.$borders.children().length; 386 this.$borders.attr('count', count); 387 return count - 1; 388 }, 389 390 toXml: function () { 391 return this.$styles.toXml(); 392 } 393 }.initialize(options || {}); 394 }
1 function get_cell_style(styles, cell, opts) { 2 if (typeof style_builder != 'undefined') { 3 if (/^\d+$/.exec(cell.s)) { 4 return cell.s 5 } // if its already an integer index, let it be 6 if (cell.s && (cell.s == +cell.s)) { 7 return cell.s 8 } // if its already an integer index, let it be 9 var s = cell.s || {}; 10 if (cell.z) s.numFmt = cell.z; 11 return style_builder.addStyle(s); 12 } 13 else { 14 var z = opts.revssf[cell.z != null ? cell.z : "General"]; 15 var i = 0x3c, len = styles.length; 16 if (z == null && opts.ssf) { 17 for (; i < 0x188; ++i) if (opts.ssf[i] == null) { 18 SSF.load(cell.z, i); 19 opts.ssf[i] = cell.z; 20 opts.revssf[cell.z] = z = i; 21 break; 22 } 23 } 24 for (i = 0; i != len; ++i) if (styles[i].numFmtId === z) return i; 25 styles[len] = { 26 numFmtId: z, 27 fontId: 0, 28 fillId: 0, 29 borderId: 0, 30 xfId: 0, 31 applyNumberFormat: 1 32 }; 33 return len; 34 } 35 }
和我自己的以下的修改
1 function parse_dom_table(table, _opts) { 2 var opts = _opts || {}; 3 var oss = opts.defaultCellStyle||{}; /*单元格样式 */ 4 if (DENSE != null) opts.dense = DENSE; 5 var ws = opts.dense ? ([]) : ({}); 6 var rows = table.getElementsByTagName('tr'); 7 var sheetRows = Math.min(opts.sheetRows || 10000000, rows.length); 8 var range = { s: { r: 0, c: 0 }, e: { r: sheetRows - 1, c: 0 } }; 9 var merges = [], midx = 0; 10 var R = 0, _C = 0, C = 0, RS = 0, CS = 0; 11 for (; R < sheetRows; ++R) { 12 var row = rows[R]; 13 var elts = (row.children); 14 for (_C = C = 0; _C < elts.length; ++_C) { 15 var elt = elts[_C], v = htmldecode(elts[_C].innerHTML); 16 for (midx = 0; midx < merges.length; ++midx) { 17 var m = merges[midx]; 18 if (m.s.c == C && m.s.r <= R && R <= m.e.r) 19 { 20 C = m.e.c + 1; midx = -1; 21 } 22 } 23 /* TODO: figure out how to extract nonstandard mso- style */ 24 CS = +elt.getAttribute("colspan") || 1; 25 if ((RS = +elt.getAttribute("rowspan")) > 0 || CS > 1) 26 merges.push({ s: { r: R, c: C }, e: { r: R + (RS || 1) - 1, c: C + CS - 1 } }); 27 var o = { t: 's', v: v,s:oss}; 28 var _t = elt.getAttribute("t") || ""; 29 if (v != null) { 30 if (v.length == 0) o.t = _t || 's'; 31 else if (opts.raw || v.trim().length == 0 || _t == "s") { } 32 else if (v === 'TRUE') o = { t: 'b', v: true, s: oss }; 33 else if (v === 'FALSE') o = { t: 'b', v: false, s: oss }; 34 else if (!isNaN(fuzzynum(v))) o = { t: 'n', v: fuzzynum(v), s: oss }; 35 else if (!isNaN(fuzzydate(v).getDate())) { 36 o = ({ t: 'd', v: parseDate(v), s: oss }); 37 if (!opts.cellDates) o = ({ t: 'n', v: datenum(o.v), s: oss }); 38 o.z = opts.dateNF || SSF._table[14]; 39 } 40 } 41 if (opts.dense) { if (!ws[R]) ws[R] = []; ws[R][C] = o; } 42 else ws[encode_cell({ c: C, r: R })] = o; 43 /* 合并数据处理开始*/ 44 if (CS > 1) { 45 for (var i = 1; i < CS; i++) { 46 var newc = C + i 47 if (RS > 1) { 48 for (var m = 1; m < RS; m++) { 49 var newr = R + m; 50 ws[encode_cell({ c: newc, r: newr })] = o; 51 } 52 } 53 else { 54 ws[encode_cell({ c: newc, r: R })] = o; 55 } 56 } 57 } 58 else { 59 if (RS > 1) { 60 for (var m = 1; m < RS; m++) { 61 var newr = R + m; 62 ws[encode_cell({ c: C, r: newr })] = o; 63 } 64 } 65 else { 66 ws[encode_cell({ c: C, r: R })] = o; 67 } 68 } 69 /*合并数据处理结束*/ 70 if (range.e.c < C) range.e.c = C; 71 C += CS; 72 73 } 74 } 75 ws['!merges'] = merges; 76 ws['!ref'] = encode_range(range); 77 if (sheetRows < rows.length) ws['!fullref'] = encode_range((range.e.r = rows.length - 1, range)); 78 return ws; 79 }
参考：
https://github.com/SheetJS/js-xlsx
https://github.com/xSirrioNx/js-xlsx
https://www.jianshu.com/p/063badece350
http://www.cnblogs.com/jtjds/p/8892510.html
