采集器调整

zouyu

2025-12-09 866a3e6cbd2df9841dfbbd733e1128938cef3e00

采集器调整

 pom.xml

@@ -19,7 +19,11 @@
            <artifactId>fastjson</artifactId>
            <version>2.0.23</version>
        </dependency>

        <dependency>
            <groupId>com.fazecast</groupId>
            <artifactId>jSerialComm</artifactId>
            <version>2.10.2</version> <!-- 检查最新版本 -->
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
@@ -39,13 +43,24 @@
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.3</version>
            <version>5.2.5</version>
        </dependency>
        <!-- 处理 .xlsx (OOXML) 格式 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
            <version>5.2.5</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>5.2.5</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-full</artifactId>
            <version>5.2.5</version>
        </dependency>

        <dependency>
@@ -63,26 +78,6 @@
            <groupId>net.sf.ucanaccess</groupId>
            <artifactId>ucanaccess</artifactId>
            <version>5.0.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.17</version>
        </dependency>

        <dependency>

 src/main/java/com/chinaztt/mes/docx/controller/DocxController.java

@@ -29,4 +29,11 @@
    public R<?> moveFile(String startFilePath, String endFilePath, String fileType) {
        return docxService.moveFile(startFilePath, endFilePath, fileType);
    }


    @GetMapping("/test")
    public void test() {
         docxService.test();
    }

}

 src/main/java/com/chinaztt/mes/docx/dto/GetFileDto.java

@@ -22,6 +22,10 @@
    private String fiberOpticRibbonCode;
    // 光纤带数据
    private String fiberOpticRibbon;
    // 管套色标字段
    private String bushingColorField;
    // 管套色标数据
    private String bushingColor;
    //数据库用户名
    private String dbUserName;
    //数据库密码

 src/main/java/com/chinaztt/mes/docx/service/DocxService.java

@@ -12,5 +12,6 @@

    R<?> moveFile(String startFilePath, String endFilePath, String fileType);

    void test();

}

 src/main/java/com/chinaztt/mes/docx/service/impl/DocxServiceImpl.java

