EasyExcel随笔
阅读原文时间:2023年07月09日阅读:3

EasyExcel

不支持的功能

  • 单个文件的并发写入、读取
  • 读取图片
  • csv读取

出现 NoSuchMethodException, ClassNotFoundException, NoClassDefFoundError

  • jar包冲突

easyExcel.xlsx文件

地区

2000年人口数(万人)

2000年比重

安徽省

5986

4.73%

北京市

1382

1.09%

福建省

3471

2.74%

甘肃省

2562

2.02%

广东省

8642

6.83%

广西壮族自治区

4489

3.55%

贵州省

3525

2.78%

海南省

787

0.62%

河北省

6744

5.33%

河南省

9256

7.31%

黑龙江省

3689

2.91%

湖北省

6028

4.76%

湖南省

6440

5.09%

吉林省

2728

2.16%

江苏省

7438

5.88%

江西省

4140

3.27%

辽宁省

4238

3.35%

难以确定常住地

105

0.08%

内蒙古自治区

2376

1.88%

宁夏回族自治区

562

0.44%

青海省

518

0.41%

山东省

9079

7.17%

山西省

3297

2.60%

陕西省

3605

2.85%

上海市

1674

1.32%

四川省

8329

6.58%

天津市

1001

0.79%

西藏自治区

262

0.21%

新疆维吾尔自治区

1925

1.52%

云南省

4288

3.39%

浙江省

4677

3.69%

中国人民解放军现役军人

250

0.20%

重庆市

3090

2.44%

对象

public class MyExcel {

    private String province;
    private Integer personNum;

    // 接收百分比的数字
    @NumberFormat("#.##")
    private String percent;

    public String getProvince() {
        return province;
    }

    public void setProvince(String province) {
        this.province = province;
    }

    public Integer getPersonNum() {
        return personNum;
    }

    public void setPersonNum(Integer personNum) {
        this.personNum = personNum;
    }

    public String getPercent() {
        return percent;
    }

    public void setPercent(String percent) {
        this.percent = percent;
    }
}

简单的读取

  1. 创建excel的对象
  2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器
  3. 直接读即可

监听器类

/**
 * 监听器
 * 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
 */
public class MyExcelListener extends AnalysisEventListener<MyExcel> {

    private static final Logger LOGGER  = LoggerFactory.getLogger(MyExcelListener.class);
    //控制存放在内存的数据的条数 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
    private static final int BATCH_COUNT = 5;
    //当一个临时缓存器 当存储器中数据超过BATCH_COUNT后进行释放
    List<MyExcel> list=new ArrayList<MyExcel>();

    /**
     * 如果要存储到数据库 则在这里接入dao 层
     *
     */
    private MyDao myDao;

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     */
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }

    /**
     * 这个每一条数据解析都会来调用
     * @param myExcel
     * @param analysisContext
     */
    public void invoke(MyExcel myExcel, AnalysisContext analysisContext) {
        System.out.println("解析到一条数据"+JSON.toJSONString(myExcel));
        LOGGER.info("解析到一条数据", JSON.toJSONString(myExcel));
        list.add(myExcel);
        // 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     * @param analysisContext
     */
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        LOGGER.info("所有数据解析完成!");
    }

    /**
     * 加上存储数据库
     *
     * */
        private void saveData() {
              LOGGER.info("{}条数据,开始存储数据库!", list.size());
              demoDAO.save(list); //dao层方法 存储到数据库
              LOGGER.info("存储数据库成功!");
          }

}

**执行 **

          String fileName="C:/Users/风筝/OneDrive/桌面/easyExcel.xlsx";
        /**
         *  文件路径 模板实体类 监听器
         */
        // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭

        //方法1
        EasyExcel.read(fileName,MyExcel.class,new MyExcelListener()).sheet().doRead();

        //方法2
        ExcelReader excelReader = EasyExcel.read(fileName, MyExcel.class, new                       MyExcelListener()).build();
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        excelReader.read(readSheet);
        // 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
        excelReader.finish();

有个很重要的点 这里的 监听器 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去

存入到持久层

/**
 * 假设这个是DAO存储。
 **/
public class DemoDAO {

