最近在封装公司统一使用的组件，主要目的是要求封装后开发人员调用简单，不用每个项目组中重复去集成同一个依赖l，写的五花八门，代码不规范，后者两行泪。
为此，我们对EasyExcel进行了二次封装，我会先来介绍下具体使用，然后再给出封装过程
环境准备
开发环境：SpringBoot+mybatis-plus+db
数据库：
-- `dfec-tcht-platform-dev`.test definitionCREATE 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**/
@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);
}
完整代码
/*** @title: TestController* @projectName df-platform* @description: TODO*/@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>() {@Overridepublic 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接口类出发，依次看下封装的几个核心类
/*** ExcelService** @interfaceName ExcelService**/
public interface ExcelService {/*** 导出Excel，默认* @param list 导出的数据* @param tClass 带有excel注解的实体类* @param response 相应* @return T*/<T> void exportExcel(List<T> list, Class<T> tClass, HttpServletResponse response) throws IOException;/*** 导出Excel，增加类型转换* @param list 导出的数据* @param tClass 带有excel注解的实体类* @param response 相应* @return T*/<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*/<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*/<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*/<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** @className DefaultExcelServiceImpl**/
@Service
public class DefaultExcelServiceImpl implements ExcelService {@Overridepublic <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);}@Overridepublic <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);}@Overridepublic <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) ;}@Overridepublic <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);}@Overridepublic <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;/*** @description: Excel 工具类* @title: ExcelUtils*/
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) {// 重置responseresponse.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;/*** @description: Excel导入的监听类* @title: ExcelListener* @projectName df-platform*/
@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<>();@Overridepublic void invoke(T data, AnalysisContext context) {list.add(data);log.info("解析到一条数据:{}", JSON.toJSONString(data));}@Overridepublic 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** @className DateConverter**/
public class DateConverter implements Converter<Date> {@Overridepublic 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;
同理，这块
注意这里也是可以用相同的方法去做字典值类型的转换的
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> {@Overridepublic Class<LocalDateTime> supportJavaTypeKey() {return LocalDateTime.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic LocalDateTime convertToJavaData(ReadCellData cellData, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) {return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));}@Overridepublic WriteCellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) {return new WriteCellData(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));}}
