Java使用POI将多个Sheet合并为一个Sheet（多个sheet怎么合并成一个）

Java使用POI将多个Sheet合并为一个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 < 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.setTrackhttp://Flag(0);

Map safeQualityParamsMap =this.getTrackSafeQualityMap(bureauDayShiftVo);

//天窗兑现统计表

Map skylightCashStatisticsMap = this.getSkylightCashStatisticsMap();

//其他安全质量信息表

Map otherSafeQualityInfoMap = this.getOtherSafeQualityInfoMap(bureauDayShiftVo);

//安全质量考核表

Map safeQualityAssessmentMap = this.getSafeQualityAssessmentMap();

//添加表

List

sheetsList.add(trackSafeQualityMap);

sheetsList.add(safeQualityParamsMap);

sheetsList.add(skylightCashStatisticsMap);

sheetsList.add(otherSafeQualityInfoMap);

sheetsList.add(safeQualityAssessmentMap);

List

//创建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.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://的名称

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.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;

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。