js实现excel导出并附带样式demo保姆级_xlsx-js-style

js xlsx [xlsx-js-style] 附带样式导出excel

目录:一、导出样式    二、实现代码    三、使用细节说明

---

一、导出样式

二、实现代码

1、npm install xlsx-js-style

“xlsx-js-style”: “^1.2.0”

2、实现的所有代码

import XLSXS from "xlsx-js-style";

function exportcc(){
	// STEP 1: Create a new workbook
    const wb = XLSXS.utils.book_new();
    //sheet工作簿标题
    const sheetName = "xlsx导出带样式";
    // STEP 2: Create data rows and styles
    let rowArray = [
      [{
        v: "xlsx导出带样式",
        t: "s",
        s: {
          font: {
            name: "Courier",
            sz: 24,
            bold: true, 
            color:'b8ddb0'
          },
          fill: { fgColor: { rgb: '9e9e9e' } } 
        }
      }],
      [{
          v: "Date",
          t: "s",
          s: {
            font: {
              name: "Courier"
            }
          }
        },
        {
          v: "Disburse Amount",
          t: "s",
          s: {
            font: {
              bold: true,
              color: {
                rgb: "FF0000"
              }
            }
          }
        },
        {
          v: "Disburse New Amount",
          t: "s",
          s: {
            fill: {
              fgColor: {
                rgb: "E9E9E9"
              }
            }
          }
        },
        {
          v: "line\nbreak",
          t: "s",
          s: {
            alignment: {
              wrapText: true
            }
          }
        },
      ]
    ];
    // STEP 3: Create worksheet with rows; Add worksheet to workbook
    const ws = XLSXS.utils.aoa_to_sheet(rowArray);
    XLSXS.utils.book_append_sheet(wb, ws, sheetName);
    
    let maxColumnNumber = 1; //默认最大列数
    rowArray.map(item=>item.length>maxColumnNumber?maxColumnNumber=item.length:'');
    //合并  #将第一行标题列合并
    let merges = ['A1:'+String.fromCharCode(64 + parseInt(maxColumnNumber))+'1'];
    let wsMerge = [];
    merges.map((item)=>{
      wsMerge.push(
        XLSXS.utils.decode_range(item)
      );
    })
    
    ws['!merges'] = wsMerge;
    console.log(ws);
    //边框样式
    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"
        }
      }
    }
    //添加外边框
    rowArray.map((item,index)=>{
        for(let i=1;i<=maxColumnNumber;i++){
          if(!ws[''+String.fromCharCode(64 + parseInt(i))+(index+1)]){
            ws[''+String.fromCharCode(64 + parseInt(i))+(index+1)] = {v:'',t:'s',s:{border:borderStyle}};
            continue ;
          }
          //边框
          ws[''+String.fromCharCode(64 + parseInt(i))+(index+1)].s.border = borderStyle;

          //字体居中
          ws[''+String.fromCharCode(64 + parseInt(i))+(index+1)].s.alignment = { vertical: 'center', horizontal: 'center' } 
        }
    })

    //添加列宽
    ws['!cols'] = ([{
      width: 40
    }, {
      width: 40
    }]);
    //添加行高
    ws['!rows'] = [{ 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }];
    // STEP 4: Write Excel file to browser  #导出
    XLSXS.writeFile(wb, sheetName+".xlsx");
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135

三、使用细节说明

1、修改列宽

 //接受参数为 数组，数组对应从A开始往后内推
 ws['!cols'] = ([{
      width: 40
    }, {
      width: 40
    }]);
1
2
3
4
5
6

2、修改行高

//接受参数为 数组，数组对应从第一开始往后内推
ws['!rows'] = [{ 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }];
1
2

3、修改边框

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"
        }
      }
    }
ws['A1'].s.border = borderStyle;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

4、字体居中

ws['B1'].s.alignment = { vertical: 'center', horizontal: 'center' }
1

5、合并

//将A1和A2合并,后面再次合并可以直接push
const wsMerge = [XLSXS.utils.decode_range('A1:A2')]
ws['!merges'] = wsMerge;
1
2
3

6、字体样式处理
请看依赖库redme.md

github依赖库:https://github.com/gitbrent/xlsx-js-style

说明与疑问：

参考依赖库
https://github.com/SheetJS/sheetjs
https://sheetjs.com/demo/table.html
xlsx-style