利用EasyExecl对execl进行导入导出
EasyExecl官方文档: https://www.yuque.com/easyexcel/doc/easyexcel
一、前言
这是我写的一个EasyExecl对execl进行导入导出的一个demo，他不像原生的Execl导入导出那样，它实现的代码比较简洁，它本身的类封装了导入导出的方法。
1.1 以下网址是鄙人的github仓库，存放着自己的技术代码demo，如有错误，还请各位大佬指正！！！
该项目完整代码地址: https://github.com/Java567/TechnologyDemo
二、准备工作
- 编译器：idea 2019.3.3版本
- 数据库：mysql---8的版本
- 插件：lombok(简化实体类的编写)
2.1 导入的execl表和sql表的模板
2.2 所需的pom依赖
2.2.1 swagger2的依赖（后端生成api接口文档）
<!--集成API接口文档的生成swagger2-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
2.2.2 lombok的依赖
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
2.2.3 easypoi依赖
<!--Easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
三、代码实现
3.1 实体类编写
package com.lj.model;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonProperty;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import java.io.Serializable;
import java.util.Date;
/**
* @description: 报告厅基本信息实体类
* @author: LiJun
* @date: Created in 2021/2/15 20:42
*/
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(value = "Auditorium", description = "报告厅模型")
public class Auditorium implements Serializable {
@ExcelIgnore
@ApiModelProperty("报告厅主键")
private Integer auditoriumId;
@ExcelProperty("报告厅名称")
@ApiModelProperty("报告厅名称")
private String auditoriumName;
@ExcelProperty("正在使用者")
@ApiModelProperty("正在使用者")
private String username;
@ExcelProperty("所属楼层编号")
@ApiModelProperty("所属楼层编号")
private Integer floorId;
@ExcelProperty("所属楼栋编号")
@ApiModelProperty("所属楼栋编号")
private Integer buildId;
@ExcelProperty("所属校区编号")
@ApiModelProperty("所属校区编号")
private Integer campusId;
@ExcelProperty("报告厅面积")
@ApiModelProperty("报告厅面积")
private Integer auditoriumArea;
@ExcelProperty("可容纳人数")
@ApiModelProperty("可容纳人数")
private Integer auditoriumCapacity;
@ExcelProperty("状态(是否空闲)")
@ApiModelProperty("状态(是否空闲)")
private String auditoriumState;
@ExcelProperty("详情介绍")
@ApiModelProperty("详情介绍")
private String auditoriumDetail;
@ExcelProperty("报告厅添加时间")
@ApiModelProperty("报告厅添加时间")
@JsonProperty("auditorium_create_time")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GTM+8")
private Date auditoriumCreateTime;
}
3.2 持久层编写
3.2.1 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.lj.dao.AuditoriumDAO">
<select id="getAllAuditorium" resultType="Auditorium">
SELECT * FROM `Auditorium`;
</select>
<insert id="save" parameterType="java.util.List">
INSERT INTO `Auditorium`
(`auditorium_name`, `username`, `floor_id`, `build_id`, `campus_id`, `auditorium_area`,
`auditorium_capacity`, `auditorium_state`, `auditorium_detail`)
VALUES
<foreach collection="list" separator="," item="item" close=";">
(#{item.auditoriumName}, #{item.username}, #{item.floorId}, #{item.buildId}, #{item.campusId},
#{item.auditoriumArea},#{item.auditoriumCapacity}, #{item.auditoriumState}, #{item.auditoriumDetail})
</foreach>
</insert>
</mapper>
3.2.2 dao层接口编写
package com.lj.dao;
import com.lj.model.Auditorium;
import org.springframework.stereotype.Repository;
import java.util.List;
/**
* @description:
* @author: LiJun
* @date: Created in 2021/2/16 20:04
*/
@Repository
public interface AuditoriumDAO {
/**
* 得到表信息
*/
List<Auditorium> getAllAuditorium();
/**
* 保存至数据库
*/
void save(List<Auditorium> list);
}
3.3 业务层编写（简单，省略不写，项目完整代码见github代码仓库网址）
3.4 控制层编写
package com.lj.controller;
import com.alibaba.excel.EasyExcel;
import com.lj.common.config.AuditoriumDataListener;
import com.lj.model.Auditorium;
import com.lj.service.AuditoriumService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
/**
* @description:
* @author: LiJun
* @date: Created in 2021/2/16 20:04
*/
@RestController
@RequestMapping("Auditorium")
@Api(tags = "Auditorium报告厅相关的API接口")
public class AuditoriumController {
@Resource
private AuditoriumService auditoriumService;
/**
* 数据导入
*/
@ResponseBody
@PostMapping("/loadIn")
@ApiOperation(value="execl数据导入接口", notes = "execl数据导入到对应的报告厅数据库表")
public void loadIn(@RequestParam("file") MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(),
Auditorium.class,
new AuditoriumDataListener(auditoriumService)).sheet().doRead();
}
/**
* 导出house信息，需要用浏览器测试,"application/vnd.ms-excel;charset=utf-8"
* @param response
* @return
* @throws IOException
*/
@ResponseBody
@GetMapping("/loadOut")
@ApiOperation(value="execl导出Auditorium接口", notes = "导出Auditorium表里的数据并生成可以下载的execl表")
public Integer loadOut(HttpServletResponse response) throws IOException{
List<Auditorium> list = auditoriumService.getAllAuditorium();
String fileName = new String("Auditorium.xlsx".getBytes("utf-8"), "ISO-8859-1");
if (list.isEmpty()){
return 0;
} else {
// 请求头
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.flushBuffer();
response.setCharacterEncoding("UTF-8");
//导出execl
EasyExcel.write(response.getOutputStream(),Auditorium.class).sheet("Auditorium").doWrite(list);
return 1;
}
}
}
四、运行结果
4.1 项目启动后访问地址
http://localhost:8080/swagger-ui.html