logo

前端实现复杂表格导入导出

通过前端技术实现复杂excel表格(合并单元格,这里没有做表格的数据类型)的导入导出,下面代码还有简单表格的导出。
以下代码,cv即可用。主要是vue3以及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

// 表格导入导出
// 引入兼容vue2兼容vue3的插件
import { reactive, toRefs } from "@vue/composition-api";

import * as XLSX from "xlsx";

// 原来是xlsx-style,后来发现有个问题,便自己拉了一个库并且npm上去了
import XLSXStyle from 'xlsx-style-revision'

import * as zipObject from "lodash/zipObject";

import { Message } from "element-ui";


// 定义数据类型
interface WorkBookModel {
name: string
sheet: any
[propname: string]: any
}
interface ExcelModel {
id: string
project: string
jobNumber: string
materialType: string
material: string
module: string
unit: string
totalDesignQuantity: string
consumedQuantity: string
lossFactorCoefficient: string
totalConsumption: string
responsibleCostPrice: string
controlPrice: string
actualQuantityConsumed: string
purchasedPrice: string
actualConsumptionAmount: string
openTiredPurchaseQuantity: string
bookStockQuantity: string
inventoryCountQuantity: string
remainingPurchaseQuantity: string
quantityWith: string
quantityWithout: string
amountWithDepletion: string
amountWithoutLoss: string
biddingUnitPrice: string
purchaseResponsibilityCostPrice: string
differencePurchaseBidPrice: string
}

