how to read a value from Column Name in Excel in Java

Question

How can I read a value related to Specific Column Names with Apache POI in Excel (xlsx).

Column Names is my 1st Row of Excel Sheet . value is the row Which have Y as cell value .

My Input is only Column Name , I need value for the Column name and Row which has value "Y"

Below is the Excel Sheet

here I if enter Column names as "Names" , it should return value "Jose" And "Age" it should return 23

I have tried and found two codes with different loop system , but getting some error

public static void main(String[] args) throws Exception {

    File file =    new File("D:\\xcel.xlsx");
    FileInputStream inputStream = new FileInputStream(file);


     Workbook wb = new XSSFWorkbook(inputStream);
     Sheet sh = wb.getSheet("Sheet1");

    //  Find the Column number Which has column name and row number 0

     Row row1 = sh.getRow(0);       
     int colNum = -1;
     for (int i = 0 ;i<=row1.getLastCellNum();i++){
         Cell cell1 = row1.getCell(i,Row.CREATE_NULL_AS_BLANK);
         String cellValue1 = cell1.getStringCellValue();
         if ("Employee".equals(cellValue1)){
              colNum = i ;
              break;}
        }



    //  Find the Row number Which is "Y" and column number 1

     int rowNum = -1;        
     for (int j = 0 ;j<=sh.getLastRowNum()+1;j++){           
         Row row2 = sh.getRow(j); 
         Cell cell2 = row2.getCell(1,Row.CREATE_NULL_AS_BLANK);       
         String cellValue2 = cell2.getStringCellValue();
            if ("Y".equals(cellValue2))
            { rowNum = j ;
            break;}                     
        }



    Row r = sh.getRow(rowNum);
     String val = r.getCell(colNum).getStringCellValue();

     System.out.println("Row number is "+rowNum);
     System.out.println("Last row number :"+sh.getLastRowNum());
     System.out.println("Column number is "+colNum);
     System.out.println("Last Column number :"+sh.getRow(0).getLastCellNum());
     System.out.println("Value is "+val); 

}

When I run the above code , I get the column number even if there is a blank cell is present before it ,

but for row , if there is a blank cell present before it , i get error Exception in thread "main" java.lang.NullPointerException But it works fine if there is no black cell.

Again in this below code , I don't get any error

But if my column name is present in last cell ,then last cell number and my column number should be same , but I getting my column number is one less than the last cell number .

But for row number , its fine , I am getting same number .

This problem is applies to the first code also

public static void main(String[] args) throws Exception {

    File file =    new File("D:\\xcel.xlsx");
    FileInputStream inputStream = new FileInputStream(file);


     Workbook wb = new XSSFWorkbook(inputStream);
     Sheet sh = wb.getSheet("Sheet1");

    //  Find the Column number Which has column name and row number 0

     Row row1 = sh.getRow(0);       

     int colNum = -1;
     for (Cell cell : row1) {
            if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                if (cell.getRichStringCellValue().getString().trim().equals("Employee")) {
                    colNum =  cell.getColumnIndex();                                   }
                                                             }
                                }

    //  Find the Row number Which is "Y" and column number 1


     int rowNum = -1;
     for (Row row : sh) {
            for (Cell cell : row) {
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (cell.getRichStringCellValue().getString().trim().equals("Y")) {
                        rowNum =  row.getRowNum();                                     }
                                                                 }
                                    }
                        }               


 // Get the required value
    Row r = sh.getRow(rowNum);
     String val = r.getCell(colNum).getStringCellValue();

     System.out.println("Row number is "+rowNum);
     System.out.println("Last row number :"+sh.getLastRowNum());
     System.out.println("Column number is "+colNum);
     System.out.println("Last Column number :"+sh.getRow(0).getLastCellNum());
     System.out.println("Value is "+val); 

Answer 1

Apache POI does not support this kind of thing out of the bag. You might need to read your EXCEL file to an abstract structure of your own writting.

I would recommend something like a Document model, that contains your headers and contents.

If your purpose is solely gathering values by column name, with a bit of data redundancy you can store each value as a map entry of {column name : row value} in a list of all rows.

When having such a structure you can easily search by column values.

For example:

import java.util.ArrayList;
import java.util.List;

public class Document {

    private List<String> headers;

    private List<DataRow> contents;

