EasyExcel的使用

1
2
3
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.0-beta2</version>

表头的class(固定版)

JavaBean对应Excel的表头
com.alibaba.excel.annotation.@ExcelProperty
value=”表头的Name”,index=第几列

1
2
3
4
5
6
import com.alibaba.excel.annotation;
@lombok.Data
public class ExcelTitle{
  @ExcelProperty(value="第一列表头内容",index=0)
  private String firstTitle;
}

这个与Excel对应的JavaBean即可以读取也可以写入

read

see

静态读

//DemoExceptionListener

1
EasyExcel.read(excleFileName, ExcelTitle.class, new DemoExceptionListener()).sheet().doRead();

动态读

1
2
3
4
AnalysisEventListener<Map<Integer,String>> listener;
EasyExcel.read(excleFileName, listener).sheet().doRead();
//or
List<Map<Integer, String>> listMap = EasyExcel.read(excleFileName).sheet().doReadSync();

write

静态写

1
EasyExcel.write(writeFile,ExcelTitle.class).registerWriteHandler(getDefaultExcelHead()).sheet(0,sheetName).doWrite(List<?> data);

动态写

1
2
3
4
ExcelWriter excelWriter;
WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo,sheetName).registerWriteHandler(getDefaultExcelHead()).head(dynamicHead).build();
excelWriter.write(List<?> data,writeSheet);
excelWriter.finish();

自定义样式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
public HorizontalCellStyleStrategy getDefaultExcelHead(){
//头的字体设置
WriteFont headWriteFont = new WriteFone();
headWriteFont.setBold(false);//取消粗体
headWriteFont.setFontName("宋体");//此处可直接指定使用的字体名词
headWriteFont.setFontHeightInPoints((short)11);//设置字体大小
//设置头
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());//颜色修改为白色
headWriteCellStyle.setWrapped(false);//取消换行
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);//设置1边框
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);//左对齐
headWriteCellStyle.setLeftBorderColor(IndexedColors.WHITE1.getIndex());//修改边框颜色
headWriteCellStyle.setRightBorderColor(IndexedColors.WHITE1.getIndex());
headWriteCellStyle.setTopBorderColor(IndexedColors.WHITE1.getIndex());
headWriteCellStyle.setBottomBorderColor(IndexedColors.WHITE1.getIndex());
//内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//这里需要指定FillPatternType为FillPatternType.SOLID_FOREGROUND不然无法显示背景颜色.头默认了FillPatternType所以可以不指定
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());//背景绿色
WriteFont contentWriteFont = new WriteFont();
//字体大小
contentWriteFont.setFontHeightInPoints((short)20);
contentWriteCellStyle.setWriteFont(contentWriteFont);
return new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);
}