jxls导入excel
一、 对比excel文件和Mysql数据库中的数据类型区别首先看一下我们要导入的EXCEL表格中的数据。
通过对比两个的数据类型
问题来了 :
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);
}
}
}