一.Excel导入导出的应用场景
1.数据导入：减轻录入的工作量
2.数据导出：统计信息归档
3.数据传输：异构系统之间数据传输
官网地址：EasyExcel · 语雀 (yuque.com)
二、EasyExcel简介
1.EasyExcel特点
Java领域解析，生成Excel比较有名的框架有Apache poi,jxl等，但他们都存在一个严重的问题就是非常的耗内存，如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或者JVM频繁的full gc.
EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单，节省内存著称，EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。
EasyExcel采用一行一行的解析模式，并将一行的解析结果以观察者的模式通知处理（AnalysisEventListener）。
try (OutputStream os = response.getOutputStream()) {
String sheetName = "xxxxx";
String fileName = URLEncoder.encode("xxxxx" + System.currentTimeMillis(), "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// aaaaaaa标题
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), aaaaaaa.class).build();
// 将sheetName写入，不为标题设置
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).needHead(Boolean.FALSE).build();
// 设置标红
WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).registerWriteHandler(new ExportWriterHandlerExcel()).build();
// 写入会创建头
excelWriter.write(excelVOS, writeSheet, writeTable0);
excelWriter.finish();
os.flush();
} catch (IOException e) {
log.error(e," ExportWriterHandlerExcel is error",e.getMessage());
}
实现CellWriteHandler
实现CellWriteHandler 自定义单元格样式处理器，可支持字体样式、背景颜色、边框样式、对齐方式、自动换行。
public class ExportWriterHandlerExcel extends ExcelWriteHandler implements CellWriteHandler {
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
// 跳过标题
if (isHead || cell.getRowIndex() < 3) {
return;
}
// 最后一个才可以执行下面代码
if (!"name".equals(head.getFieldName())) {
return;
}
Row row = cell.getRow();
if (null == row) {
return;
}
// 获取列从0开始
Cell endTime = row.getCell(5);
if (null != endTime && !StringUtils.isEmpty(endTime.getStringCellValue())) {
// 拆分时间
String[] split = endTime.getStringCellValue().split("-");
long parseLong = DateUtil.parse(split[1]).getTime();
if (DateUtil.date().getTime() > parseLong) {
// 标红方法
setColor(endTime, writeSheetHolder);
}
}
Cell targetMoney = row.getCell(6);
Cell donationMoney = row.getCell(7);
if (null != targetMoney && !StringUtils.isEmpty(targetMoney.getStringCellValue()) && null != donationMoney && !StringUtils.isEmpty(donationMoney.getStringCellValue())) {
BigDecimal target = new BigDecimal(targetMoney.getStringCellValue());
BigDecimal donation = new BigDecimal(donationMoney.getStringCellValue());
if (donation.compareTo(target) > 0) {
setColor(donationMoney, writeSheetHolder);
}
}
}
}
ExcelWriteHandler自定义类标红方法
public class ExcelWriteHandler {
protected void setColor(Cell cell, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
Workbook workbook = sheet.getWorkbook();
Font font = workbook.createFont();
font.setFontHeightInPoints((short)12);
font.setColor(IndexedColors.RED.getIndex());
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
}
实体类注解示例
//excel宽度
@ColumnWidth(20)
// 颜色字体大小
@HeadFontStyle(fontHeightInPoints = 15)
//列名
@ExcelProperty("名称")
双数据
try (OutputStream os = response.getOutputStream();) {
String sheetName = "xxxxx";
String fileName = URLEncoder.encode("xxxxxx" + System.currentTimeMillis(), "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// 大列表头 FirstHeadVO
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), FirstHeadVO.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).needHead(Boolean.FALSE).build();
WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).build();
WriteTable writeTable1 = EasyExcel.writerTable(1).needHead(Boolean.TRUE).registerWriteHandler(new FirstHeadVOWriterHandlerExcel()).build();
//小表头
writeTable0.setClazz(SecondExcelVO.class);
// 第一次写入会创建头
excelWriter.write(totalList, writeSheet, writeTable0);
// 第二次写如也会创建头，然后在第一次的后面写入数据
excelWriter.write(getFirstHeadVO(dataPageList.getDataList()), writeSheet, writeTable1);
excelWriter.finish();
os.flush();
} catch (IOException e) {
log.error("call method ,{},{}", e, e.getMessage());
}
