现在网上用的比较多的有jxl,poi的方法,但是对与java,springmvc来说,jxls的方法更好,jxl的方法有点过时,不能导入所有的格式,2017的excel就读取不了,查看了一些文档,总结下jxls的方法,来实现导入,直接代码。
需要在pom.xml中加入一个包:
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-reader</artifactId>
<version>1.0.6</version>
</dependency>
首先要写一个xml;
TestWarehouseExcel.xml:
<?xml version="1.0" encoding="UTF-8"?>
<workbook>
<worksheet name="工作表1">
<section startRow="0" endRow="1"></section>
<loop startRow="2" endRow="2" items="warehouses" var="warehouse" varType="ndm.miniwms.pojo.Warehouse">
<section startRow="2" endRow="2">
<mapping row="2" col="0">warehouse.name</mapping>
<mapping row="2" col="1">warehouse.address</mapping>
<mapping row="2" col="2">warehouse.description</mapping>
<mapping row="2" col="3">warehouse.contactName</mapping>
<mapping row="2" col="4">warehouse.contactTel</mapping>
<mapping row="2" col="5">warehouse.contactFax</mapping>
<mapping row="2" col="6">warehouse.contactEmail</mapping>
<mapping row="2" col="7">warehouse.contactQq</mapping>
<mapping row="2" col="8">warehouse.contactMsn</mapping>
<mapping row="2" col="9">warehouse.contactDesc</mapping>
</section>
<loopbreakcondition>
<rowcheck offset="0">
<cellcheck offset="0"></cellcheck>
</rowcheck>
</loopbreakcondition>
</loop>
</worksheet>
</workbook>
写个测试的方法:
ReaderWarehouseExcel.java:
package cui;
import java.beans.Beans;
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.enterprise.inject.New;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.xml.sax.SAXException;
import ndm.miniwms.pojo.Warehouse;
import net.sf.jxls.reader.ReaderBuilder;
import net.sf.jxls.reader.XLSReadStatus;
import net.sf.jxls.reader.XLSReader;
@RunWith(SpringJUnit4ClassRunner.class) //表示继承了SpringJUnit4ClassRunner类
@ContextConfiguration(locations = {"classpath:spring-mybatis.xml"})
public class ReaderWarehouseExcel {
@Test
public void test() throws IOException, SAXException, InvalidFormatException {
String xmlConfig="/doc/TestWarehouseExcel.xml";
InputStream inputXML = new BufferedInputStream(getClass().getResourceAsStream(xmlConfig));
XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);
InputStream inputXLS = getClass().getResourceAsStream("/doc/dataSourceTemplate/仓库.xls");
BufferedInputStream bis=new BufferedInputStream(inputXLS);
List<Warehouse> warehouses = new ArrayList<>();
Map<String, Object> beans = new HashMap<String, Object>();
beans.put("warehouses", warehouses);
XLSReadStatus readStatus = mainReader.read( inputXLS, beans);
for(Warehouse warehouse : warehouses) {
System.out.println(warehouse.getDescription());
}//下面就i是用mybatis来执行增加的操作,代码就不写了
}
}
其中用到了Warehouse的类:
package ndm.miniwms.pojo;
import java.util.Date;
import java.util.List;
public class Warehouse {
private Integer id; // 浠撳簱id
private Date created; // 鍒涘缓鏃堕棿
private Date modified; // 淇敼鏃堕棿
private String name; // 鍚嶇О
private String address; // 鍦板潃
private String description; // 鎻忚堪
private String contactName; // 鑱旂郴浜哄悕绉�
private String contactTel; // 鑱旂郴浜哄彿鐮�
private String contactFax; // 鑱旂郴浜轰紶鐪�
private String contactEmail; // 鑱旂郴浜洪偖绠�
private String contactQq; // 鑱旂郴浜篞Q
private String contactMsn; // 鑱旂郴浜篠kype
private String contactDesc; // 鑱旂郴浜烘弿杩�
private CompanyDetails companyDetails;//鍏徃
private List<LocationDetails> locationDetailsList; //鍒涘簱搴撲綅
public CompanyDetails getCompanyDetails() {
return companyDetails;
}
public void setCompanyDetails(CompanyDetails companyDetails) {
this.companyDetails = companyDetails;
}
public List<LocationDetails> getLocationDetailsList() {
return locationDetailsList;
}
public void setLocationDetailsList(List<LocationDetails> locationDetailsList) {
this.locationDetailsList = locationDetailsList;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
public Date getModified() {
return modified;
}
public void setModified(Date modified) {
this.modified = modified;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public String getContactName() {
return contactName;
}
public void setContactName(String contactName) {
this.contactName = contactName;
}
public String getContactTel() {
return contactTel;
}
public void setContactTel(String contactTel) {
this.contactTel = contactTel;
}
public String getContactFax() {
return contactFax;
}
public void setContactFax(String contactFax) {
this.contactFax = contactFax;
}
public String getContactEmail() {
return contactEmail;
}
public void setContactEmail(String contactEmail) {
this.contactEmail = contactEmail;
}
public String getContactQq() {
return contactQq;
}
public void setContactQq(String contactQq) {
this.contactQq = contactQq;
}
public String getContactMsn() {
return contactMsn;
}
public void setContactMsn(String contactMsn) {
this.contactMsn = contactMsn;
}
public String getContactDesc() {
return contactDesc;
}
public void setContactDesc(String contactDesc) {
this.contactDesc = contactDesc;
}
}
然后就是一个excel的表.
运行test就能将这几个数据加进数据库,上面的loop是循环,section的方法就是一个模板,循环会按照这个模板来执行循环.