easy excel的使用

1. 导入maven依赖

  <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>2.2.6</version>
  </dependency>
  <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
  <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.12</version>
      <scope>provided</scope>
  </dependency>

1. 定义导出和导入数据需要解析到的对象

    @Data
    public static class Demo {
        @ExcelProperty("数据1")
        private String fieldA;
        @ExcelProperty("数据2")
        private String fieldB;
        @ExcelProperty("数据3")
        private String fieldC;
        @ExcelProperty("数据4")
        private String fieldD;
    }

1. 导出excel数据
   使用data()生产需要导出的数据

  public List<Demo> data() {
      List<Demo> list = new ArrayList<>();
      Demo demo = new Demo();
      demo.setFieldA("A");
      demo.setFieldB("BA1");
      demo.setFieldC("CA1");
      list.add(demo);
      Demo demo1 = new Demo();
      demo1.setFieldA("A");
      demo1.setFieldB("BA2");
      demo1.setFieldC("CA21");
      demo1.setFieldD("DA21");
      list.add(demo1);
      Demo demo2 = new Demo();
      demo2.setFieldA("A");
      demo2.setFieldB("BA2");
      demo2.setFieldC("CA22");
      demo2.setFieldD("DA22");
      list.add(demo2);
      Demo demo3 = new Demo();
      demo3.setFieldA("A");
      demo3.setFieldB("BA3");
      demo3.setFieldC("CA22");
      demo3.setFieldD("DA221");
      list.add(demo3);
      Demo demo4 = new Demo();
      demo4.setFieldA("A");
      demo4.setFieldB("BA3");
      demo4.setFieldC("CA32");
      demo4.setFieldD("DA330");
      list.add(demo4);
      Demo demo5 = new Demo();
      demo5.setFieldA("A");
      demo5.setFieldB("BA3");
      demo5.setFieldC("CA32");
      demo5.setFieldD("DA331");
      list.add(demo5);
      Demo demo6 = new Demo();
      demo6.setFieldA("B");
      demo6.setFieldB("BB1");
      demo6.setFieldC("CB2");
      demo6.setFieldD("DB3");
      list.add(demo6);
      return list;
  }

• 正常导出

  @Test
  void down() throws FileNotFoundException {
      @Cleanup OutputStream os = new FileOutputStream(new File("demo.xlsx"));
      EasyExcel.write(os, Demo.class)
              .sheet("demo")
              .doWrite(data());
  }

• 导出时合并单元格
  自带的单元格合并策略 LoopMergeStrategy , SimpleRowHeightStyleStrategy
  使用方法

  @ContentLoopMerge(eachRow =2 ,columnExtend = 1)
  @ExcelProperty("数据1")
  private String fieldA;

自定义单元格合并策略,则需实现WriteHandler接口

    /**
  * 自定义合并策略
  */
  public class MergeCellStrategy extends AbstractRowWriteHandler {
      private final List<ColMark> mark = new ArrayList<>();
      private final int totalRow;
      public MergeCellStrategy(int totalRow) {
          this.totalRow = totalRow;
      }
      @Override
      public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
          if (!isHead) {
              final int cellNum = row.getPhysicalNumberOfCells();
              // 初始化标记对象
              if (CollectionUtils.isEmpty(mark)) {
                  for (int i = 0; i < cellNum; i++) {
                      final Cell cell = row.getCell(i);
                      mark.add(new ColMark(row.getRowNum(), cell.getColumnIndex(), cell.getStringCellValue()));
                  }
              } else {
                  for (int i = 0; i < cellNum; i++) {
                      final Cell cell = row.getCell(i);
                      final ColMark colMark = mark.get(i);
                      if (Objects.isNull(colMark)) {
                          mark.set(i, new ColMark(row.getRowNum(), cell.getColumnIndex(), cell.getStringCellValue()));
                          continue;
                      }
                      if (!colMark.getValue().equals(cell.getStringCellValue())) {
                          // 执行合并
                          execMerge(writeSheetHolder, row, cellNum, i, false);
                          // 重置当前col之后的col
                          resetMarkList(i, cellNum, row.getRowNum(), cell);
                      }
                  }
              }
              // 结束时执行一次合并
              if (row.getRowNum() >= totalRow) {
                  execMerge(writeSheetHolder, row, cellNum, 0, true);
              }
          }
      }
      private void execMerge(WriteSheetHolder holder, Row row, int cellNum, int beforeIndex, boolean isAll) {
          for (int i = beforeIndex; i < cellNum; i++) {
              final ColMark margeMark = mark.get(i);
              margeMark.setRowEnd(isAll ? row.getRowNum() : row.getRowNum() - 1);
              if (margeMark.rowEnd - margeMark.rowStart >= 1) {
                  merge(holder, margeMark);
              }
          }
      }
      private void merge(WriteSheetHolder holder, ColMark colMark) {
          CellRangeAddress cellRangeAddress = new CellRangeAddress(colMark.getRowStart(), colMark.getRowEnd(), colMark.col, colMark.col);
          holder.getSheet().addMergedRegionUnsafe(cellRangeAddress);
      }
      private void resetMarkList(int index, int cellNums, int rowIndex, Cell cell) {
          for (int i = index; i < cellNums; i++) {
              mark.set(i, null);
          }
          mark.set(index, new ColMark(rowIndex, cell.getColumnIndex(), cell.getStringCellValue()));
      }
  }
  @RequiredArgsConstructor
  @Data
  public class ColMark {
      @NonNull
      private int rowStart;
      private int rowEnd;
      @NonNull
      private int col;
      @NonNull
      private Object value;
  }

使用方法

  @Test
  void down() throws FileNotFoundException {
      @Cleanup OutputStream os = new FileOutputStream(new File("demo.xlsx"));
      EasyExcel.write(os, Demo.class)
              .sheet("demo")
              .registerWriteHandler(new MergeCellStrategy(data.size()))
              .doWrite(data());
  }

• 多sheet导出

  @Test
  void down1() throws FileNotFoundException {
      @Cleanup OutputStream os = new FileOutputStream(new File("demo.xlsx"));
      ExcelWriter excelWriter = null;
      try {
          excelWriter = EasyExcel.write(os).build();
          List<DownExcelApi.Demo> data = data();
          WriteSheet writeSheet = EasyExcel.writerSheet(0, "sheet1")
                  .head(DownExcelApi.Demo.class)
                  .build();
          excelWriter.write(data, writeSheet);
          WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "sheet2")
                  .head(DownExcelApi.Demo.class)
                  .build();
          // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
          excelWriter.write(data, writeSheet1);
      } finally {
          if (excelWriter != null) {
              excelWriter.finish();
          }
      }
  }

4 导入excel,并解析数据

  @Test
  void readExcel() throws FileNotFoundException {
      @Cleanup InputStream inputStream = new FileInputStream(new File("*****/demo.xlsx"));
      // Excel表格数据记录解析为具体的实体对象集合
      final SyncReadListener syncReadListener = new SyncReadListener();
      // Excel表格数据记录解析为具体的实体对象集合
      EasyExcel.read(inputStream, Demo.class, syncReadListener)
              .build()
              .read(new ReadSheet(0));
      //noinspection rawtypes
      List list = syncReadListener.getList();
  }

也可以通过实现ReadListener方法自定以数据处理方法

注意事项：

1. 不要使用@Accessors(chain = true)注解