How to validate a excel expression programmatically

Question

I have been developing an application that one of it's responsability is provide to user an page that it's possible to write math expression in EXCEL WAY.

It is an application in ASP.NET MVC, and it's use the SpreadSheetGear library to EXECUTE excel expression.

As it's show below, The page has an texarea to write expression and two button on the right. The green one is for VALIDATE THE EXPRESSION and the red one is for clean textarea.

A,B,C are parameter, that application will replace for values. Notice that it is not possible to know the parameter data type. I mean, if I write a concatenate function, It is necessary that user use double quotes (") to delimitate string. For example CONCATENATE("A","B") thus, is necessary that user KNOW functions parameters and its correlate data types.

My main issue is how to validate the expression? SpreadSheetGear there isn't any method to perform this validation.

The method spreadsheetgear provides to perform an formula is:

string formula = "{formula from textarea}"
worksheet.EvaluateValue(formula)

and it's expect and string.

As I don't know what formula user will write, or how many parameters this formula has and what are the parameters data type, it's kind difficult to validate.

Now my question is?

How could I validate the expression?

I think in an alternative: Provide to user and page with textbox for each parameter in the expression. The user will be able to provide some data and validate the RESULT of formula. If the sintaxe of formula is wrong the application throw an exception.

It would be a good solution, but for each "PROCESS" that user will interact to, He'll write 10, 15 formulas, and maybe it would be little painful.

Anyone could provide me an Good solution for that?

Thank you!

Answer 1

https://sites.google.com/site/akshatsharma80/home/data-structures/validate-an-arithmetic-expression

refer this site for validation

Answer 2

This is a very late response but I have been working on expression evaluators in Excel with VBA for a while and I might shed some light. I have three solutions to try but all have limitations.

1) Ask the user to put a '$' after a variable name to signify a string (or some other unique character). Drawback is that it is not as simple as typing a single letter for a variable.

2) Assume all variables entered are double precision. Then change the variable to strings in all combinations until one combination works. Could be very time consuming to try all the combinations if the user enters lots of individual variables.

3) Assume all variables entered are double precision. But then have a list in your program of functions that require strings for parameters. Then you could parse the expression, lookup the functions in your list and then designate the parameters that require string input with a string signifier (like in step 1). This will not account for user defined functions.

Now to test out the function, replace all the numeric variables with '1' and all the string variables with "a", then EvaluateValue. If you get a result or an error signifying a calculation error, it is good.

BTW, in order to parse the expression, I suggest the following method. I do not know C#, only VB, so I will only talk in general terms.

1) Take your expression string and do a search and replace of all the typical operators with the same operator but with a backslash ("\") in front and behind the operator (you can use any other character that is not normally used in Excel formulas if you like). This will delineate these operators so that you can easily ignore them and split up your expression into chunks. Typically only need to delineate +,-,/,*,^,<,>,= and {comma}. So search for a "+" and replace it with a "\+\" and so on. For parenthesis, replace "(" and ")" with "(\\" and "\\)" respectively.

So your sample formula "SUM(A, SQRT(B, C)) * PI()" will look like this: "SUM(\\A\,\ SQRT(\\B\,\ C\\)\\) \*\ PI(\\\\)"

You can also clean up the string a bit more by eliminating any spaces and by eliminating redundant backslashes by replacing every three consecutive backslashes with a single one (replace "\\" with "\").

2) In Visual Basic there is a command called 'Split' that can take a string like this and split it into a one dimensional array using a delimiter (in this case, the backslash). There must be an equivalent in C# or you can just make one. Your array will look like this: "SUM(", "", "A", ",", "SQRT(", "", "B", etc.

Now iterate through your array, starting at the first element and then skipping every other element. These elements will either be a number (a numeric test), a variable, a function (with have a "(" at the end of it), a parenthesis or blank.

Now you can do other checks as you need and replace the variables with actual values.

3) When you are done, rejoin the array back into a string, without any delimiters, and try the Evaluate function.