前言
- 针对数万以上数据量的导出,使用EasyExcel,提升导出速度,降低服务器的内存占用.
- 导出时先上传至文件存储服务,再将文件地址返回给前端,避免浏览器直接处理文件流.
代码部分
pom文件
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
工具类
public static FileInputStream exportExcelStream(Class<?> clazz, List<?> list) {
try {
String tmp = System.getProperty("java.io.tmpdir");
String fileName = String.valueOf(new Date().getTime());
File file = new File(tmp + fileName + ".xlsx");
FileOutputStream fileOutputStream = new FileOutputStream(file);
ExcelWriter excelWriter = write(fileOutputStream)
.registerWriteHandler(new ExcelColumnWidthHandler())
.registerWriteHandler(new ExcelSheetWriteHandler())
.registerWriteHandler(new ExcelStyleHandler())
.build();
WriteSheet writeSheet = writerSheet(0, fileName).head(clazz).build();
excelWriter.write(list, writeSheet);
excelWriter.finish();
return new FileInputStream(file);
} catch (Exception e) {
throw new ServiceException("导出Excel异常");
}
}
@PostMapping("/exportBill")
public Result exportBill() throws IOException {
BillPageVo vo = new BillPageVo();
vo.setCompanyName("");
vo.setThirdBillNo("");
vo.setCompanyCode("");
vo.setBillNo("");
vo.setGoodsName("");
FileInputStream fileInputStream = ExcelUtil.exportExcelStream(BillPageVo.class, Collections.singletonList(vo));
String url = ossUtil.upload("test/excel/" + "车辆运单导出.xlsx", fileInputStream);
fileInputStream.close();
return Result.success(url);
}
easyexcel配置
public class ExcelColumnWidthHandler extends AbstractColumnWidthStyleStrategy {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if (Boolean.TRUE.equals(isHead)) {
int columnWidth = cell.getStringCellValue().length();
columnWidth = Math.max(columnWidth * 2, 18);
if (columnWidth > 255) {
columnWidth = 255;
}
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
public class ExcelSheetWriteHandler implements SheetWriteHandler {
private static final Integer COLUMN = 100;
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
for (int i = 0; i < COLUMN; i++) {
SXSSFSheet sxssfSheet = (SXSSFSheet) writeSheetHolder.getSheet();
CellStyle cellStyle = writeWorkbookHolder.getCachedWorkbook().createCellStyle();
cellStyle.setDataFormat((short) 49);
sxssfSheet.setDefaultColumnStyle(i, cellStyle);
}
}
}
public class ExcelStyleHandler extends AbstractVerticalCellStyleStrategy {
@Override
protected WriteCellStyle contentCellStyle(Head head) {
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteFont.setFontName("等线");
contentWriteCellStyle.setWriteFont(contentWriteFont);
return contentWriteCellStyle;
}
}