最近在做项目，需要从页面的表格中导出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资源
var StyleBuilder = function (options) {
var customNumFmtId = 164;
var table_fmt = {
0: 'General',
1: '0',
2: '0.00',
3: '#,##0',
4: '#,##0.00',
9: '0%',
10: '0.00%',
11: '0.00E+00',
12: '# ?/?',
13: '# ??/??',
14: 'm/d/yy',
15: 'd-mmm-yy',
16: 'd-mmm',
17: 'mmm-yy',
18: 'h:mm AM/PM',
19: 'h:mm:ss AM/PM',
20: 'h:mm',
21: 'h:mm:ss',
22: 'm/d/yy h:mm',
37: '#,##0 ;(#,##0)',
38: '#,##0 ;[Red](#,##0)',
39: '#,##0.00;(#,##0.00)',
40: '#,##0.00;[Red](#,##0.00)',
45: 'mm:ss',
46: '[h]:mm:ss',
47: 'mmss.0',
48: '##0.0E+0',
49: '@',
56: '"上午/下午 "hh"時"mm"分"ss"秒 "'
};
var fmt_table = {};
for (var idx in table_fmt) {
fmt_table[table_fmt[idx]] = idx;
}
// cache style specs to avoid excessive duplication
_hashIndex = {};
_listIndex = [];
return {
initialize: function (options) {
this.$fonts = XmlNode('fonts').attr('count', 0).attr("x14ac:knownFonts", "1");
this.$fills = XmlNode('fills').attr('count', 0);
this.$borders = XmlNode('borders').attr('count', 0);
this.$numFmts = XmlNode('numFmts').attr('count', 0);
this.$cellStyleXfs = XmlNode('cellStyleXfs');
this.$xf = XmlNode('xf')
.attr('numFmtId', 0)
.attr('fontId', 0)
.attr('fillId', 0)
.attr('borderId', 0);
this.$cellXfs = XmlNode('cellXfs').attr('count', 0);
this.$cellStyles = XmlNode('cellStyles')
.append(XmlNode('cellStyle')
.attr('name', 'Normal')
.attr('xfId', 0)
.attr('builtinId', 0)
);
this.$dxfs = XmlNode('dxfs').attr('count', "0");
this.$tableStyles = XmlNode('tableStyles')
.attr('count', '0')
.attr('defaultTableStyle', 'TableStyleMedium9')
.attr('defaultPivotStyle', 'PivotStyleMedium4')
this.$styles = XmlNode('styleSheet')
.attr('xmlns:mc', 'http://schemas.openxmlformats.org/markup-compatibility/2006')
.attr('xmlns:x14ac', 'http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac')
.attr('xmlns', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main')
.attr('mc:Ignorable', 'x14ac')
.prefix('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>')
.append(this.$numFmts)
.append(this.$fonts)
.append(this.$fills)
.append(this.$borders)
.append(this.$cellStyleXfs.append(this.$xf))
.append(this.$cellXfs)
.append(this.$cellStyles)
.append(this.$dxfs)
.append(this.$tableStyles);
// need to specify styles at index 0 and 1.
// the second style MUST be gray125 for some reason
var defaultStyle = options.defaultCellStyle || {};
if (!defaultStyle.font) defaultStyle.font = { name: 'Calibri', sz: '12' };
if (!defaultStyle.font.name) defaultStyle.font.name = 'Calibri';
if (!defaultStyle.font.sz) defaultStyle.font.sz = 11;
if (!defaultStyle.fill) defaultStyle.fill = { patternType: "none", fgColor: {} };
if (!defaultStyle.border) defaultStyle.border = {};
if (!defaultStyle.numFmt) defaultStyle.numFmt = 0;
this.defaultStyle = defaultStyle;
var gray125Style = JSON.parse(JSON.stringify(defaultStyle));
gray125Style.fill = { patternType: "gray125", fgColor: {} }
this.addStyles([defaultStyle, gray125Style]);
return this;
},
// create a style entry and returns an integer index that can be used in the cell .s property
// these format of this object follows the emerging Common Spreadsheet Format
addStyle: function (attributes) {
var hashKey = JSON.stringify(attributes);
var index = _hashIndex[hashKey];
if (index == undefined) {
index = this._addXf(attributes); //_listIndex.push(attributes) -1;
_hashIndex[hashKey] = index;
}
else {
index = _hashIndex[hashKey];
}
return index;
},
// create style entries and returns array of integer indexes that can be used in cell .s property
addStyles: function (styles) {
var self = this;
return styles.map(function (style) {
return self.addStyle(style);
})
},
_duckTypeStyle: function (attributes) {
if (typeof attributes == 'object' && (attributes.patternFill || attributes.fgColor)) {
return { fill: attributes }; // this must be read via XLSX.parseFile(...)
}
else if (attributes.font || attributes.numFmt || attributes.border || attributes.fill) {
return attributes;
}
else {
return this._getStyleCSS(attributes)
}
},
_getStyleCSS: function (css) {
return css; //TODO
},
// Create an <xf> record for the style as well as corresponding <font>, <fill>, <border>, <numfmts>
// Right now this is simple and creates a <font>, <fill>, <border>, <numfmts> for every <xf>
// We could perhaps get fancier and avoid duplicating auxiliary entries as Excel presumably intended, but bother.
_addXf: function (attributes) {
var fontId = this._addFont(attributes.font);
var fillId = this._addFill(attributes.fill);
var borderId = this._addBorder(attributes.border);
var numFmtId = this._addNumFmt(attributes.numFmt);
var $xf = XmlNode('xf')
.attr("numFmtId", numFmtId)
.attr("fontId", fontId)
.attr("fillId", fillId)
.attr("borderId", borderId)
.attr("xfId", "0");
if (fontId > 0) {
$xf.attr('applyFont', "1");
}
if (fillId > 0) {
$xf.attr('applyFill', "1");
}
if (borderId > 0) {
$xf.attr('applyBorder', "1");
}
if (numFmtId > 0) {
$xf.attr('applyNumberFormat', "1");
}
if (attributes.alignment) {
var $alignment = XmlNode('alignment');
if (attributes.alignment.horizontal) {
$alignment.attr('horizontal', attributes.alignment.horizontal);
}
if (attributes.alignment.vertical) {
$alignment.attr('vertical', attributes.alignment.vertical);
}
if (attributes.alignment.indent) {
$alignment.attr('indent', attributes.alignment.indent);
}
if (attributes.alignment.readingOrder) {
$alignment.attr('readingOrder', attributes.alignment.readingOrder);
}
if (attributes.alignment.wrapText) {
$alignment.attr('wrapText', attributes.alignment.wrapText);
}
if (attributes.alignment.textRotation != undefined) {
$alignment.attr('textRotation', attributes.alignment.textRotation);
}
$xf.append($alignment).attr('applyAlignment', 1)
}
this.$cellXfs.append($xf);
var count = +this.$cellXfs.children().length;
this.$cellXfs.attr('count', count);
return count - 1;
},
_addFont: function (attributes) {
if (!attributes) {
return 0;
}
var $font = XmlNode('font')
.append(XmlNode('sz').attr('val', attributes.sz || this.defaultStyle.font.sz))
.append(XmlNode('name').attr('val', attributes.name || this.defaultStyle.font.name))
if (attributes.bold) $font.append(XmlNode('b'));
if (attributes.underline) $font.append(XmlNode('u'));
if (attributes.italic) $font.append(XmlNode('i'));
if (attributes.strike) $font.append(XmlNode('strike'));
if (attributes.outline) $font.append(XmlNode('outline'));
if (attributes.shadow) $font.append(XmlNode('shadow'));
if (attributes.vertAlign) {
$font.append(XmlNode('vertAlign').attr('val', attributes.vertAlign))
}
if (attributes.color) {
if (attributes.color.theme) {
$font.append(XmlNode('color').attr('theme', attributes.color.theme))
if (attributes.color.tint) { //tint only if theme
$font.append(XmlNode('tint').attr('theme', attributes.color.tint))
}
} else if (attributes.color.rgb) { // not both rgb and theme
$font.append(XmlNode('color').attr('rgb', attributes.color.rgb))
}
}
this.$fonts.append($font);
var count = this.$fonts.children().length;
this.$fonts.attr('count', count);
return count - 1;
},
_addNumFmt: function (numFmt) {
if (!numFmt) {
return 0;
}
if (typeof numFmt == 'string') {
var numFmtIdx = fmt_table[numFmt];
if (numFmtIdx >= 0) {
return numFmtIdx; // we found a match against built in formats
}
}
if (/^[0-9]+$/.exec(numFmt)) {
return numFmt; // we're matching an integer against some known code
}
numFmt = numFmt
.replace(/&/g, '&')
.replace(/</g, '<')
.replace(/>/g, '>')
.replace(/"/g, '"')
.replace(/'/g, ''');
var $numFmt = XmlNode('numFmt')
.attr('numFmtId', (++customNumFmtId))
.attr('formatCode', numFmt);
this.$numFmts.append($numFmt);
var count = this.$numFmts.children().length;
this.$numFmts.attr('count', count);
return customNumFmtId;
},
_addFill: function (attributes) {
if (!attributes) {
return 0;
}
var $patternFill = XmlNode('patternFill')
.attr('patternType', attributes.patternType || 'solid');
if (attributes.fgColor) {
var $fgColor = XmlNode('fgColor');
//Excel doesn't like it when we set both rgb and theme+tint, but xlsx.parseFile() sets both
//var $fgColor = createElement('<fgColor/>', null, null, {xmlMode: true}).attr(attributes.fgColor)
if (attributes.fgColor.rgb) {
if (attributes.fgColor.rgb.length == 6) {
attributes.fgColor.rgb = "FF" + attributes.fgColor.rgb /// add alpha to an RGB as Excel expects aRGB
}
$fgColor.attr('rgb', attributes.fgColor.rgb);
$patternFill.append($fgColor);
}
else if (attributes.fgColor.theme) {
$fgColor.attr('theme', attributes.fgColor.theme);
if (attributes.fgColor.tint) {
$fgColor.attr('tint', attributes.fgColor.tint);
}
$patternFill.append($fgColor);
}
if (!attributes.bgColor) {
attributes.bgColor = { "indexed": "64" }
}
}
if (attributes.bgColor) {
var $bgColor = XmlNode('bgColor').attr(attributes.bgColor);
$patternFill.append($bgColor);
}
var $fill = XmlNode('fill')
.append($patternFill);
this.$fills.append($fill);
var count = this.$fills.children().length;
this.$fills.attr('count', count);
return count - 1;
},
_getSubBorder: function (direction, spec) {
var $direction = XmlNode(direction);
if (spec) {
if (spec.style) $direction.attr('style', spec.style);
if (spec.color) {
var $color = XmlNode('color');
if (spec.color.auto) {
$color.attr('auto', spec.color.auto);
}
else if (spec.color.rgb) {
$color.attr('rgb', spec.color.rgb);
}
else if (spec.color.theme || spec.color.tint) {
$color.attr('theme', spec.color.theme || "1");
$color.attr('tint', spec.color.tint || "0");
}
$direction.append($color)
}
}
return $direction;
},
_addBorder: function (attributes) {
if (!attributes) {
return 0;
}
var self = this;
var $border = XmlNode('border')
.attr("diagonalUp", attributes.diagonalUp)
.attr("diagonalDown", attributes.diagonalDown);
var directions = ["left", "right", "top", "bottom", "diagonal"];
directions.forEach(function (direction) {
$border.append(self._getSubBorder(direction, attributes[direction]))
});
this.$borders.append($border);
var count = this.$borders.children().length;
this.$borders.attr('count', count);
return count - 1;
},
toXml: function () {
return this.$styles.toXml();
}
}.initialize(options || {});
}
function get_cell_style(styles, cell, opts) {
if (typeof style_builder != 'undefined') {
if (/^\d+$/.exec(cell.s)) {
return cell.s
} // if its already an integer index, let it be
if (cell.s && (cell.s == +cell.s)) {
return cell.s
} // if its already an integer index, let it be
var s = cell.s || {};
if (cell.z) s.numFmt = cell.z;
return style_builder.addStyle(s);
}
else {
var z = opts.revssf[cell.z != null ? cell.z : "General"];
var i = 0x3c, len = styles.length;
if (z == null && opts.ssf) {
for (; i < 0x188; ++i) if (opts.ssf[i] == null) {
SSF.load(cell.z, i);
opts.ssf[i] = cell.z;
opts.revssf[cell.z] = z = i;
break;
}
}
for (i = 0; i != len; ++i) if (styles[i].numFmtId === z) return i;
styles[len] = {
numFmtId: z,
fontId: 0,
fillId: 0,
borderId: 0,
xfId: 0,
applyNumberFormat: 1
};
return len;
}
}
和我自己的以下的修改
function parse_dom_table(table, _opts) {
var opts = _opts || {};
var oss = opts.defaultCellStyle||{}; /*单元格样式 */
if (DENSE != null) opts.dense = DENSE;
var ws = opts.dense ? ([]) : ({});
var rows = table.getElementsByTagName('tr');
var sheetRows = Math.min(opts.sheetRows || 10000000, rows.length);
var range = { s: { r: 0, c: 0 }, e: { r: sheetRows - 1, c: 0 } };
var merges = [], midx = 0;
var R = 0, _C = 0, C = 0, RS = 0, CS = 0;
for (; R < sheetRows; ++R) {
var row = rows[R];
var elts = (row.children);
for (_C = C = 0; _C < elts.length; ++_C) {
var elt = elts[_C], v = htmldecode(elts[_C].innerHTML);
for (midx = 0; midx < merges.length; ++midx) {
var m = merges[midx];
if (m.s.c == C && m.s.r <= R && R <= m.e.r)
{
C = m.e.c + 1; midx = -1;
}
}
/* TODO: figure out how to extract nonstandard mso- style */
CS = +elt.getAttribute("colspan") || 1;
if ((RS = +elt.getAttribute("rowspan")) > 0 || CS > 1)
merges.push({ s: { r: R, c: C }, e: { r: R + (RS || 1) - 1, c: C + CS - 1 } });
var o = { t: 's', v: v,s:oss};
var _t = elt.getAttribute("t") || "";
if (v != null) {
if (v.length == 0) o.t = _t || 's';
else if (opts.raw || v.trim().length == 0 || _t == "s") { }
else if (v === 'TRUE') o = { t: 'b', v: true, s: oss };
else if (v === 'FALSE') o = { t: 'b', v: false, s: oss };
else if (!isNaN(fuzzynum(v))) o = { t: 'n', v: fuzzynum(v), s: oss };
else if (!isNaN(fuzzydate(v).getDate())) {
o = ({ t: 'd', v: parseDate(v), s: oss });
if (!opts.cellDates) o = ({ t: 'n', v: datenum(o.v), s: oss });
o.z = opts.dateNF || SSF._table[14];
}
}
if (opts.dense) { if (!ws[R]) ws[R] = []; ws[R][C] = o; }
else ws[encode_cell({ c: C, r: R })] = o;
/* 合并数据处理开始*/
if (CS > 1) {
for (var i = 1; i < CS; i++) {
var newc = C + i
if (RS > 1) {
for (var m = 1; m < RS; m++) {
var newr = R + m;
ws[encode_cell({ c: newc, r: newr })] = o;
}
}
else {
ws[encode_cell({ c: newc, r: R })] = o;
}
}
}
else {
if (RS > 1) {
for (var m = 1; m < RS; m++) {
var newr = R + m;
ws[encode_cell({ c: C, r: newr })] = o;
}
}
else {
ws[encode_cell({ c: C, r: R })] = o;
}
}
/*合并数据处理结束*/
if (range.e.c < C) range.e.c = C;
C += CS;
}
}
ws['!merges'] = merges;
ws['!ref'] = encode_range(range);
if (sheetRows < rows.length) ws['!fullref'] = encode_range((range.e.r = rows.length - 1, range));
return ws;
}
参考：
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