export const useTableImportOutput = (useGetTableData) => {
const tableImportOutput = reactive({
excelJsonData: [],
loading: false,
excelTime: [],
excelProjectName: []
});
const jsonKeys: string[] = [
"id",
"project",
"jobNumber",
"materialType",
"material",
"module",
"unit",
"totalDesignQuantity",
"consumedQuantity",
"lossFactorCoefficient",
"totalConsumption",
"responsibleCostPrice",
"controlPrice",
"actualQuantityConsumed",
"purchasedPrice",
"actualConsumptionAmount",
"openTiredPurchaseQuantity",
"bookStockQuantity",
"inventoryCountQuantity",
"remainingPurchaseQuantity",
"quantityWith",
"quantityWithout",
"amountWithDepletion",
"amountWithoutLoss",
"biddingUnitPrice",
"purchaseResponsibilityCostPrice",
"differencePurchaseBidPrice"
];

// 导入的函数
const handleGetExcelData = file => {
return new Promise(resolve => {
// 创建一个可读的文件对象
const render = new FileReader();
render.onload = e => {
const eResult = e.target?.result;
// 读文件
const workBook = XLSX.read(eResult, {
type: "binary"
});
// 定义一个结果
const result: WorkBookModel[] = [];
// 遍历数据中的sheet表
workBook.SheetNames.forEach(v => {
result.push({
name: v,
// 将每一个表的数据json化
sheet: XLSX.utils.sheet_to_json(
workBook.Sheets[v]
)
});
});
resolve(result);
};
render.readAsBinaryString(file.raw);
});
};

// 处理json对象数据,这里只有一个sheet,如果有多个的话需要循环处理
const parseJsonResult = jsonData => {
// 获取第一组数据
const headExcelData: {} = jsonData[0].sheet[0]
// 获取第一行的日期
const excelTime: string[] = JSON.parse(JSON.stringify(Object.keys(headExcelData)[0].match(/\d+/g)))
// 获取项目单位
const excelProjectName: string[] = Object.values(headExcelData)
let header: object = {}
if (Array.isArray(excelTime) && Array.isArray(excelProjectName)) {
header = JSON.parse(JSON.stringify({
year: excelTime[0],
quarter: excelTime[1],
projectName: excelProjectName[0].split(':')[1]
}))
}
// 去除表头
const _jsonData = jsonData[0].sheet.slice(4, jsonData[0].sheet.length);
// 去除表头后的数组长度为0的时候,提醒错误
if (_jsonData.length <= 0)
return Message.error("导入数据为空,请重新导入!");
// 否则正确处理
const _result: ExcelModel[] = [];
// 遍历整个sheet,获取整行数据
_jsonData.forEach(v => {
// 获取一行单元格数据对象
const objectValue = Object.values(v);
// 使用loadsh函数一对一写入
_result.push(zipObject(jsonKeys, objectValue));
});
return {
header, _result
}
};

// 导入
const handleImportData = file => {
tableImportOutput.loading = true
handleGetExcelData(file).then(jsonData => {
tableImportOutput.excelJsonData = parseJsonResult(jsonData);
useGetTableData.tableData.value = tableImportOutput.excelJsonData._result
useGetTableData.headerData.value = tableImportOutput.excelJsonData.header
console.log('useGetTableData', useGetTableData);
tableImportOutput.loading = false
});
};

// 导出excel函数,导出没有合并单元格的数据,数据的key就是表格的表头
const handleExportExcel: Function = exportJson => {
// 新建一个sheet
const workSheet = XLSX.utils.json_to_sheet(exportJson);
// 设置sheet单元格的宽度
workSheet["!cols"] = [{ wpx: 220 }];

// 新建sheetBook
const workBook = XLSX.utils.book_new();
// 将sheet写入book中
XLSX.utils.book_append_sheet(
workBook,
workSheet,
"附表-项目经济活动分析表"
);
// 将book写入excel中
XLSX.writeFile(workBook, "物资表.xlsx");
};

// 导出带有样式的excel
const handleSheet2Blob = (sheet, sheetName) => {
sheetName = sheetName || 'sheet1';
const workbook = {
SheetNames: [sheetName],
Sheets: {},
};
// 生成excel的配置项
workbook.Sheets[sheetName] = sheet;
// 下载这里一定要用 xlsx-style 的write() 方法才可以使导出excel表格带样式
const wbout = XLSXStyle.write(workbook, { type: 'buffer' });
// 字符串转ArrayBuffer
const blob = new Blob([wbout], {
type: 'application/octet-stream',
});

return blob;
}

// 下载导出的excel
const handleDownloadDialog = (url, saveName) => {
// 创建blob地址
if (typeof url == 'object' && url instanceof Blob) {
url = URL.createObjectURL(url);
}
const aLink = document.createElement('a');
aLink.href = url;
aLink.download = saveName || '';
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);
}