    public void save(List<DemoData> list) {
        // mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}

指定列的下标或名称

 /**
     * 强制读取第三个 不建议 index 和 name 同时用,要么一个对象只用index,要么一个对象只用name去匹配
     */
    @ExcelProperty(index = 0)
    private String province;
    /**
     *
     *   用名字去匹配,这里需要注意,如果名字重复,会导致只有一个字段读取到数据
     *
     */
    @ExcelProperty("2000年人口数(万人)")
    private Integer personNum;

    // 接收百分比的数字
    @NumberFormat("#.##")
    private String percent;

读多个sheet

/**
 * 读多个或者全部sheet,这里注意一个sheet不能读取多次,多次读取需要重新读取文件
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link DemoData}
 * <p>
 * 2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,参照{@link DemoDataListener}
 * <p>
 * 3. 直接读即可
 */
@Test
public void repeatedRead() {
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 读取全部sheet
    // 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).doReadAll();

    // 读取部分sheet
    fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    ExcelReader excelReader = EasyExcel.read(fileName).build();
    // 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
    ReadSheet readSheet1 =
        EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
    ReadSheet readSheet2 =
        EasyExcel.readSheet(1).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
    // 这里注意 一定要把sheet1 sheet2 一起传进去,不然有个问题就是03版的excel 会读取多次,浪费性能
    excelReader.read(readSheet1, readSheet2);
    // 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
    excelReader.finish();
}
  • 一个sheet 不能读取多次
  • 监听器的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次
  • 读取部分sheet时 根据自己需求 定义不同的监听器 每个sheet调用自己的监听器
  • 读取时要把所有的sheet一起都放入一个excelReader.read

自定义类型转换( converter = MyConverter.class)

 /**
     * 强制读取第三个 不建议 index 和 name 同时用,要么一个对象只用index,要么一个对象只用name去匹配
     */
    @ExcelProperty(index = 0, converter = MyConverter.class )
    private String province;
    /**
     *
     *   用名字去匹配,这里需要注意,如果名字重复,会导致只有一个字段读取到数据
     *
     */
    @ExcelProperty("2000年人口数(万人)")
    private Integer personNum;

    // 接收百分比的数字
    @NumberFormat("#.##")
    private String percent;

自定义转换器类 实现converter接口

public class MyConverter implements Converter<String> {
    //要转换成的java类型
    @Override
    public Class supportJavaTypeKey() {
        return String.class;
    }

    //将转换的Excel类型
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 读的时候会调用
     * @param cellData excel表格里的值
     * @param excelContentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return "自定义的类型"+cellData.getStringValue();
    }

    /**
     * 写的时候会调用
     * @param s
     * @param excelContentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public CellData convertToExcelData(String s, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new CellData(s);
    }
}

读取表头数据

重写监听器的 invokeHeadMap方法

  /**
     * 获取表每一列的名字
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println(headMap);
        //{0=地区, 1=2000年人口数(万人), 2=2000年比重}
    }

web中的读

/**
* 文件上传
* <p>
* 1. 创建excel对应的实体对象 参照{@link UploadData}
* <p>
* 2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,参照{@link UploadDataListener}
* <p>
* 3. 直接读即可
*/
@PostMapping("upload")
@ResponseBody
public String upload(MultipartFile file) throws IOException {
   EasyExcel.read(file.getInputStream(), UploadData.class, new UploadDataListener(uploadDAO)).sheet().doRead();
   return "success";
}

读的基本步骤

Excel对应实体类

public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;

    public String getString() {
        return string;
    }

    public void setString(String string) {
        this.string = string;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public Double getDoubleData() {
        return doubleData;
    }

    public void setDoubleData(Double doubleData) {
        this.doubleData = doubleData;
    }

    public String getIgnore() {
        return ignore;
    }

    public void setIgnore(String ignore) {
        this.ignore = ignore;
    }
}

写入Excel

 public static void main(String[] args) {
        String fileName="C:/Users/风筝/OneDrive/桌面/easyExcelWrite.xlsx";
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());

        // 写法2

        // 这里 需要指定写用哪个class去写
//        ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build();
//        WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
//        excelWriter.write(data(), writeSheet);
//        // 千万别忘记finish 会帮忙关闭流
//        excelWriter.finish();

    }
    public static List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>();
        Double sum=new Double(0);
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
            sum+=0.56;
        }
        DemoData data = new DemoData();
        data.setDoubleData(sum);
        list.add(data);
        return list;
    }

