0

How to get height and width of excel sheet named range area from HSSFWorkbook POI API.

I got one reference from google but not able to get height and width of named index area of excel sheet.

Thanks in advance.

Yes it's named range, and want height and width of cells aquired by named range. below code having "print_area" named range and want to height and width of cells.

int namedCellIdx = workbook.getNameIndex("Print_Area"); HSSFName aNamedCell = workbook.getNameAt(namedCellIdx);

    // retrieve the cell at the named range and test its contents
    String a = aNamedCell.getReference();

    AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula());
    CellReference[] crefs = aref.getAllReferencedCells();
    for (int i=0; i<crefs.length; i++) {
        Sheet s = workbook.getSheet(crefs[i].getSheetName());
        Row r = sheet.getRow(crefs[i].getRow());
        System.out.println(r.getHeight());

        System.out.println(sheet.getColumnWidth(crefs[i].getCol()));;

       //here want height and width of "Print_area" cells
        Cell c = r.getCell(crefs[i].getCol());
        System.out.println(c.getStringCellValue());


        // extract the cell contents based on cell type etc.
    }
Jim Garrison
  • 85,615
  • 20
  • 155
  • 190
Vinayak
  • 155
  • 2
  • 2
  • 9
  • Do you mean "named range" instead of "named index"? Do you want the "height and width" in terms of cells? Pixels? If it's in terms of cells, what part of the API you linked is unclear? If it's pixels, forget it because that requires the display context, and when you're reading a sheet in POI there is no display context. – Jim Garrison Mar 07 '13 at 05:28
  • Yes it's named range, and want height and width of cells aquired by named range. – Vinayak Mar 07 '13 at 06:02

1 Answers1

1

Read the API docs at http://poi.apache.org/apidocs/index.html

Specifically, look at org.apache.poi.ss.util.AreaReference, from which you can get the first and last cell references and determine the size of the range.

However, you have to cope with a number of special cases, such as areas that are an entire column or entire row, or even non-contiguous.

Jim Garrison
  • 85,615
  • 20
  • 155
  • 190
  • We are new to this API, as discussed need named range height and width in MiliMeter from first and last cell. CellReference fcell =aref.getFirstCell(); CellReference lcell =aref.getLastCell(); – Vinayak Mar 07 '13 at 06:48
  • If you want the ***number of cells*** in the range, you can get that from the API. If you want the ***display size in millimeters*** as your comment implies, that is impossible. – Jim Garrison Mar 07 '13 at 06:51
  • Thanks for your quick reply,Is that possible to get height and width(in any thing) by passing first cell and last cell.? Or by passing refferances? pls help to get this urgent. – Vinayak Mar 07 '13 at 06:58
  • Let's say you have a named range `A1:B4`. This range contains 2 rows and 4 columns, for a total of 8 cells. If the answer you want is `8` or maybe `4,2`, you can get this from the API. If the answer you want is "32mm wide by 40mm tall" then that is impossible. Please clarify which answer you are looking for. – Jim Garrison Mar 07 '13 at 07:01
  • Its may be 4,2, but it's cell count number then what is returns during rowWidth or rowHeight – Vinayak Mar 07 '13 at 07:06