spring-boot-excelimport将excel数据导入到数据库

首先要准备个模板，也就是导入数据库的excel:

我在的公司过滤掉了图片，上面的模板用文字描述一下：

第一排：姓名性别年龄

第二排是要导入到数据库的数据：张无忌男 25

下面几排都是数据就不写了

然后创建一个maven项目：spring-boot-import

pom.xml文件：

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.xy</groupId>
<artifactId>spring-boot-import</artifactId>
<version>0.0.1-SNAPSHOT</version>

<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.4.2.RELEASE</version>
<relativePath/> 
</parent>

<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>

<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
<exclusions>
<exclusion>
<artifactId>commons-codec</artifactId>
<groupId>commons-codec</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.40</version>
</dependency>

<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>

再将配置文件配置好application.properties:

server.port=8088
spring.datasource.url=jdbc:mysql://192.168.26.3:3306/test?useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

src/main/resources下创建templates文件夹，这是用来存放模板的

模板名叫excelimport.html:

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org"
xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity4">
<head>
<meta charset="UTF-8"/>
<title ></title>

</head>
<body>
<form action="#" th:action="@{/excel/upload}" enctype="multipart/form-data" method="post">
<input type="file" name="file"/>
<br/>
<button type="submit">开始导入</button>
</form>

</body>
</html>

出于个人习惯，我先写的是启动类：

com.xy包下建立启动类：

ExcelImportApp:

package com.xy;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.xy.mapper")
public class ExcelImportApp {

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

}

实体类：

com.xy.entity下建两个实体类

第一个：ExcelImport.java

package com.xy.entity;
/**
* 需要导入的数据的实体类
* @author xy
*
*/
public class ExcelImport {

private String uname;

private String usex;

private String uage;

public String getUname() {
return uname;
}

public void setUname(String uname) {
this.uname = uname;
}

public String getUsex() {
return usex;
}

public void setUsex(String usex) {
this.usex = usex;
}

public String getUage() {
return uage;
}

public void setUage(String uage) {
this.uage = uage;
}

}

第二个实体类ReadExcel.java

package com.xy.entity;
/**
* excel需要的相关参数
* @author xy
*
*/
public class ReadExcel {
//总行数
private int totalRows = 0;

//总条数
private int totalCells = 0;

//错误信息的收集类
private String errorMsg;

public ReadExcel() {
super();
// TODO Auto-generated constructor stub
}

public int getTotalRows() {
return totalRows;
}

public void setTotalRows(int totalRows) {
this.totalRows = totalRows;
}

public int getTotalCells() {
return totalCells;
}

public void setTotalCells(int totalCells) {
this.totalCells = totalCells;
}

public String getErrorMsg() {
return errorMsg;
}

public void setErrorMsg(String errorMsg) {
this.errorMsg = errorMsg;
}

}

实体类后建立mapper接口

包：com.xy.mapper

ExcelImplMapper.java

package com.xy.mapper;

import org.apache.ibatis.annotations.Mapper;

import com.xy.entity.ExcelImport;
@Mapper
public interface ExcelImplMapper {
/**
* 添加
* @param excel
*/
public void save(ExcelImport excel);
}

同样的在这个包下建立ExcelImplMapper.xml,注意我没在配置文件里面配，不写在这的话找不到xml文件的

ExcelImplMapper.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="com.xy.mapper.ExcelImplMapper">
<resultMap id="excelMap" type="com.xy.entity.ExcelImport" >
<result column="uname" property="uname" jdbcType="VARCHAR" />
<result column="usex" property="usex" jdbcType="VARCHAR" />
<result column="uage" property="uage" jdbcType="VARCHAR" />
</resultMap>

