这篇文章主要为大家详细介绍了Java使用POI将多个Sheet合并为一个Sheet，文中示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下
本文实例为大家分享了Java使用POI将多个Sheet合并为一个Sheet的具体代码，供大家参考，具体内容如下
一、情景描述
最近在项目中客户提到一个新的需求，一开始是在列表查询时导出多个Excel表格，后面提到将多个Excel表格进行合并，实现一个sheet显示多个sheet内容，图示如下：
一开始：
合并后（不同表格空一行隔开）：
二、实现思路
首先，先按照生成五张表的思路来生成创建一个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 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 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; }该实体类是为了进行合并单元格操作，用来存储需要合并的单元格位置信息：
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 trackSafeQualityMap =this.getTrackSafeQualityMap(bureauDayShiftVo); //日安全质量信息表信息 bureauDayShiftVo.setTrackFlag(0); Map safeQualityParamsMap =this.getTrackSafeQualityMap(bureauDayShiftVo); //天窗兑现统计表 Map skylightCashStatisticsMap = this.getSkylightCashStatisticsMap(); //其他安全质量信息表 Map otherSafeQualityInfoMap = this.getOtherSafeQualityInfoMap(bureauDayShiftVo); //安全质量考核表 Map safeQualityAssessmentMap = this.getSafeQualityAssessmentMap(); //添加表 List> sheetsList = new ArrayList<>(); sheetsList.add(trackSafeQualityMap); sheetsList.add(safeQualityParamsMap); sheetsList.add(skylightCashStatisticsMap); sheetsList.add(otherSafeQualityInfoMap); sheetsList.add(safeQualityAssessmentMap); List> 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> **/ public Map getTrackSafeQualityMap(BureauDayShiftVo bureauDayShiftVo){ List exportList = new LinkedList<>(); List> 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 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 **/ public Map getSkylightCashStatisticsMap(){ List 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 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 **/ public Map getOtherSafeQualityInfoMap(BureauDayShiftVo bureauDayShiftVo){ List exportList = new LinkedList<>(); //ToDo 得到其他安全信息列表数据并进行封装 BureauSafeQualityOtherInfoVo bureauSafeQualityOtherInfoVo=new BureauSafeQualityOtherInfoVo(); bureauSafeQualityOtherInfoVo.setStartDate(bureauDayShiftVo.getStartDate()); bureauSafeQualityOtherInfoVo.setEndDate(bureauDayShiftVo.getEndDate()); List> 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 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 **/ public Map getSafeQualityAssessmentMap(){ List exportList = new LinkedList<>(); //ToDo 得到安全质量考核列表数据并进行封装 //供电处重点追踪信息表 ExportParams exportParams = new ExportParams(); //设置边框样式 // exportParams.setStyle(ExcelStyleType.BORDER.getClazz()); // 设置sheet的名称 exportParams.setSheetName("安全质量考核"); Map 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 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 **/ public static List getCellRangeAddressExcelVoList(int row,int col){ //合并单元格坐标位置 List 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; } 以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持0133技术站。
以上就是Java使用POI将多个Sheet合并为一个Sheet的详细内容，更多请关注0133技术站其它相关文章！
