Merge branch 'master' of http://10.153.19.25:10101/r/HangZhouMes

wuyouming666

2024-04-18 e8d3676793d4194485afec7940aaf355af594901

 hangzhoumesParent/moduleService/UnLoadGlassModule/src/main/java/com/mes/tools/ExcelToJsonConverter.java

@@ -1,108 +1,107 @@
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 "";

    }

}

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 "";
    }
}