jxls2.4.7配置(intelliJ IDEA)
1.需要准备的开源库:
poi-4.0.1,
commons-collections-3.2.1,
commons-jexl-2.1.1,
commons-beanutils-1.9.3,
commons-logging-1.2,
commons-digester3-3.2,
jxls2.4.7
如下:
由于logging的缘由,得添加slf4j-api,slf4j-log4j12,与log4j这3个包。
2.添加jar包到idea
File->Project Structure->Libraries->左侧+,选择项目目录,右侧+,添加项目lib目录下的jar包,如图:
Apply,点击Modules->Dependencies->+ ->libraries->jxlsLearn(刚刚添加的libraries),即可。
jxls使用
这里使用jxls Reader 读取exl里的数据。
1.编写xmlConfig.xml文件,以来映射Entity-exl数据的关系
<?xml version="1.0" encoding="ISO-8859-1"?>
<workbook>
<!--exl的sheet名称-->
<worksheet name="Sheet1">
<!--根据具体的格子选择数据,映射到实体的成员-->
<section startRow="0" endRow="6">
<mapping cell="B1">department.name</mapping>
<mapping cell="A4">department.chief.name</mapping>
<mapping cell="B4">department.chief.age</mapping>
...
</section>
<!--循环根据格子坐标选择数据-->
<loop startRow="7" endRow="7" items="department.staff" var="employee" varType="org.jxls.reader.sample.Employee">
<section startRow="7" endRow="7">
<mapping row="7" col="0">employee.name</mapping>
<mapping row="7" col="1">employee.age</mapping>
<mapping row="7" col="3">employee.payment</mapping>
<mapping row="7" col="4">employee.bonus</mapping>
</section>
<!--结束标记-->
<loopbreakcondition>
<rowcheck offset="0">
<cellcheck offset="0">Employee Payment Totals:</cellcheck>
</rowcheck>
</loopbreakcondition>
</loop>
</worksheet>
</workbook>
2.代码:
xmlConfig="xmlconfig.xml";
dataFile="file.xls";
try(InputStream xmlInputStream = getClass().getResourceAsStream(xmlConfig)){
XLSReader reader = ReaderBuilder.buildFromXML(xmlInputStream);
try(InputStream xlsInputStream = getClass().getResourceAsStream(dataFile)){
List<employee> list=new ArrayList<>();
Map<String,Object> beans=new HashMap<>();
beans.put("list",list);
XLSReadStatus readStatus=reader.read(xlsInputStream,beans);
//数据已写入list中了
for (Jrsc jrsc : list) {
System.out.println(employee.getName());
}
} catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
} catch (SAXException e) {
e.printStackTrace();
}
3.添加log4j.properties文件
log4j.rootLogger=WARN, Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=(%r ms) [%t] %-5p: %c#%M %x: %m%n
log4j.logger.com.genuitec.eclipse.sqlexplorer=DEBUG
log4j.logger.org.apache=WARN
log4j.logger.org.hibernate=WARN
4.别忘了实体类得编写