package com.yuanchu.mom.utils;
|
|
|
import com.alibaba.excel.EasyExcel;
|
import com.alibaba.excel.context.AnalysisContext;
|
import com.alibaba.excel.event.AnalysisEventListener;
|
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
|
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
|
import com.yuanchu.mom.excel.HuTiaoData;
|
import com.yuanchu.mom.excel.TianXianData;
|
import com.yuanchu.mom.exception.ErrorException;
|
import com.yuanchu.mom.mapper.InsOrderStateMapper;
|
import com.yuanchu.mom.mapper.InsProductMapper;
|
import com.yuanchu.mom.mapper.InsProductResult2Mapper;
|
import com.yuanchu.mom.mapper.InsSampleMapper;
|
import com.yuanchu.mom.pojo.*;
|
import com.yuanchu.mom.service.InsOrderPlanService;
|
import com.yuanchu.mom.service.InsProductResult2Service;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.beans.factory.annotation.Value;
|
import org.springframework.stereotype.Component;
|
|
import javax.annotation.Resource;
|
import javax.servlet.http.HttpServletRequest;
|
import java.io.*;
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
@Component
|
public class DianLuUtils {
|
|
@Value("${wordUrl}")
|
private String wordUrl;
|
|
@Resource
|
private InsProductMapper insProductMapper;
|
|
@Resource
|
private InsProductResult2Mapper insProductResult2Mapper;
|
|
@Resource
|
private InsProductResult2Service insProductResult2Service;
|
|
@Resource
|
private InsOrderStateMapper insOrderStateMapper;
|
|
//读取互调的文件
|
public void readDianLuFile1(Integer sampleId, InsOrderFile insOrderFile) {
|
List<InsProductResult2> insProductResult2s = new ArrayList<>();
|
String excelFilePath = wordUrl + "/" + insOrderFile.getFileUrl(); // 更新为你的文件路径
|
List<HuTiaoData> huTiaoData = readExcelData1(excelFilePath);
|
List<InsProduct> insProducts = insProductMapper.selectList(Wrappers.<InsProduct>lambdaQuery()
|
.eq(InsProduct::getState, 1)
|
.eq(InsProduct::getInsSampleId, sampleId));
|
//查询该样品的互调项目id
|
List<InsProduct> products = insProducts.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("互调")).collect(Collectors.toList());
|
if (ObjectUtils.isEmpty(products)) {
|
throw new ErrorException("该样品没有互调项目");
|
}
|
//查询检验任务id
|
List<InsOrderState> orderStates = insOrderStateMapper.selectList(Wrappers.<InsOrderState>lambdaQuery()
|
.eq(InsOrderState::getInsSampleId, sampleId)
|
.eq(InsOrderState::getLaboratory, "电路试验"));
|
//查询原有检验数据
|
List<InsProductResult2> result2s = insProductResult2Mapper.selectList(Wrappers.<InsProductResult2>lambdaQuery()
|
.eq(InsProductResult2::getNum, orderStates.get(0).getNum())
|
.in(InsProductResult2::getInsProductId, insProducts.stream().distinct().map(InsProduct::getId).collect(Collectors.toList())));
|
//List<InsProduct> productList = getInsProduct(sampleId, "电路试验");
|
//根据频点分类
|
Map<String, List<HuTiaoData>> collect = huTiaoData.stream()
|
.collect(Collectors.groupingBy(HuTiaoData::getOften, LinkedHashMap::new, Collectors.toList()));
|
for (Map.Entry<String, List<HuTiaoData>> stringListEntry : collect.entrySet()) {
|
InsProductResult2 insProductResult2 = new InsProductResult2();
|
insProductResult2.setOften(stringListEntry.getKey() + "(M)");//频点
|
//根据下倾角进行分类
|
Map<String, List<HuTiaoData>> collect1 = stringListEntry.getValue().stream().collect(Collectors.groupingBy(HuTiaoData::getAngle, LinkedHashMap::new, Collectors.toList()));
|
String angles = collect1.entrySet().stream().map(stringListEntry1 -> stringListEntry1.getKey() + "°").collect(Collectors.joining(","));
|
insProductResult2.setAngle(angles); //下倾角
|
//根据端口号进行分类
|
Map<String, List<HuTiaoData>> collect2 = stringListEntry.getValue().stream().collect(Collectors.groupingBy(HuTiaoData::getPort, LinkedHashMap::new, Collectors.toList()));
|
String ports = collect2.entrySet().stream().map(Map.Entry::getKey).collect(Collectors.joining(","));
|
insProductResult2.setPort(ports); //端口号
|
//通过同一端口号同一下倾角获取最大检验值
|
List<String> string = new ArrayList<>();
|
for (Map.Entry<String, List<HuTiaoData>> listEntry : collect1.entrySet()) {
|
Map<String, List<HuTiaoData>> collect3 = listEntry.getValue().stream().collect(Collectors.groupingBy(HuTiaoData::getPort, LinkedHashMap::new, Collectors.toList()));
|
List<String> stringList = new ArrayList<>();
|
for (Map.Entry<String, List<HuTiaoData>> entry : collect3.entrySet()) {
|
OptionalDouble max = entry.getValue().stream().mapToDouble(value -> Double.parseDouble(value.getValue())).max();
|
stringList.add(String.valueOf(max.getAsDouble()));
|
}
|
string.add("[" + stringList.stream().collect(Collectors.joining(",")) + "]");
|
}
|
String value = "[" + string.stream().collect(Collectors.joining(",")) + "]";
|
insProductResult2.setValue(value);
|
insProductResult2.setInsProductId(products.get(0).getId());
|
//次数
|
insProductResult2.setNum(orderStates.get(0).getNum());
|
//频段(先判断原有数据是否有)
|
if (ObjectUtils.isNotEmpty(result2s)) {
|
List<String> list = result2s.stream().map(InsProductResult2::getFrequency).distinct().collect(Collectors.toList());
|
for (String s : list) {
|
String[] mHzs = s.split("MHz")[0].split("-");
|
//再判断这个频点是否在频段的范围内
|
if (stringListEntry.getKey().equals("700")){
|
insProductResult2.setFrequency("703-803MHz");
|
break;
|
}
|
else if (Integer.parseInt(stringListEntry.getKey()) <= Integer.parseInt(mHzs[1]) && Integer.parseInt(stringListEntry.getKey()) >= Integer.parseInt(mHzs[0])) {
|
insProductResult2.setFrequency(s);
|
break;
|
}
|
}
|
}
|
if (ObjectUtils.isEmpty(insProductResult2.getFrequency())) {
|
throw new ErrorException("没有找到" + stringListEntry.getKey() + "对应的频段信息,请先选择频段信息进行保存");
|
}
|
insProductResult2s.add(insProductResult2);
|
}
|
insProductResult2Service.saveBatch(insProductResult2s);
|
}
|
|
//读取天线的文件
|
public void readDianLuFile2(Integer sampleId, InsOrderFile insOrderFile) {
|
List<InsProductResult2> insProductResult2s = new ArrayList<>();
|
String excelFilePath = wordUrl + "/" + insOrderFile.getFileUrl(); // 更新为你的文件路径
|
List<TianXianData> tianXianData = readExcelData2(excelFilePath);
|
//查询检验任务id
|
List<InsOrderState> orderStates = insOrderStateMapper.selectList(Wrappers.<InsOrderState>lambdaQuery()
|
.eq(InsOrderState::getInsSampleId, sampleId)
|
.eq(InsOrderState::getLaboratory, "电路试验"));
|
List<InsProduct> products = insProductMapper.selectList(Wrappers.<InsProduct>lambdaQuery()
|
.eq(InsProduct::getState, 1)
|
.eq(InsProduct::getInsSampleId, sampleId));
|
//查询该样品的电压驻波比项目id
|
List<InsProduct> products1 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("电压驻波比")).collect(Collectors.toList());
|
//查询该样品的同极化隔离度项目id
|
List<InsProduct> products2 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("同极化隔离度")).collect(Collectors.toList());
|
//查询该样品的异极化隔离度项目id
|
List<InsProduct> products3 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("异极化隔离度")).collect(Collectors.toList());
|
//查询该样品的幅度偏差项目id
|
List<InsProduct> products4 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("幅度偏差")).collect(Collectors.toList());
|
//查询该样品的最大相位偏差项目id
|
List<InsProduct> products5 = products.stream().filter(insProduct -> insProduct.getInspectionItemSubclass().equals("最大相位偏差")).collect(Collectors.toList());
|
//获取所有频段,根据频段进行分类
|
Map<String, List<TianXianData>> map = tianXianData.stream().collect(Collectors.groupingBy(TianXianData::frequency, LinkedHashMap::new, Collectors.toList()));
|
for (Map.Entry<String, List<TianXianData>> listEntry : map.entrySet()) {
|
//获取信息,根据检验项目进行分类
|
Map<String, List<TianXianData>> collect = listEntry.getValue().stream().collect(Collectors.groupingBy(TianXianData::getName, LinkedHashMap::new, Collectors.toList()));
|
for (Map.Entry<String, List<TianXianData>> entry : collect.entrySet()) {
|
InsProductResult2 insProductResult2 = new InsProductResult2();
|
if (entry.getKey().contains("驻波")) {
|
if (products1.size() <= 0) {
|
throw new ErrorException("电压驻波比项目未找到");
|
}
|
insProductResult2.setInsProductId(products1.get(0).getId());//电压驻波比
|
insProductResult2.setFrequency(listEntry.getKey());//频段
|
insProductResult2.setAngle("0°");//角度
|
insProductResult2.setNum(orderStates.get(0).getNum());//次数
|
//获取端口
|
String port = entry.getValue().stream().map(TianXianData::getPort).collect(Collectors.joining(","));
|
insProductResult2.setPort(port);//端口
|
//获取检验值
|
String value = entry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
|
insProductResult2.setValue("[[" + value + "]]");//值
|
insProductResult2s.add(insProductResult2);
|
} else if (entry.getKey().contains("同极化隔离") || entry.getKey().contains("同列隔离")) {
|
if (products2.size() <= 0) {
|
throw new ErrorException("同极化隔离项目未找到");
|
}
|
insProductResult2.setInsProductId(products2.get(0).getId());//同极化隔离
|
insProductResult2.setFrequency(listEntry.getKey());//频段
|
insProductResult2.setAngle("0°");//角度
|
insProductResult2.setNum(orderStates.get(0).getNum());//次数
|
//获取端口
|
String port = entry.getValue().stream().map(TianXianData::getPort).collect(Collectors.joining(","));
|
insProductResult2.setPort(port);//端口
|
//获取检验值
|
String value = entry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
|
insProductResult2.setValue("[[" + value + "]]");//值
|
insProductResult2s.add(insProductResult2);
|
} else if (entry.getKey().contains("异极化隔离") || entry.getKey().contains("端口间隔离")) {
|
if (products3.size() <= 0) {
|
throw new ErrorException("异极化隔离项目未找到");
|
}
|
insProductResult2.setInsProductId(products3.get(0).getId());//异极化隔离
|
insProductResult2.setFrequency(listEntry.getKey());//频段
|
insProductResult2.setAngle("0°");//角度
|
insProductResult2.setNum(orderStates.get(0).getNum());//次数
|
//获取端口
|
String port = entry.getValue().stream().map(TianXianData::getPort).collect(Collectors.joining(","));
|
insProductResult2.setPort(port);//端口
|
//获取检验值
|
String value = entry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
|
insProductResult2.setValue("[[" + value + "]]");//值
|
insProductResult2s.add(insProductResult2);
|
}
|
}
|
}
|
/*幅度和相位需要另外写功能*/
|
//先过滤出端口号一栏为偏差值的数据
|
List<TianXianData> dataList = tianXianData.stream().filter(tianXianData1 -> tianXianData1.getPort().equals("偏差值")).collect(Collectors.toList());
|
//再根据检验项目进行分组
|
Map<String, List<TianXianData>> collect = dataList.stream().collect(Collectors.groupingBy(TianXianData::getName, LinkedHashMap::new, Collectors.toList()));
|
for (Map.Entry<String, List<TianXianData>> entry : collect.entrySet()) {
|
if (entry.getKey().contains("幅度")) {
|
if (products4.size() <= 0) {
|
throw new ErrorException("幅度偏差项目未找到");
|
}
|
Map<String, List<TianXianData>> listHashMap = new HashMap<>();
|
for (TianXianData xianData : entry.getValue()) {
|
//获取频段(先根据start字段判断这个频点在哪个频段之间)
|
List<String> strings = map.keySet().stream().filter(s ->
|
!s.split("MHz")[0].split("-")[0].equals(s.split("MHz")[0].split("-")[1])
|
).collect(Collectors.toList());
|
for (String s : strings) {
|
String[] mHzs = s.split("MHz")[0].split("-");
|
//再判断这个频点是否在频段的范围内
|
if (Double.parseDouble(xianData.getStart()) <= Double.parseDouble(mHzs[1]) && Double.parseDouble(xianData.getStart()) >= Double.parseDouble(mHzs[0])) {
|
// 检查是否已经存在这个键的条目
|
List<TianXianData> xianDataList = listHashMap.getOrDefault(s, new ArrayList<>());
|
// 将数据添加到列表中，而不是覆盖旧值
|
xianDataList.add(xianData);
|
// 将更新后的列表放回map中
|
listHashMap.put(s, xianDataList);
|
break;
|
}
|
}
|
}
|
for (Map.Entry<String, List<TianXianData>> listEntry : listHashMap.entrySet()) {
|
InsProductResult2 insProductResult2 = new InsProductResult2();
|
insProductResult2.setInsProductId(products4.get(0).getId());//幅度偏差
|
insProductResult2.setAngle("0°");//角度
|
insProductResult2.setNum(orderStates.get(0).getNum());//次数
|
insProductResult2.setFrequency(listEntry.getKey());//频段
|
//获取端口
|
String port = listEntry.getValue().stream().map(tianXianData1 -> String.valueOf((int) Double.parseDouble(tianXianData1.getStart()))).collect(Collectors.joining(","));
|
insProductResult2.setPort(port);//端口
|
//获取检验值
|
String value = listEntry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
|
insProductResult2.setValue("[[" + value + "]]");//值
|
insProductResult2s.add(insProductResult2);
|
}
|
} else if (entry.getKey().contains("相位")) {
|
if (products5.size() <= 0) {
|
throw new ErrorException("最大相位偏差项目未找到");
|
}
|
Map<String, List<TianXianData>> listHashMap = new HashMap<>();
|
for (TianXianData xianData : entry.getValue()) {
|
//获取频段(先根据start字段判断这个频点在哪个频段之间)
|
List<String> strings = map.keySet().stream().filter(s ->
|
!s.split("MHz")[0].split("-")[0].equals(s.split("MHz")[0].split("-")[1])
|
).collect(Collectors.toList());
|
for (String s : strings) {
|
String[] mHzs = s.split("MHz")[0].split("-");
|
//再判断这个频点是否在频段的范围内
|
if (Double.parseDouble(xianData.getStart()) <= Double.parseDouble(mHzs[1]) && Double.parseDouble(xianData.getStart()) >= Double.parseDouble(mHzs[0])) {
|
// 检查是否已经存在这个键的条目
|
List<TianXianData> xianDataList = listHashMap.getOrDefault(s, new ArrayList<>());
|
// 将数据添加到列表中，而不是覆盖旧值
|
xianDataList.add(xianData);
|
// 将更新后的列表放回map中
|
listHashMap.put(s, xianDataList);
|
}
|
}
|
}
|
for (Map.Entry<String, List<TianXianData>> listEntry : listHashMap.entrySet()) {
|
InsProductResult2 insProductResult2 = new InsProductResult2();
|
insProductResult2.setInsProductId(products5.get(0).getId());//最大相位偏差
|
insProductResult2.setAngle("0°");//角度
|
insProductResult2.setNum(orderStates.get(0).getNum());//次数
|
insProductResult2.setFrequency(listEntry.getKey());//频段
|
//获取端口
|
String port = listEntry.getValue().stream().map(tianXianData1 -> String.valueOf((int) Double.parseDouble(tianXianData1.getStart()))).collect(Collectors.joining(","));
|
insProductResult2.setPort(port);//端口
|
//获取检验值
|
String value = listEntry.getValue().stream().map(tianXianData1 -> "\"" + tianXianData1.getValue() + "\"").collect(Collectors.joining(","));
|
insProductResult2.setValue("[[" + value + "]]");//值
|
insProductResult2s.add(insProductResult2);
|
}
|
}
|
}
|
|
Map<String, List<InsProductResult2>> groupedMap = insProductResult2s.stream()
|
.collect(Collectors.groupingBy(item -> item.getInsProductId() + "_" + item.getFrequency()));
|
// 过滤出分组后数量等于1的组
|
List<InsProductResult2> filteredList = groupedMap.values().stream()
|
.filter(list -> list.size() == 1)
|
.flatMap(List::stream)
|
.collect(Collectors.toList());
|
for (Map.Entry<String, List<InsProductResult2>> entry : groupedMap.entrySet()) {
|
if (entry.getValue().size() > 1) {
|
InsProductResult2 result2 = new InsProductResult2();
|
result2.setInsProductId(Integer.parseInt(entry.getKey().split("_")[0]));
|
result2.setFrequency(entry.getKey().split("_")[1]);
|
result2.setPort(entry.getValue().stream().map(InsProductResult2::getPort).collect(Collectors.joining(",")));
|
String value = entry.getValue().stream().map(insProductResult2 -> {
|
return insProductResult2.getValue().replace("[[", "").replace("]]", "");
|
}).collect(Collectors.joining(","));
|
result2.setValue("[[" + value + "]]");
|
result2.setAngle("0°");
|
result2.setOften(entry.getValue().stream().map(InsProductResult2::getOften).collect(Collectors.joining(",")));
|
filteredList.add(result2);
|
}
|
}
|
insProductResult2Service.saveBatch(filteredList);
|
}
|
|
|
public static List<HuTiaoData> readExcelData1(String filePath) {
|
List<HuTiaoData> dataList = new ArrayList<>();
|
|
// 初始化监听器
|
AnalysisEventListener<HuTiaoData> listener = new AnalysisEventListener<HuTiaoData>() {
|
@Override
|
public void invoke(HuTiaoData data, AnalysisContext context) {
|
// 处理每行数据，这里简单地添加到数据列表中
|
dataList.add(data);
|
}
|
|
@Override
|
public void doAfterAllAnalysed(AnalysisContext context) {
|
// 所有数据解析完成后的操作，这里暂不实现
|
}
|
};
|
|
// 读取Excel文件
|
EasyExcel.read(filePath, HuTiaoData.class, listener).sheet().doRead();
|
|
return dataList;
|
}
|
|
public static List<TianXianData> readExcelData2(String filePath) {
|
List<TianXianData> dataList = new ArrayList<>();
|
|
// 初始化监听器
|
AnalysisEventListener<TianXianData> listener = new AnalysisEventListener<TianXianData>() {
|
@Override
|
public void invoke(TianXianData data, AnalysisContext context) {
|
// 处理每行数据，这里简单地添加到数据列表中
|
dataList.add(data);
|
}
|
|
@Override
|
public void doAfterAllAnalysed(AnalysisContext context) {
|
// 所有数据解析完成后的操作，这里暂不实现
|
}
|
};
|
|
try (BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "GB2312"));
|
Workbook workbook = new XSSFWorkbook();
|
FileOutputStream fileOut = new FileOutputStream(filePath.replace("csv", "xlsx"))) {
|
Sheet sheet = workbook.createSheet("Sheet1");
|
String line;
|
int rowNum = 0;
|
while ((line = reader.readLine()) != null) {
|
Row row = sheet.createRow(rowNum++);
|
String[] columns = line.split(",");
|
for (int i = 0; i < columns.length; i++) {
|
Cell cell = row.createCell(i);
|
cell.setCellValue(columns[i]);
|
}
|
}
|
workbook.write(fileOut);
|
System.out.println("CSV 文件已成功转换为 Excel 文件");
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
|
// 读取Excel文件
|
EasyExcel.read(filePath.replace("csv", "xlsx"), TianXianData.class, listener).sheet().headRowNumber(-1).doRead();
|
return dataList;
|
}
|
|
}
|
