EasyExcel 多sheet，动态列，定义下拉框，锁定某列

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
github地址:https://github.com/alibaba/easyexcel

本人开发过的账单催收系统中账单有多种类型，支持账单导入导出，导入已收

账单类型多样化决定了excel列不同，个性化程度高，同时需要支持多sheet，因此根据业务需求提炼出了以下工具类

映射实体数据校验校验工具类

public class ValidatorUtil {
   private ValidatorUtil() {
   }

   private static Validator validator = Validation.buildDefaultValidatorFactory().getValidator();

   public static <T> StringBuilder validateEntity(T obj) throws NoSuchFieldException {
      StringBuilder result = new StringBuilder();
      Set<ConstraintViolation<T>> set = validator.validate(obj, Default.class);
      if (set != null && !set.isEmpty()) {
         for (ConstraintViolation<T> cv : set) {
            Field declaredField = obj.getClass().getDeclaredField(cv.getPropertyPath().toString());
            ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
            result.append(annotation.value()[0]).append(cv.getMessage()).append(";");
         }
      }
      return result;
   }
}

动态修改某列表头

@SneakyThrows
public static void dynamicHead(Class<?> classType, String fileName, String[] value) 
{
   Field estateName = classType.getDeclaredField(fileName);
   ExcelProperty annotation = estateName.getAnnotation(ExcelProperty.class);
   InvocationHandler invocationHandler = Proxy.getInvocationHandler(annotation);
   // 获取 AnnotationInvocationHandler 的 memberValues 字段
   Field declaredField = invocationHandler.getClass().getDeclaredField("memberValues");
   // 因为这个字段是 private final 修饰，所以要打开权限
   declaredField.setAccessible(true);
   // 获取 memberValues
   Map memberValues = (Map) declaredField.get(invocationHandler);
   // 修改 value 属性值
   memberValues.put("value", value);
}

动态隐藏某列

public static void dynamicHeadShow(Class<?> classType, List<String> filedName, Integer value) 
{
   filedName.forEach(k -> {
      try {
         Field estateName = classType.getDeclaredField(k);
         ColumnWidth annotation = estateName.getAnnotation(ColumnWidth.class);
         InvocationHandler invocationHandler = Proxy.getInvocationHandler(annotation);
         Field declaredField = invocationHandler.getClass().getDeclaredField("memberValues");
         declaredField.setAccessible(true);
         Map memberValues = (Map) declaredField.get(invocationHandler);
         memberValues.put("value", value);
      } catch (NoSuchFieldException | IllegalAccessException e) {
         log.error("dynamicHeadShow:{}", e.getLocalizedMessage());
      }
   });
}

动态修改excel的表格颜色 -1 默认 10 red

public static void dynamicContentStyle(Class<?> classType, List<String> fileName, Integer value) {
   fileName.forEach(k -> {
      try {
         Field estateName = classType.getDeclaredField(k);
         ContentStyle annotation = estateName.getAnnotation(ContentStyle.class);
         InvocationHandler invocationHandler = Proxy.getInvocationHandler(annotation);
         Field declaredField = invocationHandler.getClass().getDeclaredField("memberValues");
         declaredField.setAccessible(true);
         Map memberValues = (Map) declaredField.get(invocationHandler);
         memberValues.put("fillForegroundColor", value);
      } catch (NoSuchFieldException | IllegalAccessException e) {
         log.error("dynamicHeadShow:{}", e.getLocalizedMessage());
      }
   });
}

导入Excel，设置分析事件监听器

public class AListener extends AnalysisEventListener<ExcelEntity> {

   /* 可以用来校验列名是否被修改 */
   @Override
   public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
      //  headMap  key 列索引 ，value 列名
      super.invokeHeadMap(headMap, context);
   }

  /* 数据逐行校验 excelEntity ： 每行数据*/
   @Override
   public void invoke(ExcelEntity excelEntity, AnalysisContext analysisContext) {
   // 映射实体数据校验
   StringBuilder stringBuilder = ValidatorUtil.validateEntity(excelEntity);

   }
  /* 单个sheet走完走此方法 用来save 避免内存溢出可以分批比保存 */
   @Override
   public void doAfterAllAnalysed(AnalysisContext analysisContext) {

   }
}

使用监听器

EasyExcel.read(excelFile.getInputStream(),classType,new AListener()).headRowNumber(headNumber).build();

多sheet导出

ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
数据结构
List<List<?>> data = null;
List<Class<?>> classType = null,
data.foreach(k-{
  WriteSheet mainSheet = EasyExcel
  .writerSheet(【sheet索引】 , 【sheet名称】)
  .head(classType)
  .build();
  excelWriter.write(k, mainSheet);
})
excelWriter.finish();

定义下拉框

public class BillSheetWriteHandler extends AbstractSheetWriteHandler implements SheetWriteHandler {
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        super.afterSheetCreate(writeWorkbookHolder, writeSheetHolder);
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        workbook.createCellStyle().setLocked(true);
        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
        if (value.length == 0) {
            throw new ServiceException("下拉选数组为空");
        }
        DataValidationConstraint constraint = helper.createExplicitListConstraint(value);
        /* 指定行列约束 */
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        // 阻止输入非下拉选项的值
        dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        dataValidation.setShowErrorBox(true);
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.createErrorBox("提示", "此值与单元格定义格式不一致");
        writeSheetHolder.getSheet().addValidationData(dataValidation);
        /* 冻结窗格 目前不生效 */
        writeSheetHolder.getSheet().createFreezePane(0, 1, 0, 1);
        /* 默认筛选条件 */
        writeSheetHolder.getSheet().setAutoFilter(CellRangeAddress.valueOf("1:1"));
    }
}

冻结窗格 有点问题，暂时利用反射修改Excel列长度来隐藏列，如有大佬知道请及时指出。