unit Untchaxun;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, ComObj, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ComCtrls,  EXCEL2000, Buttons, DB, ADODB;

type
  TFrmChaXun = class(TForm)
    GroupBox1: TGroupBox;
    DateTimePicker1: TDateTimePicker;
    Edit1: TEdit;
    CheckBox1: TCheckBox;
    Edit2: TEdit;
    Edit3: TEdit;
    CheckBox2: TCheckBox;
    CheckBox3: TCheckBox;
    CheckBox4: TCheckBox;
    BitBtn1: TBitBtn;
    ADOQuery1: TADOQuery;
    procedure CheckBox1Click(Sender: TObject);
    procedure CheckBox2Click(Sender: TObject);
    procedure CheckBox3Click(Sender: TObject);
    procedure CheckBox4Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  FrmChaXun: TFrmChaXun;

implementation

uses Untbd2;

{$R *.dfm}

procedure TFrmChaXun.CheckBox1Click(Sender: TObject);
begin
  if CheckBox1.Checked then
        DateTimePicker1.Enabled:=true
    else
        DateTimePicker1.Enabled:=false;
end;

procedure TFrmChaXun.CheckBox2Click(Sender: TObject);
begin
  if CheckBox2.Checked then
        edit1.Enabled:=true
    else
      begin
        edit1.Enabled:=false;
        edit1.Text:='';
      end;
end;

procedure TFrmChaXun.CheckBox3Click(Sender: TObject);
begin
  if CheckBox3.Checked then
        edit2.Enabled:=true
    else
      begin
        edit2.Enabled:=false;
        edit2.Text:='';
      end;
end;

procedure TFrmChaXun.CheckBox4Click(Sender: TObject);
begin
  if CheckBox4.Checked then
        edit3.Enabled:=true
    else
      begin
        edit3.Enabled:=false;
        edit3.Text:='';
      end;
end;

procedure TFrmChaXun.BitBtn1Click(Sender: TObject);
   var
     s1,s2,s3,s4,ss1:string;
     sa1,sa2,sa3,sa4,sa5,sa6,sa7,sa8:string;
     i2:integer;
      v:Variant;
    Sheet: Variant;
begin
   if edit1.Enabled and (edit1.Text='')  then
    begin
      showmessage('請輸入要查詢的測量物品編號！');
      exit;
    end;
   if edit2.Enabled and (edit2.Text='')  then
     begin
      showmessage('請輸入要查詢的測量物品名稱！');
      exit;
     end;

   if edit3.Enabled and (edit3.Text='')  then
     begin
      showmessage('請輸入要查詢的測量物品來源！');
      exit;
     end;
    if   CheckBox1.Checked then
        s1:=datetostr(DateTimePicker1.Date)
      else
        s1:='';
    if  CheckBox2.Checked then
         s2:=trim(edit1.Text)
      else
         s2:='';
    if CheckBox3.Checked  then
          s3:=trim(edit2.Text)
       else
         s3:='';
    if CheckBox4.Checked then
        s4:=trim(edit3.Text)
      else
         s4:='';

   if (s1='') and (s2='') and (s3='') and (s4='') then
      begin
       showmessage('請選擇要查詢的條件！');
       exit;
      end;
       
   if (s1<>'') and (s2='') and (s3='') and (s4='') then
       ss1:='select *  from CELIANG where CLRIQI='+QuotedStr(S1);

   if (s1='') and (s2<>'') and (s3='') and (s4='') then
       ss1:='select *  from CELIANG where CLBH='+QuotedStr(S2);

   if (s1='') and (s2='') and (s3<>'') and (s4='') then
       ss1:='select *  from CELIANG where CLname='+QuotedStr(S3);

   if (s1='') and (s2='') and (s3='') and (s4<>'') then
       ss1:='select *  from CELIANG where CLWPLY='+QuotedStr(S4);
////////////
   if (s1<>'') and (s2<>'') and (s3='') and (s4='') then
       ss1:='select *  from CELIANG where CLRIQI='+QuotedStr(S1)+' AND CLBH='+QuotedStr(S2);

   if (s1<>'') and (s2='') and (s3<>'') and (s4='') then
       ss1:='select *  from CELIANG where CLRIQI='+QuotedStr(S1)+' AND CLname='+QuotedStr(S3);

   if (s1<>'') and (s2='') and (s3='') and (s4<>'') then
       ss1:='select *  from CELIANG where CLRIQI='+QuotedStr(S1)+' AND CLWPLY='+QuotedStr(S4);
/////////

   if (s1='') and (s2<>'') and (s3<>'') and (s4='') then
       ss1:='select *  from CELIANG where CLBH='+QuotedStr(S2)+' AND CLname='+QuotedStr(S3);

   if (s1='') and (s2<>'') and (s3='') and (s4<>'') then
       ss1:='select *  from CELIANG where CLBH='+QuotedStr(S2)+' AND CLWPLY='+QuotedStr(S4);

   if (s1='') and (s2='') and (s3<>'') and (s4<>'') then
       ss1:='select *  from CELIANG where CLname='+QuotedStr(S3)+' AND CLWPLY='+QuotedStr(S4);
