Java导出Excel：基于模板的动态数据填充与合计计算

一、前言在日常开发中Excel导出功能是非常常见的需求。本文将介绍一种基于Excel模板的数据导出方案该方案具有以下特点使用预定义模板保持导出格式统一支持多Sheet页数据填充支持动态行数写入自动生成合计公式最终上传至OSS存储二、实现思路查询数据 → 下载模板 → 填充数据 → 生成合计 → 上传OSS三、核心代码实现导入依赖dependency groupIdorg.apache.poi/groupId artifactIdpoi-ooxml/artifactId version4.1.2/version /dependency3.1 导出入口方法OverridepublicBaseResultqueryHousingArchiveExport(Mapmap){// 参数转换StringqueryJSONJSONObject.toJSONString(map);HtpStatisticsDTOhtpStatisticsDTOJSONObject.parseObject(queryJSON,HtpStatisticsDTO.class);// 查询数据BaseResultbaseResultqueryHousingArchive(htpStatisticsDTO);if(BaseResult.success().equalsCode(baseResult)){// 填充Excel模板MultipartFilemultipartFiledealExcelFile((ListMap)baseResult.getData(),/resource/public/doc/02房源档案数据导出模版.xlsx,0);if(null!multipartFile){// 上传至OSSreturnossService.uploadFile(multipartFile);}}returnBaseResult.failed(导出失败,null);}3.2 Excel数据处理核心方法privateMultipartFiledealExcelFile(ListMapdata,Stringurl,inttype){try{// 1. 下载模板文件byte[]excelFileBytesossService.downloadBytes(url);// 2. 创建工作簿对象WorkbookworkbookWorkbookFactory.create(newByteArrayInputStream(excelFileBytes));Sheetsheetworkbook.getSheetAt(type);workbook.setActiveSheet(type);// 3. 数据填充起始行从第5行开始introwNum4;// 4. 根据不同类型填充不同列if(type0){// 类型0房源档案导出32列fillSheetType0(sheet,data,rowNum);}elseif(type1){// 类型1其他导出10列fillSheetType1(sheet,data,rowNum);}else{// 类型2其他导出18列fillSheetType2(sheet,data,rowNum);}// 5. 生成合计行createTotalCell(sheet,rowNum,workbook,numColumns);// 6. 转换为MultipartFilereturnconvertToMultipartFile(workbook);}catch(IOExceptione){e.printStackTrace();}returnnull;}3.3 数据填充以type0为例privatevoidfillSheetType0(Sheetsheet,ListMapdata,introwNum){SimpleDateFormatsdf1newSimpleDateFormat(YYYY-MM-dd);for(inti0;idata.size();i){MapString,Objectmapdata.get(i);Rowrowsheet.createRow(rowNum);row.createCell(0).setCellValue(Convert.toStr(map.get(projectName)));row.createCell(1).setCellValue(Convert.toDouble(map.get(areaTotal)));row.createCell(2).setCellValue(Convert.toDouble(map.get(buildTotal)));row.createCell(3).setCellValue(Convert.toDouble(map.get(roomTotal)));row.createCell(4).setCellValue(Convert.toDouble(map.get(roomAreaTotal)));// 各类房源统计...row.createCell(31).setCellValue(Convert.toDouble(map.get(rcgyRoomSubTotal)));row.createCell(32).setCellValue(Convert.toDouble(map.get(rcgyAreaSubTotal)));rowNum;}}3.4 合计行生成privatevoidcreateTotalCell(Sheetsheet,introwNum,Workbookworkbook,intnumColumns){intstartRow5;// 数据起始行intendRowrowNum;// 数据结束行RowtotalRowsheet.createRow(rowNum);CelltotalLabelCelltotalRow.createCell(0);totalLabelCell.setCellValue(合计);for(intcol1;colnumColumns;col){CelltotalCelltotalRow.createCell(col);StringcolumnLetterCellReference.convertNumToColString(col);// 生成SUM公式如SUM(B5:B10)StringformulaSUM(columnLetterstartRow:columnLetterendRow);totalCell.setCellFormula(formula);// 计算公式值workbook.getCreationHelper().createFormulaEvaluator().evaluateFormulaCell(totalCell);// 设置数值格式保留两位小数CellStyletableStyleCenterworkbook.createCellStyle();tableStyleCenter.setDataFormat(workbook.createDataFormat().getFormat(0.00));totalCell.setCellStyle(tableStyleCenter);}}3.5 转换为MultipartFileprivateMultipartFileconvertToMultipartFile(Workbookworkbook){ByteArrayOutputStreambaosnull;try{baosnewByteArrayOutputStream();workbook.write(baos);returnnewCommonMultipartFile(file,.xlsx,ContentType.APPLICATION_OCTET_STREAM.toString(),baos.toByteArray());}catch(Exceptione){e.printStackTrace();}finally{IoUtil.close(baos);}returnnull;}四、关键技术点解析4.1 模板下载模板文件预先上传至OSS导出时动态下载确保格式统一byte[]excelFileBytesossService.downloadBytes(url);WorkbookworkbookWorkbookFactory.create(newByteArrayInputStream(excelFileBytes));4.2 类型安全转换使用Convert工具类安全转换数据类型Convert.toStr(map.get(projectName))// 转字符串Convert.toDouble(map.get(areaTotal))// 转Double4.3 动态合计公式通过列字母转换和行号计算自动生成SUM公式列公式说明BSUM(B5:B10)第2列合计CSUM(C5:C10)第3列合计4.4 资源管理使用ByteArrayOutputStream并在finally中正确关闭finally{IoUtil.close(baos);}五、模板设计建议5.1 模板结构行号内容第1行标题第2行导出时间等说明第3行表头第4行留空数据起始行前一行为空第5行起数据填充区5.2 模板路径管理建议将模板路径配置化excel:template:housing-archive:/resource/public/doc/xx数据导出模版.xlsx七、总结本文介绍的导出方案具有以下优点模板复用导出样式与模板保持一致无需代码控制格式扩展性好通过type参数支持多种导出模板自动合计无需手动计算利用Excel公式自动求和存储友好导出后直接上传OSS返回文件访问链接该方案已在生产环境稳定运行可满足大部分Excel导出需求。