本文实例为大家分享了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工具类
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
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.需要合并的单元格位置信息实体
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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层代码：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 |
* * @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; } |
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。
原文链接：https://blog.csdn.net/weixin_44009447/article/details/116708514
查看更多关于Java使用POI将多个Sheet合并为一个Sheet的详细内容...