Use ADO to access Excel data files directly in Delphi (Favorites)

Use ADO in Delphi to access Excel data files directly 2004-5-15 2:17:20 (Article Category: Delphi)

The support of the prawn of big fish, finally. Now write the heart, let the later people improve the efficiency of the cultivation, and become a big fish in the early days. To access the Excel data file with the ADO series control in Delphi, you should first create some basic concepts. You need to imagine an Excel data file as a relational database, each of the worksheets in the Excel file is a list of tables in the database, and each column in the worksheet is of course the list. Then, it can be performed according to the secrets below. 1. Set the adoConnection ConnectionsTRINGTSTRING to construct Connectionstring, the OLE DB provider wants to select Microsoft Jet 4.0 Ole DB Provider as the ADO driver. This is originally used to connect the Access database driver, but you can also open the Excel file.  Connected database name is of course the Excel file you want to open, pay attention to the extension * .xls, not * .mdb.  The most critical point is that the extended property extended profment is "Excel 8.0", otherwise, the error will not be identified when the test connection is reported. The properties of Extended Properties are entered in the "All" parameter pages.  Finally, the parameters in the Connectionstring after the settings are: Provider = microsoft.jet.oledb.4.0data Source = myexcelfile.xlsextended Properties = Excel 8.0Persist security info = false

2. Set the AdoDataSet or Adotable  Connect the adodataset or adotable to the AdoConnection. If you don't have to use Adoconnection, you can also use the above method to directly set the adodataset or adotable ConnectionsTRING attribute.  For Adodataset, you need to set the CommandType property to cmdtableDirect, and for adotable, set the TableDirect property to true. Because accessing the Excel file is a direct data file access, not through the SQL statement to operate the cursor access. If you do not set directly, the system reports information such as the SQL statement format.  Then, when you pull down the CommandText property in the AdoDataSet, you can choose to open the work table you want to open when you drop down the CommandText property in the AdodaTaset. Note that there is more a $ symbol behind the table name. 3. Open adodataset or adotable