MS Excel to something Flowcode understands..
Moderator: Benj
-
- Posts: 502
- Joined: Wed Nov 07, 2007 6:51 pm
- Location: Antwerp Belgium
- Has thanked: 121 times
- Been thanked: 108 times
- Contact:
MS Excel to something Flowcode understands..
I have an Excel spreadsheet with a lot of trigonometry formula's.
I would like to use them in a Flowcode project.
Is there a way that I can import these formulas (in an easy way) into FC?
Is there, for instance, a way I can convert them to C and embed them into FC?
Or do I need to convert every single formula into something FC knows?
I would like to use them in a Flowcode project.
Is there a way that I can import these formulas (in an easy way) into FC?
Is there, for instance, a way I can convert them to C and embed them into FC?
Or do I need to convert every single formula into something FC knows?
-
- Valued Contributor
- Posts: 740
- Joined: Fri Jun 06, 2014 3:53 pm
- Has thanked: 185 times
- Been thanked: 204 times
- Contact:
Re: MS Excel to something Flowcode understands..
Hi
There are online tools for what you require such as http://excelformulabeautifier.com/ (or similar) that allow you to paste your formula and cut the conversion.
Hope this may help.
Regards
There are online tools for what you require such as http://excelformulabeautifier.com/ (or similar) that allow you to paste your formula and cut the conversion.
Hope this may help.
Regards
-
- Posts: 502
- Joined: Wed Nov 07, 2007 6:51 pm
- Location: Antwerp Belgium
- Has thanked: 121 times
- Been thanked: 108 times
- Contact:
Re: MS Excel to something Flowcode understands..
Looks promising, thank you Chipfryer27.chipfryer27 wrote:Hi
There are online tools for what you require such as http://excelformulabeautifier.com/ (or similar) that allow you to paste your formula and cut the conversion.
Hope this may help.
Regards
In this script you can convert Excel formulas to C#, but can I use C# into a Flowcode project? Of can I only use C?
You write "or similar", do you know other Excel conversion tools? I didn't find any that can help me.
Thanks again!
- Benj
- Matrix Staff
- Posts: 15312
- Joined: Mon Oct 16, 2006 10:48 am
- Location: Matrix TS Ltd
- Has thanked: 4803 times
- Been thanked: 4314 times
- Contact:
Re: MS Excel to something Flowcode understands..
Hello,
Converting Excel formulas into C formulas is probably not that straightforward.
I would tackle a function or formula at a time and test it well then you can build up a library of functionality either in C or directly in Flowcode.
Converting Excel formulas into C formulas is probably not that straightforward.
I would tackle a function or formula at a time and test it well then you can build up a library of functionality either in C or directly in Flowcode.
Regards Ben Rowland - MatrixTSL
Flowcode Product Page - Flowcode Help Wiki - Flowcode Examples - Flowcode Blog - Flowcode Course - My YouTube Channel
Flowcode Product Page - Flowcode Help Wiki - Flowcode Examples - Flowcode Blog - Flowcode Course - My YouTube Channel
-
- Posts: 502
- Joined: Wed Nov 07, 2007 6:51 pm
- Location: Antwerp Belgium
- Has thanked: 121 times
- Been thanked: 108 times
- Contact:
Re: MS Excel to something Flowcode understands..
Thank you BenJ but these are very long formulas.Benj wrote:Hello,
Converting Excel formulas into C formulas is probably not that straightforward.
I would tackle a function or formula at a time and test it well then you can build up a library of functionality either in C or directly in Flowcode.
The link Chipfryer27 send works, but this script only converts to C# (the syntax for C will be different I guess?).
To get to the point where I get the result, in Excel it takes 29 steps of very long trigonometry formulas.
One of these steps (in Excel looks like this:
Result=IF(Angle>85;0;IF(Angle>5;58,1/TAN(RADIANS(Angle))-0,07/POWER(TAN(RADIANS(Angle));3)+0,000086/POWER(TAN(RADIANS(Angle));5);IF(Angle>-0,575;1735+Angle*(-518,2+Angle*(103,4+Angle*(-12,79+Angle*0,711)));-20,772/TAN(RADIANS(Angle)))))/3600
So if I can get the conversion for all these steps via a script (or so) it would save me an enormous lot of work.
-
- Posts: 444
- Joined: Tue Apr 15, 2014 4:19 pm
- Location: Kajaani, Finland
- Has thanked: 345 times
- Been thanked: 227 times
- Contact:
Re: MS Excel to something Flowcode understands..
One problem with directly using excel inside a programming platform is that all language versions of excel are highly localized, like the "AND" is "JA" in finnish version, or "SQRT" is "NELIÖJUURI"...
Would be better to use something "pretty standard" like Octave or Matlab.
Would be better to use something "pretty standard" like Octave or Matlab.
Ill just keep the good work up!
-
- Valued Contributor
- Posts: 740
- Joined: Fri Jun 06, 2014 3:53 pm
- Has thanked: 185 times
- Been thanked: 204 times
- Contact:
Re: MS Excel to something Flowcode understands..
Hi MJU
Sorry for the late reply due to travel. I initially responded whilst stuck at an airport and didn't check my link. It had been a while since I was last on it and thought it did C as well as the other options.
I had need of something similar a while back and a colleague put me on to that link, but my needs were not as complex as yours appear to be.
I'm still traveling so I don't have access to links I tried, but if you do a search on github you should find something. I'm pretty certain there are tools that will convert to C on there.
Sorry I can't be of further help at present but once my travels are over, if you still need links I will try and dig them out.
Regards
Sorry for the late reply due to travel. I initially responded whilst stuck at an airport and didn't check my link. It had been a while since I was last on it and thought it did C as well as the other options.
I had need of something similar a while back and a colleague put me on to that link, but my needs were not as complex as yours appear to be.
I'm still traveling so I don't have access to links I tried, but if you do a search on github you should find something. I'm pretty certain there are tools that will convert to C on there.
Sorry I can't be of further help at present but once my travels are over, if you still need links I will try and dig them out.
Regards
-
- Posts: 502
- Joined: Wed Nov 07, 2007 6:51 pm
- Location: Antwerp Belgium
- Has thanked: 121 times
- Been thanked: 108 times
- Contact:
Re: MS Excel to something Flowcode understands..
Thank you but I've set my Excel to English so the link that Chipfryer send works for me.EtsDriver wrote:One problem with directly using excel inside a programming platform is that all language versions of excel are highly localized, like the "AND" is "JA" in finnish version, or "SQRT" is "NELIÖJUURI"...
Would be better to use something "pretty standard" like Octave or Matlab.
BTW: SQRT in my language is "vierkantswortel"..
-
- Posts: 502
- Joined: Wed Nov 07, 2007 6:51 pm
- Location: Antwerp Belgium
- Has thanked: 121 times
- Been thanked: 108 times
- Contact:
Re: MS Excel to something Flowcode understands..
No problem that the script doesn't create C anymore.chipfryer27 wrote:Hi MJU
Sorry for the late reply due to travel. I initially responded whilst stuck at an airport and didn't check my link. It had been a while since I was last on it and thought it did C as well as the other options.
I had need of something similar a while back and a colleague put me on to that link, but my needs were not as complex as yours appear to be.
I'm still traveling so I don't have access to links I tried, but if you do a search on github you should find something. I'm pretty certain there are tools that will convert to C on there.
Sorry I can't be of further help at present but once my travels are over, if you still need links I will try and dig them out.
Regards
I guess that if I want to get this project to work, it will take al lot of effort to get it working properly.
Or.. the people @Matrix should help us by creating such a conversion tool
-
- Valued Contributor
- Posts: 1191
- Joined: Wed May 31, 2017 11:57 am
- Has thanked: 70 times
- Been thanked: 440 times
- Contact:
Re: MS Excel to something Flowcode understands..
Slightly off topic - but maybe helpful...
It is fairly easy to read excel files from within python scripts - and to create Flowcode macros.
This very ugly bit of code will read a csv file and write dummy flowcode macro(s) to a FCM file which can be imported by FC8.
As it stands here - each macro(named here A1, A2 .. An) created just returns 123. (Note also that you might need to 'view' the macros for them to become visible) - and you'll need to alter the names of the files (2nd and 3rd lines - it would be easy to make the excel file (openpyxl works well) or the csv file as used here (I don't have excel on the machine I am using) and the output file name command line arguments.
As for parsing the Excel formula into C - can you give us some more examples (or better a sample excel file) - and I'll give it some thought....
Martin
It is fairly easy to read excel files from within python scripts - and to create Flowcode macros.
This very ugly bit of code will read a csv file and write dummy flowcode macro(s) to a FCM file which can be imported by FC8.
As it stands here - each macro(named here A1, A2 .. An) created just returns 123. (Note also that you might need to 'view' the macros for them to become visible) - and you'll need to alter the names of the files (2nd and 3rd lines - it would be easy to make the excel file (openpyxl works well) or the csv file as used here (I don't have excel on the machine I am using) and the output file name command line arguments.
Code: Select all
import csv
myf = open("C:\\Users\\mn_fi\\Documents\\test.csv") # Read a dummy csv file - number of lines is used
outp = open("c:\\Users\\mn_fi\\Documents\\test.fcm","w") # Output our dummy FCM file to here
outp.writelines("<root>\r \
<file schema='101' />\r \
<macros >\r")
n = 1 # For our dummy macro names
f = csv.reader(myf) # Read the csv
for i in f: # Process each line
outp.write("<macro>\r")
outp.write("<flowline name='" + "A" + str(n) + "' description='' > \r \
<return name='Return' type='u32' description='' isconst='0' isinit='0' usrinit='0' setinit='' />\r")
outp.write("<param name='abc' type='u32' description='' isconst='0' isinit='0' usrinit='0' setinit='' /> \r\
<command class_type='native' title='C Code' ccode='/* \
Enter C code below this comment\r \
*/\r\n")
outp.write ("FCR_RETVAL = 123;\r")
outp.write("' cmdcolor='3389439' cmdcolor_sec='3389439' language='C' />\r")
outp.write("</flowline>\r \
</macro>\r")
n+=1
outp.write("</macros>\r\
<variables />\r\
</root>\r")
outp.close()
As for parsing the Excel formula into C - can you give us some more examples (or better a sample excel file) - and I'll give it some thought....
Martin
-
- Valued Contributor
- Posts: 1191
- Joined: Wed May 31, 2017 11:57 am
- Has thanked: 70 times
- Been thanked: 440 times
- Contact:
Re: MS Excel to something Flowcode understands..
Python also has a way of parsing 'arbitary' code into a AST (Abstract Syntax Tree):
For example (note that you'll need Python 3 here)
Produces
It might be possible (it surely is) to convert this to C code....
Martin
For example (note that you'll need Python 3 here)
Code: Select all
import ast
eq = "a=1.2*pi*sin(1.2)"
t = ast.parse(eq)
print(ast.dump(t))
Which - assigns a target ('a') to 1.2 * (op = mult) 'pi' * (func) sin (args = ) 1.2Module(body=[Assign(targets=[Name(id='a', ctx=Store())], value=BinOp(left=BinOp(left=Num(n=1.2), op=Mult(), right=Name(id='pi', ctx=Load())), op=Mult(), right=Call(func=Name(id='sin', ctx=Load()), args=[Num(n=1.2)], keywords=[])))])
It might be possible (it surely is) to convert this to C code....
Martin
-
- Posts: 502
- Joined: Wed Nov 07, 2007 6:51 pm
- Location: Antwerp Belgium
- Has thanked: 121 times
- Been thanked: 108 times
- Contact:
Re: MS Excel to something Flowcode understands..
Hey Martin,mnf wrote:Slightly off topic - but maybe helpful...
It is fairly easy to read excel files from within python scripts - and to create Flowcode macros.
This very ugly bit of code will read a csv file and write dummy flowcode macro(s) to a FCM file which can be imported by FC8.
As it stands here - each macro(named here A1, A2 .. An) created just returns 123. (Note also that you might need to 'view' the macros for them to become visible) - and you'll need to alter the names of the files (2nd and 3rd lines - it would be easy to make the excel file (openpyxl works well) or the csv file as used here (I don't have excel on the machine I am using) and the output file name command line arguments.
Code: Select all
import csv myf = open("C:\\Users\\mn_fi\\Documents\\test.csv") # Read a dummy csv file - number of lines is used outp = open("c:\\Users\\mn_fi\\Documents\\test.fcm","w") # Output our dummy FCM file to here outp.writelines("<root>\r \ <file schema='101' />\r \ <macros >\r") n = 1 # For our dummy macro names f = csv.reader(myf) # Read the csv for i in f: # Process each line outp.write("<macro>\r") outp.write("<flowline name='" + "A" + str(n) + "' description='' > \r \ <return name='Return' type='u32' description='' isconst='0' isinit='0' usrinit='0' setinit='' />\r") outp.write("<param name='abc' type='u32' description='' isconst='0' isinit='0' usrinit='0' setinit='' /> \r\ <command class_type='native' title='C Code' ccode='/* \ Enter C code below this comment\r \ */\r\n") outp.write ("FCR_RETVAL = 123;\r") outp.write("' cmdcolor='3389439' cmdcolor_sec='3389439' language='C' />\r") outp.write("</flowline>\r \ </macro>\r") n+=1 outp.write("</macros>\r\ <variables />\r\ </root>\r") outp.close()
As for parsing the Excel formula into C - can you give us some more examples (or better a sample excel file) - and I'll give it some thought....
Martin
Thanks for the post, but I don't understand this.
I know about Python, but never used it. I'm not that into programming, more into hardware :-s
Is it possible to guide me trough the steps I must take to convert these formulas?
I have them in text and in a working Excel file.
-
- Valued Contributor
- Posts: 1191
- Joined: Wed May 31, 2017 11:57 am
- Has thanked: 70 times
- Been thanked: 440 times
- Contact:
Re: MS Excel to something Flowcode understands..
Looking at the example given above:
The main problem to converting the formula is the conditional (IF) parts of it:
However - I don't see how the
The main problem to converting the formula is the conditional (IF) parts of it:
- is hard to decode by 'hand' - So I would say (in pseudocode):Result=IF(Angle>85;0;IF(Angle>5;58,1/TAN(RADIANS(Angle))-0,07/POWER(TAN(RADIANS(Angle));3)+0,000086/POWER(TAN(RADIANS(Angle));5);IF(Angle>-0,575;1735+Angle*(-518,2+Angle*(103,4+Angle*(-12,79+Angle*0,711)));-20,772/TAN(RADIANS(Angle)))))/3600
Code: Select all
if(Angle > 85) return 0
else
if(Angle > 5) return 58
else
1/Tan(Radians(angle....
Code: Select all
IF(Angle >-0) (coloured red) is reached at all here? ie can you explain what this should parse to?
As I understand it (and I don't often have cause to use Excel) - the IF statement is IF(Condition; True Result; False result) - I suspect I don't know how the ',' (comma) operator is working here....
Some further thoughts -
The C math library uses radians - so we can just strip out all the calls to Radians converting Radians(Angle) to Angle.
sin, cos tan convert as is.
power converts to pow
How many formula are there to be converted? Would it be simpler to just rewrite the equations in C (<10) or are they machine generated and 100s or 1000s need converting?
What 'form' do they need to be in for Flowcode - ie in my previous post I aimed to convert each formula to a separate macro - or would they just be best converted to a file of 'C' statements?
-
- Posts: 502
- Joined: Wed Nov 07, 2007 6:51 pm
- Location: Antwerp Belgium
- Has thanked: 121 times
- Been thanked: 108 times
- Contact:
Re: MS Excel to something Flowcode understands..
mnf wrote:Looking at the example given above:
The main problem to converting the formula is the conditional (IF) parts of it:
- is hard to decode by 'hand' - So I would say (in pseudocode):Result=IF(Angle>85;0;IF(Angle>5;58,1/TAN(RADIANS(Angle))-0,07/POWER(TAN(RADIANS(Angle));3)+0,000086/POWER(TAN(RADIANS(Angle));5);IF(Angle>-0,575;1735+Angle*(-518,2+Angle*(103,4+Angle*(-12,79+Angle*0,711)));-20,772/TAN(RADIANS(Angle)))))/3600
However - I don't see how theCode: Select all
if(Angle > 85) return 0 else if(Angle > 5) return 58 else 1/Tan(Radians(angle....
Code: Select all
IF(Angle >-0) (coloured red) is reached at all here? ie can you explain what this should parse to? As I understand it (and I don't often have cause to use Excel) - the IF statement is IF(Condition; True Result; False result) - I suspect I don't know how the ',' (comma) operator is working here.... Some further thoughts - The C math library uses radians - so we can just strip out all the calls to Radians converting Radians(Angle) to Angle. sin, cos tan convert as is. power converts to pow How many formula are there to be converted? Would it be simpler to just rewrite the equations in C (<10) or are they machine generated and 100s or 1000s need converting? What 'form' do they need to be in for Flowcode - ie in my previous post I aimed to convert each formula to a separate macro - or would they just be best converted to a file of 'C' statements?[/quote] It's great that you dive into this question, but I really wanted to do the work myself. The whole result I'm looking for is build upon 27 equations that give the result needed. The one I chose was one of the longest in the Excel sheet. My initial idea was running all these separate formula's through a script and get a result that I could use in Flowcode. There is no need to have every separate step visible, as long as I can choose the two last ones to be displayed. The spreadsheet was found on a website from NOAA (https://www.noaa.gov/) and calculates movements on the sky. I'm not that smart (anymore) to convert them manually so in my mind it was a good idea to run them through a script. I didn't look into the formulas to find out how they were written, I just tested the spreadsheet and saw it was good. Maybe I should try and convert them by hand if I find enough time. And try to get them into the form that Flowcode uses instead of using C? The other issue is that the controller should be able to do trigonometry calculations. Thanks already fro your help.
-
- Posts: 502
- Joined: Wed Nov 07, 2007 6:51 pm
- Location: Antwerp Belgium
- Has thanked: 121 times
- Been thanked: 108 times
- Contact:
Re: MS Excel to something Flowcode understands..
Oh my, I am so wrong with this whole thing!
I didn't have the idea to first search if these calculations were already done in C..
I started by searching for something I know, and use (Excel), just to be sure that I could test the file.
But, just now I found out that I could start by searching for a C code from the start.
And... I found several versions for the thing I want to be computed.
So the question I asked should be completed here: no there is no easy way converting Excel to something Flowcode understand.
Thank you all for helping me.
My next question will be: "how can I use this C-code in Flowcode". But that will be in another post..
My apologies for not thinking about searching for C code first..
I didn't have the idea to first search if these calculations were already done in C..
I started by searching for something I know, and use (Excel), just to be sure that I could test the file.
But, just now I found out that I could start by searching for a C code from the start.
And... I found several versions for the thing I want to be computed.
So the question I asked should be completed here: no there is no easy way converting Excel to something Flowcode understand.
Thank you all for helping me.
My next question will be: "how can I use this C-code in Flowcode". But that will be in another post..
My apologies for not thinking about searching for C code first..