CODEWITHSUNDEEP
javaapache-poiexcellibrary
user1487380
user1487380

Reputation: 362

How to get excell range name for HSSFCell apache POI API?

As title, I have a little trouble here. I can get font from cell

HSSFFont font =
    cell.getRow().getSheet().getWorkbook().
    getFontAt(cell.getCellStyle().getFontIndex());

But now I need to get range name of it. Actually I need something to anchor and determine key cell and its own value cell.

Is there some method to get range name such as workBook.getName() or .getNameAt() but how to get name index from HSSFCell?

Upvotes: 1

Views: 4908

Answers (1)

kiwiwings
kiwiwings

Reputation: 3446

Apart from rich text strings, a cell has only one font assigned, but it may be referenced by more than one named range. So you'll need to iterate through the named ranges of the workbook and check if the cell is referenced. For the sake of simplicity, I have iterated over all area.getAllReferencedCells() - in case of big ranges you'll need to check if the area isContiguous() and if your cell/row-index is inside the cell/row-index of getFirstCell() and getLastCell() bounding box.

For more info check the Busy Developers' Guide to HSSF and XSSF Features.

Or search on stackoverflow ...

(in my testcase, a cell(row 4, col 3) was referenced by three different shaped named ranges)

import java.io.File;
import java.util.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;

public class XlsRangeNames {
    public static void main(String[] args) throws Exception {
        Workbook wb = WorkbookFactory.create(new File("src/test/resources/name-range.xls"));

        Cell cell = wb.getSheetAt(0).getRow(3).getCell(2);

        for (Name n : getNamedRangesForCell(wb, cell)) {
            System.out.println(n.getNameName());
        }
    }

    static List<Name> getNamedRangesForCell(Workbook wb, Cell cell) {
        int col = cell.getColumnIndex();
        int row = cell.getRowIndex();
        String sheetName = cell.getSheet().getSheetName();
        List<Name> result = new ArrayList<Name>();

        for (int i=0; i<wb.getNumberOfNames(); i++) {
            Name name = wb.getNameAt(i);
            if (!sheetName.equals(name.getSheetName())) continue;

            AreaReference area = new AreaReference(name.getRefersToFormula());
            CellReference crList[] = area.getAllReferencedCells();
            for (CellReference cr : crList) {
                if (cr.getCol() == col
                    && cr.getRow() == row) {
                    result.add(name);
                    continue;
                }
            }
        }

        return result;
    }
}

Upvotes: 2

Related Questions