EasyExcel保姆级使用教程

1.使用步骤

添加依赖

<properties>
   <easyexcel.version>3.0.5</easyexcel.version>
</properties>
​
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>${easyexcel.version}</version>
</dependency>

2.表头设置

创建表头实体类（注意：类中变量名称需要与导出到表格中的list中对象中的字段相同）

@Data
@AllArgsConstructor
@NoArgsConstructor
public class CleaHead {
​
    @ExcelProperty(value = {"结算名称"}, index = 0)
    @ColumnWidth(47)
    private String clearingName;
    
    @ExcelProperty(value = {"主标题", "字符串标题"}, index = 1)
    @ColumnWidth(20)
    private Double clearingQuantityElec;
​
}

3.数据导出方式

1.读数据

public static class MyDataListener extends AnalysisEventListener<MyData> {
        @Override
        public void invoke(MyData data, AnalysisContext context) {
            System.out.println("读取到数据：" + data);
        }
 
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            // 所有数据解析完成后做的事情
        }
    }

2.写数据

 public void writeExcel() {
        String fileName = "example.xlsx";
        List<MyData> data = // ... 获取数据
 
        EasyExcel.write(fileName, MyData.class)
                .sheet("Sheet1")
                .doWrite(data);
    }

4.数据导出方式

1. 单个sheet直接导出到页面

   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(), ElecResultHead.class)
                       .registerWriteHandler(excelMergeHandler)
                       .sheet("电量核查")
                       .doWrite(elecResultVOS);
       
       } catch (IOException e) {
               e.printStackTrace();
           }

2. 多个sheet导出到页面

   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");
   WriteTable writeTable = EasyExcel.writerTable(1).needHead(Boolean.TRUE)
       .head(ElecResultHead.class).build();
   WriteTable writeTable1 = EasyExcel.writerTable(2)
       .needHead(Boolean.TRUE).head(StatHead.class).build();
   ​
   ExcelWriter excelWriter = EasyExcel.write(   response.getOutputStream()).registerWriteHandler(excelMergeHandler).build();
   ​
       WriteSheet writeSheet = new WriteSheet();
       writeSheet.setSheetNo(1);
       writeSheet.setSheetName("核算结果");
       excelWriter.write(elecResultVOS,writeSheet,writeTable);
   ​
               //获取统计有结算无数据
   List<StatElecNoCleaVo> statList = this.getStatELecData(
       statisticsClearingDao.getStatBinding());
       WriteSheet = new WriteSheet();
       writeSheet.setSheetNo(2);
       writeSheet.setSheetName("统计有结算无");
       excelWriter.write(statList,writeSheet,writeTable1);
   ​
       excelWriter.finish();
       } catch (IOException e) {
               e.printStackTrace();
           }

5.拦截器的使用

1. 写数据时

   实现CellWriteHandler接口

   导出时指定列的合并

   package com.zc.eq.entity.handler.diffexcel;
   ​
   import com.alibaba.excel.metadata.Head;
   import com.alibaba.excel.write.handler.CellWriteHandler;
   import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
   import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
   import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
   import com.zc.eq.entity.excel.MergeInfo;
   import org.apache.poi.ss.usermodel.Cell;
   import org.apache.poi.ss.usermodel.Row;
   import org.apache.poi.ss.usermodel.Sheet;
   import org.apache.poi.ss.util.CellRangeAddress;
   ​
   import java.util.ArrayList;
   import java.util.List;
   ​
   public class ExcelMergeHandler implements CellWriteHandler {
   ​
       //所有的合并信息
       List<MergeInfo> mergeInfos = new ArrayList<>();
   ​
       public ExcelMergeHandler() {
       }
   ​
       @Override
       public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
   ​
       }
   ​
       @Override
       public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
   ​
       }
   ​
       @Override
       public void afterCellDispose(CellWriteHandlerContext context) {
           Sheet sheet = context.getWriteSheetHolder().getSheet();
   ​
           for (int i = 0; i < mergeInfos.size(); i++) {
               MergeInfo mergeInfo = mergeInfos.get(i);
               mergeInfo.getCurColIndexList().forEach(item -> {
                   CellRangeAddress cellRangeAddress = new CellRangeAddress(mergeInfo.getCurRowIndex(),
                           mergeInfo.getCurRowIndex() + mergeInfo.getMergeNum(),
                           item,
                           item);
                   sheet.addMergedRegion(cellRangeAddress);
               });
   ​
               mergeInfos.remove(i);
           }
       }
   ​
       public void addMergeInfo(List<MergeInfo> mergeInfos){
           this.mergeInfos = mergeInfos;
       }
    
   }

   package com.zc.eq.entity.excel;
   ​
   import lombok.AllArgsConstructor;
   import lombok.Data;
   import lombok.NoArgsConstructor;
   ​
   import java.util.List;
   ​
   @Data
   @NoArgsConstructor
   @AllArgsConstructor
   public class MergeInfo {
   ​
       //当前行
       private Integer curRowIndex;
   ​
       //当前行的列集合
       private List<Integer> curColIndexList;
   ​
       //当前行的列
       private Integer curColIndex;
   ​
       //合并行数
       private Integer mergeNum;
   ​
   ​
   }

2. 读数据时

   实现AnalysisEventListener接口

   竖行合并表格的数据读取方案

   package com.zc.plant.listener;
   ​
   import com.alibaba.excel.context.AnalysisContext;
   import com.alibaba.excel.enums.CellExtraTypeEnum;
   import com.alibaba.excel.event.AnalysisEventListener;
   import com.alibaba.excel.metadata.CellExtra;
   import com.zc.plant.entity.PlantAgencyElePurchase;
   import lombok.extern.slf4j.Slf4j;
   ​
   import java.util.ArrayList;
   import java.util.List;
   ​
   //带竖行合并的表格读取
   @Slf4j
   public class ExcelReadSupListener extends AnalysisEventListener<PlantAgencyElePurchase> {
   ​
       private Integer headRowNum;
   ​
       public ExcelReadSupListener(Integer headRowNum) {
           this.headRowNum = headRowNum;
       }
    
       private List<PlantAgencyElePurchase> list = new ArrayList<>();
    
       @Override
       public void invoke(PlantAgencyElePurchase plantAgencyElePurchase, AnalysisContext analysisContext) {
           list.add(plantAgencyElePurchase);
       }
    
       @Override
       public void extra(CellExtra extra, AnalysisContext context) {
   ​
           //合并存在于第一列类型
           if (extra.getRowIndex() >= headRowNum && extra.getColumnIndex() == 0) {
               CellExtraTypeEnum type = extra.getType();
           switch (type) {
               case MERGE: {
                   if (extra.getFirstRowIndex() <= extra.getLastRowIndex() - 1) {
   ​
                       //获取合并行首行数据为合并行下方单元格数据赋值
                       int mergeNo = extra.getLastRowIndex() - extra.getFirstRowIndex();
                       if (mergeNo > 1){
                           for (int i = extra.getFirstRowIndex() + 1; i < extra.getLastRowIndex() + 1; i++) {
                               list.get(i - headRowNum).setElecSour(list.get(extra.getFirstRowIndex() - headRowNum).getElecSour());
                           }
                       }
                   }
                   break;
               }
               default:{
                   }
               }
           }
   ​
       }
    
       @Override
       public void doAfterAllAnalysed(AnalysisContext analysisContext) {
   ​
       }
    
       public List<PlantAgencyElePurchase> getList() {
           return list;
       }
    
       /*public List<CellExtra> getCellExtraList() {
           return cellExtraList;
       }*/
   }