|
forums.silverfrost.com Welcome to the Silverfrost forums
|
View previous topic :: View next topic |
Author |
Message |
AndrewLoh
Joined: 05 Nov 2012 Posts: 4
|
Posted: Tue Nov 06, 2012 1:07 am Post subject: Link excel to program created by Plato IDE |
|
|
Hi. I am new to this forum and also to the world of Fortran.
I would like to ask if it is possible for me to link an excel file to a Fortran program? I would like to retrieve values from the excel file to plug it into the program.
Also, is it possible for me to plot a graph in excel using the Fortran program?
Pls advise. |
|
Back to top |
|
|
AndrewLoh
Joined: 05 Nov 2012 Posts: 4
|
Posted: Tue Nov 06, 2012 1:39 am Post subject: |
|
|
To add on to the previous post, I would like to ask if I need to create a subprogram to store all the excel data and to release the data to the main program?
Background info:
I am trying to create a program to estimate the population size of the world in the near future, say 2100. I have retrieved data from Census.gov for the population size estimate of every year till 2050 and stored the estimated values into an excel file.
I have read from other sites and understand that I need to save my excel file as a CSV file for the Fortran program to read it. What I would like the program to do is that when I key in a given year, say 2030, the program will retrieve the relevant data from the excel file.
An image of the excel file is as follows:
|
|
Back to top |
|
|
JohnCampbell
Joined: 16 Feb 2006 Posts: 2554 Location: Sydney
|
Posted: Tue Nov 06, 2012 2:00 am Post subject: |
|
|
Andrew,
I have not been able to easily program this interface, although packages do exist to be able to do what you want.
I find it much simpler to export the spreadsheet as a .csv or .prn text file and then read in the information.
.csv files can be read in as a line of text, searching for the, field delimiter and also the " text field identifier.
The following is a recent example of code I wrote for this purpose.
Code: | subroutine parse_record (n, record, fields, nf)
!
character record*(*), fields(100)*30
integer*4 n, nf, l, i, nc
logical text
!
fields = ' '
l = 1
nf = 0
text = .false.
do i = l+1,len(record)
!
! Is this the end of a text string
if (text) then
if (record(i:i) /= '"') cycle
text = .false.
cycle
end if
!
! Is this the start of a text string
if (record(i:i) == '"') then
text = .true.
cycle
end if
!
! Does this record end now
if (record(i:i) /= ',') cycle
!
nf = nf+1
nc = i-l-1
if (nc <= 0) then
fields(nf) = ' '
else if (record(l+1:l+1) == '"') then
fields(nf) = record(l+2:i-2)
else
fields(nf) = record(l+1:i-1)
end if
l = i
end do
!
if (len_trim (record) > l) write (13,*) ' record',n,nf,' does not terminate with a comma ',trim(record(l:))
end
integer*4 function get_integer_field ( field )
!
use vessel_info
character field*(*)
character full*30
!
integer*4 iostat
real*8 val
!
if (len_trim (field) > 12) then
write (*,*) 'integer field longer than 12 characters ', field
error_count = error_count + 1
end if
!
full = field
read (full,fmt='(bn,f12.0)',iostat=iostat) val
!
if (iostat /= 0) then
write (*,*) 'unable to get integer from ', field
val = -1
error_count = error_count + 1
end if
!
get_integer_field = nint (val)
end
real*8 function get_real_field ( field )
!
use vessel_info
character field*(*)
character full*30
!
integer*4 iostat
real*8 val
!
if (len_trim (field) > 12) then
write (*,*) 'real field longer than 12 characters ', field
error_count = error_count + 1
end if
!
full = field
read (full,fmt='(bn,f12.0)',iostat=iostat) val
!
if (iostat /= 0) then
write (*,*) 'unable to get real from ', field
val = -1
error_count = error_count + 1
end if
!
get_real_field = val
end
|
(vessel_info provides error_count)
Writing .csv files is also easy, appending "," to each numeric field written.
John |
|
Back to top |
|
|
AndrewLoh
Joined: 05 Nov 2012 Posts: 4
|
Posted: Tue Nov 06, 2012 4:54 pm Post subject: |
|
|
Thanks for the information, but I don't seem to get it. I am totally a newbie in this field, but I am trying my best to learn it.
To make things easier, I have calculated the 'population size' and 'growth rate' by hand and stored them into the excel file. I am intending to use the program to retrieve the 'population size' and 'growth rate' from the excel file for 'a given year' that is keyed in by the user in the program and print the results.
How can I do this? |
|
Back to top |
|
|
AndrewLoh
Joined: 05 Nov 2012 Posts: 4
|
Posted: Wed Nov 07, 2012 1:05 am Post subject: |
|
|
Another question that I have is that how do I create an excel file and let the DO loop calculate a few values for the variables to be stored into the excel file?
My program is something like this:
!PROMPT AND READ VALUES OF RADIUS STARTING FROM 4.0 METRES TO 5.0 METRES
PRINT *, "ENTER RADIUS OF CYLINDER/CONE"
READ *, R
OPEN (5, file = "C:\Users\Wei Hao\Desktop\BOOK2.XLS",STATUS = 'NEW')
DO R = 4,5,0.010
!CALCULATE VOLUME OF CONE
VOLUME_CONE = ((Pi/3)*R**3)
PRINT *, "VOLUME_CONE", VOLUME_CONE
!CALCULATE VOLUME OF CYLINDER
VOLUME_CYLINDER = (500 - VOLUME_CONE)
PRINT *, "VOLUME_CYLINDER", VOLUME_CYLINDER
!CALCULATE HEIGHT OF THE CYLINDER
HEIGHT = (VOLUME_CYLINDER)/(Pi*R**2)
PRINT *, "HEIGHT", HEIGHT
!CALCULATE SURFACE AREA OF CYLINDER
AREA_CYLINDER = (Pi*HEIGHT*R*2.0) + (Pi*R**2.0)
PRINT *, "AREA_CYLINDER", AREA_CYLINDER
!CALCULATE SURFACE AREA OF CONE
AREA_CONE = (Pi*(SQRT(2.0))*R**2.0)
PRINT *, "AREA_CONE", AREA_CONE
!CALCULATE COST OF BUILDING CYLINDER
COST_CYLINDER = AREA_CYLINDER * 300
PRINT *, "COST_CYLINDER", COST_CYLINDER
!CALCULATE COST OF BUILDING CONE
COST_CONE = AREA_CONE * 400
PRINT *, "COST_CONE", COST_CONE
!CALCULATE COST OF BUILDING TANK
COST_TANK = COST_CONE + COST_CYLINDER
PRINT *, "COST_TANK", "($)",COST_TANK
WRITE (5,*) "VOLUME_CONE", VOLUME_CONE
WRITE (5,*) "VOLUME_CYLINDER", VOLUME_CYLINDER
WRITE (5,*) "HEIGHT", HEIGHT
WRITE (5,*) "AREA_CYLINDER", AREA_CYLINDER
WRITE (5,*) "AREA_CONE", AREA_CONE
WRITE (5,*) "COST_CYLINDER", COST_CYLINDER
WRITE (5,*) "COST_CONE", COST_CONE
WRITE (5,*) "COST_TANK", "($)",COST_TANK
CLOSE (5) |
|
Back to top |
|
|
jalih
Joined: 30 Jul 2012 Posts: 196
|
Posted: Mon Feb 11, 2013 4:13 pm Post subject: Re: |
|
|
AndrewLoh wrote: | Another question that I have is that how do I create an excel file and let the DO loop calculate a few values for the variables to be stored into the excel file? |
I have recently been working on this. My goal is to create a simple FTN95 callable DLL with a set of functions using COM object model to allow easy interaction between FTN95 and Excel.
At first, I will probably only support basic functionality: reading and writing Excel files, transferring values between FTN95 and Excel. |
|
Back to top |
|
|
JohnCampbell
Joined: 16 Feb 2006 Posts: 2554 Location: Sydney
|
Posted: Mon Feb 11, 2013 9:52 pm Post subject: |
|
|
Even the ability to read and write to a sheet/cell address would be a significant achievement. ( for a .xls file structure)
It is a functionality that is not readily or easily available.
I look forward to your updates.
John |
|
Back to top |
|
|
jalih
Joined: 30 Jul 2012 Posts: 196
|
Posted: Tue Feb 12, 2013 8:39 pm Post subject: Re: |
|
|
JohnCampbell wrote: | Even the ability to read and write to a sheet/cell address would be a significant achievement. |
Currently working:
- Init 'Excel.Application' COM-object
- Show/hide Excel
- Add workbooks
- Add worksheets
- Close workbook
- Close all workbooks
- Close worksheet
- Open workbook from file
- Set active workbook
- Set active worksheet
- Set cell value
- Get cell value
- Clean up and release COM-stuff
I will try to add some more basic functionality, write fortran module for interfacing the DLL and then put it available for download. |
|
Back to top |
|
|
JohnCampbell
Joined: 16 Feb 2006 Posts: 2554 Location: Sydney
|
Posted: Wed Feb 13, 2013 12:44 am Post subject: |
|
|
If you can read and write a cell value, then that is a significant achievement.
How do you specify the format of the information you are transfering ? Is it a character string, such as "1.2e4" or "11/02/2013", or does it support binary formats ? Don't mis-understand, anything is better than the nothing we have at the moment.
One of the problems with old implementations (which I never sucessfullly used) was with the initiatialising the file access. Do you do that automatically, as in specifying a file to open ?
I will lok forward to any further updates.
John |
|
Back to top |
|
|
jalih
Joined: 30 Jul 2012 Posts: 196
|
Posted: Wed Feb 13, 2013 7:57 am Post subject: Re: |
|
|
JohnCampbell wrote: |
How do you specify the format of the information you are transfering ? Is it a character string, such as "1.2e4" or "11/02/2013", or does it support binary formats ?
|
I support some basic data types and do my own a little simplified handling of variant data. Basically, it tries to use the data type you pass as a parameter for a function. If you pass character string for a function, it uses string and if you pass double, it uses double.
Quote: |
One of the problems with old implementations (which I never sucessfullly used) was with the initiatialising the file access. Do you do that automatically, as in specifying a file to open ?
|
I use COM-object for controlling Excel and let it do the work. Opening a XLS file is just a matter of calling 'Workbooks.Open' method. |
|
Back to top |
|
|
JohnCampbell
Joined: 16 Feb 2006 Posts: 2554 Location: Sydney
|
Posted: Wed Feb 13, 2013 9:34 am Post subject: |
|
|
This looks very interesting, as just being able to read and write cells opens the door for automated access between ftn95 and excel.
I assume you are using .xls files, or do you also access .xlsx ?
Keen to see a beta release.
John |
|
Back to top |
|
|
jalih
Joined: 30 Jul 2012 Posts: 196
|
Posted: Thu Feb 14, 2013 5:08 pm Post subject: Re: |
|
|
Quote: |
I assume you are using .xls files, or do you also access .xlsx ?
|
As Excel is really doing all the work, I guess it should be supported.
I try to put the test version of the DLL available in a couple of weeks. |
|
Back to top |
|
|
jalih
Joined: 30 Jul 2012 Posts: 196
|
Posted: Wed Feb 20, 2013 10:06 am Post subject: |
|
|
I have made some progress and have put my DLL with a simple FTN95 demo project available here
I currently have some problems with handling Fortran character strings in my library, so getting the cell values as character strings is currently not supported but I am working on it.
Demo shows how to init Excel COM-object, add and save workbooks, add sheets, name sheets, activate sheets, set cell values (int, double and string values are currently supported), get cell values (int and double values are currently supported) and how to release Excel COM-object.
There are also some more procedures defined in the source and ready to be used.
Have fun! |
|
Back to top |
|
|
jalih
Joined: 30 Jul 2012 Posts: 196
|
Posted: Fri Feb 22, 2013 10:32 am Post subject: Re: |
|
|
jalih wrote: |
I currently have some problems with handling Fortran character strings in my library, so getting the cell values as character strings is currently not supported but I am working on it.
|
This problem is fixed now. I have updated the demo project available here to show some more functionality.
As I personally don't have Excel installed, I would appreciate some feedback whether it's working as it should. |
|
Back to top |
|
|
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|