Get name of cell apache poi

Question

I have a Cell object, how can I get the name of that cell?

Would like a function such as:

String name = myCell.getName();

In Excel I have named it in the name box, so I don't want to get 'B4', I would like to get the name such as "InterestRate".

Can't find such a method, can I achieve it in some other way?

Babettebabeuf

Do you mean you want to fetch the named range that a cell belongs to, if any? Or something else? – Jagannath

@Jagannath yes, exactly, if named range is the same as a named cell (named range with one element?) – Babettebabeuf

Answer 1

To find the named range which is defined to exactly match one cell, you'd want something like:

// Get the cell we want to find - A1 for this case
Workbook wb = WorkbookFactory.create("input.xlsx");
int sheetIndex = 0;
Sheet s = wb.getSheetAt(sheetIndex);
Cell wanted = s.getRow(0).getCell(0);
String wantedRef = (new CellReference(wanted)).formatAsString();

// Check all the named range
for (int nn=0; nn<wb.getNumberOfNames(); nn++) {
   Name n = wb.getNameAt(nn);
   if (n.getSheetIndex() == -1 || n.getSheetIndex() == sheetIndex) {
      if (n.getRefersToFormula().equals(wantedRef)) {
         // Found it!
         return name.getNameName();
      }
   }
}

Note that this will return the first named range that applies to a cell, if there's more than one and you wanted them all, you'd need to tweak that code to keep going and return a list

Jagannath

Very helpful explanation even after so may years. With POI version 5.x to get all the defined custom names in the sheet List<XSSFName> nameList = workbook.getAllNames(); for ( XSSFName name : nameList) String name = name.getNameName() ; – Ulrich

Answer 2

The required method 'getAddress()' appeared in version 3.14beta1. To get a string reference you can try this:

myCell.getAddress().toString()

Agreed

I'm not sure this is what the user wants, as according to their question, they do not want A1 style addressing, they wanted a named cell. – Front