EasyExcel

一. pom引入

           <dependency>
               <groupId>com.alibaba</groupId>
               <artifactId>easyexcel</artifactId>
               <version>2.1.2</version>
           </dependency>

阿里的pom poi version可能与你的老项目的version版本有冲突 exclude 调 引入适合阿里easyExcel poi版本

简单的读和简单的写

我们服务有多层 发起读和写操作是对外暴露那层服务进行操作;

简单的订单导出

  EasyExcel.write(response.getOutputStream(), ShopExcelBo.class).registerWriteHandler(merageTarget).sheet("订单").doWrite(shopExcel);

上面的代码就是数据导出 有订单的合并单元格操作 需要自己去实现策略

public class CustomerMerageStrategy extends AbstractMergeStrategy {
   private List<ExcelBaseBo> list;
   private Boolean flag = true;

   public CustomerMerageStrategy(List<ExcelBaseBo> list) {
       if (list.size() < 1) {
           this.flag = false;
       }
       this.list = list;
   }

   @Override
   protected void merge(Sheet sheet, Cell cell, Head head, int i) {
       if (flag) {
           this.list.forEach(excelBaseBo -> {
               CellRangeAddress cellRangeAddress = new CellRangeAddress(excelBaseBo.getFirstRowIndex(), excelBaseBo.getLastRowIndex(), excelBaseBo.getFirstColumnIndex(), excelBaseBo.getLastColumnIndex());
               sheet.addMergedRegion(cellRangeAddress);
           });
           this.flag = false;
       }


   }
}

需要自己通过构造方法传入需要合并的第几行 第几列 比较复杂的

public class ExcelBaseBo {
    private int firstRowIndex;
    private int lastRowIndex;
    private int firstColumnIndex;
    private int lastColumnIndex;
}

还有一个比较常见的需求是下拉框

public class CustomSheetWriteHandler implements SheetWriteHandler {

    private static final Logger LOGGER = LoggerFactory.getLogger(CustomSheetWriteHandler.class);

    private int row;

    private List<String>company;

    public CustomSheetWriteHandler(int row,List<String>company) {
        this.row = row;
        this.company=company;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        LOGGER.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo());

        // 区间设置 第一列第一行和第二行的数据。由于第一行是头，所以第一、二行的数据实际上是第二三行
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, row, 2, 2);
        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(company.toArray(new String[0]));
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        writeSheetHolder.getSheet().addValidationData(dataValidation);
    }
}

这个实现类是来做下拉框 是在第第几列往下做下拉框

easyExcel还有一个是数字变成科学计数法 新版本已经解决了

旧版本的是这样解决的

public class CustomCellWriteHandler implements CellWriteHandler {

    private static final Logger LOGGER = LoggerFactory.getLogger(CustomCellWriteHandler.class);
    private Boolean flag = true;
    private CellStyle cellStyle;

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                 Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
                                Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        init(writeSheetHolder);
        if ((cell.getColumnIndex() == 1 || cell.getColumnIndex() == 0)) {
            cell.setCellType(CellType.STRING);
            cell.setCellStyle(cellStyle);
        }
    }

    private void init(WriteSheetHolder writeSheetHolder) {
        if (flag) {
            Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
            cellStyle = workbook.createCellStyle();
            DataFormat format = workbook.createDataFormat();
            cellStyle.setDataFormat(format.getFormat("@"));

            setFlag();

        }
    }

    private void setFlag() {
        flag = false;
    }
}

这个就是我目前遇到的 easyExcel还是挺好用的 屏蔽了很多实现细节 代码写起来更加好 更好维护

excel读还是很好解决的 可以把读到的数据变成实体对象

 EasyExcel.read(file.getInputStream(), IndexLogisticsTemplateData.class, indexOrNameDataListener).sheet().doRead();

public class IndexOrNameDataListener extends AnalysisEventListener<IndexLogisticsTemplateData> {
   private static final Logger LOGGER = LoggerFactory.getLogger(IndexOrNameDataListener.class);
   /**
    * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
    */
   List<IndexLogisticsTemplateData> list = new ArrayList<IndexLogisticsTemplateData>();

   @Override
   public void invoke(IndexLogisticsTemplateData data, AnalysisContext context) {
       LOGGER.info("解析到一条数据:{}", JsonUtil.toJson(data));
       list.add(data);
   }

   @Override
   public void doAfterAllAnalysed(AnalysisContext context) {
       saveData();
       LOGGER.info("所有数据解析完成！");
   }

   /**
    * 加上存储数据库
    */
   private void saveData() {
       LOGGER.info("{}条数据，开始存储数据库！", list.size());
       LOGGER.info("存储数据库成功！");
   }

   public List<IndexLogisticsTemplateData> getList() {
       return list;
   }
}