uprintzwb.pas

学生信息管理系统

unit UPrintzwb;

interface

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

type
  TFPrintzwb = 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;
    Panel3: TPanel;
    Label4: TLabel;
    Edit1: TEdit;
    Panel1: TPanel;
    Shape1: TShape;
    Label2: TLabel;
    Label1: TLabel;
    Shape2: TShape;
    ComboBox2: TComboBox;
    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;
    Button1: TButton;
    DBGridEh: TDBGridEh;
    Button3: TButton;
    Label3: TLabel;
    Label5: TLabel;
    Shape3: TShape;
    ComboBox1: TComboBox;
    Button2: TButton;
    Button4: TButton;
    Label6: TLabel;
    Label7: TLabel;
    ComboBox3: TComboBox;
    Button5: TButton;
    Button6: TButton;
    Button7: TButton;
    Label8: TLabel;
    Label9: TLabel;
    ComboBox4: TComboBox;
    Button8: TButton;
    Shape4: TShape;
    Label10: TLabel;
    Button9: TButton;
    Shape5: TShape;
    procedure Button3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure ADOQueryCalcFields(DataSet: TDataSet);
    procedure ADOQueryAfterOpen(DataSet: TDataSet);
    procedure Button1Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button2Click(Sender: TObject);
    procedure A_findExecute(Sender: TObject);
    procedure A_previewExecute(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure ComboBox2Change(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure Button7Click(Sender: TObject);
    procedure ComboBox4Change(Sender: TObject);
    procedure Button8Click(Sender: TObject);
    procedure Button9Click(Sender: TObject);
    procedure ComboBox3Change(Sender: TObject);
  private
    { Private declarations }
    procedure setsize;
  public
    { Public declarations }
  end;

var
  FPrintzwb: TFPrintzwb;

implementation

uses UPublic, UMain, Unit2;

{$R *.dfm}

procedure TFPrintzwb.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 试室名称='''+ComboBox2.text+''' order by 试室名称,流水号';
    Open;
  except
  end;
end;

procedure TFPrintzwb.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 ss,id from STU_ss where not (nj1 is null) order by id',ComboBox2.Items);
  ComboBox2.Items.Insert(0,'全部数据');

  InitCode('select distinct 班级 from STU_INFO where not (试室名称 is null)',ComboBox1.Items);
  ComboBox1.Items.Insert(0,'所有班级');

  ComboBox3.Items := ComboBox1.Items;

  InitCode('select distinct 班级 from STU_INFO',ComboBox4.Items);
  ComboBox4.Items.Insert(0,'所有班级');

end;

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

procedure TFPrintzwb.ADOQueryAfterOpen(DataSet: TDataSet);
begin
  StatusBar.SimpleText := '总共搜索到'+inttostr(ADOQUery.recordcount)+'条数据';
end;

procedure TFPrintzwb.Button1Click(Sender: TObject);
var
  i, j, k,m: integer;
  allCount,HCount: integer;//试室的两个组的人数
  aNj1, aNj2: string;
  XLApp: Variant;
  Sheet: Variant;
  QueryTemp, QueryData: TADOQuery;
  strname:string;
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;
  if not VarIsEmpty(XLApp) then
  begin
       XLApp.DisplayAlerts := False;
       XLApp.Quit;
       VarClear(XLApp);
  end;


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

  if combobox2.Text = '全部数据' 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');
      with QueryTemp do try
        if Active then Close;
        SQL.Text := 'select rs,nj1,nj2,rs1,rs2 from STU_ss where ss='''+Combobox2.Items[i]+'''';
        Open;
//        aCount1 := FieldByName('rs1').AsInteger;
//        aCount2 := FieldByName('rs2').AsInteger;
        allCount := FieldByName('rs').AsInteger;
        aNj1 := FieldByName('nj1').AsString;
        aNj2 := FieldByName('nj2').AsString;
      except
      end;

        if allCount<=64 then begin strname := 'Sheet64'; HCount := 8; end
        else if allCount<=72 then begin strname := 'Sheet72'; HCount := 9; end
        else if allCount<=80 then begin strname := 'Sheet80'; HCount := 10; end
        else if allCount<=88 then begin strname := 'Sheet88'; HCount := 11; end
        else begin strname := 'Sheet96';; HCount := 12; end;

        //写入数据
        if QueryData.Active then QueryData.Close;
        QueryData.SQL.Text := 'select * from STU_info where 试室名称='''+Combobox2.Items[i]
             +''' and 退学否=''F'' and 级别='''+aNj1+''' order by 试室名称,流水号';
        QueryData.Open;
        QueryData.First;
        m := 1;
        
        sheet := XLApp.WorkBooks[1].WorkSheets[strname];
        sheet.name := Combobox2.Items[i]+'座位表';
        sheet.Cells[HCount+2,1].value := Combobox2.Items[i]+'讲台';


        for k := 1 to 4 do begin
          for j := HCount downto 1 do begin
            if QueryData.eof then Break;
            sheet.Cells[j+1,m].value := QueryData.FieldByName('座位号').AsString;
            sheet.Cells[j+1,m+1].value := QueryData.FieldByName('姓名').AsString;
            QueryData.Next;
            if QueryData.eof then Break;
          end;
          sheet.Cells[1,m].value :=  aNj1;
          Inc(m,4);
        end;

        if QueryData.Active then QueryData.Close;
        QueryData.SQL.Text := 'select * from STU_info where 试室名称='''+Combobox2.Items[i]
             +''' and 退学否=''F'' and 级别='''+aNj2+''' order by 试室名称,流水号';
        QueryData.Open;
        QueryData.First;
        m := 3;

        for k := 1 to 4 do begin
          for j := HCount downto 1 do begin
            if QueryData.eof then Break;
            sheet.Cells[j+1,m].value := QueryData.FieldByName('座位号').AsString;
            sheet.Cells[j+1,m+1].value := QueryData.FieldByName('姓名').AsString;
            if QueryData.eof then Break;
            QueryData.Next;
          end;
          sheet.Cells[1,m].value :=  aNj2;
          Inc(m,4);
        end;
      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');
      with QueryTemp do try
        if Active then Close;
        SQL.Text := 'select rs,nj1,nj2,rs1,rs2 from STU_ss where ss='''+Combobox2.Text+'''';
        Open;
//        aCount1 := FieldByName('rs1').AsInteger;
//        aCount2 := FieldByName('rs2').AsInteger;
        allCount := FieldByName('rs').AsInteger;
        aNj1 := FieldByName('nj1').AsString;
        aNj2 := FieldByName('nj2').AsString;
      except
      end;

        if allCount<=64 then begin strname := 'Sheet64'; HCount := 8; end
        else if allCount<=72 then begin strname := 'Sheet72'; HCount := 9; end
        else if allCount<=80 then begin strname := 'Sheet80'; HCount := 10; end
        else if allCount<=88 then begin strname := 'Sheet88'; HCount := 11; end
        else begin strname := 'Sheet96';; HCount := 12; end;

        //写入数据
        if QueryData.Active then QueryData.Close;
        QueryData.SQL.Text := 'select * from STU_info where 试室名称='''+Combobox2.Text
             +''' and 退学否=''F'' and 级别='''+aNj1+''' order by 试室名称,流水号';
        QueryData.Open;
        QueryData.First;
        m := 1;
        
        sheet := XLApp.WorkBooks[1].WorkSheets[strname];
        sheet.name := Combobox2.Text+'座位表';
        sheet.Cells[HCount+2,1].value := Combobox2.Text+'讲台';


        for k := 1 to 4 do begin
          for j := HCount downto 1 do begin
            if QueryData.eof then Break;
            sheet.Cells[j+1,m].value := QueryData.FieldByName('座位号').AsString;
            sheet.Cells[j+1,m+1].value := QueryData.FieldByName('姓名').AsString;
            QueryData.Next;
            if QueryData.eof then Break;
          end;
          sheet.Cells[1,m].value :=  aNj1;
          Inc(m,4);
        end;

        if QueryData.Active then QueryData.Close;
        QueryData.SQL.Text := 'select * from STU_info where 试室名称='''+Combobox2.Text
             +''' and 退学否=''F'' and 级别='''+aNj2+''' order by 试室名称,流水号';
        QueryData.Open;
        QueryData.First;
        m := 3;

        for k := 1 to 4 do begin
          for j := HCount downto 1 do begin
            if QueryData.eof then Break;
            sheet.Cells[j+1,m].value := QueryData.FieldByName('座位号').AsString;
            sheet.Cells[j+1,m+1].value := QueryData.FieldByName('姓名').AsString;
            if QueryData.eof then Break;
            QueryData.Next;
          end;
          sheet.Cells[1,m].value :=  aNj2;
          Inc(m,4);
        end;
        try
          XLApp.Visible := True;
          Sheet.Printpreview;
          XLApp.ActiveWorkBook.Saved := True;
        finally
          XLApp.Quit;
          VarClear(XLApp);
        end;

  end;

      QueryData.Free;
      QueryTemp.Free;


end;

procedure TFPrintzwb.Button4Click(Sender: TObject);
begin
  if ComboBox1.ItemIndex <0 then  exit;
  if ComboBox1.ItemIndex=0 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 班级='''+ComboBox1.text+''' order by 试室名称,姓名,流水号';
    Open;
  except
  end;
end;

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

procedure TFPrintzwb.Button2Click(Sender: TObject);
var
  i, j, k, m, axh: integer;
  XLApp: Variant;
  Sheet: Variant;
  QueryData: TADOQuery;
begin
  if ADOQuery.IsEmpty then exit;
  if ComboBox1.ItemIndex < 0 then exit;
  if ComboBox1.Items.Count<2 then exit;
  if Ask('真的要打印班级座位表信息吗？','提示',MB_OKCL)<>1 then exit;
  if not VarIsEmpty(XLApp) then
  begin
       XLApp.DisplayAlerts := False;
       XLApp.Quit;
       VarClear(XLApp);
  end;


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

  if ComboBox1.ItemIndex=0 then begin
    for i := 1 to ComboBox1.Items.Count - 1 do begin
      try
        XLApp := CreateOleObject('Excel.Application');
      except
        Screen.Cursor := crDefault;