Bug 35716

Summary: Named Range creation fails for range over single cell
Product: POI Reporter: Amol Deshmukh <amolweb>
Component: HSSFAssignee: POI Developers List <dev>
Severity: normal    
Priority: P2    
Version: 3.0-dev   
Target Milestone: ---   
Hardware: Other   
OS: other   

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)