    public Document(List<String> headers, List<DataRow> contents) {
        this.headers = headers;
        this.contents = contents;
    }

    public List<DataRow> findAllWhere(String column, String value) {
        List<DataRow> result = new ArrayList<>();
        for (DataRow content : contents) {
            if (content.get(column).equals(value)) {
                result.add(content);
            }
        }
        return result;
    }

    public List<DataRow> getContents() {
        return contents;
    }

    public void setContents(List<DataRow> contents) {
        this.contents = contents;
    }

    public List<String> getHeaders() {
        return headers;
    }

    public void setHeaders(List<String> headers) {
        this.headers = headers;
    }

    @Override
    public String toString() {
        return "Document{" +
                "headers=" + headers +
                ", contents=" + contents +
                '}';
    }

}

For rows we can have something like this:

import java.util.HashMap;
import java.util.Map;

public class DataRow {

    private Map<String, String> values = new HashMap<>();
    private Integer index;

    public String put(String columnName, String value) {
        return values.put(columnName, value);
    }

    public String get(String columnName) {
        return values.get(columnName);
    }

    public Integer getIndex() {
        return index;
    }

    public void setIndex(Integer index) {
        this.index = index;
    }


    @Override
    public String toString() {
        return "DataRow{" +
                "values=" + values +
                ", index=" + index +
                '}';
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        DataRow dataRow = (DataRow) o;

        if (values != null ? !values.equals(dataRow.values) : dataRow.values != null) return false;
        return !(index != null ? !index.equals(dataRow.index) : dataRow.index != null);

    }

    @Override
    public int hashCode() {
        int result = values != null ? values.hashCode() : 0;
        result = 31 * result + (index != null ? index.hashCode() : 0);
        return result;
    }

}

Example how to parse it:

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class Parser {

    private InputStream stream;

    public Parser(InputStream stream) {
        this.stream = stream;
    }

    public Document parse() {
        try {
            Workbook wb = WorkbookFactory.create(stream);

            //TODO better sheet searching
            Sheet sheet = wb.getSheetAt(0);
            Iterator<Row> rows = sheet.rowIterator();

            List<String> headers = new ArrayList<>();
            if (rows.hasNext()) {
                Row row = rows.next();
                for (Cell cell : row) {
                    headers.add(cell.getStringCellValue());
                }
            }

            List<DataRow> contents = new ArrayList<>();
            while (rows.hasNext()) {
                Row row = rows.next();
                DataRow dataRow = new DataRow();
                dataRow.setIndex(row.getRowNum());
                for (Cell cell : row) {
                    //TODO safeguard for header resolving, cell column index might be out of bound of header array
                    dataRow.put(
                            headers.get(cell.getColumnIndex()),
                            cell.getStringCellValue()
                    );
                }
                contents.add(dataRow);
            }

            return new Document(headers, contents);
        } catch (IOException | InvalidFormatException e) {
            throw new RuntimeException(e);
        }
    }

}

Example how to use:

 Parser parser = new Parser(getClass().getResourceAsStream("resource.xlsx"));
 List<DataRow> data = parser.parse().findAllWhere("FLAG", "Y");

Full mock up is available here

Is this what you are looking for? I must warn you, this is not a good idea to read the whole excel each time you need to search on it.

public static String findFirstExecute(String excelPath, String sheetName, String columnName) {
    return findFirstValue(excelPath, sheetName, columnName, "Execute", "Y");
}

public static String findFirstValue(String excelPath, String sheetName, String columnName, String filterColumnName, String filterColumnValue) {
    try {
        Workbook wb = WorkbookFactory.create(new FileInputStream(new File(excelPath)));

        Sheet sheet = wb.getSheet(sheetName);
        Iterator<Row> rows = sheet.rowIterator();

        Map<String, Integer> headers = new HashMap<>();
        if (rows.hasNext()) {
            Row row = rows.next();
            for (Cell cell : row) {
                headers.put(cell.getStringCellValue(), cell.getColumnIndex());
            }
        }

        while (rows.hasNext()) {
            Row row = rows.next();
            String executeValue = row.getCell(headers.get(filterColumnName)).getStringCellValue();
            if (executeValue.equals(filterColumnValue)) {
                return row.getCell(headers.get(columnName)).getStringCellValue();
            }
        }
        return null;
    } catch (IOException | InvalidFormatException e) {
        throw new RuntimeException(e);
    }
}