使用Apache POI和Spring读取excel中的合并单元格

Question

我正在构建一个函数，读取excel文件中可用的数据。

我在Spring项目中使用Apache POI。

但是，我在阅读合并单元格时遇到了困难。我需要的是得到如图所示的红色高亮显示的内容。

public void importClassroomByExcel(MultipartFile file) {
        try {
            Workbook workbook = new HSSFWorkbook(file.getInputStream());
            Iterator<Sheet> sheetIterator = workbook.iterator();
            while (sheetIterator.hasNext()) {
                Sheet sheet = sheetIterator.next();
                String title = sheet.getRow(4).getCell(5).toString();

            }
            workbook.close();
        } catch (Exception e) {
            throw new BadRequestAlertException(ErrorConstants.DEFAULT_TYPE, "", e.getMessage(), "filestructerror",
                    Status.BAD_REQUEST);
        }
    }

Answer 1

我不确定你的要求是否正确。我认为这就是您想要的：在工作表中，数据范围从第5行开始，A列确定分组。现在需要为每个组获取列F和G的值。然后需要在第N列中获得每个组的第一个日期，在第O列中获得每个组的最后一个日期。

因此，需要为每个组获取第一行，并为每个组获取最后填充的行。下面的示例显示了这一点。它主要使用int getNextFilledRow（Sheet Sheet,int columnNum,int startRowNum）方法从给定行号开始获取工作表给定列中的下一个填充行。

代码应该只获取给定工作表中具有红色标记内容的单元格。

import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.*;

class ReadExcelMergedCells {

 static int getNextFilledRow(Sheet sheet, int columnNum, int startRowNum) {
  int lastRow = sheet.getLastRowNum();
  int nextFilledRow = lastRow;
  for (int r = startRowNum; r <= lastRow; r++) {
   nextFilledRow = r + 1; 
   Row row = sheet.getRow(nextFilledRow);
   Cell cell = (row != null)?cell = row.getCell(columnNum):null;
   if (cell != null && cell.getCellType() != CellType.BLANK) {
    break;
   }
  }
  return nextFilledRow;
 }

 public static void main(String[] args) throws Exception {
  Workbook workbook = WorkbookFactory.create(new FileInputStream("./Excel.xlsx"));
  Sheet sheet = workbook.getSheetAt(0);
  int lastRow = sheet.getLastRowNum();
  int startRow = 4;
  Row row = null;
  Cell cell = null;
  int columnNum = -1;
  int columNumA = 0; //column A determines the grouping
  for (int r = startRow; r <= lastRow; r = getNextFilledRow(sheet, columNumA, r)) {
   //r is the first row in this group
System.out.println("Group starting in row " + r);

   //get cell in column F of first row in this group
   columnNum = 5;
   row = sheet.getRow(r);
   cell = (row != null)?cell = row.getCell(columnNum):null;
System.out.println(cell);

   //get cell in column G of first row in this group
   columnNum = 6;
   row = sheet.getRow(r);
   cell = (row != null)?cell = row.getCell(columnNum):null;
System.out.println(cell);

   //get cell in column N of first row in this group
   columnNum = 13;
   row = sheet.getRow(r);
   cell = (row != null)?cell = row.getCell(columnNum):null;
System.out.println(cell);

   //get cell in column O of last filled row in this group
   columnNum = 14;
   int lastRowInGroup = getNextFilledRow(sheet, columNumA, r) -1;
   // get last filled cell in this group
   for (int rl = lastRowInGroup; rl >= r; rl--) {
    row = sheet.getRow(rl);
    cell = (row != null)?cell = row.getCell(columnNum):null;
    if (cell != null && cell.getCellType() != CellType.BLANK) {
     break;
    }
   }
System.out.println(cell);

System.out.println("Group ending in row " + lastRowInGroup);

  }

  workbook.close();
 }
}