EasyExcel: 结合springboot实现表格导出入(单/多sheet), 全字段校验,批次等操作(全)

---

1.前言简介

ps: 如您有更好的方案或发现错误，请不吝赐教，感激不尽啦~~~

使用了easyExcel实现导入操作, 全手动封装, 灵活使用, 为了满足部分业务需求, 也做了升级

1. 全字段进行校验, 使用注解与正则表达式, 校验到每一行参数
2. 报错信息明确, 精确到每一行, 某个字段不正确的报错
3. 多个sheet导入的excel, 提示出 sheet名下的第几行报错
4. 增加xid同批次报错回滚, 有点类似分布式事务, 也就是一行报错,全部批次数据清除
5. 增加拓展性, 制作监听器,样式封装等, 利用接口特性, 方便多工程使用拓展
6. 在特殊类型(如list等类型)导入时, 出现了报错, 进行了兼容操作
7. 增加了数据库插入批次新增, 防止推数据库的数据量过大, 业务才略微麻烦

1.1 链接传送门

1.1.1 easyExcel传送门

⇒ EasyExcel文档链接

⇒ EasyExcel-Plus尽情期待~~~

2. Excel表格导入过程

实现功能请看1 前言简介, 里面有详细说明

2.1 easyExcel的使用准备工作

2.1.1 导入maven依赖

<alibaba.easyexcel.version>3.3.4</alibaba.easyexcel.version>

<!-- easyExcel --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>${alibaba.easyexcel.version}</version></dependency>

2.1.2 建立一个util包

里面专门放置全部的excel操作, 如图所示

• realDto 里面就是具体导入业务dto
• testGroup是自行测试代码
• 其他类均为核心逻辑
  - readme.md 是使用说明, 防止后面人不知道如何使用

下面从2.1.3开始

2.1.3 ExcelUtils统一功能封装(单/多sheet导入)

