Springboot 整合EasyPoi 使用ExcelExportEntity生成多sheet的excel文件

工作中接触到了这样一个场景,导出表的内容不一样  所以把内容放在了两个sheet里面,而且实际运用中,两个sheet表头由java后台自动生成,由于数据量比较大,想到了使用easypoi,一般情况下都是在实体类字段中写注解辅助生成表头,可是现在由于表头不固定,所以想到了使用ExcelExportEntity,下面贴代码,有不懂的小伙伴可以私聊哈,注释尽量写清楚

1.pom.xml
<!-- easypoi -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>

2.测试代码

@Test
    public void  test01() throws IOException {
        List<ExcelExportEntity> entityList = new ArrayList<>();
        //构造抬头
        ExcelExportEntity excelUserNameEntity = new ExcelExportEntity("用户名", "userName");
        excelUserNameEntity.setNeedMerge(true);
        entityList.add(excelUserNameEntity);
        ExcelExportEntity excelFileNameEntity = new ExcelExportEntity("文件名称", "fileName");
        excelFileNameEntity.setNeedMerge(true);
        entityList.add(excelFileNameEntity);

        ExcelExportEntity excelFileTypeEntity = new ExcelExportEntity("文件类型", "fileType");
        excelFileTypeEntity.setNeedMerge(true);
        entityList.add(excelFileTypeEntity);
        ExcelExportEntity excelFileSizeEntity = new ExcelExportEntity("文件大小", "fileSize");
        excelFileSizeEntity.setNeedMerge(true);
        entityList.add(excelFileSizeEntity);
        ExcelExportEntity excelPhotoTimeEntity = new ExcelExportEntity("拍摄时间", "photoTime");
        excelPhotoTimeEntity.setNeedMerge(true);
        entityList.add(excelPhotoTimeEntity);
        ExcelExportEntity excelCheckStateEntity = new ExcelExportEntity("审核状态", "checkState");
        excelCheckStateEntity.setNeedMerge(true);
        entityList.add(excelCheckStateEntity);

        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        List<Map<String, Object>> list1 = new ArrayList<Map<String, Object>>();

        // 第一个表数据构造
        Map<String,Object> map1 = new HashMap<>();
        map1.put("userName","李工");
        map1.put("fileName","2019111_11.jpg");
        map1.put("fileType","图片");
        map1.put("fileSize","123KB");
        map1.put("photoTime","2019-11-11 11:11:11");
        map1.put("checkState","未审核");
        Map<String,Object> map2 = new HashMap<>();
        map2.put("userName","邱工");
        map2.put("fileName","66666.jpg");
        map2.put("fileType","图片");
        map2.put("fileSize","123KB");
        map2.put("photoTime","2019-11-11 11:11:11");
        map2.put("checkState","未审核");
        list.add(map1);
        list.add(map2);

        //第二个表数据构造
        Map<String,Object> map3 = new HashMap<>();
        map3.put("userName","李工");
        map3.put("fileName","2019111_11.mp4");
        map3.put("fileType","视频");
        map3.put("fileSize","123MB");
        map3.put("photoTime","2019-11-11 11:11:11");
        map3.put("checkState","已审核");
        Map<String,Object> map4 = new HashMap<>();
        map4.put("userName","夏冰冰");
        map4.put("fileName","xiabingbing.mp4");
        map4.put("fileType","视频");
        map4.put("fileSize","166MB");
        map4.put("photoTime","2019-11-11 11:11:11");
        map4.put("checkState","已审核");
        list1.add(map3);
        list1.add(map4);
        // sheet1设置
        Map<String, Object> sheet1ExportMap = new HashMap<>();
        sheet1ExportMap.put(NormalExcelConstants.CLASS, ExcelExportEntity.class);
        sheet1ExportMap.put(NormalExcelConstants.DATA_LIST, list);
        sheet1ExportMap.put(NormalExcelConstants.PARAMS, new ExportParams("遥信表", "遥信"));
        //这边为了方便,sheet1和sheet2用同一个表头(实际使用中可自行调整)
        sheet1ExportMap.put(NormalExcelConstants.MAP_LIST, entityList);

        //Sheet2设置
        Map<String, Object> sheet2ExportMap = new HashMap<>();
        sheet2ExportMap.put(NormalExcelConstants.CLASS, ExcelExportEntity.class);
        sheet2ExportMap.put(NormalExcelConstants.DATA_LIST, list1);
        sheet2ExportMap.put(NormalExcelConstants.PARAMS, new ExportParams("遥测表", "遥测"));
        //这边为了方便,sheet1和sheet2用同一个表头(实际使用中可自行调整)
        sheet2ExportMap.put(NormalExcelConstants.MAP_LIST, entityList);

        // 将sheet1、sheet2使用得map进行包装
        List<Map<String, Object>> sheetsList = new ArrayList<>();
        sheetsList.add(sheet1ExportMap);
        sheetsList.add(sheet2ExportMap);

        // 执行方法
        Workbook workbook = new HSSFWorkbook();

        for(Map<String,Object> map : sheetsList) {
            ExcelExportService server = new ExcelExportService();
            ExportParams param = (ExportParams) map.get("params");
            @SuppressWarnings("unchecked")
            List<ExcelExportEntity> entity = (List<ExcelExportEntity>) map.get("mapList");
            Collection<?> data = (Collection<?>) map.get("data");
            server.createSheetForMap(workbook, param, entity, data);
        }

        FileOutputStream fos = new FileOutputStream("D:/ExcelExportForMap.xls");
        workbook.write(fos);
        fos.close();
    }

效果图

测试
测试

点赞

发表评论

电子邮件地址不会被公开。必填项已用 * 标注