Hutool Java 工具类库导出 Excel，超级简单！

首先在POM.xml中加入GAV

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.0.7</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.17</version>
</dependency>

Excel写入

实体类

import lombok.AllArgsConstructor;
import lombok.Data;
import java.util.Date;

@Data
@AllArgsConstructor
public class User {
    private String name;
    private int age;
    private Date birthday;
}

然后在控制层使用就行

@RequestMapping("/export")
@ResponseBody
public void export(HttpServletResponse response) {
    List<User> list = new ArrayList<>();
    list.add(new User("zhangsan0", "1231", new Date()));
    list.add(new User("zhangsan1", "1232", new Date()));
    list.add(new User("zhangsan2", "1233", new Date()));
    list.add(new User("zhangsan3", "1234", new Date()));
    list.add(new User("zhangsan4", "1235", new Date()));
    list.add(new User("zhangsan5", "1236", new Date()));
    // 通过工具类创建writer，默认创建xls格式
    ExcelWriter writer = ExcelUtil.getWriter();
    //自定义标题别名
    writer.addHeaderAlias("name", "姓名");
    writer.addHeaderAlias("age", "年龄");
    writer.addHeaderAlias("birthDay", "生日");
    // 合并单元格后的标题行，使用默认标题样式
    writer.merge(2, "申请人员信息");
    // 一次性写出内容，使用默认样式，强制输出标题
    writer.write(list, true);
    //out为OutputStream，需要写出到的目标流
    //response为HttpServletResponse对象
    response.setContentType("application/vnd.ms-excel;charset=utf-8");
    //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
    String name = StringUtils.toUtf8String("申请学院");
    response.setHeader("Content-Disposition", "attachment;filename=" + name + ".xls");
    ServletOutputStream out = null;
    try {
        out = response.getOutputStream();
        writer.flush(out, true);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        // 关闭writer，释放内存
        writer.close();
    }
    //此处记得关闭输出Servlet流
    IoUtil.close(out);
}

Excel读取

1.读取Excel中所有行和列，都用列表表示

ExcelReader reader = ExcelUtil.getReader("d:/aaa.xlsx");
List<List<Object>> readAll = reader.read();

2.读取为Map列表，默认第一行为标题行，Map中的key为标题，value为标题对应的单元格值。

ExcelReader reader = ExcelUtil.getReader("d:/aaa.xlsx");
List<Map<String,Object>> readAll = reader.readAll();

3.读取为Bean列表，Bean中的字段名为标题，字段值为标题对应的单元格值。

ExcelReader reader = ExcelUtil.getReader("d:/aaa.xlsx");
List<Person> all = reader.readAll(Person.class);

本文Excel导入读取示例采用的是第三种方法实现，相关代码如下所示：

@RequestMapping(READ_EXCEL)
public void readExcel(){
    ExcelReader reader = ExcelUtil.getReader("H:\\user.xlsx");
    List<UserDTO> userDTOS = reader.readAll(UserDTO.class);
    //日志输出读取到的信息
    log.info(userDTOS.toString());
}

其中UserDTO类的代码如下所示：

@Data
public class UserDTO {
   
    @NotNull(message = "用户id不能为空")
    private Integer id;

    @NotNull(message = "用户名不能为空")
    @Size(min = 4, max = 16, message = "用户名长度错误")
    private String userName;

    @NotNull(message = "密码不能为空")
    @Size(min = 4, max = 16, message = "密码长度错误")
    private String loginPassword;

    @NotNull(message = "邮箱不能为空")
    @Email(message = "邮箱格式错误")
    private String email;

    @NotNull(message = "日期不能为空")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    @JSONField(format = "yyyy-MM-dd HH:mm:ss")
    private Date createDate;
}

1、ExcelUtil

从文件中读取Excel为ExcelReader

ExcelReader reader = ExcelUtil.getReader(FileUtil.file("test.xlsx"));

从流中读取Excel为ExcelReader

ExcelReader reader = ExcelUtil.getReader(ResourceUtil.getStream("aaa.xlsx"));

读取指定的sheet

ExcelReader reader;
//通过sheet编号获取
reader = ExcelUtil.getReader(FileUtil.file("test.xlsx"), 0);
//通过sheet名获取
reader = ExcelUtil.getReader(FileUtil.file("test.xlsx"), "sheet1");

2、ExcelReader

读取Excel中所有行和列，都用列表表示

ExcelReader reader = ExcelUtil.getReader("d:/aaa.xlsx");
List<List<Object>> readAll = reader.read();

读取为Map列表，默认第一行为标题行，Map中的key为标题，value为标题对应的单元格值

ExcelReader reader = ExcelUtil.getReader("d:/aaa.xlsx");
List<Map<String,Object>> readAll = reader.readAll();

读取为Bean列表，Bean中的字段名为标题，字段值为标题对应的单元格值

ExcelReader reader = ExcelUtil.getReader("d:/aaa.xlsx");
List<Person> all = reader.readAll(Person.class);

