You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Josh Micich <jo...@gmail.com> on 2009/12/01 03:56:05 UTC
Re: INDIRECT function
You are probably still running a very old version of POI. In those
versions it was best practice to check the cell type of the
evaluation result (rather than just assume CELL_TYPE_NUMERIC). You
are probably getting CELL_TYPE_ERROR due to INDIRECT not being
implemented in your version of POI. Make a small change to your test
code to see:
----------------
public static void main(String[] args) throws IOException {
HSSFWorkbook wbA = new HSSFWorkbook(new
FileInputStream("c:/josh/temp/Book1.xls"));
wbA.getSheetAt(0).getRow(1).getCell(0).setCellValue("Sheet2");
HSSFFormulaEvaluator evalA = new HSSFFormulaEvaluator(wbA);
CellValue cv = evalA.evaluate(wbA.getSheetAt(0).getRow(3).getCell(0));
if (cv.getCellType() != Cell.CELL_TYPE_NUMERIC) {
System.err.println("Wrong result type - got " + cv.getCellType());
return;
}
System.err.println("Cell A4=" + cv.getNumberValue());
}
--------------
This change is not required if you are running anything newer than
version 3.5-beta4. Since then unimplemented functions cause
NotImplementedException to be thrown
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: INDIRECT function
Posted by Adrian Butnaru <am...@gmail.com>.
Thanks everybody,
During all my tests I had an old poi in the jre directory of the jdk I
am actually using.
I am programming now with Netbeans and I thought my settings there will
be enough, but it looks the old settings in jre were overwriting the new
ones.
Thanks again to everybody!
Hannes Erven schreef:
>> Strange enough this is the output:
>> file:/C:/Program%20Files/Java/jdk1.6.0_03/jre/lib/ext/poi-3.5-beta4-20081128/
>> but this path is not existing!
>>
>
> In any case, it seems you have some older POI version somewhere in your
> classpath. Have you double-checked your classpath settings? How do you
> invoke the program in question?
>
> -hannes
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: INDIRECT function
Posted by Hannes Erven <h....@gmx.at>.
> Strange enough this is the output:
> file:/C:/Program%20Files/Java/jdk1.6.0_03/jre/lib/ext/poi-3.5-beta4-20081128/
> but this path is not existing!
In any case, it seems you have some older POI version somewhere in your
classpath. Have you double-checked your classpath settings? How do you
invoke the program in question?
-hannes
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: INDIRECT function
Posted by Adrian Butnaru <am...@gmail.com>.
Strange enough this is the output:
file:/C:/Program%20Files/Java/jdk1.6.0_03/jre/lib/ext/poi-3.5-beta4-20081128/
but this path is not existing!
Yegor Kozlov schreef:
> Execute the following code:
>
> URL url =
> HSSFWorkbook.class.getProtectionDomain().getCodeSource().getLocation();
> System.out.println(url);
>
> it will print the path to the POI jar.
>
> nightly builds can be downloaded from
> http://encore.torchbox.com/poi-svn-build/
>
> Yegor
>
>> Your prediction is good.
>>
>> This was the output:
>> Wrong result type - got 5errCode=-30
>> But how can I get rhe newest poi jar file?
>> I downloaded the one I am using
>>
>> POI-3.5-FINAL-20090928.jar
>>
>> from POI website.
>>
>>
>>
>>
>> Josh Micich schreef:
>>>> I am using POI-3.5-FINAL-20090928.jar.
>>>>
>>> There's a high chance you're not.
>>>
>>> You can also dump the error code to help diagnose the problem.
>>> Change this line:
>>> System.err.println("Wrong result type - got " + cv.getCellType() + "
>>> errCode=" + cv.getErrorValue());
>>>
>>> I predict the error code will be -30. Which will mean you are using a
>>> version of POI prior to 3.5-beta4
>>>
>>> If I am mistaken, another thing you can try is to put a breakpoint in
>>> Indirect.java:76, to confirm whether execution gets there. You can
>>> also look around the variables in the stack frame which might help
>>> explain what is going wrong.
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: INDIRECT function
Posted by Yegor Kozlov <ye...@dinom.ru>.
Execute the following code:
URL url = HSSFWorkbook.class.getProtectionDomain().getCodeSource().getLocation();
System.out.println(url);
it will print the path to the POI jar.
nightly builds can be downloaded from http://encore.torchbox.com/poi-svn-build/
Yegor
> Your prediction is good.
>
> This was the output:
> Wrong result type - got 5errCode=-30
> But how can I get rhe newest poi jar file?
> I downloaded the one I am using
>
> POI-3.5-FINAL-20090928.jar
>
> from POI website.
>
>
>
>
> Josh Micich schreef:
>>> I am using POI-3.5-FINAL-20090928.jar.
>>>
>> There's a high chance you're not.
>>
>> You can also dump the error code to help diagnose the problem.
>> Change this line:
>> System.err.println("Wrong result type - got " + cv.getCellType() + "
>> errCode=" + cv.getErrorValue());
>>
>> I predict the error code will be -30. Which will mean you are using a
>> version of POI prior to 3.5-beta4
>>
>> If I am mistaken, another thing you can try is to put a breakpoint in
>> Indirect.java:76, to confirm whether execution gets there. You can
>> also look around the variables in the stack frame which might help
>> explain what is going wrong.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: INDIRECT function
Posted by Adrian Butnaru <am...@gmail.com>.
Your prediction is good.
This was the output:
Wrong result type - got 5errCode=-30
But how can I get rhe newest poi jar file?
I downloaded the one I am using
POI-3.5-FINAL-20090928.jar
from POI website.
Josh Micich schreef:
>> I am using POI-3.5-FINAL-20090928.jar.
>>
> There's a high chance you're not.
>
> You can also dump the error code to help diagnose the problem.
> Change this line:
> System.err.println("Wrong result type - got " + cv.getCellType() + "
> errCode=" + cv.getErrorValue());
>
> I predict the error code will be -30. Which will mean you are using a
> version of POI prior to 3.5-beta4
>
> If I am mistaken, another thing you can try is to put a breakpoint in
> Indirect.java:76, to confirm whether execution gets there. You can
> also look around the variables in the stack frame which might help
> explain what is going wrong.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: INDIRECT function
Posted by Josh Micich <jo...@gmail.com>.
> I am using POI-3.5-FINAL-20090928.jar.
There's a high chance you're not.
You can also dump the error code to help diagnose the problem.
Change this line:
System.err.println("Wrong result type - got " + cv.getCellType() + "
errCode=" + cv.getErrorValue());
I predict the error code will be -30. Which will mean you are using a
version of POI prior to 3.5-beta4
If I am mistaken, another thing you can try is to put a breakpoint in
Indirect.java:76, to confirm whether execution gets there. You can
also look around the variables in the stack frame which might help
explain what is going wrong.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: INDIRECT function
Posted by Adrian Butnaru <am...@gmail.com>.
Hi,
I am using POI-3.5-FINAL-20090928.jar.
I changed my code as follows:
public static void main(String[] args) throws IOException {
HSSFWorkbook wbAA = new HSSFWorkbook(new
FileInputStream("D:/Book1.xls"));
wbAA.getSheetAt(0).getRow(1).getCell(0).setCellValue("Sheet2");
HSSFFormulaEvaluator evalAA = new HSSFFormulaEvaluator(wbAA);
CellValue cv =
evalAA.evaluate(wbAA.getSheetAt(0).getRow(3).getCell(0));
evalAA.clearAllCachedResultValues();
if (cv.getCellType() != Cell.CELL_TYPE_NUMERIC) {
System.err.println("Wrong result type - got " + cv.getCellType());
return;
}
System.err.println("Cell A4=" +cv.getNumberValue());
}
and I get:
Wrong result type - got 5
This means formula error. So INDIRECT is not evaluated corectly. Why?
I really need to make this working. What should I do?
Thanks,
Adrian
Josh Micich schreef:
> You are probably still running a very old version of POI. In those
> versions it was best practice to check the cell type of the
> evaluation result (rather than just assume CELL_TYPE_NUMERIC). You
> are probably getting CELL_TYPE_ERROR due to INDIRECT not being
> implemented in your version of POI. Make a small change to your test
> code to see:
>
> ----------------
> public static void main(String[] args) throws IOException {
> HSSFWorkbook wbA = new HSSFWorkbook(new
> FileInputStream("c:/josh/temp/Book1.xls"));
> wbA.getSheetAt(0).getRow(1).getCell(0).setCellValue("Sheet2");
> HSSFFormulaEvaluator evalA = new HSSFFormulaEvaluator(wbA);
> CellValue cv = evalA.evaluate(wbA.getSheetAt(0).getRow(3).getCell(0));
> if (cv.getCellType() != Cell.CELL_TYPE_NUMERIC) {
> System.err.println("Wrong result type - got " + cv.getCellType());
> return;
> }
> System.err.println("Cell A4=" + cv.getNumberValue());
> }
> --------------
>
> This change is not required if you are running anything newer than
> version 3.5-beta4. Since then unimplemented functions cause
> NotImplementedException to be thrown
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org