最近要改一个导出的功能,在原有的基础上,在导出一份明细数据,要求导出内容加在原有 excel 的第二个 sheet 上。考虑到数据量还比较大,干脆引入阿里的 EasyExcel 来做。
下面我先上最终代码,再来说说我遇到的坑有哪些
public String doHandle() {
try(ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
com.alibaba.excel.ExcelWriter excelWriter = EasyExcel.write(outputStream).build()) {
List<SaleTransferSummaryRateExportVo> exportVos = queryAction.querySummaryExport();
List<SaleTransferClassRateExportVo> exportRateVos = queryAction.queryClassRate();
WriteSheet writeSheet = EasyExcel.writerSheet(0, "统计").head(SaleTransferSummaryRateExportVo.class).build();
excelWriter.write(exportVos, writeSheet);
WriteSheet rateWriteSheet = EasyExcel.writerSheet(1, "明细").head(SaleTransferClassRateExportVo.class).build();
excelWriter.write(exportRateVos, rateWriteSheet);
excelWriter.close();
// 数据落地到OSS
String resultPath = ossClient.uploadFile(outputStream.toByteArray(), ContentMediaEnum.XLSX.getName(), FileExtEnum.XLSX.getName());
return resultPath;
} catch (Exception e) {
return "";
}
}
我们项目是将文件传到 oss,然后去 oss 进行下载。也可以直接写入到文件或 response
public void doHandle() {
File file=new File("");
try(
com.alibaba.excel.ExcelWriter excelWriter = EasyExcel.write(file).build()) {
List<SaleTransferSummaryRateExportVo> exportVos = queryAction.querySummaryExport();
List<SaleTransferClassRateExportVo> exportRateVos = queryAction.queryClassRate();
WriteSheet writeSheet = EasyExcel.writerSheet(0, "统计").head(SaleTransferSummaryRateExportVo.class).build();
excelWriter.write(exportVos, writeSheet);
WriteSheet rateWriteSheet = EasyExcel.writerSheet(1, "明细").head(SaleTransferClassRateExportVo.class).build();
excelWriter.write(exportRateVos, rateWriteSheet);
} catch (Exception e) {
log.error("导出异常",e);
}
}
只需要修改 write 的参数即可。
主要的代码就完成了,那么数据的属性和 excel 列名称怎么对应上的呢?
在数据的实体类上加上@ExcelProperty 注解就行了。它就能自动创建列头,并将数据对应写入。
public class SaleTransferSummaryRateExportVo {
@ExcelProperty("老师昵称")
@ColumnWidth(10)
private String teacherName;
@ExcelProperty("大区")
private String regionName;
@ExcelProperty("小组")
private String groupName;
@ExcelProperty("创建时间")
@DateTimeFormat("yyyy-MM-dd")
private Date createTime;
}
写完之后觉得表格有点丑,于是又调了下样式。也是几个注解搞定
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 44)
@HeadFontStyle(fontHeightInPoints = 10)
@ContentFontStyle(fontHeightInPoints = 10)
public class SaleTransferSummaryRateExportVo {
@ExcelProperty("老师昵称")
private String teacherName;
@ExcelProperty("大区")
private String regionName;
@ExcelProperty("小组")
private String groupName;
@ExcelProperty("创建时间")
@DateTimeFormat("yyyy-MM-dd")
private Date createTime;
}
fillForegroundColor 的值就代表颜色,具体什么值代表什么颜色,可以参考 IndexedColors 枚举类。
就这样就完成了。导出效果图如下:
我最开始用的版本是这样的,因为项目里之前就引入了 poi
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.2</version>
</dependency>
版本不对的时候写入直接报错。
异常信息如下:
Exception in thread "main" com.alibaba.excel.exception.ExcelGenerateException: java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.setCellValue(Ljava/time/LocalDateTime;)V
at com.alibaba.excel.write.ExcelBuilderImpl.addContent(ExcelBuilderImpl.java:65)
at com.alibaba.excel.ExcelWriter.write(ExcelWriter.java:70)
at com.alibaba.excel.ExcelWriter.write(ExcelWriter.java:47)
at cn.jojo.sales.app.task.ExportSalesTransferSummaryTask.main(ExportSalesTransferSummaryTask.java:90)
Caused by: java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.setCellValue(Ljava/time/LocalDateTime;)V
at com.alibaba.excel.write.executor.AbstractExcelWriteExecutor.converterAndSet(AbstractExcelWriteExecutor.java:95)
at com.alibaba.excel.write.executor.ExcelWriteAddExecutor.addJavaObjectToExcel(ExcelWriteAddExecutor.java:174)
at com.alibaba.excel.write.executor.ExcelWriteAddExecutor.addOneRowOfDataToExcel(ExcelWriteAddExecutor.java:82)
at com.alibaba.excel.write.executor.ExcelWriteAddExecutor.add(ExcelWriteAddExecutor.java:58)
at com.alibaba.excel.write.ExcelBuilderImpl.addContent(ExcelBuilderImpl.java:59)
... 3 more
excelWriter.close();
我之前因为 excelWriter 的定义是是写在 try 里的,所以没有 close,但是我的用法又是将 excelWriter 写入到字节流,然后字节流传到 oss,而且这个步骤也是在 try 里面。就导致了我一直写入不成功,后来才发现,浪费了一点时间。
书山有路勤为径,学海无涯苦作舟
版权说明 : 本文为转载文章, 版权归原作者所有 版权申明
原文链接 : https://www.cnblogs.com/javammc/p/16857425.html
内容来源于网络,如有侵权,请联系作者删除!