f90SQL - Example 2

Example 2. Writing data to an Excel spreadsheet

(Note: This is the same Example 1, but adapted to work with f90SQL-lite)

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).

Because f90SQL-lite can only access data that has been registered as a DSN, we need to open the ODBC Driver Manager and create a DSN for the spreadsheet. To do this, go to Window's Control Panel and double click the ODBC Administrator:

ODBCAdmin.jpg (41040 bytes)

In the ODBC Administrator, open the tab System DSN and click on the Add button:

add.jpg (30107 bytes)

And create a new DSN using the Excel ODBC Drivers. Call it "Sine". Click on the Select Workbook button, and select Sine.xls. Make sure you uncheck the Readonly option, otherwise the program will not be able to write new data to the spreadsheet:

NewExcelDSN.jpg (21235 bytes)

Click the OK button and close the ODBC Administrator.

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

!Version of original ExcelWrite adapted to work with f90SQL-lite

!This is an example of how to open and write data
!to an Excel workbook using f90SQL
!Notes:
!1) User must provide an Excel workbook for this program to work
!2) f90SQL-lite only supports connections through DSN, so user
! must create a DSN to the workbook using the ODBC administrator
!3) Make sure the DSN entry is write-enabled. To do this, click the
! options button in the DSN configuration window (ODBC Administrator),
! and un-check the ReadOnly entry.

!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
double precision X,Y
character(len=MaxStringLen) DSNname,SQLStmtStr,UsrName,UsrPsswrd

!Request from user the name of DSN to excel file
print *, 'Enter name the Data Source Name (DSN) that points to the Excel Workbook:'
read (*,*) DSNname
print *,'Enter your user name:'
read (*,*) UsrName
print *,'Enter your password (WARNING: password will show on the screen):'
read (*,*) UsrPsswrd

!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)

!open a connection to the excel workbook
call f90SQLConnect(ConnHndl, trim(DSNname),trim(UsrName),trim(UsrPsswrd),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) 
!Allocate a statement handle
call f90SQLAllocHandle(SQL_HANDLE_STMT,ConnHndl, StmtHndl, 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)
write(SQLStmtStr,*) 'INSERT INTO [Sheet1$] (X,Y) VALUES ({fn CONVERT(', &
                    X ,',SQL_DOUBLE)},{fn CONVERT(', Y ,',SQL_DOUBLE)})'
print *,trim(SQLStmtStr)
call f90SQLExecDirect(StmtHndl,trim(SQLStmtStr),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)

!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

Example2.zip