Read Excel header and row value

Question

I would need to read the excel content which has header and N.of rows. Based on column header input,the row needs to be extracted in JAVA.

the java code which i has reads the full excel content.

        //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);
        int totalRows = sheet.getPhysicalNumberOfRows();


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

            while (cellIterator.hasNext()) 
            {
                Cell cell = cellIterator.next();
                //Check the cell type and format accordingly
                switch (cell.getCellType()) 
                {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                }
            }
            System.out.println("");

Excel content

Coln1 Coln2 Coln3 Coln4 
A        12   nice  3e
A        23   talk  s2
A        43   res   23
B        11   xl    34
B        88   out   r45
C        45    tr    h5

EXPECTED result

if (Coln1==B)
{

Loop the list of B rows (here its 2 rows)
Coln1 Coln2 Coln3 Coln4 
B        11   xl    34
B        88   out   r45

if i need , r45 , How to pass the row cell to get the value?

}

can someone please assist. Thanks

Answer 1

If header is row 1, maybe you can do this.

Row headerRow = sheet.getRow(0);   

You can define some constant for index. e.g

int n1ColIndex = 0;    // 0-based.    this is the index of row in excel.
int n2ColIndex = 1;    // 
int n3ColIndex = 2;
int n4ColIndex = 3;

// then you can read cell by Row.getCell() method.

// below code, you can put in loop.
XSSFCell n1Cell = row.getCell(n1ColIndex);
if ( n1Cell == null ){
    continue;
}

String n1Col = n1Cell.getStringCellValue();
if( "B".equals(n1Col) ) {
    // get r45 value.

    XSSFCell n4Cell = row.getCell(n4ColIndex);
    if ( n4Cell == null ){
        continue;
    }

    String val = n4Cell.getStringCellValue();
    // this value will be 34 or r45
    // Do you understand ?

}

// update 1. how to get a row.

// method 1.
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
    Row row = rowIterator.next();

    // do something with the row var.
}

// method 2.
for ( int index=0; index < sheet.getLastRowNum(); index++){
    Row row = sheet.getRow(index);

    // check null.
    if ( row == null ){
        continue;
    }

    // do something with the row var.

}

API DOC XSSFSheet

All write by hand, no test. Any question, reply me .