|
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
Example 1. Writing data to an Excel spreadsheet
(NOTE: This example works only with f90SQL-pro)
A common situation for Fortran programmers is the need to put the results of numeric-intensive applications into a Microsoft Excel spreadsheet for graphic representation. In this example we will see how f90SQL can be use for this task.
Let's start by creating an Excel spreadsheet that will contain the numeric output of our Fortran program:
The ODBC driver for Excel, considers each spreadsheet to be a table, the columns and rows of the spreadsheet are the columns and rows of the table. Excel always adds a $ symbol to the spreadsheet names when accessed through the ODBC-API. So the name of the first table (spreadsheet) is Sheet1$. Let's add the labels X and Y to the first two columns:
These labels will be used by the Excel ODBC driver to identify the columns of the table. We will write the output of the Fortran program to these two columns. We also want to chart the output, so let's create a scatter plot and insert it into the page. The range for the scatter plot is from row 2 to row 25. These rows are currently empty so the scatter plot does not show anything:
After you have added and modified the chart to your taste, the spreadsheet should look similar to this::
The next step is to save the spreadsheet to a directory. Let's call the spreadsheet Sine.xls and save it to directory c:\temp.If you want to see the program in operation, do not close the spreadsheet, when you run the program you will see the new numbers being added to the cells (note, however that keeping the spreadsheet open will slow down the program, but it is fun to see the numbers and the chart being upgraded as the program progresses).
Now, we want to write the Fortran program that will generate and write the data for the spreadsheet. As you might have guessed, the program will generate the X,Y pairs of a sine function. This is the print out of the program:
program ExcelWrite
!This is an example of how to open and write data
!to an Excel workbook using f90SQL
!Program also demonstrates use of a direct file-connection (as opposed to a
!DSN connection) to access a datasource
!Note: User must provide an Excel workbook for this program to work
! see additional comments below
!Copyright 1998, Canaima Software
!Program computes a sin() function and writes the x,y pairs
!to Sheet1$ of workbook specified by the user. The program
!assumes that workbook has been prepared with two of the columns
!labeled X and Y.
!load f90SQL modules
use f90SQLConstants
use f90SQL
implicit none
double precision, parameter::pi=3.14159d0
integer(SQLUINTEGER_KIND),parameter::RowsetSize=10
integer(SQLINTEGER_KIND),parameter:: MaxStringLen=255
integer(SQLHENV_KIND):: EnvHndl
integer(SQLHDBC_KIND):: ConnHndl
integer(SQLHSTMT_KIND):: StmtHndl
integer(SQLRETURN_KIND)::iRet
integer(SQLSMALLINT_KIND)::ColNumber,i,ConnStrLength
double precision X,Y
character(len=MaxStringLen) fname,ConnStr,SQLStmtStr
print *,'Program ExcelWrite'
print *,'This program writes data to a Excel Worksheet'
print *,'Copy the file Sine.xls to any directory and use'
print *,'this copy instead of the original. The ODBC driver'
print *,'for Excel has limitations for updating/deleting'
print *,'data to a spreadsheet.'
print *,'If you want to see the data being updated in real'
print *,'time, you can open the file with Excel before you'
print *,'continue with this program'
print *,'Copyright 1998, Canaima Software'
print *,''
!Request from user the name and location of the excel file
print *, 'Enter name and path of the excel file (including file extension):'
read (*,*) fname
!allocate an environment handle
call f90SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, EnvHndl, iRet)
!Set ODBC version to use (3.x in this case)
call f90SQLSetEnvAttr(EnvHndl, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, iRet)
!Allocate a connection handle
call f90SQLAllocHandle(SQL_HANDLE_DBC,EnvHndl, ConnHndl, iRet)
!Create a connection string
!Note that we use a file-connection instead of a DSN connection
ConnStr='DBQ='//trim(fname)//';DRIVER={Microsoft Excel Driver (*.xls)}'
!open a connection to the excel workbook
call f90SQLDriverConnect(ConnHndl, f90SQL_NULL_PTR, ConnStr, ConnStr, &
ConnStrLength,SQL_DRIVER_COMPLETE,iRet)
if (iRet.eq.SQL_SUCCESS .or. iRet.eq. SQL_SUCCESS_WITH_INFO) then
!Set connection attributes to allow read/write (usually this is set before the
!connection is established, but there seems to be a bug in the excel ODBC driver
!that does not recognize this setting if done before connecting)
call f90SQLSetConnectAttr (ConnHndl, SQL_ATTR_ACCESS_MODE, &
SQL_MODE_READ_WRITE, iRet)
if (iRet.eq.SQL_SUCCESS .or. iRet.eq. SQL_SUCCESS_WITH_INFO) then
!Allocate a statement handle
call f90SQLAllocHandle(SQL_HANDLE_STMT,ConnHndl, StmtHndl, iRet)
!Create a parameterized INSERT SQL query
!one parameter for X and one for Y
SQLStmtStr='INSERT INTO [Sheet1$] (X,Y) VALUES (?,?)'
!Prepare the SQL query
call f90SQLPrepare(StmtHndl, SQLStmtStr, iRet)
!bind SQL statement parameters to fortran variables
ColNumber=1
call f90SQLBindParameter(StmtHndl, ColNumber, SQL_PARAM_INPUT, &
SQL_F_DOUBLE, SQL_DOUBLE, int(15,SQLUINTEGER_KIND), &
int(5,SQLSMALLINT_KIND), X, f90SQL_NULL_PTR, iRet)
ColNumber=ColNumber+1
call f90SQLBindParameter(StmtHndl, ColNumber, SQL_PARAM_INPUT, &
SQL_F_DOUBLE, SQL_DOUBLE, int(15,SQLUINTEGER_KIND), &
int(5,SQLSMALLINT_KIND), Y, f90SQL_NULL_PTR, iRet)
!loop to generate values of X and Y and insert them into workbook
X=0.0
do i=1,25
Y=sin(2.0*pi*X)
print *,x,y
call f90SQLExecute(StmtHndl,iRet)
!Check for error when adding rows
if (iRet.ne.SQL_SUCCESS .and. iRet.ne.SQL_SUCCESS_WITH_INFO) then
print *,'Error adding new records'
call ShowDiags(SQL_HANDLE_STMT,StmtHndl)
exit
endif
X=X+0.1D0
enddo
!release statement handle
call f90SQLFreeHandle(SQL_HANDLE_STMT,StmtHndl,iRet)
endif
!Free connection
call f90SQLDisconnect(ConnHndl,iRet)
else
print *,'Error opening connection to workbook'
call ShowDiags(SQL_HANDLE_DBC,ConnHndl)
endif
!release connection handle
call f90SQLFreeHandle(SQL_HANDLE_DBC,ConnHndl,iRet)
!release environment handle
call f90SQLFreeHandle(SQL_HANDLE_ENV, EnvHndl, iRet)
stop
end
|
Consult Chapters 4 and 5 of the f90SQL online manual if you do not completely understand any part of the previous program.
Now, we are ready to compile and link the program.
If you installed the f90SQL library and modules in their default directories (i.e. f90SQL.DLL in the windows system directory, f90SQL.LIB in the compiler's library directory and f90SQL.MOD, f90SQLConstants.mod and f90SQLStructures.mod in the compiler include directory) then open a command window in the directory where you saved the program and enter the following command:
If you are using Absoft's Pro-Fortran, the command is exactly the same, the output messages from the compiler will be different.
Now we are ready to run the program and see the results. Execute your program and enter the name and path of the spreadsheet you just created:
That's it. Check the spreadsheet, it should look similar to the following:
Download a PostScript version of this example
For questions regarding this site, send an e-mail to
webmaster@canaimasoft.com
