一、官方文档地址
/easyexcel/doc/easyexcel#63093516maven坐标,版本同步/alibaba/easyexcel<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.6</version></dependency>
二、读取Excel文件
1、准备读入的Excel,如下图所示:
2、定义实体
ExcelProperty:定义需要处理的字段,未加ExcelProperty的话就默认处理,如果希望忽略的话,则在类上面加注解@ExcelIgnoreUnannotatedExcelIgnore 默认所有字段都会和excel去匹配,加了这个注解会忽略该字段@Datapublic class DemoEntity {@ExcelProperty(value = "ID")private Long id;@ExcelProperty(value = "姓名")private String name;@ExcelProperty(value = "年龄")private Long age;}
3、重写读取的监听器
invokeHeadMap:可以针对Excel文件的头信息进行处理,例如比对Excel字段是否有缺失之类@Slf4jpublic class DemoListener extends AnalysisEventListener<DemoEntity> {@Getterprivate List<DemoEntity> demoEntityList = Lists.newArrayList();/*** 返回头数据,可以处理头文件** @param headMap* @param context*/@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {super.invokeHeadMap(headMap, context);}/**** 按行处理Excel文件* @param data* @param context*/@Overridepublic void invoke(DemoEntity data, AnalysisContext context) {demoEntityList.add(data);}/*** 分析完之后执行步骤** @param context*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {}}
处理头文件:示例中是用的简单的list对比进行,可以使用和实体的映射来进行处理
public class DemoListener extends AnalysisEventListener<DemoEntity> {@Getterprivate List<DemoEntity> demoEntityList = Lists.newArrayList();private List<String> standardHeadMap = Lists.newArrayList("ID", "姓名", "年龄" );/*** 返回头数据,可以处理头文件** @param headMap* @param context*/@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {super.invokeHeadMap(headMap, context);if (!MapUtils.isEmpty(headMap)) {Collection<String> headVal = headMap.values();StringBuilder diffBuilder = new StringBuilder().append("字段缺失");//差异字段总数int diffSize = 0;//以模版表头为准for (String index : standardHeadMap) {if (!headVal.contains(index)) {diffBuilder.append(" ").append(index);diffSize++;}}if (diffSize > 0) {log.error(diffBuilder.toString());throw new ExcelAnalysisException(diffBuilder.toString());}}}/**** 按行处理Excel文件* @param data* @param context*/@Overridepublic void invoke(DemoEntity data, AnalysisContext context) {demoEntityList.add(data);}/*** 分析完之后执行步骤** @param context*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {}}
4、读取文件工具类
/*** 读取EXCEl文件** @param fis* @param listener:*/public static void readExcel(InputStream fis, DemoListener listener) {ExcelReader excelReader = EasyExcel.read(fis, DemoEntity.class, listener).build();ReadSheet readSheet = EasyExcel.readSheet(0).build();excelReader.read(readSheet);// 这里千万别忘记关闭,读的时候会创建临时文件,不然磁盘会崩的excelReader.finish();}
5、测试及执行结果
代码如下:public static void main(String[] args) throws Exception {String path = "/Users/allen/Documents/book/col/demo.xlsx";File file = new File(path);InputStream inputStream = new FileInputStream(file);DemoListener listener = new DemoListener();readExcel(inputStream, listener);List<DemoEntity> list = listener.getDemoEntityList();System.out.println(JSON.toJSONString(list));}
处理结果 sheetNo:代表读取第几个sheetsheetName:代表读取的sheet的名称list:解析成功的结果集