♥️作者:小宋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即可。
teacherManageDO.setTeacherSubjectExport(teacherSubjectNew.toString());
痛点:
在split(", ")的时候一定要注意好这个逗号是有空格的!!!!不能直接split(","),否则后面所有的值都识别不到了,他就会优先识别空格,一定要注意。
teacherSubjectNew.append(dictDataRespDTO2.getData().getLabel()).append(",");