unit Unit10;

interface

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

type
  TForm10 = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    Label1: TLabel;
    bj: TComboBox;
    Label2: TLabel;
    tj: TComboBox;
    Label3: TLabel;
    gjz: TEdit;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    BitBtn4: TBitBtn;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    Label4: TLabel;
    xq: TComboBox;
    procedure FormCreate(Sender: TObject);
    procedure xqChange(Sender: TObject);
    procedure bjChange(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
  private
    { Private declarations }
      procedure CopyDbDataToExcel(Args: array of const);

  public
    { Public declarations }
  end;

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

uses Unit1;

{$R *.dfm}
procedure Tform10.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 TForm10.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 班級表';
  bj.Clear;
  with ADOQuery2 do
    begin
      close;
      sql.Clear;
      sql.Add(sqlstring);
      open;
      while not eof do
         begin
           bj.Items.Add(fieldbyname('名稱').asstring);
            next;
         end;
    end;


  

end;

procedure TForm10.xqChange(Sender: TObject);
var
data_path1:string;
j:integer;
zf,pjf:double;
begin
  zf:=0;
  pjf:=0;
  data_path1:=application.GetNamePath+'user/'+form1.user+'/'+trim(bj.Text)+'.mdb';
  ADOQuery1.connectionstring:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+data_path1+';';
  sqlstring:='select * from 第'+trim(xq.Text)+'學期成績表';
  fields_lenth:=0;
 { with adoquery2 do
  begin
  first;
  while not eof do
  begin
  for j:=3 to  ADOQuery2.Recordset.Fields.Count-3 do //  讀取字段名
  begin
  //if  ADOQuery2.Fields[i].asfloat<>0 then
  zf:=zf+ADOQuery2.Fields[j].asfloat;
  end;
  showmessage(floattostr(zf));
  pjf:=zf/(ADOQuery2.Recordset.Fields.Count-3-2);
  sqlstring:='update  第'+trim(xq.Text)+'學期成績表 set 平均分=:pjf,總分=:zf';
  with  ADOQuery2 do
  begin
  close;
  sql.Clear;
  sql.Add(sqlstring);
  Parameters.ParamByName('pjf').Value:=zf;
  Parameters.ParamByName('zf').Value:=pjf;
  execsql;
  end;
  next;
  end;
  end;  }
  with  ADOQuery1 do
  begin
  close;
  sql.Clear;
  sql.Add(sqlstring);
  open;
  close;
  sql.Clear;
  sql.Add(sqlstring);
  open;
  end;
  for i:=0 to ADOQuery1.Recordset.Fields.Count-1 do //  讀取字段名
  begin
  tj.Items.Add(ADOQuery1.Recordset.Fields[i].name);
  fields_name[i]:=ADOQuery1.Recordset.Fields[i].name;
  fields_lenth:=fields_lenth+1;
  end;
end;
procedure TForm10.bjChange(Sender: TObject);
var
i,xuezhi:integer;
begin
xq.Clear;
sqlstring:='select * from 班級表 where 名稱=:mc';
with ADOQuery2 do
    begin
      close;
      sql.Clear;
      sql.Add(sqlstring);
      Parameters.ParamByName('mc').Value:=trim(bj.Text);
      open;
      xuezhi:=fieldbyname('學制').AsInteger;
       end;
    for i:=1 to xuezhi*2 do
     xq.Items.Add(inttostr(i));
end;

procedure TForm10.BitBtn1Click(Sender: TObject);
var
str:string;
begin
for i:=0 to fields_lenth-1 do
    begin
      str:='select * from  第'+trim(xq.Text)+'學期成績表 where ';
      if (i=tj.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)+'%';  //定義參數
         open;
        end;
end;

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

procedure TForm10.BitBtn4Click(Sender: TObject);
begin
CopyDbDataToExcel([DBGrid1]);
end;

procedure TForm10.BitBtn3Click(Sender: TObject);
begin
print;
end;

end.