【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是由/SheetJS/js-xlsx下载的源文件修改的

修改主要参考了/xSirrioNx资源

1 var StyleBuilder = function (options) { 2 3var customNumFmtId = 164; 4 5 6var 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}; 37var fmt_table = {}; 38 39for (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 48return { 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', '/markup-compatibility/') 79 .attr('xmlns:x14ac', '/office/spreadsheetml//9/ac') 80 .attr('xmlns', '/spreadsheetml//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 property115 // these format of this object follows the emerging Common Spreadsheet Format116 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 property132 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; //TODO154 },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 theme245$font.append(XmlNode('tint').attr('theme', attributes.color.tint))246 }247 248 } else if (attributes.color.rgb) { // not both rgb and theme249 $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 formats269 }270 }271 272 if (/^[0-9]+$/.exec(numFmt)) {273 return numFmt; // we're matching an integer against some known code274 }275 numFmt = numFmt276 .replace(/&/g, '&amp;')277 .replace(/</g, '&lt;')278 .replace(/>/g, '&gt;')279 .replace(/"/g, '&quot;')280 .replace(/'/g, '&apos;');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 both306 //var $fgColor = createElement('<fgColor/>', null, null, {xmlMode: true}).attr(attributes.fgColor)307 if (attributes.fgColor.rgb) {308 309 if (attributes.fgColor.rgb.length == 6) {310attributes.fgColor.rgb = "FF" + attributes.fgColor.rgb /// add alpha to an RGB as Excel expects aRGB311 }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) { 2if (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}13else {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: 132 };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 { 20C = 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())) {36o = ({ t: 'd', v: parseDate(v), s: oss });37if (!opts.cellDates) o = ({ t: 'n', v: datenum(o.v), s: oss });38o.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++) {46var newc = C + i47if (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}53else {54 ws[encode_cell({ c: newc, r: R })] = o;55}56 }57 }58 else {59 if (RS > 1) {60for (var m = 1; m < RS; m++) {61 var newr = R + m;62 ws[encode_cell({ c: C, r: newr })] = o;63}64 }65 else {66ws[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}

参考：

/SheetJS/js-xlsx

/xSirrioNx/js-xlsx

/p/063badece350

/jtjds/p/8892510.html