poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject DO NOT REPLY [Bug 49612] problem in reading Named cells
Date Fri, 23 Jul 2010 10:19:21 GMT
https://issues.apache.org/bugzilla/show_bug.cgi?id=49612

Ranvijay <ranvijayps@gmail.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |NEEDINFO

--- Comment #2 from Ranvijay <ranvijayps@gmail.com> 2010-07-23 06:19:14 EDT ---
(In reply to comment #1)
> It would be helpful for us to diagnose the problem if you attach a workbook and
> sample code that demonstrate the behavior - ideally a failing junit test. 
> 
> Yegor

hi Yegor,

  this is output i have got on console.I am attaching  the Excel file
  in this excel file cell value at location [0,2] ( 0 base indexing) use
formula
  SUM(BOB+JIM) gives right value 30.but at location [0,3] which uses formula 
 
SUM('named-cell-in-formula-test.xls'!BOB+'named-cell-in-formula-test.xls'!JIM)
  is not excuting. as it throws following error

java class code::


package test;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.hssf.record.formula.eval.BoolEval;
import org.apache.poi.hssf.record.formula.eval.NameXEval;
import org.apache.poi.hssf.record.formula.eval.NumberEval;
import org.apache.poi.hssf.record.formula.eval.StringEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.IStabilityClassifier;
import org.apache.poi.ss.formula.eval.forked.ForkedEvaluator;


/**
 * Testing POI's use of Named Cells.
 * 
 * @author bsneade
 */
public class NamedCellTest {

    public static void main(final String[] args) {
        try {
            // load up the spreadsheet            
    String path="C:\\test\\named-cell-in-formula-test.xls";

            final HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(new
File(path)));                
            //HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);    
            HSSFSheet sheet1 = wb.getSheet("sheet1");
            String val2=getCellValue(wb, sheet1, 0, 2);
            System.out.println("[0,2]::"+val2);            
            String val4=getCellValue(wb, sheet1, 0, 3);
            System.out.println("[0,3]::"+val4);

        } catch (IOException e) {
            e.printStackTrace();
        }

    }

public static String getCellValue(HSSFWorkbook wb, HSSFSheet sheet,
            int rowNr, int colNr) {
        String value = null;
        ForkedEvaluator fEval = ForkedEvaluator.create(wb,
                IStabilityClassifier.TOTALLY_IMMUTABLE, null);
        HSSFRow row = sheet.getRow(rowNr);
        if (row != null) {
            HSSFCell cell = row.getCell(colNr);

         if (cell != null) {
                switch (cell.getCellType()) {

        case HSSFCell.CELL_TYPE_STRING:
            value = cell.getRichStringCellValue().getString();
                    break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            value = Double.toString(cell.getNumericCellValue());
                    break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            value = Boolean.toString(cell.getBooleanCellValue());
                    break;
        case HSSFCell.CELL_TYPE_FORMULA:

ValueEval vEval=(ValueEval)fEval.evaluate(sheet.getSheetName(),rowNr,colNr);    
   if (vEval instanceof BoolEval) {
        value = ((BoolEval) vEval).getStringValue();
        } else if (vEval instanceof NumberEval) {
          value = ((NumberEval) vEval).getStringValue();
        } else if (vEval instanceof StringEval) {
          value = ((StringEval) vEval).getStringValue();
                    }                
                    break;                    
                    default:
                }
            }
        }
        return value;
    }

}




[0,2]::30

Exception in thread "main" java.lang.RuntimeException: Unexpected arg eval type
(org.apache.poi.hssf.record.formula.eval.NameXEval)
    at
org.apache.poi.hssf.record.formula.eval.OperandResolver.coerceValueToDouble(OperandResolver.java:218)
    at
org.apache.poi.hssf.record.formula.eval.TwoOperandNumericOperation.singleOperandEvaluate(TwoOperandNumericOperation.java:30)
    at
org.apache.poi.hssf.record.formula.eval.TwoOperandNumericOperation.evaluate(TwoOperandNumericOperation.java:35)
    at
org.apache.poi.hssf.record.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
    at
org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:119)
    at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:437)
    at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:260)
    at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:206)
    at
org.apache.poi.ss.formula.eval.forked.ForkedEvaluator.evaluate(ForkedEvaluator.java:119)
    at test.NamedCellTest.getCellValue(NamedCellTest.java:68)
    at test.NamedCellTest.main(NamedCellTest.java:37)


Thanks
Ranvijay

-- 
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org


Mime
View raw message