<insert id="save" parameterType="com.xy.entity.ExcelImport" >
insert into users
(uname, usex,uage)
values
(#{uname,jdbcType=VARCHAR},
#{usex,jdbcType=VARCHAR},
#{uage,jdbcType=VARCHAR})
</insert>
</mapper>

com.xy.utils

ExcelUtil.java解析全在这个类里面

package com.xy.utils;

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

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.springframework.web.multipart.MultipartFile;

import com.xy.entity.ExcelImport;
import com.xy.entity.ReadExcel;

/**
* 文件导入数据库的工具类
* @author xy
*
*/
public class ExcelUtil {

/**
* 先创建一个实体类
*/
ReadExcel readExcel = new ReadExcel();
/**
* 读取Excel文件，获取信息集合
* @param mFile
* @return
*/
public List<ExcelImport> getExcelInfo(MultipartFile mFile){
String fileName = mFile.getOriginalFilename();//获取文件名

List<ExcelImport> ilist = null;
try {
if(!validateExcel(fileName)){//验证文件名是否合格
//不合格的话直接return
return null;
}
boolean isExcel2003 = true;//根据文件名判断是2003版本的还是2007版本的
if(isExcel2007(fileName)){
isExcel2003 = false;
}
ilist= createExcel(mFile.getInputStream(), isExcel2003);

} catch (Exception e) {
e.printStackTrace();
}
return ilist;
}
/**
* 判断是不是2003版本的excel
* @param filePath
* @return
*/
public static boolean isExcel2003(String filePath){
return filePath.matches("^.+\\.(?i)(xls)$");
}
/**
* 判断是不是2007版本的excel
* @param filePath
* @return
*/
public static boolean isExcel2007(String filePath){
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
public boolean validateExcel(String filePath){
if(filePath ==null||!(isExcel2003(filePath) || isExcel2007(filePath))){
readExcel.setErrorMsg("文件名不是excel格式");
return false;
}
return true;
}
/**
* 读取excel里面的客户信息
*/
public List<ExcelImport> readExcelValue(Workbook wb){
List<ExcelImport> ilist=new ArrayList<>();
//先得到一个sheet
Sheet sheet = wb.getSheetAt(0);
//得到excel里面的行数
int totalRows = sheet.getPhysicalNumberOfRows();
readExcel.setTotalRows(totalRows);

//得到excel里面的列，前提是有行

//大于1是因为我从第二行就是数据了，这个大家看情况而定

if(totalRows >1 && sheet.getRow(0)!=null){
int totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
readExcel.setTotalCells(totalCells);
}

for (int r = 1 ; r < totalRows; r++){
Row row = sheet.getRow(r);
if(row == null){
continue;//如果行为空的话直接中断
}
ExcelImport excelImport = new ExcelImport();
//循环xcel的列
for(int c = 0; c<readExcel.getTotalCells() ; c++){
Cell cell = row.getCell(c);

if(cell != null){

//因为我只有三列所以只有判断到三大家看自己的情况写

if(c == 0){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
//如果是纯数字,比如你写的是25,
//cell.getNumericCellValue()获得是25.0,
//通过截取字符串去掉.0获得25
String uname = String.valueOf(cell.getNumericCellValue());
//截取如果length()-2为零了，就说明只有一位数，就直接截取0到1就行
excelImport.setUname(uname.substring(0, uname.length()-2>0?uname.length()-2:1));
}else{
//如果不是纯数字可以直接获得名称
excelImport.setUname(cell.getStringCellValue());
}

}else if (c == 1){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
//如果是纯数字,比如你写的是25,
//cell.getNumericCellValue()获得是25.0,
//通过截取字符串去掉.0获得25
String usex = String.valueOf(cell.getNumericCellValue());
//截取如果length()-2为零了，就说明只有一位数，就直接截取0到1就行
excelImport.setUsex(usex.substring(0, usex.length()-2>0?usex.length()-2:1));
}else{
//如果不是纯数字可以直接获得名称
excelImport.setUsex(cell.getStringCellValue());
}
}else if( c == 2){
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
//如果是纯数字,比如你写的是25,
//cell.getNumericCellValue()获得是25.0,
//通过截取字符串去掉.0获得25
String uage = String.valueOf(cell.getNumericCellValue());
//截取如果length()-2为零了，就说明只有一位数，就直接截取0到1就行
excelImport.setUage(uage.substring(0, uage.length()-2>0?uage.length()-2:1));
}else{
//如果不是纯数字可以直接获得名称
excelImport.setUage(cell.getStringCellValue());
}
}
}
}
//最后将这些全部添加到ilist中
ilist.add(excelImport);
}
return ilist;
}
public List<ExcelImport> createExcel(InputStream is ,boolean isExcel2003){
List<ExcelImport> ilist = null;
try {
Workbook wb = null;
if(isExcel2003){//如果是2003版本的就new一个2003的wb出来
wb = new HSSFWorkbook(is);
}else{
//否则就new 一个2007版的出来
wb = new XSSFWorkbook(is);

}
//再让wb去解析readExcelValue(Workbook wb)方法
ilist = readExcelValue(wb);
} catch (Exception e) {
e.printStackTrace();
}
return ilist;
}
}

com.xy.service

ExcelImportService接口：

package com.xy.service;

import org.springframework.web.multipart.MultipartFile;

public interface ExcelImportService {

public String readExcelFile(MultipartFile file);
}

com.xy.service.impl

ExcelImportServiceImp.java:

package com.xy.service.imp;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.xy.entity.ExcelImport;
import com.xy.mapper.ExcelImplMapper;
import com.xy.service.ExcelImportService;
import com.xy.utils.ExcelUtil;

@Service
public class ExcelImportServiceImp implements ExcelImportService{

@Autowired
private ExcelImplMapper excelImplMapper;

@Override
public String readExcelFile(MultipartFile file) {
String result = "";
ExcelUtil excel = new ExcelUtil();
List<ExcelImport> ilist = excel.getExcelInfo(file);
if(ilist != null && !ilist.isEmpty()){
//不为空的话添加到数据库
for (ExcelImport excelImport : ilist) {
excelImplMapper.save(excelImport);
}
result = "上传成功";
}else{
result = "上传失败";
}
return result;
}

}

com.xy.controller

ExcelController.java

package com.xy.controller;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.xy.service.ExcelImportService;

@Controller
@RequestMapping("/excel")
public class ExcelController {

@Autowired
private ExcelImportService excelImportService;

@RequestMapping("/in")
public String toExcel(){
return "excelimport";
}
/**
* 文件上传的方法
* @param file
* @param request
* @param response
* @return
*/
@RequestMapping(value="/upload",method = RequestMethod.POST)
@ResponseBody
public String upload(@RequestParam(value="file")MultipartFile file,HttpServletRequest request, HttpServletResponse response){
String result = excelImportService.readExcelFile(file);
return result;
}
}

访问：127.0.0.1:8088/excel/in