最近有个关于导出表格后附带边框字体样式的需求，鉴于公司祖传老代码，决定用以下方式实现
- 通过SheetJs的table_to_book转化table元素为workbook
- 经过xlsx-style修改样式，导出。
源码https://github.com/cqHyp/xlsx-styleDemo
查看demohttps://cqhpoldi.com/exportExcelDemo/index.html
<!-- https://cdn.jsdelivr.net/npm/xlsx-style@0.8.13/dist/jszip.js -->
<script src="dist/jszip.js"></script>
<!-- https://cdn.jsdelivr.net/npm/xlsx-style@0.8.13/dist/cpexcel.js -->
<script src="dist/cpexcel.js"></script>
<!-- https://cdn.jsdelivr.net/npm/xlsx-style@0.8.13/dist/ods.js -->
<script src="dist/oods.js"></script>
<!-- https://cdn.jsdelivr.net/npm/file-saver@2.0.2/dist/FileSaver.js -->
<script src="dist/FileSaver.min.js"></script>
<!-- https://cdn.jsdelivr.net/npm/xlsx-style@0.8.13/dist/xlsx.js -->
<script src="dist/xlsx.js"></script>
<!-- https://cdn.jsdelivr.net/npm/xlsx@0.16.6/dist/xlsx.js -->
<script src="dist/sheetJS-xlsx.js"></script>Html代码如下：
<table id="customTable">
<thead>
。。。
</thead>
<tfoot>
。。。
</tfoot>
</table>Js代码：
let elt = document.getElementById("customTable");
let wb = XLSX.utils.table_to_book(elt, { sheet: "Sheet JS", raw: true });
let range = XLSX.utils.decode_range(wb.Sheets['Sheet JS']['!ref']);
// 定义border样式
let borderStyle = {
top: {
style: "thin",
color: { rgb: "000000" }
},
bottom: {
style: "thin",
color: { rgb: "000000" }
},
left: {
style: "thin",
color: { rgb: "000000" }
},
right: {
style: "thin",
color: { rgb: "000000" }
}
};循环定义每个单元格样式
for (let C = range.s.c; C <= range.e.c; ++C) {
for (let R = range.s.r; R <= range.e.r; ++R) {
let cell = { c: C, r: R };
let cell_ref = XLSX.utils.encode_cell(cell);
if (R == 4 || wb.Sheets['Sheet JS'][cell_ref].v == "最终结论") {
wb.Sheets['Sheet JS'][cell_ref].s = {
fill: {
fgColor: {
rgb: "eeeeee"
}
},
alignment: {
horizontal: "center"
},
font: {
name: "黑体",
sz: "15",
bold: true
},
border: borderStyle,
};
} else if (R == 1) {
wb.Sheets['Sheet JS'][cell_ref].s = {
alignment: {
horizontal: "center",
vertical: "center"
},
font: {
name: "黑体",
sz: "15",
bold: true
},
border: borderStyle,
};
} else {
if (wb.Sheets['Sheet JS'][cell_ref]) {
wb.Sheets['Sheet JS'][cell_ref].s = {
font: {
name: "黑体",
sz: "12"
},
border: borderStyle,
};
}
}
}
}通过xlsx-style和fileSaver导出excel
var wopts = { bookType: 'xlsx', bookSST: false, type: 'binary' };
var wbout = XLSXX.write(wb, wopts);
saveAs(new Blob([s2ab(wbout)], { type: "" }), "aa.xlsx");
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}如果单元格有合并，xlsx-style是不会兼容被合并单元格样式的，所以需要把被合并单元格遍历后也加上默认样式。
wb.Sheets['Sheet JS']["!merges"].forEach(item => {
if (item.e.r == item.s.r && item.e.c != item.s.c) {
// 列合并
let R = item.s.r;
let countLength = item.e.c - item.s.c;
for (let i = item.s.c; i <= item.e.c; i++) {
let cell = { c: i, r: R };
let cell_ref = XLSX.utils.encode_cell(cell);
if (!wb.Sheets['Sheet JS'][cell_ref]) {
wb.Sheets['Sheet JS'][cell_ref] = { t: "s", v: "" };
}
}
} else if (item.e.c == item.s.c && item.e.r != item.s.r) {
// 行合并
let C = item.s.c;
let countLength = item.e.r - item.s.r;
for (let i = item.s.r; i <= item.e.r; i++) {
let cell = { c: C, r: i };
let cell_ref = XLSX.utils.encode_cell(cell);
if (!wb.Sheets['Sheet JS'][cell_ref]) {
wb.Sheets['Sheet JS'][cell_ref] = { t: "s", v: "" };
}
}
}
})如果需要设置行高，需要修改源代码 write_ws_xml_data 方法，代码替换为
var DEF_PPI = 96, PPI = DEF_PPI;
function px2pt(px) { return px * 96 / PPI; }
function pt2px(pt) { return pt * PPI / 96; }
function write_ws_xml_data(ws, opts, idx, wb) {
var o = [], r = [], range = safe_decode_range(ws['!ref']), cell="", ref, rr = "", cols = [], R=0, C=0, rows = ws['!rows'];
var dense = Array.isArray(ws);
var params = ({r:rr}), row, height = -1;
for(C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C);
for(R = range.s.r; R <= range.e.r; ++R) {
r = [];
rr = encode_row(R);
for(C = range.s.c; C <= range.e.c; ++C) {
ref = cols[C] + rr;
var _cell = dense ? (ws[R]||[])[C]: ws[ref];
if(_cell === undefined) continue;
if((cell = write_ws_xml_cell(_cell, ref, ws, opts, idx, wb)) != null) r.push(cell);
}
if(r.length > 0 || (rows && rows[R])) {
params = ({r:rr});
if(rows && rows[R]) {
row = rows[R];
if(row.hidden) params.hidden = 1;
height = -1;
if (row.hpx) height = px2pt(row.hpx);
else if (row.hpt) height = row.hpt;
if (height > -1) { params.ht = height; params.customHeight = 1; }
if (row.level) { params.outlineLevel = row.level; }
}
o[o.length] = (writextag('row', r.join(""), params));
}
}
if(rows) for(; R < rows.length; ++R) {
if(rows && rows[R]) {
params = ({r:R+1});
row = rows[R];
if(row.hidden) params.hidden = 1;
height = -1;
if (row.hpx) height = px2pt(row.hpx);
else if (row.hpt) height = row.hpt;
if (height > -1) { params.ht = height; params.customHeight = 1; }
if (row.level) { params.outlineLevel = row.level; }
o[o.length] = (writextag('row', "", params));
}
}
return o.join("");
}在导出时设置!rows的hpx属性
wb.Sheets['Sheet JS']['!rows'] = [{ hpx: 150 }, { hpx: 50 }, { hpx: 50 }];qq+752905517 欢迎探讨
有没有源码啊
https://github.com/cqHyp/xlsx-styleDemo
麻烦请教下，宽度怎么设置，自己设置了无效： wb.Sheets[‘Sheet JS’][‘!rows’] = [{wch: 4}, {wch: 8}];
wb.Sheets[‘Sheet JS’][‘!cols’] = [{ wpx: 150 },…..]
只导入xlsx.js不够吗
需要搭配xlsx-style.js