package com.example.springboot.component;
|
|
import cn.hutool.json.JSONObject;
|
import com.google.gson.Gson;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.FileWriter;
|
import java.io.IOException;
|
import java.util.ArrayList;
|
import java.util.Iterator;
|
import java.util.List;
|
|
public class ExcelToJsonConverter {
|
public static void main(String[] args) {
|
String excelFilePath = "JsonFile/state.xlsx";
|
String sheetName = "Sheet1";
|
int addressColumnIndex = 0;
|
int nameColumnIndex = 1;
|
|
int unitColumnIndex = 2;
|
String outputFilePath = "JsonFile/state.json";
|
|
try (Workbook workbook = new HSSFWorkbook(new FileInputStream(excelFilePath))) {
|
Sheet sheet = workbook.getSheet(sheetName);
|
|
List<JSONObject> jsonList = new ArrayList<>();
|
|
Iterator<Row> rowIterator = sheet.iterator();
|
// Skip the header row
|
if (rowIterator.hasNext()) {
|
rowIterator.next();
|
}
|
|
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);
|
|
JSONObject jsonObject = new JSONObject();
|
jsonObject.put("codeId", name);
|
jsonObject.put("addressIndex", addressIndex);
|
|
jsonObject.put("unit", unit);
|
|
jsonList.add(jsonObject);
|
}
|
|
Gson gson = new Gson();
|
String jsonOutput = gson.toJson(jsonList);
|
|
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 "";
|
}
|
|
|
}
|
