当前位置:龙哥网 > 网络技术 > Hutool Java工具类库_ExcelUtil的使用_java

Hutool Java工具类库_ExcelUtil的使用_java

longge3个月前 (03-01)网络技术82
目录
  • Hutool java工具类库_excelUtil
    • 依赖
    • ExcelUtil
    • ExcelReader
    • ExcelWriter
  • java解析Excel使用hutool工具类

    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的使用_java

    免责声明
    本站部分资源来源于互联网 如有侵权 请联系站长删除
    龙哥网是优质的互联网科技创业资源_行业项目分享_网络知识引流变现方法的平台为广大网友提供学习互联网相关知识_内容变现的方法。#转载请注明出处!