Any way to match formulas?

[Stuart Turner <st...@turner.eu.com>]

Hi Everyone

I'm trying to replicate the Excel functionality to find formulas that
don't match with a reference formula.

In Excel you can write this: Selection.RowDifferences(ReferenceCell)

Excel will return all cells who's formulas don't match with the ReferenceCell.

The problematic part I can see of doing this manually is that Excel is
able to recognise relative references.

So if cell D4 has a formula of "=C3 + 2" and cell D5 has a formula of
"=C4 + 2" then it knows these are matching.

Is there anything that will help me accomplish this without writing a
full formula parser and comparator?

Thanks

Stuart

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Any way to match formulas?

[Stuart Turner <st...@turner.eu.com>]

I Yegor

Thanks for your reply. I thought this would need to be added.

Stuart



On 13 January 2013 18:46, Yegor Kozlov <ye...@dinom.ru> wrote:
> POI doesn't provide RowDifferences/ColumnDifferences functionality out
> of the box, but it does provide a formula parser so that you can
> compare parsed expressions and decide whether they match.
>
> Here is an example:
>
> HSSFWorkbook workbook = ...;
>
> HSSFEvaluationWorkbook fpb = HSSFEvaluationWorkbook.create(workbook);
>
> Ptg[] ptg_d4 = FormulaParser.parse("C3+2", fpb, FormulaType.CELL, -1);
>  // RefPtg,IntPtg,AddPtg
> Ptg[] ptg_d5 = FormulaParser.parse("C4+2", fpb, FormulaType.CELL, -1);
>  // RefPtg,IntPtg,AddPtg
>
> The formula parser return tokens in the Reverse polish Notation Order,
> i.e. for C3+2 it will return "C3","2","+"
>
> See more at http://poi.apache.org/spreadsheet/formula.html
>
>
> On Sun, Jan 13, 2013 at 2:06 PM, Stuart Turner <st...@turner.eu.com> wrote:
>> RowDifferences
>
> ---------------------------------------------------------------------
> 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: Any way to match formulas?

[Yegor Kozlov <ye...@dinom.ru>]

POI doesn't provide RowDifferences/ColumnDifferences functionality out
of the box, but it does provide a formula parser so that you can
compare parsed expressions and decide whether they match.

Here is an example:

HSSFWorkbook workbook = ...;

HSSFEvaluationWorkbook fpb = HSSFEvaluationWorkbook.create(workbook);

Ptg[] ptg_d4 = FormulaParser.parse("C3+2", fpb, FormulaType.CELL, -1);
 // RefPtg,IntPtg,AddPtg
Ptg[] ptg_d5 = FormulaParser.parse("C4+2", fpb, FormulaType.CELL, -1);
 // RefPtg,IntPtg,AddPtg

The formula parser return tokens in the Reverse polish Notation Order,
i.e. for C3+2 it will return "C3","2","+"

See more at http://poi.apache.org/spreadsheet/formula.html


On Sun, Jan 13, 2013 at 2:06 PM, Stuart Turner <st...@turner.eu.com> wrote:
> RowDifferences

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org