海量数据Excel报表利器——EasyExcel(一 利用反射机制导出Excel)

EasyExcel 写入(导出)

互联网的精髓就是共享,可以共享技术、共享经验、共享情感、共享快乐~

很多年前就有这个想法了,从事IT行业时间也不短了,应该把自己工作和业余所学习的东西记录并分享出来,和有缘人一起学习和交流。

如果您是那个有缘人,请上岛一叙!爪哇岛随时欢迎您!


今天,咱们一起来看看使用EasyExcel做Excel的导出(数据写入到Excel中)。。。。。。

EasyExcel导出Excel在官网(EasyExcel官网-导出数据)上面已经有很多基础的例子,这些我再重复摘抄一遍就没有啥意义了,这部分大家可以根据自己的实际场景自己去官网查找例子代码即可;

本篇文章我主要分享的是:如何通过反射机制封装一些较为复杂的动态场景下的工具类,使用工具类来帮我们减少重复劳动,让生成报表变得很简单。

功能列表:

  1. 最简单的Excel导出
  2. 通过registerWriteHandler控制单元格样式
  3. 通过head自定义表头
  4. 合并表头(静态)
  5. 动态表头(动态合并表头 & 横向无限扩展列)
  6. 多sheet页

代码片段

@Data
public class Student {
    @ExcelIgnore
    private String stuId;

    @ExcelProperty("姓名", index=0)
    private String name;
    @ExcelProperty("学科", index=1)
    private String subject;
    @ExcelProperty("分数", index=2)
    private Double score;
}

public Object exportExcel(Class <? > excelBeanClass, List < List <? >> dataList, String title) {
    File file = new File(CommonConstants.CDN_FILE_LOCAL_REPORT);
    boolean mkdir = true;
    if(!file.exists()) {
        mkdir = file.mkdirs();
    }
    if(!mkdir) {
        return new ASOError(CommonConstants.ErrorEnum.OPRATION_FAIL.getCode(), "创建文件失败");
    }
    String fileName = title + "_" + System.currentTimeMillis() + ".xlsx";
    String filePath = CommonConstants.CDN_FILE_LOCAL_REPORT + File.separator + fileName;

    // 核心
    witeExcel(filePath, title, excelBeanClass, dataList);
    
    ObjectResponse < String > resp = new ObjectResponse < > ();
    String downloadPath = CommonConstants.CDN_FILE_REMOTE + "report" + File.separator + fileName;
    resp.setData(downloadPath);
    logger.info("generateReport: downloadPath={}", downloadPath);
    return resp;
}


  1. 最简单的Excel导出
public void witeExcel(String file, String title, Class <? > excelBeanClass, List < List <? >> dataList) {
    ExcelWriterBuilder write = EasyExcel.write(filePath, excelBeanClass);
    write.autoCloseStream(true).sheet(title).doWrite(dataList);
}

public static void main(String []args) {
   List < List <? >> data = new ArrayList();
   ......
   System.out.println("download url is :" + exportExcel(Student.class, data, "Excel名称"));
}
  1. 通过registerWriteHandler控制单元格样式
public void witeExcel(String file, String title, Class <?> excelBeanClass, List <List<?>> dataList, List<WriteHandler> writeHandlerList) {
    ExcelWriterBuilder write = EasyExcel.write(filePath, excelBeanClass);
    if(CollectionUtils.isNotEmpty(writeHandlerList)) {
        **writeHandlerList.forEach(write::registerWriteHandler);**
    }
    write.autoCloseStream(true).sheet(title).doWrite(dataList);
}

public static void main(String []args) {
   //获取头和内容的策略
   WriteCellStyle headWriteCellStyle = new WriteCellStyle();
   WriteFont headWriteFont = new WriteFont();
   headWriteCellStyle.setWriteFont(headWriteFont);
   // ...内容单元格也可以同样方式设置样式...
   WriteCellStyle contentWriteCellStyle = new WriteCellStyle();

   HorizontalCellStyleStrategy horizontalCellStyleStrategy =
        new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

   //列宽的策略,宽度是小单位
   Integer columnWidthArr[] = {3000, 3000, 2000, 6000};
   List<Integer> columnWidths = Arrays.asList(columnWidthArr);
   CustomSheetWriteHandler customSheetWriteHandler = new CustomSheetWriteHandler(columnWidths);

   //自定义单元格策略
   CustomCellWriteHandler  = new CustomCellWriteHandler(yellowRowsSet);

   List<WriteHandler> writeHandlerList = new ArrayList();
   writeHandlerList.add(horizontalCellStyleStrategy);
   writeHandlerList.add(customSheetWriteHandler);
   writeHandlerList.add(customCellWriteHandler);

   List<List<Student>> data = new ArrayList();
   ......
   System.out.println("download url is :" + exportExcel(Student.class, data, "Excel名称"));
}
  1. 通过head自定义表头
public void witeExcel(String file, String title, List<List<String>> head, List < List <? >> dataList) {
    ExcelWriterBuilder = EasyExcel.write(filePath);
    // head可以根据需要自定义
    **write.head(head);**
    write.autoCloseStream(true).sheet(title).doWrite(dataList);
}

public static void main(String []args) {
   List < List <? >> data = new ArrayList();
   ......
   System.out.println("download url is :" + exportExcel(Student.class, data, "Excel名称"));
}
  1. 合并表头(静态)
@Data
public class Student {
    @ExcelIgnore
    private String stuId;

