EasyPoi 模板导出Excel (带图片) 以及一些踩坑记录

最近都是在写导出，那么为什么要用 easypoi呢，我用freemarker模板导出写好后，发现图片不行，word转Base64编码就行，excel不行，只能换一种了，一个导出弄一天。做个记录，你知道的越多，不知道的越多。

EasyPoi官网

展示效果（正面 sheet）

（反面 sheet）

1. EasyPoi模板导出

1.1 准备模板

• 用{{}}包裹变量就行，注意一点，图片所在的单元格不用提前合并。

• 这边有个遍历填充的

  第一个单元格：{{$fe: maplist t.familyName 
  中间单元格直接 t.变量
  最后一个单元格 t.变量 }}
  
  虽然官网说的 {{$fe: maplist t t.familyName}}，但是不要这么写，不然第一列元素不填充。(t是默认)
  

1.2 准备依赖

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.2</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.2</version>
        </dependency>
<!-- 正常来说上面的三个就行，版本推荐和我一样的 -->
<!-- POI的版本要对应，不然依赖冲突，找不到方法什么的都是版本问题，版本一致的，可以忽略-->
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>20.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>

1.3 具体实现

• 大概就是首先查出相关数据，并根据要求处理好格式，拼数据什么的，将查出来的数据以map的key-value格式进行保存，再利用ExcelExportUtil生成Excel，到系统Excel文件的固定保存位置，然后再去读这个文件，以流的形式返回给前台，windows.open()，实现导出下载。
• 一些路径我写死的，后续改成自己yml文件里的配置就行，不建议写死。

    @GetMapping("/exportDisciplineLeader")
    @ApiOperation("纪检干部导出")
    @LogAnnotation("纪检干部导出")
    public Result exportDisciplineLeader(HttpServletResponse response,String oid) {
        try {
            // 查询导出数据 
            HashMap<String,Object> map = disciplineLeaderInfoService.listDisciplineLeader(oid);
            // 获取resource目录下的模板位置
            String templateFileName = this.getClass().getResource("/").getPath() + "excelTemplate" + File.separator + "disciplineLeader.xls";
            // 第二个参数true是为了开启多sheet扫描，就是同一个xls文件中有多个工作表的时候。
            TemplateExportParams params = new TemplateExportParams(templateFileName, true);
            // 数据载入，生成excel文件
            Workbook book = ExcelExportUtil.exportExcel(params, map);

            ArrayList<Map<String, String>> maplist = (ArrayList<Map<String, String>>) map.get("maplist");
            // 纵向合并单元格
            CellRangeAddress cellAddresses = new CellRangeAddress(1, maplist.size()+1, 0, 0);
            book.getSheet("反面").addMergedRegion(cellAddresses);

            try {
                //设置导出文件名、创建输出流
                FileOutputStream fos = new FileOutputStream("D:\\easypoi\\华侨城集团有限公司纪委书记(纪检干部)信息采集表.xls");
                //导出excel文件
                book.write(fos);
                fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }

            // 以流的形式返回给前台
            response.setContentType("application/force-download");// 设置强制下载不打开
            response.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("华侨城集团有限公司纪委书记(纪检干部)信息采集表.xls", "UTF-8"));// 设置文件名
            byte[] buffer = new byte[1024];
            FileInputStream fis = null;
            BufferedInputStream bis = null;
            try {
                fis = new FileInputStream("D:\\easypoi\\华侨城集团有限公司纪委书记(纪检干部)信息采集表.xls");
                bis = new BufferedInputStream(fis);
                OutputStream os = response.getOutputStream();
                int i = bis.read(buffer);
                while (i != -1) {
                    os.write(buffer, 0, i);
                    i = bis.read(buffer);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (bis != null) {
                    try {
                        bis.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
                if (fis != null) {
                    try {
                        fis.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }

            return null;
        } catch (Exception e) {
            log.error("纪检干部导出失败", e);
            return Result.error().message("纪检干部导出失败");
        }
    }

• 大多数都是拼接数据的，看图片导出的设置直接到最后。

 @Override
    public HashMap<String, Object> listDisciplineLeader(String oid) throws IOException {
        HashMap<String, Object> map = new HashMap<>();
        DisciplineLeaderInfo disciplineLeaderInfo = disciplineLeaderInfoMapper.selectById(oid);
        if (disciplineLeaderInfo != null) {
            // 日期格式 yyyy年MM月dd日
            SimpleDateFormat ymdFormat = new SimpleDateFormat("yyyy年MM月dd日");
            SimpleDateFormat ymFormat = new SimpleDateFormat("yyyy年MM月");
            // 姓名
            map.put("personName", disciplineLeaderInfo.getPersonName());
            // 性别
            map.put("sexName", disciplineLeaderInfo.getSexName());
            // 民族
            map.put("nationName", disciplineLeaderInfo.getNationName());
            // 出生日期
            Date birthday = disciplineLeaderInfo.getBirthday();
            String birthdayformat = ymdFormat.format(birthday);
            map.put("birthday", birthdayformat);
            // 籍贯
            map.put("birthplace", disciplineLeaderInfo.getBirthplace());
            // 政治面貌
            map.put("politicStatuName", disciplineLeaderInfo.getPoliticStatuName());
            // 入党时间
            Date partyTime = disciplineLeaderInfo.getPartyTime();
            String partFormat = ymFormat.format(partyTime);
            map.put("partyTime", partFormat);
            // 参加工作时间
            Date workTime = disciplineLeaderInfo.getWorkTime();
            String workTimeformat = ymFormat.format(workTime);
            map.put("workTime", workTimeformat);
            // 毕业时间
            String[] graduationDate = disciplineLeaderInfo.getGraduationDate().split("-");
            map.put("graduationDate", graduationDate[0] + "年" + graduationDate[1] + "月" + graduationDate[2] + "日");
            // 户籍所在地
            map.put("censusRegisterProvinceName", disciplineLeaderInfo.getCensusRegisterProvinceName());
            map.put("censusRegisterCityName", disciplineLeaderInfo.getCensusRegisterCityName());
            map.put("censusRegisterCountyName", disciplineLeaderInfo.getCensusRegisterCountyName());
            // 身体状况
            map.put("physicalCondition", disciplineLeaderInfo.getPhysicalCondition());
            // 现任职务
            map.put("presentOccupation", disciplineLeaderInfo.getPresentOccupation());
            // 任现职时间
            String[] lengthTenureDate = disciplineLeaderInfo.getLengthTenureDate().split("-");
            map.put("lengthTenureDate", lengthTenureDate[0] + "年" + lengthTenureDate[1] + "月" + lengthTenureDate[2] + "日");
            // 学历学位-全日制
            map.put("allEducationBackgroundFirstName", disciplineLeaderInfo.getAllEducationBackgroundFirstName());
            map.put("allEducationBackgroundSecondName", disciplineLeaderInfo.getAllEducationBackgroundSecondName());
            // 毕业院校及专业
            map.put("allEducationGraduateInstitutions", disciplineLeaderInfo.getAllEducationGraduateInstitutions());
            // 学历学位-在职
            map.put("inEducationBackgroundFirstName", disciplineLeaderInfo.getInEducationBackgroundFirstName());
            map.put("inEducationBackgroundSecondName", disciplineLeaderInfo.getInEducationBackgroundSecondName());
            // 毕业院校及专业
            map.put("inEducationGraduateInstitutions", disciplineLeaderInfo.getInEducationGraduateInstitutions());
            // 专业技术职称
            map.put("professionalTechnical", disciplineLeaderInfo.getProfessionalTechnical());
            // 熟悉专业有何专长
            map.put("specialSkill", disciplineLeaderInfo.getSpecialSkill());
            // 证件号码
            map.put("cardId", disciplineLeaderInfo.getCardId());
            // 婚姻状况
            map.put("maritalStatusName", disciplineLeaderInfo.getMaritalStatusName());
            // 通讯地址
            map.put("mailingAddress", disciplineLeaderInfo.getMailingAddress());
            // 联系方式
            map.put("accusedTelOne", disciplineLeaderInfo.getAccusedTelOne());

            // 工作履历
            List<LzdaRecordInformationInfo> lzdaRecordInformationInfos = lzdaRecordInformationInfoMapper.selectList(Wrappers.<LzdaRecordInformationInfo>lambdaQuery()
                    .eq(LzdaRecordInformationInfo::getBusinessId, oid));
            if (CollectionUtils.isNotEmpty(lzdaRecordInformationInfos)) {
                StringBuffer workResume = new StringBuffer();
                for (int i = 0; i < lzdaRecordInformationInfos.size(); i++) {
                    // 工作开始时间
                    String[] workStartDateSplit = lzdaRecordInformationInfos.get(i).getWorkStartDate().split("-");
                    String workStartDate = workStartDateSplit[0] + "年" + workStartDateSplit[1] + "月" + workStartDateSplit[2] + "日";
                    // 工作结束时间
                    String[] workEndDateSplit = lzdaRecordInformationInfos.get(i).getWorkEndDate().split("-");
                    String workEndDate = workEndDateSplit[0] + "年" + workEndDateSplit[1] + "月" + workEndDateSplit[2] + "日";
                    // 单位
                    String recordworkUnit = lzdaRecordInformationInfos.get(i).getRecordworkUnit();
                    // 职务
                    String positionName = lzdaRecordInformationInfos.get(i).getPositionName();
                    // 拼接数据+换行
                    workResume.append(i + 1).append("、").append(workStartDate + "-" + workEndDate + " " + recordworkUnit + positionName).append("\n");
                }
                map.put("workResume", workResume);
            }

            // 家庭成员及主要社会关系
            List<LzdaFamilyRelationInfo> lzdaFamilyRelationInfos = lzdaFamilyRelationInfoMapper.selectList(Wrappers.<LzdaFamilyRelationInfo>lambdaQuery()
                    .eq(LzdaFamilyRelationInfo::getBusinessId, oid));
            if (CollectionUtils.isNotEmpty(lzdaFamilyRelationInfos)) {
                ArrayList<Map<String, String>> listMap = new ArrayList<>();
                for (LzdaFamilyRelationInfo lzdaFamilyRelationInfo : lzdaFamilyRelationInfos) {
                    HashMap<String, String> famap = new HashMap<>();
                    // 家庭成员姓名
                    famap.put("familyName", lzdaFamilyRelationInfo.getFamilyName());
                    // 与本人关系
                    famap.put("familyRelationName", lzdaFamilyRelationInfo.getFamilyRelationName());
                    // 工作单位或从事事业
                    famap.put("familyWorkUnit", lzdaFamilyRelationInfo.getFamilyWorkUnit());
                    // 职务
                    famap.put("familyPositionName", lzdaFamilyRelationInfo.getFamilyPositionName());

                    listMap.add(famap);
                }
                // 不到6行添加空数据
                if (lzdaFamilyRelationInfos.size() < 5) {
                    for (int i = 0; i < 6 - lzdaFamilyRelationInfos.size(); i++) {
                        HashMap<String, String> temp = new HashMap<>();
                        // 家庭成员姓名
                        temp.put("familyName", "");
                        // 与本人关系
                        temp.put("familyRelationName", "");
                        // 工作单位或从事事业
                        temp.put("familyWorkUnit", "");
                        // 职务
                        temp.put("familyPositionName", "");
                        listMap.add(temp);
                    }
                }
                map.put("maplist", listMap);
            }
            // 执纪经历
            List<JjgbRulesSituation> jjgbRulesSituations = jjgbRulesSituationMapper.selectList(Wrappers.<JjgbRulesSituation>lambdaQuery()
                    .eq(JjgbRulesSituation::getBusinessId, oid));
            if (CollectionUtils.isNotEmpty(jjgbRulesSituations)) {
                StringBuffer rulesSituation = new StringBuffer();
                for (int i = 0; i < jjgbRulesSituations.size(); i++) {
                    // 执纪开始时间
                    String ruleStartDate = ymFormat.format(jjgbRulesSituations.get(i).getRuleStartDate());
                    // 执纪结束时间
                    String ruleEndDate = ymFormat.format(jjgbRulesSituations.get(i).getRuleEndDate());
                    // 执纪经历
                    String ruleExperience = jjgbRulesSituations.get(i).getRuleExperience();
                    // 拼接数据+换行
                    rulesSituation.append(i + 1).append("、").append(ruleStartDate + "-" + ruleEndDate + " " + ruleExperience).append("\n");
                    map.put("rulesSituation", rulesSituation);
                }
            }

            // 参加培训学习情况
            map.put("learningCondition", disciplineLeaderInfo.getLearningCondition());

            // 奖励处分集合
            List<LzdaRewardPunishmentInfo> rewardPunishmentInfoList = disciplineLeaderInfo.getRewardPunishmentInfoList();
            if (CollectionUtils.isNotEmpty(rewardPunishmentInfoList)) {
                StringBuffer rewardPunishment = new StringBuffer();
                for (int i = 0; i < rewardPunishmentInfoList.size(); i++) {
                    // 拼接数据+换行
                    rewardPunishment.append(i + 1).append("、").append(rewardPunishmentInfoList.get(i).getRewardPunishmentDesignation() + "," + rewardPunishmentInfoList.get(i).getRewardPunishmentTypeName()).append("\n");
                }
                map.put("rewardPunishment", rewardPunishment);
            }

            // 考核结果
            List<JjgbThreeYearAssessmentResult> threeYearAssessmentResults = disciplineLeaderInfo.getThreeYearAssessmentResults();
            if (CollectionUtils.isNotEmpty(threeYearAssessmentResults)) {
                StringBuffer assessment = new StringBuffer();
                for (int i = 0; i < threeYearAssessmentResults.size(); i++) {
                    String assessmentDate = ymFormat.format(threeYearAssessmentResults.get(i).getAssessmentDate());
                    String assessGradeName = threeYearAssessmentResults.get(i).getAssessGradeName();
                    // 拼接数据+换行
                    assessment.append(i + 1).append("、").append(assessmentDate + "年度考核等次为" + assessGradeName).append("\n");
                }
                map.put("assessment", assessment);
            }

            MultiMediaInfo multiMediaInfo = multiMediaInfoMapper.selectOne(Wrappers.<MultiMediaInfo>lambdaQuery().eq(MultiMediaInfo::getBusinessOid, oid));
            // 图片
            ImageEntity image = new ImageEntity();
            // 这里是设置合并单元格，但是千万不要再模板你提前合并单元格。合并了这里会报错。行合并多少个格子在这里设置。
            image.setRowspan(4);//向下合并三行
            //添加图片存放路径 D:\IMG_0783.JPG
            image.setUrl(multiMediaInfo.getFilePath());
            map.put("image", image);
        }
        return map;
    }