How to ignore last column in column count? I am using Apache POI to read excel data

Question

When I use below piece of code for TC_001, it returns 8 as a column count, but I want the last row to be ignored in counting column which has Comment section. I want column count as 5 as there are 5 column with text ignoring the comment(Action 1) part.

int colCount = DataProviderFactory.getExcel().getColCount(0, 1);//this is user defined function using Apache POI to get column count of sheet with index 0 and row 1

So is there any way that on how to get column count ignoring the "Comment" row from count? I guess if there is any way to treat "G" row as last row then it might solve it. So the TC_001 should have count as 5, TC_002 as 7, TC_003 as 4, TC_004 as 1 etc. I tried to search solution online & found this one but have no idea how to implement it whether I have to add this in code or in excel or this is something different from my requirement. Please guide me on how I can achieve my requirement.

Answer 1

row.getPhysicalNumberOfCells();

is what you are looking for. From the documentation of that method:

Gets the number of defined cells (NOT number of cells in the actual row!). That is to say if only columns 0,4,5 have values then there would be 3.

As you now want to ignore the last column, you can simply do row.getPhysicalNumberOfCells() - 1;, given that the comment is always filled.

EDIT:

This method should work for columns A to G:

private static int getNumOfCellsWithContent(int rowNum, Sheet sheet) {
    int result = 0;
    Row r = sheet.getRow(rowNum);
    if (r == null) {
        return 0;
    }
    for (int i = 0; i < 7/* 7 = H */; i++) {
        Cell c = r.getCell(i);
        if (c != null && !c.getCellTypeEnum().equals(CellType.BLANK)) {
            result++;
        }
    }
    return result;
}

Answer 2

I've created a function to get the value cell excel using Apache poi 3.17, hope this can help.

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

@SuppressWarnings("resource")
public class Test {
    public static void main(String[] args) throws IOException {
        String excelFile = "/path/Book1.xlsx";

        //check row TC_001
        int rowNumber = 1;

        int columnCount = 0;
        for(int i=0; i<7; i++) {
            String getCellValue = new Test().getCellDatatable(excelFile, 0, rowNumber, i);
            if(!getCellValue.equals("")) {
                System.out.println(getCellValue);
                columnCount++;
            }
        }

        System.out.println("columnCount :" +columnCount);
    }

    public String getCellDatatable(String pathAndDatatableName, int indexSheet, int indexRow, int indexColumn) throws IOException {
        String getString;

        File myDT = new File(pathAndDatatableName);

        FileInputStream myDoc = null;
        myDoc = new FileInputStream(myDT);

        XSSFWorkbook myExcel = null;
        myExcel = new XSSFWorkbook(myDoc);

        XSSFSheet mySheet = myExcel.getSheetAt(indexSheet);

        XSSFRow row = mySheet.getRow(indexRow);
        if(row!=null) {
            XSSFCell myGetString = mySheet.getRow(indexRow).getCell(indexColumn);
            getString = getStringByCell(myGetString);
            return getString;
        }else {
            return "";
        }
    }

    private String getStringByCell(Cell cell){
        String getStrCell = null;
            if(cell != null) {
                if(cell.getCellTypeEnum() == CellType.STRING) {
                    getStrCell = cell.toString();
                }else if(cell.getCellTypeEnum() == CellType.NUMERIC) {
                    if (DateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
                        getStrCell = dateFormat.format(cell.getDateCellValue());
                    }else {
                        Double value = cell.getNumericCellValue();
                        Long longValue = value.longValue();
                        getStrCell = new String(longValue.toString());
                    }
                }else if(cell.getCellTypeEnum() == CellType.BOOLEAN) {
                    getStrCell = new String(new Boolean(cell.getBooleanCellValue()).toString());
                }else if(cell.getCellTypeEnum() == CellType.BLANK) {
                    getStrCell = "";
                }
            }else {
                getStrCell = "";
            }
        return getStrCell;
    }
}