背景
曾几何时，我们总是提心吊胆的使用POI来处理Excel文件，一个3M的文件需要100多兆的内存空间。然而现在我们可以对它Say No，因为阿里巴巴的Easyexcel来了。
Maven坐标
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.4</version>
</dependency>
示例代码包含以下功能：（1）自定义列宽的实现；（2）单元格字体颜色根据单元格的值动态调整。
public static void downloadExcel(String path, List<List<String>> heads, List<List<Object>> datas) {
OutputStream outputStream = null;
try {
outputStream = new FileOutputStream(path);
CustomCellStyleStrategy styleStrategy = getStyleStrategy(); // 列宽设置
CustomColumnWidthStyleStrategy columnWidthStyleStrategy = new CustomColumnWidthStyleStrategy();
//第一个Sheet
WriteSheet writeSheet = EasyExcel.writerSheet(0, "信息").head(heads).build();
// 如果是浏览器下载, 需要设置不关闭流
ExcelWriter excelWriter = EasyExcel.write(outputStream).autoCloseStream(Boolean.FALSE)
.excelType(ExcelTypeEnum.XLSX).registerWriteHandler(styleStrategy)
.registerWriteHandler(columnWidthStyleStrategy).build();
excelWriter.write(datas, writeSheet);
excelWriter.finish();
} catch (Exception e) {
} finally {
// 如果是浏览器下载不需要关闭流
IOUtils.closeQuietly(outputStream)
}
}
参数path是Excel下载地址；heads是列表的头；datas是列表数据；
样式设置
public static CustomCellStyleStrategy getStyleStrategy() {
//表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //背景色
headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND); headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); // 字体策略
WriteFont headWriteFont = new WriteFont();
// 字体大小
headWriteFont.setFontName("微软雅黑");
headWriteFont.setBold(true);
headWriteFont.setFontHeightInPoints((short) 10);
headWriteCellStyle.setWriteFont(headWriteFont); //设置 自动换行
// headWriteCellStyle.setWrapped(true);
//设置 垂直居中
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置内容靠居中对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontName("微软雅黑");
contentWriteFont.setBold(false);
contentWriteFont.setFontHeightInPoints((short) 9);
contentWriteCellStyle.setWriteFont(contentWriteFont); contentWriteCellStyle.setWrapped(true);
CustomCellStyleStrategy horizontalCellStyleStrategy = new CustomCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
CustomCellStyleStrategy单元格字体颜色动态调整
public class CustomCellStyleStrategy extends AbstractCellStyleStrategy {
private WriteCellStyle headWriteCellStyle;
private WriteCellStyle contentWriteCellStyle;
private CellStyle headCellStyle;
public CustomCellStyleStrategy(WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {
this.headWriteCellStyle = headWriteCellStyle;
this.contentWriteCellStyle = contentWriteCellStyle;
} protected void initCellStyle(Workbook workbook) {
if (this.headWriteCellStyle != null) {
this.headCellStyle = StyleUtil.buildHeadCellStyle(workbook, this.headWriteCellStyle);
} } protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
if (this.headCellStyle != null) {
cell.setCellStyle(this.headCellStyle);
} } protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
} private void setContentCellStyle(WriteSheetHolder writeSheetHolder, Cell cell, Head head, Integer relativeRowIndex) {
Workbook workbook = writeSheetHolder.getSheet().getWorkbook(); WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontName("微软雅黑");
contentWriteFont.setBold(false);
contentWriteFont.setFontHeightInPoints((short) 9);
int columIndex = cell.getColumnIndex();
String value = cell.getStringCellValue();
double doubleValue = Double.parseDouble(value);
if (doubleValue < 0.0) {
contentWriteFont.setColor(IndexedColors.GREEN.getIndex());
} else {
contentWriteFont.setColor(IndexedColors.RED.getIndex());
}
contentWriteCellStyle.setWriteFont(contentWriteFont);
CellStyle cellStyle = StyleUtil.buildContentCellStyle(workbook, contentWriteCellStyle);
cell.setCellStyle(cellStyle);
}
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if (isHead != null && head != null) {
if (isHead.booleanValue()) {
this.setHeadCellStyle(cell, head, relativeRowIndex);
} else {
this.setContentCellStyle(writeSheetHolder, cell, head, relativeRowIndex);
}
}
}
}
CustomColumnWidthStyleStrategy列宽设置
public class CustomColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
protected void setColumnWidth(WriteSheetHolder writeSheetHolder,
List<CellData> cellDataList, Cell cell,
Head head, Integer relativeRowIndex, Boolean isHead) {
if (!isHead) {
return;
} int columIndex = cell.getColumnIndex();
// 设置列的宽度
writeSheetHolder.getSheet().setColumnWidth(columIndex,
ColumnEnum.getWidthByIndex(columIndex));
}
}
ColumnEnum枚举类，只有核心代码，其余代码自己补齐
COLUMN_0(0, "字段1", 8 * 256),
COLUMN_1(1, "字段2", 10 * 256);
public static int getWidthByIndex(int index) {
for (ColumnEnum item : ColumnEnum.values()) {
if (item.index == index) {
return item.width;
} } return 10 * 256;
}
数据构造
public static void main(String[] args) {
String path = "D:/export/test.xlsx";
List<List<String>> heads = new ArrayList<>();
heads.add(Arrays.asList("字段1"));
heads.add(Arrays.asList("字段2"));
List<List<Object>> datas = new ArrayList<>();
List<Object> rows1 = Arrays.asList("1", "-1");
datas.add(rows1); List<Object> rows2 = Arrays.asList("-2", "2");
datas.add(rows2);
downloadExcel(path, heads, datas);
}
