导出为excel | 青训营笔记

这是我参与「第五届青训营 」伴学笔记创作活动的第 17 天

本篇记录我在做一项功能点时的一些收获。需求是这样的，我需要将一些数据导出为excel数据，这些数据有列表，有填充，也有图片。我选用的框架是easyPoi，但使用期间也遇到了不少问题。

首先就是兼容性问题，就是导入的依赖版本和poi的原生版本不兼容，导致出错，这也算是一些小坑吧。目前我的依赖使用如下：

<!-- easy poi begin -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.1.0</version>
</dependency>
<!-- easy poi end -->
<!-- poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</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-schemas</artifactId>
    <version>4.1.2</version>
</dependency>

除此之外，还有一些奇怪的问题，比如我按模板导出的时候，有些内容没有成功导入进去，格式也变得很奇怪。我真的不知道具体是什么原因，但重新修改了模板的格式排版就解决了，也挺奇怪的，记录一下。

还有一个就是图片问题，就是在合并单元格后，填充图片的话它只会填充左上角的那一个单元格，解决办法也很简单，就是手动写入要合并的行与列，如下：

private ImageEntity getImageEntity(FileVO fileVO,int rowspan,int colspan) {
    byte[] bytes = fileServiceTencentYun.fileDownload(fileVO.getFilePath());
    ImageEntity imageEntity = new ImageEntity();
    imageEntity.setData(bytes);
    imageEntity.setType(ImageEntity.Data);
    imageEntity.setRowspan(rowspan);
    imageEntity.setColspan(colspan);
    return imageEntity;
}

如下是我的具体代码。

