Get Cell by a Cell reference letter identifier with Apache POI

Question

I am trying to get the Cell from the Row by a Cell reference and I have a problem. For example I have:

row.getCell(CellReference.convertColStringToIndex("B"));

That works fine if the Column index is 1, but if Column was deleted so the B Column index became 2 and the method: CellReference.convertColStringToIndex("B") is still converting it to 1 in which case I can't get my Column, I am getting null.

So the question is how can I get the Column from the Row depending on Cell identifier which is a letter?

score 33 · Accepted Answer · edited May 23 '17 at 11:46

Take a look at my answer to a previous question:

You probably want to use the CellReference utility class to help you out. It offers conversion between Excel style letter+number references, and POI style 0-based rows+columns. When using it, you can do something like:

 Sheet sheet = workbook.getSheet("MyInterestingSheet");

 CellReference ref = new CellReference("B12");
 Row r = sheet.getRow(ref.getRow());
 if (r != null) {
    Cell c = r.getCell(ref.getCol());
 }

That will let you find the cell at a given Excel-style reference (if it's defined, else null)

score -1 · Answer 2 · answered Apr 28 '23 at 13:52

for getting multiple rows/columns:

  CellReference ref1 = new CellReference("A36"); // First LINE 1
  CellReference ref2 = new CellReference("B36"); 
  CellReference ref3 = new CellReference("C36"); 
  CellReference ref4 = new CellReference("D36");

  XSSFRow r1 = sheet.getRow(ref1.getRow()); 
    if (r1 != null) {
            Cell a36 = r1.getCell(ref1.getCol());
            Cell b36 = r1.getCell(ref2.getCol());
            Cell c36 = r1.getCell(ref3.getCol());
            Cell d36 = r1.getCell(ref4.getCol());
            Cell e36 = r1.getCell(ref5.getCol());
                            
            AMT1 = a36.getStringCellValue();
            AMT2 = b36.getStringCellValue();
            AMT3 = c36.getStringCellValue();
            AMT4 = d36.getNumericCellValue();
            AMT5 = e36.getNumericCellValue();
            AMT5A = AMT5.intValue();
           }

where r1 is the first row. This takes the xlsx entry in the first designated row and assigns it to a variable (AMTx ) as shown below. Place the variable designations right after the "public class ........" line of the java file.

  public static String AMT1;//Line 1 
  public static String AMT2;  
  public static String AMT3; 
  public static Double AMT4;  
  public static Double AMT5;  
  public static Integer AMT5A;

Note that Excel xlsx files store numbers as Double within cells -hence the conversion to integer in AMT5A.

aside: stick to java naming conventions, please .. and don't use static scope — kleopatra, Apr 28 '23 at 14:10

score -1 · Answer 3 · answered Apr 28 '23 at 15:13

-1

All: The code requires that the r1 (row 1) be exactly like the actual row number where the cells are located. In this case, it's r36 and not r1. Please change all the r1 to r36. Please excuse this oversight on my part.

answered Apr 28 '23 at 15:13

user1908358

13
5

1

please edit your previous answer to add details (vs. adding a new one) - while doing so, you can also fix the naming violations :) – kleopatra Apr 29 '23 at 12:50

Get Cell by a Cell reference letter identifier with Apache POI

3 Answers3