easyexcel导出excel文件合并相同单元格数据
1、引入easyexcel依赖
!-- 阿里开源easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.0-beta2</version>
</dependency>2、ExcelMergeUtil工具类
package com.sdy.resdir.biz.util;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.math.BigDecimal;
import java.util.List;
import static org.apache.poi.ss.usermodel.CellType.NUMERIC;
public class ExcelMergeUtil implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
public ExcelMergeUtil() {
}
public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
//当前行
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* 当前单元格向上合并
*
* @param writeSheetHolder
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 将当前单元格数据与上一个单元格数据比较
Boolean dataBool = preData.equals(curData);
//此处需要注意：因为我是按照工程名称确定是否需要合并的，所以获取每一行第二列数据和上一行第一列数据进行比较，如果相等合并，getCell里面的值，是工程名称所在列的下标
BigDecimal d1 = new BigDecimal(cell.getRow().getCell(0).getNumericCellValue());
BigDecimal d2 = new BigDecimal(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getNumericCellValue());
Boolean bool = d1.compareTo(d2) == 0 ? true:false;
// 原始的
// Boolean bool = cell.getRow().getCell(1).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(1).getStringCellValue());
if (dataBool && bool) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
3、导出的excel标题
package com.sdy.resdir.biz.vo;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.metadata.BaseRowModel;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.sdy.common.utils.DateUtil;
import io.swagger.annotations.ApiModel;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.util.Date;
/**
* Excel模板文件类
*
* @author hyh
*/
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
public class ResItemExcelVo extends BaseRowModel {
/**
* 序号
*/
@ExcelProperty(value = {"资源基本信息", "序号"},index = 0)
private Integer resNo;
/**
* 资源名称
*/
@ColumnWidth(20)
// @ExcelProperty(value = "资源名称",index = 1)
@ExcelProperty(value = {"资源基本信息", "资源名称"}, index = 1)
private String resName;
/**
* 资源类型
*/
@ColumnWidth(20)
@ExcelProperty(value = {"资源基本信息", "资源类型"} ,index = 2)
private String resType;
/**
* 资源提供部门
*/
@ColumnWidth(20)
@ExcelProperty(value = {"资源基本信息", "资源提供部门"},index = 3)
private String resSourceDept;
/**
* 资源权限
*/
@ColumnWidth(20)
@ExcelProperty(value = {"资源基本信息", "资源权限"},index = 4)
private String resPower;
/**
* 资源层级（1.省级 2.市级 3.区级）
*/
@ColumnWidth(20)
@ExcelProperty(value = {"资源基本信息", "资源层级"},index = 5)
private String resLevel;
/**
* 归集情况(1.已上线 2.未上线)
*/
@ColumnWidth(20)
@ExcelProperty(value = {"资源基本信息", "归集情况"},index = 6)
private String collectionSituation;
/**
* 是否导入省里(0-否，1-是)
*/
@ColumnWidth(20)
@ExcelProperty(value = {"资源基本信息", "是否导入省里"},index = 7)
private String isImport;
/**
* 回流标记(0-否,1-是)
*/
@ColumnWidth(20)
@ExcelProperty(value = {"资源基本信息", "是否回流"}, index = 8)
private String backFlow;
/**
* 单位名称
*/
@ColumnWidth(20)
@ExcelProperty(value = {"资源基本信息", "单位名称"}, index = 9)
private String companyName;
/**
* 数据项
*/
@ColumnWidth(10)
@ExcelProperty(value = {"编目信息","数据项"}, index = 10)
private String chineseName;
/**
* 英文名称
*/
@ColumnWidth(20)
@ExcelProperty(value = {"编目信息","英文名称"}, index = 11)
private String englishName;
/**
* 字段类型
*/
@ColumnWidth(15)
@ExcelProperty(value = {"编目信息","字段类型"}, index = 12)
private String fieldType;
/**
* 字段长度
*/
@ColumnWidth(15)
@ExcelProperty(value = {"编目信息","字段长度"}, index = 13)
private String fieldLength;
/**
* 字段精度
*/
@ColumnWidth(15)
@ExcelProperty(value = {"编目信息","字段精度"}, index = 14)
private String fieldAccuracy;
/**
* 是否主键
*/
@ColumnWidth(15)
@ExcelProperty(value = {"编目信息","是否主键"}, index = 15)
private String keywords;
/**
* 是否字典项(0-否，1-是)
*/
@ColumnWidth(15)
@ExcelProperty(value = {"编目信息","是否字典项"}, index = 16)
private String isDictionary;
/**
* 字段描述
*/
@ColumnWidth(15)
@ExcelProperty(value = {"编目信息","字段描述"}, index = 17)
private String fieldDesc;
/**
* 是否为空(0-否，1-是)
*/
@ColumnWidth(15)
@ExcelProperty(value = {"编目信息","是否为空"}, index = 18)
private String isEmpty;
/**
* 默认值
*/
@ColumnWidth(15)
@ExcelProperty(value = {"编目信息","默认值"}, index = 19)
private String defaultValue;
/**
* 共享属性
*/
@ColumnWidth(18)
@ExcelProperty(value = {"编目信息","共享属性"}, index = 20)
private String shareAttribute;
/**
* 共享条件
*/
@ColumnWidth(18)
@ExcelProperty(value = {"编目信息","共享条件"}, index = 21)
private String shareCondition;
/**
* 开放属性
*/
@ColumnWidth(18)
@ExcelProperty(value = {"编目信息","开放属性"}, index = 22)
private String openAttribute;
}
4、控制层调用
public void downLoadResItem(HttpServletResponse response, String resName, Integer isOnline, Integer resType, Integer resLevel, Integer resPower, Integer resPowerDept, Integer realmId) throws IOException {
if (StringUtil.isNotBlank(resName)) {
resName = URLDecoder.decode(resName, "UTF-8");
}
List<RdResourceDirExcelDTO> rdResourceDirList = rdResourceDirService.getList(resName, isOnline, resType, resLevel, resPower, resPowerDept, realmId);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("资源列表下载", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
ServletOutputStream output = response.getOutputStream();
ExcelWriter writer = new ExcelWriter(output, ExcelTypeEnum.XLS, true);
Sheet sheet = new Sheet(1, 0, ResItemExcelVo.class);
// sheet.setSheetName("第一页");
List<ResItemExcelVo> voList = setResItemData(rdResourceDirList);
// writer.write(voList, sheet);
//需要合并的列
int[] mergeColumeIndex = {0,1,2,3,4,5,6,7,8,9};
// 从第二行后开始合并
int mergeRowIndex = 2;
EasyExcel.write(response.getOutputStream(), ResItemExcelVo.class)
.sheet("第一页")
// .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex))
.registerWriteHandler(new ExcelMergeUtil(mergeRowIndex, mergeColumeIndex))
.doWrite(voList);
writer.finish();
output.flush();
}导出的效果excel ，我是通过序号相同合并
导出的数据类似
| 序号 | 日志名称 | 日志时间 | 字段名 | 字段描述 | 字段值 |
| 1 | 根据ID查询详情 | 2021-01-13 | total | 总数 | 25 |
| 1 | 根据ID查询详情 | 2021-01-13 | total | 总数 | 25 |
| 1 | 根据ID查询详情 | 2021-01-13 | total | 总数 | 25 |
| 2 | 获取日志列表 | 2021-01-12 | logName | 日志名称 | 查询 |
| 2 | 获取日志列表 | 2021-01-12 | logTime | 日志时间 | 2021-10-01 14:25:14 |
| 2 | 获取日志列表 | 2021-01-12 | logUser | 查询人姓名 | 王杰 |
| 3 | 查询用户信息 | 2021-01-11 | userName | 用户名 | tqf_123 |
| 3 | 查询用户信息 | 2021-01-11 | age | 年龄 | 25 |
| 3 | 查询用户信息 | 2021-01-11 | sex | 性别(0-男，1-女，2-未知) | 0 |
