feat(device): 1.添加设备导入导出功能并优化数据库关联查询 2.计量溯源性-标准物质清单导入导出

liding

2026-08-28 de775139ac660c9d2e38ae54364f25e6724ca668

 cnas-require/src/main/java/com/ruoyi/require/service/impl/FeStandardSubstanceServiceImpl.java

@@ -1,12 +1,33 @@
package com.ruoyi.require.service.impl;

import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.framework.exception.ErrorException;
import com.ruoyi.require.excel.FeStandardSubstanceImport;
import com.ruoyi.require.mapper.FeStandardSubstanceMapper;
import com.ruoyi.require.pojo.FeStandardSubstance;
import com.ruoyi.require.service.FeStandardSubstanceService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Locale;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * <p>
@@ -23,4 +44,120 @@
    public IPage<FeStandardSubstance> page(Page page, FeStandardSubstance feStandardSubstance) {
        return this.baseMapper.getPage(page,feStandardSubstance);
    }

    @Override
    public void downloadImportTemplate(HttpServletResponse response) {
        response.reset();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        try {
            String fileName = URLEncoder.encode("标准物质导入模板.xlsx", "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            EasyExcel.write(response.getOutputStream(), FeStandardSubstanceImport.class)
                    .sheet("标准物质导入模板")
                    .doWrite(Collections.emptyList());
        } catch (Exception e) {
            throw new RuntimeException("标准物质导入模板下载失败", e);
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int importStandardSubstance(MultipartFile file) {
        if (file == null || file.isEmpty()) {
            throw new ErrorException("请选择需要导入的Excel文件");
        }
        String fileName = file.getOriginalFilename();
        if (fileName == null || !fileName.toLowerCase(Locale.ROOT).endsWith(".xlsx")) {
            throw new ErrorException("仅支持.xlsx格式文件");
        }

        final List<FeStandardSubstanceImport> rows;
        try {
            rows = EasyExcel.read(file.getInputStream())
                    .head(FeStandardSubstanceImport.class)
                    .sheet()
                    .doReadSync();
        } catch (Exception e) {
            throw new ErrorException("Excel解析失败，请使用下载的导入模板");
        }
        if (CollectionUtils.isEmpty(rows)) {
            throw new ErrorException("导入文件没有标准物质数据");
        }

        Set<String> manageNumbers = new HashSet<>();
        for (int i = 0; i < rows.size(); i++) {
            String manageNum = required(rows.get(i).getManageNum(), i + 2, "管理编号");
            if (!manageNumbers.add(manageNum)) {
                throw new ErrorException("第" + (i + 2) + "行：管理编号" + manageNum + "在文件中重复");
            }
        }
        Set<String> existingNumbers = baseMapper.selectList(Wrappers.<FeStandardSubstance>lambdaQuery()
                        .in(FeStandardSubstance::getManageNum, manageNumbers)
                        .select(FeStandardSubstance::getManageNum))
                .stream().map(FeStandardSubstance::getManageNum).collect(Collectors.toSet());

        List<FeStandardSubstance> substances = new ArrayList<>();
        for (int i = 0; i < rows.size(); i++) {
            int rowNumber = i + 2;
            FeStandardSubstanceImport row = rows.get(i);
            String manageNum = required(row.getManageNum(), rowNumber, "管理编号");
            if (existingNumbers.contains(manageNum)) {
                throw new ErrorException("第" + rowNumber + "行：管理编号" + manageNum + "已存在");
            }

            FeStandardSubstance substance = new FeStandardSubstance();
            substance.setName(required(row.getName(), rowNumber, "标准物质名称"));
            substance.setModel(required(row.getModel(), rowNumber, "规格型号"));
            substance.setFactoryManufacturer(required(row.getFactoryManufacturer(), rowNumber, "生产厂家"));
            substance.setFactoryNum(required(row.getFactoryNum(), rowNumber, "出厂编号"));
            substance.setManageNum(manageNum);
            substance.setUncertainty(required(row.getUncertainty(), rowNumber, "不确定度"));
            substance.setQuantity(parseQuantity(row.getQuantity(), rowNumber));
            substance.setAcquisitionDate(parseDate(row.getAcquisitionDate(), rowNumber, "购置日期"));
            substance.setEffectiveDate(parseDate(row.getEffectiveDate(), rowNumber, "有效期"));
            substance.setFileNum(required(row.getFileNum(), rowNumber, "档案编号"));
            substance.setPosition(required(row.getPosition(), rowNumber, "存放位置"));
            substance.setRemark(trim(row.getRemark()));
            substance.setState(0);
            substances.add(substance);
        }
        saveBatch(substances);
        return substances.size();
    }

    private Long parseQuantity(String input, int rowNumber) {
        String value = required(input, rowNumber, "数量");
        try {
            long quantity = Long.parseLong(value);
            if (quantity <= 0) {
                throw new NumberFormatException();
            }
            return quantity;
        } catch (NumberFormatException e) {
            throw new ErrorException("第" + rowNumber + "行：数量必须是大于0的整数");
        }
    }

    private LocalDateTime parseDate(String input, int rowNumber, String column) {
        String value = required(input, rowNumber, column);
        try {
            return LocalDate.parse(value.substring(0, Math.min(value.length(), 10)).replace('/', '-'), DateTimeFormatter.ofPattern("yyyy-M-d"))
                    .atStartOfDay();
        } catch (Exception e) {
            throw new ErrorException("第" + rowNumber + "行：" + column + "格式应为yyyy-MM-dd");
        }
    }

    private String required(String input, int rowNumber, String column) {
        String value = trim(input);
        if (StringUtils.isBlank(value)) {
            throw new ErrorException("第" + rowNumber + "行：" + column + "不能为空");
        }
        return value;
    }

    private String trim(String value) {
        return value == null ? null : value.trim();
    }
}