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?

Answer 1

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)