废话不多说，直接上代码（先做第三步，添加依赖包）：
也可以参考这个链接:链接
1、myMapper.java
package com.gcb.Students; import tk.mybatis.mapper.common.Mapper; import tk.mybatis.mapper.common.MySqlMapper; public interface MyMapperextends Mapper , MySqlMapper { //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依赖
org.springframework.boot spring-boot-starter-web org.mybatis.spring.boot mybatis-spring-boot-starter 1.3.1 tk.mybatis mapper-spring-boot-starter 1.2.4 mysql mysql-connector-java 5.1.40 org.apache.poi poi 3.9 org.apache.poi poi-ooxml 3.9 junit junit 4.12
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{ }
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;
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;
}
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;
}
}
public static AvailableResult format(String json) {
try {
return MAPPER.readValue(json, AvailableResult.class);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
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 studentsList = new ArrayList();
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
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 #u5e7fu5ddeu94f6u884cu6d4bu8bd5u73afu5883 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内容
