1.工具类
package com.sjzl.util; import com.alibaba.excel.EasyExcel; import com.baomidou.mybatisplus.extension.service.IService; import lombok.extern.slf4j.Slf4j; import org.springframework.stereotype.Component; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.net.URLEncoder; import java.util.List; @Slf4j @Component public class ExcelTransfer<T> { /** * 上传excel 对用实体类不允许使用链式调用注解 * * <p>@Accessors(chain = true) 对应实体类实现equals和hashCode方法会自动过滤与数据库重复的数据 * * @param file 文件 * @param service 对应实体的service * @return 成功与否 * @throws ClassNotFoundException */ public boolean importExcel(MultipartFile file, IService<T> service,Class<?> aClass) throws ClassNotFoundException { try { EasyExcel.read(file.getInputStream(), aClass, new DataListener<>(service)) .sheet() .doRead(); } catch (IOException e) { log.error(e.getMessage(), e); return false; } return true; } /** * 上传excel 对用实体类不允许使用链式调用注解 自定义格式转换 * * <p>@Accessors(chain = true) 对应实体类实现equals和hashCode方法会自动过滤与数据库重复的数据 * * @param file 文件 * @param service 对应实体的service * @param list 实现自定义转换方法 * @return 成功与否 * @throws ClassNotFoundException 为找到对应的类 */ public boolean importExcel(MultipartFile file, IService<T> service, Class<?> aClass, ConvertList<T> list) throws ClassNotFoundException { try { EasyExcel.read(file.getInputStream(), aClass, new DataListener<>(service, list)) .sheet() .doRead(); } catch (IOException e) { log.error(e.getMessage(), e); return false; } return true; } /** * 下载excel * * @param response http * @param list 需要下载的数据 * @param name 文件名称 * @param sheet 表名 */ public void exportExcel( HttpServletResponse response, List<T> list, String name, String sheet, IService<T> service) throws ClassNotFoundException { String className = service.getClass().getName(); String s = className.substring(21, className.length() - 11); Class<?> aClass = Class.forName("com.esb.entity." + s); extracted(response, list, name, sheet, aClass); } /** * 下载excel * * @param response http * @param list 需要下载的数据 * @param name 文件名称 * @param sheet 表名 * @param aClass 实体类 */ public void exportExcel( HttpServletResponse response, List<T> list, String name, String sheet, Class<?> aClass) throws ClassNotFoundException { extracted(response, list, name, sheet, aClass); } private void extracted( HttpServletResponse response, List<T> list, String name, String sheet, Class<?> aClass) { try { //response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode(name, "UTF-8"); //response.setHeader("Content-disposition", "attachment;filename="+fileName+";"+"filename*=utf-8''"+fileName); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), aClass).sheet(sheet).doWrite(list); } catch (IOException e) { log.error(e.getMessage(), e); } } public void exportExcel2( HttpServletResponse response, List<T> list, String name, String sheet, IService<T> service) throws ClassNotFoundException { String className = service.getClass().getName(); String s = className.substring(21, className.length() - 11); Class<?> aClass = Class.forName("com.esb.entity." + s); extracted2(response, list, name, sheet, aClass); } private void extracted2( HttpServletResponse response, List<T> list, String name, String sheet, Class<?> aClass) { try { response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode(name, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), aClass).sheet(sheet).doWrite(list); } catch (IOException e) { log.error(e.getMessage(), e); } } }
package com.sjzl.util; 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.baomidou.mybatisplus.annotation.TableField; import com.baomidou.mybatisplus.extension.service.IService; import lombok.extern.slf4j.Slf4j; import java.lang.reflect.Field; import java.util.*; import java.util.concurrent.atomic.AtomicLong; /** * @author: QTX * @date: 2022/4/27 */ @Slf4j public class DataListener<E> implements ReadListener<E> { /** 每隔5条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收 */ private static final int BATCH_COUNT = 10000; /** 缓存的数据 */ private List<E> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); /** 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。 */ private final IService<E> service; /** excel数据与table不一致,手动实现转换方法 */ private ConvertList<E> convert; /** * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来 * * @param service */ public DataListener(IService<E> service) { this.service = service; } /** * 需要转换excel数据,请使用这个构造方法。 * * @param service * @param convert */ public DataListener(IService<E> service, ConvertList<E> convert) { this.service = service; this.convert = convert; } @Override public void onException(Exception exception, AnalysisContext context) throws Exception {} /** * 这个每一条数据解析都会来调用 * * @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()} * @param context */ @Override public void invoke(E data, AnalysisContext context) { // 哈哈 log.info("解析到一条数据:{}", JSON.toJSONString(data)); try { if (isNull(data)) { log.info("添加一条数据到备选集合:{}", JSON.toJSONString(data)); cachedDataList.add(data); } } catch (IllegalAccessException e) { throw new RuntimeException(e); } // 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM if (cachedDataList.size() >= BATCH_COUNT) { saveData(); // 存储完成清理 list cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); } } /** * 所有数据解析完成了 都会来调用 * * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) { // 这里也要保存数据,确保最后遗留的数据也存储到数据库 saveData(); log.info("所有数据解析完成!"); } @Override public boolean hasNext(AnalysisContext context) { return true; } /** 加上存储数据库 */ private void saveData() { Set<E> set = new HashSet<>(service.list()); List<E> saveList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); List<E> asList; if (convert != null) { log.info("{}条数据,开始转换数据格式。", cachedDataList.size()); saveList.addAll(new HashSet<>(convert.convert(cachedDataList))); } else { asList = cachedDataList; asList.forEach( s -> { if (!set.contains(s)) { s