Results 1 to 1 of 1
  1. #1
    pri515 is offline Member
    Join Date
    Jul 2014
    Rep Power

    Default Return a range of cells with Apache POI Java API, Excel and Java

    I have 3 named ranges on excel, say,

    Name COUNT_FAM Refers To "COUNTA(Sheet1!$X$X)"
    Name FAMILIES Refers To "INDIRECT(Sheet1!I2:I"&COUNT_FAM")"
    Name FLEX_SOM Refers To "OFFSET(Sheet1!$Y$2, 0, 0, COUNT_FAM-1, 3)".
    Now, I need a function that returns to me the range of cells (like A1:A4) or just display the cell(s) if I pass the workbook and the Refers To string as parameters. How could this be coded in Java using Apache POI Java API?

    I tried to write a method that takes the file location and name of the range as inputs instead of workbook and refers to. Here is what I have coded:

    public static void formulafn(String fileName, String rangeName) throws FileNotFoundException, IOException {

    FileInputStream file = new FileInputStream(new File(fileName));
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFName[] names = new XSSFName[workbook.getNumberOfNames()];
    for (int i = 0; i < names.length; i++) {
    names[i] = workbook.getNameAt(i);

    for (int i = 0; i < names.length; i++) {
    if (names[i].getNameName().matches(rangeName)) {
    XSSFEvaluationWorkbook hssfew = XSSFEvaluationWorkbook.create(workbook);
    Ptg[] ptg = FormulaParser.parse(names[i].getRefersToFormula(), hssfew, FormulaType.NAMEDRANGE, 0);
    for(int y=0;y<ptg.length;y++){
    Ptg tempptg = ptg[ptg.length-1];
    if(tempptg instanceof FuncVarPtg){
    FuncVarPtg ref = (FuncVarPtg) tempptg;
    I don't know how to proceed after this.

    Last edited by pri515; 07-17-2014 at 11:25 AM.

Similar Threads

  1. Replies: 0
    Last Post: 11-01-2013, 08:17 AM
  2. reading Excel repeated cells with Apache Poi
    By kaucuk in forum Apache POI
    Replies: 1
    Last Post: 07-15-2013, 11:14 PM
  3. Replies: 1
    Last Post: 05-29-2013, 10:36 AM
  4. Editing specific (existing) excel cells
    By bolbi123456 in forum Advanced Java
    Replies: 17
    Last Post: 05-19-2012, 04:13 AM
  5. Excel: How to sort a range?
    By Chris_X in forum Java Servlet
    Replies: 9
    Last Post: 11-22-2010, 02:24 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts