Java使用POI将多个Sheet合并为一个Sheet

爱被打了一巴掌 2023-01-20 09:55 10阅读 0赞

## 一。情景描述 ##

最近在项目中客户提到一个新的需求，一开始是在列表查询时导出多个Excel表格，后面提到将多个Excel表格进行合并，实现一个sheet显示多个sheet内容，图示如下：

一开始：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70]

合并后（不同表格空一行隔开）：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 2]

## 二。实现思路 ##

首先，先按照生成五张表的思路来生成创建一个`Workbook sourceWorkbook`,然后再创建一个`Workbook targetWorkbook`，创建一个新的`Sheet targetSheet`工作表，之后将`sourceWorkbook`中第一个`Sheet sheet1`中的内容复制到该表中，再将第二个`Sheet sheet2`中的内容复制到`targetSheet`中，依次操作，复制完`sourceWorkbook`中全部的五张表，即可实现将多个Sheet合并为一个Sheet的操作。

## 三。示例代码 ##

1.**POIUtil工具类**

package com.cdtye.itps.jjxt.model.util;
    import com.cdtye.itps.jjxt.model.excel.CellRangeAddressExcelVo;
    import org.apache.poi.ss.usermodel.*;
    import org.springframework.util.CollectionUtils;
    
    import java.util.List;
    
    /** * @Author Zhongks * @Description //TODO POI导出excel工具类 * @Date 17:16 2021/5/11 * @Param * @return **/
    public class POIUtil { 
        /** * @Author Zhongks * @Description //TODO 拷贝sheet（表） * @Date 17:16 2021/5/11 * @Param [targetSheet, sourceSheet, targetWork, sourceWork, startRow, cellRangeAddressExcelVoList] * @return void **/
        public static void copySheet(Sheet targetSheet, Sheet sourceSheet,
                                     Workbook targetWork, Workbook sourceWork, int startRow,
                                     List<CellRangeAddressExcelVo> cellRangeAddressExcelVoList) { 
            if(targetSheet == null || sourceSheet == null || targetWork == null || sourceWork == null){ 
                throw new IllegalArgumentException("调用PoiUtil.copySheet()方法时，targetSheet、sourceSheet、targetWork、sourceWork都不能为空，故抛出该异常！");
            }
    
            //设置单元格默认宽度
            targetSheet.setDefaultColumnWidth(25);
            //复制源表中的行
            for (int i = sourceSheet.getFirstRowNum(); i <= sourceSheet.getLastRowNum(); i++) { 
                Row sourceRow = sourceSheet.getRow(i);
                Row targetRow = targetSheet.createRow(i+startRow);  //创建新的row
                if (sourceRow != null) { 
                    copyRow(targetRow, sourceRow,
                            targetWork, sourceWork);
                }
            }
    
            //自定义合并单元格样式（若不需要进行单元格合并操作，将cellRangeAddressExcelVoList赋值为null即可）
            if(!CollectionUtils.isEmpty(cellRangeAddressExcelVoList)){ 
                //合并单元格
                for(CellRangeAddressExcelVo model:cellRangeAddressExcelVoList){ 
                    targetSheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(model.getFirstRow(),model.getLastRow(),model.getFirstCol(),model.getLastCol()));
                }
            }
        }
    
    
        /** * @Author Zhongks * @Description //TODO 拷贝row(行) * @Date 17:17 2021/5/11 * @Param [targetRow, sourceRow, targetWork, sourceWork] * @return void **/
        public static void copyRow(Row targetRow, Row sourceRow,
                                   Workbook targetWork, Workbook sourceWork)  { 
            if(targetRow == null || sourceRow == null || targetWork == null || sourceWork == null ){ 
                throw new IllegalArgumentException("调用PoiUtil.copyRow()方法时，targetRow、sourceRow、targetWork、sourceWork、targetPatriarch都不能为空，故抛出该异常！");
            }
    
            //设置行高
            targetRow.setHeight(sourceRow.getHeight());
    
            for (int i = sourceRow.getFirstCellNum(); i < sourceRow.getLastCellNum(); i++) { 
                Cell sourceCell = sourceRow.getCell(i);
                Cell targetCell = null;
    
                if (sourceCell != null && sourceCell.getStringCellValue()!="") { 
                    if (targetCell == null) { 
                        targetCell = targetRow.createCell(i);
                    }
                    //拷贝单元格，包括内容和样式
                    copyCell(targetCell, sourceCell, targetWork, sourceWork);
                }
            }
        }
    
        /** * @Author Zhongks * @Description //TODO 拷贝cell（单元格） * @Date 17:18 2021/5/11 * @Param [targetCell, sourceCell, targetWork, sourceWork] * @return void **/
        public static void copyCell(Cell targetCell, Cell sourceCell, Workbook targetWork, Workbook sourceWork) { 
            if(targetCell == null || sourceCell == null || targetWork == null || sourceWork == null ){ 
                throw new IllegalArgumentException("调用PoiUtil.copyCell()方法时，targetCell、sourceCell、targetWork、sourceWork都不能为空，故抛出该异常！");
            }
    
            CellStyle targetCellStyle=targetWork.createCellStyle();
            targetCellStyle.cloneStyleFrom(sourceCell.getCellStyle());//拷贝样式
            //重新添加样式（这里可以根据你的需要重新进行单元格样式添加）
            /*targetCellStyle.setBorderTop(BorderStyle.THIN);//设置上边框线 targetCellStyle.setBorderLeft(BorderStyle.THIN);//设置左边框线 targetCellStyle.setBorderBottom(BorderStyle.THIN);//设置下边框线 targetCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框线*/
            targetCell.setCellStyle(targetCellStyle);
    
            targetCell.setCellValue(sourceCell.getStringCellValue());
        }
    
    }

