EasyExcel 寫入（匯出）
網際網路的精髓就是共享，可以共享技術、共享經驗、共享情感、共享快樂~
很多年前就有這個想法了，從事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));
}