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

迈不过友情╰ 2022-04-13 10:10 223阅读 0赞

推荐使用开源工具包，超好用。  
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页面，代码做了部分修改，感谢博主 “纯洁的小魔鬼” 解决了我的问题。