jxls导入excel

一、 对比excel文件和Mysql数据库中的数据类型区别

首先看一下我们要导入的EXCEL表格中的数据。

再看一下MySQL数据库中的表属性，

通过对比两个的数据类型
问题来了 ：
EXCEL表格中的“使用公司”和“所属仓库”的数据类型是String类型，而数据库中是“公司ID”和“仓库ID”是Integer类型。怎么把他们换回来呢，这里我们就用到，VO类的封装了。

一般情况下，我们pojo类的属性名都是和数据库的一一对应的，为了避免用户直接看到数据库的数据类型，导致一些危险的后果。所以都会写一个VO类对pojo类不想让人看到的属性进行再次封装。

这个例子先写一个pojo类。
LocationDetails.Java

package ndm.miniwms.pojo;import java.util.Date;import java.util.List;import VO.LocationVO;public class LocationDetails {    private Integer id;     private Date created;     private Date modified;     private String name;    private Integer usage;    private String XCoord;    private String YCoord;    private String ZCoord;    private Integer warehouseId;     private Integer companyId;    public LocationDetails(){    }    //这里是vo类和pojo类交互的构造函数    public LocationDetails(LocationVO vo){        this.name=vo.getName();        this.XCoord=vo.getXCoord();        this.YCoord=vo.getYCoord();        this.ZCoord=vo.getZCoord();    }    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 Integer getUsage() {        return usage;    }    public void setUsage(Integer usage) {        this.usage = usage;    }    public String getXCoord() {        return XCoord;    }    public void setXCoord(String xCoord) {        XCoord = xCoord;    }    public String getYCoord() {        return YCoord;    }    public void setYCoord(String yCoord) {        YCoord = yCoord;    }    public String getZCoord() {        return ZCoord;    }    public void setZCoord(String zCoord) {        ZCoord = zCoord;    }    public Integer getWarehouseId() {        return warehouseId;    }    public void setWarehouseId(Integer warehouseId) {        this.warehouseId = warehouseId;    }    public Integer getCompanyId() {        return companyId;    }    public void setCompanyId(Integer companyId) {        this.companyId = companyId;    }}

它对应的VO类、
LocationVO.java

package VO;public class LocationVO {    private String name;     private String XCoord;     private String YCoord;     private String ZCoord;    private String warehouseId;  //这里是String类型的    private String companyId;   //这里是String类型的    public String getWarehouseId() {        return warehouseId;    }    public void setWarehouseId(String warehouseId) {        this.warehouseId = warehouseId;    }    public String getCompanyId() {        return companyId;    }    public void setCompanyId(String companyId) {        this.companyId = companyId;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public String getXCoord() {        return XCoord;    }    public void setXCoord(String xCoord) {        XCoord = xCoord;    }    public String getYCoord() {        return YCoord;    }    public void setYCoord(String yCoord) {        YCoord = yCoord;    }    public String getZCoord() {        return ZCoord;    }    public void setZCoord(String zCoord) {        ZCoord = zCoord;    }}

库位的DAO接口和mapper.xml文件有连接数据库进行增加操作的方法，代码如下：
LocationDetailsMapper.java

package ndm.miniwms.dao;import java.util.List;import ndm.miniwms.pojo.CompanyDetails;import ndm.miniwms.pojo.LocationDetails;public interface LocationDetailsMapper {    int add(LocationDetails locationDetails);}

下面是添加到数据库的xml代码

LocationDetailsMapper.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" ><mapper namespace="ndm.miniwms.dao.LocationDetailsMapper">    <resultMap id="BaseResultMap" type="ndm.miniwms.pojo.LocationDetails">        <id column="id" property="id" jdbcType="INTEGER" />        <result column="warehouse_id" property="warehouseId" jdbcType="INTEGER" />        <result column="company_id" property="companyId" jdbcType="INTEGER" />    </resultMap>    <insert id="add" parameterType="ndm.miniwms.pojo.LocationDetails">        insert into location_details        (created, modified, name, `usage`, XCoord,        YCoord,ZCoord, warehouse_id,company_id)        values (#{created}, #{modified}, #{name},        #{usage},#{XCoord}, #{YCoord}, #{ZCoord},        #{warehouseId},#{companyId})    </insert></mapper>

下面是具体对EXCEL文件操作的方法

location.xml

<?xml version="1.0" encoding="utf-8" ?><workbook>    <worksheet name="工作表1">    <!-- 开始行数，和结束行数。把EXCEL表中的前两排直接干掉-->    <section startRow="0" endRow="1"></section>    <!-- 下面是一行的模版，获取一行，之后的行数就按这个模版来获取-->    <!-- items是集合的名称。-->        <loop startRow="2" endRow="2" items="locations" var="locationVO"         varType="VO.LocationVO">            <section startRow="2" endRow="2">                <mapping row="2" col="0">locationVO.name</mapping>                <mapping row="2" col="1">locationVO.XCoord</mapping>                <mapping row="2" col="2">locationVO.YCoord</mapping>                <mapping row="2" col="3">locationVO.ZCoord</mapping>                <mapping row="2" col="4">locationVO.companyId</mapping>                <mapping row="2" col="5">locationVO.warehouseId</mapping>            </section>            <loopbreakcondition>                <rowcheck offset="0">                <cellcheck offset="0"></cellcheck>                </rowcheck>                    <!--循环到值为空的时候结束 -->            </loopbreakcondition>        </loop>    </worksheet></workbook>

ReadLocation.java

package zhao;import java.io.BufferedInputStream;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.annotation.Resource;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 VO.LocationVO;import ndm.miniwms.dao.CompanyDetailsMapper;import ndm.miniwms.dao.LocationDetailsMapper;import ndm.miniwms.dao.WarehouseMapper;import ndm.miniwms.pojo.CompanyDetails;import ndm.miniwms.pojo.LocationDetails;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 ReadLocation {    @Resource    CompanyDetailsMapper companydetailsmapper;    @Resource    WarehouseMapper warehousemapper;    @Resource    LocationDetailsMapper locationdetailsmapper;    @Test    public void test() throws InvalidFormatException, IOException, SAXException {        String xmlConfig = "/doc/location.xml";        InputStream inputXML = new BufferedInputStream(getClass().getResourceAsStream(xmlConfig));        XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);        InputStream inputXLS = new BufferedInputStream(                getClass().getResourceAsStream("/doc/dataSourceTemplate/库位.xls"));        BufferedInputStream bis = new BufferedInputStream(inputXLS);        List<LocationVO> locationVO = new ArrayList<>();        Map<String, Object> beans = new HashMap<String, Object>();        // 读出来，把xml中获取的locations，取出来赋值给这个集合。        beans.put("locations", locationVO);        XLSReadStatus readStatus = mainReader.read(inputXLS, beans);        List<CompanyDetails> company = companydetailsmapper.all();        List<Warehouse> warehouse = warehousemapper.all();        for (LocationVO locationvo : locationVO) {            LocationDetails loca = new LocationDetails(locationvo);            for (CompanyDetails com : company) {                // System.out.println("公司的名字：：："+com.getName());                // System.out.println("vo的名字：：："+locationvo.getCompanyId());                if (com.getName().equals(locationvo.getCompanyId())) {                    loca.setCompanyId(com.getId());                    break;                }            }            for (Warehouse wa : warehouse) {                if (wa.getName().equals(locationvo.getWarehouseId())) {                    loca.setWarehouseId(wa.getId());                    break;                }            }            locationdetailsmapper.add(loca);        }    }}