    @ExcelProperty(value="姓名", index=0)
    private String name;
    @ExcelProperty(value="成绩, 学科", index=1)
    private String subject;
    @ExcelProperty(value="成绩, 分数", index=2)
    private Double score;
}
姓名 成绩
学科 分数
AAA 语文 100
  1. 动态表头(动态合并表头 & 横向无限扩展列)
//基于Student对象的配置完成表头合并(静态)
public void witeExcel(String mainTitle, Class <?> excelBeanClass) {
    Field[] fields = excelBeanClass.getDeclaredFields();
    for(Field field: fields) {
        ExcelProperty excelProperty = field.getDeclaredAnnotation(ExcelProperty.class);
        if(excelProperty != null) {//打了ExcelProperty注解的字段处理
            try {
                InvocationHandler excelH = Proxy.getInvocationHandler(excelProperty);
                Field excelF = excelH.getClass().getDeclaredField("memberValues");
                excelF.setAccessible(true);
                Map < String, Object > excelPropertyValues = (Map<String, Object>) excelF.get(excelH);
                excelPropertyValues.put("value", new String[] {mainTitle, excelProperty.value()[0]});
            } catch(Exception e) {
                //TODO:异常处理
            }
        }
    }
}

//基于Student对象对表头进行动态合并(动态)
public void dynamicHeaderByExcelProperty(String mainTitle, String secondTitle, Class <? > excelBeanClass) {
    Field[] fields = excelBeanClass.getDeclaredFields();
    for(Field field: fields) {
        ExcelProperty excelProperty = field.getDeclaredAnnotation(ExcelProperty.class);
        if(excelProperty != null) {
            try {
                InvocationHandler excelH = Proxy.getInvocationHandler(excelProperty);
                Field excelF = excelH.getClass().getDeclaredField("memberValues");
                excelF.setAccessible(true);
                Map <String, Object> excelPropertyValues = (Map <String, Object> ) excelF.get(excelH);
                excelPropertyValues.put("value", new String[] { mainTitle, secondTitle, excelProperty.value()[0]});
            } catch(Exception e) {
                //TODO:异常处理
            }
        }
    }
}

//自定义表头的单级动态合并及横向无限扩展列
public List<List<String>> dynamicHeaderByCustom(String mainTitle, Class <?> excelBeanClass) {
    List<List<String>> headList = new ArrayList<>();
    Field[] fields = excelBeanClass.getDeclaredFields();
    for(Field field: fields) {
        ExcelProperty excelProperty = field.getDeclaredAnnotation(ExcelProperty.class);
        if(excelProperty != null) {
            List <String> fieldHeadList = new ArrayList<> ();
            // TODO:待优化扩展,指定不同列的合并
            if(StringUtils.isNotBlank(mainTitle)) {
                fieldHeadList.add(mainTitle);
            }
            fieldHeadList.addAll(Arrays.asList(excelProperty.value()));
            headList.add(fieldHeadList);
        }
    }
    return headList;
}
//自定义表头的多级动态合并及横向无限扩展列
public List<List<String>> dynamicHeaderByCustom(String mainTitle, List <Class<?>> excelBeanClassList) {
    List<List<String>> headList = new ArrayList<>();
    System.out.println("excelBeanClassList.size()=" + excelBeanClassList.size());
    excelBeanClassList.forEach(v - > {
        headList.addAll(this.dynamicHeaderByCustom(mainTitle, v));
    });
    return headList;
}

public static void main(String[] args) {
    List < List <? >> data = new ArrayList();
    ......
    System.out.println("download url is :" + exportExcel("成绩", "科目", data, "Excel名称"));
}
姓名 成绩 A卷(动态) B卷(动态)
学科 分数 填空题 判断题 问答题 附加题 填空题 判断题 问答题 附加题
AAA 数学 110 20分 15分 45分 10分 20分 20分 50分 10分
  1. 多sheet页
public void witeExcel(Class<?> excelBeanClass, String title, List<ReportSheetInfo> sheets) {
    ExcelWriterBuilder write;
    if(excelBeanClass != null) {
        write = EasyExcel.write(filePath, targetClass);
    } else {
        write = EasyExcel.write(filePath);
    }
    ExcelWriter excelWriter = write.build();
    sheets.forEach(v - > {
        ExcelWriterSheetBuilder writeSheet = EasyExcel.writerSheet(sheets.indexOf(v), v.getSheetTitle());
        writeSheet.head(v.getHead());
        if(CollectionUtils.isNotEmpty(v.getWriteHandlerList())) {
            v.getWriteHandlerList().forEach(writeSheet::registerWriteHandler);
        }
        excelWriter.write(v.getDataList(), writeSheet.build());
    });
    excelWriter.finish();
    write.autoCloseStream(true);
}

// ReportSheetInfo类中定义writeHandlerList、dataList、head集合
public static void main(String[] args) {
    List<ReportSheetInfo> sheets = new ArrayList();
    ......
    System.out.println("download url is :" + exportExcel(Student.class, "Excel名称", sheets));
}

以上是关于EasyExcel的导出Excel封装,部分源码已贴出,需要完整源码的可以在下方评论留言,今天分享就到这里。。。。。。

爪哇岛盛产最前沿的IT技术栈,欢迎广大IT同仁入岛学习分享
posted @ 2021-07-10 17:05  岛主~it_rabbit  阅读(441)  评论(1编辑  收藏  举报