spring-boot 将excel表数据插入或更新到mysql数据库

推荐使用开源工具包，超好用。
https://gitee.com/lemur/easypoi
https://gitee.com/loolly/hutool
————————更新于 2019-09-23—————————————

我的问题终于解决了，下面是完整的代码。

部署好springboot后，pom.xml 添加Apache POL依赖

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

BmcGoodsMapper.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.atguigu.springboot.dao.BmcGoodsMapper">
    <insert id="addBmcGood" parameterType="com.atguigu.springboot.domain.BmcGoods">
    insert into bmc_goods
    (g_id, g_price, s_id, uptime)
    values
    (
    #{gId},
    #{gPrice},
    #{sId},
    #{uptime}
    )
  </insert>

    <update id="updateBmcGoodsById" parameterType="com.atguigu.springboot.domain.BmcGoods">
    update bmc_goods
    set
    g_price = #{gPrice},
    s_id = #{sId},
    uptime = #{uptime}
    where g_id = #{gId}
  </update>
  
    <select id="selectById" parameterType="Integer" resultType="java.lang.Integer">
        select count(*) from bmc_goods where g_id = #{gId}
    </select>
</mapper>

BmcGoodsMapper 映射接口

package com.atguigu.springboot.dao;
import com.atguigu.springboot.domain.BmcGoods;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface BmcGoodsMapper {

    void addBmcGood(BmcGoods bmcGoods);

    int updateBmcGoodsById(BmcGoods bmcGoods);

    int selectById(Integer gId);
}

BmcGoodsService 服务层接口

package com.atguigu.springboot.service;

import org.springframework.web.multipart.MultipartFile;

public interface BmcGoodsService {

    boolean batchImport(String fileName, MultipartFile file) throws Exception;
}

BmcGoodsServiceImpl 实现类

package com.atguigu.springboot.service.impl;

import com.atguigu.springboot.common.MyException;
import com.atguigu.springboot.dao.BmcGoodsMapper;
import com.atguigu.springboot.domain.BmcGoods;
import com.atguigu.springboot.service.BmcGoodsService;
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.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
@Transactional(readOnly = true)
public class BmcGoodsServiceImpl implements BmcGoodsService {

    @Autowired
    private BmcGoodsMapper userMapper;

    @Transactional(readOnly = false, rollbackFor = Exception.class)
    @Override
    public boolean batchImport(String fileName, MultipartFile file) throws Exception {

        boolean notNull = false;
        List<BmcGoods> bmcGoodsList = new ArrayList<BmcGoods>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new MyException("上传文件格式不正确");
        }
        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);
        if (sheet != null) {
            notNull = true;
        }

        BmcGoods bmcGoods = null;
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }

            bmcGoods = new BmcGoods();

            if(row.getCell(0)!=null){
                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                bmcGoods.setgId(Integer.parseInt(row.getCell(0).getStringCellValue()));
            }

            else {
                throw new MyException("导入失败(第" + (r + 1) + "行, 商品id未填写)");
            }

            if(row.getCell(1)!=null){
                row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                bmcGoods.setgPrice(new BigDecimal(row.getCell(1).getStringCellValue()));
            }
            else {
                throw new MyException("导入失败(第" + (r + 1) + "行,商品价格未填写)");
            }

            if(row.getCell(2)!=null){
                row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                bmcGoods.setsId(Integer.parseInt(row.getCell(2).getStringCellValue()));
            }
            else {
                throw new MyException("导入失败(第" + (r + 1) + "行, 店铺id未填写)");
            }

            if(row.getCell(3)!=null){
                bmcGoods.setUptime(row.getCell(3).getDateCellValue());
            }
            else {
                throw new MyException("导入失败(第" + (r + 1) + "行,入职日期格式不正确或未填写)");
            }
            
            bmcGoodsList.add(bmcGoods);
        }

        for (BmcGoods bmcGoodsResord : bmcGoodsList) {
            Integer id = bmcGoodsResord.getgId();
            int cnt = userMapper.selectById(id);
            if (cnt == 0) {
                userMapper.addBmcGood(bmcGoodsResord);
                System.out.println(" 插入 " + bmcGoodsResord);
            } else {
                userMapper.updateBmcGoodsById(bmcGoodsResord);
                System.out.println(" 更新 " + bmcGoodsResord);
            }
        }
        return notNull;
    }
}

domain 类

package com.atguigu.springboot.domain;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

public class BmcGoods implements Serializable {

    private static final long serialVersionUID = 1L;

    private Integer gId;

    private BigDecimal gPrice;

    private Integer sId;

    private Date uptime;

    public Integer getgId() {
        return gId;
    }

    public void setgId(Integer gId) {
        this.gId = gId;
    }

    public BigDecimal getgPrice() {
        return gPrice;
    }

    public void setgPrice(BigDecimal gPrice) {
        this.gPrice = gPrice;
    }

    public Integer getsId() {
        return sId;
    }

    public void setsId(Integer sId) {
        this.sId = sId;
    }

    public Date getUptime() {
        return uptime;
    }

    public void setUptime(Date uptime) {
        this.uptime = uptime;
    }
}

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>上传文件</title>
</head>
<body>
    <form method="post" enctype="multipart/form-data" id="form" action="/bmc/import">
        <input type="file" name="file"/>
        <input type="submit" value="提交上传"/>
    </form>
</body>
</html>

BmcGoodsController.java

package com.atguigu.springboot.controller;

import com.atguigu.springboot.service.BmcGoodsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

@RestController
@RequestMapping("/bmc/")
public class BmcGoodsController {

    @Autowired
    private BmcGoodsService testService;

    @PostMapping("/import")
    public boolean addUser(@RequestParam("file") MultipartFile file) {
        boolean a = false;
        String fileName = file.getOriginalFilename();
        try {
            a = testService.batchImport(fileName, file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  a;
    }
}

MyException.java 异常处理类

package com.atguigu.springboot.common;

public class MyException extends Exception{

    public MyException(String message) {
        super(message);
    }
}

参考: https://blog.csdn.net/xyy1028/article/details/79054749
我添加了html页面，代码做了部分修改，感谢博主 “纯洁的小魔鬼” 解决了我的问题。