SSM项目实现Excel数据批量导入#私藏项目实操分享#

【SSM项目实现Excel数据批量导入#私藏项目实操分享#】临文乍了了，彻卷兀若无。这篇文章主要讲述SSM项目实现Excel数据批量导入#私藏项目实操分享#相关的知识，希望能为你提供帮助。
导入Maven依赖

< dependency> < groupId> com.alibaba< /groupId> < artifactId> easyexcel< /artifactId> < version> ${easyexcel.version}< /version> < /dependency>

Mapper及映射文件 UserMapper.java

@Mapper public interface UserMapper { int batchInsert(@Param("list") List< User> list); }

UserMapper.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.hc.mapper.UserMapper"> < resultMap id="BaseResultMap" type="com.hc.domain.User"> < !--@mbg.generated--> < !--@Table tb_user--> < id column="id" jdbcType="BIGINT" property="id" /> < result column="nickname" jdbcType="VARCHAR" property="nickname" /> < result column="avatar" jdbcType="VARCHAR" property="avatar" /> < result column="account" jdbcType="VARCHAR" property="account" /> < result column="password" jdbcType="VARCHAR" property="password" /> < result column="gender" jdbcType="TINYINT" property="gender" /> < result column="tel" jdbcType="VARCHAR" property="tel" /> < result column="email" jdbcType="VARCHAR" property="email" /> < result column="qq" jdbcType="VARCHAR" property="qq" /> < result column="wechat" jdbcType="VARCHAR" property="wechat" /> < result column="salt" jdbcType="VARCHAR" property="salt" /> < result column="info" jdbcType="VARCHAR" property="info" /> < result column="status" jdbcType="TINYINT" property="status" /> < result column="create_time" jdbcType="TIMESTAMP" property="createTime" /> < result column="update_time" jdbcType="TIMESTAMP" property="updateTime" /> < /resultMap> < sql id="Base_Column_List"> < !--@mbg.generated--> id, nickname, avatar, account, `password`, gender, tel, email, qq, wechat, salt, info, `status`, create_time, update_time < /sql> < insert id="batchInsert" keyColumn="id" keyProperty="id" parameterType="map" useGeneratedKeys="true"> < !--@mbg.generated--> insert into tb_user (nickname, avatar, account, `password`, gender, tel, email, qq, wechat, salt, info, `status`, create_time, update_time) values < foreach collection="list" item="item" separator=","> (#{item.nickname,jdbcType=VARCHAR}, #{item.avatar,jdbcType=VARCHAR}, #{item.account,jdbcType=VARCHAR}, #{item.password,jdbcType=VARCHAR}, #{item.gender,jdbcType=TINYINT}, #{item.tel,jdbcType=VARCHAR}, #{item.email,jdbcType=VARCHAR}, #{item.qq,jdbcType=VARCHAR}, #{item.wechat,jdbcType=VARCHAR}, #{item.salt,jdbcType=VARCHAR}, #{item.info,jdbcType=VARCHAR}, #{item.status,jdbcType=TINYINT}, #{item.createTime,jdbcType=TIMESTAMP}, #{item.updateTime,jdbcType=TIMESTAMP}) < /foreach> < /insert> < /mapper>

Excel监听器

@Log4j2 @Service public class UserExcelListener extends AnalysisEventListener< User> {@Resource private UserMapper userMapper; /** * 批处理阈值 */ private static final int BATCH_COUNT = 250; @Getter List< User> list = new ArrayList< > (BATCH_COUNT); @Override public void invoke(User user, AnalysisContext analysisContext) { //逐行读取数据 log.info("********** 解析到一条数据:{}", JSON.toJSONString(user)); list.add(user); if (list.size() > = BATCH_COUNT) { System.out.println("已经解析"+list.size()+"条数据"); //每250条，往数据库中存一次 int batchInsertRes = userMapper.batchInsert(list); System.out.println(batchInsertRes); list.clear(); } }@Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { log.info("**********所有数据解析完成！"); } }

测试

@ExtendWith(SpringExtension.class) @ContextConfiguration("/applicationContext.xml") public class ExcelUtilTest { @Resource private UserExcelListener userExcelListener; @Test void userListener(){ EasyExcel.read("E:\\\\Projects\\\\WorksDisplay\\\\data\\\\users.xlsx", User.class, userExcelListener) .sheet() .doRead(); } }