unit Find2;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, Grids, DBGrids, StdCtrls, Buttons, ExcelXP, OleServer,
  ComCtrls, ComObj, Excel2000;

type
  TFind2Frm = class(TForm)
    GroupBox1: TGroupBox;
    SpeedButton1: TSpeedButton;
    SpeedButton2: TSpeedButton;
    StaticText1: TStaticText;
    StaticText2: TStaticText;
    StaticText3: TStaticText;
    StaticText4: TStaticText;
    StaticText5: TStaticText;
    StaticText6: TStaticText;
    StaticText7: TStaticText;
    Edit1: TEdit;
    Edit2: TEdit;
    Edit3: TEdit;
    Edit4: TEdit;
    Edit5: TEdit;
    Edit7: TEdit;
    DBGrid1: TDBGrid;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    ExcelApplication1: TExcelApplication;
    ExcelWorkbook1: TExcelWorkbook;
    ExcelWorksheet1: TExcelWorksheet;
    ComboBox1: TComboBox;
    procedure SpeedButton1Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Find2Frm: TFind2Frm;
  sqll: string;
  ExcelApp:Variant;

implementation

{$R *.dfm}

procedure TFind2Frm.SpeedButton1Click(Sender: TObject);
var
  sql: string;
begin
  sql := 'select ConnTime as 聯系時間,PName as 姓名,PPosition as 職務,Tel as 電話,News as 動態,Note as 批注,StaPerson as 統計人 from HHMain where ';
  if trim(Edit1.Text)<>'' then
    sql := sql + ' ConnTime='+Quotedstr(Edit1.Text)+' and ';
  if trim(Edit2.Text)<>'' then
    sql := sql + ' PName='+Quotedstr(Edit2.Text)+' and ';
  if trim(Edit3.Text)<>'' then
    sql := sql + ' PPosition='+Quotedstr(Edit3.Text)+' and ';
  if trim(Edit4.Text)<>'' then
    sql := sql + ' Tel='+Quotedstr(Edit4.Text)+' and ';
  if trim(Edit5.Text)<>'' then
    sql := sql + ' News='+Quotedstr(Edit5.Text)+' and ';
  if trim(ComboBox1.Text)<>'' then
    sql := sql + ' Note='+Quotedstr(ComboBox1.Text)+' and ';
  if trim(Edit7.Text)<>'' then
    sql := sql + ' StaPerson='+Quotedstr(Edit7.Text)+' and ';
  sql := sql+'1=1 order by ConnTime desc';
  sqll := sql;
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add(sql);
  ADOQuery1.Open;
end;

procedure TFind2Frm.SpeedButton2Click(Sender: TObject);
var
  i,j: integer;
begin
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add(sqll);
  ADOQuery1.Open;
  ADOQuery1.First;
  ExcelApp := CreateOleObject( 'Excel.Application' );
  ExcelApp.Visible := True;
  ExcelApp.Caption := '查詢報表';
  ExcelApp.WorkBooks.Add;
  ExcelApp.Cells.item[1,1].Value:='聯系時間';
  ExcelApp.Cells.item[1,2].Value:='姓名';
  ExcelApp.Cells.item[1,3].Value:='職務';
  ExcelApp.Cells.item[1,4].Value:='電話';
  ExcelApp.Cells.item[1,5].Value:='動態';
  ExcelApp.Cells.item[1,6].Value:='批注';
  ExcelApp.Cells.item[1,7].Value:='統計人';
  i:=2;j:=1;
  while not ADOQuery1.Eof do
  begin
    ExcelApp.Cells.item[i,j].Value:=ADOQuery1.FieldByName('聯系時間').AsString;
    j:=j+1;
    ExcelApp.Cells.item[i,j].Value:=ADOQuery1.FieldByName('姓名').AsString;
    j:=j+1;
    ExcelApp.Cells.item[i,j].Value:=ADOQuery1.FieldByName('職務').AsString;
    j:=j+1;
    ExcelApp.Cells.item[i,j].Value:=ADOQuery1.FieldByName('電話').AsString;
    j:=j+1;
    ExcelApp.Cells.item[i,j].Value:=ADOQuery1.FieldByName('動態').AsString;
    j:=j+1;
    ExcelApp.Cells.item[i,j].Value:=ADOQuery1.FieldByName('批注').AsString;
    j:=j+1;
    ExcelApp.Cells.item[i,j].Value:=ADOQuery1.FieldByName('統計人').AsString;
    j:=1;
    i:=i+1;
    ADOQuery1.Next;
  end;   
end;

procedure TFind2Frm.DBGrid1TitleClick(Column: TColumn);
var
  AscOrDesc: Boolean;
begin
  AscOrDesc := True;
  if AscOrDesc then
    ADOQuery1.Sort := Column.FieldName + ' ASC'
  else
    ADOQuery1.Sort := Column.FieldName + ' DESC';
  AscOrDesc := not AscOrDesc;
end;

end.