3

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

KishanCS
  • 1,357
  • 1
  • 19
  • 38
  • 3
    have you tried anything? is this a question or a statement? – XtremeBaumer Dec 01 '16 at 12:46
  • cell = objSheet.getRow(34).getCell(7); this way i can set content ..But i dont know the exact position to retrieve because text places are not fixed – KishanCS Dec 01 '16 at 12:50
  • Text location changes .I need to find and replace as done in windows using CTRL+F and find text and replace it – KishanCS Dec 01 '16 at 12:51
  • 2
    Loop over all the rows and cells with http://poi.apache.org/spreadsheet/quick-guide.html#Iterator then check for the cell contents with http://poi.apache.org/spreadsheet/quick-guide.html#CellContents ? – Gagravarr Dec 01 '16 at 12:51

1 Answers1

7

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());
        }
    }
}
Gagravarr
  • 47,320
  • 10
  • 111
  • 156
  • XSSFWorkbook objWorkbook = new XSSFWorkbook(objFileInputStream); XSSFSheet objSheet = objWorkbook.getSheetAt(0); XSSFCellStyle style = objWorkbook.createCellStyle(); DataFormatter formatter1 = new DataFormatter(); working fine bro!! – KishanCS Dec 01 '16 at 13:10
  • tried on XSSF with same code... – KishanCS Dec 01 '16 at 13:10