public void exportSample(String exportToken,HttpServletResponse response) {
    String key =  RedisConstants.EXPORT_SAMPLE_TOKEN_KEY + exportToken;
    ExportSampleTokenBo exportSampleTokenBo = redisService.getObject(key, ExportSampleTokenBo.class);
    if(Objects.isNull(exportSampleTokenBo)) {
        throw new SmartBusinessException(new ResponseCodeConst("exportToken无效"));
    }
    Long orderId = exportSampleTokenBo.getOrderId();
    Long scheduleId = exportSampleTokenBo.getScheduleId();
    String fileName = exportSampleTokenBo.getFileName();
    boolean needSchedule = exportSampleTokenBo.isNeedSchedule();
    String template = SAMPLE_TEMPLATE;
    if(needSchedule) {
        template = SAMPLE_TEMPLATE_WITH_SCHEDULE;
    }

    Map<String, Object> map = new HashMap<>();

    //填充订单数据
    SampleOrderEntity sampleOrderEntity = sampleOrderService.getById(orderId);
    if(Objects.isNull(sampleOrderEntity)) {
        throw new SmartBusinessException(new ResponseCodeConst("对象不存在"));
    }
    map.put("tpDate", DateUtil.format(sampleOrderEntity.getTpDate(), "yyyy-MM-dd"));
    map.put("requestSentDate", DateUtil.format(sampleOrderEntity.getRequestSentDate(), "yyyy-MM-dd"));
    map.put("remark", sampleOrderEntity.getRemark());

    //填充schedule数据
    SampleScheduleEntity sampleScheduleEntity =
            sampleScheduleService.lambdaQuery().eq(SampleScheduleEntity::getId, scheduleId).eq(SampleScheduleEntity::getOrderId, orderId).one();
    if(Objects.isNull(sampleScheduleEntity)) {
        throw new SmartBusinessException(new ResponseCodeConst("对象不存在"));
    }
    if (needSchedule) {
        map.put("confirmPurchaseDate", DateUtil.format(sampleScheduleEntity.getConfirmPurchaseDate(), "yyyy-MM-dd"
        ));
        map.put("masterAllocationDate", DateUtil.format(sampleScheduleEntity.getMasterAllocationDate(), "yyyy-MM" +
                "-dd"));
        map.put("cardboardCollectionDate", DateUtil.format(sampleScheduleEntity.getCardboardCollectionDate(),
                "yyyy" + "-MM-dd"));
        map.put("sampleFinishedDate", DateUtil.format(sampleScheduleEntity.getSampleFinishedDate(), "yyyy-MM-dd"));
        //车板图片
        List<FileVO> sweepImgList = this.getFileVos(sampleScheduleEntity.getSweepImgIds(), ",");
        if(sweepImgList.size() != 0) {
            this.fillImg(map,"sweepImg",sweepImgList,1,1);
        }
        //审版
        map.put("sampleCheckDate",DateUtil.format(sampleScheduleEntity.getSampleCheckDate(),"yyyy-MM-dd"));
        map.put("commentText",sampleScheduleEntity.getCommentText());
        List<FileVO> commentImgList = this.getFileVos(sampleScheduleEntity.getCommentImgIds(), ",");
        if(commentImgList.size() != 0) {
            this.fillImg(map,"commentImg",commentImgList,1,1);
        }
        map.put("archiveDetailDate",DateUtil.format(sampleScheduleEntity.getArchiveDetailDate(),"yyyy-MM-dd"));
        List<FileVO> finishImgList = this.getFileVos(sampleScheduleEntity.getSampleFinishImgIds(), ",");
        if(finishImgList.size() != 0) {
            this.fillImg(map,"sampleFinishImg",finishImgList,1,1);
        }
        map.put("dispatchDate",DateUtil.format(sampleScheduleEntity.getDispatchDate(),"yyyy-MM-dd"));
        map.put("checkingNo",sampleScheduleEntity.getCheckingNo());
        map.put("weight",sampleScheduleEntity.getWeight());
        map.put("receivedSampleDate",DateUtil.format(sampleScheduleEntity.getReceivedSampleDate(),"yyyy-MM-dd"));
        List<FileVO> receiveImgList = this.getFileVos(sampleScheduleEntity.getReceiveImgIds(), ",");
        if(receiveImgList.size() != 0) {
            this.fillImg(map,"receiveImg",receiveImgList,1,1);
        }
        map.put("status",sampleScheduleEntity.getStatus().getDisplay());
        map.put("version",sampleScheduleEntity.getVersion());
        map.put("cancelDate",DateUtil.format(sampleScheduleEntity.getCancelDate(),"yyyy-MM-dd"));
        map.put("resampleDate",DateUtil.format(sampleScheduleEntity.getResampleDate(),"yyyy-MM-dd"));
        map.put("sealedDate",DateUtil.format(sampleScheduleEntity.getSealedDate(),"yyyy-MM-dd"));
    } else  {
        //审版
        map.put("sampleCheckDate",DateUtil.format(sampleScheduleEntity.getSampleCheckDate(),"yyyy-MM-dd"));
        map.put("commentText",sampleScheduleEntity.getCommentText());
        List<FileVO> commentImgList = this.getFileVos(sampleScheduleEntity.getCommentImgIds(), ",");
        if(commentImgList.size() != 0) {
            this.fillImg(map,"commentImg",commentImgList,1,1);
        }
    }

    //填充sample数据
    SampleEntity sampleEntity = sampleService.lambdaQuery().eq(SampleEntity::getScheduleId, scheduleId).last(
            "limit 1").one();
    if (!Objects.isNull(sampleEntity)) {
        map.put("series", sampleEntity.getSeries());
        map.put("style", sampleEntity.getStyle());
        map.put("styleCode", sampleEntity.getStyleCode());
        map.put("currentProcess", sampleEntity.getCurrentProcess());
        map.put("description", sampleEntity.getDescription());
        map.put("targetPrice", sampleEntity.getTargetPrice());
        map.put("tecFabric", sampleEntity.getTecFabric());
        map.put("tecLining", sampleEntity.getTecLining());
        map.put("tecColor", sampleEntity.getTecColor());
        map.put("tecStitch", sampleEntity.getTecStitch());
        map.put("tecOther", sampleEntity.getTecOther());
        map.put("unit", sampleEntity.getUnit());
    }

    //填充工艺图
    List<FileVO> tecFileList = this.getFileVos(sampleEntity.getTecImgIds(),",");
    if (tecFileList.size() != 0) {
        this.fillImg(map,"tecImg",tecFileList,6,2);
        if(tecFileList.size() > 5 && needSchedule) {
            template = SAMPLE_TEMPLATE_WITH_SCHEDULE_WITH_PAGE;
        } else if(tecFileList.size() > 5) {
            template = SAMPLE_TEMPLATE_WITH_PAGE;
        }
    }

    TemplateExportParams params =
            new TemplateExportParams(MyExcelExportUtil.convertTemplatePath("excel/" + template));
    params.setColForEach(true);//开启横向遍历

    //填充图片
    //填充款式图
    List<FileVO> fileVOS = this.getFileVos(sampleEntity.getStyleImgIds(), ",");
    if (fileVOS.size() != 0) {
        this.fillImg(map,"styleImg",fileVOS.get(0),3,2);
    }

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

    Map<String, Object> mapName = new HashMap<>();
    for (int i=0;i<fileVOS.size();i++){
        mapName = new HashMap<>();
        mapName.put("name", "t.img"+(i+1)+"");
        list.add(mapName);
    }

    List<Map<String, Object>> styleImgList = new ArrayList<>();
    for (int i=0;i<fileVOS.size();i++){
        Map<String, Object> styleImgMap  = new HashMap<>();
        byte[] bytes = fileServiceTencentYun.fileDownload(fileVOS.get(i).getFilePath());
        ImageEntity imageEntity = new ImageEntity();
        imageEntity.setData(bytes);
        styleImgMap.put("t.img"+(i+1)+"",imageEntity);
        styleImgList.add(styleImgMap);
    }

    map.put("styleImgList",styleImgList);
    map.put("list",list);

    //填充yardage数据
    List<SampleYardageEntity> yardageEntities =
            sampleYardageService.lambdaQuery().eq(SampleYardageEntity::getScheduleId, scheduleId).list();
    if (yardageEntities.size() != 0) {
        List<Map<String, Object>> yardageList = new ArrayList<>();
        yardageEntities.forEach(f -> {
            Map<String, Object> yardageMap = new HashMap<>();
            yardageMap.put("yardage", f.getYardage());
            yardageMap.put("quantity", f.getQuantity());
            yardageMap.put("yardageColor", f.getColor());
            yardageList.add(yardageMap);
        });
        map.put("yardageList", yardageList);
    }

    //填充size数据
    List<SampleSizeEntity> sampleSizeEntities =
            sampleSizeService.lambdaQuery().eq(SampleSizeEntity::getScheduleId, scheduleId).list();
    if (sampleSizeEntities.size() != 0) {
        List<Map<String, Object>> sizeList = new ArrayList<>();
        sampleSizeEntities.forEach(f -> {
            Map<String, Object> sizeMap = new HashMap<>();
            sizeMap.put("sizePart", f.getPart());
            sizeMap.put("magnitude", f.getMagnitude());
            sizeMap.put("requiredSize", f.getRequiredSize());
            sizeMap.put("sampleSize", f.getSampleSize());
            sizeMap.put("actualUsSize", f.getActualUsSize());
            sizeMap.put("actualClientSize", f.getActualClientSize());
            sizeList.add(sizeMap);
        });
        map.put("sizeList", sizeList);
    }

    //填充bom数据
    List<SampleBomEntity> sampleBomEntities = sampleBomService.lambdaQuery().eq(SampleBomEntity::getScheduleId,
            scheduleId).list();
    if (sampleBomEntities.size() != 0) {
        List<Map<String, Object>> bomList = new ArrayList<>();
        sampleBomEntities.forEach(f -> {
            Map<String, Object> bomMap = new HashMap<>();
            bomMap.put("bomPart", f.getPart());
            bomMap.put("bomType", f.getType().getDisplay());
            bomMap.put("material", f.getMaterial());
            bomMap.put("bomColor", f.getColor());
            bomMap.put("dosage", f.getDosage());
            bomMap.put("wastage", f.getWastage());
            bomMap.put("unitPrice", f.getUnitPrice());
            bomMap.put("totalPrice", f.getTotalPrice());
            bomMap.put("width", f.getWidth());
            bomMap.put("gramWeight", f.getGramWeight());
            bomMap.put("supplier", f.getSupplier());
            bomMap.put("contact", f.getContact());
            bomMap.put("address", f.getAddress());
            bomMap.put("bomRemark", f.getRemark());
            bomList.add(bomMap);
        });
        map.put("bomList", bomList);
    }

    //填充参与人信息
    List<UserSimpleVo> attendeeList = sampleAttendeeDao.getAttendeeInfoByOrderId(orderId);
    ConcurrentMap<Integer, List<UserSimpleVo>> attendeeGroupByRoleId =
            attendeeList.stream().collect(Collectors.groupingByConcurrent(UserSimpleVo::getRoleId));
    attendeeList.forEach(f -> {
        if (f.getRoleId().equals(RoleEnum.PAPER_BOARD_MASTER.getCode())) {
            map.put("boardMaker", f.getName());
        } else if (f.getRoleId().equals(RoleEnum.SWEEP_TEMPLATE_MASTER.getCode())) {
            map.put("shifter", f.getName());
        } else if (f.getRoleId().equals(RoleEnum.REVIEW_BOARD_MASTER.getCode())) {
            map.put("checker", f.getName());
        } else if (f.getRoleId().equals(RoleEnum.BUSINESS_FOLLOWER.getCode())) {
            map.put("follower", f.getName());
        } else if (f.getRoleId().equals(RoleEnum.CLIENT_USER.getCode())) {
            map.put("client", f.getName());
        } else if(f.getRoleId().equals(RoleEnum.FACTORY_USER.getCode())) {
            map.computeIfAbsent("boardMaker", k -> f.getName());
            map.computeIfAbsent("shifter", k -> f.getName());
        }
    });


    Workbook workbook = ExcelExportUtil.exportExcel(params, map);

    try {
        fileName = fileName + ".xlsx";
        SmartEasyPoiExcelUtil.downLoadExcel(fileName, response, workbook);
    } catch (IOException e) {
        e.printStackTrace();
        throw new SmartBusinessException(new ResponseCodeConst("导出失败"));
    }
}