Read merge cell in excel using Apache POI with Spring

Question

I am building a function that reads the data available in the excel file.

I use Apache POI with the Spring project.

However, I am having trouble reading with merge cells. What I need is to get the content highlighted in red as shown in the picture.

Can you suggest me the code?

Thanks !

Update:

`

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);
        }
    }

`

What have you tried? Where exactly are you stuck? Cell values of merged cells are stored in top left cell of the merged region. So first red text is in `F5`: `sheet.getRow(4).getCell(5)`. First red 60 is in `G5`: `sheet.getRow(4).getCell(6)`. — Axel Richter, Jul 05 '20 at 04:20
`sheet.getRow(4).getCell(5)` I have obtained the first information. However, I cannot tell when the merged region ends to continue reading the next information? Do you understand me ? Also I want to get information in column N and column O I have highlighted in red. — Myloo Tran, Jul 05 '20 at 04:27
All cells except top left cell are empty in merged regions. So merged region ends in the row where the next cell value is found or when the row is sheet's last row number. And column `N` has column index 13 and column `O` has column index 14: So `N5`: `sheet.getRow(4).getCell(13)`. — Axel Richter, Jul 05 '20 at 04:49
Can you suggest a source for this? I have just updated the question containing my source code. — Myloo Tran, Jul 05 '20 at 08:09

score 3 · Accepted Answer · answered Jul 05 '20 at 14:27

Not sure I understand your requirement correct. This is what I think you want: In your sheet, the data range starts in row 5 and column A determines the grouping. Now the need is getting the values of columns F and G for each group. Then the need is getting the first date for each group in column N and the last date for each group in column O.

So the need is to get the first row for each group and the last filled row for each group. Following example shows this. Mainly it uses a method int getNextFilledRow(Sheet sheet, int columnNum, int startRowNum) to get the next filled row in a given column of the sheet starting on a given row number.

The code should only get the cells having the red marked contents in your given sheet.

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();
 }
}

score 0 · Answer 2 · answered Jul 04 '20 at 16:16

Code to change color of cell to red:

    CellStyle cellStyle = cell.getCellStyle();
    Font font = workbook.createFont();

    font.setFontName("Times New Roman");
    font.setColor(IndexedColors.RED.index);

    cellStyle.setFont(font);
    cellStyle.setFillBackgroundColor(IndexedColors.BLUE.index);

    cell.setCellStyle(cellStyle);

To merge from B2 to E2:

sheet.addMergedRegion(new CellRangeAddress(1,1,1,4));

See more in :

Sorry, i want get content get the content highlighted in red. Mustn't set style. — Myloo Tran, Jul 04 '20 at 16:30

Read merge cell in excel using Apache POI with Spring

2 Answers2