JavaWeb后端解析前端传输的excel文件(SpringBoot+Vue3)

一、前端

前端拿Vue3+ElementPlus做上传示例

<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>

二、后端

1. 导入依赖

  <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.1.2</version>
  </dependency>

2. controller接收file

    @PostMapping("/yourControllerApi")
    public AjaxResult importData(@RequestBody MultipartFile file){
        return stockMgntService.importData(file);
    }

3. service具体实现

@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使用方法。