zhunit.~pas

初学DELPHI编写的“奖学金评定系统”

          dir:= FolderDF+ '\'+ zy[j]+ '.xls';
        {excel初始化}
          ExcelApplication := TExcelApplication.Create(nil);
          ExcelWorkbook := TExcelWorkbook.Create(nil);
          Excelworksheet := TExcelWorksheet.Create(nil);
          ExcelApplication.AutoConnect := False;
          ExcelApplication.AutoQuit := True;
          ExcelApplication.ConnectKind := ckNewInstance;
          {excel初始化完毕}
          ExcelApplication.Connect;
          ExcelWorkbook.ConnectTo( ExcelApplication.Workbooks._Open(dir,EmptyParam,
            EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,
            EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0));
          excelworksheet.ConnectTo(excelworkbook.Worksheets[1] as _worksheet);

          count:= 0;
          temp:= excelworksheet.cells.item[count+ 1,1];
          while length(temp)<> 0 do begin
            count:= count+ 1;
            temp:= excelworksheet.cells.item[count+ 1,1];
          end;
          progressBar1.Max:= count- 1;

          i:= 1;
          xhIndex:= 0;
          dyIndex:= 0;
          zhIndex:= 0;
          temp:= excelworksheet.cells.item[1,i];
          while length(temp)<> 0 do begin
            if temp= '学号' then
              xhIndex:= i;
            if temp= '德育' then
              dyIndex:= i;
            if temp= '综合' then
              zhIndex:= i;
            i:= i+ 1;
            temp:= excelworksheet.cells.item[1,i];
          end;

          i:= 2;
          temp:= excelworksheet.cells.item[i,1];
          while length(temp)<> 0 do begin
            tmp[i- 2]:= excelworksheet.cells.item[i,xhIndex];
            tp[i- 2]:= excelworksheet.cells.item[i,dyIndex];
            t[i- 2]:= excelworksheet.cells.item[i,zhIndex];
            i:= i+ 1;
            temp:= excelworksheet.cells.item[i,1];
          end;
          row:= i- 1;

      {退出前清空excel有关变量}
          excelworksheet.Disconnect;
          excelworkbook.Disconnect;
          excelapplication.Disconnect;
          excelworksheet.Free;
          excelworkbook.Free;
          excelapplication.Quit;
          excelapplication.Free;
      {退出前清空excel有关变量}
        richedit1.Lines.Add(timetostr(time)+zy[j]);
        update;
          for i:= 0 to row- 2 do begin
            progressBar1.Position:= i+ 1;
            update;
            first;
            while not eof do begin
              if fieldbyname('学号').AsString= tmp[i] then begin
                edit;
                fieldbyname('德育得分').AsString:= tp[i];
                fieldbyname('综合能力得分').AsString:= t[i];
                post;
                next;
              end
              else
                next;
            end;
          end;
          first;
        end;
      end;
      WriteIni();
      result:= true;
    finally
      richedit1.Lines.Add(timetostr(time)+'德育综合信息录入完成');
      StatusBar1.Panels[1].Text:= '德育综合信息录入完成';
      update;
    end;
  end;
end;

function AddData():boolean;export;
var
  i,j: byte;
  f: real;