2.**需要合并的单元格位置信息实体**

package com.cdtye.itps.jjxt.model.excel;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.experimental.Accessors;
    
    /** * @ClassName CellRangeAddressExcelVo * @Description TODO 需要合并的单元格位置信息Vo * @Author Zhongks * @Date 2021/5/11 14:09 * @Version 1.0 **/
    @Data
    @Accessors(chain = true)
    @AllArgsConstructor
    public class CellRangeAddressExcelVo { 
        //起始行号
        private int firstRow;
        //终止行号
        private int lastRow;
        //起始列号
        private int firstCol;
        //终止列号
        private int lastCol;
    
    }

该实体类是为了进行合并单元格操作，用来存储需要合并的单元格位置信息：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 3]

![在这里插入图片描述][20210512151658598.png]  
**Service层代码：**

*
         * @Author Zhongks
         * @Description //TODO excel导出
         * @Date 12:25 2021/5/7
         * @Param [list, response]
         * @return void
         **/
        public void export(BureauDayShiftVo bureauDayShiftVo,HttpServletResponse response) { 
            try { 
                // 设置下载的Excel名称，以当前时间为文件后缀，
                String dateTime = DateUtil.formatDateString(new Date(), DateUtil.DATE_FORMAT);
                String fileName = "供电安全质量日交班表"+dateTime+".xlsx";
                // 设置响应输出的头类型
                response.setHeader("content-Type", "application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename="+fileName);
    
                // excel信息部分
                //供电处重点信息追踪表信息
                bureauDayShiftVo.setTrackFlag(1);
                Map<String, Object> trackSafeQualityMap =this.getTrackSafeQualityMap(bureauDayShiftVo);
                //日安全质量信息表信息
                bureauDayShiftVo.setTrackFlag(0);
                Map<String, Object> safeQualityParamsMap =this.getTrackSafeQualityMap(bureauDayShiftVo);
                //天窗兑现统计表
                Map<String, Object> skylightCashStatisticsMap = this.getSkylightCashStatisticsMap();
                //其他安全质量信息表
                Map<String, Object> otherSafeQualityInfoMap = this.getOtherSafeQualityInfoMap(bureauDayShiftVo);
                //安全质量考核表
                Map<String, Object> safeQualityAssessmentMap = this.getSafeQualityAssessmentMap();
    
                //添加表
                List<Map<String, Object>> sheetsList = new ArrayList<>();
                sheetsList.add(trackSafeQualityMap);
                sheetsList.add(safeQualityParamsMap);
                sheetsList.add(skylightCashStatisticsMap);
                sheetsList.add(otherSafeQualityInfoMap);
                sheetsList.add(safeQualityAssessmentMap);
    
                List<Map<String, Object>> sourceSheetsList = new ArrayList<>();
    
                //创建excel文件的方法
                Workbook sourceWorkbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
                Workbook targetWorkbook = ExcelExportUtil.exportExcel(sourceSheetsList, ExcelType.HSSF);
                Workbook workbook = this.mergeWorkSheet(targetWorkbook, sourceWorkbook);
                //通过response输出流直接输入给客户端
                ServletOutputStream outputStream = response.getOutputStream();
                workbook.write(outputStream);
                outputStream.flush();
                outputStream.close();
            } catch (Exception e) { 
                e.printStackTrace();
            }
        }
    
    
        /** * @Author Zhongks * @Description //TODO 返回重点追踪以及非重点追踪excel信息 * @Date 9:31 2021/5/8 * @Param [bureauDayShiftVo] * @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>> **/
        public Map<String, Object> getTrackSafeQualityMap(BureauDayShiftVo bureauDayShiftVo){ 
            List<BureauDayShiftExcelVo> exportList = new LinkedList<>();
            List<Map<String, Object>> allTrackSafeQualityList = this.getAllTrackSafeQualityList(bureauDayShiftVo);
            //封装数据
            allTrackSafeQualityList.forEach(map -> { 
                String basicInformation="单位："+map.get("unitDeptName")+"\n"+
                        "线别："+map.get("lineName")+"\n"+
                        "所亭："+map.get("bdsSubstationName")+"\n"+
                        "开关号："+map.get("switchNo")+"\n"+
                        "故障地点："+map.get("faultPlace")+"\n"+
                        "发生时间："+DateUtil.formatDateString(map.get("stopDate"), DateUtil.DATE_FORMAT)+"\n"+
                        "停时（分钟）："+map.get("stopMinute")+"\n"+
                        "天气："+map.get("weatherInfo")+"\n"+
                        "专业分类："+map.get("faultMajorName")+"\n";
                String segmentAnalysis="单位："+map.get("unitDeptName")+"\n"+
                        "单位：详见分析报告"+"\n";
                String isTrack="";
                if(bureauDayShiftVo.getTrackFlag()==0){ 
                    isTrack="否";
                }else{ 
                    isTrack="是";
                }
                String review="科室："+map.get("trackUnitDeptName")+"\n"+
                        "问题类别："+map.get("faultCategoryConfigName")+"\n"+
                        "定责考核："+map.get("dutyType")+"\n"+
                        "审核结果："+map.get("switchNo")+"\n"+
                        "重点追踪："+isTrack+"\n";
                BureauDayShiftExcelVo bureauDayShiftExcelVo =new BureauDayShiftExcelVo(
                        DateUtil.formatDateString(map.get("inputDate"), DateUtil.DATE_FORMAT),
                        basicInformation,
                        (String)map.get("faultDescription"),
                        (String)map.get("reportType"),
                        segmentAnalysis,
                        review,
                        map.get("safeQualityState").toString(),
                        String.valueOf(bureauDayShiftVo.getTrackFlag()));
                exportList.add(bureauDayShiftExcelVo);
            });
    
            ExportParams exportParams = new ExportParams();
            //设置边框样式
    // exportParams.setStyle(ExcelStyleType.BORDER.getClazz());
            // 设置sheet的名称
            if(bureauDayShiftVo.getTrackFlag()==0){ 
                exportParams.setSheetName("日安全质量信息");
            }else{ 
                exportParams.setSheetName("供电处重点追踪信息");
            }
    
            Map<String, Object> map = new HashMap<>();
            // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
            map.put("title", exportParams);
            // 模版导出对应得实体类型，即包含了List的对象
            map.put("entity", BureauDayShiftExcelVo.class);
            // sheet中要填充得数据
            map.put("data", exportList);
            return map;
        }
    
    
        /** * @Author Zhongks * @Description //TODO 返回天窗兑现统计excel信息 * @Date 10:59 2021/5/8 * @Param [] * @return java.util.Map<java.lang.String,java.lang.Object> **/
        public Map<String, Object> getSkylightCashStatisticsMap(){ 
            List<BureauSkylightCashStatisticsExcelVo> exportList = new LinkedList<>();
    
            //ToDo 得到天窗兑现统计列表数据并进行封装
            //示例数据
            BureauSkylightCashStatisticsCommonExcelVo applicationExcelVo=new BureauSkylightCashStatisticsCommonExcelVo("申请供电类","申请非供电类");
            BureauSkylightCashStatisticsCommonExcelVo applicationTimeExcelVo=new BureauSkylightCashStatisticsCommonExcelVo("申请时间供电类","申请时间非供电类");
            BureauSkylightCashStatisticsCommonExcelVo getTimeExcelVo=new BureauSkylightCashStatisticsCommonExcelVo("给点时间供电类","给点时间非供电类");
            BureauSkylightCashStatisticsCommonExcelVo workTimeExcelVo=new BureauSkylightCashStatisticsCommonExcelVo(null,null);
            BureauSkylightCashStatisticsExcelVo bureauSkylightCashStatisticsExcelVo =new BureauSkylightCashStatisticsExcelVo("怀化供电段","高铁","沪昆高速线",
                    applicationExcelVo,"取消","10","10",applicationTimeExcelVo,getTimeExcelVo,workTimeExcelVo,"天窗取消原因");
            exportList.add(bureauSkylightCashStatisticsExcelVo);
            exportList.add(bureauSkylightCashStatisticsExcelVo);
            exportList.add(bureauSkylightCashStatisticsExcelVo);
    
            //供电处重点追踪信息表
            ExportParams exportParams = new ExportParams();
            //设置边框样式
    // exportParams.setStyle(ExcelStyleType.BORDER.getClazz());
            // 设置sheet的名称
            exportParams.setSheetName("天窗兑现统计");
    
            Map<String, Object> map = new HashMap<>();
            // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
            map.put("title", exportParams);
            // 模版导出对应得实体类型，即包含了List的对象
            map.put("entity", BureauSkylightCashStatisticsExcelVo.class);
            // sheet中要填充得数据
            map.put("data", exportList);
            return map;
        }
    
        
        /** * @Author Zhongks * @Description //TODO 返回其他安全信息excel信息 * @Date 11:01 2021/5/8 * @Param [] * @return java.util.Map<java.lang.String,java.lang.Object> **/
        public Map<String, Object> getOtherSafeQualityInfoMap(BureauDayShiftVo bureauDayShiftVo){ 
            List<BureauOtherSafeQualityInfoExcelVo> exportList = new LinkedList<>();
            //ToDo 得到其他安全信息列表数据并进行封装
            BureauSafeQualityOtherInfoVo bureauSafeQualityOtherInfoVo=new BureauSafeQualityOtherInfoVo();
            bureauSafeQualityOtherInfoVo.setStartDate(bureauDayShiftVo.getStartDate());
            bureauSafeQualityOtherInfoVo.setEndDate(bureauDayShiftVo.getEndDate());
            List<Map<String, Object>> list = bureauSafeQualityOtherInfoService.findList(bureauSafeQualityOtherInfoVo);
            list.forEach(map->{ 
                BureauOtherSafeQualityInfoExcelVo otherSafeQualityInfoExcelVo=new BureauOtherSafeQualityInfoExcelVo(
                        DateUtil.formatDateString(map.get("createDatetime"), DateUtil.DATE_FORMAT),
                        (String)map.get("description"),
                        (String)map.get("inputStaffName"),
                        DateUtil.formatDateString(map.get("createDatetime"), DateUtil.DATE_FORMAT),
                        (String)map.get("modifyStaffName"),
                        DateUtil.formatDateString(map.get("updateDatetime"), DateUtil.DATE_FORMAT)
                );
                exportList.add(otherSafeQualityInfoExcelVo);
            });
    
            //供电处重点追踪信息表
            ExportParams exportParams = new ExportParams();
            //设置边框样式
    // exportParams.setStyle(ExcelStyleType.BORDER.getClazz());
            // 设置sheet的名称
            exportParams.setSheetName("其他安全信息");
    
            Map<String, Object> map = new HashMap<>();
            // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
            map.put("title", exportParams);
            // 模版导出对应得实体类型，即包含了List的对象
            map.put("entity", BureauOtherSafeQualityInfoExcelVo.class);
            // sheet中要填充得数据
            map.put("data", exportList);
            return map;
        }
    
    
        /** * @Author Zhongks * @Description //TODO 返回安全质量考核excel信息 * @Date 11:04 2021/5/8 * @Param [] * @return java.util.Map<java.lang.String,java.lang.Object> **/
        public Map<String, Object> getSafeQualityAssessmentMap(){ 
            List<BureauSafeQualityAssessmentExcelVo> exportList = new LinkedList<>();
    
            //ToDo 得到安全质量考核列表数据并进行封装
    
    
            //供电处重点追踪信息表
            ExportParams exportParams = new ExportParams();
            //设置边框样式
    // exportParams.setStyle(ExcelStyleType.BORDER.getClazz());
            // 设置sheet的名称
            exportParams.setSheetName("安全质量考核");
    
            Map<String, Object> map = new HashMap<>();
            // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
            map.put("title", exportParams);
            // 模版导出对应得实体类型，即包含了List的对象
            map.put("entity", BureauSafeQualityAssessmentExcelVo.class);
            // sheet中要填充得数据
            map.put("data", exportList);
            return map;
        }
    
    
        /** * @Author Zhongks * @Description //TODO 合并sheet * @Date 10:39 2021/5/11 * @Param [targetWorkbook, sourceWorkbook] * @return org.apache.poi.ss.usermodel.Workbook **/
        public static Workbook mergeWorkSheet(Workbook targetWorkbook, Workbook sourceWorkbook){ 
            try{ 
                //第一个sheet
                Sheet firstSourceSheet=sourceWorkbook.getSheetAt(0);
                //获得第一个sheet总行数
                int firstSourceSheetLen=firstSourceSheet.getPhysicalNumberOfRows();
                //获取第几个工作表
                Sheet secondSourceSheet= sourceWorkbook.getSheetAt(1);
                int secondSourceSheetLen=secondSourceSheet.getPhysicalNumberOfRows();
                Sheet thirdSourceSheet=sourceWorkbook.getSheetAt(2);
                int thirdSourceSheetLen=thirdSourceSheet.getPhysicalNumberOfRows();
                Sheet fourSourceSheet=sourceWorkbook.getSheetAt(3);
                int fourSourceSheetLen=fourSourceSheet.getPhysicalNumberOfRows();
                Sheet fiveSourceSheet=sourceWorkbook.getSheetAt(4);
                //表合并后新表名称
                Sheet targetSheet = targetWorkbook.createSheet("安全质量信息日交班表");
                //表合并（根据startRow来控制各个表之间的距离，这里为空一行）
                POIUtil.copySheet(targetSheet, firstSourceSheet, targetWorkbook, sourceWorkbook,0,null);
                POIUtil.copySheet(targetSheet, secondSourceSheet, targetWorkbook, sourceWorkbook,firstSourceSheetLen+1,null);
                int thirdSourceSheetColLen=thirdSourceSheet.getRow(0).getPhysicalNumberOfCells();
                //得到需要合并单元格的坐标列表，row与col都从0开始计算
                List<CellRangeAddressExcelVo> cellRangeAddressExcelVoList = getCellRangeAddressExcelVoList(firstSourceSheetLen+secondSourceSheetLen+2, thirdSourceSheetColLen);
                //第三张表需要进行合并单元格操作
                POIUtil.copySheet(targetSheet, thirdSourceSheet, targetWorkbook, sourceWorkbook,firstSourceSheetLen+secondSourceSheetLen+2,cellRangeAddressExcelVoList);
                POIUtil.copySheet(targetSheet, fourSourceSheet, targetWorkbook, sourceWorkbook,firstSourceSheetLen+secondSourceSheetLen+thirdSourceSheetLen+3,null);
                POIUtil.copySheet(targetSheet, fiveSourceSheet, targetWorkbook, sourceWorkbook,firstSourceSheetLen+secondSourceSheetLen+thirdSourceSheetLen+fourSourceSheetLen+4,null);
                return targetWorkbook;
            }catch (Exception e){ 
                log.error("Workbook合并出错",e);
                return null;
            }
        }
    
        /** * @Author Zhongks * @Description //TODO 根据表格场景自定义需要返回合并的单元格位置坐标（注意：row与col都从0开始计算） * @Date 14:23 2021/5/11 * @Param [row, col] * @return java.util.List<com.cdtye.itps.jjxt.model.excel.CellRangeAddressExcelVo> **/
        public static List<CellRangeAddressExcelVo> getCellRangeAddressExcelVoList(int row,int col){ 
            //合并单元格坐标位置
            List<CellRangeAddressExcelVo> list=new LinkedList<>();
            for(int i=0;i<15;i++){ 
                if(i<7){ 
                    CellRangeAddressExcelVo cellRangeAddressExcelVo=new CellRangeAddressExcelVo(row,row+1,i,i);
                    list.add(cellRangeAddressExcelVo);
                }else{ 
                    CellRangeAddressExcelVo cellRangeAddressExcelVo=new CellRangeAddressExcelVo(row,row,i,i+1);
                    list.add(cellRangeAddressExcelVo);
                    i++;
                }
            }
            return list;
        }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70]: /images/20221021/5c9cd14c130d4abfa6954ab4aa1193dd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 1]: /images/20221021/634a9ceee8c14b98b3636d478436b19b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 2]: /images/20221021/a533842349db42b2949dfb20a79efa9f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwOTQ0Nw_size_16_color_FFFFFF_t_70 3]: /images/20221021/77bcad87dfee44729af8f919508c4663.png
[20210512151658598.png]: /images/20221021/c3a29924c6cb471ba7c86611dacb0a66.png