@@ -6,12 +6,16 @@
import com.chinaztt.mes.docx.service.DocxService;
import com.chinaztt.mes.docx.util.R;
import com.chinaztt.mes.docx.util.TakeWords;
import com.fazecast.jSerialComm.SerialPort;
import com.fazecast.jSerialComm.SerialPortDataListener;
import com.fazecast.jSerialComm.SerialPortEvent;
import net.sourceforge.tess4j.TesseractException;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
@@ -22,20 +26,24 @@

    @Override
    public R<?> getFile(GetFileDto getFileDto) throws IOException, SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException, TesseractException {
        File file = getFileStart(getFileDto.getFilePath(), getFileDto.getFileExtension());
        String fileExtension = getFileDto.getFileExtension();
        if(StringUtils.equals(".pngInExcel",getFileDto.getFileExtension())){
            fileExtension = ".xls";
        }
        File file = getFileStart(getFileDto.getFilePath(), fileExtension);
        if (file != null && !file.exists()) {
            return R.failed("未查询到该路径：" + getFileDto.getFilePath() + "下存在：" + getFileDto.getFileExtension() + "结尾的文件！");
        }
        switch (getFileDto.getFileExtension()) {
            case ".docx":
                return R.ok(TakeWords.readWordFile(file));
            //后缀为.xls的文件
            case ".pngInExcel":
                return R.ok(TakeWords.readPngContextInExcel(file));
            case ".xls":
                return R.ok(TakeWords.readExcelxlsFile(file));
            case ".xlsx":
                try {
                    return R.ok(TakeWords.readExcelFile(file));
                } catch (FileNotFoundException e) {
                } catch (Exception e) {
                    return R.failed("另一个程序正在使用此文件，无法进行数据采集。");
                }
            case ".txt":
@@ -130,4 +138,86 @@
        });
        return getLatestFile(list);
    }

    @Override
    public void test(){
        // 1. 获取所有可用的串口
        SerialPort[] ports = SerialPort.getCommPorts();
        if (ports.length == 0) {
            System.err.println("未找到可用的串口！");
            return;
        }

        // 打印所有可用串口（供用户选择）
        System.out.println("可用串口列表：");
        SerialPort targetPort = null;
        for (int i = 0; i < ports.length; i++) {
            if(ports[i].getSystemPortName().equals("COM7")){
                targetPort = ports[i];
            }
            System.out.printf("%d: %s (%s)\n", i + 1, ports[i].getSystemPortName(), ports[i].getPortDescription());
        }

        // 2. 选择目标串口（示例：选择第一个串口）

        System.out.println("\n选择的串口：" + targetPort.getSystemPortName());

        // 3. 配置串口参数（必须与设备一致，否则通信失败）
        targetPort.setComPortParameters(
                9600,                // 波特率（常见值：9600、19200、38400、115200）
                8,                   // 数据位（通常为 8）
                SerialPort.ONE_STOP_BIT,  // 停止位（1 位）
                SerialPort.NO_PARITY     // 校验位（无校验）
        );
        targetPort.setComPortTimeouts(SerialPort.TIMEOUT_READ_SEMI_BLOCKING, 100, 0); // 读取超时设置

        // 4. 打开串口
        if (!targetPort.openPort()) {
            System.err.println("串口打开失败！请检查端口是否被占用或权限是否足够。");
            return;
        }
        System.out.println("串口打开成功！");

        // 5. 注册数据监听（异步读取数据）
        SerialPort finalTargetPort = targetPort;
        targetPort.addDataListener(new SerialPortDataListener() {
            @Override
            public int getListeningEvents() {
                return SerialPort.LISTENING_EVENT_DATA_AVAILABLE; // 监听数据可用事件
            }

            @Override
            public void serialEvent(SerialPortEvent event) {
                System.out.println("asasa"+event.toString());
                if (event.getEventType() != SerialPort.LISTENING_EVENT_DATA_AVAILABLE) {
                    return;
                }

                // 读取可用数据
                byte[] readBuffer = new byte[finalTargetPort.bytesAvailable()];
                int bytesRead = finalTargetPort.readBytes(readBuffer, readBuffer.length);

                if (bytesRead > 0) {
                    // 转换为字符串（根据设备编码调整，常见：UTF-8、GBK）
                    String data = new String(readBuffer, 0, bytesRead, StandardCharsets.UTF_8);
                    System.out.printf("收到数据（%d 字节）：%s", bytesRead, data);
                }
            }
        });

        // 6. 保持程序运行（避免主线程退出）
        try {
            while (true) {
                Thread.sleep(1000); // 主线程休眠，不影响监听线程
            }
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
            System.out.println("程序被中断。");
        } finally {
            // 7. 关闭串口（程序退出时释放资源）
            targetPort.closePort();
            System.out.println("串口已关闭。");
        }
    }

}

 src/main/java/com/chinaztt/mes/docx/util/TakeWords.java

@@ -1,6 +1,8 @@
package com.chinaztt.mes.docx.util;

import cn.hutool.core.io.FileUtil;
import cn.hutool.http.HttpRequest;
import cn.hutool.json.JSONUtil;
import com.chinaztt.mes.docx.dto.GetFileDto;
import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
@@ -9,20 +11,19 @@
import net.sourceforge.tess4j.TesseractException;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.POIXMLTextExtractor;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hwpf.extractor.WordExtractor;
import org.apache.poi.ooxml.POIXMLDocument;
import org.apache.poi.ooxml.extractor.POIXMLTextExtractor;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xwpf.extractor.XWPFWordExtractor;

