You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Hardie82 <th...@yahoo.de> on 2008/09/23 08:44:09 UTC
IndexBound-Exception by FormulaCell-Evaluation
Hi. I have a problem by evaluate a formula cell. I want to iterate over all
cell in a spreedsheet and wrote following code:
for(Iterator<HSSFRow> rit = (Iterator<HSSFRow>)sheet.rowIterator();
rit.hasNext(); ) {
row = rit.next();
evaluator.setCurrentRow(row);
for(Iterator<HSSFCell> cit = (Iterator<HSSFCell>)row.cellIterator();
cit.hasNext(); ) {
cell = cit.next();
if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
cellValue = evaluator.evaluate(cell);
switch (cellValue.getCellType()) {
case HSSFCell.CELL_TYPE_ERROR:
switch (cellValue.getErrorValue()) {
case ErrorConstants.ERROR_DIV_0:
errors.add("cell.value.faulty.zerodivision"); break;
case ErrorConstants.ERROR_NAME:
errors.add("cell.value.faulty.name"); break;
case ErrorConstants.ERROR_VALUE:
errors.add("cells.value.faulty.value"); break;
default:
errors.add("cell.value.faulty");
}
}
if(!cell.getCellStyle().getLocked())
errors.add("protection.disabled.formulacell");
if(errors.size() > 0)
result.addError((new CellReference(row.getRowNum(),
cell.getCellNum())).formatAsString().replace("$", ""),
errors.toArray(new String[errors.size()]));
errors.clear();
}
}
}
i tested the code with some files, but everytime i get an
IndexOutOfBoundException at codeline "cellValue =
evaluator.evaluate(cell);". The stacktrace contains following message:
Exception in thread "main" java.lang.IndexOutOfBoundsException: Index: 5,
Size: 5
at java.util.ArrayList.RangeCheck(Unknown Source)
at java.util.ArrayList.get(Unknown Source)
at org.apache.poi.hssf.model.Workbook.getSheetName(Workbook.java:534)
at
org.apache.poi.hssf.model.Workbook.findSheetNameFromExternSheet(Workbook.java:1867)
at
org.apache.poi.hssf.model.Workbook.getSheetReferences(Workbook.java:1849)
at
org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetReferences(HSSFWorkbook.java:788)
at
org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetReferences(HSSFWorkbook.java:79)
at
org.apache.poi.hssf.record.formula.Ref3DPtg.getSheetName(Ref3DPtg.java:169)
at
org.apache.poi.hssf.record.formula.Ref3DPtg.toFormulaString(Ref3DPtg.java:182)
at
org.apache.poi.hssf.model.FormulaParser.toFormulaString(FormulaParser.java:925)
at
org.apache.poi.hssf.model.FormulaParser.toFormulaString(FormulaParser.java:858)
at org.apache.poi.hssf.usermodel.HSSFCell.getCellFormula(HSSFCell.java:686)
at
org.apache.poi.ss.usermodel.FormulaEvaluator.internalEvaluate(FormulaEvaluator.java:326)
at
org.apache.poi.ss.usermodel.FormulaEvaluator.evaluate(FormulaEvaluator.java:133)
at org.saxsys.ecpoi.ExChecker.checkFormulaCells(ExChecker.java:111)
at org.saxsys.ecpoi.ExChecker.checkSheet(ExChecker.java:73)
at org.saxsys.ecpoi.ExChecker.startCheck(ExChecker.java:55)
at org.saxsys.ecpoi.Main.<init>(Main.java:69)
at org.saxsys.ecpoi.Main.main(Main.java:21)
i have no idea whats wrong. have somebody a solution for this problem?
--
View this message in context: http://www.nabble.com/IndexBound-Exception-by-FormulaCell-Evaluation-tp19622168p19622168.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Pierre Lavignotte <pi...@gmail.com>.
Yes, the stack says that you try to access a sheet with a wrong index,
but if the formula is TODAY(), there is no reason for that.
On Tue, Sep 23, 2008 at 11:46 AM, Nick Burch <ni...@torchbox.com> wrote:
> On Tue, 23 Sep 2008, Hardie82 wrote:
>>
>> Exception in thread "main" java.lang.IndexOutOfBoundsException: Index: 5,
>> Size: 5
>> at java.util.ArrayList.RangeCheck(Unknown Source)
>> at java.util.ArrayList.get(Unknown Source)
>> at
>> org.apache.poi.hssf.model.Workbook.getSheetName(Workbook.java:534)
>> at
>>
>> org.apache.poi.hssf.model.Workbook.findSheetNameFromExternSheet(Workbook.java:1867)
>> at
>> org.apache.poi.hssf.model.Workbook.getSheetReferences(Workbook.java:1849)
>> at
>>
>> org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetReferences(HSSFWorkbook.java:788)
>> at
>>
>> org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetReferences(HSSFWorkbook.java:79)
>> at
>>
>> org.apache.poi.hssf.record.formula.Ref3DPtg.getSheetName(Ref3DPtg.java:169)
>
> This looks to me like you have a formula which references the 6th sheet in
> your workbook. As you only have 5 sheets, poi breaks when it attempts to get
> the name of the sheet which doesn't exist
>
> My guess is either you have a duff formula which is breaking poi, or there's
> a poi bug in turning sheet referencing formula ptgs back into text. Do you
> want to check the formulas in excel for the former, and if that looks fine
> then upload the file so we can look into the latter?
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Nick Burch <ni...@torchbox.com>.
On Tue, 23 Sep 2008, Hardie82 wrote:
> Exception in thread "main" java.lang.IndexOutOfBoundsException: Index: 5,
> Size: 5
> at java.util.ArrayList.RangeCheck(Unknown Source)
> at java.util.ArrayList.get(Unknown Source)
> at org.apache.poi.hssf.model.Workbook.getSheetName(Workbook.java:534)
> at
> org.apache.poi.hssf.model.Workbook.findSheetNameFromExternSheet(Workbook.java:1867)
> at
> org.apache.poi.hssf.model.Workbook.getSheetReferences(Workbook.java:1849)
> at
> org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetReferences(HSSFWorkbook.java:788)
> at
> org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetReferences(HSSFWorkbook.java:79)
> at
> org.apache.poi.hssf.record.formula.Ref3DPtg.getSheetName(Ref3DPtg.java:169)
This looks to me like you have a formula which references the 6th sheet in
your workbook. As you only have 5 sheets, poi breaks when it attempts to
get the name of the sheet which doesn't exist
My guess is either you have a duff formula which is breaking poi, or
there's a poi bug in turning sheet referencing formula ptgs back into
text. Do you want to check the formulas in excel for the former, and
if that looks fine then upload the file so we can look into the latter?
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Pierre Lavignotte <pi...@gmail.com>.
Josh has allready fixed the issue :
https://issues.apache.org/bugzilla/show_bug.cgi?id=45865
I guess it's available from SVN ?
So thanks to him,
Pierre
On Tue, Sep 23, 2008 at 3:14 PM, Hardie82 <th...@yahoo.de>wrote:
>
> Hi Pierre,
>
> iam not able to avoid externel file references. So i have do go the second
> way with try-catch, till the bug is fixed. I thank you a lot for your help.
>
> Hardie
>
>
> Pierre Lavignotte wrote:
> >
> > Nick,
> >
> > I've created a new bug in Bugzilla with a sample attached.
> > Please, check that I didn't make any mistake since it's my first time...
> > https://issues.apache.org/bugzilla/show_bug.cgi?id=45865
> >
> > Hardie,
> >
> > Such a formula just doesn't parse so you can't check it before.
> > The best solution is to avoid external file references since it can
> > make POI break or GIVE YOU WRONG VALUES.
> > Another is to try {} catch {} the exception and just go on...
> >
> > Pierre
> >
>
> --
> View this message in context:
> http://www.nabble.com/IndexBound-Exception-by-FormulaCell-Evaluation-tp19622168p19627396.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Hardie82 <th...@yahoo.de>.
Hi Pierre,
iam not able to avoid externel file references. So i have do go the second
way with try-catch, till the bug is fixed. I thank you a lot for your help.
Hardie
Pierre Lavignotte wrote:
>
> Nick,
>
> I've created a new bug in Bugzilla with a sample attached.
> Please, check that I didn't make any mistake since it's my first time...
> https://issues.apache.org/bugzilla/show_bug.cgi?id=45865
>
> Hardie,
>
> Such a formula just doesn't parse so you can't check it before.
> The best solution is to avoid external file references since it can
> make POI break or GIVE YOU WRONG VALUES.
> Another is to try {} catch {} the exception and just go on...
>
> Pierre
>
--
View this message in context: http://www.nabble.com/IndexBound-Exception-by-FormulaCell-Evaluation-tp19622168p19627396.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Pierre Lavignotte <pi...@gmail.com>.
Nick,
I've created a new bug in Bugzilla with a sample attached.
Please, check that I didn't make any mistake since it's my first time...
https://issues.apache.org/bugzilla/show_bug.cgi?id=45865
Hardie,
Such a formula just doesn't parse so you can't check it before.
The best solution is to avoid external file references since it can
make POI break or GIVE YOU WRONG VALUES.
Another is to try {} catch {} the exception and just go on...
Pierre
On Tue, Sep 23, 2008 at 1:22 PM, Hardie82 <th...@yahoo.de> wrote:
>
> I don't know how many sheets the external file has because i didn't get the
> file :(. But how can i save the problem? Could i check wether the formula is
> a reference or not? In that case, i could ignore the evaluate and get the
> next formula cell. I only want to get the message if it contains an error or
> not.
>
>
> Pierre Lavignotte wrote:
>>
>> Now I can reproduce...
>>
>> POI doesn't handle the external file reference and just evaluate the
>> 'examplesheetname!N12' formula as if it were in the same workbook.
>> If your external sheet has an index greater than the number of sheet
>> in the main workbook, it breaks.
>>
>
> --
> View this message in context: http://www.nabble.com/IndexBound-Exception-by-FormulaCell-Evaluation-tp19622168p19625647.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Hardie82 <th...@yahoo.de>.
I don't know how many sheets the external file has because i didn't get the
file :(. But how can i save the problem? Could i check wether the formula is
a reference or not? In that case, i could ignore the evaluate and get the
next formula cell. I only want to get the message if it contains an error or
not.
Pierre Lavignotte wrote:
>
> Now I can reproduce...
>
> POI doesn't handle the external file reference and just evaluate the
> 'examplesheetname!N12' formula as if it were in the same workbook.
> If your external sheet has an index greater than the number of sheet
> in the main workbook, it breaks.
>
--
View this message in context: http://www.nabble.com/IndexBound-Exception-by-FormulaCell-Evaluation-tp19622168p19625647.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Nick Burch <ni...@torchbox.com>.
On Tue, 23 Sep 2008, Pierre Lavignotte wrote:
> POI doesn't handle the external file reference and just evaluate the
> 'examplesheetname!N12' formula as if it were in the same workbook. If
> your external sheet has an index greater than the number of sheet in the
> main workbook, it breaks.
Are you able to upload a file that shows this to bugzilla, since the OP
isn't able to upload theirs? Hopefully Josh'll then be able to work some
of his formula parsing magic :)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Pierre Lavignotte <pi...@gmail.com>.
Now I can reproduce...
POI doesn't handle the external file reference and just evaluate the
'examplesheetname!N12' formula as if it were in the same workbook.
If your external sheet has an index greater than the number of sheet
in the main workbook, it breaks.
On Tue, Sep 23, 2008 at 12:10 PM, Hardie82 <th...@yahoo.de> wrote:
>
> It is confidential so i can't share it :(. But i tried following:
>
> if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
> System.out.println("Cell: " + (new CellReference(row.getRowNum(),
> cell.getCellNum())).formatAsString().replace("$", ""));
> Ptg[] ptgArray = FormulaParser.parse(cell.getCellFormula(),
> this.wbook);
> System.out.println("Array-Size: " + ptgArray.length);
> for(Ptg ptg : ptgArray)
> System.out.println("Ptg" + ptg.toString() + " | Size: " +
> ptg.getSize());
> cellValue = evaluator.evaluate(cell);
> [.....]
> logger.debug("ending cellcheck " + (new CellReference(row.getRowNum(),
> cell.getCellNum())).formatAsString().replace("$", ""));
>
> and get the following stacktrace:
>
> 11:54:42,964 DEBUG ExChecker:87 - start checkFormulaCells
> Cell: B3
> Array-Size: 1
> Ptgorg.apache.poi.hssf.record.formula.FuncPtg [TODAY nArgs=0] | Size: 3
> 11:54:42,964 DEBUG ExChecker:139 - ending cellcheck B3
> Cell: H5
> Exception in thread "main" java.lang.IndexOutOfBoundsException: Index: 5,
> Size: 5
>
> as you see the problem isn't cell B3 with the TODAY-function, it's cell H5
> and the content is a refernce to another sheet in the workbook
> "=examplesheetname!N12". In this cell there is again a reference to a
> excelfile "='<filepath>\[xyz.xls]examplesheetname'!N12". But this file
> doesn't exist. Could this be the reason for the exception? By the way the
> brackets after <filepath> are also in the reference.
>
>
> Pierre Lavignotte wrote:
>>
>> I get the same output, except the error.
>>
>> Can you share a the Workbook or is it confidential ?
>>
>
> --
> View this message in context: http://www.nabble.com/IndexBound-Exception-by-FormulaCell-Evaluation-tp19622168p19624725.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Hardie82 <th...@yahoo.de>.
It is confidential so i can't share it :(. But i tried following:
if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
System.out.println("Cell: " + (new CellReference(row.getRowNum(),
cell.getCellNum())).formatAsString().replace("$", ""));
Ptg[] ptgArray = FormulaParser.parse(cell.getCellFormula(),
this.wbook);
System.out.println("Array-Size: " + ptgArray.length);
for(Ptg ptg : ptgArray)
System.out.println("Ptg" + ptg.toString() + " | Size: " +
ptg.getSize());
cellValue = evaluator.evaluate(cell);
[.....]
logger.debug("ending cellcheck " + (new CellReference(row.getRowNum(),
cell.getCellNum())).formatAsString().replace("$", ""));
and get the following stacktrace:
11:54:42,964 DEBUG ExChecker:87 - start checkFormulaCells
Cell: B3
Array-Size: 1
Ptgorg.apache.poi.hssf.record.formula.FuncPtg [TODAY nArgs=0] | Size: 3
11:54:42,964 DEBUG ExChecker:139 - ending cellcheck B3
Cell: H5
Exception in thread "main" java.lang.IndexOutOfBoundsException: Index: 5,
Size: 5
as you see the problem isn't cell B3 with the TODAY-function, it's cell H5
and the content is a refernce to another sheet in the workbook
"=examplesheetname!N12". In this cell there is again a reference to a
excelfile "='<filepath>\[xyz.xls]examplesheetname'!N12". But this file
doesn't exist. Could this be the reason for the exception? By the way the
brackets after <filepath> are also in the reference.
Pierre Lavignotte wrote:
>
> I get the same output, except the error.
>
> Can you share a the Workbook or is it confidential ?
>
--
View this message in context: http://www.nabble.com/IndexBound-Exception-by-FormulaCell-Evaluation-tp19622168p19624725.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Pierre Lavignotte <pi...@gmail.com>.
I get the same output, except the error.
Can you share a the Workbook or is it confidential ?
On Tue, Sep 23, 2008 at 11:28 AM, Hardie82 <th...@yahoo.de> wrote:
>
> I insert following code:
>
> ....
> if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
> Ptg[] ptgArray = FormulaParser.parse(cell.getCellFormula(),
> this.wbook);
> System.out.println("Array-Size: " + ptgArray.length);
> for(Ptg ptg : ptgArray)
> System.out.println("Ptg" + ptg.toString() + " | Size: " +
> ptg.getSize());
> cellValue = evaluator.evaluate(cell);
> ....
>
>
> and i get following output:
>
> .....
> 11:25:03,397 DEBUG ExChecker:87 - start checkFormulaCells
> Array-Size: 1
> Ptgorg.apache.poi.hssf.record.formula.FuncPtg [TODAY nArgs=0] | Size: 3
> Exception in thread "main" java.lang.IndexOutOfBoundsException: Index: 5,
> Size: 5
> ......
>
>
>
> Pierre Lavignotte wrote:
>>
>> Regarding your stacktrace the TODAY() function is identified as a
>> Ref3DPtg token but I really don't know why...
>>
>> I can use either 3.1 FINAL or 3.5beta1-20080718 and the TODAY()
>> function is correctly evaluated.
>>
>> Try to look what is inside ptgArray when the error occurs :
>> Ptg[] ptgArray = FormulaParser.parse(cell.getCellFormula(), workbook);
>>
>>
>
> --
> View this message in context: http://www.nabble.com/IndexBound-Exception-by-FormulaCell-Evaluation-tp19622168p19624189.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Hardie82 <th...@yahoo.de>.
I insert following code:
....
if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
Ptg[] ptgArray = FormulaParser.parse(cell.getCellFormula(),
this.wbook);
System.out.println("Array-Size: " + ptgArray.length);
for(Ptg ptg : ptgArray)
System.out.println("Ptg" + ptg.toString() + " | Size: " +
ptg.getSize());
cellValue = evaluator.evaluate(cell);
....
and i get following output:
.....
11:25:03,397 DEBUG ExChecker:87 - start checkFormulaCells
Array-Size: 1
Ptgorg.apache.poi.hssf.record.formula.FuncPtg [TODAY nArgs=0] | Size: 3
Exception in thread "main" java.lang.IndexOutOfBoundsException: Index: 5,
Size: 5
......
Pierre Lavignotte wrote:
>
> Regarding your stacktrace the TODAY() function is identified as a
> Ref3DPtg token but I really don't know why...
>
> I can use either 3.1 FINAL or 3.5beta1-20080718 and the TODAY()
> function is correctly evaluated.
>
> Try to look what is inside ptgArray when the error occurs :
> Ptg[] ptgArray = FormulaParser.parse(cell.getCellFormula(), workbook);
>
>
--
View this message in context: http://www.nabble.com/IndexBound-Exception-by-FormulaCell-Evaluation-tp19622168p19624189.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Pierre Lavignotte <pi...@gmail.com>.
Regarding your stacktrace the TODAY() function is identified as a
Ref3DPtg token but I really don't know why...
I can use either 3.1 FINAL or 3.5beta1-20080718 and the TODAY()
function is correctly evaluated.
Try to look what is inside ptgArray when the error occurs :
Ptg[] ptgArray = FormulaParser.parse(cell.getCellFormula(), workbook);
On Tue, Sep 23, 2008 at 10:44 AM, Hardie82 <th...@yahoo.de> wrote:
>
> The complete output with printed formulas:
>
> 10:42:52,877 INFO Main:25 - start application
> File is directory: false
> 10:42:53,330 INFO ExChecker:54 - start checking sheet "Geschäftsstatistik"
> 10:42:53,330 DEBUG ExChecker:85 - start checkFormulaCells
> SUM(B7:B9)
> SUM(C7:C9)
> SUM(D7:D9)
> SUM(E7:E9)
> SUM(F7:F9)
> SUM(G7:G9)
> SUM(H7:H9)
> SUM(I7:I9)
> SUM(B7:H7)
> SUM(B8:H8)
> SUM(B9:H9)
> SUM(B16:B18)
> SUM(C16:C18)
> SUM(D16:D18)
> SUM(E16:E18)
> SUM(F16:F18)
> SUM(G16:G18)
> SUM(H16:H18)
> SUM(I16:I18)
> SUM(B16:H16)
> SUM(B17:H17)
> SUM(B18:H18)
> 10:42:53,549 DEBUG ExChecker:137 - finished checkFormulaCells
> 10:42:53,549 INFO ExChecker:58 - finished checking sheet
> "Geschäftsstatistik"
> 10:42:53,549 INFO ExChecker:54 - start checking sheet "Kontrolle"
> 10:42:53,549 DEBUG ExChecker:85 - start checkFormulaCells
> TODAY()
> Exception in thread "main" java.lang.IndexOutOfBoundsException: Index: 5,
> Size: 5
> at java.util.ArrayList.RangeCheck(Unknown Source)
> at java.util.ArrayList.get(Unknown Source)
> at org.apache.poi.hssf.model.Workbook.getSheetName(Workbook.java:534)
> at
> org.apache.poi.hssf.model.Workbook.findSheetNameFromExternSheet(Workbook.java:1867)
> at
> org.apache.poi.hssf.model.Workbook.getSheetReferences(Workbook.java:1849)
> at
> org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetReferences(HSSFWorkbook.java:788)
> at
> org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetReferences(HSSFWorkbook.java:79)
> at
> org.apache.poi.hssf.record.formula.Ref3DPtg.getSheetName(Ref3DPtg.java:169)
> at
> org.apache.poi.hssf.record.formula.Ref3DPtg.toFormulaString(Ref3DPtg.java:182)
> at
> org.apache.poi.hssf.model.FormulaParser.toFormulaString(FormulaParser.java:925)
> at
> org.apache.poi.hssf.model.FormulaParser.toFormulaString(FormulaParser.java:858)
> at org.apache.poi.hssf.usermodel.HSSFCell.getCellFormula(HSSFCell.java:686)
> at org.saxsys.ecpoi.ExChecker.checkFormulaCells(ExChecker.java:111)
> at org.saxsys.ecpoi.ExChecker.checkSheet(ExChecker.java:73)
> at org.saxsys.ecpoi.ExChecker.startCheck(ExChecker.java:55)
> at org.saxsys.ecpoi.Main.<init>(Main.java:69)
> at org.saxsys.ecpoi.Main.main(Main.java:21)
>
>
>
> Pierre Lavignotte wrote:
>>
>> Still no error...
>>
>> Not sure but maybe you have a specific formula that is causing the error.
>> Print out the formula before calling the evaluator.evaluate(cell) method.
>>
>> Pierre
>>
>
> --
> View this message in context: http://www.nabble.com/IndexBound-Exception-by-FormulaCell-Evaluation-tp19622168p19623598.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Hardie82 <th...@yahoo.de>.
The complete output with printed formulas:
10:42:52,877 INFO Main:25 - start application
File is directory: false
10:42:53,330 INFO ExChecker:54 - start checking sheet "Geschäftsstatistik"
10:42:53,330 DEBUG ExChecker:85 - start checkFormulaCells
SUM(B7:B9)
SUM(C7:C9)
SUM(D7:D9)
SUM(E7:E9)
SUM(F7:F9)
SUM(G7:G9)
SUM(H7:H9)
SUM(I7:I9)
SUM(B7:H7)
SUM(B8:H8)
SUM(B9:H9)
SUM(B16:B18)
SUM(C16:C18)
SUM(D16:D18)
SUM(E16:E18)
SUM(F16:F18)
SUM(G16:G18)
SUM(H16:H18)
SUM(I16:I18)
SUM(B16:H16)
SUM(B17:H17)
SUM(B18:H18)
10:42:53,549 DEBUG ExChecker:137 - finished checkFormulaCells
10:42:53,549 INFO ExChecker:58 - finished checking sheet
"Geschäftsstatistik"
10:42:53,549 INFO ExChecker:54 - start checking sheet "Kontrolle"
10:42:53,549 DEBUG ExChecker:85 - start checkFormulaCells
TODAY()
Exception in thread "main" java.lang.IndexOutOfBoundsException: Index: 5,
Size: 5
at java.util.ArrayList.RangeCheck(Unknown Source)
at java.util.ArrayList.get(Unknown Source)
at org.apache.poi.hssf.model.Workbook.getSheetName(Workbook.java:534)
at
org.apache.poi.hssf.model.Workbook.findSheetNameFromExternSheet(Workbook.java:1867)
at
org.apache.poi.hssf.model.Workbook.getSheetReferences(Workbook.java:1849)
at
org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetReferences(HSSFWorkbook.java:788)
at
org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetReferences(HSSFWorkbook.java:79)
at
org.apache.poi.hssf.record.formula.Ref3DPtg.getSheetName(Ref3DPtg.java:169)
at
org.apache.poi.hssf.record.formula.Ref3DPtg.toFormulaString(Ref3DPtg.java:182)
at
org.apache.poi.hssf.model.FormulaParser.toFormulaString(FormulaParser.java:925)
at
org.apache.poi.hssf.model.FormulaParser.toFormulaString(FormulaParser.java:858)
at org.apache.poi.hssf.usermodel.HSSFCell.getCellFormula(HSSFCell.java:686)
at org.saxsys.ecpoi.ExChecker.checkFormulaCells(ExChecker.java:111)
at org.saxsys.ecpoi.ExChecker.checkSheet(ExChecker.java:73)
at org.saxsys.ecpoi.ExChecker.startCheck(ExChecker.java:55)
at org.saxsys.ecpoi.Main.<init>(Main.java:69)
at org.saxsys.ecpoi.Main.main(Main.java:21)
Pierre Lavignotte wrote:
>
> Still no error...
>
> Not sure but maybe you have a specific formula that is causing the error.
> Print out the formula before calling the evaluator.evaluate(cell) method.
>
> Pierre
>
--
View this message in context: http://www.nabble.com/IndexBound-Exception-by-FormulaCell-Evaluation-tp19622168p19623598.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Pierre Lavignotte <pi...@gmail.com>.
Still no error...
Not sure but maybe you have a specific formula that is causing the error.
Print out the formula before calling the evaluator.evaluate(cell) method.
Pierre
On Tue, Sep 23, 2008 at 10:04 AM, Hardie82 <th...@yahoo.de> wrote:
>
> Hi Pierre,
>
> here is the complete code of the function:
>
> @SuppressWarnings("unchecked")
> private void checkFormulaCells (HSSFSheet sheet, CkResult result) {
> logger.debug("start checkFormulaCells");
> HSSFRow row = null;
> HSSFCell cell = null;
> HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet,
> this.wbook);
> ArrayList<String> errors = new ArrayList<String>();
>
> CellValue cellValue = null;
> for(Iterator<HSSFRow> rit = (Iterator<HSSFRow>)sheet.rowIterator();
> rit.hasNext(); ) {
> row = rit.next();
> evaluator.setCurrentRow(row);
> for(Iterator<HSSFCell> cit = (Iterator<HSSFCell>)row.cellIterator();
> cit.hasNext(); ) {
> cell = cit.next();
> if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
> cellValue = evaluator.evaluate(cell);
> switch (cellValue.getCellType()) {
> case HSSFCell.CELL_TYPE_ERROR:
> switch (cellValue.getErrorValue()) {
> case ErrorConstants.ERROR_DIV_0:
> errors.add("cell.value.faulty.zerodivision"); break;
> case ErrorConstants.ERROR_NAME:
> errors.add("cell.value.faulty.name"); break;
> case ErrorConstants.ERROR_VALUE:
> errors.add("cells.value.faulty.value"); break;
> default:
> errors.add("cell.value.faulty");
> }
> }
>
> if(!cell.getCellStyle().getLocked())
> errors.add("protection.disabled.formulacell");
> if(errors.size() > 0)
> result.addError((new CellReference(row.getRowNum(),
> cell.getCellNum())).formatAsString().replace("$", ""),
> errors.toArray(new String[errors.size()]));
> errors.clear();
> }
>
>
> }
> }
>
> logger.debug("finished checkFormulaCells");
> }
>
> The sheet i get with a for-loop:
>
> int anzSheets = wbook.getNumberOfSheets();
> for (int i = 0; i < anzSheets; i++) {
> logger.info("start checking sheet \"" + wbook.getSheetName(i) + "\"");
> result = checkSheet(wbook.getSheetAt(i));
> result.setSheetName(wbook.getSheetName(i));
> resultList.add(result);
> logger.info("finished checking sheet \"" + wbook.getSheetName(i) + "\"");
> }
>
> I use version 3.5_beta.
>
> Hardie
>
>
> Pierre Lavignotte wrote:
>>
>> Hi,
>>
>> I can run your code without error.
>> Can you tell me how you create your "evaluator" instance and how you
>> get your "sheet" reference ?
>> Also, wich version of POI are you using ?
>>
>> Pierre
>>
>
> --
> View this message in context: http://www.nabble.com/IndexBound-Exception-by-FormulaCell-Evaluation-tp19622168p19623049.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Hardie82 <th...@yahoo.de>.
Hi Pierre,
here is the complete code of the function:
@SuppressWarnings("unchecked")
private void checkFormulaCells (HSSFSheet sheet, CkResult result) {
logger.debug("start checkFormulaCells");
HSSFRow row = null;
HSSFCell cell = null;
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet,
this.wbook);
ArrayList<String> errors = new ArrayList<String>();
CellValue cellValue = null;
for(Iterator<HSSFRow> rit = (Iterator<HSSFRow>)sheet.rowIterator();
rit.hasNext(); ) {
row = rit.next();
evaluator.setCurrentRow(row);
for(Iterator<HSSFCell> cit = (Iterator<HSSFCell>)row.cellIterator();
cit.hasNext(); ) {
cell = cit.next();
if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
cellValue = evaluator.evaluate(cell);
switch (cellValue.getCellType()) {
case HSSFCell.CELL_TYPE_ERROR:
switch (cellValue.getErrorValue()) {
case ErrorConstants.ERROR_DIV_0:
errors.add("cell.value.faulty.zerodivision"); break;
case ErrorConstants.ERROR_NAME:
errors.add("cell.value.faulty.name"); break;
case ErrorConstants.ERROR_VALUE:
errors.add("cells.value.faulty.value"); break;
default:
errors.add("cell.value.faulty");
}
}
if(!cell.getCellStyle().getLocked())
errors.add("protection.disabled.formulacell");
if(errors.size() > 0)
result.addError((new CellReference(row.getRowNum(),
cell.getCellNum())).formatAsString().replace("$", ""),
errors.toArray(new String[errors.size()]));
errors.clear();
}
}
}
logger.debug("finished checkFormulaCells");
}
The sheet i get with a for-loop:
int anzSheets = wbook.getNumberOfSheets();
for (int i = 0; i < anzSheets; i++) {
logger.info("start checking sheet \"" + wbook.getSheetName(i) + "\"");
result = checkSheet(wbook.getSheetAt(i));
result.setSheetName(wbook.getSheetName(i));
resultList.add(result);
logger.info("finished checking sheet \"" + wbook.getSheetName(i) + "\"");
}
I use version 3.5_beta.
Hardie
Pierre Lavignotte wrote:
>
> Hi,
>
> I can run your code without error.
> Can you tell me how you create your "evaluator" instance and how you
> get your "sheet" reference ?
> Also, wich version of POI are you using ?
>
> Pierre
>
--
View this message in context: http://www.nabble.com/IndexBound-Exception-by-FormulaCell-Evaluation-tp19622168p19623049.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: IndexBound-Exception by FormulaCell-Evaluation
Posted by Pierre Lavignotte <pi...@gmail.com>.
Hi,
I can run your code without error.
Can you tell me how you create your "evaluator" instance and how you
get your "sheet" reference ?
Also, wich version of POI are you using ?
Pierre
On Tue, Sep 23, 2008 at 8:44 AM, Hardie82 <th...@yahoo.de> wrote:
>
> Hi. I have a problem by evaluate a formula cell. I want to iterate over all
> cell in a spreedsheet and wrote following code:
>
> for(Iterator<HSSFRow> rit = (Iterator<HSSFRow>)sheet.rowIterator();
> rit.hasNext(); ) {
> row = rit.next();
> evaluator.setCurrentRow(row);
> for(Iterator<HSSFCell> cit = (Iterator<HSSFCell>)row.cellIterator();
> cit.hasNext(); ) {
> cell = cit.next();
> if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
> cellValue = evaluator.evaluate(cell);
> switch (cellValue.getCellType()) {
> case HSSFCell.CELL_TYPE_ERROR:
> switch (cellValue.getErrorValue()) {
> case ErrorConstants.ERROR_DIV_0:
> errors.add("cell.value.faulty.zerodivision"); break;
> case ErrorConstants.ERROR_NAME:
> errors.add("cell.value.faulty.name"); break;
> case ErrorConstants.ERROR_VALUE:
> errors.add("cells.value.faulty.value"); break;
> default:
> errors.add("cell.value.faulty");
> }
> }
>
> if(!cell.getCellStyle().getLocked())
> errors.add("protection.disabled.formulacell");
> if(errors.size() > 0)
> result.addError((new CellReference(row.getRowNum(),
> cell.getCellNum())).formatAsString().replace("$", ""),
> errors.toArray(new String[errors.size()]));
> errors.clear();
> }
>
>
> }
> }
>
> i tested the code with some files, but everytime i get an
> IndexOutOfBoundException at codeline "cellValue =
> evaluator.evaluate(cell);". The stacktrace contains following message:
>
> Exception in thread "main" java.lang.IndexOutOfBoundsException: Index: 5,
> Size: 5
> at java.util.ArrayList.RangeCheck(Unknown Source)
> at java.util.ArrayList.get(Unknown Source)
> at org.apache.poi.hssf.model.Workbook.getSheetName(Workbook.java:534)
> at
> org.apache.poi.hssf.model.Workbook.findSheetNameFromExternSheet(Workbook.java:1867)
> at
> org.apache.poi.hssf.model.Workbook.getSheetReferences(Workbook.java:1849)
> at
> org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetReferences(HSSFWorkbook.java:788)
> at
> org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetReferences(HSSFWorkbook.java:79)
> at
> org.apache.poi.hssf.record.formula.Ref3DPtg.getSheetName(Ref3DPtg.java:169)
> at
> org.apache.poi.hssf.record.formula.Ref3DPtg.toFormulaString(Ref3DPtg.java:182)
> at
> org.apache.poi.hssf.model.FormulaParser.toFormulaString(FormulaParser.java:925)
> at
> org.apache.poi.hssf.model.FormulaParser.toFormulaString(FormulaParser.java:858)
> at org.apache.poi.hssf.usermodel.HSSFCell.getCellFormula(HSSFCell.java:686)
> at
> org.apache.poi.ss.usermodel.FormulaEvaluator.internalEvaluate(FormulaEvaluator.java:326)
> at
> org.apache.poi.ss.usermodel.FormulaEvaluator.evaluate(FormulaEvaluator.java:133)
> at org.saxsys.ecpoi.ExChecker.checkFormulaCells(ExChecker.java:111)
> at org.saxsys.ecpoi.ExChecker.checkSheet(ExChecker.java:73)
> at org.saxsys.ecpoi.ExChecker.startCheck(ExChecker.java:55)
> at org.saxsys.ecpoi.Main.<init>(Main.java:69)
> at org.saxsys.ecpoi.Main.main(Main.java:21)
>
> i have no idea whats wrong. have somebody a solution for this problem?
> --
> View this message in context: http://www.nabble.com/IndexBound-Exception-by-FormulaCell-Evaluation-tp19622168p19622168.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org