Happy3w

Excel工具推荐

2020.12.06

工作中有时会遇到一些需要读写Excel的需求,比如:

  • 主数据的导入导出;
  • 业务数据的批量上传;
  • 批量下载查询结果

与CSV比较起来更多的用户还是比较能接受Excel。
不废话,直接上例子。
用户数据数据如下,每个属性都有对应的getter和setter

public class Student {
    private String name;	// 名字
    private Date birthday;	/ 生日
    private int age;		// 年龄
    private double weight;	// 体重
    private long updateTime;	// 数据更新时间
    private boolean studying;	// 是否在校生
}

在读写这个数据之前,先库引入到项目,Maven通过如下代码添加

<dependency>
    <groupId>com.happy3w</groupId>
    <artifactId>persistence-excel</artifactId>
    <version>0.0.4</version>
</dependency>

Gradle项目通过如下配置添加

implementation 'com.happy3w:persistence-excel:0.0.4'

方法一:注解法

第一步在Student上增加注解。这里补充了很多业务规则,比如

  • Excel标题
  • 数字格式
  • 日期格式
  • 时区信息
  • 背景颜色
  • 特殊转换
  • 数据导入时的验证

具体规则参见注解后面的注释内容,代码在

https://github.com/boroborome/persistence-excel/blob/main/src/test/java/com/happy3w/persistence/excel/demo/Student.java

@NumFormat("#.00")                          // 配置默认数字格式为固定显示两位小数
public class Student {
    @ObjRdColumn(value = "名字")             // 配置这个字段在文件中的列头名称
    @FillForegroundColor(HssfColor.RED)     // 配置在导出Excel时使用红色背景色(这个在库persistence-excel中)
    private String name;

    @ObjRdColumn("生日")
    @DateFormat("yyyy-MM-dd")               // 配置使用的时间格式
    private Date birthday;

    @ObjRdColumn(value = "年龄", required = false) // 年龄不是必填项
    @NumFormat("000")                       // 年龄显示不需要小数
    private Integer age;

    @ObjRdColumn(value = "体重")    	    // 这里没有配置数字格式,使用前面配置的默认格式,两位小数显示
    private double weight;

    @ObjRdColumn("更新时间")
    @DateFormat("yyyy-MM-dd HH:mm:ss")
    @DateZoneId("UTC-8")                    // 配置读写文件时使用的时区
    private long updateTime;

    // 在校生信息需要经过转换才能变成boolean,通过配置的getter和setter转换
    @ObjRdColumn(value = "在校生", getter = "getStudyingText", setter = "setStudyingText")
    private boolean studying;

    /**
     * 配置数据从文件加载后需要额外做的一些操作。比如年龄必须大于0,小于100的检测;名字可能带有不需要的前缀,需要去掉。
     * ObjRdPostAction对被注解的方法名称、参数个数、参数顺序都没有要求,但一个对象只能有一个postAction。工具根据需要自动注入
     * @param data 刚刚解析数据使用的行信息,包括page name,行数等信息
     * @param recorder 如果有需要返回给用户的消息,通过这个recorder记录下来
     */
    @ObjRdPostAction
    public void postInit(RdRowWrapper<Student> data, MessageRecorder recorder) {
        if (age != null && (age < 0 || age > 100)) {
            recorder.appendError("Wrong age:{0}", age);
        }
        if (name.startsWith("Name:")) {
            name = name.substring(5);
        }
    }

    public String getStudyingText() {
        return studying ? "在校" : "毕业";
    }

    // 列头注册的setter方法可以带有两个额外的参数,属性值必须在第一位,其他参数数量和顺序没有要求,工具自动注入
    public void setStudyingText(String studyingText, RdRowWrapper<Student> data, MessageRecorder recorder) {
        this.studying = "在校".equals(studyingText);
    }
}

写数据方法

代码在这里https://github.com/boroborome/persistence-excel/blob/main/src/test/java/com/happy3w/persistence/excel/demo/DemoTest.java

// orgStudentList是保存Student数据的列表

// 创建一个Excel workbook,并创建一个test-page Sheet页面用于保存数据
Workbook workbook = ExcelUtil.newXlsxWorkbook();
SheetPage page = SheetPage.of(workbook, "test-page");

// 重点:通过Student创建一个"行数据表定义"
ObjRdTableDef<Student> objRdTableDef = ObjRdTableDef.from(Student.class);

// 通过"行数据助理"将数据写入excel page
RdAssistant.writeObj(orgStudentList.stream(), page, objRdTableDef);

读数据方法

// 从文件或者什么流中读入workbook。这里同时支持xlsx或者xls格式
Workbook readWorkbook = ExcelUtil.openWorkbook(new FileInputStream(new File("test.xlsx")));
SheetPage readPage = SheetPage.of(readWorkbook, "test-page");

// 创建用于接收错误信息的recorder
MessageRecorder messageRecorder = new MessageRecorder();
ObjRdTableDef<Student> objRdTableDef = ObjRdTableDef.from(Student.class);

