uprintcjb.pas

学生信息管理系统

unit UPrintcjb;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, PrnDbgeh, DB, ADODB, ActnList, Grids, DBGridEh, StdCtrls,
  ExtCtrls, ComCtrls, ToolWin,ComObj;

type
  TFPrintcjb = class(TForm)
    Splitter1: TSplitter;
    StatusBar: TStatusBar;
    CoolBar1: TCoolBar;
    ToolBar1: TToolBar;
    Tool_add: TToolButton;
    Tool_modify: TToolButton;
    Tool_save: TToolButton;
    Tool_delete: TToolButton;
    Tool_cancel: TToolButton;
    Tool_print: TToolButton;
    ToolButton7: TToolButton;
    Tool_prior: TToolButton;
    Tool_next: TToolButton;
    ToolButton1: TToolButton;
    Tool_m: TToolButton;
    ToolButton2: TToolButton;
    Tool_tx: TToolButton;
    Panel3: TPanel;
    Label4: TLabel;
    Edit1: TEdit;
    Panel1: TPanel;
    Shape1: TShape;
    Label2: TLabel;
    Label1: TLabel;
    Shape2: TShape;
    ComboBox2: TComboBox;
    Button1: TButton;
    Button3: TButton;
    Panel2: TPanel;
    ActionList: TActionList;
    A_add: TAction;
    A_modify: TAction;
    A_save: TAction;
    A_delete: TAction;
    A_cancel: TAction;
    A_preview: TAction;
    A_pr: TAction;
    A_next: TAction;
    A_find: TAction;
    A_refresh: TAction;
    A_tx: TAction;
    ADOQuery: TADOQuery;
    ADOQueryvxh: TIntegerField;
    ADOQueryID: TAutoIncField;
    ADOQueryDSDesigner: TWideStringField;
    ADOQueryDSDesigner2: TWideStringField;
    ADOQueryDSDesigner3: TSmallintField;
    ADOQueryDSDesigner4: TWideStringField;
    ADOQueryDSDesigner5: TWideStringField;
    ADOQueryDSDesigner6: TWideStringField;
    ADOQueryDSDesigner18: TWideStringField;
    ADOQueryDSDesigner19: TWideStringField;
    ADOQueryDSDesigner20: TWideStringField;
    DataSource: TDataSource;
    PrintDBGridEh: TPrintDBGridEh;
    DBGridEh: TDBGridEh;
    ADOQueryDSDesigner7: TFloatField;
    ADOQueryDSDesigner8: TFloatField;
    ADOQueryDSDesigner9: TFloatField;
    ADOQueryDSDesigner10: TFloatField;
    ADOQueryDSDesigner11: TFloatField;
    ADOQueryDSDesigner12: TFloatField;
    ADOQueryDSDesigner13: TFloatField;
    ADOQueryDSDesigner14: TFloatField;
    ADOQueryDSDesigner15: TFloatField;
    ADOQueryDSDesigner16: TFloatField;
    ADOQueryDSDesigner17: TFloatField;
    ADOQueryDSDesigner21: TWideStringField;
    Label3: TLabel;
    Label5: TLabel;
    Shape3: TShape;
    ComboBox1: TComboBox;
    Button2: TButton;
    Edit2: TEdit;
    Label6: TLabel;
    GroupBox1: TGroupBox;
    Memo1: TMemo;
    Edit3: TEdit;
    Label7: TLabel;
    ComboBox3: TComboBox;
    ProgressBar: TProgressBar;
    procedure Button3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure ADOQueryCalcFields(DataSet: TDataSet);
    procedure Button1Click(Sender: TObject);
    procedure ComboBox1Exit(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure ComboBox2Change(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  FPrintcjb: TFPrintcjb;

implementation

uses UPublic, UMain;

{$R *.dfm}

procedure TFPrintcjb.Button3Click(Sender: TObject);
begin
  if ComboBox2.ItemIndex <0 then  exit;
  if ComboBox2.Text = '全部数据' then begin
    with ADOQuery do try
      if Active then Close;
      sql.Text := 'select * from STU_info where  退学否=''F''  and (not(试室名称 is null)) order by 班级,流水号';
      Open;
    except
    end;
    exit;
  end;
  with ADOQuery do try
    if Active then Close;
    sql.Text := 'select * from STU_info where  退学否=''F'' and (not(试室名称 is null)) and 班级='''+ComboBox2.text+''' order by 班级,流水号';
    Open;
  except
  end;
end;

procedure TFPrintcjb.FormCreate(Sender: TObject);
var
  i: integer;
begin
  for i := 0 to ComponentCount - 1 do
  if (Components[i] is TADOQuery) then
  if (TADOQuery(Components[i]).SQL.Text<>'') and (TADOQuery(Components[i]).Tag<>111) then
  begin
    if  TADOQuery(Components[i]).Active then  TADOQuery(Components[i]).Close;
    try
      TADOQuery(Components[i]).Open;
    except
    end;
  end;
  InitCode('select distinct 班级 from STU_Info where not (试室名称 is null)',ComboBox2.Items);
  ComboBox2.Items.Insert(0,'全部数据');

//  ComboBox1.Items := ComboBox2.Items;
//  ComboBox1.Items[0] := '全部班级';

end;

procedure TFPrintcjb.ADOQueryCalcFields(DataSet: TDataSet);
begin
  ADOQuery.FieldByName('vxh').AsInteger := abs(ADOQuery.RecNo);
end;

procedure TFPrintcjb.Button1Click(Sender: TObject);
var
  i, j, m: integer;
  allCount: integer;//试室的两个组的人数
  XLApp: Variant;
  Sheet: Variant;
  QueryData: TADOQuery;
  strname, kmmc:string;
  kmarray: array[3..11]of string;
//  km1,km2,km3: array[1..7]of   //定义各个级别的考试科目
begin
  if ADOQuery.IsEmpty then exit;
  if Combobox2.ItemIndex < 0 then exit;
  if ComboBox2.Items.Count<2 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;


  QueryData := TADOQUery.Create(self);
  QueryData.Connection := FMain.ADOConnection;

  if combobox2.ItemIndex=0 then begin
    for i := 1 to ComboBox2.Items.Count - 1 do 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.items[i]+''' 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.Items[i]+'班成绩汇总表';
      sheet.Cells[1,1].value := Combobox2.Items[i]+'成绩统计表';
          //写入成绩信息
      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)';

        //设置显示列