SpringBoot项目导出Excel解决方案🔥
舒一笑 11/30/2024 Excel
# 1. 需求分析
- 功能目标：用户通过接口请求获取Excel文件，文件内容基于数据库中的某些数据。
- 技术栈：Spring Boot、POI库（或其他Excel相关库）、可能还需要分页、条件查询等功能。
- 导出的数据：导出的数据需要格式化并展示为Excel表格，如表头、列宽、数据格式等。
# 2. 项目环境搭建
引入相关依赖，本次演示采用EasyExcel依赖并结合POI
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.1.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>ooxml-schemas</artifactId> <version>1.4</version> </dependency>Spring Boot基本配置：确保你的Spring Boot项目可以运行，已配置好基本的Controller、Service层。
# 3. 设计思路
- 接口设计：
- 创建一个GET接口，接收查询参数（如日期范围、部门、状态等），返回一个Excel文件。
- URL示例：
GET /api/export/excel?startDate=2024-01-01&endDate=2024-12-31
- 数据查询：根据请求参数查询数据库中的数据。可以使用分页查询来避免一次性查询大量数据。
- 示例：
employeeRepository.findAllByDateRange(startDate, endDate)
- 示例：
- Excel文件生成：使用POI库将数据转换为Excel文件，并处理Excel的格式（如设置表头、列宽、日期格式、数字格式等）。
# 4. 具体实现步骤
- 定义Controller层：
- 创建一个
@RestController，提供导出Excel的接口。 - 接口返回的是通过
HttpServletResponse来实现 Excel 文件的导出。在这种方式下，文件并不是先保存到服务器的磁盘上，而是直接通过响应流返回给客户端。原理是通过将生成的 Excel 数据写入HttpServletResponse的输出流中，浏览器会触发文件下载。
- 创建一个
- Service层实现：
- 在Service层进行数据查询，封装到Excel对象中。
- 使用EasyExcel依赖并结合POI库将数据写入Excel，创建表头、数据行等。
- Excel文件生成：
- 使用EasyExcel依赖并结合POI生成Excel工作簿。
- 设置表格样式（如表头加粗、设置边框、自动列宽等）。
- 将查询到的数据填充到Excel表格的行中。
- 返回文件：
- 将生成的Excel文件通过
HttpServletResponse来实现 Excel 文件的导出
- 将生成的Excel文件通过
# 5. 代码实现（采用具体的项目代码，去掉业务逻辑部分）
Controller层：
@PostMapping("/exportUnitPerformanceScores") @ApiOperation(value = "导出单位绩效指标得分情况一览表") public void exportUnitPerformanceScores(@RequestBody ExportRo exportRo, HttpServletResponse response) throws IOException { assessExcelService.exportUnitPerformanceScores(exportRo, response); } @PostMapping("/exportPerformanceDetails") @ApiOperation(value = "导出关键业绩考核指标得分明细") public void exportPerformanceDetails(@RequestBody ExportRo exportRo, HttpServletResponse response) throws IOException { assessExcelService.exportPerformanceDetails(exportRo, response); } @PostMapping("/explainUnitPerfIndAddSub") @ApiOperation(value = "导出经营业绩考核各单位绩效指标加减分情况说明") public void explainUnitPerfIndAddSub(@RequestBody ExportRo exportRo, HttpServletResponse response) throws IOException { assessExcelService.explainUnitPerfIndAddSub(exportRo, response); } @PostMapping("/downloadZip") @ApiOperation(value = "个人模板压缩包导出") public void downloadZip(@RequestBody ExportRo exportRo, HttpServletResponse response){ try { assessExcelService.downloadZip(exportRo,response); } catch (Exception e) { ; } }Service层：
@Override public void exportUnitPerformanceScores(ExportRo exportRo, HttpServletResponse response) { // 查询数据 // 数据查询逻辑代码 // 数据封装 Map<String, List<ExportUnitPerformanceScoresVo>> sheetDataMap = new LinkedHashMap<>(); ArrayList<ExportUnitPerformanceScoresVo> exportUnitPerformanceScoresVosCityProper = new ArrayList<>(); ArrayList<ExportUnitPerformanceScoresVo> exportUnitPerformanceScoresVosCountyLevel = new ArrayList<>(); if (ObjectUtils.isNotEmpty(stringBigDecimalMapCityProper)){ dataEncapsulation(perfAssessContractsCityProper, orgTypeNameCityProperMap, stringBigDecimalMapCityProper, exportUnitPerformanceScoresVosCityProper); } if (ObjectUtils.isNotEmpty(stringBigDecimalMapCountyLevel)){ dataEncapsulation(perfAssessContractsCountyLevel, orgTypeNameCountyLevelMap, stringBigDecimalMapCountyLevel, exportUnitPerformanceScoresVosCountyLevel); } sheetDataMap.put("城区单位", exportUnitPerformanceScoresVosCityProper); sheetDataMap.put("县级产业单位", exportUnitPerformanceScoresVosCountyLevel); // 名称封装 BasePeriodOneDTO byPeriodCode = basePeriodApi.getByPeriodCode(exportRo.getPeriodCode()); StringBuffer fileName = new StringBuffer(); fileName.append("省管产业单位"); fileName.append(byPeriodCode.getPeriodName()); fileName.append("经营业绩考核县级产业单位绩效指标得分情况一览表"); try { JJExcelUtil.writeMultipleSheets(response, fileName.toString(), sheetDataMap, ExportUnitPerformanceScoresVo.class,"方正仿宋_GBK",1,true,2,(short)20,(short)20); } catch (IOException e) { throw new RuntimeException(e); } } private void dataEncapsulation(List<PerfAssessContract> perfAssessContractsCityProper, Map<String, String> orgTypeNameCityProperMap, Map<String, Map<String, BigDecimal>> stringBigDecimalMapCityProper, ArrayList<ExportUnitPerformanceScoresVo> exportUnitPerformanceScoresVosCityProper) { for (int i = 0; i < perfAssessContractsCityProper.size(); i++) { PerfAssessContract perfAssessContract = perfAssessContractsCityProper.get(i); ExportUnitPerformanceScoresVo exportUnitPerformanceScoresVo = new ExportUnitPerformanceScoresVo(); exportUnitPerformanceScoresVo.setSerialNumber(String.valueOf(i + 1)); exportUnitPerformanceScoresVo.setUnitType(orgTypeNameCityProperMap.get(perfAssessContract.getOrgType())); exportUnitPerformanceScoresVo.setUnitName(perfAssessContract.getOrgName()); // 对所有字段值进行null检查，若为null则设置为"/" exportUnitPerformanceScoresVo.setBasicScoreForKeyPerformanceAssessment( getValueOrDefault(stringBigDecimalMapCityProper.get("WUHAN_JJ_GJYJKH_YEARS"), perfAssessContract.getOrgCode())); exportUnitPerformanceScoresVo.setKeyPerformanceAssessmentScore( getStringValueOrDefault(perfAssessContract.getScoreField1())); exportUnitPerformanceScoresVo.setBasicScoreForProfessionalWorkAssessment( getValueOrDefault(stringBigDecimalMapCityProper.get("WUHAN_JJ_ZYGZKH"), perfAssessContract.getOrgCode())); exportUnitPerformanceScoresVo.setProfessionalWorkAssessmentScore( getStringValueOrDefault(perfAssessContract.getScoreField2())); exportUnitPerformanceScoresVo.setBasicScoreForAssessmentOfPartyBuildingWork( getValueOrDefault(stringBigDecimalMapCityProper.get("WUHAN_JJ_DJGZKH_YEARS"), perfAssessContract.getOrgCode())); exportUnitPerformanceScoresVo.setAssessmentScoreForPartyBuildingWork( getStringValueOrDefault(perfAssessContract.getScoreField3())); exportUnitPerformanceScoresVo.setScoreForSupportingServiceEffectiveness( getStringValueOrDefault(perfAssessContract.getScoreField4())); exportUnitPerformanceScoresVo.setRiskControlAssessmentScore( getStringValueOrDefault(perfAssessContract.getScoreField5())); exportUnitPerformanceScoresVo.setScoreForTheBonusPointsOfStrivingForExcellence( getStringValueOrDefault(perfAssessContract.getScoreField6())); exportUnitPerformanceScoresVo.setTotalScore(getStringValueOrDefault(perfAssessContract.getFinalScore())); exportUnitPerformanceScoresVo.setRanking(getStringValueOrDefault(perfAssessContract.getRanking())); exportUnitPerformanceScoresVo.setPerformanceLevel(getStringValueOrDefault(perfAssessContract.getGrade())); exportUnitPerformanceScoresVo.setKeyPerformanceEvaluationShortcomingsIndicators(getStringValueOrDefault(perfAssessContract.getResultDescr1())); exportUnitPerformanceScoresVo.setKeyPerformanceEvaluationAdvantageIndicators(getStringValueOrDefault(perfAssessContract.getResultDescr2())); exportUnitPerformanceScoresVosCityProper.add(exportUnitPerformanceScoresVo); } exportUnitPerformanceScoresVosCityProper.sort(Comparator.comparing(ExportUnitPerformanceScoresVo::getUnitName, Collator.getInstance(Locale.CHINA))); } private static String getStringValueOrDefault(Object value) { return value == null ? "/" : String.valueOf(value); } private static String getValueOrDefault(Map<String, BigDecimal> map, String key) { BigDecimal value = map != null ? map.getOrDefault(key, BigDecimal.ZERO) : BigDecimal.ZERO; return value == null || value.equals(BigDecimal.ZERO) ? "/" : String.valueOf(value); } private Map<String, Map<String, BigDecimal>> obtainWeightInformation(String periodCode, List<String> indexCodeList, List<String> templateCodeList) { LambdaQueryWrapper<PerfIndexOrg> perfIndexOrgLambdaQueryWrapper = new LambdaQueryWrapper<>(); perfIndexOrgLambdaQueryWrapper.in(PerfIndexOrg::getIndexCode, indexCodeList); perfIndexOrgLambdaQueryWrapper.eq(PerfIndexOrg::getPeriodCode, periodCode); perfIndexOrgLambdaQueryWrapper.in(PerfIndexOrg::getIndexTemplateCode, templateCodeList); // 根据条件查询 PerfIndexOrg 对象列表 List<PerfIndexOrg> perfIndexOrgs = perfIndexOrgMapper.selectList(perfIndexOrgLambdaQueryWrapper); // 先按模板编码分组，再按承担组织编码分组，然后计算权重之和 Map<String, Map<String, BigDecimal>> indexWeightMap = perfIndexOrgs.stream() .collect(Collectors.groupingBy( PerfIndexOrg::getIndexTemplateCode, // 按模板编码分组 Collectors.groupingBy( PerfIndexOrg::getOrgCode, // 再按承担组织编码分组 Collectors.mapping( perfIndexOrg -> { // 如果 indexWeight 为空，设置为默认值 0 String weightStr = perfIndexOrg.getIndexWeight(); if (weightStr == null || weightStr.isEmpty()) { weightStr = "0"; // 默认值为 0 } // 转换为 BigDecimal return new BigDecimal(weightStr); }, Collectors.reducing(BigDecimal.ZERO, BigDecimal::add) // 累加同组的权重 ) ) )); return indexWeightMap; } @Override public void exportPerformanceDetails(ExportRo exportRo, HttpServletResponse response) { // 查询数据 // 构建单位名称集合 城区单位 Map<String, String> orgNameMapCityProper = perfAssessContractIndicesCityProper.stream() .collect(Collectors.collectingAndThen( Collectors.toMap( PerfAssessContractIndex::getOrgCode, // key映射函数 perfAssessContractIndex -> perfAssessContractIndex, // value映射函数 (existing, replacement) -> existing // 合并函数，如果有重复的key，保留现有的 ), map -> new ArrayList<>(map.values()) // 将map的值收集到一个新的ArrayList中 )) .stream() .sorted(Comparator.comparing(PerfAssessContractIndex::getOrgName, Collator.getInstance(Locale.CHINA))) .collect(Collectors.toMap( PerfAssessContractIndex::getOrgCode, PerfAssessContractIndex::getOrgName, (existing, replacement) -> existing, LinkedHashMap::new // 使用LinkedHashMap来保持排序 )); // 构建单位名称集合 县级产业单位 Map<String, String> orgNameMapCountyLevel = perfAssessContractIndicesCountyLevel.stream() .collect(Collectors.collectingAndThen( Collectors.toMap( PerfAssessContractIndex::getOrgCode, // key映射函数 perfAssessContractIndex -> perfAssessContractIndex, // value映射函数 (existing, replacement) -> existing // 合并函数，如果有重复的key，保留现有的 ), map -> new ArrayList<>(map.values()) // 将map的值收集到一个新的ArrayList中 )) .stream() .sorted(Comparator.comparing(PerfAssessContractIndex::getOrgName, Collator.getInstance(Locale.CHINA))) .collect(Collectors.toMap( PerfAssessContractIndex::getOrgCode, PerfAssessContractIndex::getOrgName, (existing, replacement) -> existing, LinkedHashMap::new // 使用LinkedHashMap来保持排序 )); // 数据构建 // 动态表头构建 Map<String, List<List<String>>> headListMap = new HashMap<>(); headListMap.put("城区单位", buildHeaders(orgNameMapCityProper)); headListMap.put("县级产业单位", buildHeaders(orgNameMapCountyLevel)); // 动态数据构建 Map<String, List<Map<Integer, String>>> sheetDataMap = new LinkedHashMap<>(); sheetDataMap.put("城区单位", buildData(groupedByIndexNameCityProper)); sheetDataMap.put("县级产业单位", buildData(groupedByIndexNameCountyLevel)); // 名称封装 BasePeriodOneDTO byPeriodCode = basePeriodApi.getByPeriodCode(exportRo.getPeriodCode()); StringBuffer fileName = new StringBuffer(); fileName.append("省管产业单位"); fileName.append(byPeriodCode.getPeriodName()); fileName.append("关键业绩考核指标得分明细"); try { JJExcelUtil.writeMultipleSheets(response, fileName.toString(),sheetDataMap,headListMap); } catch (IOException e) { throw new RuntimeException(e); } } // 构建表头 private List<List<String>> buildHeaders(Map<String, String> orgNameMapCityProper) { List<List<String>> headers = new ArrayList<>(); headers.add(Arrays.asList("牵头部门", "牵头部门")); headers.add(Arrays.asList("考核指标", "考核指标")); for (String s : orgNameMapCityProper.keySet()) { headers.add(Arrays.asList(orgNameMapCityProper.get(s), "指标权重")); headers.add(Arrays.asList(orgNameMapCityProper.get(s), "指标目标值")); headers.add(Arrays.asList(orgNameMapCityProper.get(s), "指标完成值")); headers.add(Arrays.asList(orgNameMapCityProper.get(s), "考核评分")); headers.add(Arrays.asList(orgNameMapCityProper.get(s), "指标得分")); headers.add(Arrays.asList(orgNameMapCityProper.get(s), "打分依据")); } return headers; } // 构建数据 private List<Map<Integer, String>> buildData(Map<String, List<PerfAssessContractIndex>> perfAssessContractIndicesCityProper) { List<Map<Integer, String>> data = new ArrayList<>(); // 遍历每个指标名称的分组 for (Map.Entry<String, List<PerfAssessContractIndex>> entry : perfAssessContractIndicesCityProper.entrySet()) { String indexName = entry.getKey(); List<PerfAssessContractIndex> indices = entry.getValue(); // 初始化当前行数据，确保每个指标名称有固定的列 Map<Integer, String> row = new HashMap<>(); // 第一个字段是固定的：RespOrgName 和 IndexName int currentIndex = 0; // 当前列索引，初始从 0 开始 for (PerfAssessContractIndex perfAssessContractIndex : indices) { if (currentIndex == 0) { row.put(currentIndex++, getStringValueOrDefault(perfAssessContractIndex.getRespOrgName())); } if (currentIndex == 1) { row.put(currentIndex++, getStringValueOrDefault(indexName)); // 指标名称 } row.put(currentIndex++, perfAssessContractIndex.getIndexWeight() == null ? "/" : perfAssessContractIndex.getIndexWeight() + "%"); row.put(currentIndex++, getStringValueOrDefault(perfAssessContractIndex.getTargetValue())); row.put(currentIndex++, getStringValueOrDefault(perfAssessContractIndex.getFinishValue())); row.put(currentIndex++, getStringValueOrDefault(perfAssessContractIndex.getScore())); row.put(currentIndex++, getStringValueOrDefault(perfAssessContractIndex.getFinalScore())); row.put(currentIndex++, getStringValueOrDefault(perfAssessContractIndex.getFinalDesc())); } // 添加当前行到数据列表 data.add(row); } return data; } @Override public void explainUnitPerfIndAddSub(ExportRo exportRo, HttpServletResponse response) { // 查询数据 // 动态获取模板表头 List<JJDynamicTableHeadListVo> dynamicHead = jjAssessResultService.getDynamicHead(exportRo.getPeriodCode()) .stream().filter(head -> "5".equals(head.getScoringMode())).collect(Collectors.toList()); // 倒序 Collections.reverse(dynamicHead); // 获取倒序后的动态表头中的 indexTemplateCode List<String> indexTemplateCodeList = dynamicHead.stream() .map(JJDynamicTableHeadListVo::getIndexTemplateCode) .collect(Collectors.toList()); // 2. 查询绩效得分情况信息 LambdaQueryWrapper<PerfAssessContract> perfAssessQWCityProper = new LambdaQueryWrapper<>(); perfAssessQWCityProper.eq(PerfAssessContract::getPeriodCode, exportRo.getPeriodCode()); perfAssessQWCityProper.eq(PerfAssessContract::getSuitUnitCode, PerfUserThreadLocal.get().getSuitUnit()); perfAssessQWCityProper.eq(ObjectUtils.isNotEmpty(exportRo.getOrgCode()), PerfAssessContract::getOrgCode, exportRo.getOrgCode()); List<PerfAssessContract> perfAssessContracts = perfAssessContractMapper.selectList(perfAssessQWCityProper); // 3. 查询考核单位部门承担指标 城区单位 List<String> seqKeyListCityProper = perfAssessContracts.stream().map(PerfAssessContract::getSeqKey).collect(Collectors.toList()); List<PerfAssessContractIndex> perfAssessContractIndices = null; if (ObjectUtils.isNotEmpty(seqKeyListCityProper) && ObjectUtils.isNotEmpty(indexTemplateCodeList)){ LambdaQueryWrapper<PerfAssessContractIndex> perfAssessContractIndexLambdaQueryWrapper = new LambdaQueryWrapper<>(); perfAssessContractIndexLambdaQueryWrapper.in(PerfAssessContractIndex::getContractSeqKey, seqKeyListCityProper); perfAssessContractIndexLambdaQueryWrapper.eq(PerfAssessContractIndex::getPeriodCode, exportRo.getPeriodCode()); perfAssessContractIndexLambdaQueryWrapper.in(PerfAssessContractIndex::getIndexTemplateCode, indexTemplateCodeList) .orderByAsc(PerfAssessContractIndex::getOrgName) .orderByAsc(PerfAssessContractIndex::getIndexName) .orderByAsc(PerfAssessContractIndex::getRespOrgName); perfAssessContractIndices = perfAssessContractIndexMapper.selectList(perfAssessContractIndexLambdaQueryWrapper); } Map<String, String> indexTemplateNameMap = dynamicHead.stream() .collect(Collectors.toMap(JJDynamicTableHeadListVo::getIndexTemplateCode, JJDynamicTableHeadListVo::getHeadName)); // 按照倒序后的模块名称顺序封装数据 // 4. 按倒序后的模块名称顺序分组数据 Map<String, List<PerfAssessContractIndex>> groupedData = perfAssessContractIndices.stream() .collect(Collectors.groupingBy(index -> { String moduleName = indexTemplateNameMap.get(index.getIndexTemplateCode()); if (moduleName == null) { moduleName = "未知模块"; } return sanitizeSheetName(moduleName); })); // 5. 按倒序后的模块名称顺序封装数据 Map<String, List<ExplainUnitPerfIndAddSubVo>> sheetDataMap = new LinkedHashMap<>(); // 获取倒序后的模块名称列表 List<String> reversedModuleNames = dynamicHead.stream() .map(head -> indexTemplateNameMap.get(head.getIndexTemplateCode())) .collect(Collectors.toList()); // 通过倒序后的模块名称顺序封装数据 for (String assessmentModule : reversedModuleNames) { String assessmentModuleName = sanitizeSheetName(assessmentModule); List<PerfAssessContractIndex> indices = groupedData.get(assessmentModuleName); if (indices != null) { List<ExplainUnitPerfIndAddSubVo> voList = indices.stream() .map(index -> { ExplainUnitPerfIndAddSubVo vo = new ExplainUnitPerfIndAddSubVo(); vo.setAssessmentModule(assessmentModuleName); // 直接使用分组键 vo.setUnitName(index.getOrgName()); vo.setRatingExplanation(getStringValueOrDefault(index.getFinalDesc())); vo.setAssessmentIndicators(index.getIndexName()); vo.setAssessmentScore(index.getFinalScore() != null ? index.getFinalScore().toString() : "/"); vo.setLeadingDepartment(index.getRespOrgName()); return vo; }) // 排序 .sorted(Comparator.comparing(ExplainUnitPerfIndAddSubVo::getUnitName, Collator.getInstance(Locale.CHINA)) .thenComparing(ExplainUnitPerfIndAddSubVo::getAssessmentIndicators, Collator.getInstance(Locale.CHINA)) .thenComparing(ExplainUnitPerfIndAddSubVo::getLeadingDepartment, Collator.getInstance(Locale.CHINA))) .collect(Collectors.toList()); // 给排序后的 voList 重新分配 serialNumber AtomicInteger serialNumber = new AtomicInteger(1); voList.forEach(vo -> vo.setSerialNumber(String.valueOf(serialNumber.getAndIncrement()))); // 将排序后的结果放入 sheetDataMap sheetDataMap.put(assessmentModuleName, voList); } } // 名称封装 BasePeriodOneDTO byPeriodCode = basePeriodApi.getByPeriodCode(exportRo.getPeriodCode()); StringBuffer fileName = new StringBuffer(); fileName.append("省管产业单位"); fileName.append(byPeriodCode.getPeriodName()); fileName.append("经营业绩考核各单位绩效指标加减分情况说明"); try { JJExcelUtil.writeMultipleSheets(response, fileName.toString(), sheetDataMap, ExplainUnitPerfIndAddSubVo.class,"宋体",0,false,0,(short)9,(short)9); } catch (IOException e) { throw new RuntimeException(e); } } /** * 清理 Excel Sheet 名称中的非法字符，并去掉“得分” * @param sheetName 原始 Sheet 名称 * @return 处理后的合法 Sheet 名称 */ private String sanitizeSheetName(String sheetName) { // Excel Sheet 名称非法字符：\ / ? * [ ] : String cleanedName = sheetName.replaceAll("[\\\\/:?*\\[\\]]", "_"); // 去掉“得分”二字 cleanedName = cleanedName.replaceAll("得分", ""); return cleanedName; } @Override public void downloadZip(ExportRo exportRo, HttpServletResponse response) throws IOException { // 设置响应头为 ZIP 文件下载 BasePeriodOneDTO byPeriodCode = basePeriodApi.getByPeriodCode(exportRo.getPeriodCode()); response.setContentType("application/zip"); String fileName = URLEncoder.encode(byPeriodCode.getPeriodName() + "业绩考核简报","UTF-8"); response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".zip"); // 创建一个 ZipOutputStream 用来写入压缩包 ZipOutputStream zos = new ZipOutputStream(response.getOutputStream()); // 捕获每个 Excel 文件的内容并将其添加到 ZIP 包中 byte[] excel1 = captureExport(exportRo, "exportUnitPerformanceScores"); byte[] excel2 = captureExport(exportRo, "exportPerformanceDetails"); byte[] excel3 = captureExport(exportRo, "explainUnitPerfIndAddSub"); // 写入 ZIP 文件 StringBuffer fileName1 = new StringBuffer(); fileName1.append("省管产业单位"); fileName1.append(byPeriodCode.getPeriodName()); fileName1.append("经营业绩考核县级产业单位绩效指标得分情况一览表"); zos.putNextEntry(new ZipEntry(fileName1+".xlsx")); zos.write(excel1); zos.closeEntry(); StringBuffer fileName2 = new StringBuffer(); fileName2.append("省管产业单位"); fileName2.append(byPeriodCode.getPeriodName()); fileName2.append("关键业绩考核指标得分明细"); zos.putNextEntry(new ZipEntry(fileName2+".xlsx")); zos.write(excel2); zos.closeEntry(); StringBuffer fileName3 = new StringBuffer(); fileName3.append("省管产业单位"); fileName3.append(byPeriodCode.getPeriodName()); fileName3.append("经营业绩考核各单位绩效指标加减分情况说明"); zos.putNextEntry(new ZipEntry(fileName3+".xlsx")); zos.write(excel3); zos.closeEntry(); // 完成并关闭 ZIP 输出流 zos.finish(); zos.close(); } public byte[] captureExport(ExportRo exportRo, String method) throws IOException { // 创建一个 ByteArrayServletOutputStream 用来捕获文件内容 ByteArrayServletOutputStream bos = new ByteArrayServletOutputStream(); // 创建一个伪造的 HttpServletResponse 来接收输出 HttpServletResponse response = new HttpServletResponseWrapper(new HttpServletResponse() { @Override public String getCharacterEncoding() { return ""; } @Override public String getContentType() { return ""; } @Override public ServletOutputStream getOutputStream() throws IOException { return bos; // 返回自定义的 ByteArrayServletOutputStream } @Override public PrintWriter getWriter() throws IOException { return null; } @Override public void setCharacterEncoding(String charset) { } @Override public void setContentLength(int len) { } @Override public void setContentLengthLong(long len) { } // 其他方法保持不变 @Override public void setContentType(String type) {} @Override public void setBufferSize(int size) { } @Override public int getBufferSize() { return 0; } @Override public void flushBuffer() throws IOException { } @Override public void resetBuffer() { } @Override public boolean isCommitted() { return false; } @Override public void reset() { } @Override public void setLocale(Locale loc) { } @Override public Locale getLocale() { return null; } @Override public void addCookie(Cookie cookie) { } @Override public boolean containsHeader(String name) { return false; } @Override public String encodeURL(String url) { return ""; } @Override public String encodeRedirectURL(String url) { return ""; } @Override public String encodeUrl(String url) { return ""; } @Override public String encodeRedirectUrl(String url) { return ""; } @Override public void sendError(int sc, String msg) throws IOException { } @Override public void sendError(int sc) throws IOException { } @Override public void sendRedirect(String location) throws IOException { } @Override public void setDateHeader(String name, long date) { } @Override public void addDateHeader(String name, long date) { } @Override public void setHeader(String name, String value) {} @Override public void addHeader(String name, String value) { } @Override public void setIntHeader(String name, int value) { } @Override public void addIntHeader(String name, int value) { } @Override public void setStatus(int sc) { } @Override public void setStatus(int sc, String sm) { } @Override public int getStatus() { return 0; } @Override public String getHeader(String name) { return ""; } @Override public Collection<String> getHeaders(String name) { return Collections.emptyList(); } @Override public Collection<String> getHeaderNames() { return Collections.emptyList(); } // 实现其他必要的方法，或者使用 Mock 的实现 }); // 调用对应的方法来导出数据并写入 ByteArrayOutputStream switch (method) { case "exportUnitPerformanceScores": exportUnitPerformanceScores(exportRo, response); break; case "exportPerformanceDetails": exportPerformanceDetails(exportRo, response); break; case "explainUnitPerfIndAddSub": explainUnitPerfIndAddSub(exportRo, response); break; default: throw new IllegalArgumentException("Unknown export method"); } return bos.toByteArray(); }
这里比较核心的是JJExcelUtil这里的类实现
public class JJExcelUtil {
public static <T> void write(HttpServletResponse response, String filename, String sheetName, Class<T> head, List<T> data, WriteHandler writeHandler) throws IOException {
String downloadFile = URLEncoder.encode(filename, "UTF-8");
response.addHeader("Content-disposition", "attachment;filename*=utf-8''" + downloadFile);
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel");
EasyExcelFactory.write(response.getOutputStream(), head).autoCloseStream(false).registerWriteHandler(writeHandler).sheet(sheetName).doWrite(data);
}
public static <T> void write(HttpServletResponse response, String filename, String sheetName, List<List<String>> head, List<T> data, WriteHandler writeHandler) throws IOException {
String downloadFile = URLEncoder.encode(filename, "UTF-8");
response.addHeader("Content-disposition", "attachment;filename*=utf-8''" + downloadFile);
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel");
EasyExcelFactory.write(response.getOutputStream()).head(head).autoCloseStream(false).registerWriteHandler(writeHandler).sheet(sheetName).doWrite(data);
}
public static <T> void write(HttpServletResponse response, String filename, String sheetName, Class<T> head, List<T> data) throws IOException {
String downloadFile = URLEncoder.encode(filename, "UTF-8");
response.addHeader("Content-disposition", "attachment;filename*=utf-8''" + downloadFile);
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel");
((ExcelWriterBuilder) EasyExcelFactory.write(response.getOutputStream(), head).autoCloseStream(false).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())).sheet(sheetName).doWrite(data);
}
public static <T> void write(HttpServletResponse response, String filename, String sheetName, List<List<String>> head, List<T> data) throws IOException {
String downloadFile = URLEncoder.encode(filename, "UTF-8");
response.addHeader("Content-disposition", "attachment;filename*=utf-8''" + downloadFile);
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel");
((ExcelWriterBuilder) ((ExcelWriterBuilder) EasyExcelFactory.write(response.getOutputStream()).head(head)).autoCloseStream(false).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())).sheet(sheetName).doWrite(data);
}
/**
* 导出多个 sheet 页的 Excel
*
* @param response HTTP 响应对象
* @param filename 文件名
* @param sheetDataMap 包含多个 sheet 页数据的 Map，key 是 sheetName，value 是数据内容
* @param headClass 每个 sheet 页对应的表头实体类
* @param fontName 字体名称
* @param relativeHeadRowIndex 每个 sheet从几行开始写
* @param useIndexStandardStyleHandler 每个 sheet首行是否需要加标题
* @param titleFontSize 每个 要设置的标题字号大小
* @param contentFontSize 每个 要设置的内容字号大小
* @param <T> 数据类型
* @throws IOException IO 异常
*/
public static <T> void writeMultipleSheets(HttpServletResponse response, String filename,
Map<String, List<T>> sheetDataMap, Class<T> headClass,String fontName,Integer relativeHeadRowIndex,
boolean useIndexStandardStyleHandler,Integer boldLines,short titleFontSize,short contentFontSize) throws IOException {
// 设置响应头
String downloadFile = URLEncoder.encode(filename, "UTF-8");
response.addHeader("Content-disposition", "attachment;filename*=utf-8''" + downloadFile+ ".xlsx");
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel");
// 创建 ExcelWriter
try (ExcelWriter excelWriter = EasyExcelFactory.write(response.getOutputStream(), headClass).autoCloseStream(false).build()) {
int sheetNo = 0; // sheet 索引
for (Map.Entry<String, List<T>> entry : sheetDataMap.entrySet()) {
String sheetName = entry.getKey(); // sheet 页名称
List<T> data = entry.getValue(); // 当前 sheet 页的数据
// 创建 WriteSheet
WriteSheet writeSheet = EasyExcelFactory.writerSheet(sheetNo, sheetName).head(headClass)
.relativeHeadRowIndex(relativeHeadRowIndex)
.registerWriteHandler(new CelllineStyleHandler())
// .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(new FontStyleHandler(fontName,boldLines,titleFontSize,contentFontSize)).build();
// 根据条件动态添加 IndexStandardStyleHandler
if (useIndexStandardStyleHandler) {
writeSheet.getCustomWriteHandlerList().add(new IndexStandardStyleHandler(filename));
}
excelWriter.write(data, writeSheet);
sheetNo++;
}
excelWriter.finish();
}
}
/**
* 导出多个 sheet 页的 Excel（自定义表头）
*
* @param response HTTP 响应对象
* @param filename 文件名
* @param sheetDataMap 包含多个 sheet 页数据的 Map，key 是 sheetName，value 是数据内容（Map 类型）
* @param headListMap 每个 sheet 页的表头（Map 类型，每个 sheet 页的表头独立）
* @throws IOException IO 异常
*/
public static void writeMultipleSheets(HttpServletResponse response, String filename,
Map<String, List<Map<Integer, String>>> sheetDataMap,
Map<String, List<List<String>>> headListMap) throws IOException {
// 设置响应头
String downloadFile = URLEncoder.encode(filename, "UTF-8");
response.addHeader("Content-disposition", "attachment;filename*=utf-8''" + downloadFile + ".xlsx");
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel");
// 创建 ExcelWriter
try (ExcelWriter excelWriter = EasyExcelFactory.write(response.getOutputStream()).autoCloseStream(false).build()) {
int sheetNo = 0; // sheet 索引
for (Map.Entry<String, List<Map<Integer, String>>> entry : sheetDataMap.entrySet()) {
String sheetName = entry.getKey(); // 当前 sheet 页名称
List<Map<Integer, String>> entryValue = entry.getValue(); // 当前 sheet 页的数据
// 获取当前 sheet 页的表头
List<List<String>> currentHeadList = headListMap.get(sheetName);
if (currentHeadList == null) {
throw new IllegalArgumentException("未找到 sheetName 对应的表头: " + sheetName);
}
// 整理数据行
List<List<String>> result = new ArrayList<>();
for (Map<Integer, String> row : entryValue) {
List<String> rowData = new ArrayList<>();
for (int i = 0; i < currentHeadList.size(); i++) {
rowData.add(row.getOrDefault(i, "")); // 如果某列数据不存在，填充空字符串
}
result.add(rowData);
}
// 设置表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 11);
headWriteCellStyle.setWriteFont(headWriteFont);
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontName("宋体");
contentWriteFont.setFontHeightInPoints((short) 11);
contentWriteCellStyle.setWriteFont(contentWriteFont);
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
// 创建 WriteSheet
WriteSheet writeSheet = EasyExcelFactory.writerSheet(sheetNo, sheetName).head(currentHeadList)
.registerWriteHandler(horizontalCellStyleStrategy)
.registerWriteHandler(new CelllineStyleHandler())
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(new WrapCellWriteHandler())
.build();
excelWriter.write(result, writeSheet);
sheetNo++;
}
excelWriter.finish();
}
}
public static <T> List<T> read(MultipartFile file, Class<T> head) throws IOException {
ZipSecureFile.setMinInflateRatio(-1.0D);
InputStream inputStream = null;
try {
return EasyExcel.read(inputStream = file.getInputStream(), head, null).sheet().headRowNumber(1).doReadSync();
} finally {
if (inputStream != null) {
inputStream.close();
}
}
}
public static <T> List<T> read(MultipartFile file, Class<T> head, int row) throws IOException {
ZipSecureFile.setMinInflateRatio(-1.0D);
InputStream inputStream = null;
try {
return EasyExcel.read(inputStream = file.getInputStream(), head, null).sheet().headRowNumber(row).doReadSync();
} finally {
if (inputStream != null) {
inputStream.close();
}
}
}
public static <T> List<T> read(MultipartFile file, Integer headRowNumber) throws IOException {
ZipSecureFile.setMinInflateRatio(-1.0D);
InputStream inputStream = null;
try {
return EasyExcel.read(inputStream = file.getInputStream()).autoCloseStream(false).sheet().headRowNumber(headRowNumber).doReadSync();
} finally {
if (inputStream != null) {
inputStream.close();
}
}
}
public static List<Map<Integer, String>> readExcel(InputStream inputStream, String sheetName) {
List<Map<Integer, String>> dataList = new ArrayList<>();
EasyExcel.read(inputStream, new AnalysisEventListener<Map<Integer, String>>() {
@Override
public void invoke(Map<Integer, String> rowMap, AnalysisContext context) {
try {
// 处理每一行的数据
if (rowMap != null) {
dataList.add(rowMap);
}
} catch (Exception e) {
System.err.println("Error processing row: " + rowMap);
e.printStackTrace();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
System.out.println("所有数据读取完成");
}
})
.sheet(sheetName)
.doRead();
return dataList;
}
/**
* 导出组织绩效合约制定指标
*/
public static String exportIndexOrgExcel(String fileName, List<EasyExcelObj> easyExcelObjList) throws IOException {
// 获取临时目录
String tempDir = System.getProperty("java.io.tmpdir");
String filePath = tempDir + File.separator + fileName;
// 设置文件名称
ExcelWriter excelWriter = EasyExcelFactory.write(filePath).inMemory(true).build();
for (EasyExcelObj easyExcelObj : easyExcelObjList) {
String sheetName0 = getSheetNmae(easyExcelObj.getSheetName0());
WriteSheet sheet0 = EasyExcel.writerSheet(sheetName0)
.head(easyExcelObj.getHead0())
.build();
excelWriter.write(easyExcelObj.getDataList0(), sheet0);
}
excelWriter.finish();
return filePath;
}
/**
* 进行URL编码和解码，确保特殊字符能处理
*
* @param sheetName
* @return
* @throws UnsupportedEncodingException
*/
private static String getSheetNmae(String sheetName) throws UnsupportedEncodingException {
return URLDecoder.decode(URLEncoder.encode(sheetName, "UTF-8").replaceAll("\\+", "%20"), "UTF-8");
}
}
具体的相关注册类代码
public class WrapCellWriteHandler implements CellWriteHandler {
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
// 获取当前单元格
Cell cell = context.getCell();
String value = StringUtils.isBlank(cell.getStringCellValue()) ? "" : cell.getStringCellValue();
List<WriteCellData<?>> cellDataList = context.getCellDataList();
for (int i = 0; i < cellDataList.size(); i++) {
WriteCellData<?> writeCellData = cellDataList.get(i);
WriteCellStyle writeCellStyle = writeCellData.getWriteCellStyle();
WriteFont font = new WriteFont();
if (cell.getRowIndex() == 0){
if (cell.getColumnIndex() == 0){
// 第一行字体：默认方正仿宋_GBK，字号20
font.setFontName("宋体");
font.setBold(true);
font.setFontHeightInPoints((short) 11);
} else if (cell.getColumnIndex() == 1) {
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11);
font.setBold(true);
} else {
font.setFontName("方正仿宋_GBK");
font.setFontHeightInPoints((short) 20);
}
}else if (cell.getRowIndex() == 1) {
font.setFontName("宋体");
font.setBold(true);
font.setFontHeightInPoints((short) 11);
}else {
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11);
}
writeCellStyle.setWriteFont(font);
}
// 获取当前行的索引
int rowIndex = cell.getRowIndex();
// 获取当前列的索引
int columnIndex = cell.getColumnIndex();
// 设置列宽
if (columnIndex == 0) {
// 第一列宽度设置为 26
cell.getSheet().setColumnWidth(columnIndex, 26 * 256);
} else if (columnIndex == 1) {
// 第二列宽度设置为 29
cell.getSheet().setColumnWidth(columnIndex, 29 * 256);
} else {
// 后面的列宽度设置为 10
cell.getSheet().setColumnWidth(columnIndex, 10 * 256);
}
// 如果是第一行，设置行高为 58
if (rowIndex == 0) {
cell.getRow().setHeight((short) 650);
}
}
}
public class ByteArrayServletOutputStream extends ServletOutputStream {
private ByteArrayOutputStream byteArrayOutputStream;
public ByteArrayServletOutputStream() {
this.byteArrayOutputStream = new ByteArrayOutputStream();
}
@Override
public boolean isReady() {
return false;
}
@Override
public void setWriteListener(WriteListener writeListener) {
}
@Override
public void write(int b) throws IOException {
byteArrayOutputStream.write(b);
}
@Override
public void write(byte[] b) throws IOException {
byteArrayOutputStream.write(b);
}
@Override
public void write(byte[] b, int off, int len) throws IOException {
byteArrayOutputStream.write(b, off, len);
}
public byte[] toByteArray() {
return byteArrayOutputStream.toByteArray();
}
}
具体的样式实现
