一、前端
前端拿Vue3+ElementPlus做上传示例
language-html1
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
| <template>
<el-button type="primary" @click="updateData" size="small" plain>数据上传</el-button>
<el-dialog :title="upload.title" v-model="upload.open" width="400px" align-center append-to-body>
<el-upload
ref="uploadRef"
:limit="1"
accept=".xlsx, .xls"
:headers="upload.headers"
:action="upload.url"
:disabled="upload.isUploading"
:on-progress="handleFileUploadProgress"
:on-success="handleFileSuccess"
:auto-upload="false"
drag
>
<el-icon class="el-icon--upload">
<upload-filled/>
</el-icon>
<div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
<template #tip>
<div class="el-upload__tip text-center">
<span>仅允许导入xls、xlsx格式文件。</span>
</div>
</template>
</el-upload>
<template #footer>
<div class="dialog-footer">
<el-button type="primary" @click="submitFileForm">确 定</el-button>
<el-button @click="upload.open = false">取 消</el-button>
</div>
</template>
</el-dialog>
</template>
<script setup>
const upload = reactive({
// 是否显示弹出层（用户导入）
open: false,
// 弹出层标题（用户导入）
title: "标题",
// 是否禁用上传
isUploading: false,
// 是否更新已经存在的用户数据
updateSupport: 0,
// 设置上传的请求头部
headers: {
Authorization: "Bearer " + getToken()},
// 上传的地址
url: import.meta.env.VITE_APP_BASE_API + "/yourControllerApi"
});
//数据上传
const updateData = () => {
upload.title = "数据上传";
upload.open = true;
}
//文件上传中处理
const handleFileUploadProgress = (event, file, fileList) => {
upload.isUploading = true;
console.log(upload.url)
};
//文件上传成功处理
const handleFileSuccess = (rp, file, fileList) => {
//这里的rp就是后端controller的响应数据
console.log(rp)
upload.open = false;
upload.isUploading = false;
proxy.$refs["uploadRef"].handleRemove(file);
};
</script>
|
二、后端
- 导入依赖
language-xml1
2
3
4
5
| <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
|
- controller接收file
language-java1
2
3
4
5
| @PostMapping("/yourControllerApi")
public AjaxResult importData(@RequestBody MultipartFile file){
return stockMgntService.importData(file);
}
|
- service具体实现
language-java1
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
| @Service
public class StockMgntServiceImpl implements StockMgntService {
@Override
public AjaxResult importData(MultipartFile file) {
try(InputStream is = file.getInputStream();) {
Workbook workbook = WorkbookFactory.create(is);
int numberOfSheets = workbook.getNumberOfSheets();
if (numberOfSheets != 1){
//要处理多个sheet，循环就可以
System.out.println("只允许有1个Sheet");
return null;
}
//取得第一个sheet
Sheet sheet = workbook.getSheetAt(0);
//获取最后一行的索引，但通常会比预想的要多出几行。
int lastRowNum = sheet.getLastRowNum();
//循环读取每一行
for (int rowNum = 0; rowNum < lastRowNum; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if (rowData != null){
//可以使用rowData.getLastCellNum()获取最后一列的索引，这里只有6行，是假设现在的excel模板是固定的6行
for(int cellNum = 0; cellNum < 6; ++cellNum){
Cell cell = rowData.getCell(cellNum);
if (cell == null){
continue;}
cell.setCellType(CellType.STRING);
if (!cell.getStringCellValue().isEmpty()){
System.out.println(cell.getStringCellValue());
}
/*
利用这个方法可以更好的将所有数据以String获取到
Cell cell = productCodeRow.getCell(cellNum , Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
String cellValue = dataFormatter.formatCellValue(cell );
*/
}
}
}
}catch (Exception e) {
System.out.println(e.getMessage());
throw new RuntimeException();
}
}
}
|
三、结语
这样的方式只能处理简单的excel，对于结构更复杂的excel，需要其他工具utils结合。
以及可以结合SpringBoot有更好的poi使用方法。
