Bug 35716 - Named Range creation fails for range over single cell
Summary: Named Range creation fails for range over single cell
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-dev
Hardware: Other other
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2005-07-13 15:02 UTC by Amol Deshmukh
Modified: 2005-07-13 07:21 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Amol Deshmukh 2005-07-13 15:02:50 UTC
Named range support has been implemented using the usermodel class HSSFName.
However, currently when a named range is attempted to be created over a single
the behaviour is erroneous.

Two ways to create a named range over a single cel were attempted:

1. using the string "A1"
Calling HSSFName.setReference("Sheet1!A1");
throws the following exception:

   Failed with exception:
java.lang.ArrayIndexOutOfBoundsException: 1
	at org.apache.poi.hssf.record.formula.Area3DPtg.setArea(Area3DPtg.java:247)
	at org.apache.poi.hssf.record.NameRecord.setAreaReference(NameRecord.java:713)
	at org.apache.poi.hssf.usermodel.HSSFName.setReference(HSSFName.java:125)
	at <someLineInUserCode/>

2. using the string "A1:A1"
This code does not throw RuntimeException, but it retrieves the cell A1 twice
when the following code is used to retrieve the cell references:
    int namedCellIdx = wb.getNameIndex(cellName);
    HSSFName aNamedCell = wb.getNameAt(namedCellIdx);
    AreaReference aref = new AreaReference(aNamedCell.getReference());
    CellReference[] crefs = aref.getCells();
    for (int i=0, iSize=crefs.length; i<iSize; i++) {
        CellReference cref = crefs[i];
        HSSFSheet s = wb.getSheet(cref.getSheetName());
        HSSFRow r = sheet.getRow(cref.getRow());
        HSSFCell c = r.getCell(cref.getCol());
        String contents = c.getStringCellValue();

Expected resolution:
There should be atleast one way to create a named range over a single cell :)
Comment 1 Avik Sengupta 2005-07-13 15:05:30 UTC
When creating a range as A1:A1, does excel behave as expected? If so, then I
reckon all we need to fix is the edge case for AreaReference. 
Comment 2 Amol Deshmukh 2005-07-13 15:21:49 UTC
Actually, the code for the edge case of AreaReference was already in place in
the class org.apache.poi.hssf.record.NameRecord

However, the condition used in org.apache.poi.hssf.util.RangeAddress#hasRange()
was incorrect, which is now fixed.
Testcases that failed prior to the fix and passed after the fix have also been
added. Also, updated the quick-guide to include info on creating Named Ranges
(since the question popped a couple of times on the mailing list)