如何用Java Apache POI处理多值单元格XLSX并生成JSONArray

使用Java Apache POI读取XLSX文件，文件包含Teacher、class&Section、Subject三列，其中class&Section和Subject列的单元格存在多组逗号分隔的值，需要遍历文件并构建符合指定结构的JSONArray。

尝试代码 #

try {
    FileInputStream inputStream = new FileInputStream(new File("C:/Users/HP/Downloads/school.xlsx"));
    //FileInputStream inputStream = new FileInputStream(new File("TestExecution.xlsx"));

    //HashMap<Integer, Data> mp= new HashMap<Integer, Data>();
    HashMap<String, List<String>> mp= new HashMap<>();
    Workbook workbook = new XSSFWorkbook(inputStream);

    Sheet AddCatalogSheet  = workbook.getSheetAt(0);
  //Find number of rows in excel file
    int rowcount = AddCatalogSheet.getLastRowNum()- AddCatalogSheet.getFirstRowNum();
    System.out.println("Total row number: "+rowcount);
    for(int i=1; i<rowcount+1; i++){
        //Create a loop to get the cell values of a row for one iteration
        Row row = AddCatalogSheet.getRow(i);
        List<String> arrName = new ArrayList<String>();
        for(int j=0; j<row.getLastCellNum(); j++){
            // Create an object reference of 'Cell' class
            Cell cell = row.getCell(j);
            switch (cell.getCellType()) 
            {
                case Cell.CELL_TYPE_NUMERIC:
                    //System.out.print(cell.getNumericCellValue() + "	");
                    arrName.add(NumberToTextConverter.toText(cell.getNumericCellValue()));
                    break;
                case Cell.CELL_TYPE_STRING:
                    //System.out.print(cell.getStringCellValue() + "	");
                    arrName.add(cell.getStringCellValue());
                    break;
            }
            // Add all the cell values of a particular row
            
            }
        System.out.println(arrName);
        System.out.println("Size of the arrayList: "+arrName.size());
        // Create an iterator to iterate through the arrayList- 'arrName'
        JSONObject teacher = new JSONObject();
        
        JSONArray jsonArray = new JSONArray();

        for (int counter = 0; counter < arrName.size(); counter++) {              
            System.out.println(arrName.get(counter));   
            jsonArray.put(arrName.get(counter));
        } 
        System.out.println(jsonArray.toString()); 
        }
        
    
} catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

预期JSON结构 #

[
  {
    "Teacher_code": "23424234",
    "class": [
      {
        "class": "6",
        "section": "A"
      },
      {
        "class": "7",
        "section": "B"
      },
      {
        "class": "8",
        "section": "A"
      }
    ],
    "subject_name": [
      {
        "subject": "Tamil"
      },
      {
        "subject": "English"
      },
      {
        "subject": "Maths"
      }
    ]
  }
]

核心思路是读取每行的三个字段后，分别处理class&Section和Subject的逗号分隔值，将其拆分为对应结构的JSON对象/数组，最后组装成目标JSONArray。

修改后的代码 #

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONObject;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

public class ExcelToJsonConverter {
    public static void main(String[] args) {
        JSONArray resultArray = new JSONArray();

        try (FileInputStream inputStream = new FileInputStream(new File("C:/Users/HP/Downloads/school.xlsx"));
             Workbook workbook = new XSSFWorkbook(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getLastRowNum() - sheet.getFirstRowNum();

            for (int i = 1; i <= rowCount; i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                // 读取三列的值
                String teacherCode = getCellValue(row.getCell(0));
                String classSectionStr = getCellValue(row.getCell(1));
                String subjectsStr = getCellValue(row.getCell(2));

                // 构建单个教师的JSON对象
                JSONObject teacherObj = new JSONObject();
                teacherObj.put("Teacher_code", teacherCode);

                // 处理class&Section列，转换为JSON数组
                JSONArray classArray = new JSONArray();
                if (classSectionStr != null && !classSectionStr.isEmpty()) {
                    String[] classSections = classSectionStr.split(",");
                    for (String cs : classSections) {
                        cs = cs.trim();
                        // 按&拆分班级和学段，可根据实际数据格式调整分隔符（如空格、冒号）
                        String[] parts = cs.split("&");
                        if (parts.length == 2) {
                            JSONObject classItem = new JSONObject();
                            classItem.put("class", parts[0].trim());
                            classItem.put("section", parts[1].trim());
                            classArray.put(classItem);
                        }
                    }
                }
                teacherObj.put("class", classArray);

                // 处理Subject列，转换为JSON数组
                JSONArray subjectArray = new JSONArray();
                if (subjectsStr != null && !subjectsStr.isEmpty()) {
                    String[] subjects = subjectsStr.split(",");
                    for (String subj : subjects) {
                        subj = subj.trim();
                        JSONObject subjectItem = new JSONObject();
                        subjectItem.put("subject", subj);
                        subjectArray.put(subjectItem);
                    }
                }
                teacherObj.put("subject_name", subjectArray);

                resultArray.put(teacherObj);
            }

            // 打印格式化后的结果
            System.out.println(resultArray.toString(4));

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 统一处理单元格值，兼容多种单元格类型
    private static String getCellValue(Cell cell) {
        if (cell == null) return null;
        switch (cell.getCellType()) {
            case NUMERIC:
                return NumberToTextConverter.toText(cell.getNumericCellValue());
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
}

关键说明 #

1. 统一单元格读取：封装getCellValue方法，兼容数值、字符串等多种单元格类型，避免重复代码。
2. class&Section处理：假设单元格内每组值格式为班级&学段（如6&A），按逗号拆分后再按&拆分班级和学段，可根据实际数据格式调整分隔符。
3. Subject处理：按逗号拆分每个科目，将每个科目包装为{"subject": "科目名"}的JSON对象，加入subject_name数组。
4. 资源自动管理：使用try-with-resources语法自动关闭文件流和Workbook，避免资源泄漏。

内容的提问来源于stack exchange，提问作者Java_Prog_Ideas