unitstudentmanager.pas.~66~

初中成绩录入系统网络版(firebird) firebird下的网络编程

unit UnitStudentManager;

interface

uses
  SysUtils, Windows, Messages, Classes, Graphics, Controls,
  Forms, Dialogs,IBQuery,IBDatabase,unitDataModule,UnitStudent,ADODB,ComObj,Variants;

type
  TStudentManager = class(TObject)
  private
    IBTrans1: TIBTransaction;
    Query1: TIBQuery;
  public
    constructor Create;
    destructor Destroy; override;
    function AddStudent(Student:TStudent): Boolean;
    procedure DelStudent(stdsn,stdname:string);
    function ModifyStudent(stdsn,stdname:string;Student:Tstudent): Boolean;
    procedure importXLS(filename:String);
    function clearStudent():Boolean;
    function ExportStudent(FileName:String):Boolean;
  end;
  

implementation

uses UnitProgress;

{
******************************* TStudentManager ********************************
}
constructor TStudentManager.Create;
begin
  Query1:=TIBQuery.Create(nil);
  IBTrans1:=TIBTransaction.Create(nil);
  Query1.Transaction:=IBTrans1;
  IBTrans1.DefaultDatabase:=dm.IBDB;
  Query1.Database:=dm.IBDB;
end;

destructor TStudentManager.Destroy;
begin
  Query1.Free;
  IBTrans1.Free;
end;