跳转链接: 解释 @Accessors(chain = true) 与 easyExcel不兼容

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.rmi.ServerException;
import java.util.List;/*** Excel相关操作(简易)* 文章一: 解释 @Accessors(chain = true) 与 easyExcel不兼容* -> https://blog.csdn.net/qq_36268103/article/details/134954322** @author pzy* @version 1.1.0* @description ok*/
@Slf4j
public class ExcelUtils {/*** 方法1.1: 读取excel(单sheet)** @param inputStream 输入流* @param dataClass   任意类型* @param listener    监听* @param sheetNo     sheet编号* @param <T>         传入类型*/public static <T> void readExcel(InputStream inputStream, Class<T> dataClass, ReadListener<T> listener, int sheetNo) {try (ExcelReader excelReader = EasyExcel.read(inputStream, dataClass, listener).build()) {// 构建一个sheet 这里可以指定名字或者noReadSheet readSheet = EasyExcel.readSheet(sheetNo).build();// 读取一个sheetexcelReader.read(readSheet);}}/*** 方法2.1: 读取excel(多sheet)** @param inputStream 输入流* @param dataClass   任意类型* @param listener    监听* @param sheetNoList sheet编号* @param <T>         传入类型*/public static <T> void readExcel(InputStream inputStream, Class<T> dataClass, ReadListener<T> listener, List<Integer> sheetNoList) {try (ExcelReader excelReader = EasyExcel.read(inputStream, dataClass, listener).build()) {List<ReadSheet> readSheetList = Lists.newArrayList();sheetNoList.forEach(sheetNo -> {// 构建一个sheet 这里可以指定名字或者noReadSheet readSheet = EasyExcel.readSheet(sheetNo).build();readSheetList.add(readSheet);});// 读取一个sheetexcelReader.read(readSheetList);}}/*** 单sheet excel下载** @param httpServletResponse 响应对象* @param fileName            excel文件名字* @param dataClass           class类型(转换)* @param sheetName           sheet位置1的名字* @param dataList            传入的数据* @param writeHandlers       写处理器们 可变参数 (样式)* @param <T>                 泛型*/public static <T> void easyDownload(HttpServletResponse httpServletResponse,String fileName,Class<T> dataClass,String sheetName,List<T> dataList,WriteHandler... writeHandlers) throws IOException {//对响应值进行处理getExcelServletResponse(httpServletResponse, fileName);ExcelWriterSheetBuilder builder =EasyExcel.write(httpServletResponse.getOutputStream(), dataClass).sheet(sheetName);
//
//        builder.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
//                .registerWriteHandler(ExcelStyleTool.getStyleStrategy());/*样式处理器*/if (writeHandlers.length > 0) {for (WriteHandler writeHandler : writeHandlers) {builder.registerWriteHandler(writeHandler);}}builder.doWrite(dataList);}/*** 复杂 excel下载* 1. 多个sheet* 2. 多个处理器** @param httpServletResponse 响应对象* @param fileName            excel文件名字* @param dataClass           class类型(转换)* @param sheetNameList       多sheet的名字数据* @param sheetDataList        多sheet的实际数据* @param writeHandlers       写处理器们 可变参数 (样式)* @param <T>                 泛型*/public static <T> void complexDownload(HttpServletResponse httpServletResponse,String fileName,Class<T> dataClass,List<String> sheetNameList,List<List<T>> sheetDataList,WriteHandler... writeHandlers) throws IOException {if (sheetNameList.size() != sheetDataList.size()) {throw new ServerException("抱歉,名字与列表长度不符~");}//对响应值进行处理getExcelServletResponse(httpServletResponse, fileName);try (ExcelWriter excelWriter = EasyExcel.write(httpServletResponse.getOutputStream()).build()) {// 去调用写入, 这里最终会写到多个sheet里面for (int i = 0; i < sheetNameList.size(); i++) {ExcelWriterSheetBuilder builder = EasyExcel.writerSheet(i, sheetNameList.get(i)).head(dataClass);if (writeHandlers.length > 0) {for (WriteHandler writeHandler : writeHandlers) {builder.registerWriteHandler(writeHandler);}}WriteSheet writeSheet = builder.build();excelWriter.write(sheetDataList.get(i), writeSheet);}}}/*** 获取excel的响应对象** @param httpServletResponse response* @param fileName            文件名* @throws UnsupportedEncodingException 不支持编码异常*/private static void getExcelServletResponse(HttpServletResponse httpServletResponse, String fileName) throws UnsupportedEncodingException {// 设置URLEncoder.encode可以防止中文乱码，和easyexcel没有关系fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");httpServletResponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");httpServletResponse.setCharacterEncoding("utf-8");httpServletResponse.addHeader("Access-Control-Expose-Headers", "Content-Disposition");httpServletResponse.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");}

2.1.4 ExcelDataListener数据监听器

读取excel表格数据 一条一条读取出来
ps: ResultResponse就是返回值封装类 随便都行200或500

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.atomic.AtomicInteger;/*** 官方提供转换listener* ps: 有个很重要的点 ExcelDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去** @author pzy* @version 0.1.0* @description ok*/
//@Component
@Slf4j
public class ExcelDataListener<T> implements ReadListener<T> {/*** 每隔5条存储数据库，实际使用中可以300条，然后清理list ，方便内存回收*/private static final int BATCH_COUNT = 300;private final ConcurrentHashMap<String, AtomicInteger> map = new ConcurrentHashMap<>();/*** 缓存的数据*/
//    private List<T> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);private final List<T> cachedDataList = Lists.newCopyOnWriteArrayList();/*** 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。*/private final ExcelDataService excelDataService;/*** 自行定义的功能类型 1配件(库存) 2供应商 3客户(假)资料*/private final Integer functionType;/*** 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来*/public ExcelDataListener(ExcelDataService excelDataService1, Integer functionType) {this.excelDataService = excelDataService1;this.functionType = functionType;}/*** 这个每一条数据解析都会来调用** @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}*/@Overridepublic void invoke(T data, AnalysisContext context) {//        String threadName = Thread.currentThread().getName();
//        System.out.println(threadName);log.info("解析到一条数据:{}", JSON.toJSONString(data));String sheetName = context.readSheetHolder().getSheetName();//ps: 慢换LongAdder
//        if (!map.containsKey(sheetName)) {
//            map.put(sheetName, new AtomicInteger(0));
//        } else {
//            map.put(sheetName, new AtomicInteger(map.get(sheetName).incrementAndGet()));
//        }int sheetDataCounts = map.computeIfAbsent(sheetName, k -> new AtomicInteger(0)).incrementAndGet();log.info("当前sheet的数据是: {}, 数量是第: {}个", sheetName, sheetDataCounts);if (data != null) {JSONObject jsonObject = JSON.parseObject(JSON.toJSONString(data));jsonObject.put("sheetName", sheetName);jsonObject.put("sheetDataNo", sheetDataCounts);//放入sheet数据编号(如果仅一个sheetcachedDataList.add((T) jsonObject);//类型明确(不增加通配符边界了 增加使用难度)}// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (cachedDataList.size() >= BATCH_COUNT) {saveData();// 存储完成清理 list
//            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);cachedDataList.clear();//这块需要测试看看效果}}/*** 所有数据解析完成了 都会来调用*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 这里也要保存数据，确保最后遗留的数据也存储到数据库log.info("{}条数据，开始存储数据库！", cachedDataList.size());saveData();cachedDataList.clear();log.info("所有数据解析完成！");}/*** 加上存储数据库*/private void saveData() {log.info("{}条数据，开始存储数据库！", cachedDataList.size());
//        excelDataService.saveUser((T) new SystemUser());ResultResponse response = excelDataService.saveExcelData(functionType, cachedDataList);if (ResponseHelper.judgeResp(response)) {log.info("存储数据库成功！");}}}

2.1.5 ResponseHelper响应值处理

ResultResponse返回值封装类 任意即可

import com.alibaba.fastjson.TypeReference;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;import javax.servlet.http.HttpServletRequest;
import java.util.Objects;/*** 响应 工具类** @author pzy* @version 0.1.0* @description ok*/
public class ResponseHelper<T> {/*** 响应成功失败校验器* 疑似存在bug(未进行测试)*/@Deprecatedpublic T retBool(ResultResponse response) {/*1. 如果接口返回值返回的不是200 抛出异常*/if (response.getCode() != 200) {throw new ServiceException(response.getCode(), response.getMsg());}return response.getData(new TypeReference<T>() {});}/*** 请求响应值校验器(ResultResponse对象)*/public static void retBoolResp(ResultResponse response) {if (response == null) {throw new NullPointerException("服务响应异常!");}/*1. 如果接口返回值返回的不是200 抛出异常*/if (!Objects.equals(response.getCode(), 200)) {throw new ServiceException(response.getCode(), response.getMsg());}}/*** 判定响应返回值* <p>* true 表示200 服务通畅* false 表示500 服务不通畅(*/public static boolean judgeResp(ResultResponse response) {// 1. 如果接口返回值返回的不是200 返回falsereturn response != null && Objects.equals(response.getCode(), 200);}/*** 通过上下文对象获取请求头的token值* RequestHelper.getHeaderToken()*/@Deprecatedpublic static String getHeaderToken() {//请求上下文对象获取 线程RequestAttributes requestAttributes = RequestContextHolder.getRequestAttributes();assert requestAttributes != null;HttpServletRequest request = (HttpServletRequest) requestAttributes.resolveReference(RequestAttributes.REFERENCE_REQUEST);assert request != null;return request.getHeader("token");}}

2.1.6 MyConverter类-自定义转换器

@ExcelProperty(converter = MyConverter.class) 使用自定义转换器 针对list等类型进行操作

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;import java.util.Collections;
import java.util.List;
import java.util.StringJoiner;/*** list类型使用 自定义转换器(补充功能 beta版)* @author pzy* @version 0.1.0* @description ok*/
public class MyConverter implements Converter<List> {@Overridepublic Class<?> supportJavaTypeKey() {return List.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}/*** 读（导入）数据时调用*/@Overridepublic List convertToJavaData(ReadConverterContext<?> context) {//当字段使用@ExcelProperty(converter = MyConverter.class)注解时会调用//context.getReadCellData().getStringValue()会获取excel表格中该字段对应的String数据//这里可以对数据进行额外的加工处理String stringValue = context.getReadCellData().getStringValue();//将数据转换为List类型然后返回给实体类对象DTOreturn Collections.singletonList(stringValue);}/*** 写（导出）数据时调用*/@Overridepublic WriteCellData<?> convertToExcelData(WriteConverterContext<List> context) {//当字段使用@ExcelProperty(converter = MyConverter.class)注解时会调用//context.getValue()会获取对应字段的List类型数据//这里是将List<String>转换为String类型数据，根据自己的数据进行处理StringJoiner joiner = new StringJoiner(",");for (Object data : context.getValue()) {joiner.add((CharSequence) data);}//然后将转换后的String类型数据写入到Excel表格对应字段当中return new WriteCellData<>(joiner.toString());}
}

2.1.7 ExcelDataService

数据处理行为接口(多工程拓展)

import java.util.List;/*** 数据处理service** @author pzy* @version 0.1.0* @description ok*/
@FunctionalInterface
public interface ExcelDataService {/*** 保存导入的数据* 分批进入 防止数据过大 - 栈溢出** @param t 保存的数据类型*/<T> ResultResponse saveExcelData(Integer functionType, List<T> t);
}

2.1.8 ExcelReqDTO 统一请求dto

业务需要, 生成的文件名 sheet的名称 功能类型等信息
其中Lists.newArrayList() 没有的直接换成new ArrayList()即可 效果相同

import com.google.common.collect.Lists;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;import java.util.List;/*** excel统一请求dto* <p>* 传入需要的参数, 生成对应的excel表格** @author pzy* @version 0.1.0* @description ok*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
public class ExcelReqDTO {/*** 功能类型 例: 1用户 2其他业务*/private Integer functionType;/*** excel类型 1单sheet 2多sheet*/private Integer excelType;/*** 文件名称*/private String fileName;/*** sheet名称*/private String sheetName;/*** sheet名称组*/private List<String> sheetNames = Lists.newArrayList();
}

2.1.9 上传文件校验

文件大小校验可在配置文件内添加, 效果更好

import lombok.extern.slf4j.Slf4j;import org.springframework.web.multipart.MultipartFile;import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.Arrays;
import java.util.Locale;/*** 文件上传校验的公共方法* 严格校验** @author pzy* @version 1.0.0*/
@Slf4j
public class UploadCheckUtils {//20MBprivate static final Integer maxUpLoadSize = 20;/*** 只支持文件格式*/public static final String[] YES_FILE_SUPPORT = {".xlsx", ".xls", ".doc", ".docx", ".txt", ".csv"};/*** 全部文件(普通文件,图片, 视频,音频)后缀 支持的类型*/private static final String[] FILE_SUFFIX_SUPPORT = {".xlsx", ".xls", ".doc", ".docx", ".txt", ".csv",".jpg", ".jpeg", ".png", ".mp4", ".avi", ".mp3"};/*** 文件名字 需要排除的字符* 废弃:  "(", ")","",".", "——", "_","-"*/private static final String[] FILE_NAME_EXCLUDE = {"`", "!", "@", "#", "$", "%", "^", "&", "*", "=", "+","~", "·", "！", "￥", "……", "（", "）","?", ",", "<", ">", ":", ";", "[", "]", "{", "}", "/", "\\", "|","？", "，", "。", "《", "》", "：", "；", "【", "】", "、"};/*** 多文件上传* 校验+大图片压缩*/public MultipartFile[] uploadVerify(MultipartFile[] multipartFile) {/*校验1: 没有文件时,报错提示*/if (multipartFile == null || multipartFile.length <= 0) {throw new ServiceException(500, "上传文件不能为空");}/*总文件大于: ?Mb时, 拦截*/long sumSize = 0;for (MultipartFile file : multipartFile) {sumSize += file.getSize();}// 总文件超过100mb 直接拦截 beta功能 不正式使用if (sumSize > (100 * 1024 * 1024L)) {log.warn("(上传总空间)大于100MB, 文件上传过大!");
//            throw new ThirdServiceException(ResponseEnum.T160007, "(上传总空间)100");}/*校验2: 上传文件的长度小于等于1 就一个直接校验*/if (multipartFile.length <= 1) {MultipartFile[] files = new MultipartFile[1];files[0] = uploadVerify(multipartFile[0]);return files;}/*校验3: 多个文件直接校验 需要更换新的file */for (int i = 0; i < multipartFile.length; i++) {multipartFile[i] = uploadVerify(multipartFile[i]);}return multipartFile;}/*** 上传文件校验大小、名字、后缀** @param multipartFile multipartFile*/public static MultipartFile uploadVerify(MultipartFile multipartFile) {// 校验文件是否为空if (multipartFile == null) {throw new ServiceException(500, "上传文件不能为空呦~");}/*大小校验*/log.info("上传文件的大小的是: {} MB", new BigDecimal(multipartFile.getSize()).divide(BigDecimal.valueOf(1024 * 1024), CommonConstants.FINANCE_SCALE_LENGTH, RoundingMode.HALF_UP));log.info("上传限制的文件大小是: {} MB", maxUpLoadSize);if (multipartFile.getSize() > (maxUpLoadSize * 1024 * 1024L)) {throw new ServiceException(500, String.format("上传文件不得大于 %s MB", maxUpLoadSize));}// 校验文件名字String originalFilename = multipartFile.getOriginalFilename();if (originalFilename == null) {throw new ServiceException(500, "上传文件名字不能为空呦~");}for (String realKey : FILE_NAME_EXCLUDE) {if (originalFilename.contains(realKey)) {throw new ServiceException(500, String.format("文件名字不允许出现 '%s' 关键字呦~", realKey));}}// 校验文件后缀if (!originalFilename.contains(".")) {throw new ServiceException(500, "文件不能没有后缀呦~");}String suffix = originalFilename.substring(originalFilename.lastIndexOf('.'));/*校验: 文件格式是否符合要求*/if (!Arrays.asList(FILE_SUFFIX_SUPPORT).contains(suffix.toLowerCase(Locale.ROOT))) {//throw new RuntimeException("文件格式' " + realFormat + " '不支持,请更换后重试!");throw new ServiceException(500, "文件格式不支持呦~");}return multipartFile;}
}

2.1.10 最后写个readme.md(说明使用方式)

这里写不写都行, 如有错误,请指出,谢谢啦~

# excel工具类使用说明## 1.本功能支持1. excel导入
2. excel导出
3. 样式调整
4. 类型转换器## 2. 使用技术介绍- 使用alibaba的easyExcel 3.3.4版本
- 官网地址: [=> easyExcel新手必读 ](https://easyexcel.opensource.alibaba.com/docs/current)## 3. 功能说明1. ExcelUtils 统一工具类 封装了单/多sheet的导入与导出 任意类型传入 只需`.class`即可
2. ExcelStyleTool excel表格导出风格自定义
3. MyConverter: 对于list类型转换存在问题, 手写新的转换器(beta版)
4. ExcelDataListener 数据监听器, 在这里处理接收的数据
5. ExcelDataService 数据处理服务接口(封装统一的功能要求, 同时满足拓展性)
6. testGroup中 全部均为演示demo(请在需要的工程中使用)## 4. 功能的演示1. upload.html 前端简易测试功能页面(测试功能)## 5. 版本说明1. beta版(1.0.1), 测试中
2. 可能有更好的方法解决本次业务需求
3. 导出的样式仅仅是简易能用, 跟美观没啥关系## 6. 特别注意1. 生成的excel的实体类均需要新写(或者看6-2)
2. @Accessors不可使用: 源码位置-> (ModelBuildEventListener的buildUserModel)中的BeanMap.create(resultModel).putAll(map);> [不能使用@Accessors(chain = true) 注解原因: ](https://blog.csdn.net/zmx729618/article/details/78363191)
>## 7. 本文作者
> @author: pzy

2.2 easyExcel工具包(全)使用方式

testGroup组演示

2.2.1 UserExcelDTO 生成用户excel数据

跟随业务随意, 用啥字段就加啥, @ExcelIgnore //表示忽略此字段

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;/*** excel表格示例demo* ps: 不能用accessors** @author pzy* @version 0.1.0* @description ok*/
@ContentRowHeight(20)
@HeadRowHeight(30)
@ColumnWidth(25)
@NoArgsConstructor
@AllArgsConstructor
//@Accessors(chain = true)
@Data
public class UserExcelDTO {/*** 用户ID*/
//    @ExcelIgnore //忽略@ColumnWidth(20)@ExcelProperty(value = "用户编号")private Long userId;@ColumnWidth(50)@ExcelProperty(value = "真实姓名")private String realName;@ColumnWidth(50)@ExcelProperty(value = "手机号")private String phone;/*** 用户邮箱*/@ColumnWidth(50)//@ExcelProperty(value = "邮箱",converter = MyConverter.class)@ExcelProperty(value = "邮箱")private String email;}

2.2.2 ExcelDataServiceImpl实现类(工程一)

模拟一下数据库行为操作, 后面有实际操作呦~

import java.util.List;/*** 实现类 demo实现方式 (此处不可注入bean) 示例文档** @author pzy* @version 0.1.0* @description ok*/
//@Slf4j
//@RequiredArgsConstructor
//@Service
public class ExcelDataServiceImpl implements ExcelDataService {/*** 保存导入的数据* 分批进入 防止数据过大 - 栈溢出** @param t 保存的数据类型*/@Overridepublic <T> ResultResponse saveExcelData(Integer functionType, List<T> t) {//测试演示(添加数据库)return ResultResponse.booleanToResponse(true);}
//
//    /**
//     * 获取数据并导出到excel表格中
//     *
//     * @param t 传入对象
//     * @return t类型集合
//     */
//    @Override
//    public <T> List<T> getExcelData(T t) {
//        //测试演示
//        return null;
//    }
}

2.2.3 upload.html测试页面

网上找的前端页面, 改了改, 自行测试, 我这里没有token传入位置,

解决方案一: 后端放行一下, 测试后关闭即可
解决方案二: 让前端直接连, 用前端写过的页面
等等

<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><title>EasyExcel</title>
</head><body>
<div class="app"><input type="file" id="fileInput" accept=".xlsx, .xls, .csv"><button onclick="upload()">单sheet上传</button><br><br><input type="file" id="fileInput1" accept=".xlsx, .xls, .csv"><button onclick="upload1()">多sheet上传</button>
</div>
<br>
<div><button onclick="download()">单sheet导出</button>&nbsp;<button onclick="download1()">多sheet导出</button>
</div>
</body><script src="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script>
<script>const upload = () => {// 获取文件输入元素const fileInput = document.getElementById('fileInput')// 获取选中的文件const file = fileInput.files[0]if (!file) {alert('请选择一个文件')return}// 创建 FormData 对象const formData = new FormData()// 将文件添加到 FormData 对象formData.append('file', file)// 发送 POST 请求到后端axios.post('http://localhost:8001/system/excel/upload?functionType=1', formData, {headers: {'Content-Type': 'multipart/form-data' // 设置正确的 Content-Type}}).then(response => {alert('文件上传成功')console.log('文件上传成功:', response.data)}).catch(error => {console.error('文件上传失败:', error)});}const upload1 = () => {// 获取文件输入元素const fileInput = document.getElementById('fileInput1')// 获取选中的文件const file = fileInput.files[0]if (!file) {alert('请选择一个文件')return}// 创建 FormData 对象const formData = new FormData()// 将文件添加到 FormData 对象formData.append('file', file)// 发送 POST 请求到后端axios.post('http://localhost:8001/system/excel/upload1?functionType=2', formData, {headers: {'Content-Type': 'multipart/form-data', // 设置正确的 Content-Type'token': ''}}).then(response => {alert('文件上传成功')console.log('文件上传成功:', response.data)}).catch(error => {console.error('文件上传失败:', error)});}const headers = {// 'Content-Type': 'application/json', // 设置请求头部的Content-Type为application/json// token: '', // 设置请求头部的Authorization为Bearer your_token// 'Token4545': '1', // 设置请求头部的Authorization为Bearer your_token// 'responseType': 'blob', // 设置响应类型为blob(二进制大对象）};const download = () => {const url = 'http://192.168.1.254:8001/system/excel/download?fileName=单S文件&functionType=1'axios.get(url, {responseType: 'blob'}).then(response => {// 从Content-Disposition头部中获取文件名const contentDisposition = response.headers['content-disposition']console.log(response)console.log(contentDisposition)const matches = /filename\*=(utf-8'')(.*)/.exec(contentDisposition)console.log(matches)let filename = 'downloaded.xlsx'if (matches != null && matches[2] != null) {console.log(matches[2])// 解码RFC 5987编码的文件名filename = decodeURIComponent(matches[2].replace(/\+/g, ' '))} else {// 如果没有filename*，尝试使用filenameconst filenameMatch = /filename="(.*)"/.exec(contentDisposition);console.log(71)if (filenameMatch != null && filenameMatch[1] != null) {filename = filenameMatch[1]console.log(74)}}// 创建一个a标签用于下载const a = document.createElement('a')// 创建一个URL对象，指向下载的文件const url = window.URL.createObjectURL(new Blob([response.data]))a.href = urla.download = filename // 设置文件名document.body.appendChild(a)a.click()document.body.removeChild(a)window.URL.revokeObjectURL(url)}).catch(error => {console.error('下载文件时出错:', error)})}const download1 = () => {const url = 'http://192.168.1.254:8001/system/excel/test2'axios.get(url, {responseType: 'blob', // 设置响应类型为blob(二进制大对象）}).then(response => {// 从Content-Disposition头部中获取文件名const contentDisposition = response.headers['content-disposition']console.log(response)console.log(contentDisposition)const matches = /filename\*=(utf-8'')(.*)/.exec(contentDisposition)console.log(matches)let filename = 'downloaded.xlsx'if (matches != null && matches[2] != null) {console.log(matches[2])// 解码RFC 5987编码的文件名filename = decodeURIComponent(matches[2].replace(/\+/g, ' '))} else {// 如果没有filename*，尝试使用filenameconst filenameMatch = /filename="(.*)"/.exec(contentDisposition);console.log(71)if (filenameMatch != null && filenameMatch[1] != null) {filename = filenameMatch[1]console.log(74)}}// 创建一个a标签用于下载const a = document.createElement('a')// 创建一个URL对象，指向下载的文件const url = window.URL.createObjectURL(new Blob([response.data]))a.href = urla.download = filename // 设置文件名document.body.appendChild(a)a.click()document.body.removeChild(a)window.URL.revokeObjectURL(url)}).catch(error => {console.error('下载文件时出错:', error)})}
</script></html>

3.业务实战方式与效果(可跳过2.2)核心

前言: 2.2介绍的是简单的demo, 根据那个进行拓展

业务需求

1. 客户点击- 生成模板, 生成空的excel模板
2. 根据说明填写具体信息
3. 导入后, 如果数据正常,导入成功
4. 导入异常, 则明确告知数据问题在哪
5. 本次导入的数据均不生效
6. 面对多sheet导入异常, 明确指出 sheet名内的第*条数据,什么问题, 其他上同

操作方式:

• 设置批次导入(发放唯一批次号)
• 同批次的一组报错全部回滚
• 导入时生成批次, 整个线程使用一个批次
• 全字段自定义校验, 准确定位错误数据,给出精准提示

3.1 业务工具类

3.1.1 ThreadLocalUtils工具类(批次号)

写个基础的set和get , 通过当前线程传递xid号,

import java.util.Map;/*** threadLocal使用工具方法* <p>* ps: jdk建议将 ThreadLocal 定义为 private static* 避免: 有弱引用，内存泄漏的问题了** @author pzy* @description TODO beta01测试中* @version 1.0.1*/
public class ThreadLocalUtils {private static final ThreadLocal<Map<String, Object>> mapThreadLocal = new ThreadLocal<>();//获取当前线程的存的变量public static Map<String, Object> get() {return mapThreadLocal.get();}//设置当前线程的存的变量public static void set(Map<String, Object> map) {mapThreadLocal.set(map);}//移除当前线程的存的变量public static void remove() {mapThreadLocal.remove();}
}

3.1.2 自定义字段校验(注解)

-> 3.1.2_1 创建校验注解@DataCheck

如有更细致的校验, 请自行添加

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;/*** 实体类-数据校验注解* <p>* ps: 第一版* 校验方式* 1. 数据为空* 2. 最大长度* 3. 正则表达式* 4. 报错信息* <p>* 其中功能校验在 ValidatorUtils 中** @author pzy* @version 1.0.1* @description ok*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DataCheck {/*** 校验不能为空 true开启  false关闭*/boolean notBank() default false;/*** 长度*/int maxLength() default -1;/*** 正则表达式*/String value() default "";/*** 报错信息*/String message() default "";}

-> 3.1.2_2 注解实现类ValidatorUtils(校验逻辑)

对@DataCheck校验逻辑进行支持, 其中异常条数和异常sheet名称(多sheet需要)需要传递
这里先不管这俩参数
方法一: 单sheet
方法二: 多sheet

import com.alibaba.fastjson.JSON;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;import java.lang.reflect.Field;/*** 校验器工具类*/
@Slf4j
public class ValidatorUtils {/*** DataCheck注册-正则校验器1*/@SneakyThrowspublic static ResultResponse validate(Object obj, Integer errorCounts) {return validate(obj, errorCounts, null);}/*** DataCheck注册-正则校验器2*/@SneakyThrowspublic static ResultResponse validate(Object obj, Integer errorCounts, String sheetName) {Field[] fields = obj.getClass().getDeclaredFields();for (Field field : fields) {if (field.isAnnotationPresent(DataCheck.class)) {DataCheck annotation = field.getAnnotation(DataCheck.class);field.setAccessible(true);Object value = field.get(obj);//实体类参数int maxLength = annotation.maxLength(); //长度String message = "";if (StringUtils.isNotBlank(sheetName)) {message = String.format("可能是: 品类: %s ,第 %s 条,要求: %s", sheetName, errorCounts, annotation.message()); //报错信息} else {message = String.format("可能是: 第 %s 条,要求: %s", errorCounts, annotation.message()); //报错信息}String matchValue = annotation.value();//正则表达式/*校验1: 开启校验 且参数是空的 */if (annotation.notBank() && (value == null || value == "")) {log.warn("Field :[" + field.getName() + "] is null");log.error("校验出异常的数据是:=====>  {}", JSON.toJSONString(obj));
//                    throw new IllegalArgumentException("数据为空呦, " + message);return ResultResponse.error("数据为空呦, " + message);}/*校验2: 长度字段大于0 并且长度大于*/if (maxLength > 0) {if (maxLength < String.valueOf(value).length()) {log.warn("Field :[" + field.getName() + " ] is out of range");log.error("校验出异常的数据是:=====>  {}", JSON.toJSONString(obj));
//                        throw new IllegalArgumentException("数据超范围了呦, " + message);return ResultResponse.error("数据超范围了呦, " + message);}}/*校验3: 正则不匹配 则刨除异常*/if (StringUtils.isNotBlank(matchValue) && value != null && !value.toString().matches(matchValue)) {log.warn("Field :[" + field.getName() + "] is not match");log.error("校验出异常的数据是:=====>  {}", JSON.toJSONString(obj));
//                    throw new IllegalArgumentException("数据格式不对呦, " + message);return ResultResponse.error("数据格式不对呦, " + message);}}}return ResultResponse.ok();}}

3.2 工程内业务使用

3.2.0 创建上传或下载对象dto

添加校验注解 excel注册等, 不可使用@Accessors注解

/*** 临时客户dto** @author pzy* @version 0.1.0* @description ok*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserTempDTO {@DataCheck(notBank = true, maxLength = 255, value = "[A-Za-z0-9_\\-\\u4e00-\\u9fa5]+", message = "(非空)用户姓名支持中文,英文,数字,'-' 和'_', 长度255位")@ExcelProperty(value = "真实姓名")private String realname;@DataCheck(maxLength = 2, message = "性别请填写: 男,女,未知")@ExcelProperty(value = "性别")private String gender;@DataCheck(notBank = true,maxLength = 255, value = "0?(13|14|15|18|17)[0-9]{9}", message = "(非空)手机号需纯数字且长度11位")@ExcelProperty(value = "电话号")private String phone;//    @DataCheck(maxLength = 255, value = "[A-Za-z0-9_\\-\\u4e00-\\u9fa5]+", message = "地址信息名称支持中文,英文,数字,'-' 和'_', 长度255位")@DataCheck(maxLength = 255, message = "地址信息名称长度255位")@ExcelProperty(value = "地址信息")private String familyAddr;//    @DataCheck(maxLength = 255, value = "[A-Za-z0-9_\\-\\u4e00-\\u9fa5]+", message = "头像链接地址,长度255位")@DataCheck(maxLength = 255, message = "头像链接地址,长度255位")@ExcelProperty(value = "头像")private String avatarUrl;//---------------------------------------->@ExcelIgnore@ExcelProperty(value = "备用电话号")private String sparePhone;@ExcelIgnore@ExcelProperty(value = "昵称")private String nickname;@ExcelIgnore@ApiModelProperty(value = "excel的sheet名称")private String sheetName;@ExcelIgnore@ApiModelProperty(value = "excel的sheet名称对应行号,用于报错行数")private String sheetDataNo;@ExcelIgnore@ApiModelProperty(value = "xid号")private String xid;}

测试校验是否生效

    public static void main(String[] args) {UserTempDTO userTempDTO = new UserTempDTO();userTempDTO.setRealname("");userTempDTO.setGender("男");userTempDTO.setPhone("14788888888");userTempDTO.setFamilyAddr("");userTempDTO.setAvatarUrl("");ValidatorUtils.validate(userTempDTO,10);}

3.2.1 创建controller

业务的入口

@Slf4j
@RequiredArgsConstructor
@RestController
@RequestMapping("/excel/test")
public class SystemExcelController {private final SystemExcelService systemExcelService;@PostMapping("/upload")public ResultResponse upload(MultipartFile file, ExcelReqDTO excelReqDTO) throws IOException {log.info("===> excel文件上传 <===");//文件校验UploadCheckUtils.uploadVerify(file);try {Map<String, Object> map = new HashMap<>();long snowId = IdGenerater.getInstance().nextId();log.info("excel导入e_xid===> {}",snowId);map.put("e_xid", snowId);//存入threadLocalThreadLocalUtils.set(map);systemExcelService.upload(file, excelReqDTO);} finally {ThreadLocalUtils.remove();}return ResultResponse.ok("操作成功");}@GetMapping("/download")public void download(HttpServletResponse httpServletResponse, ExcelReqDTO excelReqDTO) throws IOException {log.info("===> excel文件下载 <===");systemExcelService.download(httpServletResponse, excelReqDTO);}}

3.2.2 接口SystemExcelService

/*** excel表格实现类* @author pzy* @version 0.1.0* @description ok*/
public interface SystemExcelService {/*** 上传excel文件* @param file 文件* @param excelReqDTO 请求参数*/void upload(MultipartFile file, ExcelReqDTO excelReqDTO);void download(HttpServletResponse httpServletResponse, ExcelReqDTO excelReqDTO);
}

3.2.3 实现类SystemExcelServiceImpl(需根业务自行调整)

这里面就是具体业务了
使用了ExcelUtils方法 实现多/单sheet导入与导出
导入ps: 在使用excelUtils方法时, 需要注入ExcelDataService接口来实现数据库存储操作
导出ps: 查询数据库数据, 处理 传入Lists.newArrayList() 这个位置即可

/*** excel表格实现类** @author pzy* @version 0.1.0* @description ok*/
@Service
@Slf4j
@RequiredArgsConstructor
public class SystemExcelServiceImpl implements SystemExcelService {private final ExcelDataService excelDataService;/*** 上传excel功能文件** @param file        文件* @param excelReqDTO 请求参数*/@SneakyThrows@Overridepublic void upload(MultipartFile file, ExcelReqDTO excelReqDTO) {//功能类型 1 2 3Integer functionType = excelReqDTO.getFunctionType();if (Objects.equals(functionType, 1)) {//多sheetExcelUtils.readExcel(file.getInputStream(),*.class,new ExcelDataListener<>(excelDataService, functionType),MathUtils.getIntRangeToList(0, 8));} else if (Objects.equals(functionType, 2)) {////单sheetExcelUtils.readExcel(file.getInputStream(),*.class,new ExcelDataListener<>(excelDataService, functionType), 0);} else if (Objects.equals(functionType, 3)) {////单sheetExcelUtils.readExcel(file.getInputStream(),*.class,new ExcelDataListener<>(excelDataService, functionType), 0);} else {throw new ServiceException(ResponseEnum.E30001);}}@SneakyThrows@Overridepublic void download(HttpServletResponse httpServletResponse, ExcelReqDTO excelReqDTO) {String fileName = excelReqDTO.getFileName();if (StringUtils.isBlank(fileName) || fileName.length() > 6) {throw new ServiceException("抱歉名称长度需大于0且不能超过6呦~");}//功能类型 1 2 3Integer functionType = excelReqDTO.getFunctionType();if (Objects.equals(functionType, 1)) {//sheet名字List<String> sheetNameList = ?;List<List<*>> sheetDataList = Lists.newArrayList();sheetNameList.forEach(sheetDto->sheetDataList.add(Lists.newArrayList()));ExcelUtils.complexDownload(httpServletResponse, fileName,ShopOfflineListDTO.class, sheetNameList,sheetDataList,new LongestMatchColumnWidthStyleStrategy(),ExcelStyleTool.getStyleStrategy());} else if (Objects.equals(functionType, 2)) {////写出excel核心代码ExcelUtils.easyDownload(httpServletResponse,fileName,*.class,"模板1",Lists.newArrayList(),//需要数据就传入 不需要就传递空集合new LongestMatchColumnWidthStyleStrategy(),ExcelStyleTool.getStyleStrategy());} else if (Objects.equals(functionType, 3)) {//写出excel核心代码ExcelUtils.easyDownload(httpServletResponse,fileName,*.class,"模板1",Lists.newArrayList(),new LongestMatchColumnWidthStyleStrategy(),ExcelStyleTool.getStyleStrategy());} else {throw new ServiceException(ResponseEnum.E30001);}}
}

3.2.4 寻找ExcelDataService的实现类

选择自己工程下的实现类, 写3.2.3的具体业务

如遇问题请提出

实现类重写saveExcelData()方法, 这里就列举其中的两种使用方式, 业务代码跳过

    /*** 保存导入的数据* 分批进入 防止数据过大 - 栈溢出** @param t 保存的数据类型*/
//    @Transactional@Overridepublic <T> ResultResponse saveExcelData(Integer functionType, List<T> t) {MemberResponseVo user = AuthServerConstant.loginUser.get();int companyId = user.getCompanyId();log.info("需要保存的数据: {}", JSON.toJSONString(t));//获取当前xid号-批次号(数据安全)String eXid = String.valueOf(ThreadLocalUtils.get().get("e_xid"));log.info("业务中: e_xid号=========================> {}", eXid);//功能类型 1配件(库存) 2供应商 3客户(假)资料if (Objects.equals(functionType, 1)) {//1return upload111Data(t, companyId, eXid);} else if (Objects.equals(functionType, 2)) {//2return upload222Data(t, companyId, eXid);} else if (Objects.equals(functionType, 3)) {//3return upload333Data(t, companyId, eXid);} else {throw new ServiceException(ResponseEnum.E30001);}}/*** 1. 上传配件数据** @param t         传入数据* @param companyId 公司id* @param eXid      eXid* @return ResultResponse对象*/private <T> ResultResponse uploadPartsData(List<T> t, Integer companyId, String eXid) {List<***> a1List;try {a1ListList = JSON.parseObject(JSON.toJSONString(t), new TypeReference<List<***>>() {});} catch (Exception e) {e.printStackTrace();return ResultResponse.error("类型不匹配,请先检查金额字段,必须是纯数字的整数或小数哟~");}if (CollectionUtils.isEmpty(a1List)) {return ResultResponse.ok("无数据需要导入~");}//数据处理a1List.forEach(a1DTO -> {//数据校验ResultResponse response = ValidatorUtils.validate(a1DTO, Integer.valueOf(a1.getSheetDataNo()), a1.getSheetName());if (!ResponseHelper.judgeResp(response)) {//执行回滚操作if (!ResponseHelper.judgeResp(productFeignService.rollBackPartsData(eXid))) {log.error("======> 数据eXid: {} 回滚失败了 ", eXid);}throw new IllegalArgumentException(response.getMsg());}a1.setSourceType(1);a1.setXid(eXid);//根据品类名称 转换成品类ida1.setTypeId(changeTypeNameToId(a1.getSheetName()));});//远程调用 即使出现问题也不会滚 业务内直接删除数据重新传递return ***.saveBatch(a1List);}

客户导入, 这个保留业务代码 方便查看具体使用方式

   /*** 3. 上传客户临时数据** @param t         传入数据* @param companyId 公司id* @param eXid      eXid* @return ResultResponse对象*/private <T> ResultResponse uploadUserTempData(List<T> t, Integer companyId, String eXid) {List<UserTempDTO> userTempList = JSON.parseObject(JSON.toJSONString(t), new TypeReference<List<UserTempDTO>>() {});if (CollectionUtils.isEmpty(userTempList)) {return ResultResponse.ok("无数据需要导入呦~");}List<AxUserTemp> axUserTempList = userTempList.stream().map(userTempDTO -> {//数据校验(包含回滚)ResultResponse response = ValidatorUtils.validate(userTempDTO, Integer.valueOf(userTempDTO.getSheetDataNo()));if (!ResponseHelper.judgeResp(response)) {rollBackAxUserTemp(eXid);throw new IllegalArgumentException(response.getMsg());}AxUserTemp axUserTemp = new AxUserTemp();BeanUtils.copyProperties(userTempDTO, axUserTemp);axUserTemp.setId(IdGenerater.getInstance().nextId()).setUserRole(UserRoleEnum.CONSUMER.getCode()).setCompanyId(companyId).setCreateTime(DateUtils.getNowDate()).setDelFlag(1).setXid(eXid);return axUserTemp;}).collect(Collectors.toList());try {if (!SqlHelper.retBool(axUserTempMapper.insertBatchSomeColumn(axUserTempList))) {rollBackAxUserTemp(eXid);throw new SystemServiceException(ResponseEnum.E500, String.format("前 %s 条数据存在问题,数据导入失败", axUserTempList.size()));}} catch (DuplicateKeyException e) {e.printStackTrace();rollBackAxUserTemp(eXid);throw new SystemServiceException(ResponseEnum.E500, String.format("前 %s 条数据重复,请检查(可能重复提交)", axUserTempList.size()));} catch (Exception e) {e.printStackTrace();rollBackAxUserTemp(eXid);throw new SystemServiceException(ResponseEnum.E500, String.format("前 %s 条数据存在问题,数据导入异常", axUserTempList.size()));}return ResultResponse.ok();}

其中rollbackAxUserTemp()方法如下, 手动提交事务
第一步: 注入事务管理器

 	/*** 事务管理器*/private final PlatformTransactionManager platformTransactionManager;/*** 事务的一些基础信息，如超时时间、隔离级别、传播属性等*/private final TransactionDefinition transactionDefinition;

第二步: 根据xid号进行删除数据代表回滚, 添加代码 (其中可以添加一些参数 我这直接默认了)

 /*** 回滚临时用户数据(调用-事务不看结果直接提交)** @param eXid xid号*/private void rollBackAxUserTemp(String eXid) {TransactionStatus transaction = platformTransactionManager.getTransaction(transactionDefinition);//TransactionStatus ： 事务的一些状态信息，如是否是一个新的事务、是否已被标记为回滚try {axUserTempMapper.delete(Wrappers.<AxUserTemp>lambdaQuery().eq(AxUserTemp::getXid, eXid));platformTransactionManager.commit(transaction);}  catch (Exception e) {// 回滚事务platformTransactionManager.rollback(transaction);throw e;}}

3.3 程序测试执行结果及报错解决

3.3.1 执行结果

前端接入, 可以根据上面testGroup里面html的进行调整
后端部署, 测试, 效果如下

3.3.2 报错解决

emm, 代码太长了, 遇到,想用的话评论或私信吧, 遇到的问题太多了, 挑几个重点的

3.3.2_1 CROS跨域问题

• 生产环境跨域, 代理一下,配置nginx
• 开发环境: 本地开跨域只能解决其中一种问题, 下个插件cros就行了 , 有更好的办法(后端)欢迎评论哈~

3.3.2_2 excel表格导出是空

去掉@Accessors(chain = true)即可

3.3.2_3 导入dto中有list报错

使用注解 @ExcelProperty(value = “”,converter = MyConverter.class)
试一下, 不好用评论区发一下

3.3.2_4 导出模板/sheet的名字不正确

基本是前端的问题了, 按照html里去改即可

3.3.2_5 待续未完…

想不起来还遇到哪些问题了, 业务层面的不包含, 多线程测试也正常, 等遇到问题在调整本文
如遇到部分类没有, 可根据上下文行为自行更改或评论区指出
逐步在这里添加

4. 文章的总结与预告

4.1 本文总结

easyExcel实现具体操作, 遇到问题请看 3.3

4.2 下文预告

暂无

---

@author: pingzhuyan
@description: ok
@year: 2024