SheetJs + xlsx-style 导出附带样式的excel

最近有个关于导出表格后附带边框字体样式的需求,鉴于公司祖传老代码,决定用以下方式实现

  • 通过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 欢迎探讨

《SheetJs + xlsx-style 导出附带样式的excel》有2个想法

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注