jxls读取excel中所有的数据
代码很简单,但是io的异常问题比较多,方便阅读就直接抛出异常
下面是maven依赖(也就是jxls读取的依赖,多加了个lombok)
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-reader</artifactId>
<version>1.0.6</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.4.1</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>1.0.13</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-jexcel</artifactId>
<version>1.0.6</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
测试的实体类Student(忽略了getter setter 构造等 推荐使用lombok)
public class Student {
private int id;
private String name;
private int age;
private int math;
private int chinese;
}
这个是我测试的表
最为关键的xml配置文件
<?xml version="1.0" encoding="UTF-8"?>
<workbook>
<worksheet name="Sheet1">
<!-- 没有loop包着的section是直接读取准确的行,这里为了不读取上面的标题,所以没有用mapping赋值参数 -->
<section startRow="0" endRow="0"/>
<loop items="studentList" var="student" varType="com.example.demo.gong.jxls.Student">
<!-- 有loop包着的是循环获取,从当前行开始 -->
<section startRow="1" endRow="1">
<mapping row="1" col="0">student.id</mapping>
<mapping row="1" col="1">student.name</mapping>
<mapping row="1" col="2">student.age</mapping>
<mapping row="1" col="3">student.math</mapping>
<mapping row="1" col="4">student.chinese</mapping>
</section>
<loopbreakcondition>
<rowcheck offset="0">
<cellcheck offset="0"></cellcheck>
</rowcheck>
</loopbreakcondition>
</loop>
</worksheet>
</workbook>
xml文件是最关键的,可以自定义读取准确的单元数据,也可以循环读取,可以多尝试
代码
public class JXLSGetXls {
public static void main(String[] args) throws IOException, SAXException, InvalidFormatException {
//需要导出的参数的xls文件路径
String path = "D:\\mission\\src\\main\\java\\com\\example\\demo\\gong\\jxls\\student.xls";
//配置文件路径
String xmlPath = "D:\\mission\\src\\main\\resources\\student.xml";
InputStream inputXML = new BufferedInputStream(new FileInputStream(xmlPath));
InputStream inputXLS = new BufferedInputStream(new FileInputStream(new File(path)));
XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);
//这个参数对应xml配置文件的参数
List<Student> studentList = new ArrayList<>();
Map<String, Object> beans = new HashMap<>();
beans.put("studentList", studentList);
//read是读取数据
XLSReadStatus readStatus = mainReader.read(inputXLS, beans);
//是否读取成功
boolean statusOK = readStatus.isStatusOK();
System.out.println("读取结果:" + statusOK);
//测试结果
for (Student stu : studentList) {
System.out.println(stu);
}
}
}
简单说一些小坑
配置文件里面有两种读取,一个被loop包着还有一个是没有被loop包着的,像我这个表格第一行是属性名称,不是具体的参数,就可以用不被loop包着的section,然后starerow跟endrow都是0,相当于读取掉了这行数据,然后再开始就是读下面的
如果直接是全部数据的话,就不需要这样了
小白记录一下,不对之处欢迎留言