如何把多个sheet合并到一个sheet,java导入多个sheet
本文实例为大家分享了爪哇使用POI将多个表合并为一个表的具体代码,供大家参考,具体内容如下
一、情景描述
最近在项目中客户提到一个新的需求,一开始是在列表查询时导出多个超过表格,后面提到将多个超过表格进行合并,实现一个表显示多个表内容,图示如下:
一开始:
合并后(不同表格空一行隔开):
二、实现思路
首先,先按照生成五张表的思路来生成创建一个工作簿源工作簿,然后再创建一个工作簿目标工作簿,创建一个新的工作表目标工作表工作表,之后将源工作簿中第一个工作表工作表数字一(one)中的内容复制到该表中,再将第二个工作表工作表注射毒品中的内容复制到目标表中,依次操作,复制完源工作簿中全部的五张表,即可实现将多个表合并为一个表的操作。
三、示例代码
1.POIUtil工具类
包com。CD类型。itps。jjxt。模型。util导入com。CD类型。itps。jjxt。模型。excel。cellrangeaddressexcelvo导入org。阿帕奇。poi。党卫军。用户模型。*;导入org。spring框架。util。收集实用程序;导入Java。util。列表;/* * * * @作者中科* @描述//TODO兴趣点导出超过工具类* @ Date 17:16 2021/5/11 * @ Param * @ return * */public class POIUtil {/* * * * @ Author Zhongks * @ Description//TODO拷贝工作表(表)* @ Date 17:16 2021/5/11 * @ Param[target Sheet,sourceSheet,targetWork,sourceWork,startRow,cellRangeAddressExcelVoList]* @ return void * */public static void copy Sheet(Sheet target Sheet,Sheet sourceSheet,Workbook targetWork,Workbook sourceWork,int startRow,listcellrangeaddressexcelvolvo cellRangeAddressExcelVoList){ if(target Sheet==null source Sheet==null target work==null source work==null){ throw调用PoiUtil.copySheet()方法时,目标工作表,源工作表,目标工作,源工作都不能为空,故抛出该异常!);} //设置单元格默认宽度目标工作表。setdefaultcolumnwidth(25);//复制源表中的行for(int I=源工作表。getfirstrownum();I=源表。getlastrownum();I){行来源行=来源表。getrow(I);行目标行=目标工作表。创建行(I start row);//创建新的row if (sourceRow!=null) { copyRow(targetRow,sourceRow,targetWork,source work);} } //自定义合并单元格样式(若不需要进行
单元格合并操作,将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)@AllArgsConstructorpublic 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<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; } /**&a
郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。