纯前端利用 js-xlsx 之单元格样式(4)

xlsx-style 说明文档

0.单元格样式:

样式属性 子属性 取值
fill patternType "solid" or "none"
fgColor COLOR_SPEC
bgColor COLOR_SPEC
font name "Calibri" // default
sz "11" // font size in points
color COLOR_SPEC
bold true or false
underline true or false
italic true or false
strike true or false
outline true or false
shadow true or false
vertAlign true or false
numFmt "0" // integer index to built in formats, see StyleBuilder.SSF property
"0.00%" // string matching a built-in format, see StyleBuilder.SSF
"0.0%" // string specifying a custom format
"0.00%;\\(0.00%\\);\\-;@" // string specifying a custom format, escaping special characters
"m/dd/yy" // string a date format using Excel's format notation
alignment vertical "bottom" or "center" or "top"
horizontal "bottom" or "center" or "top"
wrapText true or false
readingOrder 2 // for right-to-left
textRotation Number from 0 to 180 or 255 (default is 0)
90 is rotated up 90 degrees
45 is rotated up 45 degrees
135 is rotated down 45 degrees
180 is rotated down 180 degrees
255 is special, aligned vertically
border top { style: BORDER_STYLE, color: COLOR_SPEC }
bottom { style: BORDER_STYLE, color: COLOR_SPEC }
left { style: BORDER_STYLE, color: COLOR_SPEC }
right { style: BORDER_STYLE, color: COLOR_SPEC }
diagonal { style: BORDER_STYLE, color: COLOR_SPEC }
diagonalUp true or false
diagonalDown true or false

1.下载xlsx-style

npm install xlsx-style

xlsx-style核心模块 在 你安装路径\node_modules\xlsx-style\dist

2.示例代码

复制出 文件xlsx.full.min.js

编写示例代码:


<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title>示例</title>
</head>

<body>
    <script src="./xlsx.full.min.js"></script>
    <script>
        function saveAs(obj, fileName) {
            var tmpa = document.createElement("a");
            tmpa.download = fileName || "下载";
            tmpa.href = URL.createObjectURL(obj);
            tmpa.click();
            setTimeout(function () {
                URL.revokeObjectURL(obj);
            }, 100);
        }
        var jsono = [{
            "id": 1, "合并的列头1": "数据11", "合并的列头2": "数据12", "合并的列头3": "数据13", "合并的列头4": "数据14",
        }, {
            "id": 2, "合并的列头1": "数据21", "合并的列头2": "数据22", "合并的列头3": "数据23", "合并的列头4": "数据24",
        }];
        const wopts = { bookType: 'xlsx', bookSST: true, type: 'binary', cellStyles: true };
        function downloadExl(json, type) {
            var tmpdata = json[0];
            json.unshift({});
            var keyMap = []; //获取keys
            for (var k in tmpdata) {
                keyMap.push(k);
                json[0][k] = k;
            }
            var tmpdata = [];//用来保存转换好的json 
            json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
                v: v[k],
                position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
            }))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata[v.position] = {
                v: v.v
            });
            var outputPos = Object.keys(tmpdata); //设置区域,比如表格从A1到D10
            tmpdata["B1"].s = { font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } }, fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFFF00" } } };//<====设置xlsx单元格样式
            tmpdata["!merges"] = [{
                s: { c: 1, r: 0 },
                e: { c: 4, r: 0 }
            }];//<====合并单元格 
            var tmpWB = {
                SheetNames: ['mySheet'], //保存的表标题
                Sheets: {
                    'mySheet': Object.assign({},
                        tmpdata, //内容
                        {
                            '!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] //设置填充区域
                        })
                }
            };
            tmpDown = new Blob([s2ab(XLSX.write(tmpWB,
                { bookType: (type == undefined ? 'xlsx' : type), bookSST: false, type: 'binary' }//这里的数据是用来定义导出的格式类型
            ))], {
                    type: ""
                });
            saveAs(tmpDown, "这里是下载的文件名" + '.' + (wopts.bookType == "biff2" ? "xls" : wopts.bookType));
        }
        function getCharCol(n) {
            let temCol = '',
                s = '',
                m = 0
            while (n > 0) {
                m = n % 26 + 1
                s = String.fromCharCode(m + 64) + s
                n = (n - m) / 26
            }
            return s
        }
        function s2ab(s) {
            if (typeof ArrayBuffer !== 'undefined') {
                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;
            } else {
                var buf = new Array(s.length);
                for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF;
                return buf;
            }
        }
    </script>
    <button onclick="downloadExl(jsono)">导出</button>
</body>

</html>

效果示例:

推荐阅读更多精彩内容