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 

FORTRAN & EXCEL

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



Joined: 24 Jun 2020
Posts: 11

PostPosted: Fri Jun 26, 2020 3:09 pm    Post subject: FORTRAN & EXCEL Reply with quote

Can I import data from Excel and output the results to Excel?
If so, how (simple instructions only) do I do that?
Thanks
Back to top
View user's profile Send private message
mecej4



Joined: 31 Oct 2006
Posts: 1430

PostPosted: Fri Jun 26, 2020 3:40 pm    Post subject: Reply with quote

If you need to do this only a few times, and the items being exported and imported are numeric or simple character strings (i.e., letters and numbers only) the simplest way is to

1. Save the worksheet to a CSV file in Excel

2. read that CSV file and write a modified version of the data to another CSV file in your Fortran application.

3. Read the modified CSV file back into Excel.

For more elaborate requirements, you can write VBA code that Excel will use to call routines in a DLL compiled from Fortran sources. I neither like Excel nor have any need for it, so others will have to help you in this case.


Last edited by mecej4 on Fri Jun 26, 2020 5:24 pm; edited 1 time in total
Back to top
View user's profile Send private message
John-Silver



Joined: 30 Jul 2013
Posts: 1451
Location: Aerospace Valley

PostPosted: Fri Jun 26, 2020 3:52 pm    Post subject: Reply with quote

what's a modified ??? csv file ?
_________________
''Computers (HAL and MARVIN excepted) are incredibly rigid. They question nothing. Especially input data.Human beings are incredibly trusting of computers and don't check input data. Together cocking up even the simplest calculation ... Smile "
Back to top
View user's profile Send private message
Kenneth_Smith



Joined: 18 May 2012
Posts: 382
Location: Hamilton, Lanarkshire, Scotland.

PostPosted: Fri Jun 26, 2020 4:20 pm    Post subject: Reply with quote

In this case the G format edit descriptor is particularly useful. For example:-

Code:
integer, parameter :: dp = kind(1.d0)
integer i
real a, b
real(kind=dp) c
complex d
complex(kind=dp) e
character(len=17):: fmt = '(100(G12.4,","))'
i = 1
a = 1.0
b = huge(a)
c= huge(c)
d = cmplx(0.d0,1.d0)
e = cmplx(huge(1.d0),tiny(1.d0),kind=dp)
write(6,fmt) i
write(6,fmt) i, a, b
write(6,fmt) i, a, b, c
write(6,fmt) i, a, b, c, d
write(6,fmt) i, a, b, c, d, e
end


or

Code:
character(len=30):: fmt ='(100(G12.4,"'// ACHAR(9)// '"))'
Back to top
View user's profile Send private message Visit poster's website
John-Silver



Joined: 30 Jul 2013
Posts: 1451
Location: Aerospace Valley

PostPosted: Fri Jun 26, 2020 5:07 pm    Post subject: Reply with quote

there are 3rd party modules that you can find which (claim to be able to (who am i to argue)) .xls (x) files directly, using either ODBC API or via what are called COM commands.

All too titilatingly tricky for me.
_________________
''Computers (HAL and MARVIN excepted) are incredibly rigid. They question nothing. Especially input data.Human beings are incredibly trusting of computers and don't check input data. Together cocking up even the simplest calculation ... Smile "
Back to top
View user's profile Send private message
John-Silver



Joined: 30 Jul 2013
Posts: 1451
Location: Aerospace Valley

PostPosted: Fri Jun 26, 2020 5:13 pm    Post subject: Reply with quote

Ken,useful for what and why ???
_________________
''Computers (HAL and MARVIN excepted) are incredibly rigid. They question nothing. Especially input data.Human beings are incredibly trusting of computers and don't check input data. Together cocking up even the simplest calculation ... Smile "
Back to top
View user's profile Send private message
dahowarduk



Joined: 24 Jun 2020
Posts: 11

PostPosted: Sat Jun 27, 2020 6:59 pm    Post subject: Reply with quote

Got it working using the CSV file type. Thanks
Back to top
View user's profile Send private message
wahorger



Joined: 13 Oct 2014
Posts: 855
Location: Morrison, CO, USA

PostPosted: Sun Jun 28, 2020 4:31 pm    Post subject: Reply with quote

I've been dealing with the import and export of Excel compatible files in my commercial product for a few years. There are some blindingly stupid things Excel does as "standard". While CSV is a decent way to get blocks of numeric data into or out of a program, may the Deities have mercy on your soul if you want to include text!

If you want to export text data from your program to CSV, the only safe way is to surround the text with a construct like this:

='text_data'

Doing it this way will allow you to have commas in your data!

That said, if you then export these data from Excel to a CSV file, there is no surrounding construct. Which might be just fine in your application, but if your text data can contain a comma, you may not be able to parse the CSV output from Excel properly. One "trick is to import/export using the TAB character to separate your data fields. The option in FTN95 to read a file with TABS not converted to spaces is READ_TABS@(unitno). You can insert tabs into the output using CHAR(9).

Another problem are dates. In Excel, the date is shown, but if you export this to CSV, the formatting is lost, and you'll get a number. Because that is how Excel stores a date internally.

There are ways to "get around" these issues, but none are particularly satisfying.

If someone can create a set of routines to do the ODBC/COM interface to Excel, I'd love to see it!
Back to top
View user's profile Send private message Visit poster's website
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