It sounds like you want to parse Excel expressions and display them, once with syntax highlighting, and once as a tree.
Let's take the expression above and try to define its grammar:
IF($HA$1=23,""&AN63&" "&AO63&"",""&AK56&" "&AL56&"")
I went ahead and searched for "
Excel BNF grammar" and picked a random result, so we'll use that as our foundation.
The IF() part looks like a function call:
functionCall ::= functionName, '(', logicalExpression, ',', formulaExpression, [',', formulaExpression], ')'
functionName ::= 'IF' | 'AND' | 'NOT' | ...
Everything between single quotes is a literal that appears as written.
Everything inside square brackets (
[]) is optional and can be removed.
The commas just mean that these things are written one after the other in
sequence. The pipe characters (
|) mean that we have a choice, so
'A' | 'B'
means that either the character 'A' or 'B' will appear, but not
both.
We now need definitions of
logicalExpression and
formulaExpression.
logicalExpression ::= expression, { logicalOperator, expression }
logicalOperator ::= '>' | '<' | '=' | '>=' | '<='
formulaExpression ::= expression, { mathOperator, expression }
mathOperator ::= '+' | '-' | '*' | '/' | '^'
expression ::= primitve | cell | functionCall
Things inside the curly braces (
{}) are things that can be repeated or removed.
So a
logicalExpression is something that looks like
2 > 3
and a
formulaExpression is something that looks like
2 + 3
or
2 + 3 - 4
.
Notice how we don't just mention numbers, we talk about
expression
because expressions can be data, or
functionCall or cell addresses.
primitve ::= number | boolean | string | date
cell ::= ['$'], column, ['$'], row
column ::= letter, [ letter ]
row :: = digit, { digit }
This is more or less a very general grammar for the kinds of expressions used
by Excel. With this grammar, you can probably write (or generate) a parser
that can give you a tree data structure representing the expression. You can
then take this data structure and render it or manipulate it. You can use it
to re-write the expression but with syntax highlighting for the different
parts and you can use it to populate a TreeView in your UI.
You might want to look into
recursive descent parsing for writing a simple parser, or you might want something more heavy duty.