public String arrearagePeriodExport(ArrearageParam param) {param.setPageNo(1);param.setPageSize(Integer.MAX_VALUE);PageVo<ArrearagePeriodVo> page = queryArrearagePeriod(param);List<ArrearagePeriodVo> list = page.getRecords();// 数据处理// 1.数据分组Map<String, List<ArrearagePeriodVo>> map = list.stream().collect(Collectors.groupingBy(ArrearagePeriodVo::getPayerName));// 2.按照分组求和Map<String, BigDecimal> collects = map.entrySet().stream().collect(Collectors.toMap(Map.Entry::getKey, m -> m.getValue().stream().map(ArrearagePeriodVo::getSubtotalArrears).reduce(BigDecimal.ZERO, BigDecimal::add)));// 3.组装数据list.forEach(p -> {for (Map.Entry<String, BigDecimal> entry : collects.entrySet()) {if (p.getPayerName().equals(entry.getKey())) {p.setTotalArrears(entry.getValue());}}});// 计算总和// 欠款总额合计BigDecimal reduce = list.stream().map(ArrearagePeriodVo::getTotalArrears).distinct().reduce(BigDecimal.ZERO, BigDecimal::add);list.get(0).setSumTotalArrears(reduce);// 欠款小计合计BigDecimal reduce1 = list.stream().map(ArrearagePeriodVo::getSubtotalArrears).reduce(BigDecimal.ZERO, BigDecimal::add);list.get(0).setSumSubtotalArrears(reduce1);// 本金金额合计BigDecimal reduce2 = list.stream().map(ArrearagePeriodVo::getBillUnpaidAmt).reduce(BigDecimal.ZERO, BigDecimal::add);list.get(0).setSumBillUnpaidAmt(reduce2);// 违约金合计BigDecimal reduce3 = list.stream().map(ArrearagePeriodVo::getLateFeeUnpaidAmt).reduce(BigDecimal.ZERO, BigDecimal::add);list.get(0).setSumLateFeeUnpaidAmt(reduce3);return exportSafeConfess( list);}
public String exportSafeConfess( List<ArrearagePeriodVo> list) {String[] tableTitle = {"房产", "缴费客户", "账期", "房屋类型", "欠款总额", "产品类型", "欠款小计", "本金金额", "违约金", "资源名称", "资源简称", "项目"};int widthAttr[] = {30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30,30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30};String titleHead = "欠费账期明细表";List<Map<String, String>> dataList = new ArrayList<>();for (ArrearagePeriodVo vo : list) {Map<String, String> temp = null;temp = new HashMap<>();temp.put("房产", vo.getHouseName());temp.put("缴费客户", vo.getPayerName());temp.put("账期", vo.getAccountPeriod());temp.put("房屋类型", vo.getHouseType());temp.put("欠款总额", vo.getTotalArrears() + "");temp.put("产品类型", vo.getProductName());temp.put("欠款小计", vo.getSubtotalArrears() + "");temp.put("本金金额", vo.getBillUnpaidAmt() + "");temp.put("违约金", vo.getLateFeeUnpaidAmt() + "");temp.put("资源名称", vo.getResName());temp.put("资源简称", vo.getShortName());temp.put("项目", vo.getName());dataList.add(temp);}Map<String/* 此处的key为每个sheet的名称，一个excel中可能有多个sheet页 */, List<Map<String/* 此处key对应每一列的标题 */, String>>/* 该list为每个sheet页的数据 */> map = Maps.newHashMap();map.put("欠费账期明细表", dataList);return createExcel( tableTitle, titleHead, widthAttr, map, new int[]{0, 1, 2, 3, 4,}/* 此处数组为需要合并的列，可能有的需求是只需要某些列里面相同内容合并 */, list);}
public String createExcel( String[] title, String titleHead, int[] widthAttr, Map<String/*sheet名*/, List<Map<String/*对应title的值*/, String>>> maps, int[] mergeIndex, List<ArrearagePeriodVo> lists) {if (title.length == 0) {return null;}/*初始化excel模板*/Workbook workbook = new XSSFWorkbook();Sheet sheet = null;int n = 0;/*循环sheet页*/for (Map.Entry<String, List<Map<String/*对应title的值*/, String>>> entry : maps.entrySet()) {/*实例化sheet对象并且设置sheet名称，book对象*/try {sheet = workbook.createSheet();workbook.setSheetName(n, entry.getKey());workbook.setSelectedTab(0);} catch (Exception e) {e.printStackTrace();}// 设置样式 头 cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 水平方向的对齐方式CellStyle cellStyle_head = style(0, workbook);// 导出时间CellStyle cellStyle_export = style(3, workbook);// 标题CellStyle cellStyle_title = style(1, workbook);// 正文CellStyle cellStyle = style(2, workbook);// 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列CellRangeAddress c1 = new CellRangeAddress(0, 0, 0, title.length - 1);sheet.addMergedRegion(c1);CellRangeAddress c2 = new CellRangeAddress(1, 1, 0, title.length - 1);sheet.addMergedRegion(c2);// 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个Row row0 = sheet.createRow(0);// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个Cell cell1 = row0.createCell(0);// 设置单元格内容 标题cell1.setCellValue(titleHead);cell1.setCellStyle(cellStyle_head);// 设置合并单元格边框setRegionStyle(sheet, c1, cellStyle_head);setRegionStyle(sheet, c2, cellStyle_export);// 设置列宽for (int i = 0; i < widthAttr.length; i++) {sheet.setColumnWidth((short) i, (short) widthAttr[i] * 200);}// 在sheet里创建第二行Row row1 = sheet.createRow(1);// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个Cell cell2 = row1.createCell(0);// 设置单元格内容 标题String serviceName = lists.get(0).getServiceName();cell2.setCellValue("服务中心:" + serviceName);cell2.setCellStyle(cellStyle_export);/*初始化标题，填值标题行（第一行）*/Row row2 = sheet.createRow(2);for (int i = 0; i < title.length; i++) {/*创建单元格，指定类型*/Cell cell_1 = row2.createCell(i, Cell.CELL_TYPE_STRING);//设置标题的值cell_1.setCellValue(title[i]);//设置标题样式cell_1.setCellStyle(cellStyle_title);}/*得到当前sheet下的数据集合*/List<Map<String/*对应title的值*/, String>> list = entry.getValue();/*遍历该数据集合*/List<PoiModel> poiModels = Lists.newArrayList();if (null != workbook) {Iterator iterator = list.iterator();//int index = 1;/*这里1是从excel的第二行开始，第一行已经塞入标题了*/int index = 3;/*这里3是从excel的第四行开始，前面几行已经塞入标题了*/while (iterator.hasNext()) {Row row = sheet.createRow(index);/*取得当前这行的map，该map中以key，value的形式存着这一行值*/@SuppressWarnings("unchecked")Map<String, String> map = (Map<String, String>) iterator.next();/*循环列数，给当前行塞值*/for (int i = 0; i < title.length; i++) {String old = "";/*old存的是上一行统一位置的单元的值，第一行是最上一行了，所以从第二行开始记*/if (index > 3) {old = poiModels.get(i) == null ? "" : poiModels.get(i).getContent();}/*循环需要合并的列*/for (int j = 0; j < mergeIndex.length; j++) {/* 因为标题行前还有2行 所以index从3开始 也就是第四行*/if (index == 3) {/*记录第一行的开始行和开始列*/PoiModel poiModel = new PoiModel();poiModel.setOldContent(map.get(title[i]));poiModel.setContent(map.get(title[i]));poiModel.setRowIndex(3);poiModel.setCellIndex(i);poiModels.add(poiModel);break;} else if (i > 0 && mergeIndex[j] == i) {/*这边i>0也是因为第一列已经是最前一列了，只能从第二列开始*//*当前同一列的内容与上一行同一列不同时，把那以上的合并, 或者在当前元素一样的情况下，前一列的元素并不一样，这种情况也合并*//*如果不需要考虑当前行与上一行内容相同，但是它们的前一列内容不一样则不合并的情况，把下面条件中||poiModels.get(i).getContent().equals(map.get(title[i])) && !poiModels.get(i - 1).getOldContent().equals(map.get(title[i-1]))去掉就行*/if (!poiModels.get(i).getContent().equals(map.get(title[i])) || poiModels.get(i).getContent().equals(map.get(title[i])) && !poiModels.get(i - 1).getOldContent().equals(map.get(title[i - 1]))) {/*当前行的当前列与上一行的当前列的内容不一致时，则把当前行以上的合并*/CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex()/*从第二行开始*/, index - 1/*到第几行*/, poiModels.get(i).getCellIndex()/*从某一列开始*/, poiModels.get(i).getCellIndex()/*到第几列*/);//在sheet里增加合并单元格sheet.addMergedRegion(cra);/*重新记录该列的内容为当前内容，行标记改为当前行标记，列标记则为当前列*/poiModels.get(i).setContent(map.get(title[i]));poiModels.get(i).setRowIndex(index);poiModels.get(i).setCellIndex(i);}}/*处理第一列的情况*/if (mergeIndex[j] == i && i == 0 && !poiModels.get(i).getContent().equals(map.get(title[i]))) {/*当前行的当前列与上一行的当前列的内容不一致时，则把当前行以上的合并*/CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex()/*从第二行开始*/, index - 1/*到第几行*/, poiModels.get(i).getCellIndex()/*从某一列开始*/, poiModels.get(i).getCellIndex()/*到第几列*/);//在sheet里增加合并单元格sheet.addMergedRegion(cra);/*重新记录该列的内容为当前内容，行标记改为当前行标记*/poiModels.get(i).setContent(map.get(title[i]));poiModels.get(i).setRowIndex(index);poiModels.get(i).setCellIndex(i);}/*最后一行没有后续的行与之比较，所有当到最后一行时则直接合并对应列的相同内容 加2是因为标题行前面还有2行*/if (mergeIndex[j] == i && index == list.size() + 2) {CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex()/*从第二行开始*/, index/*到第几行*/, poiModels.get(i).getCellIndex()/*从某一列开始*/, poiModels.get(i).getCellIndex()/*到第几列*/);//在sheet里增加合并单元格sheet.addMergedRegion(cra);}}Cell cell = row.createCell(i, Cell.CELL_TYPE_STRING);cell.setCellValue(map.get(title[i]));cell.setCellStyle(cellStyle);/*在每一个单元格处理完成后，把这个单元格内容设置为old内容*/poiModels.get(i).setOldContent(old);}index++;}}n++;}int columnLength = 0;for (List<Map<String, String>> value : maps.values()) {columnLength = value.size() + 3;}Row row = sheet.createRow(columnLength);int totalCell = sheet.getRow(3).getLastCellNum();ArrearagePeriodVo vo = lists.get(0);for (int i = 0; i < totalCell; i++) {Cell cell1 = row.createCell(i);if (i == 4) {cell1.setCellValue(vo.getSumTotalArrears() + "");}if (i == 6) {cell1.setCellValue(vo.getSumSubtotalArrears() + "");}if (i == 7) {cell1.setCellValue(vo.getSumBillUnpaidAmt() + "");}if (i == 8) {cell1.setCellValue(vo.getSumLateFeeUnpaidAmt() + "");}cell1.setCellStyle(style(2, workbook));if (i == 3) {cell1.setCellValue("合计:");}// sheet.addMergedRegion(new CellRangeAddress(columnLength,columnLength+1,0,3));}FileOutputStream out = null;File outFile = null;String localPath = null;try {// Calendar calendar1 = Calendar.getInstance();// String cal = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(calendar1.getTime());// out = response.getOutputStream();// response.reset();//清空输出流// response.setHeader("Content-disposition", "attachment;filename=" + new String(titleHead.getBytes("gbk"), "iso8859-1") + cal + ".xlsx");// 设定输出文件头// response.setContentType("application/vnd.ms-excel;charset=GBK");// 定义输出类型outFile = File.createTempFile("欠费账期明细表", ".xlsx");out = new FileOutputStream(outFile);workbook.write(out);} catch (IOException e) {e.printStackTrace();} finally {try {out.flush();out.close();} catch (IOException e) {e.printStackTrace();}}String code = CodingUtil.createUUID();fileService.upload(code, outFile, "欠费账期明细表.xlsx");return code;}
/*** @param @return 设定文件 index 0:头 1：标题 2：正文* @return HSSFCellStyle 返回类型* @throws* @Title: style* @Description: TODO(样式)* @author: GMY*/public CellStyle style(int index, Workbook workbook) {CellStyle cellStyle = workbook.createCellStyle();if (index == 0) {// 设置头部样式cellStyle = workbook.createCellStyle();// // 设置字体大小 位置// cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 生成一个字体Font font = workbook.createFont();//设置字体font.setFontName("微软雅黑");Font f = workbook.createFont();f.setFontHeightInPoints((short) 10);f.setColor(IndexedColors.BLACK.getIndex());f.setBold(true);cellStyle.setFont(f);cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);cellStyle.setWrapText(true);}//标题if (index == 1) {// 设置头部样式cellStyle = workbook.createCellStyle();// // 设置字体大小 位置// cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 生成一个字体Font font = workbook.createFont();//设置字体font.setFontName("微软雅黑");Font f = workbook.createFont();f.setFontHeightInPoints((short) 10);f.setColor(IndexedColors.BLACK.getIndex());f.setBold(true);cellStyle.setFont(f);cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);cellStyle.setWrapText(true);}//正文if (index == 2) {// 设置头部样式cellStyle = workbook.createCellStyle();// // 设置字体大小 位置cellStyle.setAlignment(HorizontalAlignment.CENTER);// 生成一个字体Font font = workbook.createFont();//设置字体font.setFontName("微软雅黑");Font f = workbook.createFont();f.setFontHeightInPoints((short) 10);f.setColor(IndexedColors.BLACK.getIndex());f.setBold(true);cellStyle.setFont(f);cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);cellStyle.setWrapText(true);}//时间if (index == 3) {// 设置头部样式cellStyle = workbook.createCellStyle();// 生成一个字体Font font = workbook.createFont();//设置字体font.setFontName("微软雅黑");Font f = workbook.createFont();f.setFontHeightInPoints((short) 10);f.setColor(IndexedColors.BLACK.getIndex());f.setBold(true);cellStyle.setFont(f);cellStyle.setAlignment(HorizontalAlignment.LEFT);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);cellStyle.setWrapText(true);}return cellStyle;}
/*** @param @param sheet* @param @param region* @param @param cs 设定文件* @return void 返回类型* @throws* @Title: setRegionStyle* @Description: TODO(合并单元格后边框不显示问题)* @author: GMY*/public void setRegionStyle(Sheet sheet, CellRangeAddress region, CellStyle cs) {for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {Row row = CellUtil.getRow(i, sheet);for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {Cell cell = CellUtil.getCell(row, (short) j);cell.setCellStyle(cs);}}}
import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;@Builder@NoArgsConstructor@AllArgsConstructor@Datapublic class PoiModel {private String content;private String oldContent;private String primaryKey;private int rowIndex;private int cellIndex;}
