|
forums.silverfrost.com Welcome to the Silverfrost forums
|
View previous topic :: View next topic |
Author |
Message |
dahowarduk
Joined: 24 Jun 2020 Posts: 11
|
Posted: Fri Jun 26, 2020 3:09 pm Post subject: FORTRAN & EXCEL |
|
|
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 |
|
|
mecej4
Joined: 31 Oct 2006 Posts: 1897
|
Posted: Fri Jun 26, 2020 3:40 pm Post subject: |
|
|
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 |
|
|
Kenneth_Smith
Joined: 18 May 2012 Posts: 726 Location: Hamilton, Lanarkshire, Scotland.
|
Posted: Fri Jun 26, 2020 4:20 pm Post subject: |
|
|
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 |
|
|
dahowarduk
Joined: 24 Jun 2020 Posts: 11
|
Posted: Sat Jun 27, 2020 6:59 pm Post subject: |
|
|
Got it working using the CSV file type. Thanks |
|
Back to top |
|
|
wahorger
Joined: 13 Oct 2014 Posts: 1227 Location: Morrison, CO, USA
|
Posted: Sun Jun 28, 2020 4:31 pm Post subject: |
|
|
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 |
|
|
Notquitenewton
Joined: 25 May 2021 Posts: 20 Location: England, UK
|
Posted: Thu May 27, 2021 10:35 am Post subject: Fortran and Excel |
|
|
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 |
|
|
wahorger
Joined: 13 Oct 2014 Posts: 1227 Location: Morrison, CO, USA
|
Posted: Fri May 28, 2021 12:57 pm Post subject: |
|
|
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 |
|
|
|
|
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
|