. , , ,

,,,

,

:

: . . xxxxxx

xxxxxxx

:

:

2008


, - , , , , , . , , , , _ . _ ,_ , _ , _ SQL , _ .

, , . , ().

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 -

, , , , , , .

, , , , , , , .

, , , , , , , , .

- , , .

, , , .

, , , .

(ER-) . 1.1


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.

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)

CREATE TABLE

( VARCHAR (50) NOT NULL,

VARCHAR (50) NOT NULL,

VARCHAR (50) NOT NULL,

VARCHAR (50) NOT NULL,

PRIMARY KEY (),

ON DELETE CASCADE,

ON UPDATE CASCADE)

CREATE TABLE

( 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)

CREATE TABLE

(_ VARCHAR (10) NOT NULL,

VARCHAR (50) NOT NULL,

MONEY NOT NULL,

PRIMARY KEY (_),

ON DELETE CASCADE,

ON UPDATE CASCADE)

CREATE TABLE

(_ 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)

CREATE TABLE

(_ 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.

 

 

 

! , , , .
. , :