Creating a Microsoft Excel Spreadsheet Viewer Using Delphi 5's ADOExpress

Abstract: Scott Strool, of Inprise Corporation, shows how you can use Delphi 5's new ADO (ActiveX Data Objects) Express to access a Microsoft Excel spreadsheet.

Creating a Microsoft Excel Spreadsheet Viewer
Using Delphi 5's ADOExpress

by Scott Strool
Inprise Corporation

Delphi 5 now provides choices in how you can access your corporate data. Along with the Borland Database Engine and InterBase Express, you now can use a third data access model named ADOExpress. With ADOExpress you can now develop systems that access data in places where you might not have thought possible. ADOExpress is available in the Enterprise version of Delphi 5 and can be added to Delphi 5 Professional.

What is ADO?

ADO is Microsoft's high-level interface to all kinds of data. This application-level interface to Microsoft's data access technology is called OLE DB. OLE DB provides fast access to any data source, including relational and non-relational databases, email and file systems, text and graphics, and custom business objects.

Using ADOExpress

With Delphi's ADOExpress comes the power to access data that previously required lots of coding or the purchase of third party components. This article is a tutorial for using ADOExpress to provide an interface for Microsoft Excel worksheets. Using the ADOExpress components, you can build an application that can use an Excel file as the database. The columns of the spreadsheet become the fields and the rows, well they are still rows.

Using ADOExpress is similar to using the Data Access components that have been part of Delphi since 1.0. The three primary components used to access anything from ADO are the Connection, Command and DataSet components. These are installed on the ADO component tab.

Create a simple spreadsheet viewer using ADO.

1. Start Delphi and create a new application.

2. Drop an ADODataSet onto the form.

3. Set the CommandType property to cmdTableDirect

An ADODataSet is similar to the BDEDataSet used to access database tables. With ADO, you describe the data source and then use data-aware controls to display the data in your application. The first property to set is the CommandType. This property tells the ADO engine what type of command you are going to be calling.

4. Set the ConnectionString property.

The ConnectionString property tells ADO how to connect to the datasource. Click the ellipse to display the ConnectionString dialog. We are going to build the string ourselves so select Use Connection String and click the Build button to display the Data Link Properties dialog. The first choice to make is what data provider are we going to use to get the data. To access Excel we use the Microsoft Jet OLEDB 4.0 Provider, select it and click Next. The next piece is what are we going to connect to. In this case we want to access an Excel file. Use the Browse button and find an Excel file on your system or use Forecast.xls provided in the ADOExcel.zip file. Before we can test the connection we need to enter an Extended Property to the connection string. Click OK to go back the ConnectionString dialog and type 'Extended Properties=Excel 8.0;' without the quotes at the end of the connection string (remember, all ConnectionString entries must be separated by semi-colons. This tells ADO that we need Excel support and use the type library associated with Excel 8.0. Go ahead and click Build again and then Test the connection. If you get Test Connection Succeeded, you did everything right. Close the ConnectionString dialog. The last property to set is the CommandText property.

5. Set the CommandText property.

This property now becomes a combobox that allows you to select the tables defined in the datasource. Choose the name of the spreadsheet with a $ appended to it.

6. Drop a Datasource component from the Data Access tab and a DBGrid from the Data Controls tab.

Now we want to hook up the ADODataset to a grid for display. Connect the Grid to the datasource by setting the DataSource property to DataSource1 and connect the DataSource to the ADODataset by setting the DataSet property to ADODataSet1.

7. Set the Active property of the ADODataSet to True.

We can now test the query in design mode by activating the ADODataSet. The data from the spreadsheet will be displayed in the grid.