1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
| unit Unit1;
{ Export des données vers Excel avec ADOX }
{
1)Project->Import Type Library:
2)Select "Microsoft ADO Ext. for DDL and Security"
3)Uncheck "Generate component wrapper" at the bottom
4)Rename the class names (TTable, TColumn, TIndex, TKey, TGroup, TUser, TCatalog) in
(_Table, _Column, _Index, _Key, _Group, _User, _Catalog)
in order to avoid conflicts with the already present TTable component.
5)Select the Unit dir name and press "Create Unit".
It will be created a file named AOX_TLB.
Include ADOX_TLB in the "uses" directive inside the file in which you want
to use ADOX functionality.
That is all. Let's go now with the implementation:
}
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ADOX_TLB, StdCtrls, Grids, DB, ADODB ;
type
TForm1 = class(TForm)
Button1: TButton;
StringGrid1: TStringGrid;
procedure Button1Click(Sender: TObject);
Procedure XLS1 ;
Procedure Remplir ;
Private
{ Déclarations privées }
public
{ Déclarations publiques }
end;
var
Form1: TForm1;
cat: _Catalog;
tbl: _Table;
col: _Column;
implementation
{$R *.dfm}
Procedure TForm1.XLS1 ;
begin
If FileExists('d:\creerexcel.xls') Then DeleteFile('d:\creerexcel.xls') ;
cat := CoCatalog.Create;
cat.Set_ActiveConnection(
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\creerexcel.xls;Extended Properties=Excel 8.0');
// WorkSheet creation (table)
tbl := CoTable.Create;
tbl.Set_Name('Traductions');
col := CoColumn.Create;
with col do
begin
Set_Name('Groupe');
Set_Type_(adWChar);
end;
tbl.Columns.Append(col, adWChar, 500);
col := CoColumn.Create;
with col do
begin
Set_Name('Emplacement');
Set_Type_(adWChar);
end;
tbl.Columns.Append(col, adWChar, 150);
col := CoColumn.Create;
with col do
begin
Set_Name('ID');
Set_Type_(adInteger);
end;
tbl.Columns.Append(col, adInteger, 11);
col := CoColumn.Create;
with col do
begin
Set_Name('Référence');
Set_Type_(adWChar);
end;
tbl.Columns.Append(col, adWChar, 500);
cat.Tables.Append(tbl);
col := nil;
tbl := nil;
cat := nil;
end;
Procedure TForm1.Remplir ;
Var
Groupe, Emplacement, ID, Reference : String ;
ADOConnection : TADOConnection ;
ADOQuery : TADOQuery ;
Begin
Groupe := 'AA' ;
Emplacement := 'BB' ;
ID := '2' ;
Reference := 'RefXX' ;
ADOConnection := TADOConnection.Create(nil);
ADOConnection.LoginPrompt := false;
ADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\creerexcel.xls;Extended Properties=Excel 8.0';
ADOQuery := TADOQuery.Create(nil);
ADOQuery.Connection := ADOConnection;
// La feuille s'appelle Traduction et il faut lui ajouter un $
ADOQuery.SQL.Text := 'Select * from [' + 'Traductions' + '$]' ;
ADOQuery.Open;
// Groupe, Emplacement, ID, Reference, Trad sont des variables à remplir
ADOQuery.AppendRecord([Groupe, Emplacement, ID, Reference]);
ADOQuery.Close;
ADOConnection.Close;
End ;
procedure TForm1.Button1Click(Sender: TObject);
begin
XLS1 ;
Remplir ;
Remplir ;
end;
end. |
