Is it possible to write output results into an Excel file?

[Raasoul_K_]

 I want to be able to open output files of a FORTRAN code in excel. If I name an output file in a FORTRAN code, as: ouput1.xls, then would it be a an excel file to open in Excel?

 If not, how can I do that?

 opening txt files in Excel is another option but it is not easy for formatted outputs.

[mecej4]

If I name an output file in a FORTRAN code, as: ouput1.xls, then would it be a an excel file to open in Excel?

No. Would naming a cat "dog" make it one? Giving a file an incorrect filename extension would, through file-type association, cause that file type to be opened by the specified application when the file is opened using the Windows GUI or the "start" command. However, if the contents of the file do not have the expected format, the application (e.g., Excel) may flag an error or behave incorrectly. You can easily write the output file as a tab (or comma or some other field separator) delimited file, which Excel can open properly. For example, the format string "(I8,3(1H,,I8))" would be suitable for writing four integers per line. Based on your numerous postings on rather elementary questions relating to Fortran, I think that you should spend an appropriate amount of time learning the language before attempting formatted output.

[Raasoul_K_]

Dear mecej4 Thanks for your reply. I will do comma separated output and then read it in Excel. One more question. It seams that FORTRAN accepts txt extension. Or if it is .dat, we can rename it to txt and open with notepad. BTW, I had learned the language but haven't used in new shape. But you are correct I have to learn the new shape. What/where do you recommend to look for to update my knowledge? (Preferably a site)

[bmchenry]

i would recommend simply formatting your output as comma delimited. When you create/open the file in your Fortran program have the file have a csv suffix. Then from Excel you File->Open and select 'Text File (txt,prn,csv' type. For csv files it will open as you'd expect the spreadsheet to open (no additional steps to import) For your Fortran program outputs, make your 1st/second lines the descriptor of each field (for clarity) And then write your data with a comma ',' between each filed for each line of data

[Steven_L_Intel1]

I will comment that the G0 edit descriptor and the * unlimited format group repeat count specifier, were explicitly added to the standard to address the desire to write CSV and similar types of files. These can make such code easier to write.

[Richard_H_2]

Steve, Enlighten me with an example

[Steven_L_Intel1]

Well, let's say you had a character value, an array of reals (of non-fixed size) and an integer. You could write them out comma-delimited as follows: [fortran] write (1,'(*(G0.4,:,","))') charval,realarray,intval [/fortran] The * means "repeat as many times as necessary" and G0.4 means use character, integer, real or logical editing, depending on the datatype, take up no more space than is necessary and for reals put out four fraction digits. Think about how you'd do this without those features. Extra credit if you know what the : does. (This is an old feature.)

[DavidWhite]

: = terminate the format if the input list is exhausted.

[DavidWhite]

Steve, Further to your example on the CSV file output, I guess there is no simple way to replace "," with the Tab character. I prefer Tab delimited text files rather than CSV. Regards, David

[IanH]

