欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Hutool Java工具类库_ExcelUtil的使用

程序员文章站 2022-03-24 07:53:28
目录hutool java工具类库_excelutil依赖excelutilexcelreaderexcelwriterjava解析excel使用hutool工具类hutool java工具类库_ex...

hutool java工具类库_excelutil

依赖

<!--hutool java工具包-->
    <dependency>
       <groupid>cn.hutool</groupid>
       <artifactid>hutool-all</artifactid>
       <version>5.4.5</version>
 </dependency>

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");

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);

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();

java解析excel使用hutool工具类

hutool包,真是好用,方便,强烈推荐…

https://hutool.cn/docs/index.html#/

然后使用excel的工具类,还需要引入poi-ooxml包版本的话,报错里面有

自己确认一下就好了

 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupid>org.apache.poi</groupid>
            <artifactid>poi-ooxml</artifactid>
            <version>3.17</version>
        </dependency>

一行代码搞定:常用的就是read方法了.

excelreader reader = excelutil.getreader(fileutil.file("c:\\users\\stack\\desktop\\hanke\\aaa.xlsx"));

Hutool Java工具类库_ExcelUtil的使用

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。