使用VUE+SpringBoot+EasyExcel|使用VUE+SpringBoot+EasyExcel 整合导入导出数据的教程详解使用VUE+SpringBoot+EasyExcel整合导

1 前端
2 数据库
3 后端

3.1 contrller
3.2 mapper
3.3 bean
3.4 listener
3.5 config
3.6 配置文件

4 启动测试

创建一个普通的maven项目即可

项目目录结构

使用VUE+SpringBoot+EasyExcel|使用VUE+SpringBoot+EasyExcel 整合导入导出数据的教程详解

文章图片

1 前端

存放在resources/static 下

index.html

Document导入Excel点击上传取消导出Excel

2 数据库

CREATE TABLE `student` (`name` varchar(255) DEFAULT NULL COMMENT '姓名',`birthday` datetime DEFAULT NULL COMMENT '生日',`salary` decimal(10,4) DEFAULT NULL COMMENT '薪资') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3 后端
3.1 contrller
StudentController

@Slf4j@RestController@CrossOrigin@RequestMapping("/api/excel/")public class StudentController {@Resourceprivate StudentMapper studentMapper; @GetMapping("list")public HashMap list(@RequestParam int pageNum,@RequestParam int pageSize){// 分页查询Page page = new Page<>(pageNum, pageSize); studentMapper.selectPage(page,null); // 封装数据HashMap map = new HashMap<>(); ArrayList excelDictDTOList = new ArrayList<>(); // 转换数据page.getRecords().forEach(student -> {ExcelStudentDTO studentDTO = new ExcelStudentDTO(); BeanUtils.copyProperties(student,studentDTO); excelDictDTOList.add(studentDTO); }); map.put("list",excelDictDTOList); map.put("size",page.getTotal()); return map; }/*** 导入* @param file 文件对象*/@RequestMapping("import")@Transactional(rollbackFor = {Exception.class})public String importData( @RequestParam("file") MultipartFile file){try {// 读取文件流EasyExcel.read(file.getInputStream(),// 前端上传的文件ExcelStudentDTO.class,// 跟excel对应的实体类new ExcelDictDTOListener(studentMapper))// 监听器 .excelType(ExcelTypeEnum.XLSX)// excel的类型.sheet("模板").doRead(); log.info("importData finished"); } catch (IOException e) {log.info("失败"); e.printStackTrace(); }return "上传成功"; }/*** 导入*/@GetMapping("export")public String exportData(HttpServletResponse response){try {// 设置响应体内容response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码当然和easyexcel没有关系String fileName = URLEncoder.encode("myStu", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment; filename*=utf-8''" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(),ExcelStudentDTO.class).sheet().doWrite(studentMapper.selectList(null)); } catch (Exception e) {e.printStackTrace(); }return "上传成功"; }}

【使用VUE+SpringBoot+EasyExcel|使用VUE+SpringBoot+EasyExcel 整合导入导出数据的教程详解】
3.2 mapper
StudentMapper

@Mapperpublic interface StudentMapper extends BaseMapper {void insertBatch(List list); }

StudentMapper.xml

insert into student(name, birthday, salary)values(#{item.name} ,#{item.birthday} ,#{item.salary} )

3.3 bean
ExcelStudentDTO
导入数据时要保证excel中列名和ExcelStudentDTO一致奥

/** * excel对应的实体类 * @author jiejie */@Datapublic class ExcelStudentDTO { // excel中的列名@ExcelProperty("姓名")private String name; @ExcelProperty("生日")private Date birthday; @ExcelProperty("薪资")private BigDecimal salary; }

Student

/** * 数据库对应的实体类 * @author jiejie */@Data@TableName(value = "https://www.it610.com/article/student")public class Student {/*** 姓名*/@TableField(value = "https://www.it610.com/article/name")private String name; /*** 生日*/@TableField(value = "https://www.it610.com/article/birthday")private Date birthday; /*** 薪资*/@TableField(value = "https://www.it610.com/article/salary")private BigDecimal salary; public static final String COL_NAME = "name"; public static final String COL_BIRTHDAY = "birthday"; public static final String COL_SALARY = "salary"; }

3.4 listener
官方文档
EasyExcel读取文件需要用到
ExcelDictDTOListener

/** * 监听 * 再读取数据的同时对数据进行插入操作 * @author : look-word * @date : 2022-05-10 21:35 **/@Slf4j//@AllArgsConstructor //全参@NoArgsConstructor //无参public class ExcelDictDTOListener extends AnalysisEventListener {/*** 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收*/private static final int BATCH_COUNT = 5; List list = new ArrayList(); private StudentMapper studentMapper; //传入mapper对象public ExcelDictDTOListener(StudentMapper studentMapper) {this.studentMapper = studentMapper; }/***遍历每一行的记录* @param data* @param context*/@Overridepublic void invoke(ExcelStudentDTO data, AnalysisContext context) {log.info("解析到一条记录: {}", data); list.add(data); // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (list.size() >= BATCH_COUNT) {saveData(); // 存储完成清理 listlist.clear(); }}/*** 所有数据解析完成了都会来调用*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 这里也要保存数据，确保最后遗留的数据也存储到数据库saveData(); log.info("所有数据解析完成！"); }/*** 加上存储数据库*/private void saveData() {log.info("{}条数据，开始存储数据库！", list.size()); studentMapper.insertBatch(list); //批量插入log.info("存储数据库成功！"); }}

3.5 config
mybatisPlus分页插件
MybatisPlusConfig

@Configurationpublic class MybatisPlusConfig {/*** 新的分页插件,一缓和二缓遵循mybatis的规则,* 需要设置 MybatisConfiguration#useDeprecatedExecutor = false* 避免缓存出现问题(该属性会在旧插件移除后一同移除)*/@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(); paginationInnerInterceptor.setDbType(DbType.MYSQL); paginationInnerInterceptor.setOverflow(true); interceptor.addInnerInterceptor(paginationInnerInterceptor); return interceptor; }@Beanpublic ConfigurationCustomizer configurationCustomizer() {return configuration -> configuration.setUseDeprecatedExecutor(false); }}

3.6 配置文件
application.yaml

server:port: 8811spring:datasource: # mysql数据库连接type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/2022_source_springboot?serverTimezone=GMT%2B8&characterEncoding=utf-8username: rootpassword: 317311mybatis-plus:configuration:# sql日志log-impl: org.apache.ibatis.logging.stdout.StdOutImplmapper-locations:- classpath:mapper/*.xml

4 启动测试启动springboot哦
页面效果图

文章图片

导出效果

文章图片

注意
导入数据时要保证excel中列名和ExcelStudentDTO一致奥
到此这篇关于使用VUE+SpringBoot+EasyExcel 整合导入导出数据的文章就介绍到这了,更多相关vue springboot easyexcel导入导出数据内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！