uprintcjb.pas

学生信息管理系统

      kmmc := GetFieldVar('select km from STU_kskm where nj='''+copy(combobox2.items[i],1,4)+'''');

      for j := 11 downto 3 do
        if pos(kmarray[j],kmmc)<1 then
        sheet.Columns[j].Delete;

      XLApp.Visible := False;
      Sheet.Printout;
      XLApp.ActiveWorkBook.Saved := True;
      XLApp.Quit;
      VarClear(XLApp);
    end;
  end else begin
      try
        XLApp := CreateOleObject('Excel.Application');
      except
        Screen.Cursor := crDefault;
        Exit;
      end;
      XLApp.WorkBooks.Open(SysPath+'excel\汇总成绩.xls');
      if QueryData.Active then Close;
      QueryData.sql.Text := 'select * from STU_info where  退学否=''F'' and'
           +' (not(试室名称 is null)) and 班级='''+combobox2.text+''' order by '
           +'(语文+数学+英语+物理+化学+政治+历史+地理+生物) desc';
//           
      QueryData.Open;
      QueryData.First;
      allCount := QueryData.RecordCount;
      if allCount<=70 then begin strname := 'Sheet70';allCount:=70;end
      else if allCount<=85 then begin strname := 'Sheet85';;allCount:=85;end
      else begin strname := 'Sheet105';;allCount:=105;end;

      sheet := XLApp.WorkBooks[1].WorkSheets[strname];
      sheet.name := Combobox2.text+'班成绩汇总表';
      sheet.Cells[1,1].value := Combobox2.text+'成绩统计表';
          //写入成绩信息
      m := 1;
      for j := 0 to QueryData.RecordCount-1 do begin
            if QueryData.eof then Break;
            sheet.Cells[j+3,m].value := QueryData.FieldByName('座位号').AsString;
            sheet.Cells[j+3,m+1].value := QueryData.FieldByName('姓名').AsString;
            sheet.Cells[j+3,m+2].value := QueryData.FieldByName('语文').AsString;
            sheet.Cells[j+3,m+3].value := QueryData.FieldByName('数学').AsString;
            sheet.Cells[j+3,m+4].value := QueryData.FieldByName('英语').AsString;
            sheet.Cells[j+3,m+5].value := QueryData.FieldByName('物理').AsString;
            sheet.Cells[j+3,m+6].value := QueryData.FieldByName('化学').AsString;
            sheet.Cells[j+3,m+7].value := QueryData.FieldByName('历史').AsString;
            sheet.Cells[j+3,m+8].value := QueryData.FieldByName('政治').AsString;
            sheet.Cells[j+3,m+9].value := QueryData.FieldByName('地理').AsString;
            sheet.Cells[j+3,m+10].value := QueryData.FieldByName('生物').AsString;
            sheet.Cells[j+3,m+11].value := '=SUM(C'+inttostr(j+3)+':K'+inttostr(j+3)+')';
            sheet.Cells[j+3,m+12].value := j+1;
            QueryData.Next;
            if QueryData.eof then Break;
      end;
       //优秀率
      for j := 3 to 11 do
        sheet.Cells[allCount+3,j].value  := '=COUNTIF(C3:C72,">=90")/'+inttostr(QueryData.RecordCount);
      //良好率
      for j := 3 to 11 do
        sheet.Cells[allCount+4,j].value  := '=(COUNTIF(C3:C72,">=70")-COUNTIF(C3:C72,">=90"))/'+inttostr(QueryData.RecordCount);
      //合格率         =MAX(C3:C72)
      for j := 3 to 11 do
        sheet.Cells[allCount+5,j].value  := '=(COUNTIF(C3:C72,">=60")-COUNTIF(C3:C72,">=70"))/'+inttostr(QueryData.RecordCount);
      //最高分
      for j := 3 to 11 do
        sheet.Cells[allCount+6,j].value  := '=MAX(C3:C72)';
      //最低分
      for j := 3 to 11 do
        sheet.Cells[allCount+7,j].value  := '=MIN(C3:C72)';

        //设置显示列
      kmmc := GetFieldVar('select km from STU_kskm where nj='''+copy(combobox2.text,1,4)+'''');

      for j := 11 downto 3 do
        if pos(kmarray[j],kmmc)<1 then
        sheet.Columns[j].Delete;

      try
        XLApp.Visible := True;
        Sheet.Printpreview;
        XLApp.ActiveWorkBook.Saved := True;
      finally
        XLApp.Quit;
        VarClear(XLApp);
      end;

  end;

      QueryData.Free;
//      QueryTemp.Free;
end;

procedure TFPrintcjb.ComboBox1Exit(Sender: TObject);
var
  str: string;
begin
  if ComboBox1.ItemIndex > 0 then begin
    str := GetFieldVar('select km from STU_kskm where nj ='''+Copy(ComboBox1.Text,1,4)+'''');
    while pos(';',str)>0 do begin
      ComboBox3.Items.Add(copy(str,1,pos(';',str)-1));
      delete(str,1,pos(';',str));
    end;
  end else if ComboBox1.ItemIndex = 0 then begin
    str := GetFieldVar('select km from STU_kskm where nj =''初一''');
    while pos(';',str)>0 do begin
      ComboBox3.Items.Add(copy(str,1,pos(';',str)-1));
      delete(str,1,pos(';',str));
    end;
    str := GetFieldVar('select km from STU_kskm where nj =''初二''');
    while pos(';',str)>0 do begin
      if ComboBox3.Items.IndexOf(copy(str,1,pos(';',str)-1))<0 then
      ComboBox3.Items.Add(copy(str,1,pos(';',str)-1));
      delete(str,1,pos(';',str));
    end;
    str := GetFieldVar('select km from STU_kskm where nj =''初三''');
    while pos(';',str)>0 do begin
      if ComboBox3.Items.IndexOf(copy(str,1,pos(';',str)-1))<0 then
      ComboBox3.Items.Add(copy(str,1,pos(';',str)-1));
      delete(str,1,pos(';',str));
    end;
  end else while ComboBox3.Items.Count > 0 do ComboBox3.Items.Delete(0);
