封装poi对Office Excel的输入输出工具，简化简单的导入和导出Excel数据的操作。（暂不支持合并单元格）
<dependency>
<groupId>com.github.developframework</groupId>
<artifactId>excel-io</artifactId>
</dependency>假设存在实体Student包装数据（构造方法略）
public class Student {
// 姓名
private String name;
// 性别
private Gender gender;
// 生日
private LocalDate birthday;
// 入学时间
private LocalDateTime createTime;
// 语文成绩
private int chineseScore;
// 数学成绩
private int mathScore;
// 英语成绩
private int englishScore;
// 总成绩
private int totalScore;
// 是否合格
private Boolean qualified;
public enum Gender {
MALE, FEMALE
}
}List<Student> students=List.of(
new Student("小赵",Student.Gender.MALE,LocalDate.of(2002,1,5),LocalDateTime.now(),97,85,95),
new Student("小钱",Student.Gender.FEMALE,LocalDate.of(1999,12,25),LocalDateTime.now(),92,89,87),
new Student("小孙",Student.Gender.MALE,LocalDate.of(2001,6,8),LocalDateTime.now(),50,40,45),
new Student("小李",Student.Gender.FEMALE,LocalDate.of(2003,8,20),LocalDateTime.now(),80,90,72)
);使用ExcelIO得到输入输出处理器
List<Student> students=new LinkedList<>();
// 准备数据略
File file = ExcelIO
.writer(ExcelType.XLSX)
.load(students,(workbook,builder)->
builder.columnDefinitions(
builder.<String>column("name","学生姓名"),
builder.<Student.Gender>column("gender","性别"),
builder.<LocalDate>column("birthday","生日"),
builder.<LocalDateTime>column("createTime","入学时间"),
builder.<Integer>column("chineseScore","语文成绩"),
builder.<Integer>column("mathScore","数学成绩"),
builder.<Integer>column("englishScore","英语成绩"),
builder.<Integer>formula("总成绩","SUM(E{row}:G{row})"),
builder.<Boolean>formula("是否合格","IF(H{row} >= 180,\"合格\",\"不合格\")")
)
)
.writeToFile("D:\\学生成绩表.xlsx");
使用excel-io导入students数据
final List<Student> students = ExcelIO
.reader("D:\\学生成绩表.xlsx")
.read(Student.class, (workbook, builder) ->
builder.columnDefinitions(
builder.<String>column("name"),
builder.<Student.Gender>column("gender"),
builder.<LocalDate>column("birthday"),
builder.<LocalDateTime>column("createTime"),
builder.<Integer>column("chineseScore"),
builder.<Integer>column("mathScore"),
builder.<Integer>column("englishScore"),
builder.<Integer>formula(Integer.class, "totalScore"),
builder.<String>formula(String.class, "qualified")
.readConvert((student, qualified) -> qualified.equals("合格"))
)
);读取时可以不设置列名header
该接口是表格的定义类，一个定义类代表了一个数据表
通过该接口可以设置表格的表头信息和表格的左上角单元格位置（工作表、行、列）。
new TableDefinition<>() {
/**
* 设置表格信息
*/
@Override
public TableInfo tableInfo() {
return new TableInfo();
}
/**
* 列定义
*/
@Override
public ColumnDefinition<Student>[] columnDefinitions(Workbook workbook, ColumnDefinitionBuilder builder) {
return builder.columnDefinitions(
);
}
/**
* 全局单元格样式处理
*/
@Override
public BiConsumer<Workbook, CellStyle> globalCellStylesHandle() {
return null;
}
/**
* 申明自定义单元格样式
*/
@Override
public Map<String, CellStyle> customCellStyles(Workbook workbook) {
return Collections.emptyMap();
}
/**
* 工作表扩展处理
*/
@Override
public SheetExtraHandler<?> sheetExtraHandler() {
return null;
}
/**
* 装填完的实体单独处理
*/
@Override
public void each(Student student) {
}
};该抽象类是表格的列定义类，一个定义类代表了表中的某一列，指代了一个字段 可通过ColumnDefinitionBuilder方便创建ColumnDefinition的实例
@Override
public ColumnDefinition<Student>[] columnDefinitions(Workbook workbook, ColumnDefinitionBuilder<Student> builder) {
return builder.columnDefinitions(
builder.columnDefinitions(
builder
.<Student /*实体泛型*/, String /*字段泛型*/>column("name", "学生姓名")
// 写转化值
.writeConvert((student, field) -> /* 处理逻辑 */)
// 读转化值
.readConvert((student, field) -> /* 处理逻辑 */)
// 手动设置列宽
.columnWidth(20)
// 获得样式键 （可以针对单元格值来选择样式）
.cellStyleKey((cell, cellValue) -> /* 获得样式键 */)
)
);
}builder.column(...)声明一个普通列builder.literal(...)声明一个字面量列builder.blank(...)声明一个空列builder.formula(...)声明一个公式列
如果上述快捷写法无法满足场景要求，需要更加灵活的写入和读取方式。可以直接实现ColumnDefinition接口
@Override
public ColumnDefinition<Student>[] columnDefinitions(Workbook workbook, ColumnDefinitionBuilder builder) {
return builder.columnDefinitions(
new ColumnDefinition<>() {
/**
* 列信息
*/
@Override
public ColumnInfo getColumnInfo() {
return new ColumnInfo("name", "学生姓名", 10);
}
/**
* 描述如何把值写入单元格
*/
@Override
public Object writeIntoCell(Workbook workbook, Cell cell, Student student) {
final String name = student.getName();
cell.setCellValue(name);
return name;
}
/**
* 描述如何从单元格读取值并装填到实体
*/
@Override
public void readOutCell(Workbook workbook, Cell cell, Student student) {
final String name = cell.getStringCellValue();
student.setName(name);
}
/**
* 配置单元格格式
*/
@Override
public void configureCellStyle(Cell cell, CellStyleManager cellStyleManager, Student entity, Object value) {
cell.setCellStyle(cellStyleManager.getCellStyle(DefaultCellStyles.STYLE_NORMAL));
}
}
);
}在poi中创建CellStyle的个数是有限制的，xls上限4000个，xlsx上限64000个
如果超限了会报如下两个异常：
The maximum number of cell styles was exceeded.You can define up to 4000 styles in a .xls workbook
The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook
所以提供CellStyleManager单元格样式管理器来管理申明的样式，做到复用CellStyle对象
用于在CellStyleManager内选取CellStyle的键，可以是自定义的任意字符串。也可以是一个特定结构的字符串用于描述CellStyle的特性（格式类似于层叠样式CSS），预设有如下写法：
- align 对齐
align {vertical: right; horizontal: center}
可缩写成 a {v: right; h: center}
- border 边框
border {style: thin; color: #ff0000}
可缩写成 b {style: thin; color: RED}
- dataFormat 格式
dataFormat {format: 0.00%}
dataFormat {format: 'yyyy-mm-dd hh:mm:ss'} 如果格式中需要包含空格，需要加单引号
可缩写成 df {format: 'yyyy-mm-dd hh:mm:ss'}
- font 字体
font {size: 16; bold: true; italic: true; family: '宋体'; color: #ff0000}
可缩写成 f {size: 16; bold; italic; family: '宋体'; color: #ff0000} boolean字段是true的话可以缩写
- foreground 前景色
foreground {color: #aa1199; type: SOLID_FOREGROUND}
可缩写成 fg {color: #aa1199; type: SOLID_FOREGROUND}
- config 其他配置
config {wrapText: true} 允许单元格内换行
可缩写成 c {wrapText}
可在cellStyleKey()方法内直接使用
builder
.column(...)
// 结合文本块功能写起来更美观
.cellStyleKey((cell, entity, cellValue) -> """
a {
h: right
}
font {
color: #ffff00;
size: 20
}
""";
)预设了默认的单元格样式：
| 默认单元格样式键 | 样式键 | 说明 | 备注 |
|---|---|---|---|
| DefaultCellStyles.STYLE_TITLE | font {size: 16; bold} | 加粗 16号字 | 标题采用 |
| DefaultCellStyles.STYLE_HEADER | font {bold} | 加粗 | 列头采用 |
| DefaultCellStyles.STYLE_BODY | <空字符串> | 黑色细线边框文字居中 | 表格内容采用 |
| DefaultCellStyles.STYLE_BODY_BOLD | font {bold} | 加粗 | |
| DefaultCellStyles.STYLE_BODY_ITALIC | font {italic} | 斜体 | |
| DefaultCellStyles.STYLE_BODY_PERCENT | dataFormat {format: '0.00%'} | 0.00% 百分比格式 | |
| DefaultCellStyles.STYLE_BODY_DATETIME | dataFormat {format: 'yyyy-mm-dd hh:mm:ss'} | 日期时间格式 | |
| DefaultCellStyles.STYLE_BODY_DATE | dataFormat {format: 'yyyy-mm-dd'} | 日期格式 | |
| DefaultCellStyles.STYLE_BODY_TIME | dataFormat {format: 'hh:mm:ss'} | 时间格式 | |
| DefaultCellStyles.STYLE_BODY_NUMBER | align {horizontal: RIGHT} | 文字居右 | |
| DefaultCellStyles.STYLE_BODY_NUMBER_2_PRECISION | align {horizontal: RIGHT} dataFormat {format: '0.00'} | 精度2 文字居右 |
cell.setCellStyle(cellStyleManager.getCellStyle(DefaultCellStyles.STYLE_BODY));可以用于覆盖默认单元格样式，也可初始化自定义样式
new TableDefinition<>() {
/**
* 申明自定义单元格样式
*/
@Override
public Map<String, CellStyle> customCellStyles(Workbook workbook) {
// final CellStyle cellStyle = workbook.createCellStyle();
final CellStyle cellStyle = DefaultCellStyles.buildByCellStyleKey(DefaultCellStyles.STYLE_BODY);
// 对单元格样式做自定义配置
cellStyle.setDataFormat(...);
// 如果选择默认样式的键将会覆盖
return Map.of(DefaultCellStyles.STYLE_BODY, cellStyle);
}
}单元格可以根据值来判定选择哪个样式键
builder
.column(...)
// 获得样式键 （可以针对单元格值来选择样式）
.cellStyleKey((cell, entity, cellValue) -> {
return cellValue == null ? DefaultCellStyles.STYLE_BODY : "customKey";
})示例：
依靠单元格的值，将不合格的分数标红色背景
ExcelIO
.writer(ExcelType.XLSX)
.load(students, new TableDefinition<>() {
@Override
public Map<String, CellStyle> customCellStyles(Workbook workbook) {
// 设置单元格背景色
final CellStyle redCellStyle = DefaultCellStyles.buildByCellStyleKey("a {h: right} fg {color: RED}");
return Map.of("redColor", redCellStyle);
}
@Override
public ColumnDefinition<Student>[] columnDefinitions(Workbook workbook, ColumnDefinitionBuilder builder) {
// 判定分数大于60
final CellStyleKeyProvider<Student> scoreKeyProvider = (cell, e, v) -> ((Integer) v) >= 60 ? null : "redColor";
// 判定分数大于180
final CellStyleKeyProvider<Student> totalKeyProvider = (cell, e, v) -> ((Integer) v) >= 180 ? null : "redColor";
// 判定是否合格
final CellStyleKeyProvider<Student> qualifiedKeyProvider = (cell, e, v) -> v.equals("合格")? null : "redColor";
return builder.columnDefinitions(
builder.column("name", "学生姓名"),
builder.column("gender", "性别"),
builder.column("birthday", "生日"),
builder.column("createTime", "入学时间"),
builder.column("chineseScore", "语文成绩").cellStyleKey(scoreKeyFunction),
builder.column("mathScore", "数学成绩").cellStyleKey(scoreKeyFunction),
builder.column("englishScore", "英语成绩").cellStyleKey(scoreKeyFunction),
builder.formula(Integer.class, "总成绩", "SUM(E{row}:G{row})").cellStyleKey(totalKeyFunction),
builder.formula(String.class, "是否合格", "IF(H{row} >= 180,\"合格\",\"不合格\")").cellStyleKey(qualifiedKeyFunction)
);
}
}
)
.writeToFile("D:\\学生成绩表.xlsx");
对所有注册进单元格样式管理器的CellStyle执行统一处理
new TableDefinition<>(){
/**
* 全局单元格样式处理
*/
@Override
public BiConsumer<Workbook, CellStyle> globalCellStylesHandle() {
return (workbook, cellStyle) -> {
// 改变全部字体
final Font font = workbook.createFont();
font.setFontName("宋体");
cellStyle.setFont(font);
};
}
}制作员工考勤表
@Getter
@Setter
public class StaffAttendance {
// 员工姓名
private String name;
// 考勤记录
private Attendance[] attendances;
@Getter
@RequiredArgsConstructor(access = AccessLevel.PRIVATE)
public enum Attendance {
// 出勤
ON_DUTY("Y"),
// 请假
LEAVE("N");
private final String text;
}
}public static List<StaffAttendance> buildStaffAttendances() {
return List.of(
createStaffAttendance("张三", new int[]{15, 20}),
createStaffAttendance("李四", new int[]{3}),
createStaffAttendance("王五", new int[]{3, 4, 27})
);
}
private static StaffAttendance createStaffAttendance(String name, int[] leaveDays) {
StaffAttendance staffAttendance = new StaffAttendance();
staffAttendance.setName(name);
final StaffAttendance.Attendance[] attendances = new StaffAttendance.Attendance[31];
staffAttendance.setAttendances(attendances);
Arrays.fill(attendances, StaffAttendance.Attendance.ON_DUTY);
for (int leaveDay : leaveDays) {
attendances[leaveDay - 1] = StaffAttendance.Attendance.LEAVE;
}
return staffAttendance;
}final List<StaffAttendance> projects = buildStaffAttendances();
ExcelIO.writer(ExcelType.XLSX)
.load(projects, new TableDefinition<>() {
@Override
public TableInfo tableInfo() {
TableInfo tableInfo = new TableInfo();
tableInfo.hasTitle = true;
tableInfo.title = "员工考勤表";
tableInfo.sheetName = "考勤表";
return tableInfo;
}
@Override
public Map<String, CellStyle> customCellStyles(Workbook workbook) {
// 自定义标题样式 覆盖原有样式
final CellStyle titleCellStyle = DefaultCellStyles.buildByCellStyleKey(workbook, "font {size: 16; bold; color: BLUE}");
return Map.of(DefaultCellStyles.STYLE_TITLE, titleCellStyle);
}
@Override
public BiConsumer<Workbook, CellStyle> globalCellStylesHandle() {
return (workbook, cellStyle) -> {
// 修改全局字体
final Font font = workbook.getFontAt(cellStyle.getFontIndexAsInt());
font.setFontName("黑体");
};
}
@Override
@SuppressWarnings("unchecked")
public ColumnDefinition<StaffAttendance>[] columnDefinitions(Workbook workbook, ColumnDefinitionBuilder<StaffAttendance> builder) {
List<ColumnDefinition<StaffAttendance>> columnDefinitions = new LinkedList<>();
columnDefinitions.add(
builder.column("name", "员工姓名").columnWidth(20)
);
for (int i = 0; i < 31; i++) {
columnDefinitions.add(
builder.<StaffAttendance.Attendance>column(String.format("attendances[%d]", i), String.valueOf(i + 1))
.writeConvert((e, v) -> v.getText())
// 把请假的标记为红色
.cellStyleKey((c, e, v) -> v.equals(StaffAttendance.Attendance.LEAVE.getText()) ? "fg {color: RED}" : null)
.columnWidth(3)
);
}
// 可以用工具类方便计算出列名
final String startColumnName = ColumnUtils.getColumnNameByIndex((short) 1); // B
final String endColumnName = ColumnUtils.getColumnNameByIndex((short) 31); // AF
// 居右的红色文字样式
final CellStyleKeyProvider<StaffAttendance> redRightProvider = (c, e, v) -> "a {h: right} f {color: #ff0000}";
// 使用函数进行统计 {row} 动态行数
columnDefinitions.add(
builder.formula(Integer.class, "出勤天数", String.format("=COUNTIF(%s{row}:%s{row},\"Y\")", startColumnName, endColumnName))
.cellStyleKey(redRightProvider).columnWidth(10)
);
columnDefinitions.add(
builder.formula(Integer.class, "请假天数", String.format("=COUNTIF(%s{row}:%s{row},\"N\")", startColumnName, endColumnName))
.cellStyleKey(redRightProvider).columnWidth(10)
);
return columnDefinitions.toArray(ColumnDefinition[]::new);
}
})
.writeToFile("D:\\员工考勤表.xlsx");