3、ExcelWriter

Hutool将Excel写出封装为ExcelWriter，原理为包装了Workbook对象，每次调用merge（合并单元格）或者write（写出数据）方法后只是将数据写入到Workbook，并不写出文件，只有调用flush或者close方法后才会真正写出文件。
由于机制原因，在写出结束后需要关闭ExcelWriter对象，调用close方法即可关闭，此时才会释放Workbook对象资源，否则带有数据的Workbook一直会常驻内存

（1）将行列对象写出到Excel

List<String> row1 = CollUtil.newArrayList("aa", "bb", "cc", "dd");
List<String> row2 = CollUtil.newArrayList("aa1", "bb1", "cc1", "dd1");
List<String> row3 = CollUtil.newArrayList("aa2", "bb2", "cc2", "dd2");
List<String> row4 = CollUtil.newArrayList("aa3", "bb3", "cc3", "dd3");
List<String> row5 = CollUtil.newArrayList("aa4", "bb4", "cc4", "dd4");

List<List<String>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5);

//通过工具类创建writer
ExcelWriter writer = ExcelUtil.getWriter("d:/writeTest.xlsx");
//通过构造方法创建writer
//ExcelWriter writer = new ExcelWriter("d:/writeTest.xls");

//跳过当前行，既第一行，非必须，在此演示用
writer.passCurrentRow();

//合并单元格后的标题行，使用默认标题样式
writer.merge(row1.size() - 1, "测试标题");
//一次性写出内容，强制输出标题
writer.write(rows, true);
//关闭writer，释放内存
writer.close();

（2）写出Map数据

Map<String, Object> row1 = new LinkedHashMap<>();
row1.put("姓名", "张三");
row1.put("年龄", 23);
row1.put("成绩", 88.32);
row1.put("是否合格", true);
row1.put("考试日期", DateUtil.date());

Map<String, Object> row2 = new LinkedHashMap<>();
row2.put("姓名", "李四");
row2.put("年龄", 33);
row2.put("成绩", 59.50);
row2.put("是否合格", false);
row2.put("考试日期", DateUtil.date());

ArrayList<Map<String, Object>> rows = CollUtil.newArrayList(row1, row2);

// 通过工具类创建writer
ExcelWriter writer = ExcelUtil.getWriter("d:/writeMapTest.xlsx");
// 合并单元格后的标题行，使用默认标题样式
writer.merge(row1.size() - 1, "一班成绩单");
// 一次性写出内容，使用默认样式，强制输出标题
writer.write(rows, true);
// 关闭writer，释放内存
writer.close();

（3）写出Bean数据

public class TestBean {
    private String name;
    private int age;
    private double score;
    private boolean isPass;
    private Date examDate;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public double getScore() {
        return score;
    }

    public void setScore(double score) {
        this.score = score;
    }

    public boolean isPass() {
        return isPass;
    }

    public void setPass(boolean isPass) {
        this.isPass = isPass;
    }

    public Date getExamDate() {
        return examDate;
    }

    public void setExamDate(Date examDate) {
        this.examDate = examDate;
    }
}

TestBean bean1 = new TestBean();
bean1.setName("张三");
bean1.setAge(22);
bean1.setPass(true);
bean1.setScore(66.30);
bean1.setExamDate(DateUtil.date());

TestBean bean2 = new TestBean();
bean2.setName("李四");
bean2.setAge(28);
bean2.setPass(false);
bean2.setScore(38.50);
bean2.setExamDate(DateUtil.date());

List<TestBean> rows = CollUtil.newArrayList(bean1, bean2);

// 通过工具类创建writer
ExcelWriter writer = ExcelUtil.getWriter("d:/writeBeanTest.xlsx");
// 合并单元格后的标题行，使用默认标题样式
writer.merge(4, "一班成绩单");
// 一次性写出内容，使用默认样式，强制输出标题
writer.write(rows, true);
// 关闭writer，释放内存
writer.close();

（4）自定义Bean的key别名（排序标题）

在写出Bean的时候，我们可以调用ExcelWriter对象的addHeaderAlias方法自定义Bean中key的别名，这样就可以写出自定义标题了

// 通过工具类创建writer
ExcelWriter writer = ExcelUtil.getWriter("d:/writeBeanTest.xlsx");

//自定义标题别名
writer.addHeaderAlias("name", "姓名");
writer.addHeaderAlias("age", "年龄");
writer.addHeaderAlias("score", "分数");
writer.addHeaderAlias("isPass", "是否通过");
writer.addHeaderAlias("examDate", "考试时间");

// 合并单元格后的标题行，使用默认标题样式
writer.merge(4, "一班成绩单");
// 一次性写出内容，使用默认样式，强制输出标题
writer.write(rows, true);
// 关闭writer，释放内存
writer.close();

posted @ 2021-02-04 16:57 Bonnie_ξ 阅读(2169) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Bonnie_ξ