一、 动态表头案例
生成该种样式 excel的核心功能点有两个：
1. 如何保证表头动态可变
解决该问题需要使用到EasyExcel不创建对象写的方式来实现
2. 如何定义单元格合并策略
AbstractMergeStrategy 继承该类来实现自定义单元格合并策略
/**
* 自定义单元格合并策略
*/
public class CellMergeStrategy extends AbstractMergeStrategy {
/** 唯一标识列(该列cell内容一定是全局唯一的,当cell的值相等时才能进行下一列合并) */
private Integer uniqueColumnIndex;
/** 从哪一行开始合并 */
private Integer mergeRowIndex = 0;
/** 合并列编号,从0开始 */
private List<Integer> mergeColumnIndex = Lists.newArrayList();
private CellMergeStrategy() {
}
public CellMergeStrategy(Integer uniqueColumnIndex, Integer mergeRowIndex, Set<Integer> mergeColumnIndex) {
mergeColumnIndex.stream().forEach(item -> {
this.mergeColumnIndex.add(item);
});
this.mergeColumnIndex.stream().sorted();
if (null == uniqueColumnIndex) {
this.uniqueColumnIndex = this.mergeColumnIndex.get(0);
} else {
this.uniqueColumnIndex = uniqueColumnIndex;
}
this.mergeRowIndex = mergeRowIndex;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
int curColIndex = cell.getColumnIndex();
int curRowIndex = cell.getRowIndex();
// 判断该列是否需要合并
if (!mergeColumnIndex.contains(curColIndex)) {
return;
}
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.size(); i++) {
if (curColIndex == mergeColumnIndex.get(i)) {
this.mergeRow(sheet, cell, curRowIndex, curColIndex, uniqueColumnIndex);
break;
}
}
}
}
/**
* 向上合并单元格
* @param cell 当前单元格
* @param rowIndex 当前行
* @param colIndex 当前列
* @param uniqueColIndex 唯一标识列(该列cell内容一定是全局唯一的,当cell的值相等时才能进行下一列合并)
*/
private void mergeRow(Sheet sheet, Cell cell, int rowIndex, int colIndex, int uniqueColIndex) {
Object curCellValue = getCellValue(cell);
Object preCellValue = getCellValue(cell.getSheet().getRow(rowIndex - 1).getCell(colIndex));
boolean cellEqual = preCellValue.equals(curCellValue);
boolean baseCellEqual = true;
if (colIndex >= uniqueColIndex) {
Object baseCellValue = getCellValue(cell.getRow().getCell(uniqueColIndex));
Object preBaseCellValue = getCellValue(cell.getSheet().getRow(rowIndex - 1).getCell(uniqueColIndex));
baseCellEqual = baseCellValue.equals(preBaseCellValue);
}
/**
* 合并条件
* 1. 将当前单元格数据与上一个单元格数据比较,相同则执行合并逻辑
* 2. 唯一标识列内容相同，才能进行下一列合并
*/
if (!(cellEqual && baseCellEqual)) {
return;
}
List<CellRangeAddress> mergeRegionList = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegionList.size() && !isMerged; i++) {
CellRangeAddress cellRange = mergeRegionList.get(i);
// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellRange.isInRange(rowIndex - 1, colIndex)) {
sheet.removeMergedRegion(i);
cellRange.setLastRow(rowIndex);
sheet.addMergedRegion(cellRange);
isMerged = true;
}
}
// 若上一个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRange = new CellRangeAddress(rowIndex - 1, rowIndex, colIndex, colIndex);
sheet.addMergedRegion(cellRange);
}
}
private Object getCellValue(Cell baseCell) {
return CellType.STRING.equals(baseCell.getCellType()) ? baseCell.getStringCellValue() : baseCell.getNumericCellValue();
}
}
3. 案例的代码实现
public class AttExportDataTemplateSecond {
public static void main(String[] args) {
String writeFileName = FilePathUtil.getPath() + "att-second-" + System.currentTimeMillis() + ".xlsx";
EasyExcel.write(writeFileName)
.head(createDynamicHead())
.registerWriteHandler(new CellMergeStrategy(0, 0, Sets.newHashSet(0, 1, 2)))
.excelType(ExcelTypeEnum.XLSX)
.sheet("考勤报表")
.doWrite(createDataByList());
}
// 模拟填充数据
private static List<List<Object>> createDataByList() {
List<List<Object>> list = ListUtils.newArrayList();
for (int i = 0; i < 2; i++) {
List<Object> data = ListUtils.newArrayList();
data.add("RY044" + i);
data.add("张三" + i);
data.add("技术部");
data.add("出勤工时");
List<Integer> dayKeyList = DateUtil.getBetweenDateKey(1643644800L, 1646063999L);
for (Integer integer : dayKeyList) {
data.add("");
}
list.add(data);
List<Object> data1 = ListUtils.newArrayList();
data1.add("RY044" + i);
data1.add("张三" + i);
data1.add("技术部");
data1.add("加班工时");
for (Integer integer : dayKeyList) {
int hour = new Random().nextInt(5);
if (0 == hour) {
data1.add("");
} else {
data1.add(new Random().nextInt(5));
}
}
list.add(data1);
List<Object> data2 = ListUtils.newArrayList();
data2.add("RY044" + i);
data2.add("张三" + i);
data2.add("技术部");
data2.add("考勤补贴");
for (Integer integer : dayKeyList) {
data2.add("");
}
list.add(data2);
List<Object> data3 = ListUtils.newArrayList();
data3.add("RY044" + i);
data3.add("张三" + i);
data3.add("技术部");
data3.add("请假旷工");
for (Integer integer : dayKeyList) {
data3.add("");
}
list.add(data3);
}
return list;
}
// 创建动态日期表头
private static List<List<String>> createDynamicHead() {
List<List<String>> list = ListUtils.newArrayList();
List<String> jobNo = ListUtils.newArrayList();
jobNo.add("202201月考勤报表");
jobNo.add("工号");
list.add(jobNo);
List<String> name = ListUtils.newArrayList();
name.add("202201月考勤报表");
name.add("姓名");
list.add(name);
List<String> dept = ListUtils.newArrayList();
dept.add("202201月考勤报表");
dept.add("部门");
list.add(dept);
List<String> attendanceItem = ListUtils.newArrayList();
attendanceItem.add("202201月考勤报表");
attendanceItem.add("考勤项");
list.add(attendanceItem);
/**2022-02-01 到 2022-02-28*/
List<Integer> dayKeyList = DateUtil.getBetweenDateKey(1643644800L, 1646063999L);
for (Integer day : dayKeyList) {
List<String> oneDay = ListUtils.newArrayList();
oneDay.add("202201月考勤报表");
oneDay.add(day + "");
list.add(oneDay);
}
List<String> total = ListUtils.newArrayList();
total.add("202201月考勤报表");
total.add("合计");
list.add(total);
return list;
}
}
二、 单元格合并案例
实际开发中经常会遇到一个订单号对应多个商品，此时要以订单编号单元格。
public class MergeCellDataByOrderId {
public static void main(String[] args) {
String writeFileName = FilePathUtil.getPath() + "order-id-" + System.currentTimeMillis() + ".xlsx";
/**从哪行开始合并*/
int mergeRowIndex = 1;
ExcelWriter writer = null;
try {
writer = EasyExcel.write(writeFileName, Order.class)
.registerWriteHandler(new CellMergeStrategy(0, mergeRowIndex, Sets.newHashSet(0, 1)))
.build();
WriteSheet writeSheet = EasyExcel.writerSheet("订单信息").build();
writer.write(createData(), writeSheet);
} finally {
if (writer != null) {
writer.finish();
}
}
}
// 模拟数据填充
private static List<Order> createData() {
List<Order> orderList = Lists.newArrayList();
Order order = new Order();
String orderId = "A" + System.currentTimeMillis();
order.setOrderId(orderId);
order.setName("西红柿鸡蛋");
order.setCount(2);
order.setPrice(BigDecimal.valueOf(8.35));
orderList.add(order);
Order order1 = new Order();
order1.setOrderId(orderId);
order1.setName("西红柿鸡蛋1");
order1.setCount(4);
order1.setPrice(BigDecimal.valueOf(12.35));
orderList.add(order1);
return orderList;
}
// 订单实体对象
@Data
private static class Order {
@ExcelProperty(value = "订单编号", index = 0)
private String orderId;
@ExcelProperty(value = "商品名称", index = 1)
private String name;
@ExcelProperty(value = "商品价格", index = 2)
private BigDecimal price;
@ExcelProperty(value = "商品数量", index = 3)
private Integer count;
}
}
三、自适应列宽
比如在实际开发中，我们导出的部门路径很长，在单个单元格中显示成如上图所示，该怎么办？
- 让列宽可以根据单元格内容长度自适应
- 超过单元格能够支持最大长度时自动换行(单个单元格最大能支持
256 * 255的长度，如果设置超过这个长度导出时会报错)
public void setColumnWidth(int columnIndex, int width) {
if(width > 255*256) {
throw new IllegalArgumentException("The maximum column width for an individual cell is 255 characters.");
}
columnHelper.setColWidth(columnIndex, (double)width/256);
columnHelper.setCustomWidth(columnIndex, true);
}
关于自适应列宽，EasyExcel中可以使用 LongestMatchColumnWidthStyleStrategy，它可以根据单元格内容自动适配列宽。如果还不满足你的需要你可以继承AbstractColumnWidthStyleStrategy，自定义适配规则。
/**
* 自适应列宽
*/
public class AdaptColumnWidth {
private static final short FONT_HEIGHT_IN_POINTS = 11;
public static void main(String[] args) {
String writeFileName = FilePathUtil.getPath() + "employee-" + System.currentTimeMillis() + ".xlsx";
ExcelWriter writer = null;
try {
writer = EasyExcel.write(writeFileName, Employee.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(getHorizontalCellStyleStrategy())
.build();
WriteSheet writeSheet = EasyExcel.writerSheet("员工信息").build();
writer.write(createData(), writeSheet);
} finally {
if (writer != null) {
writer.finish();
}
}
}
// 设置表头样式、单元格内容自动换行
public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
// excel 表头格式设置
WriteCellStyle headCellStyle = new WriteCellStyle();
headCellStyle.setFillForegroundColor(IndexedColors.TEAL.getIndex()); //蓝绿色
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints(FONT_HEIGHT_IN_POINTS);
headWriteFont.setFontName("微软雅黑");
headWriteFont.setBold(Boolean.TRUE); //字体加粗
//设置字体
headCellStyle.setWriteFont(headWriteFont);
//自动换行
headCellStyle.setWrapped(Boolean.TRUE);
//垂直居中
headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//表头边框
headCellStyle.setBorderLeft(BorderStyle.THIN);
headCellStyle.setBorderRight(BorderStyle.THIN);
// excel表格内容样式设置
WriteCellStyle contentCellStyle = new WriteCellStyle();
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints(FONT_HEIGHT_IN_POINTS);
contentWriteFont.setFontName("微软雅黑");
contentWriteFont.setFontHeightInPoints(FONT_HEIGHT_IN_POINTS);
contentCellStyle.setWriteFont(contentWriteFont);
//自动换行
contentCellStyle.setWrapped(Boolean.TRUE);
//垂直居中
contentCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headCellStyle, contentCellStyle);
return horizontalCellStyleStrategy;
}
private static List<Employee> createData() {
List<Employee> orderList = Lists.newArrayList();
Employee employee = new Employee();
employee.setName("张三");
employee.setDeptName("唐门科技有限公司/技术部/暗器冶炼部");
employee.setJobName("软件开发工程师");
orderList.add(employee);
Employee employee1 = new Employee();
employee1.setName("李四");
employee1.setDeptName("唐门科技有限公司/总裁办公室/人力资源部/技术部/AAAAAAAAAAA/BBBBBBBBBBB/CCCCCCCCCCCC/DDDDDDDDDD/EEEEEEEEE/FFFFFFFF/GGGGGGGGGG/HHHHHHHHHHHH");
employee1.setJobName("CEO");
orderList.add(employee1);
return orderList;
}
@Data
private static class Employee {
@ExcelProperty(value = "姓名", index = 0)
private String name;
@ExcelProperty(value = "部门", index = 1)
private String deptName;
@ExcelProperty(value = "职位", index = 2)
private String jobName;
}
}
- 最终导出的效果
