unit Unit5;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGrids, ExtCtrls, DB,ADODB, StdCtrls, DBCtrls, Mask,
  Buttons,comobj;

type
  TForm00 = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    DataSource1: TDataSource;
    ADOQuery1: TADOQuery;
    DBGrid1: TDBGrid;
    ADOQuery2: TADOQuery;
    ADOQuery3: TADOQuery;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    banji: TComboBox;
    bj: TComboBox;
    Label3: TLabel;
    gjz: TEdit;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    BitBtn1: TBitBtn;
    BitBtn4: TBitBtn;
    procedure FormCreate(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure banjiChange(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);
    procedure BitBtn2Click(Sender: TObject);
  private
  procedure CopyDbDataToExcel(Args: array of const);
    { Private declarations }
  public

    { Public declarations }
  end;

var
  Form00: TForm00;
 sqlstring:string;
  fields_name:array[0..10] of string;
  i,fields_lenth:integer;
 implementation

uses Unit1, Unit14;

{$R *.dfm}
procedure TForm00.CopyDbDataToExcel(Args: array of const);
var
iCount, jCount: Integer; 
XLApp: Variant; 
Sheet: Variant; 
I: Integer; 
begin 
Screen.Cursor := crHourGlass; 
if not VarIsEmpty(XLApp) then 
begin 
XLApp.DisplayAlerts := False; 
XLApp.Quit; 
VarClear(XLApp); 
end; 

try 
XLApp := CreateOleObject('Excel.Application');
except 
Screen.Cursor := crDefault; 
Exit; 
end; 

XLApp.WorkBooks.Add; 
XLApp.SheetsInNewWorkbook := High(Args) + 1; 

for I := Low(Args) to High(Args) do 
begin 
XLApp.WorkBooks[1].WorkSheets[I+1].Name := TDBGrid(Args[I].VObject).Name; 
Sheet := XLApp.Workbooks[1].WorkSheets[TDBGrid(Args[I].VObject).Name]; 

if not TDBGrid(Args[I].VObject).DataSource.DataSet.Active then 
begin 
Screen.Cursor := crDefault; 
Exit; 
end; 

TDBGrid(Args[I].VObject).DataSource.DataSet.first; 
for iCount := 0 to TDBGrid(Args[I].VObject).Columns.Count - 1 do 
Sheet.Cells[1, iCount + 1] := 
TDBGrid(Args[I].VObject).Columns.Items[iCount].Title.Caption; 

jCount := 1; 
while not TDBGrid(Args[I].VObject).DataSource.DataSet.Eof do 
begin 
for iCount := 0 to TDBGrid(Args[I].VObject).Columns.Count - 1 do 
Sheet.Cells[jCount + 1, iCount + 1] := 
TDBGrid(Args[I].VObject).Columns.Items[iCount].Field.AsString; 

Inc(jCount); 
TDBGrid(Args[I].VObject).DataSource.DataSet.Next; 
end; 
XlApp.Visible := True; 
end; 
Screen.Cursor := crDefault; 
end; 



procedure TForm00.FormCreate(Sender: TObject);
var
data_path:string;
begin
 data_path:=application.GetNamePath+'user/'+form1.user+'/'+'班級.mdb';
 adoquery2.connectionstring:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+data_path+';';
 sqlstring:='select * from 班級表';
  banji.Clear;
  with adoquery2 do
    begin
      close;
      sql.Clear;
      sql.Add(sqlstring);
      open;
      while not eof do
         begin
           banji.Items.Add(fieldbyname('名稱').asstring);
            next;
         end;
    end;
end;

procedure TForm00.BitBtn1Click(Sender: TObject);
begin
CopyDbDataToExcel([DBGrid1]);
end;

procedure TForm00.banjiChange(Sender: TObject);
var
data_path1:string;
begin
  bj.Clear;
  data_path1:=application.GetNamePath+'user/'+form1.user+'/'+trim(banji.Text)+'.mdb';
  adoquery1.connectionstring:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+data_path1+';';
  sqlstring:='select * from 學(xué)生信息表';
   with  ADOQuery1 do
  begin
  close;
  sql.Clear;
  sql.Add(sqlstring);
  open;
  end;
  //form1.editdata(adoquery1,sqlstring,0);
  for i:=0 to ADOQuery1.Recordset.Fields.Count-1 do //  讀取字段名
  begin
  bj.Items.Add(ADOQuery1.Recordset.Fields[i].name);
  fields_name[i]:=ADOQuery1.Recordset.Fields[i].name;
  fields_lenth:=fields_lenth+1;
  end;
end;

procedure TForm00.BitBtn4Click(Sender: TObject);
begin
close;
end;

procedure TForm00.DBGrid1CellClick(Column: TColumn);
var

str:string;
xh:string;
begin
{with adoquery1 do
begin
str:='學(xué)生基本信息'+char(13);
str:=str+'======================================='+char(13);
str:=str+'學(xué)號：'+fieldbyname('學(xué)號').AsString+char(13);
str:=str+'姓名：'+fieldbyname('姓名').AsString+char(13);
str:=str+'性別：'+fieldbyname('性別').AsString+char(13);
str:=str+'生日：'+fieldbyname('生日').AsString+char(13);
str:=str+'地址：'+fieldbyname('地址').AsString+char(13);
str:=str+'QQ號：'+fieldbyname('QQ號').AsString+char(13);
str:=str+'email:'+fieldbyname('email').AsString+char(13);
xh:=fieldbyname('學(xué)號').AsString;
form1.bj:=trim(banji.Text);
end;
str:=str+'======================================='+char(13);
str:=str+'獎(jiǎng)勵(lì)情況:'+char(13);
adoquery3.connectionstring:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+data_path1+';';
 sqlstring:='select * from 獎(jiǎng)勵(lì)表 where 學(xué)號=:xh';
 with  adoquery3 do
  begin
  close;
  sql.Clear;
  sql.Add(sqlstring);
  Parameters.ParamByName('xh').Value:=xh;
  open;
  first;
  while not eof do
  begin
  str:=str+'第'+fieldbyname('學(xué)期').AsString+'學(xué)期：'+fieldbyname('內(nèi)容').AsString;
  next;
  end;
end;
str:=str+'======================================='+char(13);
str:=str+'處罰情況:'+char(13);
sqlstring:='select * from 懲罰表 where 學(xué)號=:xh';
 with  adoquery3 do
  begin
  close;
  sql.Clear;
  sql.Add(sqlstring);
  Parameters.ParamByName('xh').Value:=xh;
  open;
  first;
  while not eof do
  begin
  str:=str+'第'+fieldbyname('學(xué)期').AsString+'學(xué)期：'+fieldbyname('內(nèi)容').AsString;;
  next;
  end;
end;

 showmessage(str);

//form14:=Tform14.create(self);
   //form14.showmodal;
   //form14.free;   }
end;

procedure TForm00.BitBtn2Click(Sender: TObject);
var
str:string;
begin
for i:=0 to fields_lenth-1 do
    begin
      str:='select * from  學(xué)生信息表 where ';
      if (i=bj.ItemIndex) then
         begin
            str:=str+fields_name[i]+' like :keywords';
            break;
         end
      else
         continue;
     end;
     with ADOQuery1 do
       begin
         close;
         sql.Clear;
         sql.Add(str);
         Parameters.ParamByName('keywords').Value:='%'+trim(gjz.Text)+'%';  //定義參數(shù)
         open;
        end;
end;

end.