This project has moved and is read-only. For the latest updates, please go here.

How to find the referenced cells in a formula

Apr 20, 2014 at 3:38 PM
Hi

I need to get the referenced Cells in a formula. e.g. the formula = B1 * Average(B2:B6) on worksheet Testing.

I want to get a list of absolute cells references.

so in this case:

Testing.B1
Testing.B2:Testen.B6

or even better :

Testing.B1
Testing.B2
Testing.B3
Testing.B4
Testing.B5
Testing.B6

I saw there is a formulatokens property but is working.

ExcelPackage._workbook._formulaTokens.Current' threw an exception of type 'System.IndexOutOfRangeException'

Has anyone a example of the otherwise i will make a new own solution.

Regards,

Frans
Apr 20, 2014 at 9:31 PM
Hi,

There are functionality in EPPlus for handling formula tokens, but some of it is internal and nothing is documented so far (I guess you use the 4.0 beta). However the following might work for you:
var ws = package.Workbook.Worksheets["Testing"];
var tokens = package.Workbook.FormulaParser.Lexer.Tokenize(ws.Cells["B1"].Formula);
var references = tokens.Where(x => x.TokenType == TokenType.ExcelAddress);
foreach (var reference in references)
{
        var address = reference.Value;
        var excelAddress = new ExcelAddress(address);
}
The ExcellAddress class from the OfficeOpenXml namespace will give you some data about the referenced cell/range.

I am aware that we could expose this in a better way via the EPPlus interfaces, and we might do so in a future release... Hope this helps.

/Mats
Apr 22, 2014 at 10:11 AM
Edited Apr 22, 2014 at 10:39 AM
Hi Mats,

thanks for the reply. you are correct, I am using the 4.0.0- Beta2 version.

Unfortunally the workbook doesn't expose FormulaParser, only the FormulaParserManager. Which in turn doesn't expose a Lexer and no Tokenize in this Lexer.

Do you know any other way other then wainting for a new release en hoping this feature is part of it?

Frans
Apr 22, 2014 at 10:35 AM
Sorry, now I see that the FormulaParser property is Internal.

Try this instead:
class Program
    {
        static void Main(string[] args)
        {
            // Open your package here.
            using(var package = new ExcelPackage(new MemoryStream()))
            {
                var provider = new EpplusExcelDataProvider(package);
                var formulaParser = new FormulaParser(provider);
                var tokens = formulaParser.Lexer.Tokenize("B1 * Average(B2:B6)");
                var references = tokens.Where(x => x.TokenType == TokenType.ExcelAddress);
                foreach(var reference in references)
                {
                    Console.WriteLine(reference.Value);
                }
                Console.ReadKey();
            }
        }
    }
Marked as answer by Frans_van_Ek on 4/22/2014 at 2:53 AM
Apr 22, 2014 at 10:54 AM
swmal wrote:
Sorry, now I see that the FormulaParser property is Internal.

Try this instead:
class Program
    {
        static void Main(string[] args)
        {
            // Open your package here.
            using(var package = new ExcelPackage(new MemoryStream()))
            {
                var provider = new EpplusExcelDataProvider(package);
                var formulaParser = new FormulaParser(provider);
                var tokens = formulaParser.Lexer.Tokenize("B1 * Average(B2:B6)");
                var references = tokens.Where(x => x.TokenType == TokenType.ExcelAddress);
                foreach(var reference in references)
                {
                    Console.WriteLine(reference.Value);
                }
                Console.ReadKey();
            }
        }
    }
Thanks. I did manage to get the tokens.. Still some work to do on my part, but i can proceed. Thanks a lot.