互联网的精髓就是共享，可以共享技术、共享经验、共享情感、共享快乐~
很多年前就有这个想法了，从事IT行业时间也不短了，应该把自己工作和业余所学习的东西记录并分享出来，和有缘人一起学习和交流。
如果您是那个有缘人，请上岛一叙！爪哇岛随时欢迎您！
今天，咱们一起来看看使用EasyExcel做Excel的导出（数据写入到Excel中）。。。。。。
EasyExcel导出Excel在官网（EasyExcel官网-导出数据）上面已经有很多基础的例子，这些我再重复摘抄一遍就没有啥意义了，这部分大家可以根据自己的实际场景自己去官网查找例子代码即可；
本篇文章我主要分享的是：如何通过反射机制封装一些较为复杂的动态场景下的工具类，使用工具类来帮我们减少重复劳动，让生成报表变得很简单。
功能列表：
- 最简单的Excel导出
- 通过registerWriteHandler控制单元格样式
- 通过head自定义表头
- 合并表头（静态）
- 动态表头（动态合并表头 & 横向无限扩展列）
- 多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;
}
- 最简单的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名称"));
}
- 通过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名称"));
}
- 通过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名称"));
}
- 合并表头（静态）
@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 |
- 动态表头（动态合并表头 & 横向无限扩展列）
//基于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分 |
- 多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));
}