Print Page | Close Window

Reading a cell's formula

Printed From: TMS Software
Category: .NET Components
Forum Name: .NET Flexcel
Forum Discription:
Printed Date: 27 May 2020 at 2:43am

Topic: Reading a cell's formula
Posted By: Wilson James
Subject: Reading a cell's formula
Date Posted: 21 Feb 2013 at 4:04pm
How does one read a cell's formula with Flexcel?

Posted By: Adrian Gallero
Date Posted: 21 Feb 2013 at 4:11pm

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.


Posted By: Wilson James
Date 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);

Posted By: Adrian Gallero
Date 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 :)

Posted By: Eusebiu
Date 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?


Posted By: Adrian Gallero
Date Posted: 30 May 2016 at 12:41pm
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,

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:

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:
            xls.NewFile(1, TExcelFileFormat.v2016);
            xls.SetCellValue(1, 1, new TFormula("=A3 + 1"));
            var tokens = xls.GetFormulaTokens(1, 1);

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.

Posted By: Eusebiu
Date Posted: 30 May 2016 at 4:32pm
It seems the GetFormulaTokens is what I am looking for my tracer.

Print Page | Close Window