什么是 easyexcel
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便。
阿里版本源码(https://github.com/alibaba/easyexcel.git)
1. 添加源码
git clone https://github.com/stormzhai/easyexcel.git
1）修改写excel时的日期格式化的问题
2）添加了导入、导出是枚举的key、value转换支持
2. 定义实体
public class ExcelRowJavaModel extends BaseRowModel { （1）
@ExcelProperty(index = 0,value = "银行放款编号")
private int num;
@ExcelProperty(index = 1,value = "code")
private Long code;
@ExcelProperty(index = 2,value = "银行存放期期",format = "yyyy-MM-dd")（2）
private Date endTime;
@ExcelProperty(index = 3,value = "测试1")
private Double money;
@ExcelProperty(index = 4,value = "测试2")
private String times;
@ExcelProperty(index = 5,value = "测试3")
private int activityCode;
@ExcelProperty(index = 6,value = "测试4")
private Date date;
@ExcelProperty(index = 7,value = "测试5")
private Double lx;
@ExcelProperty(index = 8,value = "测试6")
private String name;
@ExcelProperty(index = 9,value = "性别",replace = "男_1,女_2")（3）
private int sex;
// 省略getter、setter
}
1）继承BaseRowModel
2）format：日期格式化
3）replace：枚举key和value
3. 定义事件解析器ExcelListener
public class ExcelListener extends AnalysisEventListener {
@Override
public void invoke(Object object, AnalysisContext context) {（1）
System.out.println("sheet:" + context.getCurrentSheet().getSheetNo() + ",row："
+ context.getCurrentRowNum() + ",data:" + object);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
1）每一行解析后数据处理，保存入库、校验等
4. 读取Excel
public static void main(String[] args) {
readExcel();
}
private static void readExcel() {
InputStream inputStream = null;
try {
inputStream = getInputStream("bb.xlsx");
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, null, excelListener);
reader.read(new Sheet(1, 1, ExcelRowJavaModel.class));
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
5. 生成Excel
public static void main(String[] args) {
writeExcel();
}
private static void writeExcel() {
OutputStream out = null;
try {
out = new FileOutputStream("c:/78.xlsx");
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
//写第一个sheet, sheet1 数据全是List<String> 无模型映射关系
Sheet sheet1 = new Sheet(1, 0,ExcelRowJavaModel.class);
writer.write(getData(), sheet1);
writer.finish();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static List<ExcelRowJavaModel> getData() {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
List<ExcelRowJavaModel> datas = new ArrayList<ExcelRowJavaModel>();
try {
ExcelRowJavaModel model = new ExcelRowJavaModel();
model.setNum(1);
model.setCode(1L);
model.setEndTime(simpleDateFormat.parse("2018-05-15"));
model.setMoney(1.0d);
model.setTimes("1");
model.setActivityCode(1);
model.setDate(new Date());
model.setLx(0.0);
model.setName("测试111");
model.setSex(1);
datas.add(model);
} catch (Exception e) {
e.printStackTrace();
}
return datas;
}
private static InputStream getInputStream(String fileName) {
return Thread.currentThread().getContextClassLoader().getResourceAsStream("" + fileName);
}
