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: 1884

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: 1520
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: 697
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: 1520
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: 1520
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: 1214
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
Notquitenewton



Joined: 25 May 2021
Posts: 20
Location: England, UK

PostPosted: Thu May 27, 2021 10:35 am    Post subject: Fortran and Excel Reply with quote

I've been importing txt files into Excel and vice versa for a long time now. Also, using the clipboard is sometimes useful and fast. Most of my programs use FTN95 for the number crunching and send the results to tab separated txt files which I can directly import into Excel with a short bit of VBA code.
I've only just joined the forum so your problems may already have been fixed but if you'd like some code to make FTN95 and Excel more communicative just let me know and I'll dig out some code which may help. As mentioned by another user on the forum, Excel VBA can be very fussy!!
You've probably done this by now anyway...
Notquitenewton
Back to top
View user's profile Send private message
wahorger



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

PostPosted: Fri May 28, 2021 12:57 pm    Post subject: Reply with quote

Notquitenewton, thanks for the offer. I have a set of routines that accommodate Excel's quirks nicely. That said, having an ODB or other interfaces would be something I would definitely be interested in.

And, Welcome Aboard! You'll find this forum a wonderful place to get hints and helps. I have found it immensely informing through these last several years.

Bill
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