当前位置：首页 > news >正文

Java 解析前端上传 ZIP 压缩包内 Excel 文件的完整实现方案

news 2025/7/22 6:46:27

使用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;
}