快捷導(dǎo)航

springboot接收excel數(shù)據(jù)文件去重方式

更新時(shí)間：2024年12月12日 14:55:59 作者：jzx-sdjz

文章主要介紹了如何在Spring?Boot中實(shí)現(xiàn)文件上傳并入庫的功能,包括讀取Excel文件、生成Entity對象、使用MergeInto語句進(jìn)行數(shù)據(jù)庫操作以及注意事項(xiàng)

springboot接收excel數(shù)據(jù)文件去重

1.創(chuàng)建測試表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL COMMENT '學(xué)號',
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `year` int(11) NOT NULL COMMENT '年份',
  `level` tinytext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '等級',
  PRIMARY KEY (`id`, `name`, `year`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

2.生產(chǎn)測試文檔

3.springboot接收文件并入庫

接收到上傳的模板文件后，首先讀取首行進(jìn)行字段名稱判斷，然后一行行的讀取excel表格中的數(shù)據(jù)，生成entity對象。

如果入庫的數(shù)組大小超過1000，那就入庫一次，然后入庫使用的類似oracle的merge into語句的ON DUPLICATE KEY UPDATE

注意事項(xiàng)：

因?yàn)闇y試樣例數(shù)據(jù)量小，上傳文件大小限制在springboot中沒有設(shè)置
生成的excel字段學(xué)號和年份單元格格式應(yīng)該設(shè)置為數(shù)值

    @Autowired
    private OperateDao operateDao;

    @PostMapping(value = "/add/tax")
    public String insertTaxData(@RequestParam("file") MultipartFile file) {
        try {
            String filename = file.getOriginalFilename();
            System.out.println(filename);
            InputStream tps = (file.getInputStream());
            final XSSFWorkbook tpWorkbook = new XSSFWorkbook(tps);

            Sheet sheet = tpWorkbook.getSheetAt(0);
            Row headerRow = sheet.getRow(0);
            if (headerRow.getCell(0).getStringCellValue().equals("學(xué)號")
                    && headerRow.getCell(1).getStringCellValue().equals("姓名")
                    && headerRow.getCell(2).getStringCellValue().equals("年份")
                    && headerRow.getCell(3).getStringCellValue().equals("等級")
            ) {
                int i = 1;
                boolean flag = true;
                List<StudentEntity> entityList = new ArrayList<>();
                StudentEntity studentEntity;
                while (flag) {
                    Row row = sheet.getRow(i++);
                    if (row != null) {
                        studentEntity = new StudentEntity();
                        Cell id = row.getCell(0);
                        Cell name = row.getCell(1);
                        Cell year = row.getCell(2);
                        Cell level = row.getCell(3);
                        studentEntity.setId((int) id.getNumericCellValue());
                        studentEntity.setName(name.getStringCellValue());
                        studentEntity.setYear((int) year.getNumericCellValue());
                        studentEntity.setLevel( level.getStringCellValue());
                        entityList.add(studentEntity);
                    } else {
                        flag = false;
                    }
                    if (entityList.size() > 1000) {
                        int rs = operateDao.addOrUpdateBatchTax(entityList);
                        System.out.println(rs);
                        entityList.clear();
                    }
                }
                if (entityList.size() > 0) {
                    operateDao.addOrUpdateBatchTax(entityList);
                }
                tps.close();
            } else {
                return "檢查模板是否正確";
            }
        } catch (IOException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        return "success";
    }

@Mapper
public interface OperateDao {
    Integer addOrUpdateBatchTax(List<StudentEntity> list);
}

<?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.test.springboot.dao.OperateDao">
    <insert id="addOrUpdateBatchTax" parameterType="java.util.List">
        INSERT INTO `student`(`id`, `name`, `year`, `level`)
        VALUES
        <foreach collection="list" item="tax" index="index" separator=",">
            (#{tax.id},#{tax.name},#{tax.year},#{tax.level})
        </foreach>
        ON DUPLICATE KEY UPDATE
        level =VALUES ( `level` )
    </insert>

</mapper>