easyexcel导出&自定义策略合并单元格
效果图
在这里插入图片描述
导入easyexcel依赖 pom
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
导出VO对象
@Data
@TableName("t_person")
public class TPerson implements Serializable {
@ExcelIgnore // 用该注解标记忽略字段
private Long id;
@ExcelProperty({"人员信息","姓名"}) // 标题,写多个标题可以让标题具有层级关系
private String personName;
@ExcelProperty({"人员信息","年龄"})
private Integer age;
@ExcelProperty({"人员信息","性别"})
private Integer sex;
@ExcelProperty({"身份证号码"})
private String idCard;
@ExcelProperty({"手机号码"})
private String phoneNumber;
}
导出的service层逻辑
其中使用自定义策略在 .registerWriteHandler(new CustomMergeStrategy())这个方法中,添加自定义策略实现类
@Override
public void testExportPersonExcel(HttpServletResponse response) throws IOException {
List<TPerson> personList = personMapper.selectList(null); // 可更换自己数据源
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-disposition", "attachment;filename=" + "全部数据.xlsx" );
EasyExcel.write(response.getOutputStream())
.head(TPerson.class)
.registerWriteHandler(new CustomMergeStrategy()) // 使用自定义策略
.excelType(ExcelTypeEnum.XLSX)
.sheet("人员列表")
.doWrite(personList);
}
自定义策略
package com.tfxing.persondaily.entity.strategy;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
public class CustomMergeStrategy implements RowWriteHandler {
public CustomMergeStrategy() {}
@Override
public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex,
Integer relativeRowIndex, Boolean isHead) {
Sheet sheet = writeSheetHolder.getSheet();
}
@Override
public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Integer relativeRowIndex, Boolean isHead) {
Sheet sheet = writeSheetHolder.getSheet();
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Integer relativeRowIndex, Boolean isHead) {
// 处理标题,可以自定义标题
titleHandle(writeSheetHolder.getSheet(),"titleName","titleName2");
// 合并单元格
// 参数说明:开始行,结束行,开始列,结束列
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 2, 1, 3); // 合并第1-3行,第2-4列
sheet.addMergedRegionUnsafe(cellRangeAddress);
}
private void titleHandle(Sheet sheet, String titleName, String titleName2) {
// 设置单元格风格
Workbook workbook = sheet.getWorkbook();
Font font = workbook.createFont();
font.setBold(true); // 字体加粗
font.setFontHeightInPoints((short)14); // 字体大小14
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.LEFT); // 内容居左
cellStyle.setFont(font);
// 将行的标题设置为titleName
for (int i = 0; i < 7; i++) {
Cell cell = sheet.getRow(1).getCell(i);
cell.setCellValue(titleName);
cell.setCellStyle(cellStyle);
}
CellStyle cellStyle1 = workbook.createCellStyle();
cellStyle1.setAlignment(HorizontalAlignment.RIGHT); // 内容居右
cellStyle1.setFont(font);
for (int i = 7; i < 12; i++) {
Cell cell = sheet.getRow(1).getCell(i);
cell.setCellValue(titleName2);
cell.setCellStyle(cellStyle1);
}
}
}
参数解析
-
writeSheetHolder:
包含了excel中整个sheet的信息,以下是比较重要有意义的参数:- sheet:
- workbook:
- stylesSource: 包含了样式风格,例如:fonts:字体大小,borders:边框大小
- rows: 当前sheet的所有行列表
- cells: 行中的单元格的列表
- workbook:
- sheetName:sheet名称
- writeSheet:
- sheet:
-
writeTableHolder:
-
rowIndex:当前所处于excel的第几行,从1开始
-
relativeRowIndex:去除标题后,当前所处于excel的第几行,从1开始
-
isHead:boolean类型,是否为标题
-
row:当前行的信息
- cells: 行中的单元格的列表
测试
将1,2,3列的第4-6行分别合并
for (int i = 0; i < 3; i++) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(3, 6, i, i); writeSheetHolder.getSheet().addMergedRegionUnsafe(cellRangeAddress);
}
效果图
后面写的一篇,合并单元格策略,更详细,更清晰
excel单元格合并策略