LebGeeks

Cpt.Majed

Member

Tool to help working on Excel

Hello,

As a title say, I am working on a tool (In C#) that helps us work with excel  . Since all our work consist of excel sheets and XML we need to create a small tool that help us do the work faster and better.

We are working on many aspects of this tool :

-Re-Format the excel formula so the user can understand it easier and faster
In order to do that many suggestions were placed

ex : =IF($HA$1=23,""&AN63&" "&AO63&"",""&AK56&" "&AL56&"")

1- All the operators will be in bold and the " ( ) " will get colored then displayed in a RichtextBox

2- The formula will be parsed and placed in a Treeview 
3- The formula will be placed in a diagram

My question is : What can and can't be done in C# and which display is better ?

Thanks in advance

saeidw

Member

Re: Tool to help working on Excel

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.