【java小白入门】springboot+mybatis实现excel文件上传并将文件数据存入数据库

 

公司项目要求有excel文件上传这个功能，由于这个功能十分常见所以公司也有这个功能的相关模块来处理，只是接触了springboot有一段时间了，所以想着自己利用springboot和mybatis来实现一下这个功能，算是对基础知识的巩固吧。

项目配置：Springboot，mybatis，POI，mysql，thymeleaf（为了达到前后端完善的目的）

项目的目录结构：

一、导入相关包——POM配置

<?xml version="1.0" encoding="UTF-8"?>
<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.luo</groupId>
    <artifactId>fileimport</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>fileimport</name>
    <description>Demo project for Spring Boot</description>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.4.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </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-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.9</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

二、配置文件——数据库相关配置

1.application.yml文件

server:
  port: 8080
spring:
  thymeleaf:
    prefix: classpath:/templates/  #classpath:指向的是是springboot项目下的resource目录，classpath:/templates/下放的是前端的html5静态页面
    suffix: .html
    mode: HTML5
  datasource:
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false  #数据库配置,useSSL=false是MySQL在高版本需要指明是否进行SSL连接
    driver-class-name: com.mysql.jdbc.Driver
    username: root
    password: root
  jpa:
    show-sql: true
  servlet:
    multipart:
      max-file-size: 30MB  #设置上传文件的大小
      max-request-size: 30MB
mybatis:
  mapper-locations: classpath:mapper/*.xml  #classpath:表示的shispringboot项目下的resource目录，classpath:mapper/*.xml映射的是mybatis的映射文件，如果您的mapper文件目录和我的不同需要修改此路径
  type-aliases-package: com.luo.filetest.model.Bank

2.创建数据表语句：

DROP TABLE IF EXISTS `banktb`;
CREATE TABLE `banktb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `docNumber` varchar(255) DEFAULT NULL,
  `account` varchar(255) DEFAULT NULL,
  `firstTime` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `flag` varchar(255) DEFAULT NULL,
  `unit` varchar(255) DEFAULT NULL,
  `remark` varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `info` varchar(255) DEFAULT NULL,
  `moneyOut` varchar(255) DEFAULT NULL,
  `moneyIn` varchar(255) DEFAULT NULL,
  `timeEnd` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=197 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

三、前端页面（templates下的index.html页面）

<!doctype html>
<html lang="ch">
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
</head>
<body>
<form action="/import" method="post" enctype="multipart/form-data"><!--enctype="multipart/form-data"是必须的-->
    <input type="file" name="file"><br>
    <input type="submit" id="btnSub" value="提交">
</form>
</body>
</html>

四、Controller层

Controller层需要一个配置一个loadPage转到上传文件的界面

package com.luo.controller;

import com.luo.model.ReturnInfoUtil;
import com.luo.model.ReturnUtil;
import com.luo.service.ExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by Luowenlv on 2018/9/10,15:29
 */
@Controller
public class ExcelController {
    @Autowired
    private ExcelService excelService;
    private ReturnInfoUtil returnInfoUtil;

    List userList = new ArrayList();

    @RequestMapping("/import")
    public String fileImport(@RequestParam("file") MultipartFile file,Model model) throws Exception {

        String fileName = file.getOriginalFilename();
        ReturnInfoUtil returnInfoUtil = excelService.getExcelInfo(fileName, file);
        model.addAttribute("retInfo",returnInfoUtil);
        return "info";
    }

    @RequestMapping("/loadPage")
    public String loadPage() {
        return "index";
    }


}

五、Service层

service层是文件上传的核心部分，需要对excel文件进行解析并提取需要上传至数据库的部分，有的项目对上传文件需要的是全部列都上传，有的需求则是只需要将表中的某几列导入数据库，因此sevice层还需要对此做相关配置。在本demo中本人采取的是比较奔的方法来处理全部上传和部分上传，利用一个int数组（resultCell）来保存需要上传列的序号，从0开始计数，将excel表中的所有列来与之匹配得到最后结果集，这样做的好处是之后要修改上传的列时只需要修改int数组的值即可。

package com.luo.service.impl;

import com.luo.dao.ExcelDao;
import com.luo.model.BankModel;
import com.luo.model.ReturnInfoUtil;
import com.luo.model.ReturnUtil;
import com.luo.service.ExcelService;
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.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

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

/**
 * Created by Luowenlv on 2018/9/10,15:29
 */
@Service
public class ExcelServiceImpl implements ExcelService {
    @Autowired
    ExcelDao excelDao;

