本文实例为大家分享了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 < 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; }
该实体类是为了进行合并单元格操作，用来存储需要合并的单元格位置信息：
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()); // 设置sheethttp://www.cppcns.com的名称 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); //供电处重点追踪信息表 exportpwww.cppcns.comarams 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; }
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。