How to read from merged cells of Excel in Java using Apache POI?

Question

I have a Excel file in .xlsx format. I have stored data by merging cells to form various columns. I am reading the Excel file via a Java web application and saving its data to a database (MySQL). But when I read from merged cells I get null values along with what are stored in the columns as well as the headers. I am using Apache POI. My code is:

public static void excelToDBLogIN() {

    FileInputStream file = null;
    Boolean flag = true;
    ArrayList<String> rows = new ArrayList<String>();
    try {


        // here uploadFolder contains the path to the Login 3.xlsx file

        file = new FileInputStream(new File(uploadFolder + "Login 3.xlsx"));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();


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

            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            String tuple = "";
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();

                //Check the cell type and format accordingly
                switch (cell.getCellType()) {

                        case Cell.CELL_TYPE_NUMERIC:                            

                        //int value = new BigDecimal(cell.getNumericCellValue()).setScale(0, RoundingMode.HALF_UP).intValue();
                        //tuple = tuple + String.valueOf(value) + "+";

                        DataFormatter objDefaultFormat = new DataFormatter();    

                        String str = objDefaultFormat.formatCellValue(cell);

                        tuple = tuple + str + "+";

                        break;

                    case Cell.CELL_TYPE_STRING:

                        tuple = tuple + cell.getStringCellValue() + "+";

                        break;

                    case Cell.CELL_TYPE_BLANK:                                                        

                        tuple = tuple + "" + "+";

                        break;


                }

            }

            rows.add(tuple);
            flag = true;

        }

    }    


    } catch (Exception e) {

        e.printStackTrace();

    } finally {

        if (file != null) {

            try {
                file.close();
                file = null;
            } catch (Exception e) {

                System.out.println("File closing operation failed");
                e.printStackTrace();
            }
        }                                 

    }

    }

}

I searched for answers in the web but did not find anything relevant.

Solutrean

Merged cells are bad bad bad and should not be allowed. Avoid them. Excel will typically store the content of a merged range in the top left cell of that range. All other cells return 0. – Dariadarian

try this https://mcmap.net/q/750855/-find-merged-cell-in-excel-split-the-cells-and-write-those-in-new-spreadsheet – Thoroughgoing

I know but the format for the excel is being made by college departments. Our project is simply to take info from them and update the databse . I personally would have avoid those. – Solutrean

@Dariadarian Is there any particular reason why merged cells are bad? – Punch

@Punch yes. They upset things. Merge A3 to D3. Now try to select C1 to C5. Or use a loop to write something into C1 to C5. See why it's bad? – Dariadarian

Answer 1

Following code of snippet might help.

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

        //For each row, iterate through all the columns
        Iterator<Cell> cellIterator = row.cellIterator();

        outer:
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            //will iterate over the Merged cells
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress region = sheet.getMergedRegion(i); //Region of merged cells

                int colIndex = region.getFirstColumn(); //number of columns merged
                int rowNum = region.getFirstRow();      //number of rows merged
                //check first cell of the region
                if (rowNum == cell.getRowIndex() && colIndex == cell.getColumnIndex()) {
                    System.out.println(sheet.getRow(rowNum).getCell(colIndex).getStringCellValue());
                    continue outer;
                }
            }
            //the data in merge cells is always present on the first cell. All other cells(in merged region) are considered blank
            if (cell.getCellType() == Cell.CELL_TYPE_BLANK || cell == null) {
                continue;
            }
            System.out.println(cell.getStringCellValue());
        }
    }

Tude

Thanks but I decided to use unmerged cells in the excel. Others including those here have advised not to use merged cells excel files to feed data into applications. – Solutrean

@Deepika What if there are no merged colomns in the file will the code work – Clerc

The code works fine, even if there are no merged columns. But the Cell iterator fetches only those cells having non-null value. My requirement was to iterate every column, whether it is null or non-null. – Plataea

Answer 2

This method can read a specific cell (including merged cell):

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public static void readCell(String excelFilePath, int rowIndex, int columnIndex) throws FileNotFoundException, IOException {
    try (InputStream inp = new FileInputStream(excelFilePath)) {
        XSSFWorkbook wb = new XSSFWorkbook(inp);
        XSSFCell cell = wb.getSheetAt(0).getRow(rowIndex).getCell(columnIndex);

        switch (cell.getCellType()) {

        case STRING:
            System.out.println(cell.getRichStringCellValue().getString());
            break;

        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                System.out.println(cell.getDateCellValue());
            } else {
                System.out.println(cell.getNumericCellValue());
            }
            break;

        case BOOLEAN:
            System.out.println(cell.getBooleanCellValue());
            break;

        case FORMULA:
            System.out.println(cell.getCellFormula());
            break;

        case BLANK:
            System.out.println();
            break;

        default:
            System.out.println();
        }

        wb.close();
    }
}

Dependencies: POI 5.0.0, JDK 1.8.0

Feathers

What does it, though? Does it work via FORMULA? – Theorbo

A merged cell has a cellType of blank. So this doesn't seem to take merged cells into account. – Appling