Reading a cell's formula

How does one read a cell's formula with Flexcel?


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.

Perfect! Thanks for the prompt reply.


A bit of feedback: My intuition led me to expect something like:
    string formula = xls.GetCellFormula(1,1);

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

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!

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.

It seems the GetFormulaTokens is what I am looking for my tracer.
Thanks!