导入excel并批量插入到数据库

灰太狼 2023-09-28 08:49 39阅读 0赞

Controller层

@PostMapping("/import")
        @ApiOperation(value = "导入excel", notes = "导入excel")
        public RestApiResponse<?> importStatistics(@RequestPart("file") MultipartFile file) {
        
          
            List<User> userList = ExcelUtils.importExcel(file, 0, 1, User.class);
            logger.info("识别到{}条数据", userList.size())；
            // 执行批量插入
            service.insertBatch(userList);
            return RestApiResponse.ok(userList);
        }

importExcel方法编写：

public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows,
                Class<T> pojoClass) {
        
                // titleRows 标题 headerRows 头
            if (file == null) {
        
                return null;
            }
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List<T> list = null;
            try {
        
                list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
            } catch (NoSuchElementException e) {
        
                throw new ServiceException("excel file can't be null!");
            } catch (Exception e) {
        
                throw new ServiceException(e);
            }
            return list;
        }

调用的依赖easypoi，以maven为例：

<dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-base</artifactId>
                <version>3.1.0</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-web</artifactId>
                <version>3.1.0</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-annotation</artifactId>
                <version>3.1.0</version>
            </dependency>

批量插入sql:

<insert id="insertBatch" parameterType="java.util.List">
            insert into
            tabble_name(id,name,age) 
            values
            <foreach collection="list" item="item" index="index" separator=",">
                (#{item.id}, #{item.name}, #{item.age})
            </foreach>
    </insert>```