unit1.pas

一个简易型的客户信息管理

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, dbcgrids, ADODB, ExtCtrls, StdCtrls, Mask, DBCtrls, Grids,
  DBGridEh, ComCtrls, Menus, Excel2000, OleServer, Buttons;

type
  TForm1 = class(TForm)
    Panel1: TPanel;
    ADODataSet1: TADODataSet;
    DataSource1: TDataSource;
    ADODataSet1ID: TAutoIncField;
    ADODataSet1QuYu: TWideStringField;
    ADODataSet1company: TWideStringField;
    ADODataSet1address: TWideStringField;
    ADODataSet1U_code: TWideStringField;
    ADODataSet1tel: TWideStringField;
    ADODataSet1fax: TWideStringField;
    ADODataSet1mobil: TWideStringField;
    ADODataSet1lxr: TWideStringField;
    ADODataSet1fzr: TWideStringField;
    Panel2: TPanel;
    DBNavigator1: TDBNavigator;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    DBGridEh1: TDBGridEh;
    Label1: TLabel;
    Label2: TLabel;
    DBEdit2: TDBEdit;
    Label3: TLabel;
    DBEdit3: TDBEdit;
    Label4: TLabel;
    DBEdit4: TDBEdit;
    Label5: TLabel;
    DBEdit5: TDBEdit;
    Label6: TLabel;
    DBEdit6: TDBEdit;
    Label7: TLabel;
    DBEdit7: TDBEdit;
    Label8: TLabel;
    Label9: TLabel;
    DBEdit9: TDBEdit;
    Label10: TLabel;
    DBEdit10: TDBEdit;
    DBComboBox1: TDBComboBox;
    ComboBox1: TComboBox;
    Label11: TLabel;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    Button2: TButton;
    ExcelApplication1: TExcelApplication;
    ExcelWorkbook1: TExcelWorkbook;
    ExcelWorksheet1: TExcelWorksheet;
    DBMemo1: TDBMemo;
    ADODataSet1remark: TMemoField;
    ComboBox2: TComboBox;
    Edit1: TEdit;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    CheckBox3: TCheckBox;
    CheckBox4: TCheckBox;
    Edit2: TEdit;
    SpeedButton1: TSpeedButton;
    procedure DBGridEh1TitleClick(Column: TColumnEh);
    procedure ADODataSet1BeforeEdit(DataSet: TDataSet);
    procedure DBGridEh1DblClick(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

uses Unit2;

{$R *.dfm}

procedure TForm1.DBGridEh1TitleClick(Column: TColumnEh);
var
  tmpStr:String;
begin

  tmpStr:= 'SELECT * FROM Mycustom order by ';
  tmpStr:=tmpStr + Column.FieldName;
  if DBGridEh1.Tag=1 then
     tmpStr:= tmpStr +' desc';
  with ADODataSet1 do
  begin
     close;
     CommandText :=tmpStr ;
     open;
  end;

  if DBGridEh1.Tag=0 then
    DBGridEh1.Tag:=1
  else
    DBGridEh1.Tag:=0;
end;

procedure TForm1.ADODataSet1BeforeEdit(DataSet: TDataSet);
begin
      PageControl1.ActivePageIndex := 1;
end;

procedure TForm1.DBGridEh1DblClick(Sender: TObject);
begin
     PageControl1.ActivePageIndex := 1;
end;

procedure TForm1.N1Click(Sender: TObject);
begin
     PageControl1.ActivePageIndex :=0;
end;

procedure TForm1.Button2Click(Sender: TObject);
var
 i,row,column:integer;
begin
 Try
  ExcelApplication1.Connect;  //连上EXCEL
 Except
  MessageDlg('Excel may not be installed',mtError,[mbOk], 0);
  Abort;
 End;

 ExcelApplication1.Caption:='Excel Application';

 ExcelApplication1.Workbooks.Add(Null,0); //打开工作薄
 ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
 ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);
 ADODataSet1.First;
 
 row:=3;
 ExcelWorksheet1.Cells.Item[1,7] := '营销客户名单';
 While Not(ADODataSet1.Eof) do //将数据库的数据导入工作薄
 begin
   column:=1;
   for i:=1 to ADODataSet1.FieldCount do
   begin
    ExcelWorksheet1.Cells.Item[row,column]:=ADODataSet1.fields[i-1].AsString;
    column:=column+1;
   end;
   ADODataSet1.Next;
   row:=row+1;
 end;
  ExcelApplication1.Visible[0]:=false;
  ExcelApplication1.Visible[0]:=True;
end;

procedure TForm1.SpeedButton1Click(Sender: TObject);
var
   sqlstr,conditionSQL:string;
begin
     sqlstr := 'select * from Mycustom ' ;
     conditionSQL := '' ;

     if (CheckBox1.Checked) then
         begin
             conditionSQL := conditionSQL + ' where quyu like  ''%' +
                               ComboBox1.Text + '%''';
         end;

     if (checkbox2.Checked) and (Edit1.Text<>'') then
     begin
         if conditionSQL<>'' then
         begin
              conditionSQL := conditionSQL + ' and address like  ''%' +
                               edit1.Text + '%''';
         end else
         begin
               conditionSQL := conditionSQL + ' where address like  ''%' +
                               edit1.Text + '%''';
         end;
     end;

      if (checkbox3.Checked) and (ComboBox2.Text<>'') then
      begin
         if conditionSQL<>'' then
         begin
              conditionSQL := conditionSQL + ' and fzr like  ''%' +
                               ComboBox2.Text + '%''';
         end else
         begin
               conditionSQL := conditionSQL + ' where fzr like  ''%' +
                               ComboBox2.Text + '%''';
         end;
      end;

      if (checkbox4.Checked) and (edit2.Text<>'') then
      begin
         if conditionSQL<>'' then
         begin
              conditionSQL := conditionSQL + ' and company like  ''%' +
                               edit2.Text + '%''';
         end else
         begin
               conditionSQL := conditionSQL + ' where company like  ''%' +
                               edit2.Text + '%''';
         end;
      end;

          with  ADODataSet1 do
          begin
              close ;
              CommandText := sqlstr+conditionSQL;
              open;
          end;

          Label11.Caption := '查询结果为：' + inttostr(ADODataSet1.RecordCount) +' 家记录。';
end;

end.