// 导出合并单元格的excel函数
const handleExportMoreExcel = exportJson => {
// 先定义一个表头
const headData: (string | number | null)[][] = [
['(开累/当期)截止 年 季(月)度分工号物资量价差表(含已消耗应结未结物资及对应设计量)', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null],
['单位:××项目经理部', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, '单位:元', null, null, null, null, null, null],
['序号', '项目名称', '工号', '材料类别', '材料名称', '规格型号', '单位', '总设计数量', '责任成本', null, null, null, null, '实际消耗成本', null, null, '开累采购数量', '账面库存数量(计算应结未结后)', '库存盘点数量', '剩余采购数量', '量差', null, null, null, '价差', null, null],
[null, null, null, null, null, null, null, null, '应耗数量', '损耗系数', '应耗合计', '责任成本价', '控制金额', '实耗数量(含应结未结)', '采购均价', '实耗金额', null, null, null, null, '含损耗节超数量', '不含损耗节超数量', '含损耗量差金额', '不含损耗量差金额', '投标单价', '采购与责任成本价差', '采购与投标单价价差'],
[null, null, null, null, null, null, null, 0, 1, 2, '3=1x(1+2)', 4, '5=3x4', 6, 7, '8=6x7', 9, '10=9-6', 11, '12=0-6-11', '13=3-6', '14=1-6', '15=7x13', '16=7x14', 17, '18=(7-4)x1', '19=(7-17)x1']
]
// 将获取的数据放入表头的数据中
exportJson.forEach(v => {
const values: string[] = Object.values(v)
headData.push(values)
});
// 创建一个sheet
const workSheet = XLSX.utils.aoa_to_sheet(headData)
// 合并sheet的单元格
workSheet["!merges"] = [
// 0行
{ s: { c: 0, r: 0 }, e: { c: 26, r: 0 } },
// 1行
{ s: { c: 0, r: 1 }, e: { c: 19, r: 1 } },
{ s: { c: 20, r: 1 }, e: { c: 26, r: 1 } },
// 2行
{ s: { c: 0, r: 2 }, e: { c: 0, r: 4 } },
{ s: { c: 1, r: 2 }, e: { c: 1, r: 4 } },
{ s: { c: 2, r: 2 }, e: { c: 2, r: 4 } },
{ s: { c: 3, r: 2 }, e: { c: 3, r: 4 } },
{ s: { c: 4, r: 2 }, e: { c: 4, r: 4 } },
{ s: { c: 5, r: 2 }, e: { c: 5, r: 4 } },
{ s: { c: 6, r: 2 }, e: { c: 6, r: 4 } },
{ s: { c: 7, r: 2 }, e: { c: 7, r: 3 } },
{ s: { c: 8, r: 2 }, e: { c: 12, r: 2 } },
{ s: { c: 13, r: 2 }, e: { c: 15, r: 2 } },
{ s: { c: 16, r: 2 }, e: { c: 16, r: 3 } },
{ s: { c: 17, r: 2 }, e: { c: 17, r: 3 } },
{ s: { c: 18, r: 2 }, e: { c: 18, r: 3 } },
{ s: { c: 19, r: 2 }, e: { c: 19, r: 3 } },
{ s: { c: 20, r: 2 }, e: { c: 23, r: 2 } },
{ s: { c: 20, r: 2 }, e: { c: 23, r: 2 } },
{ s: { c: 24, r: 2 }, e: { c: 26, r: 2 } },
]
for (const key in workSheet) {
console.log('workSheet', workSheet[key]);
if (Object.prototype.toString.call(workSheet[key]).slice(8, -1) === 'Object') {
workSheet[key].s = {
font: {
name: '仿宋',
sz: 10,
color: {
auto: 1,
},
},
alignment: {
horizontal: 'center',
vertical: 'center',
wrapText: true,
}
}
}
if (key === 'A2' || key === 'U2') {
workSheet[key].s = {
font: {
name: '仿宋',
sz: 10,
color: {
auto: 1,
},
},
alignment: {
horizontal: 'left',
vertical: 'left',
wrapText: true,
}
}
}
}
handleDownloadDialog(handleSheet2Blob(workSheet, '附表-项目经济活动分析表'), '物资表.xlsx')
};

// 导出
const handleDownload: Function = () => {
console.log("导出");
// 这里是导出的示例数据
const exportJson = [
{
id: 1,
project: "项目1",
jobNumber: "主体结构",
materialType: "主材类",
material: "钢筋",
module: "HPB-001",
unit: "吨",
totalDesignQuantity: 1,
consumedQuantity: 1,
lossFactorCoefficient: 1,
totalConsumption: 1,
responsibleCostPrice: 1,
controlPrice: 1,
actualQuantityConsumed: 1,
purchasedPrice: 1,
actualConsumptionAmount: 1,
openTiredPurchaseQuantity: 1,
bookStockQuantity: 1,
inventoryCountQuantity: 1,
remainingPurchaseQuantity: 1,
quantityWith: 1,
quantityWithout: 1,
amountWithDepletion: 1,
amountWithoutLoss: 1,
biddingUnitPrice: 1,
purchaseResponsibilityCostPrice: 1,
differencePurchaseBidPrice: 1
},
];
handleExportMoreExcel(exportJson);
};

return {
...toRefs(tableImportOutput),
handleImportData,
handleDownload
};
};