基于Java SSM实现Excel数据批量导入

基于Java SSM实现Excel数据批量导入

目录导入Maven依赖Mapper及映射文件测试

导入Maven依赖

com.alibaba

easyexcel

${easyexcel.version}

Mapper及映射文件

UserMapper.java

@Mapper

public interface UserMapper {

int batchInsert(@Param("list") List list);

}

UserMapper.xml

id, nickname, avatar, account, `password`, gender, tel, email, qq, wechat, salt,

info, `status`, create_time, update_time

insert into tb_user

(nickname, avatar, account, `password`, gender, tel, email, qq, wechat, salt, info,

`status`, create_time, update_time)

values

(#{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})

Excel监听器

@Log4j2

@Service

public class UserExcelListener extends AnalysisEventListener {

@Resource

private UserMapper userMapper;

/**

* 批处理阈值

*/

private static final int BATCH_COUNT = 250;

@Getter

List 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();

}

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。