java - 如何将Excel表格转换为Json

Question

我的任务是读取文件(特别是 Excel 工作表)，并将其转换为 JSON 以进行 API 调用。

Excel 数据:

MAIN_ID  SUB_ID    VALUE
1000     1000-A1    10
1000     1000-A2    15
1000     1000-A3    20 // MAX Value against MAIN_ID
1001     1001-A1    12
1001     1001-A2    14
1001     1001-A3    25 // MAX Value against MAIN_ID

我的要求:

编辑:

我忘记在此处添加一个必需的逻辑，因为我正在创建 JSON，所以我需要将 Max VALUE 设置为针对 MAIN_ID 的 VALUE。

即我需要创建一个 JSON 文件，例如:

{
   "data": [
      {
         "MAIN_ID": "1000",
         "SUB_ID": "1000-A1",
         "VALUE": "20"
      },
      {
         "MAIN_ID": "1000",
         "SUB_ID": "1000-A2",
         "VALUE": "20"
      },
      {
         "MAIN_ID": "1000",
         "SUB_ID": "1000-A3",
         "VALUE": "20"
      },
      {
         "MAIN_ID": "1001",
         "SUB_ID": "1001-A1",
         "VALUE": "25"
      },
      {
         "MAIN_ID": "1001",
         "SUB_ID": "1001-A2",
         "VALUE": "25"
      },
      {
         "MAIN_ID": "1001",
         "SUB_ID": "1001-A3",
         "VALUE": "25"
      }
   ]
}

Answer 1

您可以像这样简单地将对象转换为 toJson；

public String toJson(ArrayList<Object> objects){
    String result="[";
    for(int i=0;i<objects.getSize();i++){
     result+="{";
     result+="MAIN_ID:"+ objects.get(i).mainId+",";
     result+="SUB_ID:"+ objects.get(i).subId+",";
     result+="VALUE:"+ objects.get(i).value+",";
     result+="}";
     if(i<object.getSize()-1){
      result+=",";
    }
   }
result+=']';
return result;
}

尝试 Apache POI HSSF。以下是如何读取 Excel 文件的示例:

try {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row;
    HSSFCell cell;

    int rows; // No of rows
    rows = sheet.getPhysicalNumberOfRows();

    int cols = 0; // No of columns
    int tmp = 0;

    // This trick ensures that we get the data properly even if it doesn't start from first few rows
    for(int i = 0; i < 10 || i < rows; i++) {
        row = sheet.getRow(i);
        if(row != null) {
            tmp = sheet.getRow(i).getPhysicalNumberOfCells();
            if(tmp > cols) cols = tmp;
        }
    }

    for(int r = 0; r < rows; r++) {
        row = sheet.getRow(r);
        if(row != null) {
            for(int c = 0; c < cols; c++) {
                cell = row.getCell((short)c);
                if(cell != null) {
                    // Your code here
                }
            }
        }
    }
} catch(Exception ioe) {
    ioe.printStackTrace();
}

**编辑:**可以根据MAIN_ID更新列表值字段您可以在更新对象列表后迭代列表并创建新列表 mainIdAndValueList(MainId-Value)