java中jxls实现excel导入到mysql数据库

    现在网上用的比较多的有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的方法就是一个模板,循环会按照这个模板来执行循环.