通过注解方式来读取，既可以指定列的下表，也可以通过列名来映射，但是两者只能取一个。
/** * 读取实体类 */ public class UserReadEntity { @ExcelProperty(value = "姓名") private String name; /** * 强制读取第三个 这里不建议 index 和 name 同时用，要么一个对象只用index，要么一个对象只用name去匹配 */ @ExcelProperty(index = 1) private int age; @DateTimeFormat("yyyy-MM-dd HH:mm:ss") @ExcelProperty(value = "操作时间") private Date time; //set、get... }
public static void main(String[] args) throws FileNotFoundException { //同步读取文件内容 FileInputStream inputStream = new FileInputStream(new File("/Users/panzhi/Documents/easyexcel-user1.xls")); List<UserReadEntity> list = EasyExcel.read(inputStream).head(UserReadEntity.class).sheet().doReadSync(); System.out.println(JSONArray.toJSONString(list)); }
运行程序，输出结果如下：
[{"age":20,"name":"张三0","time":1616920360000},{"age":21,"name":"张三1","time":1616920360000},{"age":22,"name":"张三2","time":1616920360000},{"age":23,"name":"张三3","time":1616920360000},{"age":24,"name":"张三4","time":1616920360000},{"age":25,"name":"张三5","time":1616920360000},{"age":26,"name":"张三6","time":1616920360000},{"age":27,"name":"张三7","time":1616920360000},{"age":28,"name":"张三8","time":1616920360000},{"age":29,"name":"张三9","time":1616920360000}]
2.3.2、动态监听器读取文件
动态监听器读取文件，与上面的方式有一个明显的区别是，我们需要重新写一个实现类，来监听 easyexcel 一行一行解析出来的数据，然后将数据封装出来，基于此，我们可以编写一套动态的导入工具类，详细工具类会下面介绍到，示例代码如下：
/** * 创建一个监听器，继承自AnalysisEventListener */ public class UserDataListener extends AnalysisEventListener<Map<Integer, String>> { private static final Logger LOGGER = LoggerFactory.getLogger(UserDataListener.class); /** * 表头数据 */ private List<Map<Integer, String>> headList = new ArrayList<>(); /** * 数据体 */ private List<Map<Integer, String>> dataList = new ArrayList<>(); /** * 这里会一行行的返回头 * * @param headMap * @param context */ @Override public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) { LOGGER.info("解析到一条头数据:{}", JSON.toJSONString(headMap)); headList.add(headMap); } /** * 这个每一条数据解析都会来调用 * * @param data * one row value. Is is same as {@link AnalysisContext#readRowHolder()} * @param context */ @Override public void invoke(Map<Integer, String> data, AnalysisContext context) { LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data)); dataList.add(data); } /** * 所有数据解析完成了 都会来调用 * * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) { LOGGER.info("所有数据解析完成！"); } public List<Map<Integer, String>> getHeadList() { return headList; } public List<Map<Integer, String>> getDataList() { return dataList; } }
public static void main(String[] args) throws FileNotFoundException { FileInputStream inputStream = new FileInputStream(new File("/Users/panzhi/Documents/easyexcel-user1.xls")); //初始化一个监听器 UserDataListener userDataListener = new UserDataListener(); //读取文件数据 EasyExcel.read(inputStream, userDataListener).sheet().doRead(); System.out.println("表头：" + JSONArray.toJSONString(userDataListener.getHeadList())); System.out.println("数据体：" + JSONArray.toJSONString(userDataListener.getDataList())); }
运行程序，输出结果如下：
表头：[{0:"姓名",1:"年龄",2:"操作时间"}] 数据体：[{0:"张三0",1:"20",2:"2021-03-28 16:32:40"},{0:"张三1",1:"21",2:"2021-03-28 16:32:40"},{0:"张三2",1:"22",2:"2021-03-28 16:32:40"},{0:"张三3",1:"23",2:"2021-03-28 16:32:40"},{0:"张三4",1:"24",2:"2021-03-28 16:32:40"},{0:"张三5",1:"25",2:"2021-03-28 16:32:40"},{0:"张三6",1:"26",2:"2021-03-28 16:32:40"},{0:"张三7",1:"27",2:"2021-03-28 16:32:40"},{0:"张三8",1:"28",2:"2021-03-28 16:32:40"},{0:"张三9",1:"29",2:"2021-03-28 16:32:40"}]
其中key表示列下表！
2.3.3、复杂表头读取
在实际的开发中，我们还会遇到复杂表头的数据读取，以如下表头为例，我们应该如何读取呢？
如果你是采用注解的方式导出的文件，同样也可以通过注解方式来读取，例如上文中，我们是使用如下实体类生成的文件，我们也可通过这个类读取文件！
public class UserEntity { @ExcelProperty(value = "班级") private String className; @ExcelProperty({"学生信息", "姓名"}) private String name; @ExcelProperty({"学生信息", "年龄"}) private int age; @DateTimeFormat("yyyy-MM-dd HH:mm:ss") @ExcelProperty({"学生信息", "入学时间"}) private Date time; //set、get }
//读取文件 List<UserEntity> list = EasyExcel.read(filePath).head(UserEntity.class).sheet().doReadSync(); System.out.println(JSONArray.toJSONString(list));
读取结果如下：
[{"age":20,"className":"一年级～1班","name":"张三0","time":1618719961000},{"age":21,"className":"一年级～1班","name":"张三1","time":1618719961000},{"age":22,"className":"一年级～1班","name":"张三2","time":1618719961000},{"age":23,"className":"一年级～1班","name":"张三3","time":1618719961000},{"age":24,"className":"一年级～1班","name":"张三4","time":1618719961000},{"age":25,"className":"一年级～1班","name":"张三5","time":1618719961000},{"age":26,"className":"一年级～1班","name":"张三6","time":1618719961000},{"age":27,"className":"一年级～1班","name":"张三7","time":1618719961000},{"age":28,"className":"一年级～1班","name":"张三8","time":1618719961000},{"age":29,"className":"一年级～1班","name":"张三9","time":1618719961000}]
如果你是使用动态参数化来生成文件，那么这个时候可以采用动态监听器的方式来读取文件，在读取的时候需要指定数据所在行，示例代码如下：
public static void main(String[] args) throws FileNotFoundException { FileInputStream inputStream = new FileInputStream(new File("/Users/panzhi/Documents/easyexcel-export-user4.xlsx")); //初始化一个监听器 UserDataListener userDataListener = new UserDataListener(); //读取文件数据，指定数据所在行使用headRowNumber方法 EasyExcel.read(inputStream, userDataListener).sheet().headRowNumber(2).doRead(); System.out.println("表头：" + JSONArray.toJSONString(userDataListener.getHeadList())); System.out.println("数据体：" + JSONArray.toJSONString(userDataListener.getDataList())); }
读取结果如下：
表头：[{0:"班级",1:"学生信息",2:"学生信息",3:"学生信息"},{0:"班级",1:"姓名",2:"年龄",3:"入学时间"}] 数据体：[{0:"一年级～1班",1:"张三0",2:"20",3:"2021-04-18 12:26:01"},{0:"一年级～1班",1:"张三1",2:"21",3:"2021-04-18 12:26:01"},{0:"一年级～1班",1:"张三2",2:"22",3:"2021-04-18 12:26:01"},{0:"一年级～1班",1:"张三3",2:"23",3:"2021-04-18 12:26:01"},{0:"一年级～1班",1:"张三4",2:"24",3:"2021-04-18 12:26:01"},{0:"一年级～1班",1:"张三5",2:"25",3:"2021-04-18 12:26:01"},{0:"一年级～1班",1:"张三6",2:"26",3:"2021-04-18 12:26:01"},{0:"一年级～1班",1:"张三7",2:"27",3:"2021-04-18 12:26:01"},{0:"一年级～1班",1:"张三8",2:"28",3:"2021-04-18 12:26:01"},{0:"一年级～1班",1:"张三9",2:"29",3:"2021-04-18 12:26:01"}]
三、动态导出导入工具类封装
在实际使用开发中，我们不可能每来一个 excel 导入导出需求，就编写一个方法，而且很多业务需求都是动态导入导出，没办法基于实体类注解的方式来读取文件或者写入文件
因此，基于动态参数化生成文件和动态监听器读取文件方法，我们可以单独封装一套动态导出导出工具类，省的我们每次都需要重新编写大量重复工作，以下就是小编我在实际使用过程，封装出来的工具类，在此分享给大家！
- 动态导出工具类
public class DynamicEasyExcelExportUtils { private static final Logger log = LoggerFactory.getLogger(DynamicEasyExcelExportUtils.class); private static final String DEFAULT_SHEET_NAME = "sheet1"; /** * 动态生成导出模版(单表头) * @param headColumns 列名称 * @return excel文件流 */ public static byte[] exportTemplateExcelFile(List<String> headColumns){ List<List<String>> excelHead = Lists.newArrayList(); headColumns.forEach(columnName -> { excelHead.add(Lists.newArrayList(columnName)); }); byte[] stream = createExcelFile(excelHead, new ArrayList<>()); return stream; } /** * 动态生成模版(复杂表头) * @param excelHead 列名称 * @return */ public static byte[] exportTemplateExcelFileCustomHead(List<List<String>> excelHead){ byte[] stream = createExcelFile(excelHead, new ArrayList<>()); return stream; } /** * 动态导出文件 * @param headColumnMap 有序列头部 * @param dataList 数据体 * @return */ public static byte[] exportExcelFile(LinkedHashMap<String, String> headColumnMap, List<Map<String, Object>> dataList){ //获取列名称 List<List<String>> excelHead = new ArrayList<>(); if(MapUtils.isNotEmpty(headColumnMap)){ //key为匹配符，value为列名，如果多级列名用逗号隔开 headColumnMap.entrySet().forEach(entry -> { excelHead.add(Lists.newArrayList(entry.getValue().split(","))); }); } List<List<Object>> excelRows = new ArrayList<>(); if(MapUtils.isNotEmpty(headColumnMap) && CollectionUtils.isNotEmpty(dataList)){ for (Map<String, Object> dataMap : dataList) { List<Object> rows = new ArrayList<>(); headColumnMap.entrySet().forEach(headColumnEntry -> { if(dataMap.containsKey(headColumnEntry.getKey())){ Object data = dataMap.get(headColumnEntry.getKey()); rows.add(data); } }); excelRows.add(rows); } } byte[] stream = createExcelFile(excelHead, excelRows); return stream; } /** * 生成文件 * @param excelHead * @param excelRows * @return */ private static byte[] createExcelFile(List<List<String>> excelHead, List<List<Object>> excelRows){ try { if(CollectionUtils.isNotEmpty(excelHead)){ ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); EasyExcel.write(outputStream).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .head(excelHead) .sheet(DEFAULT_SHEET_NAME) .doWrite(excelRows); return outputStream.toByteArray(); } } catch (Exception e) { log.error("动态生成excel文件失败，headColumns：" + JSONArray.toJSONString(excelHead) + "，excelRows：" + JSONArray.toJSONString(excelRows), e); } return null; } /** * 导出文件测试 * @param args * @throws IOException */ public static void main(String[] args) throws IOException { //导出包含数据内容的文件 LinkedHashMap<String, String> headColumnMap = Maps.newLinkedHashMap(); headColumnMap.put("className","班级"); headColumnMap.put("name","学生信息,姓名"); headColumnMap.put("sex","学生信息,性别"); List<Map<String, Object>> dataList = new ArrayList<>(); for (int i = 0; i < 5; i++) { Map<String, Object> dataMap = Maps.newHashMap(); dataMap.put("className", "一年级"); dataMap.put("name", "张三" + i); dataMap.put("sex", "男"); dataList.add(dataMap); } byte[] stream = exportExcelFile(headColumnMap, dataList); FileOutputStream outputStream = new FileOutputStream(new File("/Users/panzhi/Documents/easyexcel-export-user5.xlsx")); outputStream.write(stream); outputStream.close(); } }
