Board index » delphi » Connecting to Excel Data

Connecting to Excel Data

Does anyone know (with simple instructions at detail level) how to configure
the DataBase Explorer and its objects to get access (using Delphi tables,
datasource etc) to an Excel worksheet configured as a data table (ie columns as
fields, rows as records).

I have set up what I think is some sort of alias which appears in the drop-down
list of Database property of a table, but when I click on the TableName
property to enter or select it, then I get a user entry form asking for a
UserName and a Password. Where should these be nominated, and does one have to
have them ?

Thanks in anticipation.

Alan Lloyd
alangll...@aol.com

 

Re:Connecting to Excel Data


In article <20010104011705.12497.00000...@nso-fg.aol.com>, alangll...@aol.com

Quote
(AlanGLLoyd) writes:
>Does anyone know (with simple instructions at detail level) how to configure
>the DataBase Explorer and its objects to get access (using Delphi tables,
>datasource etc) to an Excel worksheet configured as a data table (ie columns
>as
>fields, rows as records).

I finally sussed this out, if anyone needs the info I have a set of
instructions to do this. e-mail me if you want them.

Alan Lloyd
alangll...@aol.com

Re:Connecting to Excel Data


Quote
AlanGLLoyd wrote:

> >datasource etc) to an Excel worksheet configured as a data table (ie columns

> I finally sussed this out, if anyone needs the info I have a set of
> instructions to do this. e-mail me if you want them.

Why not drop that info here? I mean, every week someone asks here
something about accessing Excel or Access data from Delphi.

Markku Nevalainen

Re:Connecting to Excel Data


Quote
In article <3A67EF4B.3...@iki.fi>, Markku Nevalainen <m...@iki.fi> writes:
>Why not drop that info here? I mean, every week someone asks here
>something about accessing Excel or Access data from Delphi.

As no-one replied to my original posting (and no-one said "Me too"), I reckoned
there was not much interest, especially for a long posting, but here it is . .
.

================================
Using Excel Spreadsheet Data in Delphi

In Excel

In your Spreadsheet, ensure that the first row of the data has field names in
it ie :-
ForeName    Surname    Ident                  < < < <
Alan        Lloyd      520504
Joe         Bloggs     398483

Note the range of the data from the top-left cell to the bottom-right cell
(includng the field-name headings). The above range would be  A1:C3  if it was
in the top left-hand corner of a worksheet.
Note the worksheet name,  the default Excel first sheet name would be  Sheet1

Open ODBC Data Source Administrator

Run 32bit ODBC from Control Panel, or ODBC Administrator from the Object menu
in Delphi Database Explorer (or ODBCAd32.exe from Windows/System folder if the
Control Panel or Database Explorer gives a GPF crash on shut-down) to open the
ODBC Data Source Administrator.
Select the System DSN tab, click on the Add button, select Microsoft Excel
Driver (*.xls) and click Finish.
Enter your data source name into the edit box. This can be any name you like to
call it. For this example use XLDB.
Select the version of Excel you are using.
Click on Select Workbook button and choose your Excel file.
Uncheck Read Only if you want to write to the spreadsheet.
Click OK in Select Workbook, OK in ODBC Microsoft Excel Setup, and OK in ODBC
Data Source Administrator.

Open a Form in Delphi IDE

Open / re-open Delphi since running ODBC Data Source Administrator (to update
data aliases).
From the Data Access tab of the component palette, place a Database on your
form. Use a Database (instead of allowing Delphi to use its default) to prevent
having to fill a login form with a blank username and password whenever you
open the Excel database. Set properties as follows.:-

Property      Set to     Comment
AliasName     XLDB       From drop-down list. Choose the name you entered in
                         ODBC DataSource Administrator
DatabaseName  XLDatabase For this example use XLDatabase, but you can use
                         any name you like, but it must be the same as
                         entered in TTable.DatabaseName.
LogonPrompt   False      From drop-down list.

Also from the Data Access tab of the component palette, place a Table on your
form. Set properties as follows :-

Property      Set to       Comment
DatabaseName  XLDatabase   From drop-down list. Must be same as entered in
                           TDatabase.DatabaseName.
TableName     Sheet1$A1:C3 The worksheet and range of your data. If the
                           worksheet is not stated Delphi will use the first
                           worksheet. Use  $  to separate worksheet and
                           range (not  !  as used in Excel)

Also from the Data Access tab of the component palette, place a DataSource on
your form. Set the DataSet property to Table1.

From the Data Controls tab of the component palette, place a DBGrid on your
form. Set the DataSource property to DataSource1.

Select the Table component on your form, and set its Active property to true
from the drop-down list. You should see your data in the DBGrid.

Now set the columns in the DBGrid by right-clicking in it and selecting Columns
Editor. Click the Add button as many times as you have columns.
For each column in the list, select it in Columns Editor, and set its
properties in the Object Inspector to . . .

Property    Set to      Comment
Alignment   As desired  From drop-down list. Aligns data in the column.
FieldName   As desired  Select the data field from the drop-down list.
Width       As desired  50 pixels for numbers and 100 for strings is a good
                        starting width.
Title       Double-click to show a list of Title properties, set these as . . .
  Alignment As desired  From drop-down list. Aligns the Title only
    Caption As desired  Defaults to FieldName but can be set to your choice.
       Font As desired  Defaults to the column font but can be set differently.

If you want to change the Table.DatabaseName or Table.Table, you have to set
Table.Active to false before changing, and to true after changing.

================================

Alan Lloyd
alangll...@aol.com

Re:Connecting to Excel Data


There's a freeware component, TExcel, in the DSP and other libraries, which
encapsulates quite well most of what you'd need to do with Excel - load and
execute macros, load and save workbooks, access cells (both read and write),
etc. Not the swiftest thing in the world, but for small jobs, works OK.

R.

Quote
Markku Nevalainen <m...@iki.fi> wrote in message news:3A67EF4B.37D5@iki.fi...
> AlanGLLoyd wrote:

> > >datasource etc) to an Excel worksheet configured as a data table (ie
columns

> > I finally sussed this out, if anyone needs the info I have a set of
> > instructions to do this. e-mail me if you want them.

> Why not drop that info here? I mean, every week someone asks here
> something about accessing Excel or Access data from Delphi.

> Markku Nevalainen

Re:Connecting to Excel Data


I've try to connect a excel database following your explanations.

Everything work, but until the time I have to choose the Tablename
property for the TTable object, the dropdown list contains nothing.
I have certainly created two worksheets in my Excel Workbook, so these
two must appears.

And when I try to see the data throught the database explorer, it
doesn't display anything.

Do you know what can be the problem ? Excel driver compatibility
problem ?

(I'm using Delphi5 Pro under Win2K)

In article <20010119075922.00445.00000...@nso-bj.aol.com>,
  alangll...@aol.com (AlanGLLoyd) wrote:

Quote
> In article <3A67EF4B.3...@iki.fi>, Markku Nevalainen <m...@iki.fi>
writes:

> >Why not drop that info here? I mean, every week someone asks here
> >something about accessing Excel or Access data from Delphi.

> As no-one replied to my original posting (and no-one said "Me too"),
I reckoned
> there was not much interest, especially for a long posting, but here
it is . .
> .

> ================================
> Using Excel Spreadsheet Data in Delphi

> In Excel

> In your Spreadsheet, ensure that the first row of the data has field
names in
> it ie :-
> ForeName    Surname    Ident                  < < < <
> Alan        Lloyd      520504
> Joe         Bloggs     398483

> Note the range of the data from the top-left cell to the bottom-right
cell
> (includng the field-name headings). The above range would be  A1:C3
if it was
> in the top left-hand corner of a worksheet.
> Note the worksheet name,  the default Excel first sheet name would
be  Sheet1

> Open ODBC Data Source Administrator

> Run 32bit ODBC from Control Panel, or ODBC Administrator from the
Object menu
> in Delphi Database Explorer (or ODBCAd32.exe from Windows/System
folder if the
> Control Panel or Database Explorer gives a GPF crash on shut-down) to
open the
> ODBC Data Source Administrator.
> Select the System DSN tab, click on the Add button, select Microsoft
Excel
> Driver (*.xls) and click Finish.
> Enter your data source name into the edit box. This can be any name
you like to
> call it. For this example use XLDB.
> Select the version of Excel you are using.
> Click on Select Workbook button and choose your Excel file.
> Uncheck Read Only if you want to write to the spreadsheet.
> Click OK in Select Workbook, OK in ODBC Microsoft Excel Setup, and OK
in ODBC
> Data Source Administrator.

> Open a Form in Delphi IDE

> Open / re-open Delphi since running ODBC Data Source Administrator
(to update
> data aliases).
> From the Data Access tab of the component palette, place a Database
on your
> form. Use a Database (instead of allowing Delphi to use its default)
to prevent
> having to fill a login form with a blank username and password
whenever you
> open the Excel database. Set properties as follows.:-

> Property      Set to     Comment
> AliasName     XLDB       From drop-down list. Choose the name you
entered in
>                          ODBC DataSource Administrator
> DatabaseName  XLDatabase For this example use XLDatabase, but you can
use
>                          any name you like, but it must be the same as
>                          entered in TTable.DatabaseName.
> LogonPrompt   False      From drop-down list.

> Also from the Data Access tab of the component palette, place a Table
on your
> form. Set properties as follows :-

> Property      Set to       Comment
> DatabaseName  XLDatabase   From drop-down list. Must be same as
entered in
>                            TDatabase.DatabaseName.
> TableName     Sheet1$A1:C3 The worksheet and range of your data. If
the
>                            worksheet is not stated Delphi will use
the first
>                            worksheet. Use  $  to separate worksheet
and
>                            range (not  !  as used in Excel)

> Also from the Data Access tab of the component palette, place a
DataSource on
> your form. Set the DataSet property to Table1.

> From the Data Controls tab of the component palette, place a DBGrid
on your
> form. Set the DataSource property to DataSource1.

> Select the Table component on your form, and set its Active property
to true
> from the drop-down list. You should see your data in the DBGrid.

> Now set the columns in the DBGrid by right-clicking in it and
selecting Columns
> Editor. Click the Add button as many times as you have columns.
> For each column in the list, select it in Columns Editor, and set its
> properties in the Object Inspector to . . .

> Property    Set to      Comment
> Alignment   As desired  From drop-down list. Aligns data in the
column.
> FieldName   As desired  Select the data field from the drop-down list.
> Width       As desired  50 pixels for numbers and 100 for strings is
a good
>                         starting width.
> Title       Double-click to show a list of Title properties, set
these as . . .
>   Alignment As desired  From drop-down list. Aligns the Title only
>     Caption As desired  Defaults to FieldName but can be set to your
choice.
>        Font As desired  Defaults to the column font but can be set
differently.

> If you want to change the Table.DatabaseName or Table.Table, you have
to set
> Table.Active to false before changing, and to true after changing.

> ================================

> Alan Lloyd
> alangll...@aol.com

Sent via Deja.com
http://www.deja.com/

Other Threads