Easy excel 实现自定义功能

Easy excel如何快速实现自定义、个性化的需求

首先，引入easy excel的依赖。

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

注意，依赖的引入需要关注poi版本。具体参考：https://easyexcel.opensource.alibaba.com/qa/#关于版本选择

easy excel包下，主要有两个依赖包core与support，核心实现与代理支持实现。

查看core包，/write/handle目录下，是它的一些拦截器接口、抽象类、具体实现等，包含了merge、style等。其中，merge包是它自定义的合并策略实现，style包是样式策略的实现。

自定义Head颜色

参考DefaultStyle类，此类继承了HorizontalCellStyleStrategy。通过order控制拦截顺序。DefaultStyle里设置的就是默认的样式。
DEFAULT_DEFINE_STYLE =-70000，所以，我们自定义的head style，order顺序在前即可覆盖默认实现，比如-60000。

DefaultStyle.java

public class DefaultStyle extends HorizontalCellStyleStrategy {
   public int order() {
       return OrderConstant.DEFAULT_DEFINE_STYLE;
   }

   public DefaultStyle() {
       WriteCellStyle headWriteCellStyle = new WriteCellStyle();
       headWriteCellStyle.setWrapped(true);
       headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
       headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
       headWriteCellStyle.setLocked(true);
       headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
       headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
       headWriteCellStyle.setBorderTop(BorderStyle.THIN);
       headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
       headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
       headWriteCellStyle.setBorderRight(BorderStyle.THIN);
       WriteFont headWriteFont = new WriteFont();
       headWriteFont.setFontName("宋体");
       headWriteFont.setFontHeightInPoints(Short.valueOf((short)14));
       headWriteFont.setBold(true);
       headWriteCellStyle.setWriteFont(headWriteFont);
       this.setHeadWriteCellStyle(headWriteCellStyle);
   }
}

自定义head style 拦截器

public class DefaultHeadStyle extends HorizontalCellStyleStrategy{
  public int order() {//后覆盖前
      return -60000;
  }

  public DefaultHeadStyle() {
      WriteCellStyle headWriteCellStyle = new WriteCellStyle();
      headWriteCellStyle.setWrapped(true);
      headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
      headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
      headWriteCellStyle.setLocked(true);
      headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
      headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
      headWriteCellStyle.setBorderTop(BorderStyle.THIN);
      headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
      headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
      headWriteCellStyle.setBorderRight(BorderStyle.THIN);
      WriteFont headWriteFont = new WriteFont();
      headWriteFont.setFontName("宋体");
      headWriteFont.setFontHeightInPoints(Short.valueOf((short)14));
      headWriteFont.setBold(true);
      headWriteCellStyle.setWriteFont(headWriteFont);
      this.setHeadWriteCellStyle(headWriteCellStyle);
  }
}

自定义合并单元格策略

参考OnceAbsoluteMergeStrategy类，它实现了SheetWriteHandler。通过CellRangeAddress控制合并范围，addMergedRegionUnsafe方法执行合并。

OnceAbsoluteMergeStrategy.java

import com.alibaba.excel.metadata.property.OnceAbsoluteMergeProperty;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.util.CellRangeAddress;

public class OnceAbsoluteMergeStrategy implements SheetWriteHandler {
    private final int firstRowIndex;
    private final int lastRowIndex;
    private final int firstColumnIndex;
    private final int lastColumnIndex;

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

    public OnceAbsoluteMergeStrategy(OnceAbsoluteMergeProperty onceAbsoluteMergeProperty) {
        this(onceAbsoluteMergeProperty.getFirstRowIndex(), onceAbsoluteMergeProperty.getLastRowIndex(), onceAbsoluteMergeProperty.getFirstColumnIndex(), onceAbsoluteMergeProperty.getLastColumnIndex());
    }

    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        CellRangeAddress cellRangeAddress = new CellRangeAddress(this.firstRowIndex, this.lastRowIndex, this.firstColumnIndex, this.lastColumnIndex);
        writeSheetHolder.getSheet().addMergedRegionUnsafe(cellRangeAddress);
    }
}

自定义合并策略OnceExcelMergeStrategy.java

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

public class OnceExcelMergeStrategy implements SheetWriteHandler {

    private final List<CellRangeAddress> cellRangeAddresses;

    public OnceExcelMergeStrategy(List<CellRangeAddress> cellRangeAddresses) {
        this.cellRangeAddresses = cellRangeAddresses;
    }

    public OnceExcelMergeStrategy(OnceExcelMergeStrategy onceExcelMergeStrategy) {
        this(onceExcelMergeStrategy.cellRangeAddresses);
    }

    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        for (CellRangeAddress cellRangeAddress : this.cellRangeAddresses){
            if (cellRangeAddress.getFirstRow()>=cellRangeAddress.getLastRow() || cellRangeAddress.getFirstColumn()>cellRangeAddress.getLastColumn()){
                continue;
            }
            //区域如果存在重复，文件会异常。
            writeSheetHolder.getSheet().addMergedRegion(cellRangeAddress);
        }
    }

}

放在afterSheetCreate方法，是表示sheet对象创建后，再执行合并策略。