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

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.

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

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

character(len=30):: fmt ='(100(G12.4,''// ACHAR(9)// ''))'

Got it working using the CSV file type. Thanks

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!

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

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