java使用poi生成Excel文件并合并单元格
业务需要根据分管部门字段进行合并，现在提供一种思路。
controller层
@Inject(target = "/infoResourcesManageRest/custom/batchDetailExcelExport", type = InjectTypeExt.CUSTOM_URL)public WSResult<?> batchDetailExcelExport(JSONObject jsonObject) throws FileNotFoundException, UnsupportedEncodingException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {String savePath = downloadBasePath;String fileName = UUID.randomUUID() + "人才公寓情况表.xls";SearchFilter filter = SearchFilter.newSearchFilter(jsonObject);filter.setSortField("rcgyshjs").setSortDir("asc");List<IdEntity> list = FormDataManagerUtils.findAllByFilter("personApartmentApply", filter);String outPath = savePath + fileName;OutputStream os = null;File file = new File(savePath);if (!file.exists()) {file.mkdirs();}os = new FileOutputStream(outPath);String title = "人才公寓情况表";int sheetNum = 1;// 工作薄sheet编号int bodyRowCount = 2;// 正文内容行号int currentRowCount = 1;// 当前的行号int perPageNum = 50000;// 每个工作薄显示50000条数据String filename = new String(("人才公寓情况表.xls").getBytes("utf-8"), "ISO-8859-1");os = new FileOutputStream(outPath);// 输出流HSSFWorkbook workbook = new HSSFWorkbook();// 创建excelHSSFSheet sheet = workbook.createSheet(title + sheetNum);// 创建一个工作薄ExportExcel exportExcel = new ExportExcel();ExportExcel.setBatchDetailSheetColumn(sheet);// 设置工作薄列宽HSSFRow row = null;// 创建一行HSSFCell cell = null;// 每个单元格HSSFCellStyle titleCellStyle = ExportExcel.createTitleCellStyle(workbook);ExportExcel.batchDetail(sheet, titleCellStyle, workbook);// 写入标题// 第二行开始写入数据HSSFCellStyle bodyCellStyle = ExportExcel.createBodyCellStyle(workbook);bodyCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框bodyCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框bodyCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框bodyCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框bodyCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中bodyCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中bodyCellStyle.setWrapText(true);HSSFCellStyle dateBobyCellStyle = ExportExcel.createDateBodyCellStyle(workbook);int i = 1;LinkedList<String> strs = new LinkedList<>();LinkedHashSet<String> set = new LinkedHashSet<>();for (IdEntity entity : list) {String rcgyshjs = BeanUtils.getProperty(entity, "rcgyshjs");//13.分管部门String name = BeanUtils.getProperty(entity, "name");//姓名String xb = BeanUtils.getProperty(entity, "xb");//性别String gzdw = BeanUtils.getProperty(entity, "gzdw");//企业名称String zgxw = BeanUtils.getProperty(entity, "zgxw");//4.最高学位String zc = BeanUtils.getProperty(entity, "zc");//5.职称String rzzw = BeanUtils.getProperty(entity, "rzzw");//5.最高学位、职务等String lxfs = BeanUtils.getProperty(entity, "lxfs");//6.联系方式String rccc = BeanUtils.getProperty(entity, "rccc");//7.专家复审意见（人才类别）String fjlx = BeanUtils.getProperty(entity, "fjlx");//8.入住房间类型String mj = BeanUtils.getProperty(entity, "mj");//9.面积String zfzt = BeanUtils.getProperty(entity, "zfzt");//10.是否入住String fjh = BeanUtils.getProperty(entity, "fjh");//11.房间号String bz = BeanUtils.getProperty(entity, "bz");//12.备注row = sheet.createRow(bodyRowCount);cell = row.createCell(0);// 序号cell.setCellStyle(bodyCellStyle);cell.setCellValue(i++);cell = row.createCell(1);// 分管部门cell.setCellStyle(bodyCellStyle);cell.setCellValue(rcgyshjs);cell = row.createCell(2);// 姓名cell.setCellStyle(bodyCellStyle);cell.setCellValue(name);cell = row.createCell(3);// 性别cell.setCellStyle(bodyCellStyle);cell.setCellValue(xb);cell = row.createCell(4);// 企业名称cell.setCellStyle(bodyCellStyle);cell.setCellValue(gzdw);cell = row.createCell(5);// 最高学位cell.setCellStyle(bodyCellStyle);cell.setCellValue(zgxw);cell = row.createCell(6);// 职称cell.setCellStyle(bodyCellStyle);cell.setCellValue(zc);cell = row.createCell(7);// 最高学位、职务等cell.setCellStyle(bodyCellStyle);cell.setCellValue(rzzw);cell = row.createCell(8);// 联系方式cell.setCellStyle(bodyCellStyle);cell.setCellValue(lxfs);cell = row.createCell(9);// 专家复审意见（人才类别）cell.setCellStyle(bodyCellStyle);cell.setCellValue(rccc);cell = row.createCell(10);// 入住房间类型cell.setCellStyle(bodyCellStyle);cell.setCellValue(fjlx);cell = row.createCell(11);// 面积cell.setCellStyle(bodyCellStyle);cell.setCellValue(mj);cell = row.createCell(12);// 是否入住cell.setCellStyle(bodyCellStyle);cell.setCellValue(zfzt);cell = row.createCell(13);// 房间号cell.setCellStyle(bodyCellStyle);cell.setCellValue(fjh);cell = row.createCell(14);// 备注cell.setCellStyle(bodyCellStyle);cell.setCellValue(bz);if (currentRowCount % perPageNum == 0) {// 每个工作薄显示50000条数据sheet = null;sheetNum++;// 工作薄编号递增1sheet = workbook.createSheet(title + sheetNum);// 创建一个新的工作薄ExportExcel.setBatchDetailSheetColumn(sheet);// 设置工作薄列宽bodyRowCount = 3;// 正文内容行号置位为0ExportExcel.batchDetail(sheet, titleCellStyle, workbook);// 写入标题}bodyRowCount++;// 正文内容行号递增1currentRowCount++;// 当前行号递增1strs.add(rcgyshjs);set.add(rcgyshjs);}//找到需要合并单元格的规律，Integer num = 2;Map<String, List<String>> collect = strs.stream().collect(Collectors.groupingBy(s -> s, Collectors.toList()));for (String s : set) {for (Map.Entry<String, List<String>> m : collect.entrySet()) {if (s.equals(m.getKey())) {System.out.println("888888888888888888" + m);//合并第二列，根据list数组中的 rcgyshjs 字段分组合并sheet.addMergedRegion(new CellRangeAddress(num, num + m.getValue().size() - 1, 1, 1));num = num + m.getValue().size();}}}try {workbook.write(os);os.flush();} catch (IOException e) {e.printStackTrace();} finally {try {os.close();} catch (IOException e) {e.printStackTrace();}}return WSResult.successResult(outPath);}
Excel工具类
```javapublic class ExportExcel {/*** 设置标题单元样式** @param workbook* @return*/public static HSSFCellStyle createTitleCellStyle(HSSFWorkbook workbook) {HSSFCellStyle cellStyle = workbook.createCellStyle();HSSFFont font = workbook.createFont();font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);font.setFontHeightInPoints((short) 12);font.setFontName(HSSFFont.FONT_ARIAL);// 设置标题字体cellStyle.setFont(font);cellStyle.setWrapText(true);cellStyle = workbook.createCellStyle();cellStyle.setFont(font);// 设置列标题样式cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);// 设置背景色cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居中return cellStyle;}/*** 设置正文单元样式** @param workbook* @return*/public static HSSFCellStyle createBodyCellStyle(HSSFWorkbook workbook) {HSSFCellStyle cellStyle = workbook.createCellStyle();HSSFFont font = workbook.createFont();font.setFontHeightInPoints((short) 14);//font.setFontName("宋体");// 设置标题字体font.setFontName(HSSFFont.FONT_ARIAL);// 设置标题字体cellStyle.setFont(font);cellStyle = workbook.createCellStyle();cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居中return cellStyle;}/*** 设置正文单元时间样式** @param workbook* @return*/public static HSSFCellStyle createDateBodyCellStyle(HSSFWorkbook workbook) {HSSFCellStyle cellStyle = workbook.createCellStyle();HSSFFont font = workbook.createFont();font.setFontHeightInPoints((short) 12);font.setFontName(HSSFFont.FONT_ARIAL);// 设置标题字体cellStyle.setFont(font);cellStyle = workbook.createCellStyle();cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居中HSSFDataFormat format = workbook.createDataFormat();cellStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));return cellStyle;}/*** -07-14 17:41:39* 设置表格宽度* **/public static void setBatchDetailSheetColumn(HSSFSheet sheet) {sheet.setDefaultRowHeight((short) 500);sheet.setColumnWidth((short) 0, (short) 3000);// 设置 序号 宽度sheet.setColumnWidth((short) 1, (short) 5000);// 设置 分管部门 宽度sheet.setColumnWidth((short) 2, (short) 5000);// 设置 姓名 宽度sheet.setColumnWidth((short) 3, (short) 5000);// 设置 性别 宽度sheet.setColumnWidth((short) 4, (short) 5000);// 设置 企业名称 宽度sheet.setColumnWidth((short) 5, (short) 5000);// 设置 最高学位 宽度sheet.setColumnWidth((short) 6, (short) 5000);// 设置 职称 宽度sheet.setColumnWidth((short) 7, (short) 5000);// 设置 最高学位、职务等 宽度sheet.setColumnWidth((short) 8, (short) 5000);// 设置 联系方式 宽度sheet.setColumnWidth((short) 9, (short) 5000);// 设置 专家复审意见（人才类别） 宽度sheet.setColumnWidth((short) 10, (short)5000);// 设置 入住房间类型 宽度sheet.setColumnWidth((short) 11, (short)5000);// 设置 面积 宽度sheet.setColumnWidth((short) 12, (short)5000);// 设置 是否入住 宽度sheet.setColumnWidth((short) 13, (short)5000);// 设置 房间号 宽度sheet.setColumnWidth((short) 14, (short)5000);// 设置 备注 宽度}/*** -07-14 17:42:03* 设置表头* **/public static void batchDetail(HSSFSheet sheet, HSSFCellStyle cellStyle, HSSFWorkbook workbook) {HSSFCellStyle bcs = ExportExcel.createTitleCellStyle(workbook);bcs.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框bcs.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框bcs.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框bcs.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框bcs.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中bcs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中bcs.setWrapText(true);HSSFRow row = null;HSSFCell cell = null;cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中cellStyle.setWrapText(true);/*第一行*/sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14));//2.申请年度单位/*第二行*/sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 0)); //1.序号sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 1)); //2.分管部门sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 2)); //3.姓名sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 3)); //4.性别sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 4)); //5.企业名称sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 5)); //6.最高学位sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 6)); //7.职称sheet.addMergedRegion(new CellRangeAddress(1, 1, 7, 7)); //8.最高学位、职务等sheet.addMergedRegion(new CellRangeAddress(1, 1, 8, 8)); //9.联系方式sheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 9)); //10.专家复审意见（人才类别）sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 10)); //11.入住房间类型sheet.addMergedRegion(new CellRangeAddress(1, 1, 11, 11)); //12.面积sheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 12));//13.是否入住sheet.addMergedRegion(new CellRangeAddress(1, 1, 13, 13));//14.房间号sheet.addMergedRegion(new CellRangeAddress(1, 1, 14, 14));//15.备注/*第一行塞值*/row = sheet.createRow(0);cell = row.createCell(0);// IDcell.setCellStyle(cellStyle);cell.setCellValue("高新区入住人才公寓人员表情况表");/*第二行塞值*/row = sheet.createRow(1);cell = row.createCell(0);cell.setCellStyle(cellStyle);cell.setCellValue("序号");cell = row.createCell(1);cell.setCellStyle(cellStyle);cell.setCellValue("分管部门");cell = row.createCell(2);cell.setCellStyle(cellStyle);cell.setCellValue("姓名");cell = row.createCell(3);cell.setCellStyle(cellStyle);cell.setCellValue("性别");cell = row.createCell(4);cell.setCellStyle(cellStyle);cell.setCellValue("企业名称");cell = row.createCell(5);cell.setCellStyle(cellStyle);cell.setCellValue("最高学位");cell = row.createCell(6);cell.setCellStyle(cellStyle);cell.setCellValue("职称");cell = row.createCell(7);cell.setCellStyle(cellStyle);cell.setCellValue("最高学位、职务等");cell = row.createCell(8);cell.setCellStyle(cellStyle);cell.setCellValue("联系方式");cell = row.createCell(9);cell.setCellStyle(cellStyle);cell.setCellValue("专家复审意见（人才类别）");cell = row.createCell(10);cell.setCellStyle(cellStyle);cell.setCellValue("入住房间类型");cell = row.createCell(11);cell.setCellStyle(cellStyle);cell.setCellValue("面积");cell = row.createCell(12);cell.setCellStyle(cellStyle);cell.setCellValue("是否入住");cell = row.createCell(13);cell.setCellStyle(cellStyle);cell.setCellValue("房间号");cell = row.createCell(14);cell.setCellStyle(cellStyle);cell.setCellValue("备注");}
生成效果如下
其他复杂的效果同理，只需要找到需要合并表格的规律，再算出坐标，就可以进行合并操作。
