springboot+mybatis+mysql将excel表内容导入到数据库

废话不多说，直接上代码（先做第三步，添加依赖包）：
也可以参考这个链接:链接
1、myMapper.java

package com.gcb.Students;

import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

/**
 * 继承自己的MyMapper
 */
public interface MyMapper<T> extends Mapper<T>, MySqlMapper<T> {
    //TODO
    //FIXME 特别注意，该接口不能被扫描到，否则会出错
}

2、StudentsApplication.java

package com.gcb.Students;


import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

import tk.mybatis.spring.annotation.MapperScan;


@SpringBootApplication
@MapperScan(basePackages = "com.gcb.Students.Dao")
@ComponentScan(basePackages= {"com.gcb.Students"})
public class StudentsApplication {

	public static void main(String[] args) {
		SpringApplication.run(StudentsApplication.class, args);
	}

}

3、pom.xml依赖

<!--新项目能正常启动的基础  -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		
		<!--mybatis -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.1</version>
		</dependency>
		<!--mapper -->
		
		<dependency>
			<groupId>tk.mybatis</groupId>
			<artifactId>mapper-spring-boot-starter</artifactId>
			<version>1.2.4</version>
		</dependency>
		
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.40</version>
		</dependency>
		
        <!--xls(03)-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        
        <!--xlsx(07)-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        

4、controller

package com.gcb.Students.Controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.gcb.Students.Po.AddStudentsInfoReqVo;
import com.gcb.Students.Po.AutoInsertInfoReqVo;
import com.gcb.Students.Po.AvailableResult;
import com.gcb.Students.Po.excelDataToMYSQLReqVo;
import com.gcb.Students.Service.StudentsService;

@RestController
@RequestMapping("/Students/")
public class StudentsController {
	
	@Autowired
	private StudentsService studentsservice;
	
	//插入excel表的内容
	@RequestMapping("excelDataToMYSQL")
	public AvailableResult excelDataToMYSQL(@RequestBody excelDataToMYSQLReqVo reqVo) {
		return studentsservice.excelDataToMYSQL(reqVo);
	}
	
}

5、dao

package com.gcb.Students.Dao;

import com.gcb.Students.MyMapper;
import com.gcb.Students.Po.Studentsinfo;

public interface StudentsinfoMapper extends MyMapper<Studentsinfo> {
}

4、po

package com.gcb.Students.Po;

import java.io.Serializable;

public class excelDataToMYSQLReqVo implements Serializable{
	//文件的绝对路径
	private String filepath;

	public String getFilepath() {
		return filepath;
	}

	public void setFilepath(String filepath) {
		this.filepath = filepath;
	}

	@Override
	public String toString() {
		return "excelDataToMYSQLReqVo [filepath=" + filepath + "]";
	}
	
}

这个也是放在po下

package com.gcb.Students.Po;

import java.util.List;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;

/**
 * @Description: 自定义响应数据结构 
 */
public class AvailableResult {

	// 定义jackson对象
	private static final ObjectMapper MAPPER = new ObjectMapper();

	// 响应业务状态
	private Integer status;

	// 响应消息
	private String msg;

	// 响应中的数据
	private Object data;

	private String ok; // 不使用

	public static AvailableResult build(Integer status, String msg, Object data) {
		return new AvailableResult(status, msg, data);
	}

	public static AvailableResult ok(Object data) {
		return new AvailableResult(data);
	}

	public static AvailableResult ok() {
		return new AvailableResult(null);
	}

	public static AvailableResult errorMsg(String msg) {
		return new AvailableResult(500, msg, null);
	}

	public static AvailableResult errorMap(Object data) {
		return new AvailableResult(501, "error", data);
	}

	public static AvailableResult errorTokenMsg(String msg) {
		return new AvailableResult(502, msg, null);
	}

	public static AvailableResult errorException(String msg) {
		return new AvailableResult(555, msg, null);
	}

	public AvailableResult() {

	}

	// public static LeeJSONResult build(Integer status, String msg) {
	// return new LeeJSONResult(status, msg, null);
	// }

	public AvailableResult(Integer status, String msg, Object data) {
		this.status = status;
		this.msg = msg;
		this.data = data;
	}

	public AvailableResult(Object data) {
		this.status = 200;
		this.msg = "OK";
		this.data = data;
	}

	public Boolean isOK() {
		return this.status == 200;
	}

	public Integer getStatus() {
		return status;
	}

	public void setStatus(Integer status) {
		this.status = status;
	}

	public String getMsg() {
		return msg;
	}

	public void setMsg(String msg) {
		this.msg = msg;
	}

	public Object getData() {
		return data;
	}

	public void setData(Object data) {
		this.data = data;
	}

