Skip to content

Commit 62ab768

Browse files
jqmin19jqmin19
committed
添加 excel-export
1 parent 450c232 commit 62ab768

File tree

6 files changed

+405
-2
lines changed

6 files changed

+405
-2
lines changed

‎src/cool/index.ts

+1-1
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@ export default {
1414
}
1515
},
1616
// Excel 导出模块
17-
"export",
17+
"excel-export",
1818
// crud 模块
1919
{
2020
name: "crud",

‎src/cool/modules/excel-export/components/export-btn.vue

+147
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,147 @@
1+
<template>
2+
<el-button
3+
:size="size"
4+
:type="type"
5+
:plain="plain"
6+
:round="round"
7+
:circle="circle"
8+
:icon="icon"
9+
:loading="loading"
10+
:disabled="disabled"
11+
@click="toExport"
12+
>
13+
<slot>导出</slot>
14+
</el-button>
15+
</template>
16+
17+
<script lang="ts">
18+
import { defineComponent, inject, ref } from "vue";
19+
import { currentDate, export_json_to_excel } from "../utils";
20+
21+
export default defineComponent({
22+
name: "cl-export-btn",
23+
24+
props: {
25+
filename: [Function, String],
26+
autoWidth: {
27+
type: Boolean,
28+
default: true
29+
},
30+
bookType: {
31+
type: String,
32+
default: "xlsx"
33+
},
34+
header: Array,
35+
columns: {
36+
type: Array,
37+
required: true
38+
},
39+
data: [Function, Array],
40+
maxExportLimit: Number, // 最大导出条数，不传或者小于等于0为不限制
41+
size: {
42+
type: String,
43+
default: "mini"
44+
},
45+
disabled: Boolean,
46+
type: String,
47+
plain: Boolean,
48+
round: Boolean,
49+
circle: Boolean,
50+
icon: String
51+
},
52+
53+
setup(props) {
54+
const loading = ref<boolean>(false);
55+
const crud = inject<any>("crud");
56+
57+
async function getHeader(columns: any[], fields: any[]) {
58+
return (
59+
props.header || columns.filter((e) => fields.includes(e.prop)).map((e) => e.label)
60+
);
61+
}
62+
63+
function getData() {
64+
if (typeof props.data === "function") {
65+
return props.data();
66+
} else {
67+
if (props.data) {
68+
return props.data;
69+
} else {
70+
return crud.service
71+
.page({
72+
...crud.paramsReplace(crud.params),
73+
maxExportLimit: props.maxExportLimit,
74+
isExport: true
75+
})
76+
.then((res: any) => res.list)
77+
.catch((err: string) => {
78+
console.error(err);
79+
return null;
80+
});
81+
}
82+
}
83+
}
84+
85+
async function getFileName() {
86+
if (typeof props.filename === "function") {
87+
return await props?.filename();
88+
} else {
89+
const { year, month, day, hour, minu, sec } = currentDate();
90+
return props.filename || `报表（${year}-${month}-${day} ${hour}_${minu}_${sec}）`;
91+
}
92+
}
93+
94+
async function toExport() {
95+
if (!props.columns) {
96+
return console.error("cl-export-btn.columns 不能为空！");
97+
}
98+
99+
// 加载
100+
loading.value = true;
101+
102+
// 表格列
103+
const columns = props.columns.filter(
104+
(e: any) =>
105+
!["selection", "expand", "index"].includes(e.type) &&
106+
!(e.filterExport || e["filter-export"])
107+
);
108+
109+
// 字段
110+
const fields = columns.map((e: any) => e.prop).filter(Boolean);
111+
112+
// 表头
113+
let header = await getHeader(columns, fields);
114+
115+
// 数据
116+
let data = await getData();
117+
118+
if (!data) {
119+
loading.value = false;
120+
return console.error("导出数据异常");
121+
}
122+
123+
// 文件名
124+
let filename = await getFileName();
125+
126+
// 过滤
127+
data = data.map((d: any) => fields.map((f) => d[f]));
128+
129+
// 导出 excel
130+
export_json_to_excel({
131+
header,
132+
data,
133+
filename,
134+
autoWidth: props.autoWidth,
135+
bookType: props.bookType
136+
});
137+
138+
loading.value = false;
139+
}
140+
141+
return {
142+
loading,
143+
toExport
144+
};
145+
}
146+
});
147+
</script>

‎src/cool/modules/excel-export/index.ts

+9
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
import ExportBtn from "./components/export-btn.vue";
2+
3+
export { ExportBtn };
4+
5+
export default {
6+
components: {
7+
ExportBtn
8+
}
9+
};

‎src/cool/modules/excel-export/utils/export2excel.ts

+232
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,232 @@
1+
/* eslint-disable */
2+
// @ts-nocheck
3+
import { saveAs } from 'file-saver';
4+
import XLSX from 'xlsx';
5+
6+
function generateArray(table) {
7+
var out = [];
8+
var rows = table.querySelectorAll('tr');
9+
var ranges = [];
10+
for (var R = 0; R < rows.length; ++R) {
11+
var outRow = [];
12+
var row = rows[R];
13+
var columns = row.querySelectorAll('td');
14+
for (var C = 0; C < columns.length; ++C) {
15+
var cell = columns[C];
16+
var colspan = cell.getAttribute('colspan');
17+
var rowspan = cell.getAttribute('rowspan');
18+
var cellValue = cell.innerText;
19+
if (cellValue !== '' && cellValue == +cellValue) cellValue = +cellValue;
20+
21+
//Skip ranges
22+
ranges.forEach(function(range) {
23+
if (
24+
R >= range.s.r &&
25+
R <= range.e.r &&
26+
outRow.length >= range.s.c &&
27+
outRow.length <= range.e.c
28+
) {
29+
for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
30+
}
31+
});
32+
33+
//Handle Row Span
34+
if (rowspan || colspan) {
35+
rowspan = rowspan || 1;
36+
colspan = colspan || 1;
37+
ranges.push({
38+
s: {
39+
r: R,
40+
c: outRow.length
41+
},
42+
e: {
43+
r: R + rowspan - 1,
44+
c: outRow.length + colspan - 1
45+
}
46+
});
47+
}
48+
49+
//Handle Value
50+
outRow.push(cellValue !== '' ? cellValue : null);
51+
52+
//Handle Colspan
53+
if (colspan) for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
54+
}
55+
out.push(outRow);
56+
}
57+
return [out, ranges];
58+
}
59+
60+
function datenum(v, date1904) {
61+
if (date1904) v += 1462;
62+
var epoch = Date.parse(v);
63+
return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
64+
}
65+
66+
function sheet_from_array_of_arrays(data, opts) {
67+
var ws = {};
68+
var range = {
69+
s: {
70+
c: 10000000,
71+
r: 10000000
72+
},
73+
e: {
74+
c: 0,
75+
r: 0
76+
}
77+
};
78+
for (var R = 0; R != data.length; ++R) {
79+
for (var C = 0; C != data[R].length; ++C) {
80+
if (range.s.r > R) range.s.r = R;
81+
if (range.s.c > C) range.s.c = C;
82+
if (range.e.r < R) range.e.r = R;
83+
if (range.e.c < C) range.e.c = C;
84+
var cell = {
85+
v: data[R][C]
86+
};
87+
if (cell.v == null) continue;
88+
var cell_ref = XLSX.utils.encode_cell({
89+
c: C,
90+
r: R
91+
});
92+
93+
if (typeof cell.v === 'number') cell.t = 'n';
94+
else if (typeof cell.v === 'boolean') cell.t = 'b';
95+
else if (cell.v instanceof Date) {
96+
cell.t = 'n';
97+
cell.z = XLSX.SSF._table[14];
98+
cell.v = datenum(cell.v);
99+
} else cell.t = 's';
100+
101+
ws[cell_ref] = cell;
102+
}
103+
}
104+
if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
105+
return ws;
106+
}
107+
108+
function Workbook() {
109+
if (!(this instanceof Workbook)) return new Workbook();
110+
this.SheetNames = [];
111+
this.Sheets = {};
112+
}
113+
114+
function s2ab(s) {
115+
var buf = new ArrayBuffer(s.length);
116+
var view = new Uint8Array(buf);
117+
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
118+
return buf;
119+
}
120+
121+
export function export_table_to_excel(id) {
122+
var theTable = document.getElementById(id);
123+
var oo = generateArray(theTable);
124+
var ranges = oo[1];
125+
126+
/* original data */
127+
var data = oo[0];
128+
var ws_name = 'SheetJS';
129+
130+
var wb = new Workbook(),
131+
ws = sheet_from_array_of_arrays(data);
132+
133+
/* add ranges to worksheet */
134+
// ws['!cols'] = ['apple', 'banan'];
135+
ws['!merges'] = ranges;
136+
137+
/* add worksheet to workbook */
138+
wb.SheetNames.push(ws_name);
139+
wb.Sheets[ws_name] = ws;
140+
141+
var wbout = XLSX.write(wb, {
142+
bookType: 'xlsx',
143+
bookSST: false,
144+
type: 'binary'
145+
});
146+
147+
saveAs(
148+
new Blob([s2ab(wbout)], {
149+
type: 'application/octet-stream'
150+
}),
151+
'test.xlsx'
152+
);
153+
}
154+
155+
export function export_json_to_excel({
156+
multiHeader = [],
157+
header,
158+
data,
159+
filename,
160+
merges = [],
161+
autoWidth = true,
162+
bookType = 'xlsx'
163+
} = {}) {
164+
/* original data */
165+
filename = filename || 'excel-list';
166+
data = [...data];
167+
data.unshift(header);
168+
169+
for (let i = multiHeader.length - 1; i > -1; i--) {
170+
data.unshift(multiHeader[i]);
171+
}
172+
173+
var ws_name = 'SheetJS';
174+
var wb = new Workbook(),
175+
ws = sheet_from_array_of_arrays(data);
176+
177+
if (merges.length > 0) {
178+
if (!ws['!merges']) ws['!merges'] = [];
179+
merges.forEach(item => {
180+
ws['!merges'].push(XLSX.utils.decode_range(item));
181+
});
182+
}
183+
184+
if (autoWidth) {
185+
/*设置worksheet每列的最大宽度*/
186+
const colWidth = data.map(row =>
187+
row.map(val => {
188+
/*先判断是否为null/undefined*/
189+
if (val == null) {
190+
return {
191+
wch: 10
192+
};
193+
} else if (val.toString().charCodeAt(0) > 255) {
194+
/*再判断是否为中文*/
195+
return {
196+
wch: val.toString().length * 2
197+
};
198+
} else {
199+
return {
200+
wch: val.toString().length
201+
};
202+
}
203+
})
204+
);
205+
/*以第一行为初始值*/
206+
let result = colWidth[0];
207+
for (let i = 1; i < colWidth.length; i++) {
208+
for (let j = 0; j < colWidth[i].length; j++) {
209+
if (result[j]['wch'] < colWidth[i][j]['wch']) {
210+
result[j]['wch'] = colWidth[i][j]['wch'];
211+
}
212+
}
213+
}
214+
ws['!cols'] = result;
215+
}
216+
217+
/* add worksheet to workbook */
218+
wb.SheetNames.push(ws_name);
219+
wb.Sheets[ws_name] = ws;
220+
221+
var wbout = XLSX.write(wb, {
222+
bookType: bookType,
223+
bookSST: false,
224+
type: 'binary'
225+
});
226+
saveAs(
227+
new Blob([s2ab(wbout)], {
228+
type: 'application/octet-stream'
229+
}),
230+
`${filename}.${bookType}`
231+
);
232+
}

0 commit comments

Comments
 (0)