Web forum is in read-only mode. Login as active registered customer for write access
  Forum Search   New Posts New Posts

Reading a cell's formula

 Post Reply Post Reply
Author
Eusebiu View Drop Down
New Member
New Member
Avatar

Joined: 27 Dec 2015
Posts: 5
Post Options Post Options   Quote Eusebiu Quote  Post ReplyReply Direct Link To This Post Topic: Reading a cell's formula
    Posted: 30 May 2016 at 4:32pm
It seems the GetFormulaTokens is what I am looking for my tracer.
Thanks!
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1245
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 30 May 2016 at 12:41pm
Hi,
I am not sure I understand the question, but if GetCellValue is not a TFormula, then there is no formula in the cell, so you can't retrieve it.
To be more clear: A cell can have:
    Empty value,
    Number,
    String,
    Boolean,
    Error,
    Formula

If the cell has an error (TFlxFormulaErrorValue) then it doesn't have a formula. You can get a cell with an error by typing "#DIV/0!" (without quotes) directly in Excel. This is not a formula, this is an error, and you can't retrieve the formula behind because there is none.

If  the cell has a formula (the value returned is a TFormula), then the formula result can be:
    Empty,
    Number,
    String,
    Boolean,
    Error

The difference is that a formula result can't be another formula.
If you get a TFormula with a TFlxFormulaErrorValue as result, then you can retrieve the formula text with TFormula.Text. 

But if you want to know which cells a cell references, instead of parsing the formula text, yourself, you can use the GetFormulaTokens method:
[CODE]
            xls.NewFile(1, TExcelFileFormat.v2016);
            xls.SetCellValue(1, 1, new TFormula("=A3 + 1"));
            var tokens = xls.GetFormulaTokens(1, 1);
[CODE]

This will return an array of tokens which define the formula in RPN notation. There you can get the TTokenCellAddress, TTokenCellRange, TTokenRelativeCellAddress, TTokenRelativeCellRange for the references in that formula.
Back to Top
Eusebiu View Drop Down
New Member
New Member
Avatar

Joined: 27 Dec 2015
Posts: 5
Post Options Post Options   Quote Eusebiu Quote  Post ReplyReply Direct Link To This Post Posted: 30 May 2016 at 8:31am
How can the formula be retrieved if GetCellValue is not a TFormula (e.g. TFlxFormulaErrorValue)?
I am trying to create feature in my app to trace the errors back until all cells in the formula are valid.
Is there already a feature for this in FlexCel?

Thanks!
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1245
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 21 Feb 2013 at 4:40pm
Well, if I was rewriting FlexCel .NET today (and not in .NET 1.1 as it was originally created), I would have
GetCellValue return a struct that can contain a formula, a string, a number or a boolean, instead of returning an object as it does today.

But the idea would be the same, GetCellValue returns whatever is in the cell, so there isn't really a need for another method GetCellFormula. We have already enough methods in XlsFile :)

Back to Top
Wilson James View Drop Down
New Member
New Member
Avatar

Joined: 21 Feb 2013
Posts: 4
Post Options Post Options   Quote Wilson James Quote  Post ReplyReply Direct Link To This Post Posted: 21 Feb 2013 at 4:27pm
Perfect! Thanks for the prompt reply.

A bit of feedback: My intuition led me to expect something like:
    string formula = xls.GetCellFormula(1,1);
Back to Top
Adrian Gallero View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 1245
Post Options Post Options   Quote Adrian Gallero Quote  Post ReplyReply Direct Link To This Post Posted: 21 Feb 2013 at 4:11pm
Hi,

XlsFile xls = new XlsFile("myfile.xlsx", true);
object CellValue = xls.GetCellValue(1,1);

TFormula fmla = CellValue as TFormula;
if (fmla != null)
{
   DoSomething(fmla.Text, fmla.Result);
}

You can find a more complete example in how to read values with FlexCel in the "Reading Files" demo.

Regards,
   Adrian.
Back to Top
Wilson James View Drop Down
New Member
New Member
Avatar

Joined: 21 Feb 2013
Posts: 4
Post Options Post Options   Quote Wilson James Quote  Post ReplyReply Direct Link To This Post Posted: 21 Feb 2013 at 4:04pm
How does one read a cell's formula with Flexcel?

Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down