Can outsystems handle excel formulas?

Can outsystems handle excel formulas?

  
A typical excel file will contain one or more sheets and some columns and rows of data.

Now what would happen if you had one sheet with "data" and a main sheet that you use in your outsystems app to retrieve value using excel formulas?

For example if you used VLOOKUP function in your main sheet instead of specifying the actual value, would outsystems be able to read the value? What about if you had calculations with formula =B1+B2?
Afaik the result-values are imported. (At least with =SUM it was the case)

But to be 100% I think you should test it :)


Hi Robert,

TL;DR yes and no.
This is not an easy answer...

From my investigation OutSystems Platform 9 Amsterdam uses different libraries to handle excel import in .NET and Java stack. The support for functions is then the support given by each library in each stack and it may vary.
I have attached a module with my investigaton. You can save the resource included in the module to see what type of functions and formulas I tested.

A very important note before you continue reading is that this information is valid for importing data from excel file only. It does not apply to exporting data to excel.

In both stacks it seems that support for most common functions and formulas works the same:
  • This includes Math Arithmetics and Trigonometry (+,-,*,/,SUM,SUMIF,POWER,SQRT,SIN,COS,TAN,etc).
  • It also works for Text, Lookup and Reference functions (CONCATENATE, LEN, VLOOKUP, HLOOKUP, ADDRESS, etc.).
  • And it also works for Date functions (YEAR, MONTH, DATEDIFF, DAYS360, etc.). Regarding the date functions it is important to notice that the data types must be correctly set. For instance I created the entities by importing an excel file (you'll find as resource of the attached module). For Date function columns that data type inferred is Integer. This produces no runtime error in .NET but in Java it throws an error. And it is probably not what you really whant. The good thing is if you set the data type to be Date or Date Time accordingly everything works fine. Bottom line both data types in import structure and in the entity are set as Date and the import works fine.
There's one final but very important note to take. None of the libraries used performs any calculation of these functions. This means that the value that is imported is the last value that was calculated on Excel (a calculation usually occurs when you open and close the excel file). This is very important for functions whose value depends on when they are calculated, e.g. NOW() and RAND().

I hope this helps,
Cheers

PS: We'll make this information more clear in our documentation soon.
André Vieira

Thank you very much for doing the test.


"We'll make this information more clear in our documentation soon."

Yes please do, it will help alot :)