Node XLSX

此 api 是通过 node-xlsx 、xlsx-style、xlsx 三个 npm 组合使用的，来弥补 node-xlsx 不能设置颜色的问题，参数格式按照 node-xlsx 方式使用，设置样式参数按照 xlsx-style。

注意： 存在缺陷 设置不了行高(待完善)

按照

npm i xlsx-style-node

使用

在 js 中使用 const xlsx = require("xlsx-style-node").default; 在 ts 中使用 import * as xlsx from 'xlsx-style-node';

1const fs = require("fs");
2const data: any = [
3  ["导表时间：2021-05-27 15:36:28"],
4  ["用户Id: 288884"],
5  ["用户名：test"],
6  [],
7  ["对账公式："],
8  ["期末余额=期初余额+入金-出金"],
9  [],
10  ["币种：USD"],
11  [
12    "*期初余额指本月度1号0点0分0秒余额",
13    null,
14    null,
15    "*期末余额指本月度最后一天23点59分59秒余额",
16  ],
17  [
18    "期初余额（总计）",
19    "10000.01",
20    null,
21    "期末余额（总计）",
22    "20000.01",
23    null,
24    "入金（总计）",
25    "20000",
26    null,
27    "出金（总计）",
28    "10000",
29  ],
30  [
31    "账户余额",
32    "8000",
33    null,
34    "账户余额",
35    "18000",
36    null,
37    "账户充值",
38    "19000",
39    null,
40    "账户转出",
41    "9000",
42  ],
43  [
44    "储值卡总可用余额",
45    "99",
46    null,
47    "储值卡总可用余额",
48    "3232",
49    null,
50    "账户转入",
51    "999",
52    null,
53    "账户充值手续费",
54    "000",
55  ],
56  [
57    "额度卡（预算）总可用余额",
58    "99",
59    null,
60    "额度卡（预算）总可用余额",
61    "33",
62    null,
63    "储值卡转入",
64    "44",
65    null,
66    "储值卡转出",
67    "500",
68  ],
69  [
70    null,
71    null,
72    null,
73    null,
74    null,
75    null,
76    "储值卡退款",
77    "22",
78    null,
79    "储值卡消费（已完成）",
80    "111",
81  ],
82  [
83    null,
84    null,
85    null,
86    null,
87    null,
88    null,
89    "额度卡转入",
90    "22",
91    null,
92    "储值卡消费（处理中）",
93    "111",
94  ],
95  [
96    null,
97    null,
98    null,
99    null,
100    null,
101    null,
102    "额度卡退款",
103    "22",
104    null,
105    "储值卡退款手续费",
106    "111",
107  ],
108  [null, null, null, null, null, null, null, null, null, "额度卡转出", "11"],
109  [
110    null,
111    null,
112    null,
113    null,
114    null,
115    null,
116    null,
117    null,
118    null,
119    "额度卡消费（已完成）",
120    "11",
121  ],
122  [
123    null,
124    null,
125    null,
126    null,
127    null,
128    null,
129    null,
130    null,
131    null,
132    "额度卡消费（处理中）",
133    "11",
134  ],
135  [
136    null,
137    null,
138    null,
139    null,
140    null,
141    null,
142    null,
143    null,
144    null,
145    "额度卡退款手续费",
146    "11",
147  ],
148  [null, null, null, null, null, null, null, null, null, "开卡费", "11"],
149];
150
151// 设置样式
152for (const index in data) {
153  if (Number(index) < 5) continue;
154  if (index === "7") continue; // 第八行不处理
155  if (!data[index].length) continue;
156  for (const index2 in data[index]) {
157    if (!data[index][index2]) continue;
158    const content = data[index][index2];
159    if (index === "5") {
160      data[index][index2] = {
161        v: content,
162        s: {
163          font: {
164            size: 24,
165            bold: true, // 加粗
166          },
167        },
168      };
169    } else {
170      data[index][index2] = {
171        v: content,
172        s: {
173          alignment: {
174            vertical: "center",
175            horizontal: "center",
176          },
177          font: {
178            size: 24,
179            ...(index === "8" && { color: { rgb: "ff280c" } }), // 设置颜色
180            ...(index === "9" && { bold: true }), // 字体加粗
181          },
182        },
183      };
184    }
185  }
186}
187
188// 单元格宽度
189const options = {
190  "!cols": [
191    { wpx: 260 },
192    { wpx: 80 },
193    { wpx: 30 },
194    { wpx: 300 },
195    { wpx: 80 },
196    { wpx: 30 },
197    { wpx: 160 },
198    { wpx: 80 },
199    { wpx: 30 },
200    { wpx: 170 },
201    { wpx: 80 },
202  ],
203};
204
205const buffer = xlsx.build([{ name: "mySheetName", data: data, options }]);
206fs.writeFileSync("test1.xlsx", buffer);

文档

node-xlsx