Java多个Sheet合并为一个Sheet
Keson Z 人气:0一、情景描述
最近在项目中客户提到一个新的需求，一开始是在列表查询时导出多个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<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.需要合并的单元格位置信息实体
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<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;
}加载全部内容