forums.silverfrost.com Forum Index forums.silverfrost.com
Welcome to the Silverfrost forums
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Link excel to program created by Plato IDE

 
Post new topic   Reply to topic    forums.silverfrost.com Forum Index -> General
View previous topic :: View next topic  
Author Message
AndrewLoh



Joined: 05 Nov 2012
Posts: 4

PostPosted: Tue Nov 06, 2012 1:07 am    Post subject: Link excel to program created by Plato IDE Reply with quote

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
View user's profile Send private message
AndrewLoh



Joined: 05 Nov 2012
Posts: 4

PostPosted: Tue Nov 06, 2012 1:39 am    Post subject: Reply with quote

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
View user's profile Send private message
JohnCampbell



Joined: 16 Feb 2006
Posts: 2554
Location: Sydney

PostPosted: Tue Nov 06, 2012 2:00 am    Post subject: Reply with quote

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
View user's profile Send private message
AndrewLoh



Joined: 05 Nov 2012
Posts: 4

PostPosted: Tue Nov 06, 2012 4:54 pm    Post subject: Reply with quote

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
View user's profile Send private message
AndrewLoh



Joined: 05 Nov 2012
Posts: 4

PostPosted: Wed Nov 07, 2012 1:05 am    Post subject: Reply with quote

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
View user's profile Send private message
jalih



Joined: 30 Jul 2012
Posts: 196

PostPosted: Mon Feb 11, 2013 4:13 pm    Post subject: Re: Reply with quote

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
View user's profile Send private message
JohnCampbell



Joined: 16 Feb 2006
Posts: 2554
Location: Sydney

PostPosted: Mon Feb 11, 2013 9:52 pm    Post subject: Reply with quote

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
View user's profile Send private message
jalih



Joined: 30 Jul 2012
Posts: 196

PostPosted: Tue Feb 12, 2013 8:39 pm    Post subject: Re: Reply with quote

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
View user's profile Send private message
JohnCampbell



Joined: 16 Feb 2006
Posts: 2554
Location: Sydney

PostPosted: Wed Feb 13, 2013 12:44 am    Post subject: Reply with quote

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
View user's profile Send private message
jalih



Joined: 30 Jul 2012
Posts: 196

PostPosted: Wed Feb 13, 2013 7:57 am    Post subject: Re: Reply with quote

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
View user's profile Send private message
JohnCampbell



Joined: 16 Feb 2006
Posts: 2554
Location: Sydney

PostPosted: Wed Feb 13, 2013 9:34 am    Post subject: Reply with quote

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
View user's profile Send private message
jalih



Joined: 30 Jul 2012
Posts: 196

PostPosted: Thu Feb 14, 2013 5:08 pm    Post subject: Re: Reply with quote

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
View user's profile Send private message
jalih



Joined: 30 Jul 2012
Posts: 196

PostPosted: Wed Feb 20, 2013 10:06 am    Post subject: Reply with quote

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
View user's profile Send private message
jalih



Joined: 30 Jul 2012
Posts: 196

PostPosted: Fri Feb 22, 2013 10:32 am    Post subject: Re: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    forums.silverfrost.com Forum Index -> General All times are GMT + 1 Hour
Page 1 of 1

 
Jump to:  
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