end;

procedure TFPrintcjb.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action := caFree;
  Release;
end;

procedure TFPrintcjb.ComboBox2Change(Sender: TObject);
begin
  if ComboBox2.ItemIndex<0 then exit;
  if ComboBox2.ItemIndex=0 then  Button1.Caption :='打  印'
  else  Button1.Caption := '打印预览';
end;

procedure TFPrintcjb.Button2Click(Sender: TObject);
   function getbjmc(vStr: string): String;
   var
     i,j, k: integer;
   begin
     Result :='1';
     j := 999; k := 999;
     for i := 1 to length(vStr) do
     if vStr[i] in ['0'..'9'] then begin
        if j = 999 then j := i
        else k :=i;
     end;
     if (j=999)then exit;
     if k<>999 then Result := vStr[j]+vStr[k] else
       Result := vStr[j];
   end;
var
  i,j,k, hnumber: integer;
  XLApp: Variant;
  Sheet: Variant;
  QueryData: TADOQuery;
  str, kmmc,bjnumber,bjname:string;
  //kmmc考试的科目,bjname班级名称, bjnumber班级号：比如初一（11）,班级号为11 ,hnumber填充行的数
  kmarray: array[3..11]of string;
begin
  if ADOQuery.IsEmpty then exit;
  if ComboBox1.ItemIndex < 0 then exit;
  if Ask('真的要输出各科成绩汇总表吗？','提示',MB_OKCL)<>1 then exit;
  kmarray[3] := '语文'; kmarray[4] := '数学'; kmarray[5] := '英语';
  kmarray[6] := '物理'; kmarray[7] := '化学'; kmarray[8] := '历史';
  kmarray[9] := '政治'; kmarray[10] := '地理'; kmarray[11] := '生物';
  if not VarIsEmpty(XLApp) then
  begin
       XLApp.DisplayAlerts := False;
       XLApp.Quit;
       VarClear(XLApp);
  end;
  try
    XLApp := CreateOleObject('Excel.Application');
  except
    Exit;
  end;
  kmmc := GetFieldVar('select km from STU_kskm where nj='''+ComboBox1.Text+'''');
  if trim(kmmc)='' then begin
    showmessage('没有考试科目');
    exit;
  end;
  copyfile(PChar(SysPath+'excel\各班科统计结果.xls'),PChar(SysPath+'各班科统计结果.xls'),false);
  XLApp.WorkBooks.Open(SysPath+'excel\各班科统计结果.xls');
  InitCode('select distinct(班级) from STU_INFO where  not (试室名称 is null) and 级别='''+ComboBox1.Text+'''',ComboBox3.Items);
  if ComboBox3.Items.Count <1 then begin
    ShowMessage('该级别不存在考试班级');
    exit;
  end;
  SCreen.Cursor := crHourGlass;
  ProgressBar.Position := 0;
  for i := 3 to 11 do begin
    ProgressBar.Position := i-2;
    sheet := XLApp.WorkBooks[1].WorkSheets[kmarray[i]];
    //填写标题，其他的科目,日期等东西
    str := '';
    for j := 0 to Memo1.Lines.Count-1 do 
    str := str +Memo1.Lines[j];
    Sheet.Cells[1,1].value := str;
    str := '';
    
    Sheet.Cells[2,2].value := ComboBox1.Text+kmarray[i];
    Sheet.Cells[2,13].value := Edit2.Text;
    Sheet.Cells[2,22].value := Edit3.Text;
    Sheet.Cells[2,33].value := FormatDateTime('yyyy"年"MM"月"dd"日"',now);

    if  Pos(kmarray[i],kmmc)>0 then begin
      //下面按班进行统计，共有ComboBox3.Items.Count多各班
      for j := 0 to ComboBox3.Items.Count - 1 do begin
        //刷新显示
        CoolBar1.Repaint;
        CoolBar1.Refresh;
        Panel1.Repaint;
        Panel1.Refresh;
        Panel2.Repaint;
        Panel2.Refresh;

        bjnumber := getbjmc(ComboBox3.Items[j]);
        bjname   := ComboBox3.Items[j];
        hnumber  := 4+strtoint(bjnumber);  //取得该班所在的行
        Sheet.Cells[hnumber,1].value := bjnumber+'班';

        //统计每个班各个分数段人数
        str := 'select count(*) from STU_INFO where 退学否 = ''F'' and 班级='''+bjname+'''';
        //先统计100分的
        Sheet.Cells[hnumber,2].value := GetFieldVar(str+' and '+kmarray[i]+'=100');
        //再统计99分的，把大于98小于100的归纳到99分
        Sheet.Cells[hnumber,3].value := GetFieldVar(str+' and '+kmarray[i]+'>98 and '+kmarray[i]+'<100');

        //统计50分到98分的
        for k := 98 downto 50 do
          Sheet.Cells[hnumber,102-k].value := GetFieldVar(str+' and '+kmarray[i]+'>'+inttostr(k-1)+' and '+kmarray[i]+'<='+inttostr(k));

        hnumber  := 23+strtoint(bjnumber);  //取得该班所在的行
        Sheet.Cells[hnumber,1].value := bjnumber+'班';
        //统计49分到0分的
        for k := 49 downto 0 do
          Sheet.Cells[hnumber,51-k].value := GetFieldVar(str+' and '+kmarray[i]+'>'+inttostr(k-1)+' and '+kmarray[i]+'<='+inttostr(k));
      end;
    end else
      //删除该工作表
      
    end;
    SCreen.Cursor := crDefault;
    ProgressBar.Position := 0;    
    ShowMessage('输出成功!');
    XLApp.Visible := True;
end;

end.