最近在封装公司统一使用的组件,主要目的是要求封装后开发人员调用简单,不用每个项目组中重复去集成同一个依赖l,写的五花八门,代码不规范,后者两行泪。
为此,我们对EasyExcel进行了二次封装,我会先来介绍下具体使用,然后再给出封装过程
环境准备
开发环境:SpringBoot+mybatis-plus+db
数据库:
sql 复制代码 -- `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;
使用
第一步、在接口类中引入以下
less 复制代码 @Aurowired ExcelService excelService;
第二步、标注字段
这些个注解是EasyExcel的注解,我们做了保留,仍然使用他的注解
less 复制代码/** * 【请填写功能名称】对象 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; }
第三步、使用
less 复制代码@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); }
完整代码
java 复制代码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); } }
哈哈哈,是不是非常简洁
以上只是一个简单的使用情况,我们还封装了支持模板的导入、导出,数据转换等问题,客官请继续向下看。
如果遇到有读取到的数据和实际保存的数据不一致的情况下,可以使用如下方式导入,这里给出一个示例
java 复制代码 @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接口类出发,依次看下封装的几个核心类
java 复制代码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); }
以上接口只有个导入、导出,只是加了几个重载方法而已
再看下具体的实现类
java 复制代码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
java 复制代码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
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、通过模板导出数据作为导入数据再导入进来,日期格式不正确
解决方法:
java 复制代码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); } }
实体类上添加
java 复制代码 /** * 创建时间 */ @Schema(description = "创建时间") @ExcelProperty(value = "创建时间",converter = DateConverter.class) private Date bornDate;
同理,这块
注意这里也是可以用相同的方法去做字典值类型的转换的,可以参考下芋道源码的DictConvert.java
2、POI版本
这里切记POI版本和ooxml的版本一堆要保持一致,不然会出现各种问题
3、日期类型 LocalDateTime 转换的问题
java 复制代码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/curren…
参照芋道源码