最近在封装公司统一使用的组件，主要目的是要求封装后开发人员调用简单，不用每个项目组中重复去集成同一个依赖l，写的五花八门，代码不规范，后者两行泪。
为此，我们对EasyExcel进行了二次封装，我会先来介绍下具体使用，然后再给出封装过程
环境准备
开发环境：SpringBoot+mybatis-plus+db
数据库：
-- `dfec-tcht-platform-dev`.test definition
CREATE TABLE `test` (
`num` decimal(10,0) DEFAULT NULL COMMENT '数字',
`sex` varchar(100) DEFAULT NULL COMMENT '性别',
`name` varchar(100) DEFAULT NULL COMMENT '姓名',
`born_date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
使用
第一步、在接口类中引入以下
@Aurowired
ExcelService excelService;
第二步、标注字段
这些个注解是EasyExcel的注解，我们做了保留，仍然使用他的注解
/**
* 【请填写功能名称】对象 test
*
* @author trg
* @date Fri Jan 19 14:14:08 CST 2024
*/
@Data
@TableName("test")
public class TestEntity {
/**
* 数字
*/
@Schema(description = "数字")
@ExcelProperty("数字")
private BigDecimal num;
/**
* 性别
*/
@Schema(description = "性别")
@ExcelProperty("性别")
private String sex;
/**
* 姓名
*/
@Schema(description = "姓名")
@ExcelProperty("姓名")
private String name;
/**
* 创建时间
*/
@Schema(description = "创建时间")
@ExcelProperty(value = "创建时间")
private Date bornDate;
}
第三步、使用
@PostMapping("/importExcel")
public void importExcel(@RequestParam MultipartFile file){
excelService.importExcel(file, TestEntity.class,2,testService::saveBatch);
}
@PostMapping("/exportExcel")
public void exportExcel(HttpServletResponse response) throws IOException {
excelService.exportExcel(testService.list(),TestEntity.class,response);
}
完整代码
package com.dfec.server.controller;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.dfec.framework.excel.service.ExcelService;
import com.dfec.server.entity.TestEntity;
import com.dfec.server.entity.TestVo;
import com.dfec.server.service.TestService;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
import java.util.function.Function;
/**
* @author trg
* @title: TestController
* @projectName df-platform
* @description: TODO
* @date 2023/6/1915:22
*/
@RestController
@RequestMapping("test")
@RequiredArgsConstructor
public class TestController {
private final ExcelService excelService;
private final TestService testService;
@PostMapping("/importExcel")
public void importExcel(@RequestParam MultipartFile file){
excelService.importExcel(file, TestEntity.class,2,testService::saveBatch);
}
@PostMapping("/exportExcel")
public void exportExcel(HttpServletResponse response) throws IOException {
excelService.exportExcel(testService.list(),TestEntity.class,filePath,response);
}
}
哈哈哈，是不是非常简洁
以上只是一个简单的使用情况，我们还封装了支持模板的导入、导出，数据转换等问题，客官请继续向下看。
如果遇到有读取到的数据和实际保存的数据不一致的情况下，可以使用如下方式导入，这里给出一个示例
@PostMapping("/importExcel")
public void importExcel(@RequestParam MultipartFile file){
Function<TestEntity, TestVo> map = new Function<TestEntity, TestVo>() {
@Override
public TestVo apply(TestEntity testEntities) {
TestVo testVo = new TestVo();
testVo.setNum(testEntities.getNum());
testVo.setSex(testEntities.getSex());
testVo.setBaseName(testEntities.getName());
return testVo;
}
};
excelService.importExcel(file, TestEntity.class,2,map,testService::saveBatchTest);
}
封装过程
核心思想：
对导入和导出提供接口、保持最少依赖原则
我们先从ExcelService接口类出发，依次看下封装的几个核心类
package com.dfec.framework.excel.service;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
import java.util.function.Consumer;
import java.util.function.Function;
/**
* ExcelService
*
* @author LiuBin
* @interfaceName ExcelService
* @date 2024/1/16 11:21
**/
public interface ExcelService {
/**
* 导出Excel，默认
* @param list 导出的数据
* @param tClass 带有excel注解的实体类
* @param response 相应
* @return T
* @author trg
* @date 2024/1/15 17:32
*/
<T> void exportExcel(List<T> list, Class<T> tClass, HttpServletResponse response) throws IOException;
/**
* 导出Excel，增加类型转换
* @param list 导出的数据
* @param tClass 带有excel注解的实体类
* @param response 相应
* @return T
* @author trg
* @date 2024/1/15 17:32
*/
<T, R> void exportExcel(List<T> list, Function<T, R> map, Class<R> tClass, HttpServletResponse response) throws IOException;
/**
* 导出Excel，按照模板导出，这里是填充模板
* @param list 导出的数据
* @param tClass 带有excel注解的实体类
* @param template 模板
* @param response 相应
* @return T
* @author trg
* @date 2024/1/15 17:32
*/
<T> void exportExcel(List<T> list, Class<T> tClass, String template, HttpServletResponse response) throws IOException;
/**
* 导入Excel
* @param file 文件
* @param tClass 带有excel注解的实体类
* @param headRowNumber 表格头行数据
* @param map 类型转换
* @param consumer 消费数据的操作
* @return T
* @author trg
* @date 2024/1/15 17:32
*/
<T, R> void importExcel(MultipartFile file, Class<T> tClass, Integer headRowNumber, Function<T, R> map, Consumer<List<R>> consumer);
/**
* 导入Excel
* @param file 文件
* @param tClass 带有excel注解的实体类
* @param headRowNumber 表格头行数据
* @param consumer 消费数据的操作
* @return T
* @author trg
* @date 2024/1/15 17:32
*/
<T> void importExcel(MultipartFile file, Class<T> tClass, Integer headRowNumber, Consumer<List<T>> consumer);
}
以上接口只有个导入、导出，只是加了几个重载方法而已
再看下具体的实现类
package com.dfec.framework.excel.service.impl;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.dfec.framework.excel.convert.LocalDateTimeConverter;
import com.dfec.framework.excel.service.ExcelService;
import com.dfec.framework.excel.util.ExcelUtils;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.stream.Collectors;
/**
* DefaultExcelServiceImpl
*
* @author LiuBin
* @className DefaultExcelServiceImpl
* @date 2024/1/16 11:42
**/
@Service
public class DefaultExcelServiceImpl implements ExcelService {
@Override
public <T> void exportExcel(List<T> list, Class<T> tClass, HttpServletResponse response) throws IOException {
setResponse(response);
EasyExcel.write(response.getOutputStream())
.head(tClass)
.excelType(ExcelTypeEnum.XLSX)
.registerConverter(new LocalDateTimeConverter())
.sheet("工作簿1")
.doWrite(list);
}
@Override
public <T, R> void exportExcel(List<T> list, Function<T, R> map, Class<R> tClass, HttpServletResponse response) throws IOException {
setResponse(response);
List<R> result = list.stream().map(map::apply).collect(Collectors.toList());
exportExcel(result, tClass, response);
}
@Override
public <T> void exportExcel(List<T> list, Class<T> tClass,String template, HttpServletResponse response) throws IOException {
setResponse(response);
EasyExcel.write(response.getOutputStream())
.withTemplate(template)
.excelType(ExcelTypeEnum.XLS)
.useDefaultStyle(false)
.registerConverter(new LocalDateTimeConverter())
.sheet(0)
.doFill(list) ;
}
@Override
public <T,R> void importExcel(MultipartFile file, Class<T> tClass,Integer headRowNumber, Function<T, R> map,Consumer<List<R>> consumer) {
List<T> excelData = ExcelUtils.readExcelData(file,tClass,headRowNumber);
List<R> result = excelData.stream().map(map::apply).collect(Collectors.toList());
consumer.accept(result);
}
@Override
public <T> void importExcel(MultipartFile file, Class<T> tClass,Integer headRowNumber, Consumer<List<T>> consumer) {
List<T> excelData = ExcelUtils.readExcelData(file,tClass,headRowNumber);
consumer.accept(excelData);
}
public void setResponse(HttpServletResponse response) throws UnsupportedEncodingException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("data", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xls");
}
}
ExcelUtils
package com.dfec.framework.excel.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.fastjson.JSON;
import com.dfec.common.exception.ServiceException;
import com.dfec.framework.excel.listener.ExcelListener;
import com.dfec.framework.excel.service.ExcelBaseService;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import java.util.Set;
/**
* @author trg
* @description: Excel 工具类
* @title: ExcelUtils
* @email 1446232546@qq.com
* @date 2023/9/14 9:18
*/
public class ExcelUtils {
/**
* 将列表以 Excel 响应给前端
*
* @param response 响应
* @param fileName 文件名
* @param sheetName Excel sheet 名
* @param head Excel head 头
* @param data 数据列表哦
* @param <T> 泛型，保证 head 和 data 类型的一致性
* @throws IOException 写入失败的情况
*/
public static <T> void excelExport(HttpServletResponse response, String fileName, String sheetName,
Class<T> head, List<T> data) throws IOException {
write(response, fileName);
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream(), head).autoCloseStream(Boolean.FALSE).sheet(sheetName)
.doWrite(data);
}
/**
* 根据模板导出
*
* @param response 响应
* @param templatePath 模板名称
* @param fileName 文件名
* @param sheetName Excel sheet 名
* @param head Excel head 头
* @param data 数据列表哦
* @param <T> 泛型，保证 head 和 data 类型的一致性
* @throws IOException 写入失败的情况
*/
public static <T> void excelExport(HttpServletResponse response, String templatePath, String fileName, String sheetName,
Class<T> head, List<T> data) throws IOException {
write(response, fileName);
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream(), head).withTemplate(templatePath).autoCloseStream(Boolean.FALSE).sheet(sheetName)
.doWrite(data);
}
/**
* 根据参数，只导出指定列
*
* @param response 响应
* @param fileName 文件名
* @param sheetName Excel sheet 名
* @param head Excel head 头
* @param data 数据列表哦
* @param excludeColumnFiledNames 排除的列
* @param <T> 泛型，保证 head 和 data 类型的一致性
* @throws IOException 写入失败的情况
*/
public static <T> void excelExport(HttpServletResponse response, String fileName, String sheetName,
Class<T> head, List<T> data, Set<String> excludeColumnFiledNames) throws IOException {
write(response, fileName);
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream(), head).autoCloseStream(Boolean.FALSE).excludeColumnFiledNames(excludeColumnFiledNames).sheet(sheetName)
.doWrite(data);
}
private static void write(HttpServletResponse response, String fileName) {
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = MapUtils.newHashMap();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
try {
response.getWriter().println(JSON.toJSONString(map));
} catch (IOException ex) {
throw new RuntimeException(ex);
}
}
}
public static <T> List<T> read(MultipartFile file, Class<T> head) throws IOException {
return EasyExcel.read(file.getInputStream(), head, null)
// 不要自动关闭，交给 Servlet 自己处理
.autoCloseStream(false)
.doReadAllSync();
}
/**
* 读取 Excel(多个 sheet)
*
* @param excel 文件
* @param rowModel 实体类映射
* @return Excel 数据 list
*/
public static <T> List<T> readExcelData(MultipartFile excel, Class<T> rowModel, Integer headRowNumber) {
ExcelListener excelListener = new ExcelListener();
ExcelReaderBuilder readerBuilder = getReader(excel, excelListener);
if (readerBuilder == null) {
return null;
}
if (headRowNumber == null) {
headRowNumber = 1;
}
readerBuilder.head(rowModel).headRowNumber(headRowNumber).doReadAll();
return excelListener.getData();
}
/**
* 读取 Excel(多个 sheet)
*
* @param excel 文件
* @param rowModel 实体类映射
* @return Excel 数据 list
*/
public static <T> List<T> excelImport(MultipartFile excel, ExcelBaseService excelBaseService, Class rowModel) {
ExcelListener excelListener = new ExcelListener(excelBaseService);
ExcelReaderBuilder readerBuilder = getReader(excel, excelListener);
if (readerBuilder == null) {
return null;
}
readerBuilder.head(rowModel).doReadAll();
return excelListener.getData();
}
/**
* 读取某个 sheet 的 Excel
*
* @param excel 文件
* @param rowModel 实体类映射
* @param sheetNo sheet 的序号 从1开始
* @param headLineNum 表头行数，默认为1
* @return Excel 数据 list
*/
public static <T> List<T> excelImport(MultipartFile excel, ExcelBaseService excelBaseService, Class rowModel, int sheetNo,
Integer headLineNum) {
ExcelListener excelListener = new ExcelListener(excelBaseService);
ExcelReaderBuilder readerBuilder = getReader(excel, excelListener);
if (readerBuilder == null) {
return null;
}
ExcelReader reader = readerBuilder.headRowNumber(headLineNum).build();
ReadSheet readSheet = EasyExcel.readSheet(sheetNo).head(rowModel).build();
reader.read(readSheet);
return excelListener.getData();
}
/**
* 返回 ExcelReader
*
* @param excel 需要解析的 Excel 文件
* @param excelListener 监听器
*/
private static ExcelReaderBuilder getReader(MultipartFile excel,
ExcelListener excelListener) {
String filename = excel.getOriginalFilename();
if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
throw new ServiceException("文件格式错误！");
}
InputStream inputStream;
try {
inputStream = new BufferedInputStream(excel.getInputStream());
return EasyExcel.read(inputStream, excelListener);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}
ExcelListener.java
package com.dfec.framework.excel.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.dfec.framework.excel.service.ExcelBaseService;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* @author trg
* @description: Excel导入的监听类
* @title: ExcelListener
* @projectName df-platform
* @email 1446232546@qq.com
* @date 2023/9/14 16:23
*/
@Slf4j
public class ExcelListener<T> extends AnalysisEventListener<T> {
private ExcelBaseService excelBaseService;
public ExcelListener(){}
public ExcelListener(ExcelBaseService excelBaseService){
this.excelBaseService = excelBaseService;
}
/**
* 每隔1000条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 1000;
List<T> list = new ArrayList<>();
@Override
public void invoke(T data, AnalysisContext context) {
list.add(data);
log.info("解析到一条数据:{}", JSON.toJSONString(data));
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("所有数据解析完成！");
}
/**
* 返回list
*/
public List<T> getData() {
return this.list;
}
}
遇到的问题
1、通过模板导出数据作为导入数据再导入进来，日期格式不正确
解决方法：
package com.dfec.server;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.dfec.common.utils.str.StringUtils;
import java.util.Date;
/**
* DateConverter
*
* @author trg
* @className DateConverter
* @date 2024/1/25 16:09
**/
public class DateConverter implements Converter<Date> {
@Override
public Date convertToJavaData(ReadConverterContext<?> context) throws Exception {
Class<?> aClass = context.getContentProperty().getField().getType();
CellDataTypeEnum type = context.getReadCellData().getType();
String stringValue = context.getReadCellData().getStringValue();
if(aClass.equals(Date.class) && type.equals(CellDataTypeEnum.STRING) && StringUtils.isBlank(stringValue)){
return null;
}
return Converter.super.convertToJavaData(context);
}
}
实体类上添加
/**
* 创建时间
*/
@Schema(description = "创建时间")
@ExcelProperty(value = "创建时间",converter = DateConverter.class)
private Date bornDate;
同理，这块
注意这里也是可以用相同的方法去做字典值类型的转换的，可以参考下芋道源码的DictConvert.java
2、POI版本
这里切记POI版本和ooxml的版本一堆要保持一致，不然会出现各种问题
3、日期类型 LocalDateTime 转换的问题
package com.dfec.framework.excel.convert;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
/**
* 解决 EasyExcel 日期类型 LocalDateTime 转换的问题
*/
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
@Override
public Class<LocalDateTime> supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDateTime convertToJavaData(ReadCellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
}
@Override
public WriteCellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return new WriteCellData(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
}
}
遗留问题
目前我们使用的这个EasyExcel版本是3.3.2，但是发现，导出的时候按照模板去导出文件数据的话只能支持xls，xlsx的不支持，目前还未有解决方案，有遇到的朋友还望不吝赐教
参照：
EasyExcel官方文档；
easyexcel.opensource.alibaba.com/docs/current/
点赞+在看，Bug少一半！
