unitachieve.pas.~192~

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

  workBook1.workSheets[1].Cells[1,11]:='历史';
  workBook1.workSheets[1].Cells[1,12]:='总分';
  workBook1.workSheets[1].Cells[1,13]:='照顾分';
  workBook1.workSheets[1].Cells[1,14]:='扣减分';
  workBook1.workSheets[1].Cells[1,15]:='投档分';
  workBook1.workSheets[1].Cells[1,16]:='操行';
  workBook1.workSheets[1].Cells[1,17]:='地理';
  workBook1.workSheets[1].Cells[1,18]:='生物';
  workBook1.workSheets[1].Cells[1,19]:='实验';
  workBook1.workSheets[1].Cells[1,20]:='体育';
  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;
    for j:=5 to Query1.FieldCount do
      workBook1.workSheets[1].Cells[i,j]:=Query1.Fields[j-1].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;

procedure TAchieve.generateZKZH;
var
  schorder,kaoshi,zuowei:string;
  i,j:Integer;
  IBUpdateSQL1:TIBUpdateSQL;
begin
  IBUpdateSqL1:=TIBUpdateSQL.Create(nil);
  Query1.UpdateObject:=IBUpdateSQL1;
  IBUpdateSQL1.ModifySQL.Clear;
  IBUpdateSQL1.ModifySQL.Add('update student set zkzh=:zkzh where bmh=:OLD_bmh');
  IBTrans1.Active:=false;
  Query1.SQL.Text:='select bmh,zkzh from student';
  Query1.Open;
  query1.First;
  randomize();
  while not query1.Eof do
  begin
    Query1.Edit;
    Query1.FieldByName('zkzh').AsString:=inttostr(random(5000));
    Query1.Post;
    Query1.Next;
  end;
  try
    IBTrans1.Commit;
  except
    IBTrans1.Rollback;
  end;
  dm.Trans1.Active:=false;
  dm.Query1.Close;
  dm.Query1.SQL.Text:='select schorder from school order by schorder';
  dm.Query1.Open;
  dm.Query1.First;
  while not dm.Query1.Eof do
  begin
    schorder:=dm.Query1.FieldByName('schorder').AsString;
    IBTrans1.Active:=false;
    Query1.Close;
    Query1.SQL.Text:='select zkzh,bmh from student where '+
      'substring(bmh from 1 for 4)='''+schorder+''' order by zkzh';
    query1.Open;
    query1.First;
    i:=1;//考室编号
    j:=1; //座位号
    while not query1.Eof do
    begin
      if i<10 then
        kaoshi:='0'+inttostr(i)
      else
        kaoshi:=inttostr(i);
      if j<10 then
        zuowei:='0'+inttostr(j)
      else
        zuowei:=inttostr(j);
      Query1.Edit;
      query1.FieldByName('zkzh').AsString:=CurYear+schorder+kaoshi+zuowei;
      Query1.Post;
      j:=j+1;
      if j>30 then
      begin
        j:=1;
        i:=i+1;//增加一个考室
      end;
      Query1.Next;
    end;
    try
      IBTrans1.Commit;
    except
      IBTrans1.Rollback;
    end;
    dm.Query1.Next;
  end;
  IBTrans1.Active:=false;
  Query1.Close;
  Query1.SQL.Text:='delete from ach1';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='delete from ach2';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='insert into ach1(zkzh) select zkzh from student';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='insert into ach2(zkzh) select zkzh from student';
  Query1.ExecSQL;
  IBTrans1.Commit;
  IBUpdateSQL1.Free;
end;

function TAchieve.ImportJncj(FileName: String): Boolean;
var
  ExcelApp,workSheet1:Variant;
  i,TotalRows:Integer;
  fmProgress:TfmProgress;
begin
  //电子表格格式:报考号,操行,地理,生物,实验操作,体育
  result:=true;
  try
    ExcelApp:=CreateOLEObject('Excel.Application');
//    workSheet1:=CreateOLEObject('Excel.Sheet');
  except
    Application.MessageBox('初始化Excel失败,请确认系统安装了Excel','错误',MB_OK or MB_ICONERROR);
    result:=false;
    exit;
  end;
  try
    ExcelApp.workBooks.Open(FileName);
  except
    ExcelApp.Quit;
    showmessage('非法Excel文件');
    result:=false;
    exit;
  end;
  workSheet1:=ExcelApp.WorkBooks[1].workSheets[1];

  TotalRows:=workSheet1.rows.Count;
  fmProgress:=TfmProgress.Create(nil);
  fmProgress.ProgressBar1.Max:=TotalRows;
  dm.Trans1.Active:=false;
  for i:=2 to totalRows do
  begin
    if varAsType(workSheet1.Cells[i,1],varString)='' then
      break;//数据已结束
    dm.Query1.Close;
    dm.Query1.SQL.Text:='update student set '+
      'caoxing='''+varAsType(workSheet1.Cells[i,2],varString)+''','+
      'dili='''+varAsType(workSheet1.Cells[i,3],varString)+''','+
      'shengwu='''+varAsType(workSheet1.Cells[i,4],varString)+''','+
      'shiyan='''+varAsType(workSheet1.Cells[i,5],varString)+''','+
      'tiyu='''+varAsType(workSheet1.Cells[i,6],varString)+''' '+
      'where bmh='''+varAsType(workSheet1.Cells[i,1],varString)+'''';
    dm.Query1.ExecSQL;
    fmProgress.ProgressBar1.StepIt;
  end;
  try
    dm.Trans1.Commit;
  except
    dm.Trans1.Rollback;
    result:=false;
  end;
  freeAndNil(fmProgress);
end;

function TAchieve.ImportZGF(FileName: String): Boolean;
var
  ExcelApp,workSheet1:Variant;
  i,TotalRows:Integer;
  fmProgress:TfmProgress;
begin
  //电子表格格式:报考号,操行,地理,生物,实验操作,体育
  result:=true;
  try
    ExcelApp:=CreateOLEObject('Excel.Application');
//    workSheet1:=CreateOLEObject('Excel.Sheet');
  except
    Application.MessageBox('初始化Excel失败,请确认系统安装了Excel','错误',MB_OK or MB_ICONERROR);
    result:=false;
    exit;
  end;
  try
    ExcelApp.workBooks.Open(FileName);
  except
    ExcelApp.Quit;
    showmessage('非法Excel文件');
    result:=false;
    exit;
  end;
  workSheet1:=ExcelApp.WorkBooks[1].workSheets[1];

  TotalRows:=workSheet1.rows.Count;
  fmProgress:=TfmProgress.Create(nil);
  fmProgress.ProgressBar1.Max:=TotalRows;
  dm.Trans1.Active:=false;
  for i:=2 to totalRows do
  begin
    if varAsType(workSheet1.Cells[i,1],varString)='' then
      break;//数据已结束
    dm.Query1.Close;
    dm.Query1.SQL.Text:='update student set '+
      'bxzgf='''+varAsType(workSheet1.Cells[i,2],varString)+''','+ //本县照顾分
      'tyzgf='''+varAsType(workSheet1.Cells[i,3],varString)+''' '+ //通用照顾分
      'where bmh='''+varAsType(workSheet1.Cells[i,1],varString)+'''';
    dm.Query1.ExecSQL;
    fmProgress.ProgressBar1.StepIt;
  end;
  try
    dm.Trans1.Commit;
  except
    dm.Trans1.Rollback;
    result:=false;
  end;
  freeAndNil(fmProgress);
end;

function TAchieve.ImportZkzh(FileName: String): Boolean;
var
  ExcelApp,workSheet1:Variant;
  i,TotalRows:Integer;
  fmProgress:TfmProgress;
begin
  //电子表格的第一列为报考号,第二列为准考证号
  result:=true;
  try
    ExcelApp:=CreateOLEObject('Excel.Application');
//    workSheet1:=CreateOLEObject('Excel.Sheet');
  except
    Application.MessageBox('初始化Excel失败,请确认系统安装了Excel','错误',MB_OK or MB_ICONERROR);
    result:=false;
    exit;
  end;
  try
    ExcelApp.workBooks.Open(FileName);
  except
    ExcelApp.Quit;
    showmessage('非法Excel文件');
    result:=false;
    exit;
  end;
  workSheet1:=ExcelApp.WorkBooks[1].workSheets[1];
  //清除原有考生的成绩信息

  Query1.Close;
  Query1.SQL.Text:='delete from ach1';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='delete from ach2';
  Query1.ExecSQL;
  Query1.Close;
  Query1.SQL.Text:='update student set zkzh=null';
  Query1.ExecSQL;
  IBTrans1.Commit;

  TotalRows:=workSheet1.rows.Count;
  fmProgress:=TfmProgress.Create(nil);
  fmProgress.ProgressBar1.Max:=TotalRows;
  dm.Trans1.Active:=false;
  for i:=2 to totalRows do
  begin
    if varAsType(workSheet1.Cells[i,1],varString)='' then
      break;//数据已结束
    dm.Query1.Close;
    dm.Query1.SQL.Text:='update student set zkzh='''+
      varAsType(workSheet1.Cells[i,2],varString)+
      ''' where bmh='''+varAsType(workSheet1.Cells[i,1],varString)+'''';
    dm.Query1.ExecSQL;
    fmProgress.ProgressBar1.StepIt;
  end;
  try
    dm.Trans1.Commit;
  except
    dm.Trans1.Rollback;
    result:=false;
  end;
  IBTrans1.Active:=false;
  try
    Query1.SQL.Text:='insert into ach1(zkzh) select zkzh from student';
    Query1.ExecSQL;
    Query1.SQL.Text:='insert into ach2(zkzh) select zkzh from student';
    Query1.ExecSQL;
    IBTrans1.Commit;
  except
    Application.MessageBox('产生成绩录入表错误!原因为部分考生缺少准考证号!','错误',MB_OK or MB_ICONERROR);
    IBTrans1.Rollback;
    result:=false;
  end;
  try
    ExcelApp.Quit;
    ExcelApp:=UnAssigned;
  except
    result:=false;
  end;
  freeAndNil(fmProgress);
end;

function TAchieve.ImportZyxx(FileName: String): Boolean;
var
  ExcelApp,workSheet1:Variant;
  i,TotalRows:Integer;
  fmProgress:TfmProgress;
begin
  //电子表格格式:报考号,高志1,高志2,高志3,高志4,五志1,五志2,中志1,中志2,中志3
  result:=true;
  try
    ExcelApp:=CreateOLEObject('Excel.Application');
//    workSheet1:=CreateOLEObject('Excel.Sheet');
  except
    Application.MessageBox('初始化Excel失败,请确认系统安装了Excel','错误',MB_OK or MB_ICONERROR);
    result:=false;
    exit;
  end;
  try
    ExcelApp.workBooks.Open(FileName);
  except
    ExcelApp.Quit;
    showmessage('非法Excel文件');
    result:=false;
    exit;
  end;
  workSheet1:=ExcelApp.WorkBooks[1].workSheets[1];

  TotalRows:=workSheet1.rows.Count;
  fmProgress:=TfmProgress.Create(nil);
  fmProgress.ProgressBar1.Max:=TotalRows;
  dm.Trans1.Active:=false;
  for i:=2 to totalRows do
  begin
    if varAsType(workSheet1.Cells[i,1],varString)='' then
      break;//数据已结束
    dm.Query1.Close;
    dm.Query1.SQL.Text:='update student set '+
      'gaozhi1='''+varAsType(workSheet1.Cells[i,2],varString)+''','+
      'gaozhi2='''+varAsType(workSheet1.Cells[i,3],varString)+''','+
      'gaozhi3='''+varAsType(workSheet1.Cells[i,4],varString)+''','+
      'gaozhi4='''+varAsType(workSheet1.Cells[i,5],varString)+''','+
      'wuzhi1='''+varAsType(workSheet1.Cells[i,6],varString)+''','+
      'wuzhi2='''+varAsType(workSheet1.Cells[i,7],varString)+''','+
      'zhongzhi1='''+varAsType(workSheet1.Cells[i,8],varString)+''','+
      'zhongzhi2='''+varAsType(workSheet1.Cells[i,9],varString)+''','+
      'zhongzhi3='''+varAsType(workSheet1.Cells[i,10],varString)+''' '+
      'where bmh='''+varAsType(workSheet1.Cells[i,1],varString)+'''';
    dm.Query1.ExecSQL;
    fmProgress.ProgressBar1.StepIt;
  end;
  try
    dm.Trans1.Commit;
  except
    dm.Trans1.Rollback;
    result:=false;
  end;
  freeAndNil(fmProgress);
end;

end.