My scenario is to convert String to Uipath Mathematical formulas. Currently we are trying to maintain a list of formulas that we are using in uipath with an assign activity. If any formulas get changed in future, the client will just replace the formula in excel without making changes in code.
In this we are replacing $ with CurrentRow and * with .ToString.
This excel file we are taking as a dictionary. Based on column name the formula should be applied to the data row which is in loop.
Currently I am getting the formula as a string. If I assign this value in for each loop the entire formula is added to the data table. Is there any way to get the output result of the formula with any other approach?
These formulas will change frequently. Could anyone please help me with this?
Hello.
In a cell of an Excel file the value is different by Formula.
After my knowledge, these are different properties of the cell.
And in UIPath, I think it should have to work in the same way.
When retrieving a value of a cell, just use the item property.
ColumnName can be also the name of an Excel range.
Your code string of the Item argument must be ColumnName
Formula is actually the value you want to get from cell.
myvalue = CurrentRow.Item(ColumnName)
Item gives the value of a column in a row, which is a cell.
This is when you read the formula in the first Excel file.
This value must be kept somehow in a DataTable, or a list.
When setting the formula, this happens into another Excel:
CurrentRow.Item(ColumnName).Formula = myvalue
I think this might have to be working.
Hope it helps, Adrian
Thanks for your valuable reply. I am getting this error while using .expression
RemoteException wrapping System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. â> RemoteException wrapping System.Data.EvaluateException: The expression contains undefined function call IF().
at System.Data.FunctionNodeâŚctor(DataTable table,
String name)
at System.Data.ExpressionParser.Parse()
at System.Data.DataExpressionâŚctor(DataTable table,
String expression,
Type type)
at System.Data.DataColumn.set_Expression(String value)
â End of inner exception stack trace â
at System.RuntimeMethodHandle.InvokeMethod(Object target,
Object arguments,
Signature sig,
Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj,
Object parameters,
Object arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj,
BindingFlags invokeAttr,
Binder binder,
Object parameters,
CultureInfo culture)
at System.Reflection.RuntimePropertyInfo.SetValue(Object obj,
Object value,
BindingFlags invokeAttr,
Binder binder,
Object index,
CultureInfo culture)
at System.Reflection.RuntimePropertyInfo.SetValue(Object obj,
Object value,
Object index)
at System.Activities.ExpressionUtilities.PropertyLocationFactory1.PropertyLocation.set_Value(T value) at System.Activities.Location1.ReferenceLocation.set_Value(T value)
at System.Activities.Location`1.set_ValueCore(Object value)
at System.Activities.ActivityContext.SetValueCore[T](LocationReference locationReference,
T value)
at System.Activities.ActivityContext.SetValue[T](LocationReference locationReference,
T value)
at System.Activities.Argument.Set(ActivityContext context,
Object value)
at System.Activities.Statements.Assign.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance,
ActivityExecutor executor,
BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor,
BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor,
BookmarkManager bookmarkManager,
Location resultLocation)
When you are trying to perform this replace, this wonât get replaced with the Datarow object CurrentRow instead it will be replaced with the String "CurrentRow".
It will not be an Expression and it will be just a String.
Better approach would be to convert your formulas suitable for either a DataColumn Expression and update the datatable or as a Excel formula itself where you use Write Cell Activity inside an Excel Application Scope and then maybe use Autofill Range Activity to apply the formula to all other cells.
Excel Formula is preferred as it seems the Formula would be a bit more complicated (multiple ifâs)
Let us know your thoughts on this and if needed further help on the conversion.
I have two excel files. One file contains the actual formulas which we are using in UiPath expression editor and other one is result sheet.
My requirements is to read the formula sheet and convert the formula to expression. This expression should work in data table not in excel. The result of the expression should be written in respective columns of the result sheet.
Is there any way to convert string to expression in UiPath
We are trying to give more flexibility to our client in terms of formulas. If any formula gets changed., then need to change in config. The actual process is using an excel macro, but sometimes excel is failing due to heavy size. They need to get rid of this.
Please suggest a better approach for this scenario.
Hello, here I think the problem is the name of the column.
âColumn1â is a generic name, you should use the name of a column from Excel.
As I had tried to explain, can be also used the name of an Excel column Range
If the column name is âAâ, you can use âAâ. if is it âBâ, then use âBâ, and so onâŚ
It gives an error because the column with the name âColumn1â doesnât exist.
Since the Expression Editor makes an evaluation, then it throws an exception.
In the file Formula.xlsx, the formula can be get as a value from column âBâ
In Result.xlsx must be set the Formula for cells âAâ, âBâ, and so on in row 2.
Hope it helps, Adrian
lrtetala
Anil_G
james07
sven.wullum1
ijanszen1
vineelag
Monchello_Curry
Affan_Basar
Chimbu
Hemant_Deshmukh