	/**
	 * 
	 * @Description: 将json结果集转化为LeeJSONResult对象 需要转换的对象是一个类
	 * @param jsonData
	 * @param clazz
	 * @return
	 * 
	 * 
	 * @date 2016年4月22日 下午8:34:58
	 */
	public static AvailableResult formatToPojo(String jsonData, Class<?> clazz) {
		try {
			if (clazz == null) {
				return MAPPER.readValue(jsonData, AvailableResult.class);
			}
			JsonNode jsonNode = MAPPER.readTree(jsonData);
			JsonNode data = jsonNode.get("data");
			Object obj = null;
			if (clazz != null) {
				if (data.isObject()) {
					obj = MAPPER.readValue(data.traverse(), clazz);
				} else if (data.isTextual()) {
					obj = MAPPER.readValue(data.asText(), clazz);
				}
			}
			return build(jsonNode.get("status").intValue(), jsonNode.get("msg").asText(), obj);
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 
	 * @Description: 没有object对象的转化
	 * @param json
	 * @return
	 * 
	 * 
	 * @date 2016年4月22日 下午8:35:21
	 */
	public static AvailableResult format(String json) {
		try {
			return MAPPER.readValue(json, AvailableResult.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 
	 * @Description: Object是集合转化 需要转换的对象是一个list
	 * @param jsonData
	 * @param clazz
	 * @return
	 * 
	 * 
	 * @date 2016年4月22日 下午8:35:31
	 */
	public static AvailableResult formatToList(String jsonData, Class<?> clazz) {
		try {
			JsonNode jsonNode = MAPPER.readTree(jsonData);
			JsonNode data = jsonNode.get("data");
			Object obj = null;
			if (data.isArray() && data.size() > 0) {
				obj = MAPPER.readValue(data.traverse(),
						MAPPER.getTypeFactory().constructCollectionType(List.class, clazz));
			}
			return build(jsonNode.get("status").intValue(), jsonNode.get("msg").asText(), obj);
		} catch (Exception e) {
			return null;
		}
	}

	public String getOk() {
		return ok;
	}

	public void setOk(String ok) {
		this.ok = ok;
	}

}

6、service

package com.gcb.Students.Service;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.gcb.Students.Dao.StudentsinfoMapper;
import com.gcb.Students.Po.AvailableResult;
import com.gcb.Students.Po.Studentsinfo;
import com.gcb.Students.Po.excelDataToMYSQLReqVo;

@Service
public class StudentsService {

	//添加提示变量
	Logger logger = LoggerFactory.getLogger(StudentsService.class);
	
	//学生信息
	@Autowired
	private StudentsinfoMapper studentsinfoMapper;

	//插入excel数据
	public AvailableResult excelDataToMYSQL(excelDataToMYSQLReqVo reqVo) {
		try {
			logger.info(">>>>>>>>>>开始添加excel表内容");
			
			//判断参数是否为空
			if(reqVo == null) {
				logger.info(">>>>>>>>>>请求参数为空");
				AvailableResult.errorException("请求参数为空");
			}
			
			if(StringUtil.nullAndEmpty(reqVo.getFilepath())) {
				logger.info(">>>>>>>>>>文件绝对路径为空");
				AvailableResult.errorException("文件绝对路径为空");
			}
			
			logger.info(">>>>>>>>>>请求参数为："+reqVo.toString());
			
			//将路径进行转义
			String filepath = reqVo.getFilepath().replace("##", "\\");
			
			logger.info(">>>>>>>>>>新请求参数为："+reqVo.toString());
			
			//定义流对象
			InputStream is = new FileInputStream(filepath);
			
			//创建excel表对象
			Workbook wb = new XSSFWorkbook(is);
			
			//获取第一个表格
			Sheet sheet = wb.getSheetAt(0);
			
			if(sheet!=null) {
				
				//创建一个新的List对象
				List<Studentsinfo> studentsList = new ArrayList<Studentsinfo>();
				
				for(int rowNum = 1;rowNum<=sheet.getLastRowNum();rowNum++) {
					//创建一个新的学生对象
					Studentsinfo studentsinfo = new Studentsinfo();
					
					//获取每一行
					Row row = sheet.getRow(rowNum);
					
					//获取单元格的值
					studentsinfo.setName(row.getCell(0).getStringCellValue());
					studentsinfo.setPassword(row.getCell(1).getStringCellValue());
					
					//将每一个学生信息加入到List里
					studentsList.add(studentsinfo);
				}
				
				//将List插入到数据库
				studentsinfoMapper.insertList(studentsList);
				
			}
			return AvailableResult.ok();
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(">>>>>>>>>>添加学生信息异常，原因为：" + e.getMessage());
			return AvailableResult.errorException(e.getMessage());
		}
	}
}

7、mapper

<?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="com.gcb.Students.Dao.StudentsinfoMapper" >
  <resultMap id="BaseResultMap" type="com.gcb.Students.Po.Studentsinfo" >
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="password" property="password" jdbcType="VARCHAR" />
  </resultMap>
</mapper>

8、application.properties

server.port=8768
mybatis.mapper-locations=classpath:mapper/*.xml

mapper.mappers=com.gcb.Students.MyMapper
mapper.not-empty=false
mapper.identity=MYSQL

spring.datasource.driverClassName=com.mysql.jdbc.Driver
#\u5e7f\u5dde\u94f6\u884c\u6d4b\u8bd5\u73af\u5883
spring.datasource.url=jdbc:mysql://localhost:3306/students?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
spring.datasource.username=root
spring.datasource.password=rootroot

9、组织图：

10、postman请求参数：

{"filepath":"f:\\test.xlsx"}

11、test.xlsx内容