/////////
   if (s1<>'') and (s2<>'') and (s3<>'') and (s4='') then
       ss1:='select *  from CELIANG where CLRIQI='+QuotedStr(S1)+' AND CLBH='+QuotedStr(S2)+'AND CLname='+QuotedStr(S3);

   if (s1<>'') and (s2<>'') and (s3='') and (s4<>'') then
       ss1:='select *  from CELIANG where CLRIQI='+QuotedStr(S1)+' AND CLBH='+QuotedStr(S2)+'AND CLWPLY='+QuotedStr(S4);

   if (s1<>'') and (s2='') and (s3<>'') and (s4<>'') then
       ss1:='select *  from CELIANG where CLRIQI='+QuotedStr(S1)+' AND CLname='+QuotedStr(S3)+'AND CLWPLY='+QuotedStr(S4);

   if (s1='') and (s2<>'') and (s3<>'') and (s4<>'') then
       ss1:='select *  from CELIANG where CLBH='+QuotedStr(S2)+' AND CLname='+QuotedStr(S3)+'AND CLWPLY='+QuotedStr(S4);

   if (s1<>'') and (s2<>'') and (s3<>'') and (s4<>'') then
       ss1:='select *  from CELIANG where CLRIQI='+QuotedStr(S1)+' AND  CLBH='+QuotedStr(S2)+' AND CLname='+QuotedStr(S3)+'AND CLWPLY='+QuotedStr(S4);
//////////////////
         ADOQuery1.Close;
         ADOQuery1.SQL.Clear;
         ADOQuery1.SQL.Add(s1);
         ADOQuery1.Open;
         ADOQuery1.First;
         i2:=0;

          try
       v:= CreateOleObject('Excel.Application');
       v.Visible :=true;
       v.Workbooks.Add;
       v.Workbooks[1].WorkSheets[1].Name := 'NMR測量查詢數據';
       V.CAPTION:='NMR測量查詢數據';
       V.Range['A1', 'A1'].ColumnWidth := 17;
       V.Range['B1', 'B1'].ColumnWidth := 20;
       V.Range[sheet.cells[1,1],sheet.cells[1,2]].merge; //合并單元格
       V.Range[sheet.cells[1,1],sheet.cells[1,2]].HorizontalAlignment:= xlCenter; // 文本水平居中方式
     //    V.activesheet.rows[2].rowheight := 1/0.035; //設置指定行的高度(單位:磅)(1磅＝0.035厘米),以第二行為例:
       Sheet:= v.Workbooks[1].WorkSheets[1];
       V.cells[1,1].value := 'NMR測量查詢的數據'; //  給單元格賦值
     //  Sheet.Cells[1,1] :='DELPHI盒子';
     //  Sheet.Cells[2,1] :='http://www.delphibox.com';
 //   V.activesheet.range[ 'A1:d4' ].borders.weight := 2;
 // V.activesheet.pagesetup.printgridlines := true;
    //設置第2個工作表為活動工作表      V.worksheets[2].activate;
    //或 V.workssheets[ 'sheet2' ].activate;
      // 設置指定列的寬度(單位:字符個數),以第一列為例



           while not ADOQuery1.Eof do
           begin
             sa1 :=ADOQuery1.FieldByName('TREN').AsString;  // 測量人員名稱
             sa2 :=ADOQuery1.FieldByName('CLname').AsString;  // 測量物品名稱
             sa3 :=ADOQuery1.FieldByName('CLBH').AsString;  // 測量物品編號
             sa4 :=ADOQuery1.FieldByName('CLWPLY').AsString;  //測量物品來源
             sa5 :=ADOQuery1.FieldByName('CLRIQI').AsString;  //測量日期
             sa6 :=ADOQuery1.FieldByName('CZHBDBH').AsString;  //  參照的標定編號
             sa7 :=ADOQuery1.FieldByName('FIDFILE').AsString;  // FID文件名
             sa8 :=ADOQuery1.FieldByName('YHL').AsString;  //  油含量

             Sheet.Cells[2+9*i2,1] :='測量人員名稱';   Sheet.Cells[2+9*i2,2]:=sa1;
             Sheet.Cells[3+9*i2,1] :='測量物品名稱';   Sheet.Cells[3+9*i2,2]:=sa2;
             Sheet.Cells[4+9*i2,1] :='測量物品編號';   Sheet.Cells[4+9*i2,2]:=sa3;
             Sheet.Cells[5+9*i2,1] :='測量物品來源';   Sheet.Cells[5+9*i2,2]:=sa4;
             Sheet.Cells[6+9*i2,1] :='測量日期';       Sheet.Cells[6+9*i2,2]:=sa5;
             Sheet.Cells[7+9*i2,1] :='參照的標定編號'; Sheet.Cells[7+9*i2,2]:=sa6;
             Sheet.Cells[8+9*i2,1] :='FID文件名';      Sheet.Cells[8+9*i2,2]:=sa7;
             Sheet.Cells[9+9*i2,1] :='油含量';         Sheet.Cells[9+9*i2,2]:=sa8;


             i2:=i2+1;
            ADOQuery1.Next;
           end;
           V.activesheet.range[ 'A1:b'+inttostr(9+9*i2) ].borders.weight := 2;

      except
    Showmessage('初始化Excel失敗，可能沒裝Excel，或者其他錯誤；請重起再試。');
    v.DisplayAlerts := false;//是否提示存盤
    v.Quit;//如果出錯則退出
    exit;
  end;


end;

procedure TFrmChaXun.FormCreate(Sender: TObject);
begin
   DateTimePicker1.Date:=date;
end;

end.