java - 使用 Apache POI 时某些单元格不可见 - Java

Question

使用 Apache POI 开发程序时，出现了问题。读取文件后，Excel 电子表格的某些行中的某些单元格消失。有谁知道为什么会发生这种情况以及如何解决它？我使用的是 Apache POI 版本 3.17。

我需要解析的Excel表格的一部分

迭代器，显示某些单元格不可读

更新1:部分代码

XSSFWorkbook workbook = new XSSFWorkbook(Main.class.getResourceAsStream("/schedule/" + fileName));

    XSSFSheet sheet = workbook.getSheetAt(0);

    Iterator<Row> rowIterator = sheet.iterator();

    while (rowIterator.hasNext())
    {
        Row row = rowIterator.next();

        int rowNum = row.getRowNum();

        Iterator<Cell> cellIterator = row.cellIterator();

        int cellNumber = -1;

        while (cellIterator.hasNext())
        {
            cellNumber++;

            Cell cell = cellIterator.next();

            CellType cellType = cell.getCellTypeEnum();

            switch (cellType)
            {
                case STRING:
                    if (checkGroupCellPlus(cell.getStringCellValue()))
                    {
                        Group = cell.getStringCellValue();

                        Main._Log.info("Группа " + Group + " подана на разбор");

                        groupId++;

                        groupDao.Insert(groupId, Group);

                        Iterator<Row> rowIteratorSchedule = sheet.iterator();

                        Row rowSchedule = rowIteratorSchedule.next();

                        for (int i=0; i < rowNum+1; i++)
                        {
                            rowSchedule = rowIteratorSchedule.next();
                        }

                        Integer classTime = 1;
                        Short dayOfWeek = 0;
                        Short numberOfWeek = 1;

                        while (rowSchedule.getRowNum() < 74)
                        {

                            rowSchedule = rowIteratorSchedule.next();

                            String subject = "";
                            String subjectType = "";
                            String teacher = "";
                            String classroom = "";
                            String teacherName = "";
                            String teacherSername = "";
                            String teacherSecondName = "";

                            Iterator<Cell> cellIteratorSchedule = rowSchedule.iterator();

                            Cell cellSchedule = cellIteratorSchedule.next();

                            String day = cellSchedule.getStringCellValue();

                            switch(day.toLowerCase())
                            {
                                case("понедельник"):
                                    dayOfWeek = 1;
                                    break;
                                case("вторник"):
                                    dayOfWeek = 2;
                                    break;
                                case("среда"):
                                    dayOfWeek = 3;
                                    break;
                                case("четверг"):
                                    dayOfWeek = 4;
                                    break;
                                case("пятница"):
                                    dayOfWeek = 5;
                                    break;
                                case("суббота"):
                                    dayOfWeek = 6;
                                    break;
                            }

                            /**Integer cellNumber1 = cellNumber;
                            if (numberOfWeek == 2)
                                cellNumber1 = cellSchedule.getColumnIndex();*/

                            for (int i=0; i < cellNumber; i++)
                            {
                                cellSchedule = cellIteratorSchedule.next();
                            }

                            switch (cellSchedule.getCellTypeEnum())
                            {
                                case STRING:
                                    if (!cellSchedule.getStringCellValue().toLowerCase().
                                            replaceAll("[^A-Za-zА-Яа-я]", "").equals("") &&
                                            !cellSchedule.getStringCellValue().toLowerCase().equals("день") &&
                                            !cellSchedule.getStringCellValue().toLowerCase().equals("самостоятельных")
                                            && !cellSchedule.getStringCellValue().toLowerCase().equals("занятий") &&
                                            !cellSchedule.getStringCellValue().toLowerCase().equals("военная") &&
                                            !cellSchedule.getStringCellValue().toLowerCase().equals("подготовка") &&
                                            !cellSchedule.getStringCellValue().toLowerCase().
                                                    equals("военная подготовка") &&
                                            !cellSchedule.getStringCellValue().toLowerCase().
                                                    equals("занятия по адресу:") && !cellSchedule.getStringCellValue().
                                            toLowerCase().equals("ул. М.Пироговская, д.1"))
                                    {
                                        subject = cellSchedule.getStringCellValue();
                                    }
                                    break;
                                case NUMERIC:
                                    Double subject1 = cellSchedule.getNumericCellValue();
                                    subject = subject1.toString();
                            }
                            cellSchedule = cellIteratorSchedule.next();

                                switch (cellSchedule.getCellTypeEnum())
                                        {
                                            case STRING:
                                                if (!cellSchedule.getStringCellValue().toLowerCase().
                                                        replaceAll("[^A-Za-zА-Яа-я]", "").equals(""))
                                                {
                                                    subjectType = cellSchedule.getStringCellValue();
                                                }
                                                break;

                                            case NUMERIC:
                                                Double subjectType1 = cellSchedule.getNumericCellValue();
                                                subjectType = subjectType1.toString();
                                                break;
                                        }

                            cellSchedule = cellIteratorSchedule.next();

                            switch (cellSchedule.getCellTypeEnum())
                            {
                                case STRING:
                                    if (!cellSchedule.getStringCellValue().toLowerCase().
                                            replaceAll("[^A-Za-zА-Яа-я]", "").equals(""))
                                    {
                                        teacher = cellSchedule.getStringCellValue();
                                    }
                                    break;

                                case NUMERIC:
                                    Double teacher1 = cellSchedule.getNumericCellValue();
                                    teacher = teacher1.toString();
                                    break;
                            }

                            cellSchedule = cellIteratorSchedule.next();

                            switch (cellSchedule.getCellTypeEnum())
                            {
                                case STRING:
                                    if (!cellSchedule.getStringCellValue().toLowerCase().
                                            replaceAll("[^A-Za-zА-Яа-я]", "").equals(""))
                                    {
                                        classroom = cellSchedule.getStringCellValue();
                                    }
                                    break;

                                case NUMERIC:
                                    Double classroom1 = cellSchedule.getNumericCellValue();
                                    classroom = classroom1.toString();
                                    break;
                            }

                                if (teacher.matches("[А-Я]([а-я]{1,})\\s[А-Я]\\.[А-Я]\\."))
                                {

                                    teacherSecondName = teacher.substring(0, teacher.indexOf(" "));
                                    teacher = teacher.replaceFirst(teacherSecondName, "");
                                    teacherName = teacher.substring(0, teacher.indexOf("."));
                                    teacher = teacher.replaceFirst(teacherName, "");
                                    teacher = teacher.replaceFirst("\\.", "");
                                    teacherSername = teacher.substring(0, teacher.indexOf("."));
                                }
                                else if (!teacher.contains(".")) teacherSecondName = teacher;


                               Main._Log.info("Разобрана запись " + fileName + " " + Group + " " + subject + " " + subjectType + " " + teacherSecondName + " " + dayOfWeek + " " + numberOfWeek + " " + classTime + " " + classroom);

                                if (!teacherSecondName.equals("") || !subject.equals("") || !subjectType.equals("") ||
                                        !classroom.equals(""))
                                {
                                    teacherDao.Insert(teacherId, teacherName, teacherSername, teacherSecondName,
                                            null, null);

                                    subjectDao.Insert(subjectId, subject, teacherId);

                                    classroomDao.Insert(classroomId, classroom, null);

                                    subjectTypeDao.Insert(subjectTypeId, subjectId, subjectType);

                                    scheduleDao.Insert(scheduleId, classTime, classroomId, subjectId, dayOfWeek,
                                            numberOfWeek);

                                    teacherId++;
                                    subjectId++;
                                    classroomId++;
                                    subjectTypeId++;
                                    scheduleId++;
                                }

                                if (numberOfWeek == 1)
                                    numberOfWeek++;
                                else if (numberOfWeek == 2)
                                {
                                    numberOfWeek = 1;
                                    classTime++;
                                }

                                if (classTime == 7)
                                    classTime = 1;
                        }

                    }
                    break;

                case NUMERIC:
                    break;
            }
        }
    }

Answer 1

这是 Microsoft 在 Excel 文件格式中设计的，Excel 稀疏地存储单元格，即仅存储已定义的单元格，而不是现有单元格之间的空单元格。

因此，Apache POI 中没有改变这一点的计划。您的代码需要对此进行调整，Row.getCell() 将为此类单元格返回 null。迭代器不会返回那些空的 Cell。

迭代时可以使用Cell.getColumnIndex()查看您在行中的位置，而不是通过 cellNumber++

自己进行计数