registerbook.~pas

一个在Delphi中操作控制excel的控件。操作方便

unit RegisterBook;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Db, DBTables, Grids, DBGrids, comobj,Buttons;

type
  TForm1 = class(TForm)
    GroupBox1: TGroupBox;
    Label1: TLabel;
    ComboBox1: TComboBox;
    Query1: TQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Label2: TLabel;
    ComboBox2: TComboBox;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    Query2: TQuery;
    BitBtn3: TBitBtn;
    procedure FormCreate(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure ComboBox2Change(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
  private
  excel:variant;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;
   year,month,day:string;
implementation

{$R *.DFM}

procedure TForm1.FormCreate(Sender: TObject);
var
  strpath:string;
 begin
 combobox1.clear;
 combobox2.clear;
  shortdateformat:='yyyy-mm-dd';
  strpath:=extractfilepath(application.exename);
  query1.databasename:=strpath;//+'\TemperHumid.DB';
  query2.databasename:=strpath;
  with query1 do
   begin
    query1.close;
    sql.clear;
    sql.add('select TemperHumid.* from TemperHumid');
    query1.open;
    while not eof do
     begin
      year:=copy(datetostr(fieldbyname('datetime').asdatetime),1,4);
       if combobox2.items.indexof(year)=-1 then
          combobox2.items.add(year);
      next;
    end;
    first;
   end;
  combobox2.ItemIndex:=0;
  with query2 do
    begin
     query2.close;
     sql.clear;
     sql.add('select TemperHumid.* from TemperHumid  WHERE (EXTRACT( Year FROM datetime) =:yy )')  ;
     query2.Params[0].asinteger:=strtoint(combobox2.text);
     query2.open;
     while not eof do
     begin
       month:=copy(datetostr(fieldbyname('datetime').asdatetime),6,2);
       if combobox1.items.indexof(month)=-1 then
          combobox1.items.add(month);
      next;
    end;
    end;
    combobox1.ItemIndex:=0;
end;

procedure TForm1.BitBtn2Click(Sender: TObject);
begin
 close;
end;

procedure TForm1.ComboBox2Change(Sender: TObject);
begin
combobox1.clear;
 with query2 do
    begin
     query2.close;
     sql.clear;
     sql.add('select TemperHumid.* from TemperHumid  WHERE (EXTRACT( Year FROM datetime) =:yy )')  ;
     query2.Params[0].asinteger:=strtoint(combobox2.text);
     query2.open;
     while not eof do
     begin
       month:=copy(datetostr(fieldbyname('datetime').asdatetime),6,2);
       if combobox1.items.indexof(month)=-1 then
          combobox1.items.add(month);
      next;
    end;
    end;
    combobox1.ItemIndex:=0;
end;

procedure TForm1.BitBtn3Click(Sender: TObject);
begin
 with query1 do
    begin
     query1.close;
     sql.clear;
     sql.add('select TemperHumid.* from TemperHumid  WHERE (EXTRACT( Year FROM datetime) =:yy ) and (EXTRACT( month FROM datetime) =:mm )')  ;
     query1.Params[0].asinteger:=strtoint(combobox2.text);
     query1.Params[1].asinteger:=strtoint(combobox1.text);
     query1.open;
     end;
end;

procedure TForm1.BitBtn1Click(Sender: TObject);
var
 WorkBook, Sheet: Variant;
 Col, Row,iRow,iCol,i : Integer;
 strpath:string;
begin
 try
      Excel:=CreateOleObject('Excel.Application');
       excel.visible:=true;
      WorkBook:=CreateOleobject('Excel.Sheet');
    except
      application.Messagebox('无法打开Xls文件，请确认已 经安装EXCEL.','',
           mb_OK+mb_IconStop);
      Exit;
    end;
      workBook:= Excel.workBooks.Open(ExtractFilepath(Application.ExeName)+'BookIn.xls');
      excel.worksheets['sheet1'].activate;
   with query2 do
    begin
     query2.close;
     sql.clear;
     sql.add('select TemperHumid.* from TemperHumid  WHERE (EXTRACT( Year FROM datetime) =:yy ) and (EXTRACT( month FROM datetime) =:mm ) order by datetime ')  ;
     query2.Params[0].asinteger:=strtoint(combobox2.text);
     query2.Params[1].asinteger:=strtoint(combobox1.text);
     query2.open;
     query2.first;
    while not eof do
     begin
     day:=copy(datetostr(fieldbyname('datetime').asdatetime),9,2);
      row:=-2;
     iCol:=4+strtoint(day)-12;
     col:=icol;
    Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('weather').asinteger);
     inc(row,1);
     Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('relativehumidity-first').asinteger);
      inc(row,1);
     Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('relativehumidity-last').asinteger);
      inc(row,1);
     Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('relativehumidity-avg').asinteger);
      inc(row,1);
      Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('absolutehumidity-first').asinteger);
      inc(row,1);
     Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('absolutehumidity-last').asinteger);
      inc(row,1);
    Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('absolutehumidity-avg').asinteger);
      inc(row,1);
       Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('zero-first').asinteger);
      inc(row,1);
     Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('zero-last').asinteger);
      inc(row,1);
       Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('aeration-first').asinteger);
      inc(row,1);
     Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('aeration-last').asinteger);
      inc(row,1);
     Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('aeration-times').asinteger);
      inc(row,1);
      Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('aerationbefor-tem').asinteger);
      inc(row,1);
     Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('aerationbefor-hum').asinteger);
      inc(row,1);
     Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('aerationbefor-abshum').asinteger);
      inc(row,1);
     Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('AerationAfert-tem').asinteger);
      inc(row,1);
     Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('AerationAfert-hum').asinteger);
      inc(row,1);
     Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('AerationAfert-abshum').asinteger);
      inc(row,1);
      Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('MoistureAbsorption-mac').asinteger);
      inc(row,1);
     Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('MoistureAbsorption-dos').asinteger);
      inc(row,1);
     Excel.ActiveCell.Cells[Row, Col].value :=inttostr(fieldbyname('MoistureAbsorption-wat').asinteger);
      next;
      end;
    end;
end;

end.