背景介绍最近要改一个导出的功能,在原有的基础上,在导出一份明细数据,要求导出内容加在原有 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();// 数据落地到OSSString 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 注解就行了 。它就能自动创建列头 , 并将数据对应写入 。
- @ColumnWidth 列宽度
- @ExcelIgnore 代表不用导出的属性
- DateTimeFormat 日期格式化
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 枚举类 。就这样就完成了 。导出效果图如下:
文章插图
遇到的坑
- 版本问题
文章插图
<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
推荐阅读
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- VirtualBox 下 CentOS7 静态 IP 的配置 → 多次踩坑总结,蚌埠住了!
- ElasticSearch这些坑记得避开
- 微信长截屏怎么操作(截取聊天记录长图)
- 微信长截图怎么截(截取聊天记录长图)
- 微信好友删除怎么找回(恢复1-5年聊天记录)
- 使用 StringUtils.split 的坑
- Pictionary 方法记录
- Programiranje 方法记录
- 微信如何添加自己好友(找回添加好友记录)
- 用微信账号怎么添加好友(微信怎么查自己主动添加好友记录)