From 866a3e6cbd2df9841dfbbd733e1128938cef3e00 Mon Sep 17 00:00:00 2001
From: zouyu <2723363702@qq.com>
Date: 星期二, 09 十二月 2025 17:45:16 +0800
Subject: [PATCH] 采集器调整
---
src/main/java/com/chinaztt/mes/docx/util/TakeWords.java | 299 +++++++++++++++++++++++++++++++++--------------------------
1 files changed, 165 insertions(+), 134 deletions(-)
diff --git a/src/main/java/com/chinaztt/mes/docx/util/TakeWords.java b/src/main/java/com/chinaztt/mes/docx/util/TakeWords.java
index 3a7cf0c..58fb34a 100644
--- a/src/main/java/com/chinaztt/mes/docx/util/TakeWords.java
+++ b/src/main/java/com/chinaztt/mes/docx/util/TakeWords.java
@@ -1,39 +1,41 @@
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;
import net.sourceforge.tess4j.Tesseract;
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.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
-import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hwpf.extractor.WordExtractor;
-import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
+import org.apache.poi.ooxml.POIXMLDocument;
+import org.apache.poi.ooxml.extractor.POIXMLTextExtractor;
import org.apache.poi.openxml4j.opc.OPCPackage;
-import org.apache.poi.ss.usermodel.*;
-import org.apache.poi.xssf.usermodel.XSSFSheet;
-import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xwpf.extractor.XWPFWordExtractor;
-import java.awt.Color;
-import java.awt.image.BufferedImage;
-import javax.imageio.ImageIO;
import java.io.*;
-import java.nio.file.Files;
import java.sql.*;
import java.util.*;
-import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class TakeWords {
private static final String splitIdentifier = "@-@"; // 鑷畾涔夊敮涓�鏍囪瘑绗�
+
+ // 绉戝璁℃暟娉曟鍒欐ā寮�
+ private static final Pattern SCIENTIFIC_PATTERN = Pattern.compile(
+ "^[+-]?\\d+(\\.\\d+)?[eE][+-]?\\d+$"
+ );
public static Object readWordFile(File file) {
String result = "";
@@ -56,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鐨勪釜鏁� 鍙鍙栫涓�涓猻heet
-// 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++) {
@@ -76,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)) {
// 鑾峰彇绗竴涓伐浣滆〃
@@ -90,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 {
@@ -200,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);
@@ -210,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();
@@ -220,21 +174,37 @@
StringBuilder stringBuilder = new StringBuilder();
// 鍒涘缓 reader
- try (BufferedReader br = Files.newBufferedReader(file.toPath())) {
- // CSV鏂囦欢鐨勫垎闅旂
- String DELIMITER = ",";
- // 鎸夎璇诲彇
- String line;
- while ((line = br.readLine()) != null) {
- // 鍒嗗壊
- String[] columns = line.split(DELIMITER);
- // 鎵撳嵃琛�
- stringBuilder.append(String.join(splitIdentifier, columns)).append("\n");
+ try (FileReader fileReader = new FileReader(file);
+ CSVReader csvReader = new CSVReaderBuilder(fileReader).build()) {
+
+ String[] nextLine;
+ while ((nextLine = csvReader.readNext()) != null) {
+ // 澶勭悊姣忎竴琛屾暟鎹�
+ for (String cell : nextLine) {
+ if(StringUtils.isNotBlank(cell)){
+ stringBuilder.append(scientificToNumber(cell)).append(splitIdentifier);
+ }
+ }
+ stringBuilder.append("\n");
}
- } catch (IOException ex) {
- ex.printStackTrace();
+ } catch (IOException e) {
+ e.printStackTrace();
+ } catch (CsvValidationException e) {
+ throw new RuntimeException(e);
}
return stringBuilder.toString();
+ }
+
+ /**
+ * 灏嗙瀛﹁鏁版硶杞崲涓烘暟瀛�
+ * @param cell
+ * @return
+ */
+ public static String scientificToNumber(String cell){
+ if(SCIENTIFIC_PATTERN.matcher(cell).matches()){
+ return String.valueOf(Double.parseDouble(cell));
+ }
+ return cell;
}
public static Object readMdbFile(File file, GetFileDto getFileDto) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
@@ -242,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();
//寮曞叆椹卞姩
@@ -257,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()+ "'";
@@ -266,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();
@@ -280,6 +252,7 @@
}
tableMap.put("data", list);
} catch (Exception e) {
+ e.printStackTrace();
} finally {
closeA1l(conn, preparedStatement, rs);
}
@@ -300,33 +273,78 @@
} catch (Exception ignore) {
}
}
- public static Object getmysqlFile(GetFileDto getFileDto) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
+
+ public static Object getMysqlFile(GetFileDto getFileDto){
Map<String, Object> tableMap = new HashMap<>(16);
- // 鏁版嵁搴撹繛鎺ヤ俊鎭�
- String url = "jdbc:mysql://localhost:3306/"+getFileDto.getDbFileName()+"?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true";
+ // 浠� GetFileDto 鑾峰彇鏁版嵁搴撳悕锛屽搴斻�愭枃浠跺悕绉般�戝瓧娈�
+ String dbName = getFileDto.getDbFileName();
String user = getFileDto.getDbUserName();
String password = getFileDto.getDbPassword();
- List<ThicknessData> dataList = new ArrayList<>();
+ // 浠� GetFileDto 鑾峰彇鏁版嵁琛ㄥ悕锛屽搴斻�愭暟鎹簱琛ㄥ悕銆戝瓧娈�
+ String table = getFileDto.getDbTable();
+ // 妫�鏌ユ暟鎹簱鍚嶅拰琛ㄥ悕鏄惁涓虹┖
+ if (dbName == null || dbName.isEmpty() || table == null || table.isEmpty()) {
+ return R.failed("鏁版嵁搴撳悕鎴栬〃鍚嶄笉鑳戒负绌�");
+ }
+ // 鏁版嵁搴撹繛鎺ヤ俊鎭�
+ 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;
+ List<Map<String, Object>> dataList = new ArrayList<>();
- try (
- // 寤虹珛杩炴帴
- Connection connection = DriverManager.getConnection(url, user, password);
- // 鍒涘缓 Statement 瀵硅薄鎵ц SQL
- Statement statement = connection.createStatement()
- ) {
- String sql = "SELECT ThinnestPoint, AverageThickness FROM model1records";
- ResultSet resultSet = statement.executeQuery(sql);
+ try {
+ // 寤虹珛杩炴帴
+ connection = DriverManager.getConnection(url, user, password);
+ // 鏋勫缓鍩虹 SQL
+ 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);
+ resultSet = preparedStatement.executeQuery();
+ ResultSetMetaData metaData = resultSet.getMetaData();
+ int columnCount = metaData.getColumnCount();
// 閬嶅巻缁撴灉闆嗚幏鍙栨暟鎹�
while (resultSet.next()) {
- double thinnestPoint = resultSet.getDouble("ThinnestPoint");
- double averageThickness = resultSet.getDouble("AverageThickness");
- dataList.add(new ThicknessData(thinnestPoint, averageThickness));
+ Map<String, Object> rowData = new HashMap<>();
+ for (int i = 1; i <= columnCount; i++) {
+ String columnName = metaData.getColumnName(i);
+ rowData.put(columnName, resultSet.getObject(i));
+ }
+ dataList.add(rowData);
}
tableMap.put("data", dataList);
} catch (Exception e) {
e.printStackTrace();
+ // 鍋囪 R 绫绘湁 failed 鏂规硶锛岃嫢娌℃湁闇�琛ュ厖瀹炵幇
return R.failed("鏁版嵁搴撴煡璇㈠嚭閿�: " + e.getMessage());
+ } finally {
+ try {
+ if (resultSet != null) {
+ resultSet.close();
+ }
+ if (preparedStatement != null) {
+ preparedStatement.close();
+ }
+ if (connection != null) {
+ connection.close();
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
}
return tableMap;
}
@@ -379,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();
--
Gitblit v1.9.3