前端常见的文件操作
记录一下工作中用到的文件操作
文件的预览
微软的在线预览功能
注意: 文件需要能够公开访问
1
2
3// 前缀: https://view.officeapps.live.com/op/view.aspx?src=
// url: 文件地址
window.open(`https://view.officeapps.live.com/op/view.aspx?src=${encodeURIComponent(url)}`);使用微软的在线预览功能预览文件
Word
https://view.officeapps.live.com/op/view.aspx?src=https://cdn.zxiaosi.com/hexo/office/temp.docx
Excel
https://view.officeapps.live.com/op/view.aspx?src=https://cdn.zxiaosi.com/hexo/office/temp.xlsx
PPT
https://view.officeapps.live.com/op/view.aspx?src=https://cdn.zxiaosi.com/hexo/office/temp.pptx
Edge 浏览器可以直接预览的文件
kkFileView 部署文件预览服务
独立部署,向外提供 http 预览服务(外部系统只需要访问本项目预览接口并传入需要预览文件的 url,就可以打开预览页面)
WebOffice 开放平台 收费
HTML2Canvas + JsPDF 导出 PDF
官网链接
HTML2Canvas 将 HTML 转换为图片
JsPDF 将图片转换为 PDF
代码示例
工具类
exportPdf.ts
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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348import html2canvas from 'html2canvas';
import jsPDF from 'jspdf';
/**
* 参考:
* - https://juejin.cn/post/7138370283739545613
* - https://gitee.com/jseven68/vue-pdf2
*/
interface OutPutPDFParams {
/** 页眉dom元素 */
header?: HTMLElement;
/** 页脚dom元素 */
footer?: HTMLElement;
/** 需要转换的dom根节点 */
element: HTMLElement;
/** 一页pdf的内容宽度,0-592.28 */
contentWidth?: number;
/** pdf文件名 */
filename?: string;
}
interface CanvasData {
width: number;
height: number;
data: string;
}
/** A4纸的宽度 */
const A4_WIDTH = 592.28;
/** A4纸的高度 */
const A4_HEIGHT = 841.89;
/**
* 将元素转化为canvas元素
* @param element 需要转化的元素
* @param width 内容宽度
* @returns 返回canvas元素的宽度、高度、转化后的图片Data
*/
async function toCanvas(element: HTMLElement, width: number): Promise<CanvasData> {
// canvas元素
const canvas = await html2canvas(element, {
// allowTaint: true, // 允许渲染跨域图片
scale: window.devicePixelRatio * 2, // 通过 放大 增加清晰度
useCORS: true, // 允许跨域
});
// 获取canavs转化后的宽度
const canvasWidth = canvas.width;
// 获取canvas转化后的高度
const canvasHeight = canvas.height;
// 高度转化为PDF的高度
const height = (width / canvasWidth) * canvasHeight;
// 转化成图片Data
const canvasData = canvas.toDataURL('image/jpeg', 1.0);
return { width, height, data: canvasData };
}
/**
* 添加页眉
* @param header 页眉元素
* @param pdf jsPDF实例
* @param contentWidth 内容宽度
*/
async function addHeader(header: HTMLElement, pdf: jsPDF, contentWidth: number) {
const { height: headerHeight, data: headerData } = await toCanvas(header, contentWidth);
pdf.addImage(headerData, 'JPEG', 0, 0, contentWidth, headerHeight);
}
/**
* 添加页脚
* @param pageSize 页数
* @param pageNo 当前页码
* @param footer 页脚元素
* @param pdf jsPDF实例
* @param contentWidth 内容宽度
*/
async function addFooter(pageSize: number, pageNo: number, footer: HTMLElement, pdf: jsPDF, contentWidth: number) {
// 页码元素,类名这里写死了
const pageNoDom = footer.querySelector('.pdf-footer-page') as HTMLElement;
const pageSizeDom = footer.querySelector('.pdf-footer-page-count') as HTMLElement;
if (pageNoDom) pageNoDom.innerText = pageNo + '';
if (pageSizeDom) pageSizeDom.innerText = pageSize + '';
const { height: footerHeight, data: footerData } = await toCanvas(footer, contentWidth);
pdf.addImage(footerData, 'JPEG', 0, A4_HEIGHT - footerHeight, contentWidth, footerHeight);
}
/**
* 截取图片添加到pdf中
* @param _x x坐标
* @param _y y坐标
* @param pdf jsPDF实例
* @param data 图片data
* @param width 宽度
* @param height 高度
*/
function addImage(_x: number, _y: number, pdf: jsPDF, data: string, width: number, height: number) {
pdf.addImage(data, 'JPEG', _x, _y, width, height);
}
/**
* 添加空白遮挡
* @param x x 与 页面左边缘的坐标
* @param y y 与 页面上边缘的坐标
* @param width 填充宽度
* @param height 填充高度
* @param pdf jsPDF实例
*/
function addBlank(x: number, y: number, width: number, height: number, pdf: jsPDF) {
pdf.setFillColor(255, 255, 255);
pdf.rect(x, y, Math.ceil(width), Math.ceil(height), 'F');
}
/**
* 获取元素距离网页顶部的距离
* @param element dom元素
* @returns 距离顶部的高度
*/
function getElementTop(element: any) {
let actualTop = element.offsetTop;
let current = element.offsetParent;
// 通过遍历offsetParant获取距离顶端元素的高度值
while (current && current !== null) {
actualTop += current.offsetTop;
current = current.offsetParent;
}
return actualTop;
}
/**
* 生成pdf(A4多页pdf截断问题, 包括页眉、页脚 和 上下左右留空的护理)
* - 内容与页眉、页脚之间留空留白的高度 - baseY
* - 强制分页的标记点 - split-page
* - 自动分页标记 - divide-inside
* @param props {@link OutPutPDFParams}
*/
export async function outputPDF({ element, contentWidth = 550, footer, header, filename = 'temp.pdf' }: OutPutPDFParams) {
// 如果 dom根节点 不存在, 则直接返回
if (!(element instanceof HTMLElement)) return;
// jsPDFs实例
const pdf = new jsPDF({ unit: 'pt', format: 'a4', orientation: 'p' });
/** 根元素距离网页顶部的距离 */
const elementTop = getElementTop(element) || 0;
/** 页眉元素的高度 */
let headerHeight = 0;
/** 页脚元素的高度 */
let footerHeight = 0;
if (header) {
// 页眉元素 经过转换后在PDF的高度
const headerCanvas = await toCanvas(header, contentWidth);
headerHeight = headerCanvas.height;
}
if (footer) {
// 页脚元素 经过转换后在PDF页面的高度
const footerCanvas = await toCanvas(footer, contentWidth);
footerHeight = footerCanvas.height;
}
// 距离 PDF 左边的距离, / 2 表示居中 - 左右边距
const baseX = (A4_WIDTH - contentWidth) / 2;
// 距离 PDF 页眉和页脚的间距, 留白留空
const baseY = 15;
// 除去页眉、页脚、还有内容与两者之间的间距后, 每页内容的实际高度
const originalPageHeight = A4_HEIGHT - headerHeight - footerHeight - 2 * baseY;
// 元素在网页页面的宽度
const elementWidth = element.offsetWidth;
// PDF内容宽度 和 在HTML中宽度 的比, 用于将 元素在网页的高度 转化为 PDF内容内的高度, 将 元素距离网页顶部的高度 转化为 距离Canvas顶部的高度
const rate = contentWidth / elementWidth;
// 每一页的分页坐标, PDF高度, 初始值为根元素距离顶部的距离
const pages = [0];
/**
* 普通元素 - 位置更新方法
* @param top 元素距离顶部的高度
*/
function updateNormalElPos(top: number) {
const prevTop = pages.length > 0 ? pages[pages.length - 1] : 0;
// 当前距离顶部高度 - 上一个分页点的高度 大于 正常一页的高度, 则需要载入分页点
if (top - prevTop > originalPageHeight) pages.push(prevTop + originalPageHeight);
}
/**
* 存在可能跨页的元素 - 位置更新方法
* @param elHeight 元素高度
* @param top 元素距离顶部的高度
*/
function updatePos(elHeight: number, top: number) {
const prevTop = pages.length > 0 ? pages[pages.length - 1] : 0;
// 同 updateNormalElPos 方法
if (top - prevTop >= originalPageHeight) {
pages.push(prevTop + originalPageHeight);
return;
}
// 当前距离顶部高度 + 元素自身高度 - 上一个分页点的高度 大于 一页内容的高度, 则证明元素跨页
// top != prevTop 这个条件是防止多个元素嵌套情况下,他们 top 是一样的
if (top + elHeight - prevTop > originalPageHeight && top !== prevTop) {
pages.push(top);
return;
}
}
/**
* 对于富文本元素,观察所得段落之间都是以<p> / <img> 元素相隔,因此不需要进行深度遍历 (仅针对个人遇到的情况)
*/
function traversingEditor(nodes: NodeListOf<ChildNode>) {
// 遍历子节点
for (let i = 0; i < nodes.length; ++i) {
const one = nodes[i] as HTMLElement;
const { offsetHeight } = one;
const offsetTop = getElementTop(one);
const top = (contentWidth / elementWidth) * offsetTop;
updatePos((contentWidth / elementWidth) * offsetHeight, top);
}
}
/** 遍历正常的元素节点 */
function traversingNodes(nodes: NodeListOf<ChildNode>) {
for (let i = 0; i < nodes.length; ++i) {
/** 当前节点 */
const one = nodes[i] as HTMLElement;
/** 分页标记 - 强制分页的标记点 */
const isSplit = one.classList && one.classList.contains('split-page');
/** 自动分页标记 - 根据元素高度自动判断是否需要分页 */
const isDivideInside = one.classList && one.classList.contains('divide-inside');
/** 终点元素 - 图片元素作为深度终点, 不再继续深入 */
const isIMG = one.tagName === 'IMG';
/** 终点元素 - table的每一行元素也作为深度终点, 不再继续深入, 自定义(antd table 组件) */
const isTableCol = one.classList && one.classList.contains('ant-table-row');
/** 特殊元素 - 富文本元素 */
const isEditor = one.classList && one.classList.contains('editor');
// 对需要处理分页的元素, 计算是否跨界, 若跨界, 则直接将顶部位置作为分页位置, 进行分页, 且子元素不需要再进行判断
const { offsetHeight } = one;
// 计算出距离顶部最终高度 【减去根元素距离网页顶部的高度】
const offsetTop = getElementTop(one) - elementTop;
// dom转换后距离顶部的高度 => 转换成canvas高度
const top = rate * offsetTop;
if (isSplit) {
pages.push(top); // 将当前高度作为分页位置
traversingNodes(one.childNodes); // 执行深度遍历操作
continue;
}
if (isDivideInside) {
updatePos(rate * offsetHeight, top); // 执行位置更新操作
traversingNodes(one.childNodes); // 执行深度遍历操作
continue;
}
if (isTableCol || isIMG) {
// dom高度转换成生成pdf的实际高度
// 代码不考虑dom定位、边距、边框等因素, 需在dom里自行考虑, 如将box-sizing设置为border-box
updatePos(rate * offsetHeight, top);
continue;
}
if (isEditor) {
updatePos(rate * offsetHeight, top); // 执行位置更新操作
traversingEditor(one.childNodes); // 遍历富文本节点
continue;
}
// 对于普通元素, 则判断是否高度超过分页值, 并且深入
updateNormalElPos(top); // 执行位置更新操作
traversingNodes(one.childNodes); // 遍历子节点
}
return;
}
// 深度遍历节点的方法
traversingNodes(element.childNodes);
// 一页的高度, 转换宽度为一页元素的宽度
const { width, height, data } = await toCanvas(element, contentWidth);
// 可能会存在遍历到底部元素为深度节点,可能存在最后一页位置未截取到的情况
// if (pages[pages.length - 1] + originalPageHeight < height) {
// pages.push(pages[pages.length - 1] + originalPageHeight);
// }
// 根据分页位置 开始分页
for (let i = 0; i < pages.length; ++i) {
console.log(`%c共${pages.length}页, 生成第${i + 1}页`, 'color: yellow');
// 向 PDF 中添加 canvas 图片 (dom元素)
addImage(baseX, baseY + headerHeight - pages[i], pdf, data, width, height);
// 将 内容 与 页眉之间留空留白的部分进行遮白处理
addBlank(0, headerHeight, A4_WIDTH, baseY, pdf);
// 将 内容 与 页脚之间留空留白的部分进行遮白处理
addBlank(0, A4_HEIGHT - baseY - footerHeight, A4_WIDTH, baseY, pdf);
// 对于除最后一页外,对 内容 的多余部分进行遮白处理
if (i < pages.length - 1) {
// 获取当前页面需要的内容部分高度
const imageHeight = pages[i + 1] - pages[i];
// 对多余的内容部分进行遮白
addBlank(0, baseY + imageHeight + headerHeight, A4_WIDTH, A4_HEIGHT - imageHeight, pdf);
}
// 添加页眉
if (header) await addHeader(header, pdf, A4_WIDTH);
// 添加页脚
if (footer) await addFooter(pages.length, i + 1, footer, pdf, A4_WIDTH);
// 若不是最后一页,则分页
if (i !== pages.length - 1) {
// 增加分页
pdf.addPage();
}
}
return pdf.save(filename);
}使用示例
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
38import { Button } from 'antd';
import styles from './index.module.less';
import { outputPDF } from '../../utils/exportPdf';
const Index = () => {
/** 下载 */
const handleDownload = async () => {
const element = document.getElementById('pdfContent');
if (element) {
try {
await outputPDF({ element, contentWidth: 560, filename: 'temp.pdf' });
} catch (error) {
console.log(error);
}
}
};
return (
<div className={styles.page}>
<Button type="primary" onClick={handleDownload}>
下载
</Button>
{/* 不设置宽度, 默认以 父元素的的宽度 为 PDF宽度 */}
<div className={styles.pdf}>
<div id="pdfContent" className={styles.pdfContent}>
<div>我是内容</div>
<img src="" alt="" crossOrigin="anonymous" />
{/* 图片一定要加 crossOrigin 参数 */}
{/* <img src="" alt="" crossOrigin="anonymous" /> */}
</div>
</div>
</div>
);
};
export default Index;
功能演示
Fortune-Sheet + ExcelJS 导入导出 Excel
官网链接
代码示例
工具类
enum.ts
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/** 边框样式枚举 */
export const borderStyleMap = {
'1': 'thin', // 细边框
'2': 'hair', // 虚线边框
'3': 'dotted', // 点状边框
'4': 'dashed', // 虚线边框
'5': 'dashDot', // 点划线边框
'6': 'dashDotDot', // 双点划线边框
'7': 'double', // 双边框
'8': 'medium', // 中等边框
'9': 'mediumDashed', // 中等虚线边框
'10': 'mediumDashDot', // 中等点划线边框
'11': 'mediumDashDotDot', // 中等双点划线边框
'12': 'slantDashDot', // 斜线点划线边框
'13': 'thick', // 粗边框
};
/** 水平对齐枚举 */
export const verticalAlignmentMap = {
'0': 'middle', // 居中对齐
'1': 'top', // 顶部对齐
'2': 'bottom', // 底部对齐
};
/** 垂直对齐枚举 */
export const horizontalAlignmentMap = {
'0': 'center', // 居中对齐
'1': 'left', // 左对齐
'2': 'right', // 右对齐
};
/** 文本换行枚举 */
export const wrapTextMap = {
'1': false, // 溢出
'2': true, // 自动换行
};
/** 文本角度枚举 */
export const textRotationMap = {
'1': 45, // 向上倾斜
'2': -45, // 向下倾斜
'3': 'vertical', // 竖排文字
'4': 90, // 向上90°
'5': -90, // 向下90°
};
/**
* 根据key获取value
* @param map 枚举对象
* @param key 枚举key
*/
export function getValueByKey(map: Record<string, any>, key: any): any {
return map[key];
}
/**
* 据value获取key
* @param map 枚举对象
* @param value 枚举value
*/
export function getKeyByValue(map: Record<string, any>, value: any): any {
return Object.keys(map).find((key) => map[key] === value);
}工具类
tool.ts
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/** 将单元格坐标转为对应的行列索引 */
export const handleCellToRowCol = (cell: string) => {
// 将字母部分转换为大写
const colIndex = cell.substring(0, 1).toUpperCase();
// 提取数字部分并转换为数字
const row = parseInt(cell.substring(1));
// 将列转换为从 1 开始的数字,A 对应 1,B 对应 2,依此类推
const col = colIndex.charCodeAt(0) - 'A'.charCodeAt(0) + 1;
return { row, col };
};
/**
* 转换内部链接引用
* @param reference - 内部链接引用
*/
export const handleConvertReference = (reference: string) => {
// 使用正则表达式分隔工作表名称和单元格范围
const regex = /^(.*)!(.*)$/;
const match = reference.match(regex);
if (match) {
let sheetName = match[1];
const cellRange = match[2];
// 检查工作表名称是否已经用单引号包裹
if (!sheetName.startsWith("'")) {
sheetName = `${sheetName}`;
}
// 在原始引用前添加 #
const convertedReference = cellRange ? `#${sheetName}!${cellRange}` : `#${sheetName}`;
return convertedReference;
} else {
// 如果输入格式不正确,返回原始引用
return reference;
}
};
/**
* 将 ARGB 颜色值转换为 RGB 格式
*/
function handleRgbToHex(r, g, b) {
return ((1 << 24) + (r << 16) + (g << 8) + b).toString(16).slice(1);
}
/**
* 将 RGB 颜色值转换为 ARGB 格式
*/
export function handleColorToARGB(color: string, defaultColor?: string) {
if (!color) return defaultColor || undefined;
let hex = '';
// 如果颜色值是十六进制格式
if (color?.startsWith('#')) {
hex = color?.slice(1);
}
// 如果颜色值是 RGB 格式
else if (color?.startsWith('rgb')) {
const rgbValues = color?.match(/\d+/g);
hex = handleRgbToHex(Number(rgbValues?.[0]), Number(rgbValues?.[1]), Number(rgbValues?.[2]));
}
// 补全颜色值长度为 6 位
if (hex?.length === 3) {
hex = hex.replace(/(.)/g, '$1$1');
}
// 将 ARGB 值返回
return 'FF' + hex?.toUpperCase();
}
/**
* 将 ARGB 颜色值转换为 RGB 格式
*/
export function handleArgbToHex(argb?: string) {
if (!argb) return undefined;
return argb.replace('FF', '#');
}
/**
* 处理类型
* @param type 类型
* @param v1 计算值1
* @param v2 计算值2
*/
export const handleOperator = (type: string, v1?: any, v2?: any) => {
switch (type) {
case 'between':
return `介于${v1}和${v2}之间`;
case 'notBetween':
return `不介于${v1}和${v2}之间`;
case 'equal':
return `等于${v1}`;
case 'notEqual':
return `不等于${v1}`;
case 'greaterThan':
return `大于${v1}`;
case 'lessThan':
return `小于${v1}`;
case 'greaterThanOrEqual':
return `大于等于${v1}`;
case 'lessThanOrEqual':
return `小于等于${v1}`;
default:
return '';
}
};
/** 获取最大值 */
export const handleMaxValue = (...numbers: any[]) => {
const filteredNumbers = numbers.filter((num) => typeof num === 'number');
if (filteredNumbers.length === 0) return 0; // 没有有效的数值参数
return Math.max(...filteredNumbers);
};工具类
exportExcel.ts
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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628import { Sheet, Cell, SheetConfig } from '@fortune-sheet/core';
import ExcelJS, { Alignment, Border, BorderStyle, Borders, CellModel, CellValue, DataValidation, Fill, Font, Workbook, Worksheet, WorksheetView } from 'exceljs';
import { handleConvertReference, handleColorToARGB, handleOperator } from './tool';
import { borderStyleMap, getValueByKey, horizontalAlignmentMap, textRotationMap, verticalAlignmentMap, wrapTextMap } from './enum';
/**
* excel 单元格默认的宽度和高度 是 8.43 和 15
* - https://support.microsoft.com/zh-cn/office/%E6%9B%B4%E6%94%B9%E5%88%97%E5%AE%BD%E5%92%8C%E8%A1%8C%E9%AB%98-72f5e3cc-994d-43e8-ae58-9774a0905f46?ns=excel&version=21&syslcid=2052&uilcid=2052&appver=zxl210&helpid=20926&ui=zh-cn&rs=zh-cn&ad=cn
*
* fortune-sheet 单元格默认的宽度和高度 是 73 和 19
* - https://ruilisi.github.io/fortune-sheet-docs/zh/guide/sheet.html#%E5%88%9D%E5%A7%8B%E5%8C%96%E9%85%8D%E7%BD%AE
*/
/** excel 默认列宽 */
const excelColWidth = 8.43;
/** excel 默认行高 */
const excelRowHeight = 15;
/** fortune-sheet 默认列宽 */
const defaultColWidth = 73;
/** fortune-sheet 默认行高 */
const defaultRowHeight = 19;
/** fortune-sheet 默认行数 */
const defaultRows = 36;
/** fortune-sheet 默认列数 */
const defaultColumns = 18;
/**
* 处理行高、列宽、隐藏行、隐藏列
*/
const handleColumnRowLen = (worksheet: Worksheet, config: Sheet['config']) => {
if (!config) return worksheet;
const { columnlen, rowlen, rowhidden, colhidden } = config;
const conversionWidth = defaultColWidth / excelColWidth;
const conversionHeight = defaultRowHeight / excelRowHeight;
// 列长度
if (columnlen) {
const columns = Object.keys(columnlen);
columns.forEach((column) => {
worksheet.getColumn(Number(column) + 1).width = (columnlen?.[column] ?? 0) / conversionWidth;
});
}
// 行高度
if (rowlen) {
const rows = Object.keys(rowlen);
rows.forEach((row) => {
worksheet.getRow(Number(row) + 1).height = (rowlen?.[row] ?? 0) / conversionHeight;
});
}
// 隐藏行
if (rowhidden) {
const rows = Object.keys(rowhidden);
rows.forEach((row) => {
worksheet.getRow(Number(row) + 1).hidden = true;
});
}
// 隐藏列
if (colhidden) {
const columns = Object.keys(colhidden);
columns.forEach((column) => {
worksheet.getColumn(Number(column) + 1).hidden = true;
});
}
return worksheet;
};
/**
* 处理图片: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%9B%BE%E7%89%87
* - excelJs 不会处理已变化单元格的宽度 https://github.com/exceljs/exceljs/issues/2730
* @param workbook 工作簿
* @param worksheet 工作表
* @param sheetData 工作表数据
*/
const handleImages = (workbook: Workbook, worksheet: Worksheet, sheetData: Sheet) => {
const { row, column, images, config } = sheetData;
if (!images) return worksheet;
const { colhidden, columnlen, rowhidden, rowlen } = config || {};
const columnObj = { ...colhidden, ...columnlen }; // 列对象
const rowObj = { ...rowhidden, ...rowlen }; // 行对象
const rowLen = row || defaultRows; // 行数
const columnLen = column || defaultColumns; // 列数
for (let i = 0; i < images?.length; i++) {
const { src, height, width, left, top } = images?.[i] || {};
let col = 0; // 图片所在列 0~1 表示一个单元格
let row = 0; // 图片所在行 0~1 表示一个单元格
let restLeft = left; // 剩余宽度
let restTop = top; // 剩余高度
for (let i = 0; i <= rowLen; i++) {
const minuend = columnObj[i] || defaultColWidth;
if (restLeft - minuend > 0) {
restLeft -= minuend;
col++;
} else {
col += restLeft / minuend;
break;
}
}
for (let i = 0; i <= columnLen; i++) {
const minuend = rowObj[i] || defaultRowHeight;
if (restTop - minuend > 0) {
restTop -= minuend;
row++;
} else {
row += restTop / minuend;
break;
}
}
if (col === 0) col = left / defaultColWidth; // 如果不存在自定义列和隐藏列, 设置列默认值
if (row === 0) row = top / defaultRowHeight; // 如果不存在自定义行和隐藏行, 设置行默认值
const imageId = workbook.addImage({ base64: src, extension: 'png' });
worksheet.addImage(imageId, { tl: { col, row }, ext: { width, height }, editAs: 'absolute' });
}
return worksheet;
};
/**
* 处理边框: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E8%BE%B9%E6%A1%86
* @param worksheet 工作表
* @param borderInfo 边框信息
*/
const handleBorder = (worksheet: Worksheet, borderInfo: any[] = []) => {
if (!borderInfo) return worksheet;
borderInfo?.forEach((border) => {
let realBorder = {} as Partial<Borders>;
const { row, column } = border?.range?.[0] || {};
const style = getValueByKey(borderStyleMap, border?.style) as BorderStyle;
switch (border?.borderType) {
case 'border-left' /* 左框线 */:
case 'border-right' /* 右框线 */:
case 'border-top' /* 上框线 */:
case 'border-bottom' /* 下框线 */: {
const key = border?.borderType?.slice?.(7);
const currentCell = worksheet.getCell(row?.[0] + 1, column?.[0] + 1);
currentCell.border = { ...currentCell.border, [key]: { style, color: { argb: handleColorToARGB(border?.color) } } };
break;
}
case 'border-none' /* 无 */:
case 'border-all' /* 所有 */: {
const borderObj = { style, color: { argb: handleColorToARGB(border?.color) } } as Partial<Border>;
realBorder = { top: borderObj, left: borderObj, bottom: borderObj, right: borderObj };
for (let i = row?.[0]; i <= row?.[1]; i++) {
for (let j = column?.[0]; j <= column?.[1]; j++) {
worksheet.getCell(i + 1, j + 1).border = realBorder;
}
}
break;
}
case 'border-inside' /* 内侧 */: {
const borderObj = { style, color: { argb: handleColorToARGB(border?.color) } } as Partial<Border>;
// 内侧横线的范围,从 row[0] + 1 到 row[1] - 1 行,从 column[0] 到 column[1] 列
for (let i = row?.[0] + 1; i <= row?.[1] - 1; i++) {
for (let j = column?.[0]; j <= column?.[1]; j++) {
const cell = worksheet.getCell(i + 1, j + 1);
cell.border = { top: borderObj, bottom: borderObj };
}
}
// 内侧竖线的范围,从 row[0] 到 row[1] 行,从 column[0] + 1 到 column[1] - 1 列
for (let i = row?.[0]; i <= row?.[1]; i++) {
for (let j = column?.[0] + 1; j <= column?.[1] - 1; j++) {
const cell = worksheet.getCell(i + 1, j + 1);
cell.border = { ...cell.border, left: borderObj, right: borderObj };
}
}
break;
}
case 'border-outside' /* 外侧 */: {
const borderObj = { style, color: { argb: handleColorToARGB(border?.color) } } as Partial<Border>;
// 外侧边框的范围,从 row[0] 到 row[1] 行,从 column[0] 到 column[1] 列
for (let i = row?.[0]; i <= row?.[1]; i++) {
for (let j = column?.[0]; j <= column?.[1]; j++) {
// 设置外侧边框,仅设置最外侧一圈
if (i === row[0] || i === row[1] || j === column[0] || j === column[1]) {
const cell = worksheet.getCell(i + 1, j + 1);
cell.border = {
top: i === row[0] ? borderObj : undefined,
bottom: i === row[1] ? borderObj : undefined,
left: j === column[0] ? borderObj : undefined,
right: j === column[1] ? borderObj : undefined,
};
}
}
}
break;
}
case 'border-horizontal' /* 内侧横线 */: {
const borderObj = { style, color: { argb: handleColorToARGB(border?.color) } } as Partial<Border>;
// 内侧横线的范围,从 row[0] + 1 到 row[1] - 1 行,从 column[0] 到 column[1] 列
for (let i = row?.[0] + 1; i <= row?.[1] - 1; i++) {
for (let j = column?.[0]; j <= column?.[1]; j++) {
const cell = worksheet.getCell(i + 1, j + 1);
cell.border = { top: borderObj, bottom: borderObj };
}
}
break;
}
case 'border-vertical' /* 内侧竖线 */: {
const borderObj = { style, color: { argb: handleColorToARGB(border?.color) } } as Partial<Border>;
// 内侧竖线的范围,从 row[0] 到 row[1] 行,从 column[0] + 1 到 column[1] - 1 列
for (let i = row?.[0]; i <= row?.[1]; i++) {
for (let j = column?.[0] + 1; j <= column?.[1] - 1; j++) {
const cell = worksheet.getCell(i + 1, j + 1);
cell.border = { left: borderObj, right: borderObj };
}
}
break;
}
case 'border-slash' /* 边框斜线 */: {
const borderObj = { up: false, down: true, style, color: { argb: handleColorToARGB(border?.color) } } as Partial<Border>;
for (let i = row?.[0]; i <= row?.[1]; i++) {
for (let j = column?.[0]; j <= column?.[1]; j++) {
const cell = worksheet.getCell(i + 1, j + 1);
cell.border = { diagonal: borderObj };
}
}
break;
}
default:
break;
}
});
return worksheet;
};
/**
* 处理冻结视图: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%86%BB%E7%BB%93%E8%A7%86%E5%9B%BE
* @param worksheet Worksheet 工作表
* @param frozen Sheet["frozen"] 冻结视图
*/
const handleFrozen = (worksheet: Worksheet, frozen: Sheet['frozen']): Worksheet['views'] => {
if (frozen) {
const { type, range } = frozen;
const column_focus = Number(range?.column_focus || -1) + 1;
const row_focus = Number(range?.row_focus || -1) + 1;
const otherView: Partial<WorksheetView> = worksheet.views[0];
switch (type) {
case 'rangeColumn':
return [{ ...otherView, state: 'frozen', xSplit: column_focus, ySplit: 0, style: undefined }];
case 'rangeRow':
return [{ ...otherView, state: 'frozen', xSplit: 0, ySplit: row_focus, style: undefined }];
case 'both':
return [{ ...otherView, state: 'frozen', xSplit: column_focus, ySplit: row_focus, style: undefined }];
default:
return worksheet.views;
}
} else {
return worksheet.views;
}
};
/**
* 处理自动筛选器: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E8%87%AA%E5%8A%A8%E7%AD%9B%E9%80%89%E5%99%A8
* - excelJs 没有 filter 参数
* @param filter_select 自动筛选器
*/
const handleAutoFilter = (filter_select: Sheet['filter_select']): Worksheet['autoFilter'] => {
if (filter_select && Object.keys(filter_select).length > 0) {
const { row, column } = filter_select;
return {
from: { row: row[0], column: column[0] },
to: { row: row[1], column: column[1] },
};
} else {
return undefined;
}
};
/**
* 处理公式值: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%85%AC%E5%BC%8F%E5%80%BC
* @param f 公式
* @param v 值
*/
const handleFormula = (f: string, v: string | number | boolean | undefined) => {
return { formula: f, result: v };
};
/**
* 处理超链接: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E8%B6%85%E9%93%BE%E6%8E%A5%E5%80%BC
* @param hyperlink 超链接
* @param r 行
* @param c 列
* @param v 真实值
*/
const handleHyperlink = (hyperlink: Sheet['hyperlink'], r: number, c: number, v: any): CellValue => {
const hl = hyperlink?.[r + '_' + c];
switch (hl?.linkType) {
case 'webpage' /** 网页跳转 */:
return { text: v, hyperlink: hl?.linkAddress, tooltip: hl?.linkAddress };
case 'cellrange' /** 文件内跳转 */:
return { text: v, hyperlink: handleConvertReference(hl?.linkAddress) };
case 'sheet' /** 工作表跳转 */:
return { text: v, hyperlink: handleConvertReference(hl?.linkAddress + '!A1') };
}
};
/**
* 处理正常值
* @param ct 单元格类型
* @param m 显示值
*/
const handleNormalValue = (ct: Cell['ct'], m: any) => {
// 对数字类型的值特殊处理
return ct?.fa === 'General' && ct?.t === 'n' ? Number(m) : m || null;
};
/**
* 处理数字格式: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E6%95%B0%E5%AD%97%E6%A0%BC%E5%BC%8F
* @param ct 单元格类型
*/
const handleNumFmt = (ct: Cell['ct']) => {
return ct?.fa || '';
};
/**
* 处理字体: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%AD%97%E4%BD%93
* @param cell 单元格
*/
const handleFont = (cell: Cell): Partial<Font> => {
const { ff, fs, bl, it, un, cl, fc } = cell;
return {
// @ts-ignore
name: ff,
size: fs || 10,
bold: bl === 1, // 加粗
italic: it === 1, // 斜体
underline: un === 1, // 下划线
strike: cl === 1, // 删除线
color: { argb: handleColorToARGB(fc!, 'FF000000') }, // 颜色
};
};
/**
* 处理背景色: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%A1%AB%E5%85%85
* @param bg 背景色
*/
const handleBackground = (bg: string): Fill => {
return { type: 'pattern', pattern: 'solid', fgColor: { argb: handleColorToARGB(bg, 'FFFFFFFF') } };
};
/**
* 处理对齐方式: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%AF%B9%E9%BD%90
* @param cell 单元格
*/
const handleAlignment = (cell: Cell): Partial<Alignment> => {
const { vt, ht, tb, rt } = cell;
const alignment: Partial<Alignment> = {
vertical: undefined,
horizontal: 'left',
textRotation: 0,
};
switch (tb + '') {
case '0' /* 截断 */:
alignment.horizontal = 'fill';
break;
case '1' /* 溢出 */:
case '2' /* 自动换行 */:
alignment.wrapText = getValueByKey(wrapTextMap, vt + '');
break;
}
alignment.vertical = getValueByKey(verticalAlignmentMap, vt + ''); // 垂直对齐
alignment.horizontal = getValueByKey(horizontalAlignmentMap, ht + ''); // 水平对齐
alignment.textRotation = getValueByKey(textRotationMap, rt + ''); // 文本角度
return alignment;
};
/**
* 处理合并单元格: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%90%88%E5%B9%B6%E5%8D%95%E5%85%83%E6%A0%BC
* @param worksheet 工作表
* @param merge 合并单元格信息
* @param r 行
* @param c 列
*/
const handleMergeCells = (worksheet: Worksheet, merge: SheetConfig['merge'], r: number, c: number) => {
const mergeObj = merge?.[r + '_' + c];
if (mergeObj) {
const { r: merge_r, c: merge_c, rs: merge_rs, cs: merge_cs } = mergeObj;
worksheet.mergeCells(merge_r + 1, merge_c + 1, merge_r + merge_rs, merge_c + merge_cs);
}
return worksheet;
};
/**
* 处理注释/批注: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%8D%95%E5%85%83%E6%A0%BC%E6%B3%A8%E9%87%8A
* @param ps 批注
*/
const handleNote = (ps: Cell['ps']): string | CellModel['comment'] => {
// exceljs 不支持注释换行 x.x
const texts = ps?.value?.split('<br>').map((_) => ({ text: _ }));
return { texts: texts, editAs: 'twoCells' };
};
/**
* 数据验证: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E6%95%B0%E6%8D%AE%E9%AA%8C%E8%AF%81
* @param dataVerification 数据验证
* @param r 行
* @param c 列
* @param v 值
*/
const handleDataValidation = (dataVerification: Sheet['dataVerification'], r: number, c: number): DataValidation => {
const dv = dataVerification?.[r + '_' + c];
switch (dv?.type) {
case 'dropdown' /* 下拉框 */: {
return {
type: 'list',
formulae: [`"${dv?.value1}"`],
allowBlank: true,
showInputMessage: dv?.hintShow,
// promptTitle: "提示",
prompt: dv?.hintValue,
showErrorMessage: dv?.prohibitInput,
// errorTitle: "错误",
error: '你选择的不是下拉列表中的选项',
};
}
case 'number_integer' /* 整数 */: {
const tip = handleOperator(dv?.type2, dv?.value1, dv?.value2);
return {
type: 'whole',
operator: dv?.type2,
formulae: [Number(dv?.value1), Number(dv?.value2)],
allowBlank: true,
showInputMessage: dv?.hintShow,
prompt: dv?.hintValue,
showErrorMessage: dv?.prohibitInput,
error: `你输入的不是${tip}的整数`,
};
}
case 'number_decimal' /* 小数 */: {
const tip = handleOperator(dv?.type2, dv?.value1, dv?.value2);
return {
type: 'decimal',
operator: dv?.type2,
formulae: [Number(dv?.value1), Number(dv?.value2)],
allowBlank: true,
showInputMessage: dv?.hintShow,
prompt: dv?.hintValue,
showErrorMessage: dv?.prohibitInput,
error: `你输入的不是${tip}的小数`,
};
}
case 'text_length' /* 文本长度 */: {
const tip = handleOperator(dv?.type2, dv?.value1, dv?.value2);
return {
type: 'textLength',
operator: dv?.type2,
formulae: [Number(dv?.value1), Number(dv?.value2)],
allowBlank: true,
showInputMessage: dv?.hintShow,
prompt: dv?.hintValue,
showErrorMessage: dv?.prohibitInput,
error: `你输入的不是长度${tip}的文本`,
};
}
case 'date' /* 日期 */: {
const tip = handleOperator(dv?.type2, dv?.value1, dv?.value2);
return {
type: 'date',
operator: dv?.type2,
formulae: [new Date(dv?.value1), new Date(dv?.value2)],
allowBlank: true,
showInputMessage: dv?.hintShow,
prompt: dv?.hintValue,
showErrorMessage: dv?.prohibitInput,
error: `你输入的不是${tip}的日期`,
};
}
case 'number' /* 数字 */:
case 'text_content' /** 文本内容 */:
case 'validity' /* 有效性 */:
case 'checkbox' /* 复选框 */:
default:
// 这里处理的都是 exceljs 不支持的类型 - x.x
return { type: 'custom', formulae: [], allowBlank: true };
}
};
/**
* 下载Excel
* @param workbook ExcelJS.Workbook
* @param fileName 文件名
*/
export const handleDownloadExcel = async (workbook: Workbook, fileName: string = 'temp.xlsx') => {
// 将工作簿转换为二进制数据
const buffer = await workbook.xlsx.writeBuffer();
const blob = new Blob([buffer], { type: 'application/octet-stream' });
const url = window.URL.createObjectURL(blob);
// 创建一个 a 标签
const link = document.createElement('a');
link.href = url;
link.setAttribute('download', fileName);
document.body.appendChild(link);
link.click();
// 释放资源
window.URL.revokeObjectURL(url);
document.body.removeChild(link);
};
/**
* 导出Excel
* - 问题1: 列宽和行高改变时会影响图片的位置
* - 问题2: excelJs 没有 filter 参数
* @param workbookData 工作簿数据
* @param fileName 文件名
*/
export const handleExportExcel = async (workbookData: Sheet[], fileName: string) => {
// 创建一个工作簿
const workbook = new ExcelJS.Workbook();
// 工作表排序
const newWorkbookData = [...workbookData]?.sort((a, b) => (a.order ?? 0) - (b.order ?? 0));
newWorkbookData.map((sheetData) => {
const { name, hide, zoomRatio = 1, data, config, frozen, filter_select, dataVerification, hyperlink }: Sheet = sheetData!;
let worksheet = workbook.addWorksheet(name, {
// 工作表隐藏: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%B7%A5%E4%BD%9C%E8%A1%A8%E7%8A%B6%E6%80%81
state: hide === 1 ? 'hidden' : 'visible',
// 视图百分比: https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%B7%A5%E4%BD%9C%E8%A1%A8%E8%A7%86%E5%9B%BE
views: [{ zoomScale: zoomRatio * 100 }],
properties: {
/**
* 列宽是自动计算的, 行高是15
* https://github.com/exceljs/exceljs/blob/master/README_zh.md#%E5%B7%A5%E4%BD%9C%E8%A1%A8%E5%B1%9E%E6%80%A7
*/
defaultColWidth: excelColWidth,
defaultRowHeight: excelRowHeight,
},
});
// 处理行高、列宽、隐藏行、隐藏列
worksheet = handleColumnRowLen(worksheet, config);
// 处理图片
worksheet = handleImages(workbook, worksheet, sheetData);
// 处理边框
worksheet = handleBorder(worksheet, config?.borderInfo);
// 处理冻结视图
worksheet.views = handleFrozen(worksheet, frozen);
// 处理筛选器
worksheet.autoFilter = handleAutoFilter(filter_select);
// 处理数据
data?.forEach((row, r) => {
const newRow = row.map((cell, c) => {
if (!cell) return null;
const { f, m, v, ct, hl, bg, ps } = cell; // 单元格
const currentCell = worksheet.getCell(r + 1, c + 1); // 获取单元格
// currentCell.model.type = 1; // 默认类型
// currentCell.model.text = m ? m + "" : undefined; // 默认文本
// currentCell.model.value = v; // 默认值
if (f) currentCell.value = handleFormula(f, v); // 公式值
else if (hl) currentCell.value = handleHyperlink(hyperlink, r, c, v); // 超链接
else currentCell.value = handleNormalValue(ct, m || v); // 正常值
if (ct) currentCell.numFmt = handleNumFmt(ct); // 数字格式
if (cell) currentCell.font = handleFont(cell); // 字体
if (bg) currentCell.fill = handleBackground(bg); // 背景色
if (cell) currentCell.alignment = handleAlignment(cell); // 对齐方式
if (ps) currentCell.note = handleNote(ps); // 注释
if (dataVerification) currentCell.dataValidation = handleDataValidation(dataVerification, r, c); // 数据验证
if (config?.merge) worksheet = handleMergeCells(worksheet, config?.merge, r, c); // 合并单元格
});
worksheet.addRow(newRow);
});
});
// 下载Excel
await handleDownloadExcel(workbook, fileName);
};工具类
importExcel.ts
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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473import ExcelJS, { Worksheet } from 'exceljs';
import { Cell, CellWithRowAndCol, Sheet, SheetConfig } from '@fortune-sheet/core';
import { handleArgbToHex, handleCellToRowCol, handleMaxValue } from './tool';
import { borderStyleMap, getKeyByValue, horizontalAlignmentMap, textRotationMap, wrapTextMap } from './enum';
/** excel 默认列宽 */
const excelColWidth = 8.43;
/** excel 默认行高 */
const excelRowHeight = 15;
// const excelRowHeight = 14.25;
/** fortune-sheet 默认列宽 */
const defaultColWidth = 73;
/** fortune-sheet 默认行高 */
const defaultRowHeight = 19;
/** fortune-sheet 默认行数 */
const defaultRows = 36;
/** fortune-sheet 默认列数 */
const defaultColumns = 18;
/**
* 导入Excel
* - 问题1: 列宽和行高改变时会影响图片的位置
* - 问题2: excelJS 获取不到工作表内的超链接
* - 问题3: fortune-sheet 数字格式(百分比、货币等)不生效
* - 问题4: fortune-sheet 斜线边框方法不生效
* @param file 文件
*/
export const handleImportExcel = async (file: File) => {
try {
// 初始化数据
const excelData: Sheet[] = [];
// 读取Excel文件
const buffer = await file.arrayBuffer();
const workbook = new ExcelJS.Workbook();
const worksheets = await workbook.xlsx.load(buffer);
// 遍历所有的工作表
worksheets.eachSheet((worksheet: Worksheet) => {
const { name, rowCount, columnCount, state, views, autoFilter } = worksheet;
// 声明变量
let frozen = {} as Sheet['frozen']; // 冻结区域
const filter_select = {} as { row: number[]; column: number[] }; // 筛选
const config = {} as SheetConfig; // 配置
const images = [] as Sheet['images']; // 图片
const hyperlink = {} as Record<string, { linkType: string; linkAddress: string }>; // 超链接
const calcChain = [] as Sheet['calcChain']; // 公式链
const dataVerification = {} as any; // 数据验证
const celldata = [] as CellWithRowAndCol[]; // 单元格数据
// 最大行数和列数
const maxRow = handleMaxValue(rowCount, defaultRows);
const maxCol = handleMaxValue(columnCount, defaultColumns);
// 是否隐藏
const hide = state === 'visible' ? 0 : 1;
// 缩放比例
const zoomRatio = (views[0]?.zoomScale || 100) / 100;
// 冻结区域 - 控制台会报错: https://github.com/ruilisi/fortune-sheet/issues/434
if (views[0].state === 'frozen') {
const { xSplit = 0, ySplit = 0 } = views[0]; // 选中区域
if (xSplit > 0 && ySplit > 0) {
frozen = { type: 'both', range: { row_focus: ySplit - 1, column_focus: xSplit - 1 } };
} else if (xSplit === 0) {
frozen = { type: 'rangeRow', range: { row_focus: ySplit - 1, column_focus: 0 } };
} else if (ySplit > 0) {
frozen = { type: 'rangeColumn', range: { row_focus: 0, column_focus: xSplit - 1 } };
}
}
// 自动筛选器
if (autoFilter) {
filter_select.row = [autoFilter.from.row, autoFilter.to.row];
filter_select.column = [autoFilter.from.col, autoFilter.to.col];
}
// 处理列宽、隐藏列
for (let colNumber = 1; colNumber <= maxCol; colNumber++) {
const curCol = worksheet.getColumn(colNumber);
if (curCol.width) {
if (config.columnlen === undefined) config.columnlen = {};
config.columnlen[colNumber - 1] = curCol.width * (defaultColWidth / excelColWidth);
}
if (curCol.hidden) {
if (config.colhidden === undefined) config.colhidden = {};
config.colhidden[colNumber - 1] = 0;
}
}
// 处理行高、隐藏行
for (let rowNumber = 1; rowNumber <= maxRow; rowNumber++) {
const curRow = worksheet.getRow(rowNumber);
if (curRow.height) {
if (config.rowlen === undefined) config.rowlen = {};
config.rowlen[rowNumber - 1] = curRow.height * (defaultRowHeight / excelRowHeight);
}
if (curRow.hidden) {
if (config.rowhidden === undefined) config.rowhidden = {};
config.rowhidden[rowNumber - 1] = 0;
}
}
// 处理图片: excelJs 不会处理已变化单元格的宽度 https://github.com/exceljs/exceljs/issues/2730
const sheetImages = worksheet.getImages();
if (sheetImages && sheetImages.length > 0) {
sheetImages.map((item) => {
const { imageId, range } = item;
// 获取图像的数据
const image = workbook.getImage(Number(imageId));
// 图片的base64
const base64 = `data:${image.type}/${image.extension};base64,${image.buffer?.toString('base64')}`;
// 获取图像的左上角和右下角的单元格位置
const topLeftCell = range.tl;
const bottomRightCell = range.br;
// 获取单元格的行高和列宽,并计算图像的宽度和高度
let realWidth = 0;
let realHeight = 0;
// 图片在单元格内
if (Math.floor(topLeftCell.row) === Math.floor(bottomRightCell.row)) {
const curRow = Math.floor(topLeftCell.row) + 1;
const height = Number(worksheet.getRow(curRow).height || 0) * (defaultRowHeight / excelRowHeight) || defaultRowHeight;
realHeight = height * (bottomRightCell.row - topLeftCell.row);
} else {
for (let row = Math.floor(topLeftCell.row); row < Math.floor(bottomRightCell.row) + 1; row++) {
const height = worksheet.getRow(row).height * (defaultRowHeight / excelRowHeight) || defaultRowHeight;
if (row < topLeftCell.row) {
realHeight += height * (topLeftCell.row - row);
} else if (row > bottomRightCell.row) {
realHeight += height * (1 - (row - bottomRightCell.row));
} else {
realHeight += height;
}
}
}
if (Math.floor(topLeftCell.col) === Math.floor(bottomRightCell.col)) {
const curCol = Math.floor(topLeftCell.col) + 1;
const width = Number(worksheet?.getColumn(curCol)?.width || 0) * (defaultColWidth / excelColWidth) || defaultColWidth;
realWidth = width * (bottomRightCell.col - topLeftCell.col);
} else {
for (let col = topLeftCell.col; col < bottomRightCell.col; col++) {
const width = Number(worksheet?.getColumn(col)?.width || 0) * (defaultColWidth / excelColWidth) || defaultColWidth;
if (col < topLeftCell.col) {
realWidth += width * (topLeftCell.col - col);
} else if (col > bottomRightCell.col) {
realWidth += width * (1 - (col - bottomRightCell.col));
} else {
realWidth += width;
}
}
}
let realLeft = 0; // 计算图像的左边距
let realTop = 0; // 计算图像的上边距
for (let col = 1; col < topLeftCell.col + 1; col++) {
const left = Number(worksheet?.getColumn(col)?.width || 0) * (defaultColWidth / excelColWidth) || defaultColWidth;
if (col < topLeftCell.col) {
realLeft += left;
} else {
realLeft += left * (1 - (col - topLeftCell.col));
}
}
for (let row = 1; row < topLeftCell.row + 1; row++) {
const top = worksheet.getRow(row).height * (defaultRowHeight / excelRowHeight) || defaultRowHeight;
if (row < topLeftCell.row) {
realTop += top;
} else {
realTop += top * (1 - (row - topLeftCell.row));
}
}
images?.push({
id: imageId,
src: base64,
width: realWidth,
height: realHeight,
left: realLeft,
top: realTop,
});
});
}
// 设置单元格
worksheet.eachRow({ includeEmpty: true }, (row, rowNumber) => {
row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
if (cell.type !== 0) console.log('cell', cell.model);
const currentCell = {} as CellWithRowAndCol;
currentCell.r = Number(rowNumber) - 1;
currentCell.c = Number(colNumber) - 1;
currentCell.v = {} as Cell;
const { type, value, font, formula, result, numFmt, alignment, fill, note, dataValidation, border } = cell;
switch (type) {
case 0 /** Null */:
currentCell.v.v = null;
break;
case 1 /** Merge */: {
// 起始点
const { row: start_r, col: start_c } = handleCellToRowCol(cell.model.master);
// 当前点
const { row: end_r, col: end_c } = handleCellToRowCol(cell.address);
// 合并单元格 - 1.设置mc
const mc = { r: start_r - 1, c: start_c - 1 };
currentCell.v.mc = mc;
// 合并单元格 - 2.设置merge
const merge = { r: start_r - 1, c: start_c - 1, rs: end_r - start_r + 1, cs: end_c - start_c + 1 };
config.merge = { ...config.merge, [`${start_r - 1}_${start_c - 1}`]: merge };
break;
}
case 2 /** Number */:
currentCell.v.ht = 2; // 右对齐
currentCell.v.v = value + '';
currentCell.v.ct = { fa: numFmt || 'General', t: 'n' };
break;
case 3 /** String */:
currentCell.v.v = value + '';
currentCell.v.ct = { fa: 'General', t: 's' };
break;
case 4 /** Date */:
currentCell.v.ct = { fa: numFmt || 'General', t: 'd' };
break;
case 5 /** Hyperlink */:
currentCell.v.v = value?.text;
hyperlink[`${currentCell.r}_${currentCell.c}`] = {
linkType: 'webpage',
linkAddress: value?.hyperlink,
};
break;
case 6 /** Formula */:
currentCell.v.ht = 2; // 右对齐
currentCell.v.f = '=' + formula;
currentCell.v.v = result;
currentCell.v.ct = { fa: 'General', t: 'n' };
// 等待excel加载完成后, 手动计算公式 - ref.current?.calculateFormula();
// calcChain?.push({ r: currentCell.r, c: currentCell.c });
break;
}
// 边框
if (border && Object.keys(border).length > 0) {
if (config.borderInfo === undefined) config.borderInfo = [];
const borderKeys = Object.keys(border) || [];
if (borderKeys.includes('left')) {
config.borderInfo?.push({
rangeType: 'range',
borderType: 'border-left',
style: getKeyByValue(borderStyleMap, border.left?.style || 'thin'),
color: handleArgbToHex(border.left?.color?.argb || 'FF000000'),
range: [{ row: [rowNumber - 1, rowNumber - 1], column: [colNumber - 1, colNumber - 1] }],
});
}
if (borderKeys.includes('right')) {
config.borderInfo?.push({
rangeType: 'range',
borderType: 'border-right',
style: getKeyByValue(borderStyleMap, border.right?.style || 'thin'),
color: handleArgbToHex(border.right?.color?.argb || 'FF000000'),
range: [{ row: [rowNumber - 1, rowNumber - 1], column: [colNumber - 1, colNumber - 1] }],
});
}
if (borderKeys.includes('top')) {
config.borderInfo?.push({
rangeType: 'range',
borderType: 'border-top',
style: getKeyByValue(borderStyleMap, border.top?.style || 'thin'),
color: handleArgbToHex(border.top?.color?.argb || 'FF000000'),
range: [{ row: [rowNumber - 1, rowNumber - 1], column: [colNumber - 1, colNumber - 1] }],
});
}
if (borderKeys.includes('bottom')) {
config.borderInfo?.push({
rangeType: 'range',
borderType: 'border-bottom',
style: getKeyByValue(borderStyleMap, border.bottom?.style || 'thin'),
color: handleArgbToHex(border.bottom?.color?.argb || 'FF000000'),
range: [{ row: [rowNumber - 1, rowNumber - 1], column: [colNumber - 1, colNumber - 1] }],
});
}
// 斜线 fortune-sheet 不生效
if (borderKeys.includes('diagonal')) {
config.borderInfo?.push({
rangeType: 'range',
borderType: 'border-slash',
style: getKeyByValue(borderStyleMap, border.diagonal?.style || 'thin'),
color: handleArgbToHex(border.diagonal?.color?.argb || 'FF000000'),
range: [{ row: [rowNumber - 1, rowNumber - 1], column: [colNumber - 1, colNumber - 1] }],
});
}
}
// 字体
if (font) {
if (font.name) currentCell.v.ff = font.name;
if (font.size) currentCell.v.fs = font.size;
if (font.bold) currentCell.v.bl = 1;
if (font.italic) currentCell.v.it = 1;
if (font.underline) currentCell.v.un = 1;
if (font.strike) currentCell.v.cl = 1;
if (font.color) currentCell.v.fc = handleArgbToHex(font.color.argb);
}
// 背景色
if (fill && fill.type === 'pattern') {
currentCell.v.bg = handleArgbToHex(fill?.fgColor?.argb);
}
// 对齐方式
if (alignment) {
// 水平对齐
if (alignment.horizontal) {
switch (alignment.horizontal) {
case 'left' /* 左对齐 */:
case 'center' /* 居中对齐 */:
case 'right' /* 右对齐 */:
currentCell.v.ht = Number(getKeyByValue(horizontalAlignmentMap, alignment.horizontal));
break;
case 'fill' /* 截断 */:
currentCell.v.tb = '0';
break;
}
}
// 垂直对齐
if (alignment.vertical) {
currentCell.v.vt = Number(getKeyByValue(horizontalAlignmentMap, alignment.vertical));
}
// 自动换行
if (alignment.wrapText) {
currentCell.v.tb = getKeyByValue(wrapTextMap, alignment.wrapText);
}
// 文本角度
if (alignment.textRotation) {
currentCell.v.rt = Number(getKeyByValue(textRotationMap, alignment.textRotation));
}
}
// 注释
if (note) {
const noteVal = note?.texts ? note.texts.map((_) => _.text).join('') : note || null;
currentCell.v.ps = {
left: null,
top: null,
width: null,
height: null,
value: noteVal,
isShow: false,
};
}
// 数据验证
if (dataValidation) {
const { type, formulae, showInputMessage, prompt, showErrorMessage, operator } = dataValidation;
switch (type) {
case 'list' /** 下拉列表 */:
dataVerification[`${currentCell.r}_${currentCell.c}`] = {
type: 'dropdown',
value1: JSON.parse(formulae?.[0]),
checked: false,
hintShow: showInputMessage,
hintValue: prompt,
prohibitInput: showErrorMessage,
};
break;
case 'whole' /** 整数 */:
dataVerification[`${currentCell.r}_${currentCell.c}`] = {
type: 'number_integer',
type2: operator,
value1: formulae?.[0] + '',
value2: formulae?.[1] + '',
hintShow: showInputMessage,
hintValue: prompt,
prohibitInput: showErrorMessage,
};
break;
case 'decimal' /** 小数 */:
dataVerification[`${currentCell.r}_${currentCell.c}`] = {
type: 'number_decimal',
type2: operator,
value1: formulae?.[0] + '',
value2: formulae?.[1] + '',
hintShow: showInputMessage,
hintValue: prompt,
prohibitInput: showErrorMessage,
};
break;
case 'textLength' /** 文本长度 */:
dataVerification[`${currentCell.r}_${currentCell.c}`] = {
type: 'text_length',
type2: operator,
value1: formulae?.[0] + '',
value2: formulae?.[1] + '',
hintShow: showInputMessage,
hintValue: prompt,
prohibitInput: showErrorMessage,
};
break;
case 'date' /** 日期 */:
dataVerification[`${currentCell.r}_${currentCell.c}`] = {
type: 'date',
type2: operator,
value1: formulae?.[0] + '',
value2: formulae?.[1] + '',
hintShow: showInputMessage,
hintValue: prompt,
prohibitInput: showErrorMessage,
};
break;
}
}
celldata.push(currentCell);
});
});
// 处理单元格
const mergeKeys = Object.keys(config.merge || {});
mergeKeys.forEach((key: any) => {
const [r, c] = key.split('_');
const cell = celldata.find((item) => item.r === Number(r) && item.c === Number(c)) as CellWithRowAndCol;
cell.v.mc = config.merge?.[key];
celldata.push(cell);
});
excelData.push({
name,
row: maxRow,
column: maxCol,
hide,
zoomRatio,
frozen,
filter_select,
config,
images,
hyperlink,
calcChain,
dataVerification,
celldata,
});
});
return excelData;
} catch (e) {
console.error('报错~', e);
return false;
}
};
功能演示
其他 在线 Excel 网站 参考
Univer(Luckysheet)
目前不能导入图片,导入导出功能需要pro版本
Handsontable
图片展示需要自定义单元格实现,而且还需要pro
Jspreadsheet
需要pro
Spreadjs 目前体验最好用的, 但是收费
本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来源 小四先生的云!
评论