,,,
: . . xxxxxx
xxxxxxx
:
:
2008
ACCESS Delphi. SQL.
:
, -, , , , , , , , , , , , , , , COM-, DELPHI.
1
1.1 ER-
1.2 -
2
3
3.1
4
4.1
4.2 SQL
4.3
4.4
4.5 Office
4.5.1 Microsoft Excel
4.5.1 Microsoft Word
- . , , - . , . . , , 5-7%. .
, , .
, , , .
( ) , , . . , .
, Microsoft Access, .
, .
, . , .
, COM Microsoft Office .
COM -, - , . , . COM : OLE ( ), ActiveX- ActiveX, DCOM, COM+.
COM (MS Office, MTS, ) Windows (Automation, Drag & Drop, ...).
COM , , , , . COM , , , , . , , COM- , .
Microsoft Office . , . , Delphi QuickReport , , Microsoft Office, , , Crystal Reports (Crystal Decisions).
, Microsoft Office , , , , Microsoft Office (, ).
1
1.1 ER-
, , - , .
. . / :
, ;
;
;
ER- :
c , ( ). ;
, . .
.
1.2 -
, , , , , , .
2
, , , , , , . , , , .
, , . , , .
, .
, , .
: , , , , , , , , , , . , , .
3
3.1
. , ( ) .
:
: _, _, _, , _, , , .
: _, , .
: , , , .
: , , , .
: , , , .
: _, , MHz, Monitor, Video, Hdd, Mouse, Keyboard, Headphones .
Microsoft Access . 3.1
4
4.1
4.1 . 4.1 -
_ | () | |||
_ | / |
() |
||
_ | / |
() |
||
_ | ||||
() | ||||
_ | () | |||
() |
||||
() |
||||
() | ||||
4.2 SQL
SQL CREATE TABLE.
CREATE TABLE.
( VARCHAR (50) NOT NULL,
VARCHAR (50) NOT NULL,
VARCHAR (50) NOT NULL,
NUMBER NOT NULL,
PRIMARY KEY (),
FOREIGN KEY (),
REFERENCES ,
ON DELETE CASCADE,
ON UPDATE CASCADE)
( VARCHAR (50) NOT NULL,
VARCHAR (50) NOT NULL,
VARCHAR (50) NOT NULL,
VARCHAR (50) NOT NULL,
PRIMARY KEY (),
ON DELETE CASCADE,
ON UPDATE CASCADE)
( VARCHAR (50) NOT NULL,
VARCHAR (50) NOT NULL,
VARCHAR (50) NOT NULL,
VARCHAR (50) NOT NULL,
PRIMARY KEY (),
FOREIGN KEY (),
REFERENCES ,
ON DELETE CASCADE,
ON UPDATE CASCADE)
(_ VARCHAR (10) NOT NULL,
VARCHAR (50) NOT NULL,
MONEY NOT NULL,
PRIMARY KEY (_),
ON DELETE CASCADE,
ON UPDATE CASCADE)
(_ VARCHAR (50) NOT NULL,
VARCHAR (50) NOT NULL,
MHz VARCHAR (50) NOT NULL,Monitor
Video VARCHAR (50) NOT NULL,
Hdd VARCHAR (50) NOT NULL,
Mouse VARCHAR (50) NOT NULL,
Keyboard VARCHAR (50) NOT NULL,
Headphones VARCHAR (50) NOT NULL,
PRIMARY KEY (_),
ON DELETE CASCADE,
ON UPDATE CASCADE)
(_ VARCHAR (50) NOT NULL,
_ DATE NOT NULL,
_ DATE NOT NULL,
NUMBER NOT NULL,
_ VARCHAR (50) NOT NULL,
VARCHAR (50) NOT NULL,
MONEY NOT NULL,
VARCHAR (50) NOT NULL,
PRIMARY KEY (_, _, _, , ),
FOREIGN KEY (_),
REFERENCES ,
ON DELETE CASCADE,
ON UPDATE CASCADE,
FOREIGN KEY ( )
REFERENCES ,
ON DELETE CASCADE,
ON UPDATE CASCADE,
FOREIGN KEY (_),
REFERENCES ,
ON DELETE CASCADE,
ON UPDATE CASCADE,
)
4.3
) . , .4.2.
, , , , .
NULL-. NULL- ( ), . NULL- . NULL- ACCESS.
4.4
. :
1. :
SELECT ._, .
FROM INNER JOIN ( INNER JOIN ([ ] INNER JOIN ON [ ].=.) ON ._=._) ON ._=._
WHERE (((._)=[ ]));
2. .
SELECT Sum([ ].) AS
FROM [ ]
WHERE ((([ ]._)=[ ]));
3. ,
SELECT [ ]., [ ]., [ ].
FROM [ ]
WHERE [ ]. Is Null;
4. , 5 .
SELECT [ ]., [ ]., [ ]._, [ ]._, [ ]._, [ ].
FROM [ ]
GROUP BY [ ]., [ ]., [ ]._, [ ]._, [ ]._, [ ].
HAVING ((([ ].)>5));
5. , .
SELECT ., [ ]._, [ ]._, [ ]._, [ ].
FROM INNER JOIN ( INNER JOIN ([ ] INNER JOIN [ ] ON [ ].=[ ].) ON ._=[ ]._) ON ._=[ ]._
WHERE (((._)=[ ]))
ORDER BY [ ]._;
6. ,
SELECT Sum([ ].) AS
FROM [ ]
WHERE ((([ ].)=[ ]));
7. ,
SELECT [ ]._, [ ]._, [ ]._, [ ]., [ ]._, [ ]., [ ]., [ ].
FROM [ ]
ORDER BY [ ]._, [ ].;
8.
SELECT MAX([ ]._) AS
FROM [ ];
9.
SELECT MIN([ ]._) AS
FROM [ ];
10.
SELECT [ ]._, [ ]._, [ ]., [ ]._
FROM INNER JOIN ( INNER JOIN ([ ] INNER JOIN [ ] ON [ ].=[ ].) ON ._=[ ]._) ON ._=[ ]._
WHERE ((([ ]._)=[ ]));
11. .
SELECT [ ]._, [ ]._, [ ]., [ ]._
FROM INNER JOIN ( INNER JOIN ([ ] INNER JOIN [ ] ON [ ].=[ ].) ON ._=[ ]._) ON ._=[ ]._
WHERE ((([ ]._)=[ ]));
12.
SELECT ._, ., .
FROM INNER JOIN ( INNER JOIN ([ ] INNER JOIN [ ] ON [ ].=[ ].) ON ._=[ ]._) ON ._=[ ]._
GROUP BY ._, [ ]._, ., .
HAVING (("_"="_"));
4.5 Office
club.mdb, Microsoft Office.
4.5.1 Microsoft Word. Delphi
TADOConection, TADODataSet TButton ( .4.3). Caption Name 1.
4.1
Caption | Name | |
Tbutton1 | MS Word | Report_Word |
Tbutton2 | MS Excel | Report_Excel |
ConnectionString TADODataSet , club.mdb .
Application, Document, Range Table:
Var Forml: TForm1;
// Range Table
Wd, Doc, Rng, Tbl: Variant;
CommandType CommandText 4.2.
4.2
CommandType | CommandText | |
TADODataSet1 | cmdText | Select , , From |
OnClick Buttonl:
procedure TForm1.Report_WordClick(Sender: TObject);
var I, Rcnt:integer;
begin
//
ADODataSet1.Open;
Rcnt:=ADODataSet1.RecordCount;
// MS Word
wd:= CreateOleObject('Word.Application');
// MS Word
wd.Visible :=True;
//
wd.Documents.Add;
Doc:= wd.Documents.Item(1);
//
Doc.Paragraphs.Add;
//
Doc.Paragraphs.Item(1).Style:=' 1';
//
Rng := Doc.Range(0);
Rng.InsertBefore(' ');
//
Doc.Paragraphs.Add;
Rng.InsertAfter(': : : : : ');
//
ADODataSet1.First;
for I := 1 to Rcnt do begin
//
Doc.Paragraphs.Add;
//
Rng.InsertAfter(ADODataSet1.Fields[0].AsString+':'+
ADODataSet1.Fields[1].ASString+':'+
ADODataSet1.Fields[2].AsString+':'+
ADODataSet1.Fields[3].ASString+':'+
ADODataSet1.Fields[4].ASString+':'+
ADODataSet1.Fields[5].ASString);
ADODataSet1.Next;
end;
//
Rng:=Doc.Range(Doc.Paragraphs.Item(3).Range.Start, Doc.Paragraphs.Item(Rcnt+3).Range.End);
Tbl:=Rng.ConvertToTable(':',Rcnt,6);
//
Tbl.Columns.Item(1).Width:=Tbl.Columns.Item(1).Width-30;
Tbl.Columns.Item(2).Width:=Tbl.Columns.Item(2).Width+20;
Tbl.Columns.Item(3).Width:=Tbl.Columns.Item(3).Width-3;
Tbl.Columns.Item(4).Width:=Tbl.Columns.Item(4).Width+20;
Tbl.Columns.Item(5).Width:=Tbl.Columns.Item(5).Width-5;
Tbl.Columns.Item(6).Width:=Tbl.Columns.Item(6).Width+5;
//
Wd.DisplayAlerts:=False;
//
Doc.SaveAs(D:\SOT\Custrep.doc');
// Word
//Wd.Quit;
Wd:=Unassigned;
end;
MS Word. club.mdb (.4.4).
.
-, Microsoft Word, .
wd:= CreateOleObject('Word.Application');
// MS Word
wd.Visible :=True;
//
wd.Documents.Add;
, :
//
Doc.Paragraphs.Add;
//
Doc.Paragraphs.Item(1).Style:=' 1';
//
Rng := Doc.Range(0);
Rng.lnsertBefore( ');
//
Doc.Paragraphs.Add;
Rng.InsertAfter(': : : : : ');
, , , :
//
ADODataSet1.First;
for I := 1 to Rcnt do begin
//
Doc.Paragraphs.Add;
//
Rng.InsertAter(ADODataSet1.Fields[0].AsString+':'+
ADODataSet1.Fields[1].ASString+':'+
ADODataSet1.Fields[2].AsString+':'+
ADODataSet1.Fields[3].ASString+':'+
ADODataSet1.Fields[4].ASString+':'+
ADODataSet1.Fields[5].ASString);
ADODataSet1.Next;
end;
Word , :
//
Rng:=Doc.Range(Doc.Paragraphs.Item(3).Range.Start, Doc.Paragraphs.Item(rcnt+3).Range.End); Tbl:=Rng.ConvertToTable(':',rcnt.3);
//
Tbl.Columns.Item(1).Width:=Tbl.Columns.Item(1).Width-30;
Tbl.Columns.Item(2).Width:=Tbl.Columns.Item(2).Width+20;
Tbl.Columns.Item(3).Width:=Tbl.Columns.Item(3).Width-3;
Tbl.Columns.Item(4).Width:=Tbl.Columns.Item(4).Width+20;
Tbl.Columns.Item(5).Width:=Tbl.Columns.Item(5).Width-5;
Tbl.Columns.Item(6).Width:=Tbl.Columns.Item(6).Width+5; , Word:
//
Wd.DisplayAlerts:=False;
? , Word, , , DCOM -, TCP/IP HTTP/HTTPS. Word ; , DCOM , DCOM- ( ) . , , , , , , .
, , :
//
Doc.SaveAs('D:\SOT\Custrep.doc');
// Word
Wd.Quit;
Wd:=Unassigned;
4.5.2 Microsoft Excel
, Application, WorkBook WorkSheet:
var
Forml: TForm1;
. . .
// Excel Application, WorkBook WorkSheet
Xl, Wb, Ws: Variant;
OnClick Button2:
procedure TForm1.Report_ExcelClick(Sender: TObject);
var I, Rcnt:integer;
begin
//
ADODataSet1.Open;
Rcnt := ADODataSet1.RecordCount;
// Microsoft Excel
Xl := CreateOleObject('Excel.Application');
// Microsoft Excel
Xl.Visible := True;
//
Xl.WorkBooks.Add;
Wb := XL.WorkBooks[1];
Ws := Wb.WorkSheets[1];
Ws.Name := ' ';
//
Ws.Cells[1,1] := ' ';
Ws.Cells[1,1].Font.Bold := True;
Ws.Cells[1,1].Font.Size := 16;
Ws.Cells[2,1] := '';
Ws.Cells[2,2] := '';
Ws.Cells[2,3] := '';
Ws.Cells[2,4] := '';
Ws.Cells[2,5] := ' ';
Ws.Cells[2,6] := '';
for I:=1 to 6 do
Ws.Cells[2,i].Font.Bold := True;
//
ADODataSet1.First;
for I:=1 to Rcnt do
begin
//
Ws.Cells[i+2,1] := ADODataSet1.Fields[0].AsString;
Ws.Cells[i+2,2] := ADODataSet1.Fields[1].AsString;
Ws.Cells[i+2,3] := ADODataSet1.Fields[2].AsString;
Ws.Cells[i+2,4] := ADODataSet1.Fields[3].AsString;
Ws.Cells[i+2,5] := ADODataSet1.Fields[4].AsString;
Ws.Cells[i+2,6] := ADODataSet1.Fields[5].AsString;
ADODataSet1.Next;
end;
//
Xl.Columns[1].ColumnWidth:=Xl.Columns[1].ColumnWidth+5;
Xl.Columns[2].ColumnWidth:=Xl.Columns[2].ColumnWidth+5;
Xl.Columns[3].ColumnWidth:=Xl.Columns[3].ColumnWidth+5;
Xl.Columns[4].ColumnWidth:=Xl.Columns[4].ColumnWidth+5;
Xl.Columns[5].ColumnWidth:=Xl.Columns[5].ColumnWidth+5;
Xl.Columns[6].ColumnWidth:=Xl.Columns[6].ColumnWidth+5;
//
Xl.DisplayAlerts:=false;
//
Wb.SaveAs('D:\SOT\Custrer.xls');
// Excel
//Xl.Quit;
Xl:=Unassigned;
end;
MS Excel. club.mdb (.4.5).
-, Microsoft Excel, :
// Microsoft Excel
Xl := CreateOleObject('Excel.Application');
// Microsoft Excel
Xl.Visible := True;
//
Xl.WorkBooks.Add;
Wb := XL.WorkBooks[1];
Ws := Wb.WorkSheets[1];
Ws.Name := ' ';
, :
//
Ws.Cells[1,1] := ' ';
Ws.Cells[1,1].Font.Bold := True;
Ws.Cells[1,1].Font.Size := 16;
Ws.Cells[2,1] := '';
Ws.Cells[2,2] := '';
Ws.Cells[2,3] := '';
Ws.Cells[2,4] := '';
Ws.Cells[2,5] := ' ';
Ws.Cells[2,6] := '';
for I:=1 to 6 do
Ws.Cells[2,i].Font.Bold := True; , , , :
//
ADODataSet1.First;
for I:=1 to Rcnt do
begin
//
Ws.Cells[i+2,1] := ADODataSet1.Fields[0].AsString;
Ws.Cells[i+2,2] := ADODataSet1.Fields[1].AsString;
Ws.Cells[i+2,3] := ADODataSet1.Fields[2].AsString;
Ws.Cells[i+2,4] := ADODataSet1.Fields[3].AsString;
Ws.Cells[i+2,5] := ADODataSet1.Fields[4].AsString;
Ws.Cells[i+2,6] := ADODataSet1.Fields[5].AsString;
ADODataSet1.Next;
end;
, , :
//
Xl.Columns[1].ColumnWidth:=Xl.Columns[1].ColumnWidth+5;
Xl.Columns[2].ColumnWidth:=Xl.Columns[2].ColumnWidth+5;
Xl.Columns[3].ColumnWidth:=Xl.Columns[3].ColumnWidth+5;
Xl.Columns[4].ColumnWidth:=Xl.Columns[4].ColumnWidth+5;
Xl.Columns[5].ColumnWidth:=Xl.Columns[5].ColumnWidth+5;
Xl.Columns[6].ColumnWidth:=Xl.Columns[6].ColumnWidth+5;
, Excel. Word, Excel , Excel:
//
Xl.DisplayAlerts:=false;
//
Wb.SaveAs('D:\SOT\Custrep.xls');
, Excel :
// Excel
Xl.Quit;
Xl:=Unassigned;
.
procedure TForm1.Button6Click(Sender: TObject);
begin
with form1.ADOQuery2 do begin
//Close; // -
SQL.Clear; //
// SQL
SQL.Add('INSERT INTO (, , , ) VALUES ("Annie", " ..", ":)", 1000);');
ExecSQL;
end;
(
.
- , , , , .
. , .
. . .
. , , , , . Access , SQL , .
, Microsoft Office , , , , Microsoft Office (, ).
.
1. .. .
2. . .-.: , 1980.-608.
3. .., .. , SQL2 SQL SERVER: .-.: ,2000-250 .
4. . Access.-:.1997-848.
5. ., . . SQL.-:,1998-565.
6. . .- 6- .-:, 1998.-784.
7. , , , , , , . ++Builder 5. , 2. : . . .: , 2001. - 832 .: . . . .
8. .. Delphi. . 2-, . . .: -, 2004 . 848 .: .
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ComObj, ActiveX, DB, ADODB, StdCtrls, Grids, DBGrids, ComCtrls,
OleServer, ExcelXP;
type
TForm1 = class(TForm)
Report_Word: TButton;
Report_Excel: TButton;
CreatePivotTable: TButton;
ADOConnection1: TADOConnection;
ADODataSet1: TADODataSet;
PageControl1: TPageControl;
TabSheet1: TTabSheet;
TabSheet2: TTabSheet;
TabSheet3: TTabSheet;
ADOTable1: TADOTable;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
RadioButton1: TRadioButton;
ComboBox1: TComboBox;
RadioButton2: TRadioButton;
ComboBox2: TComboBox;
DBGrid2: TDBGrid;
ADOQuery1: TADOQuery;
DataSource2: TDataSource;
Select: TMemo;
Button1: TButton;
DBGrid3: TDBGrid;
DataSource3: TDataSource;
TabSheet4: TTabSheet;
ADOQuery2: TADOQuery;
DataSource4: TDataSource;
DBGrid4: TDBGrid;
Button4: TButton;
ADODataSet3: TADODataSet;
Button5: TButton;
TabSheet5: TTabSheet;
DBGrid5: TDBGrid;
DataSource5: TDataSource;
Button6: TButton;
procedure Report_WordClick(Sender: TObject);
procedure Report_ExcelClick(Sender: TObject);
procedure CreatePivotTableClick(Sender: TObject);
procedure RadioButton1Click(Sender: TObject);
procedure ComboBox1Click(Sender: TObject);
procedure RadioButton2Click(Sender: TObject);
procedure ComboBox2Click(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button4Click(Sender: TObject);
procedure Button5Click(Sender: TObject);
procedure Button6Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
Wd, Doc, Rng, Tbl, Xl, Wb, Ws, Ch, Z : Variant;
implementation
{$R *.dfm}
procedure TForm1.Report_WordClick(Sender: TObject);
var I, Rcnt:integer;
begin
//
ADODataSet1.Open;
Rcnt:=ADODataSet1.RecordCount;
// MS Word
wd:= CreateOleObject('Word.Application');
// MS Word
wd.Visible :=True;
//
wd.Documents.Add;
Doc:= wd.Documents.Item(1);
//
Doc.Paragraphs.Add;
//
Doc.Paragraphs.Item(1).Style:=' 1';
//
Rng := Doc.Range(0);
Rng.InsertBefore(' ');
//
Doc.Paragraphs.Add;
Rng.InsertAfter(': : : : : ');
//
ADODataSet1.First;
for I := 1 to Rcnt do begin
//
Doc.Paragraphs.Add;
//
Rng.InsertAfter(ADODataSet1.Fields[0].AsString+':'+
ADODataSet1.Fields[1].ASString+':'+
ADODataSet1.Fields[2].AsString+':'+
ADODataSet1.Fields[3].ASString+':'+
ADODataSet1.Fields[4].ASString+':'+
ADODataSet1.Fields[5].ASString);
ADODataSet1.Next;
end;
//
Rng:=Doc.Range(Doc.Paragraphs.Item(3).Range.Start, Doc.Paragraphs.Item(Rcnt+3).Range.End);
Tbl:=Rng.ConvertToTable(':',Rcnt,6);
//
Tbl.Columns.Item(1).Width:=Tbl.Columns.Item(1).Width-30;
Tbl.Columns.Item(2).Width:=Tbl.Columns.Item(2).Width+20;
Tbl.Columns.Item(3).Width:=Tbl.Columns.Item(3).Width-3;
Tbl.Columns.Item(4).Width:=Tbl.Columns.Item(4).Width+20;
Tbl.Columns.Item(5).Width:=Tbl.Columns.Item(5).Width-5;
Tbl.Columns.Item(6).Width:=Tbl.Columns.Item(6).Width+5;
//
Wd.DisplayAlerts:=False;
//
Doc.SaveAs('D:\SOT\Custrep.doc');
// Word
//Wd.Quit;
Wd:=Unassigned;
end;
procedure TForm1.Report_ExcelClick(Sender: TObject);
var I, Rcnt:integer;
begin
//
ADODataSet1.Open;
Rcnt := ADODataSet1.RecordCount;
// Microsoft Excel
Xl := CreateOleObject('Excel.Application');
// Microsoft Excel
Xl.Visible := True;
//
Xl.WorkBooks.Add;
Wb := XL.WorkBooks[1];
Ws := Wb.WorkSheets[1];
Ws.Name := ' ';
//
Ws.Cells[1,1] := ' ';
Ws.Cells[1,1].Font.Bold := True;
Ws.Cells[1,1].Font.Size := 16;
Ws.Cells[2,1] := '';
Ws.Cells[2,2] := '';
Ws.Cells[2,3] := '';
Ws.Cells[2,4] := '';
Ws.Cells[2,5] := ' ';
Ws.Cells[2,6] := '';
for I:=1 to 6 do
Ws.Cells[2,i].Font.Bold := True;
//
ADODataSet1.First;
for I:=1 to Rcnt do
begin
//
Ws.Cells[i+2,1] := ADODataSet1.Fields[0].AsString;
Ws.Cells[i+2,2] := ADODataSet1.Fields[1].AsString;
Ws.Cells[i+2,3] := ADODataSet1.Fields[2].AsString;
Ws.Cells[i+2,4] := ADODataSet1.Fields[3].AsString;
Ws.Cells[i+2,5] := ADODataSet1.Fields[4].AsString;
Ws.Cells[i+2,6] := ADODataSet1.Fields[5].AsString;
ADODataSet1.Next;
end;
//
Xl.Columns[1].ColumnWidth:=Xl.Columns[1].ColumnWidth+5;
Xl.Columns[2].ColumnWidth:=Xl.Columns[2].ColumnWidth+5;
Xl.Columns[3].ColumnWidth:=Xl.Columns[3].ColumnWidth+5;
Xl.Columns[4].ColumnWidth:=Xl.Columns[4].ColumnWidth+5;
Xl.Columns[5].ColumnWidth:=Xl.Columns[5].ColumnWidth+5;
Xl.Columns[6].ColumnWidth:=Xl.Columns[6].ColumnWidth+5;
//
Xl.DisplayAlerts:=false;
//
Wb.SaveAs('D:\SOT\Custrer.xls');
// Excel
//Xl.Quit;
Xl:=Unassigned;
end;
procedure TForm1.CreatePivotTableClick(Sender: TObject);
var
WB,PC,PT:Variant;
const
// Excel
xlExternal = $00000002;
xlCmdSql = $00000002;
xlColumnField = $00000002;
xlDataField = $00000004;
xlPageField = $00000003;
xlRowField = $00000001;
begin
// ADODataSet2.Open;
// Microsoft Excel
Xl := CreateOleObject('Excel.Application');
// Excel
Xl.Visible:=true;
//
Xl.WorkBooks.Add(1);
Wb := Xl.WorkBooks[1];
Ws := Wb.WorkSheets[1];
//
PC := WB.PivotCaches.Add(xlExternal);
//
PC.Connection := 'OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;'+
'Data Source=D:\SOT\club.mdb';
PC.CommandType := xlCmdSql;
PC.CommandText := 'SELECT , _,'+' , _, FROM 01Cost_more_5';
//
PC.CreatePivotTable(WB.Worksheets[1].Cells[1,1],'PivotTable1');
PT := WB.Worksheets[1].PivotTables('PivotTable1');
//
PT.PivotFields('').Orientation := xlRowField;
PT.PivotFields('').Position := 1;
PT.PivotFields('_').Orientation := xlRowField;
PT.PivotFields('_').Position := 2;
PT.PivotFields('').Orientation := xlPageField;
PT.PivotFields('_').Orientation := xlColumnField;
PT.PivotFields('').Orientation := xlDataField;
WB.Worksheets[1].Columns[2].ColumnWidth := 15;
WB.Worksheets[1].Columns[1].ColumnWidth := 20;
end;
procedure TForm1.RadioButton1Click(Sender: TObject);
var i:integer;
begin
ADOConnection1.GetTableNames(ComboBox1.Items);
for i:=Combobox1.Items.Count-1 downto 0 do
if Pos ('0', Combobox1.Items[i])>0 then Combobox1.Items.Delete(i) ;
for i:=Combobox1.Items.Count-1 downto 0 do
if Pos ('1', Combobox1.Items[i])>0 then Combobox1.Items.Delete(i) ;
for i:=Combobox1.Items.Count-1 downto 0 do
if Pos ('2', Combobox1.Items[i])>0 then Combobox1.Items.Delete(i) ;
end;
procedure TForm1.ComboBox1Click(Sender: TObject);
begin
if ADOTable1.Active then ADOTable1.Active:=False;
ADOTable1.TableName:=ComboBox1.Text;ADOTable1.Active:=True;
TabSheet1.Caption:= ComboBox1.Text;
end;
procedure TForm1.RadioButton2Click(Sender: TObject);
var i:integer;
begin
ADOConnection1.GetTableNames(ComboBox2.Items);
for i:=Combobox2.Items.Count-1 downto 0 do
if Pos ('', Combobox2.Items[i])>0 then Combobox2.Items.Delete(i) ;
for i:=Combobox2.Items.Count-1 downto 0 do
if Pos ('', Combobox2.Items[i])>0 then Combobox2.Items.Delete(i) ;
for i:=Combobox2.Items.Count-1 downto 0 do
if Pos (' ', Combobox2.Items[i])>0 then Combobox2.Items.Delete(i) ;
for i:=Combobox2.Items.Count-1 downto 0 do
if Pos ('', Combobox2.Items[i])>0 then Combobox2.Items.Delete(i) ;
for i:=Combobox2.Items.Count-1 downto 0 do
if Pos ('', Combobox2.Items[i])>0 then Combobox2.Items.Delete(i) ;
for i:=Combobox2.Items.Count-1 downto 0 do
if Pos ('~', Combobox2.Items[i])>0 then Combobox2.Items.Delete(i) ;
end;
procedure TForm1.ComboBox2Click(Sender: TObject);
begin
if ADOTable1.Active then ADOTable1.Active:=False;
ADOTable1.TableName:=ComboBox2.Text;ADOTable1.Active:=True;
TabSheet1.Caption:= ComboBox2.Text;
end;
procedure TForm1.Button1Click(Sender: TObject);
begin
if ADOQuery2.Active then ADOQuery2.Active:=False;
ADOQuery2.SQL:=Select.Lines;ADOQuery2.Active:=True;
end;
procedure TForm1.Button2Click(Sender: TObject);
begin
if ADOTable1.Active then ADOTable1.Active:=False;
ADOTable1.TableName:=ComboBox1.Text;ADOTable1.Active:=True;
TabSheet1.Caption:= ComboBox1.Text;
end;
procedure TForm1.Button4Click(Sender: TObject);
{var
fam: string[30];
begin
fam:=InputBox(' ',
' OK.', '');
if fam <> '' //
then }
begin
with form1.ADOQuery2 do begin
Close; // -
SQL.Clear; //
// SQL
SQL.Add('SELECT , , ');
SQL.Add('FROM ');
Open; //
end;
{if ADOQuery2.RecordCount <> 0 then
DataSource1.DataSet:=ADOQuery2 // -
else begin
ShowMessage(' , .');
DataSource1.DataSet:=ADOTable1;
end; }
end;
procedure TForm1.Button5Click(Sender: TObject);
var I, Rcnt:integer;
begin
//
ADODataSet3.Open;
Rcnt := ADODataSet3.RecordCount;
// Microsoft Excel
Xl := CreateOleObject('Excel.Application');
// Microsoft Excel
Xl.Visible := True;
//
Xl.WorkBooks.Add;
Wb := XL.WorkBooks[1];
Ws := Wb.WorkSheets[1];
Ws.Name := '';
//
Ws.Cells[1,1] := '';
Ws.Cells[1,1].Font.Bold := True;
Ws.Cells[1,1].Font.Size := 16;
Ws.Cells[2,1] := '';
Ws.Cells[2,2] := '';
Ws.Cells[2,3] := '';
Ws.Cells[2,4] := '';
for I:=1 to 4 do
Ws.Cells[2,i].Font.Bold := True;
//
ADODataSet3.First;
for I:=1 to Rcnt do
begin
//
Ws.Cells[i+2,1] := ADODataSet3.Fields[0].AsString;
Ws.Cells[i+2,2] := ADODataSet3.Fields[1].AsString;
Ws.Cells[i+2,3] := ADODataSet3.Fields[2].AsString;
Ws.Cells[i+2,4] := ADODataSet3.Fields[3].AsString;
ADODataSet3.Next;
end;
//
Xl.Columns[1].ColumnWidth:=Xl.Columns[1].ColumnWidth+5;
Xl.Columns[2].ColumnWidth:=Xl.Columns[2].ColumnWidth+5;
Xl.Columns[3].ColumnWidth:=Xl.Columns[3].ColumnWidth+5;
Xl.Columns[4].ColumnWidth:=Xl.Columns[4].ColumnWidth+5;
//
Xl.DisplayAlerts:=false;
//
Wb.SaveAs('D:\SOT\Custrer2.xls');
// Excel
//Xl.Quit;
Xl:=Unassigned;
end;
procedure TForm1.Button6Click(Sender: TObject);
begin
with form1.ADOQuery2 do begin
//Close; // -
SQL.Clear; //
// SQL
SQL.Add('INSERT INTO (, , , ) VALUES ("Annie", " ..", ":)", 1000);');
ExecSQL;
//
end;
end;
end.
Copyright (c) 2024 Stud-Baza.ru , , , .