Easyexcel（6-单元格合并）
注解@ContentLoopMerge
用于设置合并单元格的注解，作用于字段上
[*]eachRow：每隔几行合并
[*]columnExtend：合并列的下标
@AllArgsConstructor
@NoArgsConstructor
@Data
public class User {
@ContentLoopMerge(eachRow = 2, columnExtend = 1)
@ExcelProperty(value = "用户Id")
private Integer userId;
@ExcelProperty(value = "姓名")
private String name;
@ExcelProperty(value = "手机")
private String phone;
@ExcelProperty(value = "邮箱")
private String email;
@ExcelProperty(value = "创建时间")
private Date createTime;
}@OnceAbsoluteMerge
用于指定位置的单元格合并，作用于类上
[*]firstRowIndex：第一行下标
[*]lastRowIndex：最后一行下标
[*]firstColumnIndex：第一列下标
[*]lastColumnIndex：最后一列下标
@OnceAbsoluteMerge(firstColumnIndex = 0, lastColumnIndex = 0, firstRowIndex = 1, lastRowIndex = 2)
@AllArgsConstructor
@NoArgsConstructor
@Data
public class User {
@ExcelProperty(value = "用户Id")
private Integer userId;
@ExcelProperty(value = "姓名")
private String name;
@ExcelProperty(value = "手机")
private String phone;
@ExcelProperty(value = "邮箱")
private String email;
@ExcelProperty(value = "创建时间")
private Date createTime;
}类方法
LoopMergeStrategy
源码查看
public class LoopMergeStrategy implements RowWriteHandler {
// 每隔几行合并
private final int eachRow;
// 合并几列
private final int columnExtend;
// 合并列
private final int columnIndex;
public LoopMergeStrategy(int eachRow, int columnIndex) {
this(eachRow, 1, columnIndex);
}
public LoopMergeStrategy(int eachRow, int columnExtend, int columnIndex) {
if (eachRow < 1) {
throw new IllegalArgumentException("EachRows must be greater than 1");
}
if (columnExtend < 1) {
throw new IllegalArgumentException("ColumnExtend must be greater than 1");
}
if (columnExtend == 1 && eachRow == 1) {
throw new IllegalArgumentException("ColumnExtend or eachRows must be greater than 1");
}
if (columnIndex < 0) {
throw new IllegalArgumentException("ColumnIndex must be greater than 0");
}
this.eachRow = eachRow;
this.columnExtend = columnExtend;
this.columnIndex = columnIndex;
}
public LoopMergeStrategy(LoopMergeProperty loopMergeProperty, Integer columnIndex) {
this(loopMergeProperty.getEachRow(), loopMergeProperty.getColumnExtend(), columnIndex);
}
@Override
public void afterRowDispose(RowWriteHandlerContext context) {
// 判断是否为表头
if (context.getHead() || context.getRelativeRowIndex() == null) {
return;
}
// 循环进行单元格合并
if (context.getRelativeRowIndex() % eachRow == 0) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(context.getRowIndex(),
context.getRowIndex() + eachRow - 1,
columnIndex, columnIndex + columnExtend - 1);
context.getWriteSheetHolder().getSheet().addMergedRegionUnsafe(cellRangeAddress);
}
}
}基本使用
通过 registerWriteHandler 方法设置单元格合并策略，用于指定某几列每相差几行进行单元格合并
[*]指定单列合并
@GetMapping("/download1")
public void download1(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
User user1 = new User();
user1.setUserId(123);
user1.setName("as");
user1.setPhone("15213");
user1.setEmail("5456");
user1.setCreateTime(new Date());
User user2 = new User();
user2.setUserId(123);
user2.setName("asbnm");
user2.setPhone("15213");
user2.setEmail("5456");
user2.setCreateTime(new Date());
User user3 = new User();
user3.setUserId(123);
user3.setName("as");
user3.setPhone("46543213");
user3.setEmail("5456");
user3.setCreateTime(new Date());
// 第1列每隔2行合并一次
LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);
EasyExcel.write(response.getOutputStream(), User.class)
.registerWriteHandler(loopMergeStrategy)
.sheet("模板")
.doWrite(Arrays.asList(user1, user2, user3));
} catch (Exception e) {
e.printStackTrace();
}
}
[*]指定多列合并
@GetMapping("/download1")
public void download1(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
User user1 = new User();
user1.setUserId(123);
user1.setName("as");
user1.setPhone("15213");
user1.setEmail("5456");
user1.setCreateTime(new Date());
User user2 = new User();
user2.setUserId(123);
user2.setName("asbnm");
user2.setPhone("15213");
user2.setEmail("5456");
user2.setCreateTime(new Date());
User user3 = new User();
user3.setUserId(123);
user3.setName("as");
user3.setPhone("46543213");
user3.setEmail("5456");
user3.setCreateTime(new Date());
// 第2列开始每隔2行合并一次，从第2列开始的两列进行合并
LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 2, 2);
EasyExcel.write(response.getOutputStream(), User.class)
.registerWriteHandler(loopMergeStrategy)
.sheet("模板")
.doWrite(Arrays.asList(user1, user2, user3));
} catch (Exception e) {
e.printStackTrace();
}
}
OnceAbsoluteMergeStrategy
源码查看
public class OnceAbsoluteMergeStrategy implements SheetWriteHandler {
// 第一行
private final int firstRowIndex;
// 最后一行
private final int lastRowIndex;
// 第一列
private final int firstColumnIndex;
// 最后一列
private final int lastColumnIndex;
public OnceAbsoluteMergeStrategy(int firstRowIndex, int lastRowIndex, int firstColumnIndex, int lastColumnIndex) {
if (firstRowIndex < 0 || lastRowIndex < 0 || firstColumnIndex < 0 || lastColumnIndex < 0) {
throw new IllegalArgumentException("All parameters must be greater than 0");
}
this.firstRowIndex = firstRowIndex;
this.lastRowIndex = lastRowIndex;
this.firstColumnIndex = firstColumnIndex;
this.lastColumnIndex = lastColumnIndex;
}
public OnceAbsoluteMergeStrategy(OnceAbsoluteMergeProperty onceAbsoluteMergeProperty) {
this(onceAbsoluteMergeProperty.getFirstRowIndex(), onceAbsoluteMergeProperty.getLastRowIndex(),
onceAbsoluteMergeProperty.getFirstColumnIndex(), onceAbsoluteMergeProperty.getLastColumnIndex());
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
CellRangeAddress cellRangeAddress =
new CellRangeAddress(firstRowIndex, lastRowIndex, firstColumnIndex, lastColumnIndex);
writeSheetHolder.getSheet().addMergedRegionUnsafe(cellRangeAddress);
}
}基本使用
通过 registerWriteHandler 方法设置单元格合并策略，用于指定一个区域内的单元格进行合并
@GetMapping("/download2")
public void download2(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
User user1 = new User();
user1.setUserId(123);
user1.setName("as");
user1.setPhone("15213");
user1.setEmail("5456");
user1.setCreateTime(new Date());
User user2 = new User();
user2.setUserId(123);
user2.setName("asbnm");
user2.setPhone("15213");
user2.setEmail("5456");
user2.setCreateTime(new Date());
User user3 = new User();
user3.setUserId(123);
user3.setName("as");
user3.setPhone("46543213");
user3.setEmail("5456");
user3.setCreateTime(new Date());
// 从第1行第3列合并到第3行第3列
OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy = new OnceAbsoluteMergeStrategy(0, 2, 2, 2);
EasyExcel.write(response.getOutputStream(), User.class)
.registerWriteHandler(onceAbsoluteMergeStrategy)
.sheet("模板")
.doWrite(Arrays.asList(user1, user2, user3));
} catch (Exception e) {
e.printStackTrace();
}
}
合并单元格工具类
AbstractMergeStrategy
基本思路
[*]继承 AbstractMergeStrategy 抽象合并策略，重写 merge 方法
[*]传入要合并的数据列表，循环判断上下行是否是相同的数据，如果是则为同一个组，否则为另一个组，使用 List 保存每个组的数量
[*]单元格渲染时，循环遍历每个组的值后，计算要合并的单元格的上下标
使用
/**
* 自定义合并策略 该类继承了AbstractMergeStrategy抽象合并策略，需要重写merge()方法
*/
public class CustomMergeStrategy extends AbstractMergeStrategy {
/**
* 分组，每几行合并一次
*/
private List<Integer> exportFieldGroupCountList;
/**
* 目标合并列index
*/
private Integer targetColumnIndex;
/**
* 需要开始合并单元格的首行index
*/
private Integer rowIndex;
public CustomMergeStrategy(List<String> exportDataList, Integer targetColumnIndex, Integer rowIndex) {
this.exportFieldGroupCountList = getGroupCountList(exportDataList, rowIndex);
this.targetColumnIndex = targetColumnIndex;
this.rowIndex = rowIndex;
}
// 该方法将目标列根据值是否相同连续可合并，存储可合并的行数
private List<Integer> getGroupCountList(List<String> exportDataList, Integer rowIndex) {
if (CollectionUtils.isEmpty(exportDataList)) {
return new ArrayList<>();
}
List<Integer> groupCountList = new ArrayList<>();
int count = 1;
for (int i = rowIndex + 1, len = exportDataList.size(); i < len; i++) {
// 判断上一列和当前列的值是否相同
if (exportDataList.get(i).equals(exportDataList.get(i - 1))) {
count++;
} else {
groupCountList.add(count);
count = 1;
}
}
// 处理完最后一条后
groupCountList.add(count);
return groupCountList;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if (null == rowIndex) {
rowIndex = cell.getRowIndex();
}
// 仅从首行以及目标列的单元格开始合并，忽略其他
if (cell.getRowIndex() == rowIndex + 1 && cell.getColumnIndex() == targetColumnIndex) {
mergeGroupColumn(sheet);
}
}
private void mergeGroupColumn(Sheet sheet) {
int rowCount = rowIndex + 1;
for (Integer count : exportFieldGroupCountList) {
if (count == 1) {
rowCount += count;
continue;
}
// 合并单元格
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount + count - 1, targetColumnIndex, targetColumnIndex);
sheet.addMergedRegionUnsafe(cellRangeAddress);
rowCount += count;
}
}
}
[*]从首行开始合并单元格
@GetMapping("/download3")
public void download3(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
User user1 = new User();
user1.setUserId(123);
user1.setName("as");
user1.setPhone("15213");
user1.setEmail("5456");
user1.setCreateTime(new Date());
User user2 = new User();
user2.setUserId(123);
user2.setName("asbnm");
user2.setPhone("15213");
user2.setEmail("5456");
user2.setCreateTime(new Date());
User user3 = new User();
user3.setUserId(123);
user3.setName("as");
user3.setPhone("46543213");
user3.setEmail("5456");
user3.setCreateTime(new Date());
List<User> userList = Arrays.asList(user1, user2, user3);
CustomMergeStrategy customMergeStrategy = new CustomMergeStrategy(userList.stream().map(e ->
String.valueOf(e.getUserId())).collect(Collectors.toList()), 0, 0);
EasyExcel.write(response.getOutputStream(), User.class)
.registerWriteHandler(customMergeStrategy)
.sheet("模板")
.doWrite(userList);
} catch (Exception e) {
e.printStackTrace();
}
}
[*]从指定行开始合并单元格
@GetMapping("/download3")
public void download3(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
User user1 = new User();
user1.setUserId(123);
user1.setName("as");
user1.setPhone("15213");
user1.setEmail("5456");
user1.setCreateTime(new Date());
User user2 = new User();
user2.setUserId(123);
user2.setName("asbnm");
user2.setPhone("15213");
user2.setEmail("5456");
user2.setCreateTime(new Date());
User user3 = new User();
user3.setUserId(123);
user3.setName("as");
user3.setPhone("46543213");
user3.setEmail("5456");
user3.setCreateTime(new Date());
List<User> userList = Arrays.asList(user1, user2, user3);
CustomMergeStrategy customMergeStrategy = new CustomMergeStrategy(userList.stream().map(e ->
String.valueOf(e.getUserId())).collect(Collectors.toList()), 0, 1);
EasyExcel.write(response.getOutputStream(), User.class)
.registerWriteHandler(customMergeStrategy)
.sheet("模板")
.doWrite(userList);
} catch (Exception e) {
e.printStackTrace();
}
}
CellWriteHandler
基本思路
[*]实现 CellWriteHandler 类的 afterCellDispose 方法，在每个单元格完全创建完之后执行合并单元格操作
[*]判断当前列是否为要合并的列，且当前行是否已经到达要操作的行数
[*]如果是，则判断上一行和当前行的数据是否一致，且序号是否一致
[*]如果是，则进行合并单元格操作，如果上一行已经被合并过了，则进行移除，然后再重新合并单元格
使用
/** * excel合并单元格导出工具类 */public class EasyExcelUtil implements CellWriteHandler { /** * 需要合并的列 */ private int[] mergeColumnIndex; /** * 从哪一行开始合并 */ private int mergeRowIndex; public EasyExcelUtil() { } public EasyExcelUtil(int mergeRowIndex, int[] mergeColumnIndex) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex; } /** * 创建每个单元格之前执行 * * @param writeSheetHolder * @param writeTableHolder * @param row * @param head * @param columnIndex * @param relativeRowIndex * @param isHead */ @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { CellWriteHandler.super.beforeCellCreate(writeSheetHolder, writeTableHolder, row, head, columnIndex, relativeRowIndex, isHead); } /** * 每个单元格数据内容渲染之后执行 * * @param writeSheetHolder * @param writeTableHolder * @param cellData * @param cell * @param head * @param relativeRowIndex * @param isHead */ @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { CellWriteHandler.super.afterCellDataConverted(writeSheetHolder, writeTableHolder, cellData, cell, head, relativeRowIndex, isHead); } /** * 每个单元格完全创建完之后执行 * * @param writeSheetHolder * @param writeTableHolder * @param cellDataList * @param cell * @param head * @param relativeRowIndex * @param isHead */ @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List 鼓励转贴优秀软件安全工具和文档！ 收藏一下 不知道什么时候能用到 感谢，下载保存了 收藏一下 不知道什么时候能用到 懂技术并乐意极积无私分享的人越来越少。珍惜 前排留名，哈哈哈 yyds。多谢分享 过来提前占个楼 感谢分享 感谢，下载保存了 感谢分享，学习下。 收藏一下 不知道什么时候能用到 东西不错很实用谢谢分享 收藏一下 不知道什么时候能用到 鼓励转贴优秀软件安全工具和文档！ 感谢，下载保存了 鼓励转贴优秀软件安全工具和文档！ 分享、互助 让互联网精神温暖你我 这个有用。