You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Yegor Kozlov <ye...@dinom.ru> on 2012/12/02 14:33:03 UTC

Re: BUG!? in Area3DPtg Class??

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 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