You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Pedro Miguel Ribeiro Martins <pe...@gmail.com> on 2012/11/28 17:03:26 UTC
BUG!? in Area3DPtg Class??
Hi everybody:
I've parsed a Spreadsheet formula with FormulaParser.parse() which returns an Array of Ptg parsing tokens, being some of them classes of Ref3DPtg.
These member refer to cells in other sheets, and therefore have the method getExternSheetIndex().
The problem is that this method ALWAYS returns the bad index of the target Sheet, producing what seems like random indexes.
Has anyone ever had this behavior? I can share the code if necessary.
Thanks,
Pedro
Re: BUG!? in Area3DPtg Class??
Posted by pedromartins4 <pe...@gmail.com>.
Hi again.
This worked neatly. Thanks :)
But now I have the same problem with XSSFCell, since:
Field f = XSSFCell.class.getDeclaredField("_record");
does not work. Any help?
Thanks in advance,
Pedro
On Dec 2, 2012, at 1:33 PM, Yegor Kozlov wrote:
> Firstly, formulas in the binary .xls format are stored in parsed form.
> You don't need to re-parse the formula string, instead you can read
> Ptgs directly from the worksheet. The underlying formula record is
> private but you can access via reflection:
>
> Field f = HSSFCell.class.getDeclaredField("_record");
> f.setAccessible(true);
>
> HSSFCell cell = wb.getSheetAt(0).getRow(5).getCell(1);
>
> FormulaRecordAggregate fa = (FormulaRecordAggregate)f.get(cell);
>
> Ptg[] ptg = fa.getFormulaTokens();
>
>
> Secondly, externSheetIndex in Area3DPtg is the one-based index to
> EXTERNSHEET record in the workbook.
>
> To get sheet index you should use
> HSSFEvaluationWorkbook.convertFromExternSheetIndex(int
> externSheetIndex ) :
>
>
> int sheetIndex =
> refWorkbookEvaluator.getSheetIndexByExternIndex(ref3D.getExternSheetIndex());
>
> Here is the code:
>
>
> HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("ref3D.xls"));
> HSSFEvaluationWorkbook ew =
> HSSFEvaluationWorkbook.create((HSSFWorkbook) wb);
>
> Field f = HSSFCell.class.getDeclaredField("_record");
> f.setAccessible(true);
>
> HSSFCell cell = wb.getSheetAt(0).getRow(5).getCell(1);
> System.out.println(cell.getCellFormula());
>
> FormulaRecordAggregate fa = (FormulaRecordAggregate)f.get(cell);
>
> Ptg[] ptg = fa.getFormulaTokens();
> Ref3DPtg cellRef = (Ref3DPtg) ptg[0];
>
> System.out.println("ExternSheetIndex: " +
> cellRef.getExternSheetIndex());
> System.out.println("SheetIndex: " +
> ew.convertFromExternSheetIndex(cellRef.getExternSheetIndex()));
> System.out.println("SheetName: " +
> ew.getSheetNameByExternSheet(cellRef.getExternSheetIndex()));
>
>
> Yegor
>
>
> On Thu, Nov 29, 2012 at 3:18 PM, Pedro Miguel Ribeiro Martins
> <pr...@di.uminho.pt> wrote:
>> FormulaParsingWorkbook ew = HSSFEvaluationWorkbook.create((HSSFWorkbook)
>> wb);
>
> ---------------------------------------------------------------------
> 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: BUG!? in Area3DPtg Class??
Posted by Yegor Kozlov <ye...@dinom.ru>.
Firstly, formulas in the binary .xls format are stored in parsed form.
You don't need to re-parse the formula string, instead you can read
Ptgs directly from the worksheet. The underlying formula record is
private but you can access via reflection:
Field f = HSSFCell.class.getDeclaredField("_record");
f.setAccessible(true);
HSSFCell cell = wb.getSheetAt(0).getRow(5).getCell(1);
FormulaRecordAggregate fa = (FormulaRecordAggregate)f.get(cell);
Ptg[] ptg = fa.getFormulaTokens();
Secondly, externSheetIndex in Area3DPtg is the one-based index to
EXTERNSHEET record in the workbook.
To get sheet index you should use
HSSFEvaluationWorkbook.convertFromExternSheetIndex(int
externSheetIndex ) :
int sheetIndex =
refWorkbookEvaluator.getSheetIndexByExternIndex(ref3D.getExternSheetIndex());
Here is the code:
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("ref3D.xls"));
HSSFEvaluationWorkbook ew =
HSSFEvaluationWorkbook.create((HSSFWorkbook) wb);
Field f = HSSFCell.class.getDeclaredField("_record");
f.setAccessible(true);
HSSFCell cell = wb.getSheetAt(0).getRow(5).getCell(1);
System.out.println(cell.getCellFormula());
FormulaRecordAggregate fa = (FormulaRecordAggregate)f.get(cell);
Ptg[] ptg = fa.getFormulaTokens();
Ref3DPtg cellRef = (Ref3DPtg) ptg[0];
System.out.println("ExternSheetIndex: " +
cellRef.getExternSheetIndex());
System.out.println("SheetIndex: " +
ew.convertFromExternSheetIndex(cellRef.getExternSheetIndex()));
System.out.println("SheetName: " +
ew.getSheetNameByExternSheet(cellRef.getExternSheetIndex()));
Yegor
On Thu, Nov 29, 2012 at 3:18 PM, Pedro Miguel Ribeiro Martins
<pr...@di.uminho.pt> wrote:
> FormulaParsingWorkbook ew = HSSFEvaluationWorkbook.create((HSSFWorkbook)
> wb);
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: BUG!? in Area3DPtg Class??
Posted by Pedro Miguel Ribeiro Martins <pr...@di.uminho.pt>.
Here it is:
Workbook wb = WorkbookFactory.create(new File(CHANGE THE PATH+"/Class.xls"));
FormulaParsingWorkbook ew = HSSFEvaluationWorkbook.create((HSSFWorkbook) wb);
// Formula is: Sheet2!B4
Ptg[] ptg = FormulaParser.parse(wb.getSheetAt(0).getRow(5).getCell(1).getCellFormula(), ew, FormulaType.NAMEDRANGE , 0);
Ref3DPtg cellRef = (Ref3DPtg) ptg[0];
// will print: org.apache.poi.ss.formula.ptg.Ref3DPtg [sheetIx=0 ! B4]
System.out.println(cellRef);
// This External Sheet Ref should be 1, but is 0
System.out.println("External Sheet Ref -> "+cellRef.getExternSheetIndex());
// If formula is Sheet1!B4 External Sheet Ref will be 2, should be 0
// If formula is Sheet3!B4 External Sheet Ref will be 1, should be 2
I am also sending the Spreadsheet attached in case you need it.
Thanks for the help mate.
Pedro
On 29 Nov 2012, at 10:46, Yegor Kozlov wrote:
> Please post sample code. It's hard to tell what is wrong without a example.
>
> Yegor
>
> On Wed, Nov 28, 2012 at 8:03 PM, Pedro Miguel Ribeiro Martins
> <pe...@gmail.com> wrote:
>> Hi everybody:
>>
>> I've parsed a Spreadsheet formula with FormulaParser.parse() which returns an Array of Ptg parsing tokens, being some of them classes of Ref3DPtg.
>> These member refer to cells in other sheets, and therefore have the method getExternSheetIndex().
>> The problem is that this method ALWAYS returns the bad index of the target Sheet, producing what seems like random indexes.
>> Has anyone ever had this behavior? I can share the code if necessary.
>>
>> Thanks,
>> Pedro
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
Re: BUG!? in Area3DPtg Class??
Posted by Yegor Kozlov <ye...@dinom.ru>.
Please post sample code. It's hard to tell what is wrong without a example.
Yegor
On Wed, Nov 28, 2012 at 8:03 PM, Pedro Miguel Ribeiro Martins
<pe...@gmail.com> wrote:
> Hi everybody:
>
> I've parsed a Spreadsheet formula with FormulaParser.parse() which returns an Array of Ptg parsing tokens, being some of them classes of Ref3DPtg.
> These member refer to cells in other sheets, and therefore have the method getExternSheetIndex().
> The problem is that this method ALWAYS returns the bad index of the target Sheet, producing what seems like random indexes.
> Has anyone ever had this behavior? I can share the code if necessary.
>
> Thanks,
> Pedro
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org