MS Excel to something Flowcode understands..

For Flowcode users to discuss projects, flowcharts, and any other issues related to Flowcode 8.

Moderator: Benj

Post Reply
MJU
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..

Post by MJU »

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?

chipfryer27
Valued Contributor
Valued Contributor
Posts: 618
Joined: Fri Jun 06, 2014 3:53 pm
Has thanked: 184 times
Been thanked: 195 times
Contact:

Re: MS Excel to something Flowcode understands..

Post by chipfryer27 »

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

MJU
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..

Post by MJU »

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
Looks promising, thank you Chipfryer27.
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!

User avatar
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..

Post by Benj »

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.

MJU
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..

Post by MJU »

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.
Thank you BenJ but these are very long formulas.
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.

EtsDriver
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..

Post by EtsDriver »

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.
Ill just keep the good work up!

chipfryer27
Valued Contributor
Valued Contributor
Posts: 618
Joined: Fri Jun 06, 2014 3:53 pm
Has thanked: 184 times
Been thanked: 195 times
Contact:

Re: MS Excel to something Flowcode understands..

Post by chipfryer27 »

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

MJU
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..

Post by MJU »

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.
Thank you but I've set my Excel to English so the link that Chipfryer send works for me.

BTW: SQRT in my language is "vierkantswortel"..

MJU
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..

Post by MJU »

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
No problem that the script doesn't create C anymore.
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 :-)

mnf
Valued Contributor
Valued Contributor
Posts: 1188
Joined: Wed May 31, 2017 11:57 am
Has thanked: 70 times
Been thanked: 439 times
Contact:

Re: MS Excel to something Flowcode understands..

Post by mnf »

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

mnf
Valued Contributor
Valued Contributor
Posts: 1188
Joined: Wed May 31, 2017 11:57 am
Has thanked: 70 times
Been thanked: 439 times
Contact:

Re: MS Excel to something Flowcode understands..

Post by mnf »

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)

Code: Select all

import ast

eq = "a=1.2*pi*sin(1.2)"
t = ast.parse(eq)
print(ast.dump(t))
Produces
Module(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=[])))])
Which - assigns a target ('a') to 1.2 * (op = mult) 'pi' * (func) sin (args = ) 1.2

It might be possible (it surely is) to convert this to C code....

Martin

MJU
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..

Post by MJU »

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
Hey 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.

mnf
Valued Contributor
Valued Contributor
Posts: 1188
Joined: Wed May 31, 2017 11:57 am
Has thanked: 70 times
Been thanked: 439 times
Contact:

Re: MS Excel to something Flowcode understands..

Post by mnf »

Looking at the example given above:

The main problem to converting the formula is the conditional (IF) parts of it:
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
- is hard to decode by 'hand' - So I would say (in pseudocode):

Code: Select all

	if(Angle > 85) return 0
	else
		if(Angle > 5) return 58
		else 
			1/Tan(Radians(angle....
However - I don't see how the

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?

MJU
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..

Post by MJU »

mnf wrote:Looking at the example given above:

The main problem to converting the formula is the conditional (IF) parts of it:
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
- is hard to decode by 'hand' - So I would say (in pseudocode):

Code: Select all

	if(Angle > 85) return 0
	else
		if(Angle > 5) return 58
		else 
			1/Tan(Radians(angle....
However - I don't see how the

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.

MJU
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..

Post by MJU »

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.. :oops:

Post Reply