java的Excel导出，数组与业务字典匹配并去掉最后一个逗号

小宋1021

于 2024-09-18 16:30:10 发布

阅读量762

点赞数 11

文章标签： java 开发语言

本文链接：https://blog.csdn.net/qq_50684356/article/details/142336539

版权

♥️作者：小宋1021
🤵‍♂️个人主页：小宋1021主页
♥️坚持分析平时学习到的项目以及学习到的软件开发知识，和大家一起努力呀！！！
🎈🎈加油！加油！加油！加油
🎈欢迎评论 💬点赞👍🏻 收藏 📂加关注+

首先展示导出结果，如图所示：

代码展示：

导出实体类：

由于不想和DO混用，就单独为导出的字段建立实体类，一方面是容易使用 @ExcelProperty注解，方便观察，另一方面是有些字段在数据库存的是业务字典的value，是Integer类型，我需要再定义一个String类型的字段来接收于业务字典匹配完成后的label的值并完成导出。

package com.todod.education.module.hr.controller.admin.teachermanage.vo;


import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;
import lombok.ToString;

@Schema(description = "管理后台 -  教师信息导出管理 Response VO")
@Data
@ExcelIgnoreUnannotated
@ToString(callSuper = true)
public class TeacherExportRespVO {
    @Schema(description = "教师姓名", example = "赵六")
    @ExcelProperty("教师姓名")
    private String teacherName;

    @Schema(description = "手机号", example = "赵六")
    @ExcelProperty("手机号")
    private String phone;

    @Schema(description = "性别", example = "赵六")
    @ExcelProperty("性别")
    private String teacherSexExport;

    @Schema(description = "科目", example = "赵六")
    @ExcelProperty("科目")
    private String teacherSubjectExport;

    @Schema(description = "教师学段", example = "赵六")
    @ExcelProperty("教师学段")
    private String teacherStageExport;

    @Schema(description = "课补级别", example = "赵六")
    @ExcelProperty("课补级别")
    private Integer supplyLevel;

    @Schema(description = "就职状态", example = "赵六")
    @ExcelProperty("就职状态")
    private String teacherJobStatusExport;

}

实体类DO字段：

  /**
     * 性别（导出）
     */
    @TableField(exist = false)
    private String teacherSexExport;
    /**
     * 科目（导出）
     */
    @TableField(exist = false)
    private String teacherSubjectExport;

    /**
     * 教师学段（导出）
     */
    @TableField(exist = false)
    private String teacherStageExport;
    /**
     * 就职状态（导出）
     */
    @TableField(exist = false)
    private String teacherJobStatusExport;

Service：

    /**
     *  教师信息导出
     *
     * @param
     * @return
     */
    PageResult<TeacherManageDO> getTeacherExportData(TeacherManagePageReqVO pageReqVO);

ServiceImpl：

    //教师信息导出
    @Override
    public PageResult<TeacherManageDO> getTeacherExportData(TeacherManagePageReqVO pageReqVO) {
        PageResult<TeacherManageDO> teacherManageDOPageResult = teacherManageMapper.selectPage(pageReqVO);
        List<TeacherManageDO> list = teacherManageDOPageResult.getList();
        for (TeacherManageDO teacherManageDO : list) {

            if(teacherManageDO.getTeacherSex() == null){
                teacherManageDO.setTeacherSex("");
            }
            else {
                CommonResult<DictDataRespDTO> dictDataRespDTO = dictDataApi.getDictData("system_user_sex", teacherManageDO.getTeacherSex());
                if (dictDataRespDTO != null){
                    teacherManageDO.setTeacherSexExport(dictDataRespDTO.getData().getLabel());
                }

            }

            if(teacherManageDO.getTeacherSubject() == null){
                teacherManageDO.setTeacherSubject("");
            }else{

                String teacherSubject = teacherManageDO.getTeacherSubject();
                teacherSubject = teacherSubject.replace("[", "").replace("]", "");
                String[] parts = teacherSubject.split(", ");
                StringBuilder teacherSubjectNew = new StringBuilder();
                for (int  i = 0; i < parts.length; i++) {
                    CommonResult<DictDataRespDTO> dictDataRespDTO2 = dictDataApi.getDictData("subject", parts[i]);
                    if (dictDataRespDTO2 != null && dictDataRespDTO2.getData() != null) {
                        if (i< parts.length-1){
                            teacherSubjectNew.append(dictDataRespDTO2.getData().getLabel()).append(",");
                        }
                        else {
                            teacherSubjectNew.append(dictDataRespDTO2.getData().getLabel());
                        }

                    }
                }
                // 最后，如果需要移除最后一个逗号，可以检查字符串并移除
                if (teacherSubjectNew.length() > 0 && teacherSubjectNew.charAt(teacherSubjectNew.length() - 1) == ',') {
                    teacherSubjectNew.charAt(teacherSubjectNew.length() - 1); // 移除最后一个逗号
                }
                teacherManageDO.setTeacherSubjectExport(teacherSubjectNew.toString());

            }

            if(teacherManageDO.getTeacherStage() == null){
                teacherManageDO.setTeacherStage("");
            }else{
                CommonResult<DictDataRespDTO> dictDataRespDTO3 = dictDataApi.getDictData("public_teacher_stage", teacherManageDO.getTeacherStage());
                if (dictDataRespDTO3 != null){
                    teacherManageDO.setTeacherStageExport(dictDataRespDTO3.getData().getLabel());
                }

            }

            if(teacherManageDO.getJobStatus() == null){
                teacherManageDO.setJobStatus("");
            }else{
                CommonResult<DictDataRespDTO> dictDataRespDTO4 = dictDataApi.getDictData("hr_status", teacherManageDO.getJobStatus());
                if (dictDataRespDTO4 != null){
                    teacherManageDO.setTeacherJobStatusExport(dictDataRespDTO4.getData().getLabel());
                }

            }


        }

        return teacherManageDOPageResult;
    }

