【前后前】導入Excel文件閉環模型：Vue3前端上傳Excel文件，【Java后端接收、解析、返回數據】，Vue3前端接收展示數據

一、Vue3前端上傳（導入）Excel文件

ReagentInDialog.vue

<script setup lang="ts" name="ReagentInDialog">// 導入
const onImportClick = () => {// 模擬點擊元素if (fileInputRef.value) {// 重置以允許重復選擇相同文件fileInputRef.value.value = "";fileInputRef.value.click();}
};// 點擊【導入】觸發
const handleImport= async (e: Event) => {let dataList = [];try {tableLoading.value = true;// 獲取文件對象const input = e.target as HTMLInputElement;if (!input.files?.length) return;const file = input.files[0];// 鍵值列名映射表const keyColMap: Record<string, string> = {試劑編號: "reagentNo",試劑名稱: "reagentName",規格型號: "reagentSpec",單位: "reagentUnit",批號: "batchNo",有效期至: "validityDate",入庫數量: "amount",入庫金額: "total"};// 導入文件，由前端解析文件，獲取數據// dataList = await importExcelFileByClient(file, keyColMap);// 導入文件，由后端解析文件，獲取數據dataList = await importExcelFileByServer(file, keyColMap);} finally {tableLoading.value = false;}
}</script><template><el-button type="primary" plain @click="onImportClick">導入</el-button><!-- 文件輸入元素，不顯示，通過點擊按鈕【導入】執行 onImportClick，模擬點擊該元素，從而觸發 handleImport事件 --><inputref="fileInputRef"type="file"accept=".xls, .xlsx"style="display: none"@change="handleImport" /></template>

excelUtils.ts

import { formatJson } from "@/utils/formatter";
import { convertFileSize } from "@/utils/pubUtils";
import { ElMessage } from "element-plus";
import * as xlsx from "xlsx";
import { uploadFileService } from "@/api/upload";/*** 從Excel文件導入數據，由后端解析文件，獲取數據* @param file 導入文件* @param colKeyMap 列名鍵值映射，key --> value，如：excel中列名為【樣品編號】，其鍵值設置對應為【sampleNo】* @returns 列表數據*/
export async function importExcelFileByServer(file: any, keyColMap?: Record<string, string>) {// 定義及初始化需要返回的列表數據let dataList: any[] = [];// 文件校驗// 校驗文件名后綴if (!/\.(xls|xlsx)$/.test(file.name)) {ElMessage.warning("請導入excel文件！");return dataList;}// 校驗文件格式// application/vnd.ms-excel 為 .xls文件// application/vnd.openxmlformats-officedocument.spreadsheetml.sheet 為 .xlsx文件else if (file.type !== "application/vnd.ms-excel" &&file.type !== "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") {ElMessage.warning("excel文件已損壞，請檢查！");return dataList;}// 校驗文件大小else if (convertFileSize(file.size, "B", "MB") > 1) {ElMessage.warning("文件大小不能超過1MB！");return dataList;}// 文件讀取const fileReader = new FileReader();// 以二進制的方式讀取文件內容fileReader.readAsArrayBuffer(file);// 等待打開加載完成文件，其實就是執行 fileReader.onloadend = () => {}，返回 true 表示成功，false 表示失敗const result = await loadedFile(fileReader);if (result) {// 通過 FormData 對象實現文件上傳const formData = new FormData();// 將文件對象 file 添加到 formData 對象中，uploadFile 需要與后端接口中接收文件的參數名一致formData.append("uploadFile", file);// 發送請求，上傳文件到后端服務器，后端接收文件，進行解析，并返回數據集const result = await uploadFileService(formData);dataList = keyColMap ? formatJson(result.data, keyColMap) : result.data;}// 返回列表數據return dataList;
}

upload.ts

import request from "@/utils/request";/*** 上傳文件，后端解析Excel文件，返回解析后的列表數據* @param file 文件，表單數據* @returns 列表數據*/
export const uploadFileService = (file: FormData) => {return request.post("/upload/parseExcelFile", file, {// 上傳文件，需設置 headers 信息，將"Content-Type"設置為"multipart/form-data"headers: {"Content-Type": "multipart/form-data"}});
};

二、Java后端接收、解析、返回數據

UploadController.java

package com.weiyu.controller;import com.weiyu.utils.ExcelUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;import java.util.List;
import java.util.Map;/*** 上傳 Controller*/
@RestController
@RequestMapping("/upload")
@Slf4j
public class UploadController {// 上傳文件，后端解析Excel文件，返回解析后的列表數據// 因為前端是用 "Content-Type": "multipart/form-data" 的方式發送的請求，這里就不能用 @RequestBody，而是用 MultipartFile// 并且形參名稱 uploadFile 需要與前端定義的保持一致@PostMapping("/parseExcelFile")public ResponseEntity<?> uploadAndParseExcelFile(MultipartFile uploadFile) {log.info("【上傳文件】，解析Excel文件，/upload/parseExcelFile，uploadFile = {}", uploadFile);try {// 驗證文件if (uploadFile.isEmpty()) {return ResponseEntity.badRequest().body("文件為空");}// 驗證文件類型String contentType = uploadFile.getContentType();if (contentType == null || (!contentType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") && !contentType.equals("application/vnd.ms-excel"))) {return ResponseEntity.badRequest().body("僅支持 Excel 文件 (.xlsx, .xls)");}// 解析 ExcelList<Map<String, Object>> data = ExcelUtils.parseExcel(uploadFile);// 返回解析結果return ResponseEntity.ok(data);} catch (Exception e) {return ResponseEntity.internalServerError().body("解析失敗: " + e.getMessage());}}
}

三、Vue3前端接收展示數據

1、正常發送請求數據

2、正常接收響應數據

3、解析出錯

四、后端修改方案

UploadController.java

package com.weiyu.controller;import com.weiyu.pojo.Result;
import com.weiyu.utils.ExcelUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.server.ResponseStatusException;import java.util.List;
import java.util.Map;/*** 上傳 Controller*/
@RestController
@RequestMapping("/upload")
@Slf4j
public class UploadController {// 上傳文件，后端解析Excel文件，返回解析后的列表數據// 因為前端是用 "Content-Type": "multipart/form-data" 的方式發送的請求，這里就不能用 @RequestBody，而是用 MultipartFile// 并且形參名稱 uploadFile 需要與前端定義的保持一致@PostMapping("/parseExcelFile")@ResponseBody // 直接序列化返回值，使用 Result<List<Map<String, Object>>> 替換 ResponseEntity<?>public Result<List<Map<String, Object>>> uploadAndParseExcelFile(MultipartFile uploadFile) {log.info("【上傳文件】，解析Excel文件，/upload/parseExcelFile，uploadFile = {}", uploadFile);try {// 驗證文件
//            if (uploadFile.isEmpty()) {
//                return ResponseEntity.badRequest().body("文件為空");
//            }// 驗證文件類型
//            String contentType = uploadFile.getContentType();
//            if (contentType == null || (!contentType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") && !contentType.equals("application/vnd.ms-excel"))) {
//                return ResponseEntity.badRequest().body("僅支持 Excel 文件 (.xlsx, .xls)");
//            }// 解析 ExcelList<Map<String, Object>> data = ExcelUtils.parseExcel(uploadFile);// 返回解析結果
//            return ResponseEntity.ok(data);return Result.success(data);} catch (Exception e) {
//            return ResponseEntity.internalServerError().body("解析失敗: " + e.getMessage());throw new ResponseStatusException(HttpStatus.INTERNAL_SERVER_ERROR, "解析失敗: " + e.getMessage(), e);}}
}

前端導入效果

五、后端完善方案?

UploadController.java

package com.weiyu.controller;import com.weiyu.pojo.Result;
import com.weiyu.service.UploadService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;import java.util.List;
import java.util.Map;/*** 上傳 Controller*/
@RestController
@RequestMapping("/upload")
@Slf4j
public class UploadController {@Autowiredprivate UploadService uploadService;// 上傳文件，后端解析Excel文件，返回解析后的列表數據// 因為前端是用 "Content-Type": "multipart/form-data" 的方式發送的請求，這里就不能用 @RequestBody，而是用 MultipartFile// 并且形參名稱 uploadFile 需要與前端定義的保持一致@PostMapping("/parseExcelFile")// @ResponseBody // 直接序列化返回值，使用 Result<List<Map<String, Object>>> 替換 ResponseEntity<?>public Result<List<Map<String, Object>>> uploadAndParseExcelFile(MultipartFile uploadFile) {log.info("【上傳文件】，解析Excel文件，/upload/parseExcelFile，uploadFile = {}", uploadFile);List<Map<String, Object>> data = uploadService.parseExcelFile(uploadFile);return Result.success(data);}
}

UploadServiceImpl.java

package com.weiyu.service.impl;import com.weiyu.service.UploadService;
import com.weiyu.utils.ExcelUtils;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;import java.io.IOException;
import java.util.List;
import java.util.Map;/*** 上傳 Service 接口實現*/
@Service
public class UploadServiceImpl implements UploadService {// 解析 Excel 文件@Overridepublic List<Map<String, Object>> parseExcelFile(MultipartFile uploadFile) {try {// 解析 Excelreturn ExcelUtils.parseExcel(uploadFile);} catch (IOException e) {throw new RuntimeException(e);}}
}

excel文件處理工具類 ExcelUtils.java?

package com.weiyu.utils;import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.time.ZoneId;
import java.util.*;/*** excel文件處理工具類*/
@Slf4j
@Component //通過@Component注解，將該工具類交給ICO容器管理，需要使用的時候不需要new，直接@Autowired注入即可
public class ExcelUtils {public static List<Map<String, Object>> parseExcel(MultipartFile file) throws IOException {try (InputStream inputStream = file.getInputStream()) {Workbook workbook = WorkbookFactory.create(inputStream);Sheet sheet = workbook.getSheetAt(0);// 獲取表頭行Row headerRow = sheet.getRow(0);if (headerRow == null) {return Collections.emptyList();}// 處理表頭（處理重復列名）List<String> headers = processHeaders(headerRow);// 解析數據行List<Map<String, Object>> data = new ArrayList<>();for (int i = 1; i <= sheet.getLastRowNum(); i++) {Row row = sheet.getRow(i);if (row == null) continue;Map<String, Object> rowData = parseRow(row, headers);if (!rowData.isEmpty()) {data.add(rowData);}}return data;}}private static List<String> processHeaders(Row headerRow) {List<String> headers = new ArrayList<>();Map<String, Integer> headerCount = new HashMap<>();for (Cell cell : headerRow) {String header = getCellValueAsString(cell).trim();// 處理空表頭if (header.isEmpty()) {header = "Column_" + (cell.getColumnIndex() + 1);}// 處理重復表頭int count = headerCount.getOrDefault(header, 0) + 1;headerCount.put(header, count);if (count > 1) {header = header + "_" + count;}headers.add(header);}return headers;}private static Map<String, Object> parseRow(Row row, List<String> headers) {Map<String, Object> rowData = new LinkedHashMap<>();DataFormatter formatter = new DataFormatter();for (int i = 0; i < headers.size(); i++) {String header = headers.get(i);Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);// 根據單元格類型處理數據switch (cell.getCellType()) {case STRING:rowData.put(header, cell.getStringCellValue().trim());break;case NUMERIC:if (DateUtil.isCellDateFormatted(cell)) {// 日期類型處理rowData.put(header, cell.getDateCellValue().toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime());} else {// 數值類型處理double value = cell.getNumericCellValue();if (value == (int) value) {rowData.put(header, (int) value);} else {rowData.put(header, value);}}break;case BOOLEAN:rowData.put(header, cell.getBooleanCellValue());break;case FORMULA:// 公式單元格處理rowData.put(header, evaluateFormulaCell(cell));break;default:rowData.put(header, formatter.formatCellValue(cell));}}return rowData;}private static Object evaluateFormulaCell(Cell cell) {try {switch (cell.getCachedFormulaResultType()) {case NUMERIC:return cell.getNumericCellValue();case STRING:return cell.getStringCellValue();case BOOLEAN:return cell.getBooleanCellValue();default:return "";}} catch (Exception e) {return "FORMULA_ERROR";}}private static String getCellValueAsString(Cell cell) {if (cell == null) return "";switch (cell.getCellType()) {case STRING:return cell.getStringCellValue();case NUMERIC:return NumberToTextConverter.toText(cell.getNumericCellValue());case BOOLEAN:return String.valueOf(cell.getBooleanCellValue());case FORMULA:return evaluateFormulaCell(cell).toString();default:return "";}}
}

?