写入后的Excel

字符串标题

日期标题

数字标题

字符串0

2020-04-24 00:22:10

0.56

字符串1

2020-04-24 00:22:10

0.56

字符串2

2020-04-24 00:22:10

0.56

字符串3

2020-04-24 00:22:10

0.56

字符串4

2020-04-24 00:22:10

0.56

字符串5

2020-04-24 00:22:10

0.56

字符串6

2020-04-24 00:22:10

0.56

字符串7

2020-04-24 00:22:10

0.56

字符串8

2020-04-24 00:22:10

0.56

字符串9

2020-04-24 00:22:10

0.56

5.6

根据参数只导出指定列

public void excludeOrIncludeWrite() {
    String fileName = TestFileUtil.getPath() + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";

    // 根据用户传入字段 假设我们要忽略 date
    Set<String> excludeColumnFiledNames = new HashSet<String>();
    excludeColumnFiledNames.add("date");
    // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
    EasyExcel.write(fileName, DemoData.class).excludeColumnFiledNames(excludeColumnFiledNames).sheet("模板")
        .doWrite(data());

    fileName = TestFileUtil.getPath() + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";
    // 根据用户传入字段 假设我们只要导出 date
    Set<String> includeColumnFiledNames = new HashSet<String>();
    includeColumnFiledNames.add("date");
    // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
    EasyExcel.write(fileName, DemoData.class).includeColumnFiledNames(includeColumnFiledNames).sheet("模板")
        .doWrite(data());
}

指定写入的列(Excel中下标从1开始java中下标从0开始)

public class IndexData {
    @ExcelProperty(value = "字符串标题", index = 0) //对应表中第一列
    private String string;
    @ExcelProperty(value = "日期标题", index = 1)//对应表中第二列
    private Date date;
    /**
     * 这里设置3 会导致第二列空的
     */
    @ExcelProperty(value = "数字标题", index = 3)//对应表中第三列
    private Double doubleData;
}

复杂头写入

public class ComplexHeadData {
    @ExcelProperty({"主标题", "字符串标题"})
    private String string;
    @ExcelProperty({"主标题", "日期标题"})
    private Date date;
    @ExcelProperty({"主标题", "数字标题"})
    private Double doubleData;

重复多次写入(写到单个或者多个Sheet)

  public void repeatedWrite() {

-----------------------------------------------------------------------------------------------
      // 方法1 如果写到同一个sheet
      String fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
      // 这里 需要指定写用哪个class去写
      ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build();
      // 这里注意 如果同一个sheet只要创建一次
      WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
      // 去调用写入,这里我调用了五次,实际使用时根据数据库分页的总的页数来
      for (int i = 0; i < 5; i++) {
          // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
          List<DemoData> data = data();
          excelWriter.write(data, writeSheet);
      }
      // 千万别忘记finish 会帮忙关闭流
      excelWriter.finish();

 ----------------------------------------------------------------------------------------------
      // 方法2 如果写到不同的sheet 同一个对象
      fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
      // 这里 指定文件
      excelWriter = EasyExcel.write(fileName, DemoData.class).build();
      // 去调用写入,这里我调用了五次,实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
      for (int i = 0; i < 5; i++) {
          // 每次都要创建writeSheet 这里注意必须指定sheetNo
          writeSheet = EasyExcel.writerSheet(i, "模板").build();
          // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
          List<DemoData> data = data();
          excelWriter.write(data, writeSheet);
      }
      // 千万别忘记finish 会帮忙关闭流
      excelWriter.finish();
----------------------------------------------------------------------------------------------
      // 方法3 如果写到不同的sheet 不同的对象
      fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
      // 这里 指定文件
      excelWriter = EasyExcel.write(fileName).build();
      // 去调用写入,这里我调用了五次,实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
      for (int i = 0; i < 5; i++) {
          // 每次都要创建writeSheet 这里注意必须指定sheetNo。这里注意DemoData.class 可以每次都变,我这里为了方便 所以用的同一个class 实际上可以一直变
          writeSheet = EasyExcel.writerSheet(i, "模板").head(DemoData.class).build();
          // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
          List<DemoData> data = data();
          excelWriter.write(data, writeSheet);
      }
      // 千万别忘记finish 会帮忙关闭流
      excelWriter.finish();
  }

日期、数字或者自定义格式转换

public class ConverterData {
    /**
     * 所有的 字符串起前面加上"自定义:"三个字
     */
    @ExcelProperty(value = "字符串标题", converter = CustomStringStringConverter.class)
    private String string;
    /**
     * 我想写到excel 用年月日的格式
     */
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty("日期标题")
    private Date date;
    /**
     * 我想写到excel 用百分比表示
     */
    @NumberFormat("#.##%")
    @ExcelProperty(value = "数字标题")
    private Double doubleData;

合并单元格