Controller：

    @GetMapping("/export-excel")
    @Operation(summary = "导出教师管理 Excel")
    @PreAuthorize("@ss.hasPermission('hr:teacher-manage:export')")
    @ApiAccessLog(operateType = EXPORT)
    public void exportTeacherManageExcel(@Valid TeacherManagePageReqVO pageReqVO,
              HttpServletResponse response) throws IOException {
        pageReqVO.setPageSize(PageParam.PAGE_SIZE_NONE);
        List<TeacherManageDO> list = teacherManageService.getTeacherExportData(pageReqVO).getList();
        // 导出 Excel
        ExcelUtils.write(response, "教师管理.xls", "数据", TeacherExportRespVO.class,
                        BeanUtils.toBean(list, TeacherExportRespVO.class));
    }

下面我们来拆解一下实现类。

场景：由于业务需求的变化，老师的科目由单个变成了多个，我在存储的时候存储的是数组的字符串，类似于：“[103, 202, 203, 204]”，这就需要去掉左右两侧[]以及拆分成字符串数组再与业务字典进行匹配。

主要就是这句话，让我们来看一下：

      if(teacherManageDO.getTeacherSubject() == null){
                teacherManageDO.setTeacherSubject("");
            }else{

                String teacherSubject = teacherManageDO.getTeacherSubject();
                teacherSubject = teacherSubject.replace("[", "").replace("]", "");
                String[] parts = teacherSubject.split(", ");
                StringBuilder teacherSubjectNew = new StringBuilder();
                for (int  i = 0; i < parts.length; i++) {
                    CommonResult<DictDataRespDTO> dictDataRespDTO2 = dictDataApi.getDictData("subject", parts[i]);
                    if (dictDataRespDTO2 != null && dictDataRespDTO2.getData() != null) {
                        if (i< parts.length-1){
                            teacherSubjectNew.append(dictDataRespDTO2.getData().getLabel()).append(",");
                        }
                        else {
                            teacherSubjectNew.append(dictDataRespDTO2.getData().getLabel());
                        }

                    }
                }
                // 最后，如果需要移除最后一个逗号，可以检查字符串并移除
                if (teacherSubjectNew.length() > 0 && teacherSubjectNew.charAt(teacherSubjectNew.length() - 1) == ',') {
                    teacherSubjectNew.charAt(teacherSubjectNew.length() - 1); // 移除最后一个逗号
                }
                teacherManageDO.setTeacherSubjectExport(teacherSubjectNew.toString());

            }

if(teacherManageDO.getTeacherSubject() == null){
teacherManageDO.setTeacherSubject("");
}

如果科目为空的话就是空字符串插入到excel表里，如果不为空则走else。

String teacherSubject = teacherManageDO.getTeacherSubject();
teacherSubject = teacherSubject.replace("[", "").replace("]", "");
String[] parts = teacherSubject.split(", ");
StringBuilder teacherSubjectNew = new StringBuilder();

这里定义了一个String类型的teacherSubject，为了接收去掉左右括号【】的字符串，又定义了一个Sting类型的数组parts，接收逗号分割以后的teacherSubject字符串，让它变成数组，然后开始for循环遍历。

for (int i = 0; i < parts.length; i++) {
CommonResult<DictDataRespDTO> dictDataRespDTO2 = dictDataApi.getDictData("subject", parts[i]);
if (dictDataRespDTO2 != null && dictDataRespDTO2.getData() != null) {
if (i< parts.length-1){
teacherSubjectNew.append(dictDataRespDTO2.getData().getLabel()).append(",");
}
else {
teacherSubjectNew.append(dictDataRespDTO2.getData().getLabel());
}

}
}

(这里最开始我用的增强for循环，但是发现增强for循环不容易去掉最后一个逗号，最后一个逗号依旧会拼接到字符串里，所以又改成了普通for循环)

循环遍历parts里面的每一个值，让这个值去业务字典里找与之对应的唯一的一个label

CommonResult<DictDataRespDTO> dictDataRespDTO2 = dictDataApi.getDictData("subject", parts[i]);

如果这个label不为空，也就是找到的话，就复制给要导出的这个teacherSubjectNew字段

if (dictDataRespDTO2 != null && dictDataRespDTO2.getData() != null) {
if (i< parts.length-1){
teacherSubjectNew.append(dictDataRespDTO2.getData().getLabel()).append(",");
}

这里要注意的是如果到了最后一个元素的时候我们选择走else也就是不在拼接逗号，如果没有到最后一个元素我们就拼接逗号。

 if (dictDataRespDTO2 != null && dictDataRespDTO2.getData() != null) {
                        if (i< parts.length-1){
                            teacherSubjectNew.append(dictDataRespDTO2.getData().getLabel()).append(",");
                        }
                        else {
                            teacherSubjectNew.append(dictDataRespDTO2.getData().getLabel());
                        }

                    }

最后再赋值给TeacherSubjectExport即可。