function TStudentManager.AddStudent(Student:TStudent): Boolean;
begin
  Query1.Close;
  Query1.SQL.Text:='Insert into student'+
    '(bmh,stdSn,stdName,school,classRoom,sex,hukou,fangchan,bearing,stdOrder) '+
    'values('''+
    student.bmh+''','''+
    student.stdSn+''','''+
    student.stdName+''','''+
    student.school+''','+
    inttostr(student.classRoom)+','''+
    student.sex+''','''+
    student.hukou+''','''+
    student.fangchan+''','''+
    student.bearing+''','+
    inttostr(student.stdOrder)+')';
  try
    Query1.ExecSQL;
    IBTrans1.Commit;
    result:=true;
  except
    IBTrans1.Rollback;
    result:=false;
  end;
end;

procedure TStudentManager.DelStudent(stdsn,stdname:string);
begin
  Query1.Close;
  Query1.SQL.Text:='delete from student where stdsn='''+stdsn+''' and stdname='''+stdname+'''';
  Query1.ExecSQL;
  IBTrans1.Commit;
end;

function TStudentManager.ModifyStudent(stdsn,stdname:string;Student:Tstudent):
        Boolean;
begin
  Query1.Close;
  Query1.SQL.Text:='update student set '+
    'bmh='''+student.bmh+''','+
    'stdSn='''+student.stdSn+''','+
    'stdName='''+student.stdName+''','+
    'school='''+student.school+''','+
    'classroom='+inttostr(student.classRoom)+','+
    'sex='''+student.sex+''','+
    'hukou='''+student.hukou+''','+
    'fangchan='''+student.fangchan+''','+
    'bearing='''+student.bearing+''','+
    'stdOrder='+inttostr(student.stdOrder)+' '+
    'where stdsn='''+stdsn+''' and stdname='''+stdname+'''';
  try
    Query1.ExecSQL;
    IBTrans1.Commit;
    result:=true;
  except
    IBTrans1.Rollback;
    result:=false;
  end;

end;


procedure TStudentManager.importXLS(filename: String);
var
  Conn2:TADOConnection;
  AdoTable1:TAdoTable;
  tbNames:TStrings;
  temp:string;
begin
  Conn2:=TAdoConnection.Create(nil);
  AdoTable1:=TAdoTable.Create(nil);
  tbNames:=TStringList.Create;
  Conn2.LoginPrompt:=false;
  Conn2.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+Filename+
    ';Extended Properties=Excel 8.0;Persist Security Info=False';
  try
    Conn2.Open();
  except
    Application.MessageBox('文件非法,不能正确打开!','错误',MB_OK or MB_ICONERROR);
    Conn2.Free;
    AdoTable1.Free;
    tbNames.Free;
    exit;
  end;
  AdoTable1.Connection:=Conn2;
  try
    conn2.GetTableNames(tbNames);
    AdoTable1.TableName:=tbNames.Strings[0];
    AdoTable1.TableDirect:=true;
    AdoTable1.Open;
  except
    Application.MessageBox('文件不能正确打开,如果是本软件导出的格式,请先用Excel打开并保存一次!','错误',MB_OK or MB_ICONERROR);
    Conn2.Free;
    AdoTable1.Free;
    tbNames.Free;
    exit;
  end;
  dm.Table1.Close;
  dm.TransTable1.Active:=false;
  dm.Table1.TableName:='STUDENT';
  dm.Table1.Open;
  AdoTable1.First;
  while not AdoTable1.Eof do
  begin
    try
      dm.Table1.Append;
      dm.Table1.FieldByName('BMH').AsString:=AdoTable1.Fields[0].AsString;
      dm.Table1.FieldByName('STDNAME').AsString:=AdoTable1.Fields[1].AsString;
      dm.Table1.FieldByName('STDSN').AsString:=AdoTable1.Fields[2].AsString;
      dm.Table1.FieldByName('KSLB').AsString:=AdoTable1.Fields[3].AsString;
      dm.Table1.FieldByName('SCHOOL').AsString:=AdoTable1.Fields[4].AsString;
      dm.Table1.FieldByName('HUJI').AsString:=AdoTable1.Fields[5].AsString;
      dm.Table1.FieldByName('BIRTH').AsDateTime:=AdoTable1.Fields[6].AsDateTime;
      dm.Table1.FieldByName('MZ').AsString:=AdoTable1.Fields[7].AsString;
      dm.Table1.FieldByName('ADDRESS').AsString:=AdoTable1.Fields[8].AsString;
      dm.Table1.FieldByName('PHONE').AsString:=AdoTable1.Fields[9].AsString;
      dm.Table1.FieldByName('ZGF').AsFloat:=AdoTable1.Fields[10].AsFloat;
      dm.Table1.FieldByName('KJF').AsFloat:=AdoTable1.Fields[11].AsFloat;
      dm.Table1.FieldByName('SEX').AsString:=AdoTable1.Fields[12].AsString;
      dm.Table1.FieldByName('TY').AsString:=AdoTable1.Fields[13].AsString;
      dm.Table1.FieldByName('POST').AsString:=AdoTable1.Fields[14].AsString;
      dm.Table1.Post;
    except
      dm.Table1.Cancel;
      if Application.MessageBox(pchar('发现不合法数据,报名号:'+AdoTable1.Fields[0].AsString+
        ',行数:'+Inttostr(AdoTable1.RecNo)+#13+'是否继续?'),'警告',MB_YESNO or MB_ICONWARNING)<>IDYES then
          break;
    end;
    AdoTable1.Next;
  end;
  try
    dm.TransTable1.Commit;
  except
    dm.TransTable1.Rollback;
  end;
  AdoTable1.Close;
  Conn2.Free;
  AdoTable1.Free;
  tbNames.Free;
end;

function TStudentManager.clearStudent:Boolean;
begin
  if Application.MessageBox('此操作将清除所有学生数据,是否继续?','请确认',MB_OKCANCEL or MB_ICONWARNING)=IDOK then
  begin
    Query1.Close;
    Query1.SQL.Text:='delete from student';
    Query1.ExecSQL;
    IBTrans1.Commit;
    result:=true;
  end
  else
    result:=false;
end;

function TStudentManager.ExportStudent(FileName: String): Boolean;
var
  ExcelApp,WorkBook1:Variant;
  i,cnt:Integer;
  fmProgress:TfmProgress;
begin
  try
    ExcelApp:=CreateOLEObject('Excel.Application');
    workBook1:=CreateOLEObject('Excel.Sheet');
  except
    Application.MessageBox('初始化Excel失败,请确认系统安装了Excel','错误',MB_OK or MB_ICONERROR);
    result:=false;
    exit;
  end;
  workBook1:=ExcelApp.WorkBooks.Add;
  IBTrans1.Active:=false;
  Query1.Close;
  Query1.SQL.Text:='select count(*) as cnt from student';
  Query1.Open;
  cnt:=Query1.FieldByName('cnt').AsInteger;
  fmProgress:=TfmProgress.Create(nil);
  fmProgress.ProgressBar1.Max:=cnt;
  fmProgress.Show;
  fmProgress.Update;
  Query1.Close;
  Query1.SQL.Text:='select school,zkzh,stdname,substring(zkzh from 5 for 2),substring(zkzh from 7 for 2),a.bmh from '+
        'student order by zkzh';
  Query1.Open;

  workBook1.workSheets[1].Cells[1,1]:='学校';
  workBook1.workSheets[1].Cells[1,2]:='准考证号';
  workBook1.workSheets[1].Cells[1,3]:='姓名';
  workBook1.workSheets[1].Cells[1,4]:='考室';
  workBook1.workSheets[1].Cells[1,5]:='座位';
  workBook1.workSheets[1].Cells[1,6]:='报名号';
  Query1.First;
  i:=2;
  while not Query1.Eof do
  begin
    workBook1.workSheets[1].Cells[i,1]:=''''+Query1.Fields[0].AsString;
    workBook1.workSheets[1].Cells[i,2]:=''''+Query1.Fields[1].AsString;
    workBook1.workSheets[1].Cells[i,3]:=''''+Query1.Fields[2].AsString;
    workBook1.workSheets[1].Cells[i,4]:=''''+Query1.Fields[3].AsString;
    workBook1.workSheets[1].Cells[i,5]:=''''+Query1.Fields[4].AsString;
    workBook1.workSheets[1].Cells[i,6]:=''''+Query1.Fields[5].AsString;
    i:=i+1;
    Query1.Next;
    fmProgress.ProgressBar1.StepIt;
  end;
  try
    workBook1.SaveAs(Filename);
    workBook1.Close;
    ExcelApp.Quit;
    ExcelApp:=UnAssigned;
    result:=true;
  except
    result:=false;
  end;
  freeAndNil(fmProgress);
end;

end.