    /**
     *
     * @param fileName
     * @param file
     * @return
     * @throws Exception
     */
    @Override
    public ReturnInfoUtil getExcelInfo(String fileName, MultipartFile file) throws Exception {
        ReturnInfoUtil returnInfoUtil = new ReturnInfoUtil();
        int[] resultCell = new int[]{0,3,4};//要将表中的哪几列传入数据库中，从0开始计数
        List<BankModel> resultList = new ArrayList<>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return ReturnUtil.error("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        resultList = getSheetVal(sheet, resultCell);
        System.out.println("结果集---"+resultList);
        try{
            excelDao.addUser(resultList);
            returnInfoUtil = ReturnUtil.success("");
        }catch (Exception e){
            e.printStackTrace();
            returnInfoUtil = ReturnUtil.error("数据导入失败");
        }
        return returnInfoUtil;
    }

    /**
     *
     * @param sheet
     * @param resultCell 需要将哪些列插入至数据库
     * @return
     */
    public List getSheetVal(Sheet sheet, int[] resultCell){
        List<BankModel> bankList = new ArrayList<>();//返回的结果集
        int[] resultIndex = new int[resultCell.length];//存储需要上传字段的下标
        BankModel bank;
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            bank = new BankModel();
            for (int i = 0;i<row.getPhysicalNumberOfCells();i++){
                String temp = getCellVal(row.getCell(i)).toString().trim();
                for (int j=0;j<resultCell.length;j++){
                    if (i==resultCell[j]){
                        switch (i){
                            case 0:
                                bank.setDocNumber(temp);
                                break;
                            case 1:
                                bank.setAccount(temp);
                                break;
                            case 2:
                                bank.setFirstTime(temp);
                                break;
                            case 3:
                                bank.setFlag(temp);
                                break;
                            case 4:
                                bank.setUnit(temp);
                                break;
                            case 5:
                                bank.setRemark(temp);
                                break;
                            case 6:
                                bank.setInfo(temp);
                                break;
                            case 7:
                                bank.setMoneyOut(temp);
                                break;
                            case 8:
                                bank.setMoneyIn(temp);
                                break;
                            case 9:
                                bank.setTimeEnd(temp);
                                break;
                            default:
                                break;
                        }
                    }else{
                        continue;
                    }
                }
            }
            bankList.add(bank);
        }
        return bankList;
    }

    /**
     *
     * @param cell
     * @return
     */
    public Object getCellVal(Cell cell){
        Object obj = null;
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case NUMERIC:
                obj = cell.getNumericCellValue();
                break;
            case STRING:
                obj = cell.getStringCellValue();
                break;
            default:
                break;
        }
        return obj;
    }
}

 

六、mybatis  Mapper映射文件

本demo中测试的是部分上传（int[] resultCell = new int[]{0,3,4};），因此数据库语句只插入部分列，如果您有其他的需求，可以先修改数组resultCell的值，但请注意数组resultCell的长度不应该超过上传文件的总列数，可以对resultCell数组进行一次判断再做处理，最后修改sql语句即可，其他部分不用做修改。

<?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.luo.dao.ExcelDao">
    <insert id="addUser" parameterType="java.util.List">
        insert into banktb(docNumber,flag,unit) values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.docNumber, jdbcType=VARCHAR},#{item.flag, jdbcType=VARCHAR},#{item.unit, jdbcType=VARCHAR})
        </foreach>
    </insert>
</mapper>

七、其他帮助类

1.excel文件映射类，即您要上传的文件的列，本demo中的文件类：

package com.luo.model;

/**
 * Created by Luowenlv on 2018/9/10,15:34
 */
public class BankModel {
    private Integer id;
    private String docNumber;
    private String account;
    private String firstTime;
    private String flag;
    private String unit;
    private String remark;
    private String info;
    private String moneyOut;
    private String moneyIn;
    private String timeEnd;

    public BankModel() {
    }

