jxls-Reader技术解析EXCEL-简单案例

 jxls-Reader技术解析EXCEL

jxls-Reader 技术,用于读取excel中的数据,把数据映射到JavaBean,xml配置文件用于解析excel文件,解决如何填充的问题:

提示: 目标要构建 xlsReader对象,构建xlsReader对象的最简单的方式,是使用特定的xml配置文件

步骤:

① 创建JavaBean

package com.wuhao.entity;

import java.util.Date;

/**
 * @author: wuhao
 * @since: 2021/5/20 15:34
 */
public class Student {

    private String name;
    private String password;
    private String sex;
    private int age;
    private Date birthday;

    public Student() {

    }
    public Student(String name, String password, String sex, int age, Date birthday) {
        this.name = name;
        this.password = password;
        this.sex = sex;
        this.age = age;
        this.birthday = birthday;
    }

    public String getName() {
        return name;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public int getAge() {
        return age;
    }

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

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "Student{" +
                "name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", sex='" + sex + '\'' +
                ", age=" + age +
                ", birthday=" + birthday +
                '}';
    }
}

② 创建xml配置文件

<?xml version="1.0" encoding="utf-8"?>
<workbook>
    <worksheet name="Sheet1">
        <!--loop（循环）元素定义Excel循环块,该元素包括
        startRow和endRow指定循环开始行,循环结束行
        itmes 映射集合
        var 映射的bean
        varType 类的路径
        -->
        <section startRow="0" endRow="0"></section>
        <loop startRow = "1" endRow = "2" items = "studentList" var = "student" varType = "com.wuhao.entity.Student">
            <!--loop元素可以包含任意数量的内部section和loop元素
            并且必须包含loopbreakcondition元素的定义,该元素里标示中断循环的条件;
            -->
            <!--循环开始行-->
            <section startRow="1" endRow="1">
                <!--循环中每一次节点属性配置-->
                <mapping row="1" col="0">student.name</mapping>
                <mapping row="1" col="1">student.password</mapping>
                <mapping row="1" col="2">student.sex</mapping>
                <mapping row="1" col="3">student.age</mapping>
                <mapping row="1" col="4">student.birthday</mapping>
            </section>
            <!--循环结束配置-->
            <loopbreakcondition>
                <rowcheck offset = "0">
                    <!--cellcheck中不写内容,则默认空白时停止循环-->
                    <cellcheck offset = "0"></cellcheck>
                </rowcheck>
            </loopbreakcondition>
        </loop>
    </worksheet>
</workbook>

③ 解析Excel代码

package com.wuhao;

import com.wuhao.entity.Student;
import org.junit.Test;
import org.jxls.reader.ReaderBuilder;
import org.jxls.reader.XLSReadStatus;
import org.jxls.reader.XLSReader;

import java.io.BufferedInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author: wuhao
 * @since: 2021/5/20 11:09
 * 通过readBuilder类使用XML映射文件和departmentdata.xls构建xlsReader对象
 * xlsReader对象读取xls中的数据,填充响应Java Bean代码
 */
public class JxlsReaderTest {

    @Test
    public  void test() {
        try {
            try (InputStream xmlInputStream = new BufferedInputStream(getClass().getResourceAsStream("/demo/xmlConfig.xml"))){
                final XLSReader reader = ReaderBuilder.buildFromXML(xmlInputStream);
                try (InputStream xlsInputStream = new BufferedInputStream(getClass().getResourceAsStream("/demo/demo.xls"))) {
                    List<Student> studentList = new ArrayList<>();
                    Map<String, Object> beans = new HashMap<>();
                    beans.put("studentList", studentList);
                    XLSReadStatus read = reader.read(xlsInputStream, beans);
                    System.out.println(studentList);
                }
            }
        }catch (Exception e){
          e.printStackTrace();
        }
    }
}