平台导入错误提醒，模板下拉框

guyue

1 天以前 734e626b73e837480310413798ace688bfdb92b7

平台导入错误提醒，模板下拉框

 src/main/java/com/linghu/controller/OrderController.java

@@ -208,6 +208,8 @@
                queryWrapper.ge(Orders::getCreate_time, startTime);
            }
        }
        // 排序
        queryWrapper.orderByDesc(Orders::getCreate_time);
        // 分页查询
        if (pageNum != null && pageSize != null) {
            Page<Orders> pageInfo = new Page<>(pageNum, pageSize);

 src/main/java/com/linghu/controller/PlatformController.java

@@ -3,11 +3,14 @@
import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.linghu.listener.PlatformExcelListener;
import com.linghu.listener.TypeDropdownWriteHandler;
import com.linghu.mapper.ReferenceMapper;
import com.linghu.model.common.ResponseResult;
import com.linghu.model.entity.Platform;
import com.linghu.model.entity.Reference;
import com.linghu.model.entity.Type;
import com.linghu.model.excel.ExcelDataWithRow;
import com.linghu.model.excel.PlatformExcel;
import com.linghu.model.page.CustomPage;
import com.linghu.service.PlatformService;
@@ -15,6 +18,8 @@

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.log4j.Log4j;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
@@ -35,7 +40,9 @@
@RestController
@RequestMapping("/platform")
@Api(value = "平台相关接口", tags = "设置-平台")
@Slf4j
public class PlatformController {


    @Autowired
    private PlatformService platformService;
@@ -108,25 +115,39 @@
    public ResponseResult<CustomPage<Platform>> list(
            @RequestParam(required = false) Integer page,
            @RequestParam(required = false) Integer pageSize) {
        // 构建查询条件并添加排序（按创建时间倒序）
        LambdaQueryWrapper<Platform> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.orderByDesc(Platform::getCreate_time); // 新增的排序条件

        if (page != null && pageSize != null) {
            Page<Platform> pageInfo = new Page<>(page, pageSize);
            Page<Platform> result = platformService.page(pageInfo);
            Page<Platform> result = platformService.page(pageInfo, queryWrapper);
            return ResponseResult.success(new CustomPage<>(result));
        } else {
            List<Platform> list = platformService.list();
            List<Platform> list = platformService.list(queryWrapper);
            CustomPage<Platform> customPage = new CustomPage<>(new Page<>());
            customPage.setRecords(list);
            customPage.setTotal(list.size());
            return ResponseResult.success(customPage);

        }
    }

    @GetMapping("/download")
    @ApiOperation("下载平台模板")
    public ResponseEntity<byte[]> downloadTemplate() throws IOException {
        // 获取所有类型名称
        List<Type> typeList = typeService.list();
        List<String> typeNames = typeList.stream()
                .map(Type::getType_name)
                .collect(Collectors.toList());

        ByteArrayOutputStream out = new ByteArrayOutputStream();
        EasyExcel.write(out, PlatformExcel.class).sheet("平台模板").doWrite(new ArrayList<>());

        // 注册下拉框拦截器
        EasyExcel.write(out, PlatformExcel.class)
                .registerWriteHandler(new TypeDropdownWriteHandler(typeNames))
                .sheet("平台模板")
                .doWrite(new ArrayList<>());

        return ResponseEntity.ok()
                .header(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=platform_template.xlsx")
@@ -138,71 +159,75 @@
    @ApiOperation("导入平台数据")
    public ResponseResult<String> importPlatforms(@RequestParam("file") MultipartFile file) {
        try {
            // 检查文件是否为空
            if (file.isEmpty()) {
                return ResponseResult.error("上传文件不能为空");
            }

            // 读取Excel数据
            List<PlatformExcel> excelList = EasyExcel.read(file.getInputStream())
            // 使用自定义监听器读取数据（包含行号）
            PlatformExcelListener listener = new PlatformExcelListener();
            EasyExcel.read(file.getInputStream())
                    .head(PlatformExcel.class)
                    .sheet()
                    .doReadSync();
                    .registerReadListener(listener)
                    .doRead();

            // 数据转换与验证
            List<ExcelDataWithRow<PlatformExcel>> excelList = listener.getDataList();
            List<Platform> platforms = new ArrayList<>();
            List<String> errorMessages = new ArrayList<>();

            for (PlatformExcel excel : excelList) {
                // 检查必要字段
            // 遍历数据并验证（逻辑同方案一）
            for (ExcelDataWithRow<PlatformExcel> excelData : excelList) {
                int rowNum = excelData.getRowNumber();
                PlatformExcel excel = excelData.getData();
                List<String> rowErrors = new ArrayList<>();
                // 验证逻辑
                if (!StringUtils.hasText(excel.getPlatform_name())) {
                    errorMessages.add("平台名称不能为空");
                    continue;
                }
                if (!StringUtils.hasText(excel.getType_name())) {
                    errorMessages.add("平台类型不能为空");
                    continue;
                    rowErrors.add("平台名称不能为空");
                }
                if (!StringUtils.hasText(excel.getDomain())) {
                    errorMessages.add("平台域名不能为空");
                    continue;
                    rowErrors.add("平台域名不能为空");
                }

                // 查找类型
                // ... 其他验证
                // 查找类型（仅当类型名称不为空时检查，避免空指针）
                Integer typeId = null;
                if (StringUtils.hasText(excel.getType_name())) {
                Type typeByName = typeService.getTypeByName(excel.getType_name());
                if (typeByName == null) {
                    errorMessages.add("未知的平台类型: " + excel.getType_name());
                    continue;
                        rowErrors.add("未知的平台类型: " + excel.getType_name());
                }
                    typeId = typeByName.getType_id();

                // 构建平台对象
                }else {
                    rowErrors.add("平台类型不能为空");
                }
                if (!rowErrors.isEmpty()) {
                    errorMessages.add(String.format("第%d行错误: %s", rowNum, String.join("；", rowErrors)));
                } else {
                    // 构建Platform对象...
                Platform platform = new Platform();
                platform.setPlatform_name(excel.getPlatform_name());
                platform.setDomain(excel.getDomain());
                platform.setType_id(typeByName.getType_id());

                // 设置创建时间（解决之前的数据库错误）
                    platform.setType_id(typeId);
                platform.setCreate_time(LocalDateTime.now());

                platforms.add(platform);
            }
            }

            // 处理错误
            // 错误处理和导入逻辑（同方案一）
            if (!errorMessages.isEmpty()) {
                return ResponseResult.error("数据验证失败: " + String.join("; ", errorMessages));
                // 构建清晰的错误信息
                StringBuilder errorMsg = new StringBuilder();
                errorMsg.append("导入失败，共").append(errorMessages.size()).append("条数据存在错误：\n");
                errorMessages.forEach(msg -> errorMsg.append(msg).append("\n"));

                return ResponseResult.error(400, errorMsg.toString());
            }

            // 批量保存
            if (!platforms.isEmpty()) {
                platformService.saveBatch(platforms);
                return ResponseResult.success("成功导入" + platforms.size() + "条数据");
            } else {
                return ResponseResult.success();
            }
            // 无错误时导入...
            return ResponseResult.success("导入成功，共" + platforms.size() + "条数据");

        } catch (Exception e) {
            // 记录详细异常信息

            log.error(e.getMessage());
            return ResponseResult.error("文件解析失败：" + e.getMessage());
        }
    }

 src/main/java/com/linghu/listener/PlatformExcelListener.java

New file
@@ -0,0 +1,30 @@
package com.linghu.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import com.linghu.model.excel.ExcelDataWithRow;
import com.linghu.model.excel.PlatformExcel;

import java.util.ArrayList;
import java.util.List;

// 自定义监听器
public class PlatformExcelListener extends AnalysisEventListener<PlatformExcel> {
    private List<ExcelDataWithRow<PlatformExcel>> dataList = new ArrayList<>();
    private int rowNumber = 0; // 行号计数器（从1开始）

    @Override
    public void invoke(PlatformExcel data, AnalysisContext context) {
        rowNumber++; // 跳过表头行（如果表头是第1行，这里从第2行开始计数）
        dataList.add(new ExcelDataWithRow<>(rowNumber, data));
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
    }

    public List<ExcelDataWithRow<PlatformExcel>> getDataList() {
        return dataList;
    }
}

 src/main/java/com/linghu/listener/TypeDropdownWriteHandler.java

New file
@@ -0,0 +1,38 @@
package com.linghu.listener;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.List;

public class TypeDropdownWriteHandler implements SheetWriteHandler {
    private final List<String> typeNames;

    public TypeDropdownWriteHandler(List<String> typeNames) {
        this.typeNames = typeNames;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();

        // 设置下拉框的单元格范围（第2行(index=1)开始的所有行，第3列(index=2)）
        CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, 2, 2);
        
        // 创建下拉选项约束
        DataValidationConstraint constraint = helper.createExplicitListConstraint(
                typeNames.toArray(new String[0]));
        
        // 应用约束到单元格范围
        DataValidation validation = helper.createValidation(constraint, addressList);
        validation.setShowErrorBox(true);
        validation.setSuppressDropDownArrow(true); // 显示下拉箭头
        validation.createErrorBox("输入错误", "请从下拉列表中选择类型名称");
        validation.setShowErrorBox(true);
        
        sheet.addValidationData(validation);
    }
}

 src/main/java/com/linghu/model/excel/ExcelDataWithRow.java

New file
@@ -0,0 +1,16 @@
package com.linghu.model.excel;

import lombok.Data;

@Data
public class ExcelDataWithRow<T> {
    private int rowNumber; // 行号（从1开始）
    private T data;

    // 构造方法、getter、setter
    public ExcelDataWithRow(int rowNumber, T data) {
        this.rowNumber = rowNumber;
        this.data = data;
    }

}