Kishan C S Kishan C S - 4 days ago 4
Java Question

Is it posible to find text and get cell index i.e[row number and column numer ] in excel xssf document using java?

Find and get column and row value of that text using java.
In Xssf document.

Find location of the text searched in excel file using java

Answer

This is covered in the Apache POI documentation, which you'd be very much suggested to read!

Specifically, in iterating over rows and cells and in getting the cell contents

Taking the code from there, and your need, we get:

String toFind = "needle in haystack";

Workbook wb = WorkbookFactory.create(new File("input.xlsx"));
DataFormatter formatter = new DataFormatter();
Sheet sheet1 = wb.getSheetAt(0);
for (Row row : sheet1) {
    for (Cell cell : row) {
        CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());

        // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
        String text = formatter.formatCellValue(cell);

        // is it an exact match?
        if (toFind.equals(text)) {
           System.out.println("Text matched at " + cellRef.formatAsString());
        }
        // is it a partial match?
        else if (text.contains(toFind)) {
           System.out.println("Text found as part of " + cellRef.formatAsString());
        }
    }
}
Comments