小白easyexcel的使用介绍
我这里使用的是springboot里实现从excel读取出数据,并且批量存储到数据库里。
我这里使用的是mybatis_plus实现的
文章目的与实现:
-
目的:自己建的一个excel,想要点击上传按钮就直接将excel里的内容直接传输到数据库。
-
实现:
- 进入localhost:8080.
- 点击选择文件,选择自己建的excel
- 点击提交,返回success就是成功了。
- 查看数据库的前后对比
刷新表:
代码部分:
这里我把整个代码和excel文件都放到了百度网盘里,有需要的可以自行下载,欢迎大家来讨论哈哈哈~~~
链接:https://pan.baidu.com/s/1LtF9u-dymL9W51lthYDkUA
提取码:9z49
复制这段内容后打开百度网盘手机App,操作更方便哦
- 前端页面:(贼简单的一个按钮即可)
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>文件上传</h1>
<form action="http://localhost:8080/excel" method="post" enctype="multipart/form-data">
文件名称:<input name="excelfile" type="file"/><br>
<input type="submit" value="提交"/>
</form>
</body>
- 数据库页面:
我这里想要输入的excel固定格式为name–>date–>gender
所以数据库的表也设计为这个格式:
-
后端页面:正常的mvc分层
- 总览结构:
- 数据库映射类:这里也是简单的name–>date–>gender即可
@Data public class DemoExcel { private String name; private Date date; private String gender; }
- mapper层
@Mapper public interface DemoExcelMapper extends BaseMapper<DemoExcel> { void batchInsert(List<DemoExcel> list); }
- service层
public interface DemoExcelService extends IService<DemoExcel> { void batchInsert(List<DemoExcel> list); }
- Impl层:
@Service public class DemoExcelServiceImpl extends ServiceImpl<DemoExcelMapper,DemoExcel> implements DemoExcelService { @Autowired DemoExcelMapper demoExcelMapper; @Override public void batchInsert(List<DemoExcel> list) { demoExcelMapper.batchInsert(list); } }
- controller层:
@RestController public class DemoExcelController { @Autowired DemoExcelListener demoExcelListener; @RequestMapping("/excel") public String readExcel(MultipartFile excelfile){ try { ExcelReaderBuilder read = EasyExcel.read(excelfile.getInputStream(), DemoExcel.class, demoExcelListener); read.sheet().doRead(); return "success"; } catch (IOException e) { return "false"; } } }
- 监听器层:!!!这个层是最重要的!!!!!!!!!!整篇核心!!!!!!!!!!!!!
@Component @Scope("prototype") public class DemoExcelListener extends AnalysisEventListener<DemoExcel> { @Autowired DemoExcelService demoExcelService; ArrayList<DemoExcel> excellist=new ArrayList<DemoExcel>(); @Override public void invoke(DemoExcel demoExcel, AnalysisContext analysisContext) { excellist.add(demoExcel); if (excellist.size()==5) { demoExcelService.batchInsert(excellist); excellist.clear(); } } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { } }
- mapper.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.ws.easyexcel.mapper.DemoExcelMapper"> <insert id="batchInsert" parameterType="com.ws.easyexcel.entity.DemoExcel"> insert into demoexcel.demo_excel(name, date, gender) values <foreach collection="list" item="item" separator=","> (#{item.name},#{item.date},#{item.gender}) </foreach> </insert> </mapper>
- application.yml
spring: datasource: username: root password: '0000' url: jdbc:mysql://localhost:3306/demoexcel?useSSL=false driver-class-name: org.gjt.mm.mysql.Driver mybatis-plus: configuration: map-underscore-to-camel-case: true
- excel长什么样