    public BankModel(Integer id, String docNumber, String account, String firstTime, String flag, String unit, String remark, String info, String moneyOut, String moneyIn, String timeEnd) {
        this.id = id;
        this.docNumber = docNumber;
        this.account = account;
        this.firstTime = firstTime;
        this.flag = flag;
        this.unit = unit;
        this.remark = remark;
        this.info = info;
        this.moneyOut = moneyOut;
        this.moneyIn = moneyIn;
        this.timeEnd = timeEnd;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDocNumber() {
        return docNumber;
    }

    public void setDocNumber(String docNumber) {
        this.docNumber = docNumber;
    }

    public String getAccount() {
        return account;
    }

    public void setAccount(String account) {
        this.account = account;
    }

    public String getFirstTime() {
        return firstTime;
    }

    public void setFirstTime(String firstTime) {
        this.firstTime = firstTime;
    }

    public String getFlag() {
        return flag;
    }

    public void setFlag(String flag) {
        this.flag = flag;
    }

    public String getUnit() {
        return unit;
    }

    public void setUnit(String unit) {
        this.unit = unit;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }

    public String getInfo() {
        return info;
    }

    public void setInfo(String info) {
        this.info = info;
    }

    public String getMoneyOut() {
        return moneyOut;
    }

    public void setMoneyOut(String moneyOut) {
        this.moneyOut = moneyOut;
    }

    public String getMoneyIn() {
        return moneyIn;
    }

    public void setMoneyIn(String moneyIn) {
        this.moneyIn = moneyIn;
    }

    public String getTimeEnd() {
        return timeEnd;
    }

    public void setTimeEnd(String timeEnd) {
        this.timeEnd = timeEnd;
    }

    @Override
    public String toString() {
        return "BankModel{" +
                "id=" + id +
                ", docNumber='" + docNumber + '\'' +
                ", account='" + account + '\'' +
                ", firstTime='" + firstTime + '\'' +
                ", flag='" + flag + '\'' +
                ", unit='" + unit + '\'' +
                ", remark='" + remark + '\'' +
                ", info='" + info + '\'' +
                ", moneyOut='" + moneyOut + '\'' +
                ", moneyIn='" + moneyIn + '\'' +
                ", timeEnd='" + timeEnd + '\'' +
                '}';
    }
}

 

2.统一返回结果类，定义返回给前端的统一的结果类，导入成功或失败，返回内容和返回数据

package com.luo.model;

/**
 * Created by Luowenlv on 2018/12/7,9:57
 * 定义返回信息
 */
public class ReturnInfoUtil {
    private Integer retFlag;
    private String retMsg;
    private Object retVal;

    public ReturnInfoUtil(Integer retFlag, String retMsg, Object retVal) {
        this.retFlag = retFlag;
        this.retMsg = retMsg;
        this.retVal = retVal;
    }

    public ReturnInfoUtil() {
    }

    public Integer getRetFlag() {
        return retFlag;
    }

    public void setRetFlag(Integer retFlag) {
        this.retFlag = retFlag;
    }

    public String getRetMsg() {
        return retMsg;
    }

    public void setRetMsg(String retMsg) {
        this.retMsg = retMsg;
    }

    public Object getRetVal() {
        return retVal;
    }

    public void setRetVal(Object retVal) {
        this.retVal = retVal;
    }

    @Override
    public String toString() {
        return "ReturnInfoUtil{" +
                "retFlag=" + retFlag +
                ", retMsg='" + retMsg + '\'' +
                ", retVal=" + retVal +
                '}';
    }
}

 

package com.luo.model;


/**
 * Created by Luowenlv on 2018/12/7,10:00
 */
public class ReturnUtil {
    private static ReturnInfoUtil returnInfoUtil = new ReturnInfoUtil();
    public static ReturnInfoUtil error(String retMag){
        returnInfoUtil.setRetFlag(1);
        returnInfoUtil.setRetMsg(retMag);
        returnInfoUtil.setRetVal("");
        return returnInfoUtil;
    }
    public static ReturnInfoUtil success(Object retVal){
        returnInfoUtil.setRetFlag(0);
        returnInfoUtil.setRetMsg("导入成功");
        returnInfoUtil.setRetVal(retVal);
        return returnInfoUtil;
    }
}

八、springboot启动类：

@MapperScan注解可以指定要扫描的Mapper类的包的路径

package com.luo;

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

@SpringBootApplication
@MapperScan("com.luo.dao")
public class FileimportApplication {

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

九、点击上传后返回的结果页面（thymeleaf）

返回结果由controller层定义

<!doctype html>
<html lang="ch" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
</head>
<body>
<span th:text="${retInfo}"></span>
</body>
</html>

 

代码示例中没有贴入Service层和Dao层的接口，上传的测试文件也没有贴出，完整的demo可以通过这个路径来进行下载：https://github.com/xiBei-luo/JAVA/tree/master/fileimport

本demo是一个很简单的小例子，但是兼顾了前端、服务层、数据库持久层，服务层在解析excel时借助了POI框架，由于本人能力有限，其中使用了很多的for循环和判断，性能上可能达不到有些项目的需求，比较适合本人这样的新手学习和巩固，如果您有更好的想法和思路欢迎评论和私信多多指教，如果您对此例子有任何问题也欢迎评论和私信，我会尽我所能为您解惑。