import java.io.*;
import java.nio.file.Files;
import java.sql.*;
import java.util.*;
import java.util.regex.Pattern;
@@ -57,19 +58,22 @@
        return result;
    }

    public static Object readExcelFile(File file) throws IOException {
    /**
     * 读取excel文件,兼容.xlsx,.xls格式
     * @param file
     * @return
     */
    public static Object readExcelFile(File file) {
        StringBuilder result = new StringBuilder();
        //创建工作簿对象
        try {
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(Files.newInputStream(file.toPath()));
            //获取工作簿下sheet的个数 只读取第一个sheet
//            int sheetNum = xssfWorkbook.getNumberOfSheets();
        try (
                FileInputStream fis = new FileInputStream(file);
                Workbook workbook = WorkbookFactory.create(fis)
        ) {
            //遍历工作簿中的所有数据
            XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
            Sheet sheet = workbook.getSheetAt(0);
            //获取最后一行的num，即总行数。此处从0开始
            int maxRow = sheet.getLastRowNum();
            for (int row = 1; row <= maxRow; row++) {
                //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
            for (int row = 0; row <= maxRow; row++) {
                int maxRol = sheet.getRow(row).getLastCellNum();
                StringBuilder aLine = new StringBuilder();
                for (int rol = 0; rol < maxRol; rol++) {
@@ -83,7 +87,13 @@
        return result.toString();
    }

    public static Object readExcelxlsFile(File file) throws IOException {
    /**
     * 读取excel文件中的图片内容
     * @param file
     * @return
     * @throws IOException
     */
    public static Object readPngContextInExcel(File file) throws IOException {
        String result = "";
        try (FileInputStream fis = new FileInputStream(file);
             Workbook workbook = new HSSFWorkbook(fis)) {
@@ -104,100 +114,18 @@
                try (FileOutputStream fos = new FileOutputStream(tempFile)) {
                    fos.write(pictureData);
                }
                String ocrResult = "";
                String ocrResult;
                try {
                    ocrResult = (String) readPngFile(tempFile);
                } catch (TesseractException e) {
                    ocrResult = "OCR识别失败: " + e.getMessage();
                    ocrResult = ocrImageContext(tempFile.getAbsolutePath());
                } finally {
                    // 删除临时文件
                    tempFile.delete();
                }
                result = ocrResult;
//                String ocrText = fixOcrText(ocrResult);
//                result.append("OCR Result:").append(ocrText).append(",");


//                for (HSSFPictureData picture : pictures) {
//                    // 获取图片类型
//                    String pictureType = picture.suggestFileExtension();
//                    // 获取图片数据
//                    byte[] pictureData = picture.getData();
//                    // 创建临时文件
//                    File tempFile = File.createTempFile(UUID.randomUUID().toString(), "." + pictureType);
//                    try (FileOutputStream fos = new FileOutputStream(tempFile)) {
//                        fos.write(pictureData);
//                    }
//                    // 图片预处理
////                    File processedFile = preprocessImage(tempFile, pictureType);
//                    // 调用 readPngFile1 方法读取图片文字信息
//                    String ocrResult = "";
//                    try {
//                        ocrResult = (String) readPngFile(tempFile);
////                        ocrResult = (String) readPngFile(tempFile);
////                        ocrResult = (String) readPngFile(processedFile);
//                    } catch (TesseractException e) {
//                        ocrResult = "OCR识别失败: " + e.getMessage();
//                    } finally {
//                        // 删除临时文件
////                        tempFile.delete();
////                        processedFile.delete();
//                    }
//
//                    // 将图片信息添加到结果中
////                    result.append("Picture Type: ").append(pictureType)
////                            .append(", Picture Size: ").append(pictureData.length)
////                            .append(" bytes")
////                            .append(", OCR Result: ").append(ocrResult)
////                            .append(",");
//                    String ocrText = fixOcrText(ocrResult);
//                    result.append("OCR Result:").append(ocrText).append(",");
//                }
            }
//
//            // 遍历每一行
//            for (Row row : sheet) {
//                // 遍历每一列
//                for (Cell cell : row) {
//                    CellType cellType = CellType.forInt(cell.getCellType());
//                    switch (cellType) {
//                        case STRING:
//                            result.append(cell.getStringCellValue());
//                            break;
//                        case NUMERIC:
//                            if (DateUtil.isCellDateFormatted(cell)) {
//                                result.append(cell.getDateCellValue());
//                            } else {
//                                result.append(cell.getNumericCellValue());
//                            }
//                            break;
//                        case BOOLEAN:
//                            result.append(cell.getBooleanCellValue());
//                            break;
//                        case FORMULA:
//                            result.append(cell.getCellFormula());
//                            break;
//                        default:
//                            result.append("");
//                    }
//                    result.append("\t");
//                }
//                result.append("\n");
//            }
        }
        return result;

    }

    // 修正 OCR 识别文本中的错误关键词
    public static String fixOcrText(String ocrText) {
        // 定义错误关键词和正确内容的映射，这里处理“击 宇 强 庞”修正为“击穿强度”
        // 考虑到可能有空格分隔，用正则匹配包含这些字的内容
        ocrText = ocrText.replaceAll("击\\s*宇\\s*强\\s*庞", "击穿强度");
        // 还可以继续添加其他错误修正，比如下面假设“电 压 \\(HV\\)”里的空格影响，也修正下
        ocrText = ocrText.replaceAll("电\\s*压\\s*\\(HV\\)", "电压(KV)");
        ocrText = ocrText.replaceAll("电\\s*流\\s*\\(nt\\)", "电流(mA)");
        return ocrText;
    }

    public static Object readPngFile1(File file) throws IOException, TesseractException {
@@ -284,8 +212,8 @@
        Properties prop = new Properties();
        //设置编码
        prop.put("charSet", "UTF-8");
        prop.put("user", "");
        prop.put("password", "");
        prop.put("user",  StringUtils.isNotBlank(getFileDto.getDbUserName())?getFileDto.getDbUserName():"");
        prop.put("password", StringUtils.isNotBlank(getFileDto.getDbPassword())?getFileDto.getDbPassword():"");
        //数据地址
        String dbUrl = "jdbc:ucanaccess://" + file.getPath();
        //引入驱动
@@ -299,8 +227,6 @@
        try {
            List<Object> list = new ArrayList<>();
            //遍历获取多张表数据
//            String s = "select * from " + getFileDto.getDbFileName() + " where 1=1" + getFileDto.getMdbEntrustCode() + " = '" + getFileDto.getEntrustCode() +
//                    "' and " + getFileDto.getMdbSampleCode() + " = '" + getFileDto.getSampleCode() + "'";
            String s = "select * from " + getFileDto.getDbFileName() + " where 1=1";
            if(StringUtils.isNotBlank(getFileDto.getMdbEntrustCode())){
                s+=" and " + getFileDto.getMdbEntrustCode() + " = '" + getFileDto.getEntrustCode()+ "'";
@@ -308,6 +234,10 @@
            if(StringUtils.isNotBlank(getFileDto.getMdbSampleCode())){
                s+=" and " + getFileDto.getMdbSampleCode() + " = '" + getFileDto.getSampleCode() + "'";
            }
            if(StringUtils.isNotBlank(getFileDto.getBushingColorField())){
                s+=" and " + getFileDto.getBushingColorField() + " = '" + getFileDto.getBushingColor() + "'";
            }

            preparedStatement = conn.prepareStatement(s);
            rs = preparedStatement.executeQuery();
            ResultSetMetaData data = rs.getMetaData();
@@ -370,9 +300,9 @@

            String sql = "SELECT * FROM "+table+" WHERE 1=1";
            if(StringUtils.isNotBlank(getFileDto.getMdbEntrustCode()) ){
                sql+=" AND (" + getFileDto.getMdbEntrustCode() + " = '" + getFileDto.getEntrustCode()+ "'";
                sql+=" AND (" + getFileDto.getMdbEntrustCode() + " = TRIM('" + getFileDto.getEntrustCode()+ "')";
                if(StringUtils.isNotBlank(getFileDto.getLotBatchNo())){
                    sql+=" OR "+ getFileDto.getMdbEntrustCode() + " = '" + getFileDto.getLotBatchNo()+ "'";
                    sql+=" OR "+ getFileDto.getMdbEntrustCode() + " = TRIM('" + getFileDto.getLotBatchNo()+ "')";
                }
                sql+=")";
            }
@@ -382,7 +312,6 @@
            if(StringUtils.isNotBlank(getFileDto.getCableTag())){
                sql+=" AND Color = '" + getFileDto.getCableTag() + "'";
            }

            // 创建 PreparedStatement 对象执行 SQL
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
@@ -468,6 +397,19 @@
        return tableMap;
    }

    /**
     * ocr识别图片内容
     * @param imagePath 图片路径
     * @return
     */
    public static String ocrImageContext(String imagePath){
        //调用ocr识别服务
        Map<String,Object> jsonMap = new HashMap<>();
        jsonMap.put("imagePath",imagePath);
        String requestBody = JSONUtil.toJsonStr(jsonMap);
        return HttpRequest.post("localhost:8080/ocr/recognize").body(requestBody).execute().body();
    }

    public static Object readPngFile(File file) throws IOException, TesseractException {
        String canonicalPath32 = FileUtil.file(".", "/jre_32/tessdata").getCanonicalPath();
        String canonicalPath64 = FileUtil.file(".", "/jre_64/tessdata").getCanonicalPath();