设备数据采集器
blame | 历史 | 补丁 | 提交 | 提交对比 | 忽略空白
zouyu
2025-12-09 866a3e6cbd2df9841dfbbd733e1128938cef3e00 
 src/main/java/com/chinaztt/mes/docx/util/TakeWords.java


@@ -1,8 +1,9 @@


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.chinaztt.mes.docx.dto.ThicknessData;


import com.opencsv.CSVReader;


import com.opencsv.CSVReaderBuilder;


import com.opencsv.exceptions.CsvValidationException;


@@ -10,16 +11,16 @@


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.*;


@@ -57,19 +58,22 @@


        return result;


    }




    public static Object readExcelFile(File file) throws FileNotFoundException, IOException {


    /**


     * 读取excel文件,兼容.xlsx,.xls格式


     * @param file


     * @return


     */


    public static Object readExcelFile(File file) {


        StringBuilder result = new StringBuilder();


        //创建工作簿对象


        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));


        //获取工作簿下sheet的个数 只读取第一个sheet


//            int sheetNum = xssfWorkbook.getNumberOfSheets();


        //遍历工作簿中的所有数据


        for (int i = 0; i < 1; i++) {


            XSSFSheet sheet = xssfWorkbook.getSheetAt(i);


        try (


                FileInputStream fis = new FileInputStream(file);


                Workbook workbook = WorkbookFactory.create(fis)


        ) {


            //遍历工作簿中的所有数据


            Sheet sheet = workbook.getSheetAt(0);


            //获取最后一行的num，即总行数。此处从0开始


            int maxRow = sheet.getLastRowNum();


            for (int row = 0; row <= maxRow; row++) {


                //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***


                int maxRol = sheet.getRow(row).getLastCellNum();


                StringBuilder aLine = new StringBuilder();


                for (int rol = 0; rol < maxRol; rol++) {


@@ -77,12 +81,20 @@


                }


                result.append(aLine).append("\n");


            }


        } catch (Exception e) {


            e.printStackTrace();


        }


        return result.toString();


    }




    public static Object readExcelxlsFile(File file) throws IOException {


        StringBuilder result = new StringBuilder();


    /**


     * 读取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)) {


            // 获取第一个工作表


@@ -91,86 +103,29 @@


            if (workbook instanceof HSSFWorkbook) {


                HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook;


                List<HSSFPictureData> pictures = hssfWorkbook.getAllPictures();


                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) readPngFile1(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(",");


                //处理最后一张图片数据


                HSSFPictureData lastPicture = pictures.get(pictures.size()-1);


                // 获取图片类型


                String pictureType = lastPicture.suggestFileExtension();


                // 获取图片数据


                byte[] pictureData = lastPicture.getData();


                // 创建临时文件


                File tempFile = File.createTempFile(UUID.randomUUID().toString(), "." + pictureType);


                try (FileOutputStream fos = new FileOutputStream(tempFile)) {


                    fos.write(pictureData);


                }


                String ocrResult;


                try {


                    ocrResult = ocrImageContext(tempFile.getAbsolutePath());


                } finally {


                    // 删除临时文件


                    tempFile.delete();


                }


                result = ocrResult;


            }


//


//            // 遍历每一行


//            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 {


@@ -201,9 +156,6 @@


        return tesseract.doOCR(file);


    }










    public static Object readTxtFile(File file) throws IOException {


        FileInputStream fin = new FileInputStream(file);


        InputStreamReader reader = new InputStreamReader(fin);


@@ -211,6 +163,7 @@


        StringBuilder stringBuilder = new StringBuilder();


        String strTmp = "";


        while ((strTmp = buffReader.readLine()) != null) {


            strTmp = strTmp.replaceAll("\t",",");


            stringBuilder.append(strTmp).append("\n");


        }


        buffReader.close();


@@ -221,21 +174,6 @@




        StringBuilder stringBuilder = new StringBuilder();


        // 创建 reader


//        try (BufferedReader br = Files.newBufferedReader(file.toPath())) {


//            // CSV文件的分隔符


//            String DELIMITER = ",";


//            // 按行读取


//            String line;


//            System.out.println(br.readLine());


//            while ((line = br.readLine()) != null) {


//                // 分割


//                String[] columns = line.split(DELIMITER);


//                // 打印行


//                stringBuilder.append(String.join(splitIdentifier, columns)).append("\n");


//            }


//        } catch (IOException ex) {


//            ex.printStackTrace();


//        }


        try (FileReader fileReader = new FileReader(file);


             CSVReader csvReader = new CSVReaderBuilder(fileReader).build()) {




@@ -274,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();


        //引入驱动


@@ -289,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()+ "'";


@@ -298,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();


@@ -312,6 +252,7 @@


            }


            tableMap.put("data", list);


        } catch (Exception e) {


            e.printStackTrace();


        } finally {


            closeA1l(conn, preparedStatement, rs);


        }


@@ -332,6 +273,7 @@


        } catch (Exception ignore) {


        }


    }




    public static Object getMysqlFile(GetFileDto getFileDto){


        Map<String, Object> tableMap = new HashMap<>(16);


        // 从 GetFileDto 获取数据库名，对应【文件名称】字段


@@ -345,7 +287,7 @@


            return R.failed("数据库名或表名不能为空");


        }


        // 数据库连接信息


        String url = "jdbc:mysql://localhost:3306/"+dbName+"?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true";


        String url = "jdbc:mysql://localhost:3306/"+dbName+"?useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&characterEncoding=utf8";


        Connection connection = null;


        PreparedStatement preparedStatement = null;


        ResultSet resultSet = null;


@@ -356,9 +298,22 @@


            connection = DriverManager.getConnection(url, user, password);


            // 构建基础 SQL




            StringBuilder sql = new StringBuilder("SELECT * FROM ").append(table);


            String sql = "SELECT * FROM "+table+" WHERE 1=1";


            if(StringUtils.isNotBlank(getFileDto.getMdbEntrustCode()) ){


                sql+=" AND (" + getFileDto.getMdbEntrustCode() + " = TRIM('" + getFileDto.getEntrustCode()+ "')";


                if(StringUtils.isNotBlank(getFileDto.getLotBatchNo())){


                    sql+=" OR "+ getFileDto.getMdbEntrustCode() + " = TRIM('" + getFileDto.getLotBatchNo()+ "')";


                }


                sql+=")";


            }


            if(StringUtils.isNotBlank(getFileDto.getMdbSampleCode())){


                sql+=" AND " + getFileDto.getMdbSampleCode() + " = '" + getFileDto.getSampleCode() + "'";


            }


            if(StringUtils.isNotBlank(getFileDto.getCableTag())){


                sql+=" AND Color = '" + getFileDto.getCableTag() + "'";


            }


            // 创建 PreparedStatement 对象执行 SQL


            preparedStatement = connection.prepareStatement(sql.toString());


            preparedStatement = connection.prepareStatement(sql);


            resultSet = preparedStatement.executeQuery();


            ResultSetMetaData metaData = resultSet.getMetaData();


            int columnCount = metaData.getColumnCount();


@@ -371,11 +326,6 @@


                }


                dataList.add(rowData);


            }


//            while (resultSet.next()) {


//                double thinnestPoint = resultSet.getDouble("ThinnestPoint");


//                double averageThickness = resultSet.getDouble("AverageThickness");


//                dataList.add(new ThicknessData(thinnestPoint, averageThickness));


//            }


            tableMap.put("data", dataList);


        } catch (Exception e) {


            e.printStackTrace();


@@ -447,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();