JAVA使用POI实现Excel单元格合并-02

JAVA使用POI实现Excel单元格合并

实现效果

解释：只要是遇见与前一行相同的数据就合并

引入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.getExcel1(brucellosisListDTO,request,response);}

Service

 void getExcel1(BrucellosisListDTO brucellosisListDTO, HttpServletRequest request, HttpServletResponse response);

serviceImpl

 @Overridepublic void getExcel1(BrucellosisListDTO brucellosisListDTO, HttpServletRequest request, HttpServletResponse response) {List<BrucellosisExportExcel> list = queryExcelList(brucellosisListDTO);//表头String[] titleAttr = {"姓名","养殖户类型","手机号","人口数","所在区域(省)","所在区域(市)","所在区域(区/县)","所在区域(乡镇)","所在区域(乡村)","防疫负责人","养殖总数","布病人数","布病人员","布病人手机号码","布病人身份证号码"};//设置单元的宽度int[] widthAttr = {30,30,30,30,50,30,30,30,30,30,30,30,30,30,30};String titleHead = "布病统计";List<Map<String, String>> dataList = new ArrayList<>();if (CollectionUtils.isNotEmpty(list)){LinkedHashMap<String, List<BrucellosisExportExcel>> collect = list.stream().collect(Collectors.groupingBy(BrucellosisExportExcel::getFarmerPhone, LinkedHashMap::new, Collectors.toList()));dataFarmer(collect,dataList);}Map<String,List<Map<String, String>>> map = Maps.newHashMap();map.put("布病统计", dataList);ExportExcelByPoiUtil.createExcel(response,titleAttr,titleHead,widthAttr,map,new int[]{0,1,2,3,4,5,6,7,8,9,10,11});}public List<BrucellosisExportExcel> queryExcelList(BrucellosisListDTO brucellosisListDTO) {//查询对应的养殖户信息List<BrucellosisExportExcel> list = businessTaskBrucellosisMapper.queryExcelList(brucellosisListDTO);//养殖户下患布病的人数List<BrucellosisFarmerNumVO> brucellosisFarmerNumVOList = businessTaskBrucellosisMapper.queryBruCount();if(!brucellosisFarmerNumVOList.isEmpty()){list.forEach(res ->{String farmerPhone = res.getFarmerPhone();List<BrucellosisFarmerNumVO> collect = brucellosisFarmerNumVOList.stream().filter(result -> result.getFarmerPhone().equals(farmerPhone)).collect(Collectors.toList());if(ObjectUtils.isNotEmpty(collect)){res.setBruNum(collect.get(0).getBruNum());}});}return list;}/***合并数据* @param collect 根据养殖户Id分组* @date 2024/3/21 17:18* @author fyh**/public  void dataFarmer(LinkedHashMap<String, List<BrucellosisExportExcel>> collect,List<Map<String, String>> dataList){Set<String> longs = collect.keySet();List<String> farmerIdList = new ArrayList<>(longs);for (int i = 0; i < farmerIdList.size(); i++) {List<BrucellosisExportExcel> list1 = collect.get(farmerIdList.get(i));dataVoWorkOrder(dataList,list1);}}private void dataVoWorkOrder(List<Map<String, String>> dataList, List<BrucellosisExportExcel> list) {if (CollectionUtils.isNotEmpty(list)) {for (BrucellosisExportExcel displayBrucellosisVO : list) {Map<String, String> temp = new HashMap<>();temp.put("姓名", displayBrucellosisVO.getFarmerName());temp.put("养殖户类型", displayBrucellosisVO.getFarmerTypeName());temp.put("手机号", displayBrucellosisVO.getFarmerPhone());temp.put("人口数", displayBrucellosisVO.getPopulation()+"");temp.put("所在区域(省)", displayBrucellosisVO.getProvinceName());temp.put("所在区域(市)", displayBrucellosisVO.getCityName());temp.put("所在区域(区/县)", displayBrucellosisVO.getAreaName());temp.put("所在区域(乡镇)", displayBrucellosisVO.getTownshipName());temp.put("所在区域(乡村)", displayBrucellosisVO.getStreetName());temp.put("防疫负责人", displayBrucellosisVO.getPersonInChargeName());temp.put("养殖总数", displayBrucellosisVO.getAnimalNum()+"");temp.put("布病人数", displayBrucellosisVO.getBruNum()+"");temp.put("布病人员", displayBrucellosisVO.getUserName());temp.put("布病人手机号码", displayBrucellosisVO.getUserPhone());temp.put("布病人身份证号码", displayBrucellosisVO.getIdCard());dataList.add(temp);}}}

ExportExcelByPoiUtil 合并的公共类

public class ExportExcelByPoiUtil {private static Logger logger = LoggerFactory.getLogger(ExportExcelByPoiUtil.class);/*** @param @param request* @param @param response* @param @param title 标题数组* @param @param titleHead  Excel标题* @param @param widthAttr  单元格宽度* @param @param maps  数据* @param @param mergeIndex  要合并的列   数组* @param @return    设定文件* @return String    返回类型* @throws*/@SuppressWarnings("rawtypes")public static void createExcel(HttpServletResponse response,String[] title,String titleHead ,int[] widthAttr,Map<String/*sheet名*/, List<Map<String/*对应title的值*/, String>>> maps, int[] mergeIndex){if (title.length==0){return;}/*初始化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.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);// 设置单元格内容 标题//cell2.setCellValue("导出时间：" + DateUtil.getDateString(DateUtil.DATE_TIME_PATTERN) );cell2.setCellValue("导出时间："+ DateUtil.now());cell2.setCellStyle(cellStyle_export);/*初始化标题，填值标题行（第一行）*/Row row2 = sheet.createRow(2);for(int i = 0; i<title.length; i++){/*创建单元格，指定类型*///Cell cell_1 = row2.createCell(i, Cell.class.CELL_TYPE_STRING);Cell cell_1 = row2.createCell(i,CellType.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]))去掉就行*///|| 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]))){if(index - 1 ==poiModels.get(i).getRowIndex() && poiModels.get(i).getCellIndex() == poiModels.get(i).getCellIndex()){continue;}/*当前行的当前列与上一行的当前列的内容不一致时，则把当前行以上的合并*/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]))){if(index - 1 ==poiModels.get(i).getRowIndex() && poiModels.get(i).getCellIndex() == poiModels.get(i).getCellIndex()){continue;}/*当前行的当前列与上一行的当前列的内容不一致时，则把当前行以上的合并*/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){if(index == poiModels.get(i).getRowIndex() && poiModels.get(i).getCellIndex() == poiModels.get(i).getCellIndex()){continue;}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,CellType.STRING);cell.setCellValue(map.get(title[i]));cell.setCellStyle(cellStyle);/*在每一个单元格处理完成后，把这个单元格内容设置为old内容*/poiModels.get(i).setOldContent(old);}index++;}}n++;}OutputStream out = 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(StandardCharsets.UTF_8), "iso8859-1") + cal + ".xlsx");// 设定输出文件头response.setContentType("application/vnd.ms-excel;charset=UTF-8");// 定义输出类型workbook.write(out);}catch (IOException e){logger.error("导出异常",e);}finally {try {out.flush();out.close();}catch (IOException e){logger.error("流的关闭异常",e);}}}/*** @param @return    设定文件  index 0:头 1：标题 2：正文* @return HSSFCellStyle    返回类型* @throws*/public static CellStyle style(int index, Workbook workbook) {CellStyle cellStyle = null;if (index == 0) {// 设置头部样式cellStyle = workbook.createCellStyle();// 设置字体大小 位置cellStyle.setAlignment(HorizontalAlignment.CENTER);// 生成一个字体Font font = workbook.createFont();//设置字体font.setFontName("微软雅黑");//字体颜色font.setColor(IndexedColors.BLACK.getIndex());// HSSFColor.VIOLET.indexfont.setFontHeightInPoints((short) 12);// 上下居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//背景白色cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());//这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法展示背景色，头默认了 FillPatternType所以可以不指定cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置边框线cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);//设置对其cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setFont(font);}//标题if (index == 1) {cellStyle = workbook.createCellStyle();// 设置字体大小 位置cellStyle.setAlignment(HorizontalAlignment.CENTER);// 生成一个字体Font font_title = workbook.createFont();//设置字体font_title.setFontName("微软雅黑");font_title.setColor(IndexedColors.BLACK.getIndex());// HSSFColor.VIOLET.index//字体颜色font_title.setFontHeightInPoints((short) 10);cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法展示背景色，头默认了 FillPatternType所以可以不指定cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置边框样式cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);//设置对其cellStyle.setAlignment(HorizontalAlignment.CENTER);// 上下居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setFont(font_title);}//正文if (index == 2) {// 设置样式cellStyle = workbook.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER);// 生成一个字体Font font_title = workbook.createFont();//设置字体font_title.setFontName("微软雅黑");cellStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());cellStyle.setWrapText(true); // 自动换行cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//背景白色cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());//设置边框样式cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中}//时间if (index == 3) {// 设置样式cellStyle = workbook.createCellStyle();// 居中cellStyle.setAlignment(HorizontalAlignment.RIGHT);// 生成一个字体Font font_title = workbook.createFont();//设置字体font_title.setFontName("微软雅黑");font_title.setColor(IndexedColors.BLACK.getIndex());// HSSFColor.VIOLET.index// //字体颜色font_title.setFontHeightInPoints((short) 10);//font_title.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 上下居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置单元格格式cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setFont(font_title);}if (index == 4) {// 设置样式cellStyle = workbook.createCellStyle();//设置背景色cellStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置边框样式cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中}return cellStyle;}/*** @param @param sheet* @param @param region* @param @param cs    设定文件* @return void    返回类型* @throws*/public static 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);}}}
}

PoiModel 实体类

/*** @package: com.ruoyi.easyExcelHeadStyle* @program: prevention* @author: fyh* @date: 2024/3/21* @description: 用来记录上一行数据**/
@Data
public class PoiModel {//内容private String content;//上一行同一位置内容private String oldContent;//行标private int rowIndex;//列标private int cellIndex;
}