你要的不固定列excel导入导出,它来啦!(三)

通过注解方式来读取,既可以指定列的下表,也可以通过列名来映射,但是两者只能取一个。

/**
 * 读取实体类
 */
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、复杂表头读取

在实际的开发中,我们还会遇到复杂表头的数据读取,以如下表头为例,我们应该如何读取呢?

你要的不固定列excel导入导出,它来啦!(三)

如果你是采用注解的方式导出的文件,同样也可以通过注解方式来读取,例如上文中,我们是使用如下实体类生成的文件,我们也可通过这个类读取文件!

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();
    }
}
上一篇:WPF快速指导6:触发器


下一篇:实例:Netty 处理 TCP协议数据分包问题