Use Aadoconnection to connect excel in Delphi7 today

today in Delphi using ADO to connect Excel (previously read in a book, review the-_-!!), some gains, dare not exclusive. The first part: 1. Set the ADOConnection ConnectionString property of the OLE DB provider to select Microsoft Jet 4.0 OLE Dbprovider (which was originally used to connect to the Access database driver, But you can also open the Excel file), press "Next" button 2. Select the database name. Note: Excel's extension is *.xls, and the default file type is Microsoft Access database (*.mdb), we choose "All files (* *)" bar, then select the Excel file we want to connect!! If it is in the current path, fill in the 2007.xls directly. Just don't panic. Press the test Connection button, or the error dialog box appears. You can try it and see ^_^ 3. In the All tab, locate Extended properties, double-click it, or press the Edit Value button to set the Extended properties to Excel 8.0. 4. We return to the "Connection" tab and press the "Test Connection" button. Test connection Success!!! ^_^ Finally, the parameters in the ConnectionString after Setup are:

--2003 and version
' Provider=Microsoft.Jet.OLEDB.4.0;Data source=%s; Extended Properties=excel 8.0 '
--2007
' Provider=microsoft.ace.oledb.12.0;data source=%s; Extended properties= Excel 12.0; Persist Security info=true ';

%s represents the path to the file

Persist Security info=false Part II: Then use Adotable,adodataset and adoquery to connect to just the adoconnection, of course you can set adotable directly, The ConnectionString property of the Adodataset or adoquery. 1. Adotable settings: A. Adotable TableName Property Value: The system automatically generates Excel sheet1$,sheet2$ and sheet3$. Direct active is set to true, and the system reports information such as malformed SQL statements. B. Locate the TableDirect property, set to True. Because access to an Excel file is direct data file access, it is not accessed through SQL statements that manipulate cursors. Then set the active to true. It worked!! ^_^ C. You can change the sheet1$,sheet2$ and sheet3$ in the TableName property to [sheet1$],[sheet2$] and [sheet3$] without using the TableDirect property. Yes, just add "[]" on the line. 2. Adodataset settings: A. Just set the CommandType property to Cmdtabledirect, select the worksheet, and the active setting to true succeeds. B. You can also set the CommandType property to Cmdtable and change the CommandText property to [sheet1$],[sheet2$] and [sheet3$]. 3. Adoquery settings: SQL properties are as follows:

SELECT * FROM [sheet1$] Summary: Review previous studies, try a new connection method, beneficial to deepen memory. I find that the name of the worksheet is appended with a $ sign, and after opening Adotable,adodataset or adoquery, their cursortype automatically becomes ctstatic.


Get the Excel version

[Delphi]

2. {
4. Const
6. Wordversion97 = 8;
8. Wordversion2000 = 9;
10. WORDVERSIONXP = 10;
12. Wordversion2003 = 11;
14. Wordversion2007 = 12;
16. }
20. function Getinstalledwordversion:integer;
22. Var
24. Excel:olevariant;
26. Begin
28. Try
30. Excel:=createoleobject (' Excel.Application ');
32. Except
34. ShowMessage (' Error ... ');
36. Exit;
38. End
40. Result: = Excel.version;
42. Excel.quit;
44. Excel: = UnAssigned;
46. End

Transferred from: http://blog.csdn.net/love3s/article/details/6973761

Use Aadoconnection to connect excel in Delphi7 today