 public void mergeWrite() {
     String fileName = TestFileUtil.getPath() + "mergeWrite" + System.currentTimeMillis() + ".xlsx";
     // 每隔2行会合并 把eachColumn 设置成 3 也就是我们数据的长度,所以就第一列会合并。当然其他合并策略也可以自己写
     LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);
     // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
     EasyExcel.write(fileName, DemoData.class).registerWriteHandler(loopMergeStrategy).sheet("模板")
         .doWrite(data());
 }

自定义合并规则

  1. 首先自定义一个类继承AbstractMergeStrategy

  2. 继承后会有一个merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex)方法

    • sheet是当前操作cel所在l的sheet
    • cell为当前操作cell 此方法每一个cell进行写出时都会进行调用
    • head当前sheet的每一列的头标题

系统自带方法:OnceAbsoluteMergeStrategy

 /**
 *
 *
 *
 *  Excel 中 下标从1开始  java中下标从0开始
 *    int firstRowIndex, 起始表格所在行
 *    int lastRowIndex,   终止表格所在行
 *    int firstColumnIndex, 起始表格所在列
 *    int lastColumnIndex  终止表格所在列
 */

public OnceAbsoluteMergeStrategy(int firstRowIndex, int lastRowIndex, int firstColumnIndex, int lastColumnIndex) {
        if (firstRowIndex < 0 || lastRowIndex < 0 || firstColumnIndex < 0 || lastColumnIndex < 0) {
            throw new IllegalArgumentException("All parameters must be greater than 0");
        }
        this.firstRowIndex = firstRowIndex;
        this.lastRowIndex = lastRowIndex;
        this.firstColumnIndex = firstColumnIndex;
        this.lastColumnIndex = lastColumnIndex;
    }

系统自带方法LoopMergeStrategy

/**
*
*  Excel 中 下标从1开始  java中下标从0开始
*    int eachRow, 合并行数
*    int columnCount, 合并列数 默认为1
*     int columnIndex 表格中的第几列
*
*
**/

public LoopMergeStrategy(int eachRow, int columnCount, int columnIndex) {
        if (eachRow < 1) {
            throw new IllegalArgumentException("EachRows must be greater than 1");
        }
        if (columnCount < 1) {
            throw new IllegalArgumentException("ColumnCount must be greater than 1");
        }
        if (columnCount == 1 && eachRow == 1) {
            throw new IllegalArgumentException("ColumnCount or eachRows must be greater than 1");
        }
        if (columnIndex < 0) {
            throw new IllegalArgumentException("ColumnIndex must be greater than 0");
        }
        this.eachRow = eachRow;
        this.columnCount = columnCount;
        this.columnIndex = columnIndex;
    }

web中的写

/**
 * 文件下载(失败了会返回一个有部分数据的Excel)
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link DownloadData}
 * <p>
 * 2. 设置返回的 参数
 * <p>
 * 3. 直接写,这里注意,finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
 */
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
    // 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
    String fileName = URLEncoder.encode("测试", "UTF-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
    EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("模板").doWrite(data());
    ------------------------------------------------------------------------------------------
        /**
     * 文件下载并且失败的时候返回json(默认失败了会返回一个有部分数据的Excel)
     *
     * @since 2.1.1
     */
    @GetMapping("downloadFailedUsingJson")
    public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
        // 这里注意 使用swagger 会导致各种问题,请直接用浏览器或者用postman
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), DownloadData.class).autoCloseStream(Boolean.FALSE).sheet("模板")
                .doWrite(data());
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

写的基本步骤