Geek asks and answers

Get cell index from cell value, Apache POI

Environment    Status    Version    PatchNumber
Windows        Live      1.0        2
Unix           Live      2.0        4
Mac            Live      1.3        8

If I have the above data in excel, how can I access the PatchNumber cell number using text

XSSFRow row = (XSSFRow) rows.next();
I would like to access row.getCellNumber("PatchNumber");// Note. This method does not exist in Apache POI.

+5
source share
3 answers

I think I understand what you need - do you want to know which column contains the word "Patch" in it in the first row? If so, all you have to do is something like:

Sheet s = wb.getSheetAt(0);
Row r = s.getRow(0);

int patchColumn = -1;
for (int cn=0; cn<r.getLastCellNum(); cn++) {
   Cell c = r.getCell(cn);
   if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK) {
       // Can't be this cell - it empty
       continue;
   }
   if (c.getCellType() == Cell.CELL_TYPE_STRING) {
      String text = c.getStringCellValue();
      if ("Patch".equals(text)) {
         patchColumn = cn;
         break;
      }
   }
}
if (patchColumn == -1) {
   throw new Exception("None of the cells in the first row were Patch");
}

Just loop the cells in the first (header) row, check their value and mark the column you are in when you find the text!

+5
source

Isn't that what you want?

row.getCell(3)

public XSSFCell getCell (int cellnum) (0 ) , Row.MissingCellPolicy .

- "PatchNumber", PatchNumber. , , . , : -)

. columns, columns.indexOf("PatchNumber")

http://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFRow.html#getCell(int)

+2
public CellAddress searchStringInXslx(String string) throws IOException{
            FileInputStream inputStream = new FileInputStream("Books.xlsx");
            Workbook workbook = new XSSFWorkbook(inputStream);
            Sheet firstSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = firstSheet.iterator();
            CellAddress columnNumber=null;

            while(iterator.hasNext()){
                 Row nextRow = iterator.next();
                 Iterator<Cell> cellIterator = nextRow.cellIterator();
                 while (cellIterator.hasNext()) {
                     Cell cell = cellIterator.next();
                     if(cell.getCellType()==Cell.CELL_TYPE_STRING){ 
                         String text = cell.getStringCellValue();
                          if (string.equals(text)) {
                             columnNumber=cell.getAddress();
                             break;
                          }
                        }
                     }
            }
            workbook.close();
            return columnNumber;
     }
0
source

More articles:


All Articles