EasyExcel常见场景

目录

业务场景

实现1：生成excel并用base64加密得到加密串

实现2：去掉excel表头默认格式

实现3：excel设置自定义列宽

实现4：excel文件中有一列需要设置公式，G列 = F列 - H列 - I列

实现5：G列需要设置，当数值 < 0 时 字体显示为红色

实现12345总

实现6：excel多sheet文件流组装

实现7：excel文件流发送邮件

实现8： 设置某列背景色

实现9：excel设置公式错误情况处理

---

在本次涉及到excel开发中，使用了之前了解过的com.alibaba.easyexcel，本篇记录下一些用法

业务场景

1、与Excel方交互，需要将结果list实体类 转成 excel 的形式，再用base64加密，最终以加密串的方式给交互

2、去掉excel表头默认格式

3、excel设置自定义列宽

4、excel文件中有一列需要设置公式，G列 = F列 - H列 - I列

5、G列需要设置，当数值 < 0 时 字体显示为红色

接下来逐个解决，使用easyexcel版本3.0.5

实现1：生成excel并用base64加密得到加密串

解决可见 excel写为字节流使用base64加解密_zhangm2020的博客-CSDN博客

ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
EasyExcel.write(byteArrayOutputStream, AllotTaskResult.class)
        .sheet().doWrite(buildAllotTaskResultList(list));
String excelContent = Base64Utils.encodeToString(byteArrayOutputStream.toByteArray());

实现2：去掉excel表头默认格式

由于使用easyexcel生成excel有默认的表头格式，首先需要将默认格式去掉，使用

.useDefaultStyle(false)

参考：如何清除表头的默认格式？ · Issue #556 · alibaba/easyexcel · GitHub

实现3：excel设置自定义列宽

easyexcel有留扩展点，可以实现拦截器去实现自己需要扩展的内容，自定义列宽可以使用

.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())

实现比较简单，找到每一列最长的字节长度 * 256

public class LongestMatchColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
	...

    Integer columnWidth = dataLength(cellDataList, cell, isHead);
	if (columnWidth < 0) {
	    return;
	}
	if (columnWidth > MAX_COLUMN_WIDTH) {
	    columnWidth = MAX_COLUMN_WIDTH;
	}
	Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
	if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
	    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
	    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
	}
}

private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
	//表头字段
    if (isHead) {
        return cell.getStringCellValue().getBytes().length;
    }
    WriteCellData<?> cellData = cellDataList.get(0);
    CellDataTypeEnum type = cellData.getType();
    if (type == null) {
        return -1;
    }
    switch (type) {
        case STRING:
            return cellData.getStringValue().getBytes().length;
        case BOOLEAN:
            return cellData.getBooleanValue().toString().getBytes().length;
        case NUMBER:
            return cellData.getNumberValue().toString().getBytes().length;
        default:
            return -1;
    }
}

但是，使用这个自定义结果与 excel直接操作自定义列宽 不一致

 代码生成

excel：格式 -> 自动调整列宽 

代码生成较宽，待了解

实现4：excel文件中有一列需要设置公式，G列 = F列 - H列 - I列

使用拦截器实现的扩展点：自定义Handler类实现CellWriteHandler接口

.registerWriteHandler(new XXXCellWriteHandler())

自定义类复写afterCellDispose方法：在单元格上的所有操作完成后调用的方法

public class XXXCellWriteHandler implements CellWriteHandler {

    private static final int SURPLUS_COUNT_COLUMN = 6;

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (cell.getRowIndex() == 0) {
            return;
        }

        //设置 剩余可提量 列公式
        if (SURPLUS_COUNT_COLUMN == cell.getColumnIndex()) {
            int currRowIndex = cell.getRowIndex() + 1;
            cell.setCellFormula("=F" + currRowIndex + "-H" + currRowIndex + "-I" + currRowIndex);
        }
    }
}

实现5：G列需要设置，当数值 < 0 时 字体显示为红色

在实体类字段上添加注解：

@ContentStyle(dataFormat = 38)

参考：如何设置单元格格式 · 语雀

实现12345总

最终，代码结构为

ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
EasyExcel.write(byteArrayOutputStream, AllotTaskResult.class)
        .useDefaultStyle(false)
        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
        .registerWriteHandler(new PickUpCellWriteHandler())
        .sheet().doWrite(buildAllotTaskResultList(list));
String excelContent = Base64Utils.encodeToString(byteArrayOutputStream.toByteArray());

20220324更新

实现6：excel多sheet文件流组装

ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
//定义文件，格式等
ExcelWriter excelWriter = EasyExcel.write(byteArrayOutputStream).useDefaultStyle(false).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();

//定义多个sheet
WriteSheet goodsInfoSheet = EasyExcel.writerSheet(0, excelSheetMap.get(0)).head(GoodsInfo.class).build();
WriteSheet allotCalculateResultSheet = EasyExcel.writerSheet(1, excelSheetMap.get(1)).head(AllotCalculateItemResult.class).build();
WriteSheet allotGroupByChannelResultSheet = EasyExcel.writerSheet(2, excelSheetMap.get(2)).head(AllotGroupByChannelResult.class).build();
WriteSheet goodsRemainResultSheet = EasyExcel.writerSheet(3, excelSheetMap.get(3)).head(GoodsRemainResult.class).build();

//写入每个sheet
excelWriter.write(result.getGoodsInfos(), goodsInfoSheet)
        .write(result.getAllotCalculateItemResults(), allotCalculateResultSheet)
        .write(result.getAllotGroupByChannelResults(), allotGroupByChannelResultSheet)
        .write(result.getGoodsRemainResults(), goodsRemainResultSheet).finish();

注意：将每个sheet格式定义放在文件定义时，否则不生效。例如：.useDefaultStyle(false) 去掉表头默认格式

实现7：excel文件流发送邮件

邮件带附件，以文件流发送，需要构建dataSource

 其中，excel对应的content_type为"application/excel"

private static final String CONTENT_FILE_TYPE = "application/excel";

//附件list
List<MailAttach> list = Lists.newArrayList(MailAttach.build(fileName, byteArrayOutputStream.toByteArray(), CONTENT_FILE_TYPE, ""));

//发送
emailManager.sendEmail(notifyUsers, SUCCESS_SUBJECT, content, list);

20220629更新

实现8： 设置某列背景色

@ExcelProperty("XXX")
@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 13)
private int allotNum;

常用注解参考：easyexcel中的常用注解_想养一只！的博客-CSDN博客_easyexcel注解

颜色对应

实现9：excel设置公式错误情况处理

=IF(ISNA(), "", "")：ISNA

=IF(ISERR(), "", "")：ISERR是否为错误值（除#N/A错误之外的任意错误值）

=IF(ISERROR(), "", "")：ISERROR确定一个数字或表达式是否错误，例如：A1/A2，A1、A2为非数据或A2为空或A2为0，都会显示true