begin
  result:= false;
  with MainForm do begin
  try
    readini(false);
    visible:= true;
    //screen.Cursor:= crhourglass;
    richedit1.Lines.Add(timetostr(time)+'成绩后期处理中');
    StatusBar1.Panels[1].Text:= '成绩后期处理中';
    update;
    richedit1.Lines.Add(timetostr(time)+'加入综测成绩');
    progressbar1.Max:= zyzrs;
    progressbar1.Position:= 0;
    update;
    with adoquery1 do begin
      sql.Clear;
      sql.Add('select 学号,姓名,专业班级,智育成绩,德育得分,综合能力得分,体育成绩,综合测评成绩 from 学籍成绩');
      open;
      while not eof do begin
        progressbar1.Position:= progressbar1.Position+ 1;
        update;
        edit;
        fieldbyname('综合测评成绩').Asfloat:= roundto((fieldbyname('智育成绩').AsFloat* propzy+
          fieldbyname('德育得分').AsFloat* propdy+
          fieldbyname('综合能力得分').AsFloat* propzh+
          fieldbyname('体育成绩').AsFloat* propty), -4);
        post;
        next;
      end;

      for i:= 1 to zys do begin
        sql.Clear;
        sql.Add('select * from 学籍成绩 where 专业 = "'+ zy[i]+ '"');
        open;
        sort:= '智育成绩 DESC';
        j:= 1;
        first;
        progressbar1.Position:= 0;
        richedit1.Lines.Add(timetostr(time)+'加入智育百分比: '+ zy[i]);
        progressbar1.Max:= zyrs[i];
        update;
        while not eof do begin
          progressbar1.Position:= progressbar1.Position+ 1;
          update;
          edit;
          f:= RoundTo(j/zyrs[i], -4);
          fieldbyname('智育百分比').AsFloat:= f;
          post;
          next;
          j:= j+ 1;
        end;
        sort:= '综合测评成绩 DESC';
        j:= 1;
        first;
        progressbar1.Position:= 0;
        richedit1.Lines.Add(timetostr(time)+'加入综测百分比: '+ zy[i]);
        progressbar1.Max:= zyrs[i];
        while not eof do begin
          progressbar1.Position:= progressbar1.Position+ 1;
          update;
          edit;
          f:= RoundTo(j/ zyrs[i], -4);
          fieldbyname('综测百分比').AsFloat:= f;
          post;
          next;
          j:= j+ 1;
        end;
        progressbar1.Position:= 0;
        progressbar1.Max:= round(zyzrs* zbl);
        richedit1.Lines.Add(timetostr(time)+'加入奖学金等级: '+ zy[i]);
        update;
        close;
        sql.Clear;
        sql.Add('select * from 学籍成绩 where 专业 = "'+ zy[i]+ '" and 综测百分比 < '+ floattostr(jdzc)+
          ' and 智育百分比 < '+ floattostr(jdzy)+
          ' and 必修课挂科门数 = 0 and 选修课挂科门数 = 0 and 体育课挂科门数 = 0');
        open;
        while not eof do begin
          progressbar1.Position:= progressbar1.Position+ 1;
          update;
          edit;
          if length(fieldbyname('奖学金等级').AsString)= 0 then
            fieldbyname('奖学金等级').AsString:= '甲等奖学金';
          post;
          next;
        end;
        close;
        sql.Clear;
        sql.Add('select * from 学籍成绩 where 专业 = "'+ zy[i]+ '" and 综测百分比 < '+ floattostr(ydzc)+
          ' and 智育百分比 < '+ floattostr(ydzy)+
          ' and 必修课挂科门数 = 0 and 选修课挂科门数 = 0 and 体育课挂科门数 = 0');
        open;
        while not eof do begin
          progressbar1.Position:= progressbar1.Position+ 1;
          update;
          edit;
          if length(fieldbyname('奖学金等级').AsString)= 0 then
            fieldbyname('奖学金等级').AsString:= '乙等奖学金';
          post;
          next;
        end;
        close;
        sql.Clear;
        sql.Add('select * from 学籍成绩 where 专业 = "'+ zy[i]+ '" and 综测百分比 < '+ floattostr(bdzc)+
          ' and 智育百分比 < '+ floattostr(bdzy)+
          ' and 必修课挂科门数 = 0 and 选修课挂科门数 = 0 and 体育课挂科门数 = 0');
        open;
        while not eof do begin
          progressbar1.Position:= progressbar1.Position+ 1;
          update;
          edit;
          if length(fieldbyname('奖学金等级').AsString)= 0 then
            fieldbyname('奖学金等级').AsString:= '丙等奖学金';
          post;
          next;
        end;
      end;
    end;
    result:= true;
  finally
    richedit1.Lines.Add(timetostr(time)+'成绩后期处理完毕');
    StatusBar1.Panels[1].Text:= '成绩后期处理完毕';
    update;
    writeini();
    screen.Cursor:= crdefault;
  end;
  end;
end;

function Output(): boolean;
var
  i: integer;
  row, column: integer;
  RstCJdir,RstPJdir: string;
