使用zip壓縮包上傳excel文件的優點

 	1、體積更小，節約帶寬2、比excel直接讀取更方便攜帶參數及修改3、可以一次性批量導入

Java代碼

Controller

	@PostMapping("/importData")@ApiOperationSupport(order = 3)@ApiOperation(value = "上傳")public R importData(@RequestParam MultipartFile file,@RequestParam("versionId") String versionId) {try {dataTableFjService.importDataSheets(file,versionId);} catch (Exception e) {log.error("上傳文件接口", e);return R.fail("上傳文件接口異常");}return R.success("上傳成功");}

Service

	/*** 導入數據* @param file 壓縮包文件* @param versionId 參數版本id* @throws IOException*/void importDataSheets(MultipartFile file, String dataManagementId, String versionId);

Impl

@Transactional(rollbackFor = Exception.class)
public void importDataSheets(MultipartFile file, String versionId) throws IOException {// 檢查上傳文件是否為空if (file.isEmpty()) {throw new ServiceException("上傳文件為空");}// 檢查版本信息是否存在DataVersionFj dataVersion = dataVersionFjService.getById(versionId);if (Func.isEmpty(dataVersion)) {throw new ServiceException("未查詢到版本信息");}// 創建ZIP輸入流，使用GBK編碼處理中文文件名@Cleanup ZipInputStream zipInputStream = new ZipInputStream(file.getInputStream(), Charset.forName("GBK"));ZipEntry entry = zipInputStream.getNextEntry();// 遍歷ZIP中的所有條目while (entry != null) {if (!entry.isDirectory()) {// 獲取文件名（處理可能包含的路徑分隔符）String entryName = entry.getName();if (entryName.contains("/")) {entryName = entryName.split("/")[1];}// 驗證是否為Excel文件if (!isExcelFile(entryName)) {throw new ServiceException("文件類型有誤");}// 將當前ZIP條目內容讀入字節數組byte[] fileBytes = IOUtils.toByteArray(zipInputStream);// 創建獨立的輸入流，避免關閉ZIP流@Cleanup InputStream is = new ByteArrayInputStream(fileBytes);// 處理Excel文件applicationContext.getBean(XXXImpl.class).processExcelFile(dataVersion, entryName, is);}// 關閉當前ZIP條目，移動到下一個zipInputStream.closeEntry();entry = zipInputStream.getNextEntry();}// 更新數據……
}/*** 處理Excel文件并導入數據到數據庫* * @param dataVersion 數據版本信息* @param fileName Excel文件名* @param zipInputStream Excel文件輸入流* @throws ServiceException 當文件處理失敗時拋出異常*/
@Transactional(rollbackFor = Exception.class)
public void processExcelFile(DataVersionFj dataVersion, String fileName, InputStream zipInputStream) {Workbook workbook = null;try {// 根據文件擴展名創建不同格式的Workbookworkbook = fileName.endsWith("xlsx") ? new XSSFWorkbook(zipInputStream) :new HSSFWorkbook(zipInputStream);} catch (Exception e) {e.printStackTrace();throw new ServiceException("[" + fileName + "]，文件打開失敗，請核對文件內容");}// 解析表名并獲取對應的枚舉類型String[] tableNames = fileName.split("[.]");TableNameEnum tableNameEnum = TableNameEnum.getByName(tableNames[0]);// 存儲Excel前三行表頭信息（用于處理多級表頭）List<String> fieldMeaningNames = null;  // 第一行表頭List<String> fieldMeaningNames2 = null; // 第二行表頭List<String> fieldMeaningNames3 = null; // 第三行表頭// 獲取第一個工作表Sheet dataSheet = workbook.getSheetAt(0);int dataRow = 0; // 數據起始行索引// 讀取前3行表頭信息Iterator<Row> rowIterator = dataSheet.iterator();int dataIndex = 0;while (rowIterator.hasNext() && dataIndex < 3) {Row headRow = rowIterator.next();Iterator<Cell> cellIterator = headRow.cellIterator();// 獲取當前行的有效表頭內容List<String> fieldMeaningNamesThis = getFieldMeaningName(cellIterator);// 處理有效行（跳過空行）if (Func.isNotEmpty(fieldMeaningNamesThis)) {if (isEmptyRow(fieldMeaningNamesThis, fileName)) {continue; // 跳過無效行}// 按行索引分配表頭信息switch (dataIndex) {case 0:fieldMeaningNames = fieldMeaningNamesThis;break;case 1:fieldMeaningNames2 = fieldMeaningNamesThis;break;case 2:fieldMeaningNames3 = fieldMeaningNamesThis;break;}dataRow = dataIndex;}dataIndex++;}// 驗證表頭是否存在if (fieldMeaningNames == null || fieldMeaningNames.size() == 0) {throw new ServiceException("上傳文件內容為空");}try {// 處理多級表頭的合并單元格情況（向下繼承父級表頭）if (Func.isNotEmpty(fieldMeaningNames3)) {for (int i = 1; i < fieldMeaningNames3.size(); i++) {if (Func.isNotBlank(fieldMeaningNames3.get(i)) && Func.isBlank(fieldMeaningNames2.get(i))) {// 向上查找最近的非空父級表頭for (int j = i - 1; j >= 0; j--) {if (Func.isNotBlank(fieldMeaningNames2.get(j))) {fieldMeaningNames2.set(i, fieldMeaningNames2.get(j));break;}}}}}// 處理二級表頭的合并單元格情況if (Func.isNotEmpty(fieldMeaningNames2)) {for (int i = 1; i < fieldMeaningNames2.size(); i++) {if (Func.isNotBlank(fieldMeaningNames2.get(i)) && Func.isBlank(fieldMeaningNames.get(i))) {// 向上查找最近的非空父級表頭for (int j = i - 1; j >= 0; j--) {if (Func.isNotBlank(fieldMeaningNames.get(j))) {fieldMeaningNames.set(i, fieldMeaningNames.get(j));break;}}}}}// 構建列名和位置信息List<Map<String, Object>> fieldMeaningModify = new ArrayList<>();List<Map<String, Object>> fieldMeaningTemp = new ArrayList<>();for (int i = 0; i < fieldMeaningNames.size(); i++) {Map<String, Object> item = new HashMap<>();item.put("name", fieldMeaningNames.get(i));item.put("index", i);fieldMeaningTemp.add(item);}// 如果沒有自定義列映射，使用臨時列信息if (fieldMeaningModify.size() == 0) {fieldMeaningModify = fieldMeaningTemp;}//自行處理excel數據列表內容……} catch (Exception e) {log.error("插入數據異常：{}", e.getMessage());e.printStackTrace();throw new ServiceException("導入異常");}
}
/*** 獲取Excel行中所有單元格的值* * @param cellIterator 單元格迭代器* @return 包含所有單元格格式化后值的列表*/
public List<String> getFieldMeaningName(Iterator<Cell> cellIterator) {// 存儲當前行所有單元格的值List<String> fieldMeaningNames = new ArrayList<>();// 創建數據格式化器，用于正確處理不同類型的單元格值DataFormatter dataFormatter = new DataFormatter();// 遍歷當前行的所有單元格while (cellIterator.hasNext()) {Cell cell = cellIterator.next();// 使用DataFormatter獲取單元格的格式化文本值// 例如：數字類型會保留格式（如百分比、貨幣），日期類型會轉為字符串String value = dataFormatter.formatCellValue(cell);// 將單元格值添加到列表中fieldMeaningNames.add(value);}return fieldMeaningNames;
}