Fortran output to pre-made Excel - is it possible? How? 2

[meddles]

Hello,

I know how to make an output to a tab separated or comma separated text file. What I would like to know is, is there a way to open a pre-formatted Excel file and write the data to it?

When I use the open command to open 'file.xls,' it indeed writes some things to the file. I get a strange error (in Excel) when attempting to open the file that tells me it is not the correct file type.

Is there a neat way to make a copy of an Excel template and write data into the Excel file, or is the best bet just writing to a CSV or TSV and then manually copying and pasting? I would really like it if I could just generate a new Excel from an original template each time I use my program.

Thank you

 

[xwb]

You can output your excel book in XML and just rename it as .xls.

Try this: open your workbook with multiple sheets and SaveAs a .xml file. Then rename the .xml to .xls. Open the renamed .xls file and your tabs will still be there.

So all you need to do is to use an XML template to write your file out.

 

[meddles]

Thanks

That's pretty helpful. I am about to investigate it further.

On another note. I was stuck earlier trying to use char(9) in my format statement. Any idea why it would not work? It kept simply printing char(9). I have followed other online examples but I think I missed something.

7778 FORMAT(A, CHAR(9))

and I get the error "Unknown Edit Descriptor C has been detected" from my Absoft 11.1 compiler.

 

[FJacq]

CHAR(9) has not to be included in a format : it has to be put in the write statement. Example :

      character(15) :: string='my_field'
      open(10,"test.txt")
      write(10,1000) string,char(9) ! the string followed by a tab
1000  format(2A)

François Jacq

 

[meddles]

Hah! I wish I had asked earlier then.

I can make the data append into a .XLS, but both char(9) "horizontal tab" and char(11) "vertical tab" enter hte same way. I guess I am going to have to just make my output into a text file and then drop it into a .XLS? I was really hoping I could make my program

1) make a copy of a .xls file and name it
2) write directly into specified columns/rows of the pre-formatted Excel (i.e. set up the graphs, etc. one time. Currently I use PLPlot 5.3.1 and have an output to the screen, but for passing the data around, Excel is easier

So
1) Any suggestions on how to specify where the text would go in Excel, or is the main option XML and TXT?

 

[xwb]

Try using markers, say some code like #UNITCOST or #UNITS. Look for them in the template, then write out the equivalent values on another sheet. Just copy line by line, looking for the #s and substituting the values you want on the output sheet.

For instance, if you wanted #UNITS to be replaced by 25.6, and you come across

<Row ss:Index="28">
<Cell ss:Index="7"><Data ss:Type="Number">#UNITS</Data></Cell>
</Row>

Just substitute it and output

<Row ss:Index="28">
<Cell ss:Index="7"><Data ss:Type="Number">25.6</Data></Cell>
</Row>

Easiest if you have a lookup table with values and substitute as you go along.