jxls导入excel

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);
        }

    }
}