java使用poi自定义excel标题头并导出

时间：2023-07-08

1、创建XSSFWorkbook对象（也就是excel文档对象） 2、通过XSSFWorkbook对象创建sheet对象（也就是excel中的sheet） 3、通过sheet对象创建XSSFRow对象（row行对象） 4、通过XSSFRow对象创建列cell并set值（列名） 5、处理数据循环表头(业务需要)

service impl层

@Override public StatisticalReportResultDTO getStatisticalExcelReport(StatisticalReportQuery query) { // 查询条件 StatisticalReportPageVO queryVO = StatisticalReportConverter.queryToDO(query); LambdaQueryWrapper queryWrapper = new LambdaQueryWrapper(); // 年月转换为时间段 if (query.getStatisticalPeriodStartTime() != null && query.getStatisticalPeriodEndTime() != null) { queryVO.setStatisticalPeriodStartTime(query.getStatisticalPeriodStartTime()); queryVO.setStatisticalPeriodEndTime(query.getStatisticalPeriodEndTime()); } if (StringUtils.isNotBlank(query.getTaxId())) { String[] taxIds = query.getTaxId().split(","); queryVO.setTaxId(taxIds); } queryWrapper.setEntity(queryVO); // 列表 List list = statisticalReportMapper.getStatisticalExcelReport(queryWrapper); StatisticalReportResultDTO statisticalReportResultDTO = new StatisticalReportResultDTO(); StatisticalReportDTO total = new StatisticalReportDTO(); if (CollectionUtils.isNotEmpty(list)) { BigDecimal zero = BigDecimal.ZERO; BigDecimal amount = zero, districtRetainedAmount = zero, districtIncentiveAmountReceivable = zero, districtRewardAmountActuallyReceived = zero, tnIncome = zero, intermediaryIncome = zero, enterpriseIncome = zero; for (StatisticalReportDTO vo : list) { //比例回显 if (vo.getDistrictRetentionRatio() != null) { BigDecimal districtRetentionRatio = vo.getDistrictRetentionRatio(); BigDecimal percentage = new BigDecimal(100); BigDecimal newDistrictRetentionRatio = districtRetentionRatio.multiply(percentage).setScale(2, RoundingMode.HALF_UP);//保留两位小数; vo.setDistrictRetentionRatio(newDistrictRetentionRatio); } if (vo.getDistrictRewardRatio() != null) { BigDecimal districtRewardRatio = vo.getDistrictRewardRatio(); BigDecimal percentage = new BigDecimal(100); BigDecimal newDistrictRewardRatio = districtRewardRatio.multiply(percentage).setScale(2, RoundingMode.HALF_UP);//保留两位小数; vo.setDistrictRewardRatio(newDistrictRewardRatio); } // 计算总合计 amount = amount.add(vo.getAmount() != null ? vo.getAmount() : zero); districtRetainedAmount = districtRetainedAmount.add(vo.getDistrictRetainedAmount() != null ? vo.getDistrictRetainedAmount() : zero); districtIncentiveAmountReceivable = districtIncentiveAmountReceivable .add(vo.getDistrictIncentiveAmountReceivable() != null ? vo.getDistrictIncentiveAmountReceivable() : zero); districtRewardAmountActuallyReceived = districtRewardAmountActuallyReceived .add(vo.getDistrictRewardAmountActuallyReceived() != null ? vo.getDistrictRewardAmountActuallyReceived() : zero); tnIncome = tnIncome.add(vo.getTnIncome() != null ? vo.getTnIncome() : zero); intermediaryIncome = intermediaryIncome.add(vo.getIntermediaryIncome() != null ? vo.getIntermediaryIncome() : zero); enterpriseIncome = enterpriseIncome.add(vo.getEnterpriseIncome() != null ? vo.getEnterpriseIncome() : zero); } total.setAmount(amount); total.setDistrictRewardAmountActuallyReceived(districtRewardAmountActuallyReceived); total.setDistrictRetainedAmount(districtRetainedAmount); total.setDistrictIncentiveAmountReceivable(districtIncentiveAmountReceivable); total.setTnIncome(tnIncome); total.setIntermediaryIncome(intermediaryIncome); total.setEnterpriseIncome(enterpriseIncome); } statisticalReportResultDTO.setList(list); statisticalReportResultDTO.setTotal(total); return statisticalReportResultDTO; }

controller 层

@ApiOperation(value = "获取Excel导出数据作者：jlk 日期： 2022年01月27日") @GetMapping(value = "/getExcelExport") public void getExcelExport(HttpServletRequest request, HttpServletResponse response, StatisticalReportQuery query) { StatisticalReportResultDTO statisticalReportResultDTO = statisticalReportService.getStatisticalExcelReport(query); List statisticalReportConditionDTOList = statisticalReportService.getExcelReportCondition(query); String fileName = "总部经济-统计报表" + System.currentTimeMillis(); //调用poi导出Excel ExcelUtil.poiExportExcel(statisticalReportResultDTO, statisticalReportConditionDTOList, query, fileName, response); }

ExcelUtil工具类

@SuppressWarnings("resource") public static void poiExportExcel(StatisticalReportResultDTO statisticalReportResultDTO, List statisticalReportConditionDTOList, StatisticalReportQuery query, String fileName, HttpServletResponse response) { try { List statisticalReportList = statisticalReportResultDTO.getList(); XSSFWorkbook wb = new XSSFWorkbook(); XSSFRow row = null; XSSFCell cell = null; // 建立新的sheet对象（excel的表单）并设置sheet名字 XSSFSheet sheet = wb.createSheet("统计报表"); sheet.setDefaultRowHeightInPoints(20); sheet.setDefaultColumnWidth(12); //----------------标题样式-------------------------------- XSSFCellStyle titleStyle = wb.createCellStyle(); //标题样式 titleStyle.setAlignment(HorizontalAlignment.CENTER); titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); Font ztFont = wb.createFont(); ztFont.setItalic(false); // 设置字体为斜体字 //ztFont.setColor(Font.COLOR_NORMAL); // 将字体设置为“红色” ztFont.setFontHeightInPoints((short) 16); // 将字体大小设置为18px ztFont.setFontName("宋体"); // 将“宋体”字体应用到当前单元格上 ztFont.setBold(true); //加粗 //ztFont.setUnderline(Font.U_DOUBLE);// 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线） //ztFont.setStrikeout(true);// 是否添加删除线 titleStyle.setFont(ztFont); //------------------------------------------------------------ //----------------二级标题格样式---------------------------------- XSSFCellStyle titleStyle2 = wb.createCellStyle(); //表格样式 titleStyle2.setAlignment(HorizontalAlignment.CENTER); titleStyle2.setVerticalAlignment(VerticalAlignment.CENTER); titleStyle2.setBorderBottom(BorderStyle.THIN);//下边框 titleStyle2.setBorderLeft(BorderStyle.THIN);//左边框 titleStyle2.setBorderRight(BorderStyle.THIN);//右边框 titleStyle2.setBorderTop(BorderStyle.THIN);//上边框 Font ztFont2 = wb.createFont(); ztFont2.setItalic(false); // 设置字体为斜体字 ztFont2.setColor(Font.COLOR_NORMAL); // 将字体设置为“红色” ztFont2.setFontHeightInPoints((short) 10); // 将字体大小设置为18px ztFont2.setFontName("宋体"); // 字体应用到当前单元格上 ztFont2.setBold(true); //加粗 //ztFont.setUnderline(Font.U_DOUBLE);// 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线） //ztFont.setStrikeout(true);// 是否添加删除线 titleStyle2.setFont(ztFont2); titleStyle2.setWrapText(true);//设置自动换行 //---------------------------------------------------------- //----------------单元格样式----------------------------------- XSSFCellStyle cellStyle = wb.createCellStyle(); //表格样式 cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN);//下边框 cellStyle.setBorderLeft(BorderStyle.THIN);//左边框 cellStyle.setBorderRight(BorderStyle.THIN);//右边框 cellStyle.setBorderTop(BorderStyle.THIN);//上边框 Font cellFont = wb.createFont(); cellFont.setItalic(false); // 设置字体为斜体字 cellFont.setColor(Font.COLOR_NORMAL); // 将字体设置为“红色” cellFont.setFontHeightInPoints((short) 9); // 将字体大小设置为18px cellFont.setFontName("宋体"); // 字体应用到当前单元格上 cellStyle.setFont(cellFont); cellStyle.setWrapText(true);//设置自动换行 //---------------------------------------------------------- // ----------------------创建第一行---------------------------- // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个 row = sheet.createRow(0); // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个 cell = row.createCell(0); // 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列，截至列 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10)); // 设置单元格内容 String titleName = "天能招引企业财政奖励及分配明细"; if (query.getStatisticalPeriodStartTime() != null && query.getStatisticalPeriodEndTime() != null) { LocalDateTime startTime = query.getStatisticalPeriodStartTime(); LocalDateTime endTime = query.getStatisticalPeriodEndTime(); String startY = String.valueOf(startTime.getYear()); String startM = String.valueOf(startTime.getMonthValue()); String endY = String.valueOf(endTime.getYear()); String endM = String.valueOf(endTime.getMonthValue()); titleName = titleName + "(" + startY + "年" + startM + "月-" + endY + "年" + endM + "月)"; } cell.setCellValue(titleName); cell.setCellStyle(titleStyle); // -------------------------------------------------- // ------------------处理数据start--------------------- if (CollectionUtils.isNotEmpty(statisticalReportConditionDTOList)) { int rowNum = 1; for (int i = 0; i < statisticalReportConditionDTOList.size(); i++) { StatisticalReportConditionDTO statisticalReportConditionDTO = statisticalReportConditionDTOList.get(i); String name = ""; if (!StringUtils.isEmpty(statisticalReportConditionDTO.getDealerName())) { name = statisticalReportConditionDTO.getDealerName(); } rowNum = rowNum + i; creatCell(sheet, row, cell, titleStyle2, rowNum, name); rowNum = rowNum + 1; if (CollectionUtils.isNotEmpty(statisticalReportList)) { BigDecimal zero = BigDecimal.ZERO; BigDecimal amount = zero, districtRetainedAmount = zero, districtIncentiveAmountReceivable = zero, districtRewardAmountActuallyReceived = zero, tnIncome = zero, intermediaryIncome = zero, enterpriseIncome = zero; int mergeSize = 0; String dealerId = ""; for (int j = 0; j < statisticalReportList.size(); j++) { List