SpringBoot+Easyexcel实现导入导出
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.11.0</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!--druid-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.16</version>
</dependency>
<!--mybatisplus依赖-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<!--代码生成器:模板引擎Freemarker依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-freemarker</artifactId>
</dependency>
<!--代码生成器-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- hutool工具包 -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
</dependency>
<!-- 导入easyexcel依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<!-- FastJson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.58</version>
</dependency>
<!--swagger-->
<dependency>
<groupId>com.github.xiaoymin</groupId>
<artifactId>knife4j-spring-boot-starter</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-extension</artifactId>
<version>3.4.3.4</version>
</dependency>
</dependencies>
常用注解:
-
@ExcelProperty:主要用于获取excel表头对应的列的数据
- value:表头名,例如:value = {“出库单”,“序号”},或value = “序号”,复杂表头使用第一种方式,简单表头使用第二种方式,value的值可以接收一个数组类型的数据
- index:表头名的索引,从0开始,例如:index=2
-
@ColumnWidth:设置列的宽度
- value: 指定列的宽度,例如:value = 15
-
@DateTimeFormat:格式化日期格式
- value:指定要格式化的日期格式,例如:value=“yyyy年MM月dd日”,value="yyyy-MM-dd等
导入
简单导入
excel表结构
编写导入实体
@Data
public class PersonImportExcel {
@ExcelProperty(value = "序号",index = 0)
private Long id;
@ExcelProperty(value = "姓名",index = 1)
private String name;
@ExcelProperty(value = "性别",index = 2, converter = SexConverter.class)
private Integer sex;
@ExcelProperty(value = "年龄",index = 3)
private Integer age;
@ExcelProperty(value = "时代",index = 4)
private String era;
@ExcelProperty(value = "电话",index = 5)
private String phone;
@ExcelProperty(value = "职业",index = 6)
private String career ;
}
由于数据库将性别存储的数据类型为int,而excel存储的是String,所以需要做类型转换,只需在 @ExcelProperty注解中添加converter属性,在编写有一个类型转换的类SexConverter即可,代码如下
public class SexConverter implements Converter<Integer> {
public static final String FEMALE = "女";
public static final String MALE = "男";
public static final Integer FEMALE_VALUE = 1;
public static final Integer MALE_VALUE = 2;
/**
* 将 Excel 展示的数据 转换为 数据库中存储的数据
*
* @param cellData 单元格值
*/
@Override
public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
if (FEMALE.equals(cellData.getStringValue())) {
return FEMALE_VALUE;
} else if (MALE.equals(cellData.getStringValue())) {
return MALE_VALUE;
} else {
throw new CustomException("性别字段值必须为男或女",20);
}
}
/**
* 将从数据库中查到的数据转换为 Excel 展示的数据
*
* @param value 枚举值
*/
@Override
public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
if (FEMALE_VALUE.equals(value)) {
return new WriteCellData<>(FEMALE);
} else if (MALE_VALUE.equals(value)) {
return new WriteCellData<>(MALE);
} else {
throw new CustomException("性别字段值必须为男或女",20);
}
}
}
数据库对应实体:
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="Person对象", description="人物表")
public class Person implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "主键")
@TableId(value = "id", type = IdType.AUTO)
private Long id;
@ApiModelProperty(value = "姓名")
private String name;
@ApiModelProperty(value = "性别,1男,2女")
private Integer sex;
@ApiModelProperty(value = "年龄")
private Integer age;
@ApiModelProperty(value = "时代")
private String era;
@ApiModelProperty(value = "电话")
private String phone;
@ApiModelProperty(value = "职业")
private String career;
}
导入关键代码
public void importPerson(MultipartFile file) throws IOException {
if (file == null){
throw new CustomException(ResultCodeEnum.UPLOAD_FILE_NOT_BLANK);
}
EasyExcel.read(file.getInputStream(), PersonImportExcel.class, new PageReadListener<PersonImportExcel>(dataList -> {
for (PersonImportExcel personImportExcel : dataList) {
log.info("读取到一条数据{}", personImportExcel);
}
List<Person> personList = new ArrayList<>();
dataList.forEach(personImportExcel -> {
if(null != personImportExcel){
Person person = new Person();
BeanUtils.copyProperties(personImportExcel,person);
personList.add(person);
}
});
log.info("personList={}",personList);
this.saveOrUpdateBatch(personList);
})).sheet().doRead();
}
效果:
复杂导入
excel表结构
编写导入实体
/**
* 导入相关实体
*/
@Data
public class InventoryRecordImportExcel {
@ExcelProperty(value = "序号",index = 0)
private Long recordId;
@ExcelProperty(value = "样品编号",index = 1)
private String sampleNo;
@ExcelProperty(value = "调查船",index = 2)
private String shipName;
@ExcelProperty(value = "航次",index = 3)
private String voyageNo;
@ExcelProperty(value = {"详细位置", "海域"},index = 4)
private String seaArea;
@ExcelProperty(value = {"详细位置","站位"},index = 5)
private String station;
@ExcelProperty(value = {"详细位置","X坐标"},index = 6)
private String xCoordinate;
@ExcelProperty(value = {"详细位置","Y坐标"},index = 7)
private String yCoordinate;
@ExcelProperty(value = {"详细位置","详细地址"},index = 8)
private String detailAddress;
@ExcelProperty(value = "结束深度(m)",index = 9)
private Double endDepth;
@ExcelProperty(value = "心长(m)",index = 10)
private Double heartLength;
@ExcelProperty(value = "存放位置",index = 11)
private String storageLocation;
@ExcelProperty(value = "保存状况",index = 12)
private Double preservationCondition;
@ExcelProperty(value = "备注",index = 13)
private String remarks;
}
注意:复杂导入对应的字段使用@ExcelProperty注解时,value值必须使用
{}
,括号中写excel中复杂的表头内容,例如:@ExcelProperty(value = {“详细位置”,“Y坐标”},index = 7),index指的是表头的索引,从0开始,0表示第A列,以此类推。
数据库对应实体:
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="Record对象", description="")
public class Record implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "主键")
@TableId(type = IdType.AUTO)
private Long recordId;
@ApiModelProperty(value = "样品编号")
private String sampleNo;
@ApiModelProperty(value = "调查船")
private String shipName;
@ApiModelProperty(value = "航次")
private String voyageNo;
@ApiModelProperty(value = "结束深度")
private Double endDepth;
@ApiModelProperty(value = "心长")
private Double heartLength;
@ApiModelProperty(value = "存放位置")
private String storageLocation;
@ApiModelProperty(value = "保存状况")
private Double preservationCondition;
@ApiModelProperty(value = "备注")
private String remark;
}
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="Seat对象", description="")
public class Seat implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "主键")
@TableId(type = IdType.AUTO)
private Long seatId;
@ApiModelProperty(value = "记录id")
private Long recordId;
@ApiModelProperty(value = "海域")
private String seaArea;
@ApiModelProperty(value = "站位")
private String station;
@ApiModelProperty(value = "X坐标")
private String xCoordinate;
@ApiModelProperty(value = "Y坐标")
private String yCoordinate;
@ApiModelProperty(value = "详细地址")
private String detailAddress;
}
导入关键代码
/**
* 导入
* @param file
* @throws IOException
*/
@Transactional(rollbackFor = Exception.class)
@Override
public void importRecord(MultipartFile file) throws IOException {
if (file.isEmpty()){
throw new CustomException(ResultCodeEnum.UPLOAD_FILE_NOT_BLANK);
}
EasyExcel.read(file.getInputStream(), InventoryRecordImportExcel.class, new PageReadListener<InventoryRecordImportExcel>(dataList -> {
for (InventoryRecordImportExcel recordImportExcel : dataList) {
log.info("读取到一条数据{}", recordImportExcel);
}
List<Record> recordList = new ArrayList<>();
List<Seat> seatList = new ArrayList<>();
dataList.forEach(recordImportExcel -> {
if(null != recordImportExcel){
Record record = new Record();
Seat seat = new Seat();
if (recordImportExcel.getRecordId() != null){
BeanUtils.copyProperties(recordImportExcel,record);
recordList.add(record);
BeanUtils.copyProperties(recordImportExcel,seat);
seat.setSeatId(record.getRecordId());
seatList.add(seat);
}
}
});
log.info("recordList={}",recordList);
log.info("seatList={}",seatList);
this.saveOrUpdateBatch(recordList);
seatService.saveOrUpdateBatch(seatList);
})).sheet().doRead();
}
最终导入数据库的效果:
导出
简单导出
编写导出实体,与导入复杂实体类似
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class PersonExportExcel implements Serializable {
private static final long serialVersionUID = 1L;
@ExcelProperty(value = "序号",index = 0)
private Long id;
@ExcelProperty(value = "姓名",index = 1)
private String name;
@ExcelProperty(value = "性别",index = 2, converter = SexConverter.class)
private Integer sex;
@ExcelProperty(value = "年龄",index = 3)
private Integer age;
@ExcelProperty(value = "时代",index = 4)
private String era;
@ExcelProperty(value = "电话",index = 5)
private String phone;
@ExcelProperty(value = "职业",index = 6)
private String career ;
}
导出关键代码
public void exportPerson(HttpServletResponse response) throws IOException {
//查询所有的人物信息
List<Person> personList = this.list();
List<PersonExportExcel> recordRepsVOList = new ArrayList<>();
for (Person person : personList) {
PersonExportExcel personExportExcel = new PersonExportExcel();
BeanUtils.copyProperties(person,personExportExcel);
recordRepsVOList.add(personExportExcel);
}
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("人物信息.xlsx", "UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
EasyExcel.write(response.getOutputStream(), PersonExportExcel.class).autoCloseStream(Boolean.FALSE).sheet("sheet1")
.doWrite(recordRepsVOList);
}
最终导出的效果:
复杂导出
编写导出实体,与导入复杂实体类似
@Data
public class InventoryRecordExportExcel {
@ExcelProperty(value = {"出库单","序号"},index = 0)
private Long recordId;
@ExcelProperty(value = {"出库单","样品编号"},index = 1)
private String sampleNo;
@ExcelProperty(value = {"出库单","调查船"},index = 2)
private String shipName;
@ExcelProperty(value = {"出库单","航次"},index = 3)
private String voyageNo;
@ExcelProperty(value = {"出库单","详细位置", "海域"},index = 4)
private String seaArea;
@ExcelProperty(value = {"出库单","详细位置","站位"},index = 5)
private String station;
@ExcelProperty(value = {"出库单","详细位置","X坐标"},index = 6)
private String xCoordinate;
@ExcelProperty(value = {"出库单","详细位置","Y坐标"},index = 7)
private String yCoordinate;
@ExcelProperty(value = {"出库单","详细位置","详细地址"},index = 8)
private String detailAddress;
@ExcelProperty(value = {"出库单","结束深度(m)"},index = 9)
private Double endDepth;
@ExcelProperty(value = {"出库单","心长(m)"},index = 10)
private Double heartLength;
@ExcelProperty(value = {"出库单","存放位置"},index = 11)
private String storageLocation;
@ExcelProperty(value = {"出库单","保存状况"},index = 12)
private Double preservationCondition;
@ExcelProperty(value = {"出库单","备注"},index = 13)
private String remarks;
}
导出关键代码
/**
* 导出样本数据
* @param servletResponse
*/
@Override
public void exportRecordToExcel(HttpServletResponse servletResponse) throws IOException {
//查询所有的记录信息
List<Record> recordList = this.list();
//查询所有的位置信息
List<Seat> seatList = seatService.list();
List<InventoryRecordExportExcel> recordRepsVOList = new ArrayList<>();
for (Record record : recordList) {
InventoryRecordExportExcel recordExportExcel = null;
for (Seat seat : seatList) {
if (record.getRecordId().equals(seat.getRecordId())) {
recordExportExcel = new InventoryRecordExportExcel();
BeanUtils.copyProperties(record, recordExportExcel);
recordExportExcel.setSeaArea(seat.getSeaArea());
recordExportExcel.setStation(seat.getStation());
recordExportExcel.setXCoordinate(seat.getXCoordinate());
recordExportExcel.setYCoordinate(seat.getYCoordinate());
recordExportExcel.setDetailAddress(seat.getDetailAddress());
recordRepsVOList.add(recordExportExcel);
}
}
}
servletResponse.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("样本数据.xlsx", "UTF-8"));
servletResponse.setContentType("application/vnd.ms-excel;charset=UTF-8");
EasyExcel.write(servletResponse.getOutputStream(), InventoryRecordExportExcel.class).autoCloseStream(Boolean.FALSE).sheet("sheet1")
.doWrite(recordRepsVOList);
}
最终导出的效果:
读excel表中全部的sheet表
excel表
编写导出实体
@Data
public class InventoryRecordImportExcel {
@ExcelProperty(value = "序号",index = 0)
private Long recordId;
@ExcelProperty(value = "样品编号",index = 1)
private String sampleNo;
@ExcelProperty(value = "调查船",index = 2)
private String shipName;
@ExcelProperty(value = "航次",index = 3)
private String voyageNo;
@ExcelProperty(value = {"详细位置", "海域"},index = 4)
private String seaArea;
@ExcelProperty(value = {"详细位置","站位"},index = 5)
private String station;
@ExcelProperty(value = {"详细位置","X坐标"},index = 6)
private String xCoordinate;
@ExcelProperty(value = {"详细位置","Y坐标"},index = 7)
private String yCoordinate;
@ExcelProperty(value = {"详细位置","详细地址"},index = 8)
private String detailAddress;
@ExcelProperty(value = "结束深度(m)",index = 9)
private Double endDepth;
@ExcelProperty(value = "心长(m)",index = 10)
private Double heartLength;
@ExcelProperty(value = "存放位置",index = 11)
private String storageLocation;
@ExcelProperty(value = "保存状况",index = 12)
private Double preservationCondition;
@ExcelProperty(value = "备注",index = 13)
private String remarks;
}
编写与数据库表对应的实体:
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="Record对象", description="")
public class Record implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "主键")
@TableId(type = IdType.AUTO)
private Long recordId;
@ApiModelProperty(value = "样品编号")
private String sampleNo;
@ApiModelProperty(value = "调查船")
private String shipName;
@ApiModelProperty(value = "航次")
private String voyageNo;
@ApiModelProperty(value = "结束深度")
private Double endDepth;
@ApiModelProperty(value = "心长")
private Double heartLength;
@ApiModelProperty(value = "存放位置")
private String storageLocation;
@ApiModelProperty(value = "保存状况")
private Double preservationCondition;
@ApiModelProperty(value = "备注")
private String remark;
}
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="Seat对象", description="")
public class Seat implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "主键")
@TableId(type = IdType.AUTO)
private Long seatId;
@ApiModelProperty(value = "记录id")
private Long recordId;
@ApiModelProperty(value = "海域")
private String seaArea;
@ApiModelProperty(value = "站位")
private String station;
@ApiModelProperty(value = "X坐标")
private String xCoordinate;
@ApiModelProperty(value = "Y坐标")
private String yCoordinate;
@ApiModelProperty(value = "详细地址")
private String detailAddress;
}
创建监听器
public class MoreSheetListener implements ReadListener<InventoryRecordImportExcel> {
private List<InventoryRecordImportExcel> dataList = new ArrayList<>();
@Override
public void invoke(InventoryRecordImportExcel data, AnalysisContext context) {
dataList.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 所有数据解析完成后的操作
}
public List<InventoryRecordImportExcel> getDataList() {
return dataList;
}
}
读取全部数据并导入数据库
@Transactional(rollbackFor = Exception.class)
public void importMoreSheetRecord(MultipartFile file) throws IOException {
MoreSheetListener listener = new MoreSheetListener();
ExcelReaderBuilder excelReaderBuilder = EasyExcel.read(file.getInputStream(), InventoryRecordImportExcel.class, listener);
excelReaderBuilder.doReadAll();
// 获取所有 sheet 的数据
List<InventoryRecordImportExcel> allData = listener.getDataList();
//allData.forEach(System.out::println);
List<Record> recordList = new ArrayList<>();
List<Seat> seatList = new ArrayList<>();
allData.forEach(recordImportExcel ->{
if(null != recordImportExcel){
Record record = new Record();
Seat seat = new Seat();
if (recordImportExcel.getRecordId() != null){
BeanUtils.copyProperties(recordImportExcel,record);
recordList.add(record);
BeanUtils.copyProperties(recordImportExcel,seat);
seat.setSeatId(record.getRecordId());
seatList.add(seat);
}
}
});
this.saveOrUpdateBatch(recordList);
seatService.saveOrUpdateBatch(seatList);
}
效果
本文的代码案例地址:https://github.com/rookiesnewbie/ImprotAndExport