// 将page中所有数据读取出来,错误信息记录到recorder中
List<Student> newDataList = RdAssistant.readObjs(readPage, objRdTableDef, messageRecorder)
  .collect(Collectors.toList());

if (messageRecorder.isSuccess()) {
  // 保存数据到数据库
}  else {
  // messageRecorder.getErrors();// 读取所有错误信息,如果Excel中有多个错误,这里是多个错误
  // messageRecorder.toResponse();// 将errors,warnings等各种信息转换为一个response返回
}

方法二:自定义

方法一是通过注解构建ObjRdTableDef,但是有时数据对象不是我们的,不能在上面添加注解,或者干脆这个对象就不存在,只是一组需要导出的数据,此时我们可以直接创建一个RdTableDef,这里没有Obj了。

RdTableDef rdTableDef = new RdTableDef();
rdTableDef.config(new NumFormatCfg("#.00"))
        .setColumns(Arrays.asList(RdColumnDef.builder() // 按照Excel中出现的Title顺序填写
                        .title("名字")
                        .dataType(String.class)         // 数据类型是用于读取Excle用的,如果只用于写入,可以不填写这个信息
                        .extConfigs(createExtConfigs(new FillForegroundColorCfg(HssfColor.RED)))
                        .build(),
                RdColumnDef.builder()
                        .title("生日")
                        .dataType(Date.class)
                        .extConfigs(createExtConfigs(new DateFormatCfg("yyyy-MM-dd")))
                        .build(),
                RdColumnDef.builder()
                        .title("年龄")
                        .dataType(Integer.class)
                        .extConfigs(createExtConfigs(new NumFormatCfg("000")))
                        .build(),
                RdColumnDef.builder()
                        .title("体重")
                        .dataType(Double.class)
                        .build(),
                RdColumnDef.builder()
                        .title("更新时间")
                        .dataType(Long.class)
                        .extConfigs(createExtConfigs(new DateFormatCfg("yyyy-MM-dd HH:mm:ss"),
                                new DateZoneIdCfg("UTC-8")))
                        .build(),
                RdColumnDef.builder()
                        .title("在校生")
                        .dataType(String.class)
                        .build()
        ));

private ExtConfigs createExtConfigs(IRdConfig...configs) {
    ExtConfigs extConfigs = new ExtConfigs();
    for (IRdConfig config : configs) {
        extConfigs.regist(config);
    }
    return extConfigs;
}

有了这个rdTableDef,我们可以开始读写Excle了

写数据方法

// 创建一个Excel workbook,并创建一个test-page Sheet页面用于保存数据
Workbook workbook = ExcelUtil.newXlsxWorkbook();
SheetPage page = SheetPage.of(workbook, "test-page");

// 通过"行数据助理"将数据写入excel page
RdAssistant.writeObj(orgStudentList.stream().map(s ->	// 这里的数据不再是一个对象,而是一个列表
                Arrays.asList(s.getName(), s.getBirthday(), s.getAge(), s.getWeight(), s.getUpdateTime(), s.getStudyingText())),
        page, rdTableDef);

workbook.write(new FileOutputStream(new File("test.xlsx")));

读数据方法

// 从文件或者什么流中读入workbook。这里同时支持xlsx或者xls格式
Workbook readWorkbook = ExcelUtil.openWorkbook(new FileInputStream(new File("test.xlsx")));
SheetPage readPage = SheetPage.of(readWorkbook, "test-page");

// 创建用于接收错误信息的recorder
MessageRecorder messageRecorder = new MessageRecorder();

// 将page中所有数据读取出来,错误信息记录到recorder中。这里读到的数据是一个列表
List<List<?>> newDataList = RdAssistant.readObjs(readPage, rdTableDef, messageRecorder)
        .collect(Collectors.toList());

if (messageRecorder.isSuccess()) {
    // 保存数据到数据库
}

其他

上面是基本使用,作为一个工具,在做到使用简单的同时还需要有扩展性。这个工具在可以在如下维度扩展

  • 扩展数据类型的转换规则。比如Demo中展示了long当做时间类型处理的案例,其实Excel中数据类型和模型中数据类型不一致的时候系统都会进行自动转换。
  • 扩展从Excel读写不同数据类型的逻辑。比如时间类型数据如何从Excel单元格读取出来?参见ICellAccessor的使用
  • 扩展对配置信息的解析方式。比如:如何将配置NumFormatCfg构建一个Excel中的样式?参见IRdConfig的使用
  • 扩展对象上可以使用的样式注解。比如:如何增加字体配置的注解?参见:https://github.com/boroborome/persistence-core#%E6%89%A9%E5%B1%95%E6%B3%A8%E8%A7%A3

其他功能都参见

https://github.com/boroborome/persistence-excel

当前连接:
http://www.happy3w.com/archives/e-x-c-e-l-gong-ju-tui-jian