首先看一下我们要导入的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); } } }