【js-xlsx和file-saver插件】前端html的table导出数据到excel的表格合并显示boder

时间：2022-11-20 11:01:14

最近在做项目，需要从页面的表格中导出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, '&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 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

 

标签：

相关文章