poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Kays" <Jeff.K...@onvoy.com>
Subject RE: problem evaluating reference to another sheet
Date Mon, 10 Jul 2006 14:54:15 GMT
OK, that didn't work, sorry. Here is a small snippet of the code I'm using:

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(args[0]));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheet("Sheet2");
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
HSSFCell c = row.getCell(1);
if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
    System.out.print(", Formula: " + c.getCellFormula());
    CellValue cv = evaluator.evaluate(c);
    if (cv == null) {
        System.out.println(", CellValue is NULL");
    } else if (cv.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        System.out.println(", Cell Type: String, value: " + cv.getStringValue());
    }
}

This code works fine when the cell reference is on the current sheet (Sheet2) but doesn't
work for a reference to another sheet (i.e. Sheet1!A1).

Thanks again.

  jeff


-----Original Message-----
From:	Jeff Kays
Sent:	Mon 7/10/2006 9:22 AM
To:	POI Users List
Cc:	
Subject:	RE: problem evaluating reference to another sheet

Thanks for the ideas and tips. I'm still having problems. Maybe the difference  is that Nidhi's
example is for creating/writing a spreadsheet, while I'm trying to read and evaluate a spreadsheet?

Anyways, I've created a small test program and spreadsheet to demonstrate my issue, which
are attached. After compiling, execute it by typing:
java ReadRefs testRefs.xls

The output should be:
Reading Sheet2
Cell: 0, type: 2, Formula: A1, Cell Type: String, value: S2A1
Cell: 1, type: 2, Formula: Sheet1!A1, Cell Type: String, value: S2A1

The program reads a couple of cells from Sheet2. The first cell (Cell: 0) references another
cell on Sheet2, which evaluates correctly. The second cell (Cell: 1) references a cell on
Sheet1, and the output is incorrect - it should not be S2A1 as shown above but S1A1. I'm guessing
that since I'm trying to evaluate something on a different sheet it's confusing the evaluator.

I'm hoping there is a simple solution to this. Otherwise I'll write some code to read the
sheet reference (the part before the '!'), create an evaluator for that sheet and evaluate
the cell reference through it.

Thanks.

  jeff



-----Original Message-----
From:	Sumit_Machwe@external.mckinsey.com [mailto:Sumit_Machwe@external.mckinsey.com]
Sent:	Fri 7/7/2006 4:08 PM
To:	POI Users List
Cc:	
Subject:	RE: problem evaluating reference to another sheet

Jeff,
        Example quoted by Nidhi is appropriate. I am not able to 
understand when you say "Now, it seems to be
ignoring the sheet reference and reading cells on the current sheet".  I 
think you should review the code again to check why it is referencing the 
"current" sheet and not the "correct" sheet. 

Are you using the single quotes to reference the other sheet name ?  Also, 
I think the example quoted below is referencing the cell based on its 
name, which is an added feature. If I rewrite the statement like 

String _formula =
"SUM(\'"+fromSheetname+"\'!"+"B:11"+":"+"B:12"+")"; 

and set this formula in a cell: 
cell.setCellType(2).setCellFormula(_formula); 
it should work. 

I don't have much experience with the HSSFFormulaEvaluator class. However, 
I don't think you need a Evaluator class to read the cell. If your 
intention is to read the formula of a cell which is of type formula, you 
can simply go a cell.getCellFormula(). Maybe I am missing something... :) 

- Sumit




"Nidhi Tuli" <ntuli@paymentone.com> 
07/07/2006 04:42 PM
Please respond to
"POI Users List" <poi-user@jakarta.apache.org>


To
"POI Users List" <poi-user@jakarta.apache.org>
cc

Subject
RE: problem evaluating reference to another sheet







For me the following code works fine.

CellReference toRef = new CellReference(1,"colName1");
CellReference fromRef = new CellReference(2,"colName2");
 
String formula =
"SUM(\'"+fromSheetname+"\'!"+toRef.toString()+":"+fromRef.toString()+")"
;

This code generated formula resulted in =SUM('Report'!B11:B11) where
'Report' is the name of the sheet.

Hope this helps
Nidhi

-----Original Message-----
From: Jeff Kays [mailto:Jeff.Kays@onvoy.com] 
Sent: Friday, July 07, 2006 1:35 PM
To: POI Users List
Subject: RE: problem evaluating reference to another sheet

Sumit, thanks for the idea. The sheet I was referencing did have a space
in it. So I renamed the sheet to remove the space. Now, it seems to be
ignoring the sheet reference and reading cells on the current sheet.

So I should only need the one HSSFFormulaEvaluator instance, set with
the current page? I can create a small test example if that would help.
Thanks!

  jeff


-----Original Message-----
From:            Sumit_Machwe@external.mckinsey.com
[mailto:Sumit_Machwe@external.mckinsey.com]
Sent:            Fri 7/7/2006 3:21 PM
To:              POI Users List
Cc: 
Subject:                 Re: problem evaluating reference to another sheet

Jeff,
        Cell references to other worksheets within the worksheet should 
work. When I implemented this, I found that "spaces" within the sheet
name 
are not recognized by POI. If sheet names are like "firstname lastname" 
then it will create problems. Try removing the space chars in the names 
and then give it a shot. 
I did this while "creating" multi-tab excel workbook having cross 
reference formula's.
hope this helps. 

Sumit 




"Jeff Kays" <Jeff.Kays@onvoy.com> 
07/07/2006 03:38 PM
Please respond to
"POI Users List" <poi-user@jakarta.apache.org>


To
<poi-user@jakarta.apache.org>
cc

Subject
problem evaluating reference to another sheet






Hi. I'm trying to read an Excel spreadsheet that has references to other

sheets in the same workbook. References to cells on my current sheet
work 
fine, but when I try to evaluate one of these references to another
sheet 
I get the following exception:
java.lang.StringIndexOutOfBoundsException: String index out of range: 0
        at java.lang.String.charAt(String.java:444)
        at 
org.apache.poi.hssf.util.CellReference.<init>(CellReference.java:44)
        at 
org.apache.poi.hssf.record.formula.ReferencePtg.<init>(ReferencePtg.java
:50)
        at 
org.apache.poi.hssf.model.FormulaParser.Ident(FormulaParser.java:292)
        at 
org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:480)
        at 
org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:569)
        at 
org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:61
7)
        at 
org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:721)
        at 
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEvaluate(HSSF
FormulaEvaluator.java:284)
        at 
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaE
valuator.java:181)


I'm using the 3.0 alpha1 jars.
My questions are: Should this work? If not, can I do something like
create 
an HSSFFormulaEvaluator for the sheet being referenced and then evaluate

the cell on the reference sheet with that evaluator?

Any help is greatly appreciated!

  jeff


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/





+=========================================================+
This message may contain confidential and/or privileged
information.  If you are not the addressee or authorized to
receive this for the addressee, you must not use, copy,
disclose or take any action based on this message or any
information herein.  If you have received this message in
error, please advise the sender immediately by reply e-mail
and delete this message.  Thank you for your cooperation.
+=========================================================+





---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/





+=========================================================+
This message may contain confidential and/or privileged
information.  If you are not the addressee or authorized to
receive this for the addressee, you must not use, copy,
disclose or take any action based on this message or any
information herein.  If you have received this message in
error, please advise the sender immediately by reply e-mail
and delete this message.  Thank you for your cooperation.
+=========================================================+










Mime
View raw message