How does one read a cell's formula with Flexcel?
Hi,
Perfect! Thanks for the prompt reply.
Well, if I was rewriting FlexCel .NET today (and not in .NET 1.1 as it was originally created), I would have
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!