Platform
Learn
Community
Support
Sign up
or
Log in
Profile
messages
Messages
logout
Logout
Platform
Home
Downloads
IPP
Licensing
Project Sizing
Learn
Training
Documentation
Evaluation Guide
Community
Jobs
Home
Forums
Forge
Ideas
Badges
Members
Support
Partner Guide
Resources Library
Opportunities
Account Management
Technical Proof
Sign up
or
Log in
Home
Jobs
Forums
Forge
Ideas
Members
Badges
Dear ,
What would improve your OutSystems Community experience? Let us know by taking this 2-minute survey.
Pick up the survey
Community
›
Forums
›
Technology & Integration
Can outsystems handle excel formulas?
Community
›
Forums
›
Technology & Integration
Can outsystems handle excel formulas?
New Post
New Post
- -
Posted on 2015-08-10
- -
Rank: #28
Posted on 2015-08-10
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?
J.
Posted on 2015-08-10
J.
Rank: #9
Posted on 2015-08-10
Solution
Afaik the result-values are imported. (At least with =SUM it was the case)
But to be 100% I think you should test it :)
Solution
André Vieira
Posted on 2015-08-10
André Vieira
Rank: #7
Posted on 2015-08-10
Solution
ExcelImportTests.oml
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.
Solution
- -
Posted on 2015-08-10
- -
Rank: #28
Posted on 2015-08-10
Solution
André Vieira
Thank you very much for doing the test.
Solution
- -
Posted on 2015-08-10
- -
Rank: #28
Posted on 2015-08-10
Solution
"We'll make this information more clear in our documentation soon."
Yes please do, it will help alot :)
Solution
Login to reply
New Post
Available Forums
Technology & Integration
News and Announcements
Forge Discussions
Meta
Community Quick Guides
Usability
Installation Troubleshooting
Personal Environment Troubleshooting
Forum Notifications
Email Digest Settings
Loading...
(hidden) New Badge Popup