pom文件project标签报错_springboot+mybatis实现excel文件上传

点击上方Java开发联盟，选择“星标公众号”

优质文章，第一时间送达

简介：

        项目要求有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.0modelVersion>    <groupId>com.luogroupId>    <artifactId>fileimportartifactId>    <version>0.0.1-SNAPSHOTversion>    <packaging>jarpackaging>    <name>fileimportname>    <description>Demo project for Spring Bootdescription>    <parent>        <groupId>org.springframework.bootgroupId>        <artifactId>spring-boot-starter-parentartifactId>        <version>2.0.4.RELEASEversion>        <relativePath/>     parent>    <properties>        <project.build.sourceEncoding>UTF-8project.build.sourceEncoding>        <project.reporting.outputEncoding>UTF-8project.reporting.outputEncoding>        <java.version>1.8java.version>    properties>    <dependencies>        <dependency>            <groupId>org.springframework.bootgroupId>            <artifactId>spring-boot-starter-jdbcartifactId>        dependency>        <dependency>            <groupId>org.springframework.bootgroupId>            <artifactId>spring-boot-starter-webartifactId>        dependency>        <dependency>            <groupId>org.mybatis.spring.bootgroupId>            <artifactId>mybatis-spring-boot-starterartifactId>            <version>1.3.2version>        dependency>        <dependency>            <groupId>mysqlgroupId>            <artifactId>mysql-connector-javaartifactId>            <scope>runtimescope>        dependency>        <dependency>            <groupId>org.springframework.bootgroupId>            <artifactId>spring-boot-starter-testartifactId>            <scope>testscope>        dependency>        <dependency>            <groupId>org.apache.poigroupId>            <artifactId>poi-ooxmlartifactId>            <version>3.17version>        dependency>        <dependency>            <groupId>com.alibabagroupId>            <artifactId>druid-spring-boot-starterartifactId>            <version>1.1.9version>        dependency>        <dependency>            <groupId>org.springframework.bootgroupId>            <artifactId>spring-boot-starter-thymeleafartifactId>        dependency>    dependencies>    <build>        <plugins>            <plugin>                <groupId>org.springframework.bootgroupId>                <artifactId>spring-boot-maven-pluginartifactId>            plugin>        plugins>    build>project>

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

1、application.yml文件

server:  port: 8080spring:  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: 30MBmybatis:  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页面)

<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>Documenttitle>head><body><form action="/import" method="post" 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; @Controllerpublic 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; @Servicepublic 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 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 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                String temp = getCellVal(row.getCell(i)).toString().trim();                for (int j=0;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"?><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;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; /** * 定义返回信息 */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; 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层定义

<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>Documenttitle>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循环和判断，性能上可能达不到有些项目的需求，比较适合新手学习和巩固，如果您有更好的想法和思路欢迎私信或者留言区多多指教，如果您对此例子有任何问题也欢迎评论和私信，我会尽我所能为您解惑。

温暖提示

为了方便大家更好的学习，本公众号经常分享项目干货源码案例给大家去练习，如果本公众号没有你要学习的功能案例，你可以联系小编(微信：wcy18898375730)提供你帮助哦！

推荐案例

1、springboot+Mybatis+vue前后端分离开发：作业管理系统

2、SpringMVC +Spring+ Mybatis 的完整小项目

3、Java基础知识总结(四)

4、Java基础知识总结(五)