-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdemo.html
136 lines (131 loc) · 3.86 KB
/
demo.html
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
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Demo</title>
<script src="node_modules/xlsx/dist/xlsx.core.min.js"></script>
</head>
<body>
<table>
<thead>
<tr>
<th>姓名</th>
<th>年龄</th>
<th>成绩</th>
<th>错题率</th>
</tr>
</thead>
<tbody>
<tr>
<td>张三</td>
<td>19</td>
<td>85</td>
<td>10%</td>
</tr>
<tr>
<td>李四</td>
<td>20</td>
<td>76</td>
<td>23%</td>
</tr>
</tbody>
</table>
<input type="submit" value="导出成EXCEL文件" onClick="exportJsonToExcel();">
<style>
table, table th, table td {
border: 1px solid black;
}
input {
margin-top: 20px;
}
</style>
<script type="text/javascript">
// ref: https://juejin.cn/post/6989437152341786655
function exportFile(sheetData, fileName) {
// 将由对象组成的数组转化成sheet
const sheet = XLSX.utils.json_to_sheet(sheetData)
// 百分数和数字更改为数字类型
Object.keys(sheet).forEach((key) => {
if (sheet[key].v) {
const val = sheet[key].v
if (!isNaN(val)) {
sheet[key].t = 'n'
}
if (val.lastIndexOf('%') === val.length - 1) {
sheet[key].t = 'n'
sheet[key].z = '0.00%'
sheet[key].v = Number(val.substring(0, val.length - 1)) / 100
}
}
})
// 创建虚拟的workbook
const wb = XLSX.utils.book_new()
// 把sheet添加到workbook中
XLSX.utils.book_append_sheet(wb, sheet, fileName)
const workbookBlob = workbook2blob(wb)
openDownload(workbookBlob, `${fileName}.xls`)
}
function openDownload(blob, fileName) {
if (typeof blob === 'object' && blob instanceof Blob) {
blob = URL.createObjectURL(blob) // 创建blob地址
}
const aLink = document.createElement('a')
aLink.href = blob
// HTML5新增的属性，指定保存文件名，可以不要后缀，注意，有时候 file:///模式下不会生效
aLink.download = fileName || ''
let event
if (window.MouseEvent) {
event = new MouseEvent('click')
}
// 移动端
else {
event = document.createEvent('MouseEvents')
event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null)
}
aLink.dispatchEvent(event)
}
function workbook2blob(workbook) {
// 生成excel的配置项
const wopts = {
// 要生成的文件类型
bookType: 'xlsx',
// 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
bookSST: false,
type: 'binary',
}
const wbout = XLSX.write(workbook, wopts)
const blob = new Blob([s2ab(wbout)], {
type: 'application/octet-stream',
})
return blob
}
// 将字符串转ArrayBuffer
function s2ab(s) {
const buf = new ArrayBuffer(s.length)
const view = new Uint8Array(buf)
for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xff
return buf
}
function exportJsonToExcel() {
let sheetData = [
{
name: '张三',
age: '19',
score: '85',
inaccuracy_rate: '10%'
},
{
name: '李四',
age: '20',
score: '76',
inaccuracy_rate: '23%'
}
];
// 如果想标题栏是中文，可尝试将 `name` `age` 直接换成中文
exportFile(sheetData, '成绩统计');
}
</script>
</body>
</html>