SpringBoot整合EasyExcel实现导入导出数据

SpringBoot整合EasyExcel实现导入导出数据

目录前言1.前端2.数据库3.后端3.1 contrller3.2 mapper3.3 bean3.4 listener3.5 config3.6 配置文件4.启动测试

前言

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

项目目录结构

1.前端

存放在resources/static 下

index.html

rel="stylesheet"

href="https://unpkg.com/element-ui/lib/theme-chalk/index.css" rel="external nofollow"

/>

@click="dialogVisible = true"

type="primary"

size="mini"

icon="el-icon-download"

>

导入Excel

@click="dialogVisible = true"

type="primary"

size="mini"

icon="el-icon-download"

>

导入Excel

title="数据字典导入"

:visible.sync="dialogVisible"

width="30%"

>

:auto-upload="true"

:multiple="false"

:limit="1"

:on-exceed="fileUploadExceed"

:on-success="fileUploadSuccess"

:on-error="fileUploadError"

:action="importUrl"

name="file"

accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

>

点击上传

title="数据字典导入"

:visible.sync="dialogVisible"

width="30%"

>

:auto-upload="true"

:multiple="false"

:limit="1"

:on-exceed="fileUploadExceed"

:on-success="fileUploadSuccess"

:on-error="fileUploadError"

:action="importUrl"

name="file"

accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

>

点击上传

:auto-upload="true"

:multiple="false"

:limit="1"

:on-exceed="fileUploadExceed"

:on-success="fileUploadSuccess"

:on-error="fileUploadError"

:action="importUrl"

name="file"

accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

>

点击上传

取消

@click="exportData"

type="primary"

size="mini"

icon="el-icon-upload2"

>

导出Excel

@click="exportData"

type="primary"

size="mini"

icon="el-icon-upload2"

>

导出Excel

@size-change="handleSizeChange"

@current-change="handleCurrentChange"

:current-page="pageNum"

:page-sizes="[2, 5, 10, 20]"

:page-size="pageSize"

background

layout="total, sizes, prev, pager, next, jumper"

:total="total"

>

@size-change="handleSizeChange"

@current-change="handleCurrentChange"

:current-page="pageNum"

:page-sizes="[2, 5, 10, 20]"

:page-size="pageSize"

background

layout="total, sizes, prev, pager, next, jumper"

:total="total"

>

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 {

@Resource

private 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 "上传成功";

}

}

3.2 mapper

StudentMapper

@Mapper

public 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

*/

@Data

public class ExcelStudentDTO {

// excel中的列名

@ExcelProperty("姓名")

private String name;

@ExcelProperty("生日")

private Date birthday;

@ExcelProperty("薪资")

private BigDecimal salary;

}

Student

/**

* 数据库对应的实体类

* @author jiejie

*/

@Data

@TableName(value = "student")

public class Student {

/**

* 姓名

*/

@TableField(value = "name")

private String name;

/**

* 生日

*/

@TableField(value = "birthday")

private Date birthday;

/**

* 薪资

*/

@TableField(value = "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

*/

@Override

public void invoke(ExcelStudentDTO data, AnalysisContext context) {

log.info("解析到一条记录: {}", data);

list.add(data);

// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM

if (list.size() >= BATCH_COUNT) {

saveData();

// 存储完成清理 list

list.clear();

}

}

/**

* 所有数据解析完成了 都会来调用

*/

@Override

public void doAfterAllAnalysed(AnalysisContext context) {

// 这里也要保存数据，确保最后遗留的数据也存储到数据库

saveData();

log.info("所有数据解析完成！");

}

/**

* 加上存储数据库

*/

private void saveData() {

log.info("{}条数据，开始存储数据库！", list.size());

studentMapper.insertBatch(list); //批量插入

log.info("存储数据库成功！");

}

}

3.5 config

mybatisPlus分页插件

MybatisPlusConfig

@Configuration

public class MybatisPlusConfig {

/**

* 新的分页插件,一缓和二缓遵循mybatis的规则,

* 需要设置 MybatisConfiguration#useDeprecatedExecutor = false

* 避免缓存出现问题(该属性会在旧插件移除后一同移除)

*/

@Bean

public MybatisPlusInterceptor mybatisPlusInterceptor() {

MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();

paginationInnerInterceptor.setDbTypeODQERiH(DbType.mysql);

paginationInnerInterceptor.setOverflow(true);

interceptor.addInnerInterceptor(paginationInnerInterceptor);

return interceptor;

}

@Bean

public ConfigurationCustomizer configurationCustomizer() {

return configuration -> configuration.setUseDeprecatedExecutor(false);

}

}

3.6 配置文件

application.yaml

server:

port: 8811

spring:

datasource: # mysql数据库连接

type: com.zaxxer.hikari.HikariDataSource

driver-class-name: com.mysql.cj.jdbc.Driver

url: jdbc:mysql://localhost:3306/2022_source_springboot?serverTimezone=GMT%2B8&characterEncoding=utf-8

username: root

password: 317311

mybatis-plus:

configuration:# sql日志

log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

mapper-locations:

- classpath:mapper/*.xml

4.启动测试

启动springboot哦

页面效果图

导出效果

注意

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

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