阅读量:1
POI实现单元格行合并
实现效果
如果最后面的三行数据大于1时 那么前面十二行就需要行合并
引入jar
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.2.2</version> </dependency> 代码实现
controller层
@PostMapping(value = "getExcel") public void getExcel(@RequestBody BrucellosisListDTO brucellosisListDTO, HttpServletRequest request, HttpServletResponse response){ businessTaskBrucellosisService.getExcel(brucellosisListDTO,request,response); } Service层
void getExcel(BrucellosisListDTO brucellosisListDTO, HttpServletRequest request, HttpServletResponse response); ServiceImpl层实现类
@Override public void getExcel(BrucellosisListDTO brucellosisListDTO, HttpServletRequest request, HttpServletResponse response) { //数据来源 List<BrucellosisExportExcel> list = queryExcelList(brucellosisListDTO); //数据来源 通过参数传入 String[] masterHead = {"姓名","养殖户类型","手机号码","人口数","所在区域(省)","所在区域(市)","所在区域(区/县)","所在区域(乡镇)","所在区域(乡村)","防疫负责人","养殖总数","布病人数"}; String[] childHead = {"布病人员","布病人手机号码","布病人身份证号码"}; int[] widthColumn = new int[]{20,20,25,15,30,30,30,20,30,30,20,15,25,50,50,50}; //创建Excel工作薄对象 XSSFWorkbook workbook=new XSSFWorkbook(); //创建Excel工作表对象 Sheet sheet = workbook.createSheet("布病人员统计"); //设置单元格居中 CellStyle cellStyle = workbook.createCellStyle(); //设置边框样式 cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中 //创建行的单元格，从0开始 Row row = sheet.createRow(0); //创建统计单元格 Cell masterCell=row.createCell(0); //设置第一个表头样式 CellStyle cellStyleHead = workbook.createCellStyle(); cellStyleHead.setAlignment(HorizontalAlignment.CENTER); cellStyleHead.setVerticalAlignment(VerticalAlignment.CENTER); cellStyleHead.setBorderBottom(BorderStyle.THIN); cellStyleHead.setBorderLeft(BorderStyle.THIN); cellStyleHead.setBorderRight(BorderStyle.THIN); cellStyleHead.setBorderTop(BorderStyle.THIN); cellStyleHead.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex()); cellStyleHead.setFillPattern(FillPatternType.SOLID_FOREGROUND); //赋值 masterCell.setCellValue("养殖户布病人员基本信息统计"); masterCell.setCellStyle(cellStyleHead); //合并列 CellRangeAddress region = new CellRangeAddress(0, 0, 0, (masterHead.length+childHead.length-1)); sheet.addMergedRegion(region); row.setHeight((short) (3*200)); // 设置列宽 for (int i = 0; i <widthColumn.length; i++) { sheet.setColumnWidth((short) i, (short) widthColumn[i] * 200); } //表头 从1开始 设置第二个表头样式 Row titleRow = sheet.createRow(1); //主表头 for (int i = 0; i < masterHead.length ; i++) { Cell msCell = titleRow.createCell(i); msCell.setCellStyle(cellStyleHead); msCell.setCellValue(masterHead[i]); } //子表头 for (int i = 0; i < childHead.length; i++) { Cell chcell = titleRow.createCell(masterHead.length+i); chcell.setCellStyle(cellStyleHead); chcell.setCellValue(childHead[i]); } //封装数据 excelPoi(list,sheet,cellStyle,region); String fileName = null; try { fileName = URLEncoder.encode("患布病人员统计","UTF-8"); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx"); response.setHeader("filename", fileName + ".xlsx"); response.setHeader("Pragma", "public"); response.setHeader("Cache-Control", "no-store"); response.addHeader("Cache-Control", "max-age=0"); response.setHeader("Access-Control-Expose-Headers","Content-Disposition"); OutputStream os = response.getOutputStream(); workbook.write(os); os.flush(); os.close(); workbook.close(); } catch (Exception e) { throw new RuntimeException(e); } } //数据封装以及塞值方法 public void excelPoi(List<BrucellosisExportExcel> list, Sheet sheet,CellStyle cellStyle, CellRangeAddress region){ //封装父表和子表对象 Map<String, List<BrucellosisExportExcel>> farmerPhoneMap = list.stream().collect(Collectors.groupingBy(BrucellosisExportExcel::getFarmerPhone)); Set<String> strings = farmerPhoneMap.keySet(); List<String> farmerPhoneList = new ArrayList<>(strings); List<Map<BrucellosisExportExcel,List<BrucellosisExportExcel>>> masterList = new ArrayList<>(); Map<BrucellosisExportExcel,List<BrucellosisExportExcel>> map = new HashMap(); for (int i = 0; i < farmerPhoneList.size(); i++) { List<BrucellosisExportExcel> brucellosisExportExcels = farmerPhoneMap.get(farmerPhoneList.get(i)); //养殖户基本信息 BrucellosisExportExcel brucellosisExportExcel = brucellosisExportExcels.get(0); map.put(brucellosisExportExcel,brucellosisExportExcels); } masterList.add(map); //填充数据 int lastRowIndex = 2; //记录最后行位置 for (Map<BrucellosisExportExcel,List<BrucellosisExportExcel>> m : masterList){ for (BrucellosisExportExcel key : m.keySet()){ List<BrucellosisExportExcel> chlist = m.get(key); Row valueRow = sheet.createRow(lastRowIndex); Cell farmerNameCell = valueRow.createCell(0); farmerNameCell.setCellStyle(cellStyle); farmerNameCell.setCellValue(key.getFarmerName()); if (chlist.size()>1){ //子表数量大于1才进行 行合并 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 0, 0); sheet.addMergedRegion(region); } Cell farmerTypeNameCell = valueRow.createCell(1); farmerTypeNameCell.setCellStyle(cellStyle); farmerTypeNameCell.setCellValue(key.getFarmerTypeName()); if (chlist.size()>1){ //子表数量大于1才进行 行合并 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 1, 1); sheet.addMergedRegion(region); } Cell farmerPhoneCell = valueRow.createCell(2); farmerPhoneCell.setCellStyle(cellStyle); farmerPhoneCell.setCellValue(key.getFarmerPhone()); if (chlist.size()>1){ //子表数量大于1才进行 行合并 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 2, 2); sheet.addMergedRegion(region); } Cell populationCell = valueRow.createCell(3); populationCell.setCellStyle(cellStyle); populationCell.setCellValue(key.getPopulation()); if (chlist.size()>1){ //子表数量大于1才进行 行合并 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 3, 3); sheet.addMergedRegion(region); } Cell provinceNameCell = valueRow.createCell(4); provinceNameCell.setCellStyle(cellStyle); provinceNameCell.setCellValue(key.getProvinceName()); if (chlist.size()>1){ //子表数量大于1才进行 行合并 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 4, 4); sheet.addMergedRegion(region); } Cell cityNameCell = valueRow.createCell(5); cityNameCell.setCellStyle(cellStyle); cityNameCell.setCellValue(key.getCityName()); if (chlist.size()>1){ //子表数量大于1才进行 行合并 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 5, 5); sheet.addMergedRegion(region); } Cell areaNameCell = valueRow.createCell(6); areaNameCell.setCellStyle(cellStyle); areaNameCell.setCellValue(key.getAreaName()); if (chlist.size()>1){ //子表数量大于1才进行 行合并 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 6, 6); sheet.addMergedRegion(region); } Cell townNameCell = valueRow.createCell(7); townNameCell.setCellStyle(cellStyle); townNameCell.setCellValue(key.getTownshipName()); if (chlist.size()>1){ //子表数量大于1才进行 行合并 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 7, 7); sheet.addMergedRegion(region); } Cell streetNameCell = valueRow.createCell(8); streetNameCell.setCellStyle(cellStyle); streetNameCell.setCellValue(key.getStreetName()); if (chlist.size()>1){ //子表数量大于1才进行 行合并 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 8, 8); sheet.addMergedRegion(region); } Cell personInChargeNameCell = valueRow.createCell(9); personInChargeNameCell.setCellStyle(cellStyle); personInChargeNameCell.setCellValue(key.getPersonInChargeName()); if (chlist.size()>1){ //子表数量大于1才进行 行合并 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 9, 9); sheet.addMergedRegion(region); } Cell animalNumCell = valueRow.createCell(10); animalNumCell.setCellStyle(cellStyle); animalNumCell.setCellValue(key.getAnimalNum()); if (chlist.size()>1){ //子表数量大于1才进行 行合并 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 10, 10); sheet.addMergedRegion(region); } Cell bruNumCell = valueRow.createCell(11); bruNumCell.setCellStyle(cellStyle); bruNumCell.setCellValue(key.getBruNum()); if (chlist.size()>1){ //子表数量大于1才进行 行合并 region=new CellRangeAddress(lastRowIndex, lastRowIndex+chlist.size()-1, 11, 11); sheet.addMergedRegion(region); } for (int i = 0; i < chlist.size(); i++) { BrucellosisExportExcel brucellosisExportExcel = chlist.get(i); Row chRow; if (i == 0){ //避免重复创建 覆盖主表数据 chRow = valueRow; }else { chRow = sheet.createRow(lastRowIndex); } lastRowIndex++; Cell userNameCell = chRow.createCell(12); userNameCell.setCellStyle(cellStyle); userNameCell.setCellValue(brucellosisExportExcel.getUserName()); Cell usePhoneCell = chRow.createCell(13); usePhoneCell.setCellStyle(cellStyle); usePhoneCell.setCellValue(brucellosisExportExcel.getUserPhone()); Cell idCardCell = chRow.createCell(14); idCardCell.setCellStyle(cellStyle); idCardCell.setCellValue(brucellosisExportExcel.getIdCard()); } } } } 备注： 由于我这里要合并的列比较多而且牵扯到每一列都不同，所以使用了一行一行去校验和合并的方法；当然 如果需要合并的就只有一行 可以在 excelPoi的第二个for循环里面使用循环合并的方式。
