FastExcel实现Excel文件读写以及模板填充(Java)

本文介绍了一个基于Java的商铺信息导出功能，利用Apache POI和Hutool库将商铺数据导出到Excel表格中。系统首先从数据库查询个体和企业商铺信息，然后转换成Excel所需的格式，并设置不同的工作表分别展示个体和企业的详细信息，最后将数据写入Excel并提供下载。

项目背景

EasyExcel 是阿里巴巴开源的一个Excel处理框架，它以使用简单、节省内存著称，并且在解析Excel时采用了流式处理机制，避免了将整个文件数据一次性加载到内存中，从而降低了内存占用并提高了读写速度。

随着阿里巴巴宣布停止对EasyExcel的主动更新，原作者离开了阿里并创建了一个新的项目——FastExcel。FastExcel不仅继承了EasyExcel的所有特性，还在此基础上进行了显著的性能优化，并增加了新功能

1. 环境准备与依赖引入

在 pom.xml 中添加以下依赖：

<dependency>
    <groupId>cn.idev.excel</groupId>
    <artifactId>fastexcel</artifactId>
    <version>1.1.0</version>
</dependency>

2. 定义实体类

通过 @ExcelProperty 注解实现 Excel 列与 Java 对象的映射：

@Data
public class User {
    @ExcelProperty("编号")
    private Integer id;
    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty("年龄")
    private Integer age;
}

3. 简单导出

List<User> users = new ArrayList<>();
users.add(new User(1, "张三", 25));
users.add(new User(2, "李四", 30));

FastExcel.write("output.xlsx")
    .sheet("用户列表")
    .head(User.class)
    .doWrite(users);

4. 复杂样式导出

4.1 自定义样式处理器

public class CustomCellWriteHandler implements CellWriteHandler {

    private final WriteCellStyle headWriteCellStyle;
    private final WriteCellStyle contentWriteCellStyle;

    public CustomCellWriteHandler(WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {
        this.headWriteCellStyle = headWriteCellStyle;
        this.contentWriteCellStyle = contentWriteCellStyle;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        // 在创建单元格之前的操作（如果需要）
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 设置表头样式
        if (isHead) {
            cell.setCellStyle(headWriteCellStyle);
        } else {
            cell.setCellStyle(contentWriteCellStyle);
        }
    }

    @Override
    public int order() {
        return 50001;
    }
}

4.2 定义样式

public class ComplexExportDemo {
    public static void main(String[] args) {
        // 准备数据
        List<User> users = new ArrayList<>();
        users.add(new User("张三", 25, "zhangsan@example.com"));
        users.add(new User("李四", 30, "lisi@example.com"));

        // 定义样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

        // 写入Excel
        String fileName = "complex_export.xlsx";
        ExcelWriter excelWriter = EasyExcel.write(fileName)
                .registerWriteHandler(new CustomCellWriteHandler(headWriteCellStyle, contentWriteCellStyle))
                .build();
        WriteSheet writeSheet = EasyExcel.writerSheet("用户数据").build();
        excelWriter.write(users, writeSheet);

        // 关闭资源
        excelWriter.close();
    }
}

5. 多sheet导出

public class MultiSheetExportDemo {
    public static void main(String[] args) {
        // 准备数据
        List<User> users1 = new ArrayList<>();
        users1.add(new User("张三", 25, "zhangsan@example.com"));
        users1.add(new User("李四", 30, "lisi@example.com"));

        List<User> users2 = new ArrayList<>();
        users2.add(new User("王五", 35, "wangwu@example.com"));
        users2.add(new User("赵六", 40, "zhaoliu@example.com"));

        // 写入Excel
        String fileName = "multi_sheet_export.xlsx";
        ExcelWriter excelWriter = EasyExcel.write(fileName).build();

        // 写入第一个sheet
        WriteSheet writeSheet1 = EasyExcel.writerSheet("用户数据1").build();
        excelWriter.write(users1, writeSheet1);

        // 写入第二个sheet
        WriteSheet writeSheet2 = EasyExcel.writerSheet("用户数据2").build();
        excelWriter.write(users2, writeSheet2);

        // 关闭资源
        excelWriter.close();
    }
}

6. 数据转换

实现 Converter 接口来进行数据转换。例如，将学号属性 userNo 统一在 Excel 的值加上前缀 uno:，性别属性 gender 由字符串转枚举值。

6.1 学号转换器

public class UserNoConverter implements Converter<String> {

    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public String convertToJavaData(ReadConverterContext<?> context) {
        return "uno:" + context.getReadCellData().getStringValue();
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) {
        return new WriteCellData<>(context.getValue());
    }
}

6.2 性别转换器

public class GenderConverter implements Converter<Integer> {

    @Override
    public Class<?> supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        String value = cellData.getStringValue();
        if (StringUtils.isBlank(value)) {
            return 2; // 未知
        }
        if (value.indexOf('男') != -1) {
            return 0;
        }
        if (value.indexOf('女') != -1) {
            return 1;
        }
        return 2;
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) {
        Integer value = context.getValue();
        if (Objects.equals(value, 0)) {
            return new WriteCellData<>("男");
        }
        if (Objects.equals(value, 1)) {
            return new WriteCellData<>("女");
        }
        return new WriteCellData<>("未知");
    }
}

在映射的实体类 User 进行转换器绑定：

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class User {

    @ExcelProperty(index = 0)
    private Long id;
    @ExcelProperty(index = 1, converter = UserNoConverter.class)
    private String userNo;
    @ExcelProperty(index = 3, converter = GenderConverter.class)
    private Integer gender;
    @ExcelProperty(index = 2)
    private String name;
    @ExcelProperty(index = 4)
    private Date birthday;
    @ExcelProperty(index = 5)
    private String phone;
    @ExcelProperty(index = 6)
    private String email;
    @ExcelIgnore
    private Integer isDelete;
    @ExcelProperty(index = 7)
    private String address;
}