Jörg Kuthe
qtXLS
Instructions for the Use of the qtXLS Software
Revision date: 18th of April 2007
© Copyright Jörg Kuthe (QT software GmbH), Berlin, Germany, 2005-2007.
All rights reserved.
QT software GmbH
Konstanzer Str. 10
D-10707 Berlin
Germany
Telefon 030/9290087-0
Telefax 030/9290087-2
®
http://www.qtsoftware.de
eMail info@qtsoftware.de
n
Contents
1. Introduction ..................................................2
1.1 Functions and Restrictions..................................2
2. Short Survey of the qtXLS Routines ..............................4
2.1 Use of qtXLS in Your Programs .............................4
2.2 Structure of qtXLS Applications .............................5
3. Reference ....................................................9
3.1 The Fortran 90 MODULE qtXLS and the C Header File qtXLS.h ....9
3.2 The Fortran 90 MODULE qtXLSDeclarations ...................9
3.3 The C Header File qtXLS.h.................................11
3.4 Fundamentals to the Call of qtXLS Routines ..................15
3.4.1 Use of KINDs and Defined Types ......................................15
3.4.2 Names of Constants .................................................15
3.4.3 Naming of Routine Arguments ........................................15
3.4.4 Lengths of Names...................................................16
3.4.5 Zero-Terminated Strings .............................................16
3.4.6 Structures (TYPEs bzw. structURES) ....................................17
3.4.6.1 TYPE and struct qT_ColumnInfo ...............................17
3.4.6.2 TYPE and struct qT_SQLColumn ...............................17
3.4.6.3 TYPE and struct qT_TIMESTAMP_STRUCT .......................19
3.4.7 Error Codes and Error Handling........................................20
3.5 Description of the qtXLS Routines ..........................21
qtSetLicence_qtXLS - Set qtXLS License ................................21
qtXLSCloseEXCELFile - Close Excel File .................................21
qtXLSCreateEXCELFile - Create Excel File................................22
qtXLSCrea te Ta ble - Create Table.......................................23
qtXLSDo es Ta ble Na meE xist - Check if Table exists.........................24
qtXLSGet Co lum nIn fo - Get Co lum n Information...........................25
qtXLSGe tEr ror Mes sa ges - Ge t Er ror Mes sa ges ...........................28
qtXLSGet Nu mer ic Va lue - Get Numerical Value............................29
qtXLSGetszStrin gLength - Get Length of a zero terminated String ............29
qtXLSGet Ta ble Na mes - Get Table Names................................30
qtXLSGe tRowCount - Count Rows in a Table .............................32
qtXLSO pen EX CEL Fi le - Open Excel File .................................33
qtXLSRea dRows - Rea d Rows .........................................34
qtXLSSe tEr ror Le vel - Set Er ror Le vel....................................39
qtXLSSe tEr ror Mes sa ges Dis play - Set Error Display Modus..................40
qtXLSSet Li cen ce Path - Set Licence Path .................................40
qtXLSWri te Rows - Wri te Rows ........................................41
4. Compile & Link ...............................................47
4.1 General Notes...........................................47
With Absoft ProFortran for Windows ..........................................47
With Compaq Visual Fortran .................................................49
With Intel Visual Fortran.....................................................50
With Lahey/Fujitsu Fortran for Windows (LF95, v5.7)..............................51
With Microsoft Visual C++ ..................................................52
With Salford FTN95 or Silverfrost FTN95 (Win32) ................................53
5. Contents and Structure of the qtXLS Installation ..................55
6. Passing on of qtXLS Applications ...............................56
7. System Requirements .........................................56
8. Licence Agreement - Legal Conditions to Use the qtXLS Software ....56
9. Other Notes .................................................57
QT software GmbH, Berlin http://www.qtsoftware.de 1
n
1. Introduction
The qtXLS Library offers the programmer routines for reading and writing
Microsoft Excel formatted files. Their names usually end on .xls. qtXLS is
based on the ODBC drivers provided by Microsoft which are usually set up
to a PC automatically at the installation from Excel under Windows (cf.
illus.1). The existence of the Microsoft Excel ODBC drivers is one of the
prerequisites for the
functioning of the qtXLS
routines. If these drivers
aren't existing on a PC,
then they can be
provided by either
installation of Microsoft
Excel or the Microsoft
data Access
Components (MDAC).
The latter should be the
more economical
alternative since they can
be loaded by Microsoft's
web site free of charge.
One finds them the fastest with the help of the search function in the
"download center".
ð
http://www.microsoft.com/downloads
At the time of the writing of these operating instructions the MDAC could be
found inside the download category "Drivers". Provided that there aren't
license legal objections on the part of the manufacturer, you will find the
reference to the drivers also on the web page of QT software:
ð
http://www.qtsoftware.de/vertrieb/db/qtxls_e.htm
Since ODBC is the basis of qtXLS, qtXLS communicates with the Excel
ODBC driver about the ODBC functions integrated in Windows and there
with the help of the Structured Query Language (SQL). This is relevant for
the use of some of the qtXLS routines since they use the facilities of SQL.
n
1.1 Functions and Restrictions
With qtXLS routines you can
n
create files in the Excel file format,
n
create tables within these files,
n
write data to tables,
n
read data from tables and
n
obtain information about tables and columns.
Since qtXLS is based on the ODBC drivers of Microsoft, qtXLS is also
affected by their restrictions. The essential limitations are:
n
For reading Excel tables the names of the columns must be in the
first row of the table (cf. illus.2). When laying out tables by means of
qtXLS the tables are set up correspondingly so that this prerequisite is
filled, (i.e. tables which were produced with qtXLS are also thus
readable). Writing is then carried out in the following rows.
QT software GmbH, Berlin http://www.qtsoftware.de 2
Fig. 1: ODBC Mi cro soft Ex cel Dri ver
n
Writing into Excel tables
the new data are always
added. It is not possible to
write to a specific row in
the table.
n
It can be written only in
rows (not column wise).
n
The Excel data types
NUMBER, DATETIME
(date and time), TEXT,
CURRENCY and
LOGICAL only can be used. Formulas or other formats are not
supported.
n
Text formattings (font type, color etc.) are not possible.
n
Names of columns and tables can consist out of almost any valid Excel
characters. The blank characters (ASCII 32 or CHAR(32)) and the
exclamation mark (!) shouldn't be used. Also don’t use the dollar sign ($)
particularly in table names.
n
Names of columns and tables must not be identical with SQL keywords
(e.g. INSERT, TEXT, SELECT etc.).
n
Maximum length of column names: 63 characters
n
Maximum length of table names: 255 characters
n
The Excel ODBC drivers support the Excel versions 3.0 4.0, 5.0/7.0, 97,
2000 as well as later as far as they are compatible (this might be the case
well, in principle).
n
It can be possible that an Excel ODBC driver permits that only a limited
number or Excel files are open at the same time. It may also be possible
that an Excel ODBC driver allows only the access to one single file.
QT software GmbH, Berlin http://www.qtsoftware.de 3
Fig. 2: Ex cel tab le qtXLSDe mo Tab le (in
qtXLSDe mo3.xls, crea ted by qtXLSDe moWri te Tab le)
n
2. Short Survey of the qtXLS Routines
The qtXLS functions are in a Dynamic-Link-Library (DLL) named
qtXLS.dll. A following table lists the routines by name and groups them:
Function Group /
qtXLS Rou ti ne
Function
File Functions
qtXLSCrea teEX CEL Fi le Create Ex cel file
qtXLSO pen EX CEL Fi le Open Ex cel file
qtXLSClo seEX CEL Fi le Clo se Ex cel fi le
Ta ble Functions
qtXLSCrea te Ta ble Crea te table and de fi ne co lumns
qtXLSRea dRows Read table rows
qtXLSWri te Rows Wri te ta ble rows
In for ma tion Functions
qtXLSGet Ta ble Na mes Get ta ble na mes
qtXLSDo es Ta ble Na meE xist Check if ta ble exist
qtXLSGet Co lum nIn fo Get in for ma tion about co lumns
qtXLSGe tRowCount Count the num ber of rows in a table
qtXLSGet Nu mer ic Va lue Get the va lue of a “Nu me ric” type
Er ror Hand ling
qtXLSGe tEr ror Mes sa ges Get error mes sa ges
qtXLSSe tEr ror Le vel Con trol er ror hand ling
qtXLSSe tEr ror Mes sa ges Dis play Set up dis play of er ror mes sa ges
Ot her Functions
qtXLSGetszStrin gLength Get length of a null-terminated string
qtSet Li cen ce_qtXLS Aut ho ri ze use of qtXLS (see the file
qtXLSSet Li cen ce_0611_#####.f90)
qtXLSSet Li cen ce Path Set path for li cen se file
A detailed description of the qtXLS routines is in the chapter "Reference".
n
2.1 Use of qtXLS in Your Programs
To use the qtXLS routines in your programs a so-called binding is needed
that consists of an import library and additional files, e.g. Fortran 90
module files (end on .mod) or a pre-compiled C header file (qtXLS.h). In
the chapter "Compile & Link" these compiler specific files are discussed in
detail. Furthermore either a license file or a licence routine which is
delivered by QT software at the purchase of qtXLS is needed for the
unrestricted use of the qtXLS functions. The license file has the form
L####-######.lic (e.g. L0611-570739.lic)
QT software GmbH, Berlin http://www.qtsoftware.de 4