package com.mes.tools;
|
|
import com.google.gson.Gson;
|
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 java.io.FileInputStream;
|
import java.io.FileWriter;
|
import java.io.IOException;
|
import java.util.ArrayList;
|
import java.util.Iterator;
|
import java.util.LinkedHashMap;
|
import java.util.List;
|
|
public class ExcelToJsonConverter {
|
|
public static void main(String[] args) {
|
String excelFilePath = "src/main/resources/JsonFile/alarm.xlsx";
|
String sheetName = "Sheet1";
|
int addressColumnIndex = 0;
|
int nameColumnIndex = 1;
|
|
//int unitColumnIndex = 2;
|
String outputFilePath = "src/main/resources/JsonFile/PlcAlarm.json";
|
|
try (Workbook workbook = new XSSFWorkbook(new FileInputStream(excelFilePath))) {
|
Sheet sheet = workbook.getSheet(sheetName);
|
|
List<LinkedHashMap<String, Object>> jsonList = new ArrayList<>();
|
|
Iterator<Row> rowIterator = sheet.iterator();
|
// Skip the header row
|
if (rowIterator.hasNext()) {
|
rowIterator.next();
|
}
|
|
int plcAddressLength = 0; // 记录所有 addressLenght 的和
|
int addressIndex = 0; // 自增的地址索引
|
while (rowIterator.hasNext()) {
|
Row row = rowIterator.next();
|
|
Cell nameCell = row.getCell(nameColumnIndex);
|
Cell addressCell = row.getCell(addressColumnIndex);
|
// Cell unitCell = row.getCell(unitColumnIndex);
|
|
String name = nameCell.getStringCellValue();
|
String address = addressCell.getStringCellValue();
|
// String unit = unitCell.getStringCellValue();
|
|
// String addressIndex = extractAddressIndex(address);
|
|
LinkedHashMap<String, Object> jsonObject = new LinkedHashMap<>();
|
jsonObject.put("codeId", name);
|
jsonObject.put("addressIndex", addressIndex);
|
|
int addressLength = 0;
|
if (address.contains("~")) {
|
addressLength = 14;
|
} else {
|
addressLength = 2;
|
}
|
// jsonObject.put("addressLenght", String.valueOf(addressLength));
|
|
//jsonObject.put("unit", unit);
|
|
plcAddressLength += addressLength;
|
|
jsonList.add(jsonObject);
|
addressIndex++;
|
}
|
|
LinkedHashMap<String, Object> resultObject = new LinkedHashMap<>();
|
resultObject.put("plcAddressBegin", "DB100.0");
|
resultObject.put("plcAddressLenght", String.valueOf(plcAddressLength));
|
// resultObject.put("dataType", "word");
|
resultObject.put("dataType", "bit");
|
resultObject.put("parameteInfor", jsonList);
|
|
Gson gson = new Gson();
|
String jsonOutput = gson.toJson(resultObject);
|
|
try (FileWriter fileWriter = new FileWriter(outputFilePath)) {
|
fileWriter.write(jsonOutput);
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
|
|
private static String extractAddressIndex(String address) {
|
// Assuming the address format is "DB103.DBW0" or "DB103.DBB100~DBB113"
|
if (address.startsWith("DB") && address.contains(".DBW")) {
|
int startIndex = address.indexOf(".DBW") + 4;
|
int endIndex = address.length();
|
return address.substring(startIndex, endIndex);
|
} else if (address.startsWith("DB") && address.contains(".DBB")) {
|
int startIndex2 = address.indexOf(".DBB") + 4;
|
int endIndex2 = address.indexOf("~");
|
return address.substring(startIndex2, endIndex2);
|
}
|
return "";
|
}
|
}
|