Perhaps [fortran]write (1,'(*(G0.4,:,"' // ACHAR(9) // '"))') charval,realarray,intval[/fortran] assuming the character expression suits your definition of simple. (Perhaps (?) this is marginal because tab isn't part of the Fortran character set, but I think you'd be doing well to find a compiler that didn't support that aspect.)

[DavidWhite]

Thanks Ian. I did not think I could concatenate the format strings within the Write Statement, otherwise I would have worked out something like what you have there. up until now, I have been creating my output string (with up to 100 tab delimited fields) by writing them into a character variable, then writing to my file. This gives me another way to look at doing this. thanks, David

[mecej4]

I did not think I could concatenate the format strings within the Write Statement

David, Note that a character variable may be used in place of a character constant for the format in a WRITE or READ statement. Likewise, one may build up a format string by (i) declaring a character variable for the string, (ii) doing an internal WRITE to that string (or manipulating string expressions as IanH showed) to produce the format, and (iii) following that by a WRITE to an external file using the newly built-up format string.

[John_Campbell]

A less sophistocated approach than Ian's could be: . character :: tab = char (9) character :: comma = ',' ! write (1, '(*(G0.4,:,a))' ) charval,tab, realarray,tab, intval . The use of tab is preferred over comma as this will always import into Excel, when using paste, while the use of , requires either opening the file as a .csv file or previously selecting , as a deliminiter in the "text to columns" option. I have spent some time in making sure my tab delimited .txt file has an appropriate layout for using the Ctl A, Ctl C, Ctl V sequence. I have had macros to do this, but it is usually simpler to use Notepad and Excel, as the requirements frequently change. . All this acknowledges that it is not easy to write directly to an Excel cell from Fortran. Although some packages are available to do this, they do not make it much easier!! . John

[IanH]

If SIZE(realarray) /= 1 then Excel will get somewhat confused with that approach of having "tab" in the item list. Another option for output to Excel is to write the data out in a HTML table. This has the advantage of making your data files impressively huge and unwieldy, with the downside that you spend hours trying to get the formatting just so. For those that spend much time moving data to and from Excel and some Fortran thing, there's also the option of talking to Excel "directly" via COM. Not for the faint-of-heart in many respects, but after some pain it certainly works-ok-for-me now in some applications. Lately I've settled on using a HDF5 file to store the results, with some VBA code and a Fortran helper DLL to let me pull the bits that I want into Excel so I can draw the necessary pretty charts.

[John_Campbell]

Ian, . Hopefully all the following options would work. My preference is for the third write format. There can be a problem with the length of the input string for pasteing into excel. . real*8 array(4,5) integer*4 i,j character :: tab = char(9) character*20 :: comment(5) ! forall (i=1:4, j=1:5) array(i,j) = i+j comment = (/ 'aaa', 'bbb', 'third', 'fourth line', 'last comment' /) ! write (*,'(*(g0.4,a,1x))') (( array(i,j),tab, i=1,4),j=1,5) write (*,'(4(g0.4,a,1x), a )') (( array(i,j),tab, i=1,4), trim(comment(j)), j=1,5) write (*,'(4(f0.4,a,1x), a )') (( array(i,j),tab, i=1,4), trim(comment(j)), j=1,5) end

[dboggs]

All of the solutions so far have dealt with the process of completely filling a blank Excel worksheet with data. This is actually fairly easy. More generally, we have need of writing to only a portion of an existing worksheet, or of reading the contents of [a portion of] a worksheet. And I don't mean by getting into Excel, export the contents to an ASCII file, and then read that. What we need is a direct interface to a .xls file based on the Excel object model.

Recognizing that IVF can't do this directly, I search for a 3rd-party solution. All I have come up with is a company called Canaimasoft.com, which appears to be defunct. Does anyone know of any other? Or is there a way to do it within IVF? I vaguely recall some discussion in this forum 2 - 3 years ago but it didn't seem very easy.

It's hard enough for Fortran to keep up in today's Language-of-the-month mentality, but in my field the .xls file has become the defacto standard for exchanging data with customers, so a good easy way of interfacing Fortran to Excel would go a long ways to supporting the future of Fortran.

[IanH]

You can use COM to talk to (read or write) parts of an Excel worksheet.  I'm pretty sure there are examples that come with IVF (I would have learnt from something like that), but its been a while since I've looked at those.  If you have a particular scenario that isn't covered by those/that examples, let me know and ankle-biters and model convergence permitting I'll see if I can put something together.

The current office file formats (xlsx etc) are just zipped file/directory structures of xml files (change the name of an .xlsx worksheet to .zip and open it in your favourite archive viewer to see what I mean).  Not sure I'd fancy trying to work with that directly for an existing file though.

[Arjen_Markus]

You can try the ODBC module in my FLibs project - http://flibs.sf.net This ought to give you read/write access to MS Excel files.

A problem is: how to find out what cells to write to. But I will that to you.

Regards,

Arjen

[Anthony_Richards]

dboggs wrote:
All of the solutions so far have dealt with the process of completely filling a blank Excel worksheet with data. This is actually fairly easy. More generally, we have need of writing to only a portion of an existing worksheet, or of reading the contents of [a portion of] a worksheet.

In my limited experience, if you have a Fortran program producing data that you want to display in Excel from within the Fortran code, including drawing a chart, it is a good idea to first program what you want to do in Visual Basic (Express, .NET), get it to work and then figure out how the sequence of required VB code operations can be reproduced using Fortran that makes use of the Excel module that can be created using the Fortran module wizard operating on the installed Microsoft Excel Object library. If it is a question of charting data, I find writing the data to a text file in fixed-width, comma or tab-delimited format is a good start. Then you can test the VB code using [csharp]        oXL.Workbooks.OpenText(Filename:="c:\yourfilenamehere.txt", Origin:=437, StartRow:=1, DataType:=XlTextParsingType.xlFixedWidth)[/csharp] then, defining your ranges, use the chart wizard to plot the data. Although OpenText causes a new workbook to be created and filled from cell A1, you can later move the data, if you so wish, to another workbook\worksheet, placing it in a more convenient location. This code can usually be reproduced using the code available in the Excel Fortran module, again it is best to do it in VB first, then you know exactly what the Fortran must reproduce.

[Paul_Curtis]

It is pretty straightforward within IVF programs to open an Excel worksheet and write content to defined cells, either one at a time or in cell ranges.  See the attached sample code.