begin
  ReadIni(false);
  result:= false;
  with mainform do begin
  try
  CreateDirectory(PChar(ExtractFilePath(ParamStr(0))+'处理结果xls'),nil);
  RstCJdir:= FolderRst+ '\全部成绩.xls';
  RstPJdir:= FolderRst+ '\评奖结果.xls';

    ExcelApplication1.Connect;
    ExcelApplication1.Visible[0]:= false;
    ExcelApplication1.SheetsInNewWorkbook[1]:= zys;
    ExcelApplication1.SheetsInNewWorkbook[2]:= zys;
    ExcelApplication1.Caption:= '全部成绩录入中...';
    update;
    ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Add(emptyparam,0));

    for i:= 1 to zys do begin
      progressbar1.Position:= 0;
      progressbar1.Max:= zyrs[i];
      richedit1.Lines.Add(timetostr(time)+'开始导出成绩信息');
      richedit1.Lines.Add(timetostr(time)+'成绩录入:'+zy[i]);
      StatusBar1.Panels[1].Text:= '导出成绩信息中';
      update;
      excelworksheet1.ConnectTo(excelworkbook1.Worksheets[i] as _worksheet);
      excelworksheet1.Activate;
      excelworksheet1.Name:= zy[i];
      excelapplication1.Range['d1','d3000'].NumberFormatLocal:= '@';
      with adoquery1 do begin
        close;
        sql.Clear;
        sql.Add('select * from 学籍成绩 where 专业 = "'+ zy[i]+ '"');
        open;
        sort:= '综测百分比 ASC';
        first;

        for column:= 1 to FieldCount- 1 do
          excelworksheet1.Cells.Item[1,column]:= fields.Fields[column].FieldName;
        for row:= 1 to RecordCount do begin
          for column:= 1 to FieldCount- 1 do begin
            excelworksheet1.Cells.Item[row+ 1,column]:= Fields[column].AsString;
          end;
          next;
          progressbar1.Position:= progressbar1.Position+ 1;
          update;
        end;
        excelworksheet1.Cells.Range['a1','a3000'].ColumnWidth:= 11;
        excelworksheet1.Cells.Range['a1','z1'].WrapText:= true;
        excelworksheet1.Cells.Range['r1','r3000'].ColumnWidth:= 11;
        excelapplication1.Range['g1','g3000'].NumberFormatLocal:= '0.00%';
        excelapplication1.Range['m1','m3000'].NumberFormatLocal:= '0.00%';
      end;
    end;
    excelworkbook1.SaveCopyAs(RstCJdir);//,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam);
    excelworkbook1.Saved[1]:= true;
    excelworkbook1.Close;

    ExcelApplication1.Caption:= '评奖结果录入中...';
    update;
    ExcelWorkbook2.ConnectTo(ExcelApplication1.Workbooks.Add(emptyparam,0));
    for i:= 1 to zys do begin
      progressbar1.Position:= 0;

      richedit1.Lines.Add(timetostr(time)+'评奖结果录入: '+zy[i]);
      StatusBar1.Panels[1].Text:= '评奖结果录入中';
      update;
      excelworksheet1.ConnectTo(excelworkbook2.Worksheets[i] as _worksheet);
      excelworksheet1.Activate;
      excelworksheet1.Name:= zy[i];
      with adoquery1 do begin
        close;
        sql.Clear;
        sql.Add('select 学号,姓名,专业,专业班级,智育成绩,智育百分比,综合测评成绩,综测百分比,奖学金等级 from 学籍成绩 where 专业 = "'+
               zy[i]+ '" and ( 奖学金等级 = "甲等奖学金" or 奖学金等级 = "乙等奖学金" or 奖学金等级 = "丙等奖学金")');
        open;
        sort:= '综测百分比 ASC';
        first;
        progressbar1.Max:= RecordCount;
        for column:= 1 to FieldCount do
          excelworksheet1.Cells.Item[1,column]:= fields.Fields[column- 1].FieldName;
        for row:= 1 to RecordCount do begin
          for column:= 1 to FieldCount do begin
              excelworksheet1.Cells.Item[row+ 1,column]:= Fields[column- 1].AsString;
          end;
          next;
          progressbar1.Position:= progressbar1.Position+ 1;
          update;
        end;
        excelworksheet1.Cells.Range['a1','i1'].WrapText:= true;
        excelworksheet1.Cells.Range['a1','a3000'].ColumnWidth:= 11;
        excelworksheet1.Cells.Range['i1','i3000'].ColumnWidth:= 11;
        excelapplication1.Range['e1','e3000'].NumberFormatLocal:= '0.00';
        excelapplication1.Range['f1','f3000'].NumberFormatLocal:= '0.00%';
        excelapplication1.Range['g1','g3000'].NumberFormatLocal:= '0.00';
        excelapplication1.Range['h1','h3000'].NumberFormatLocal:= '0.00%';
      end;
    end;

    excelworkbook2.SaveCopyAs(RstPJdir);//,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam);
    excelworkbook2.Saved[1]:= True;
    excelworkbook2.Close;
    excelapplication1.Disconnect;
    excelapplication1.Quit;
    result:= true;
  finally
    richedit1.Lines.Add(timetostr(time)+'成绩信息导出完成');
    StatusBar1.Panels[1].Text:= '成绩信息导出完成';
    update;
  end;
  end;
end;
end.