one.~pas

这是一个DELPHI+ACCESS所做的开课生成器

unit one;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, TabNotBk, StdCtrls, Buttons, ExtCtrls, ExcelXP,
  OleServer, Grids, DBGrids, Excel2000, QDialogs, Access2000, DB, ADODB,
  AccessXP,two, Mask;

type
  TForm1 = class(TForm)
    StatusBar1: TStatusBar;
    ExcelApplication1: TExcelApplication;
    ExcelWorkbook1: TExcelWorkbook;
    ExcelWorksheet1: TExcelWorksheet;
    SaveDialog1: TSaveDialog;
    aa: TAccessApplication;
    ADOQuery1: TADOQuery;
    ADOConnection1: TADOConnection;
    DataSource1: TDataSource;
    OpenDialog1: TOpenDialog;
    TabbedNotebook2: TTabbedNotebook;
    TabbedNotebook1: TTabbedNotebook;
    Panel1: TPanel;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    BitBtn4: TBitBtn;
    GroupBox4: TGroupBox;
    ComboBox2: TComboBox;
    ComboBox1: TComboBox;
    GroupBox5: TGroupBox;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    GroupBox6: TGroupBox;
    BitBtn8: TBitBtn;
    Button1: TButton;
    BitBtn9: TBitBtn;
    DBGrid1: TDBGrid;
    ComboBox3: TComboBox;
    ComboBox4: TComboBox;
    ComboBox5: TComboBox;
    Panel2: TPanel;
    BitBtn6: TBitBtn;
    BitBtn7: TBitBtn;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    selectdir: TButton;
    GroupBox2: TGroupBox;
    Label2: TLabel;
    selectdirb: TBitBtn;
    GroupBox3: TGroupBox;
    Label3: TLabel;
    BitBtn5: TBitBtn;
    GroupBox7: TGroupBox;
    CheckBox1: TCheckBox;
    GroupBox8: TGroupBox;
    RadioButton3: TRadioButton;
    RadioButton4: TRadioButton;
    GroupBox9: TGroupBox;
    GroupBox10: TGroupBox;
    GroupBox11: TGroupBox;
    Edit1: TEdit;
    Button2: TButton;
    ADOQuery2: TADOQuery;
    ListBox1: TListBox;
    Label4: TLabel;
    BitBtn10: TBitBtn;
    ComboBox6: TComboBox;
    GroupBox12: TGroupBox;
    GroupBox13: TGroupBox;
    GroupBox14: TGroupBox;
    ListBox2: TListBox;
    ComboBox7: TComboBox;
    Edit2: TEdit;
    BitBtn11: TBitBtn;
    ComboBox8: TComboBox;
    ComboBox9: TComboBox;
    BitBtn12: TBitBtn;
    TabbedNotebook3: TTabbedNotebook;
    GroupBox15: TGroupBox;
    GroupBox16: TGroupBox;
    Edit3: TEdit;
    ComboBox10: TComboBox;
    ComboBox11: TComboBox;
    BitBtn13: TBitBtn;
    GroupBox17: TGroupBox;
    ComboBox12: TComboBox;
    ComboBox13: TComboBox;
    BitBtn14: TBitBtn;
    BitBtn15: TBitBtn;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    ComboBox14: TComboBox;
    Label8: TLabel;
    DataSource2: TDataSource;
    DBGrid2: TDBGrid;
    ADOQuery3: TADOQuery;
    GroupBox18: TGroupBox;
    Label9: TLabel;
    ComboBox17: TComboBox;
    ComboBox16: TComboBox;
    ComboBox15: TComboBox;
    GroupBox19: TGroupBox;
    GroupBox20: TGroupBox;
    BitBtn16: TBitBtn;
    BitBtn17: TBitBtn;
    BitBtn18: TBitBtn;
    BitBtn19: TBitBtn;
    BitBtn20: TBitBtn;
    ComboBox18: TComboBox;
    Label10: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    Label13: TLabel;
    Edit4: TEdit;
    Label14: TLabel;
    ComboBox19: TComboBox;
    Header1: THeader;
    Label15: TLabel;
    Edit5: TEdit;
    Label16: TLabel;
    MaskEdit1: TMaskEdit;
    Label17: TLabel;
    Header2: THeader;
    Label18: TLabel;
    MaskEdit2: TMaskEdit;
    Label19: TLabel;
    Edit6: TEdit;
    Header3: THeader;
    Label20: TLabel;
    MaskEdit3: TMaskEdit;
    Label21: TLabel;
    MaskEdit4: TMaskEdit;
    MaskEdit5: TMaskEdit;
    MaskEdit6: TMaskEdit;
    Label22: TLabel;
    Label23: TLabel;
    Edit7: TEdit;
    Label24: TLabel;
    Label25: TLabel;
    MaskEdit7: TMaskEdit;
    ComboBox20: TComboBox;
    BitBtn21: TBitBtn;
    BitBtn22: TBitBtn;
    BitBtn23: TBitBtn;
    BitBtn24: TBitBtn;
    procedure ComboBox1Change(Sender: TObject);
    procedure ComboBox2DropDown(Sender: TObject);
    procedure ComboBox1DropDown(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure selectdirClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure selectdirbClick(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure ComboBox3Change(Sender: TObject);
    procedure ComboBox4DropDown(Sender: TObject);
    procedure ComboBox5DropDown(Sender: TObject);
    procedure ComboBox3DropDown(Sender: TObject);
    procedure BitBtn6Click(Sender: TObject);
    procedure ComboBox5Change(Sender: TObject);
    procedure BitBtn7Click(Sender: TObject);
    procedure RadioButton1Click(Sender: TObject);
    procedure RadioButton2Click(Sender: TObject);
    procedure BitBtn8Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure BitBtn9Click(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure RadioButton3Click(Sender: TObject);
    procedure RadioButton4Click(Sender: TObject);
    procedure BitBtn10Click(Sender: TObject);
    procedure ComboBox6DropDown(Sender: TObject);
    procedure ComboBox7DropDown(Sender: TObject);
    procedure ComboBox7Change(Sender: TObject);
    procedure BitBtn11Click(Sender: TObject);
    procedure Edit2Click(Sender: TObject);
    procedure ComboBox8DropDown(Sender: TObject);
    procedure ComboBox9DropDown(Sender: TObject);
    procedure ComboBox9Change(Sender: TObject);
    procedure ComboBox8Change(Sender: TObject);
    procedure BitBtn12Click(Sender: TObject);
    procedure ComboBox11DropDown(Sender: TObject);
    procedure BitBtn13Click(Sender: TObject);
    procedure ComboBox14DropDown(Sender: TObject);
    procedure ComboBox14Change(Sender: TObject);
    procedure ComboBox13DropDown(Sender: TObject);
    procedure BitBtn14Click(Sender: TObject);
    procedure BitBtn15Click(Sender: TObject);
    procedure TabbedNotebook3Change(Sender: TObject; NewTab: Integer;
      var AllowChange: Boolean);
    procedure ComboBox16DropDown(Sender: TObject);
    procedure BitBtn16Click(Sender: TObject);
    procedure TabbedNotebook2Change(Sender: TObject; NewTab: Integer;
      var AllowChange: Boolean);
    procedure BitBtn20Click(Sender: TObject);
    procedure ComboBox18DropDown(Sender: TObject);
    procedure ComboBox18Change(Sender: TObject);
    procedure DBGrid2CellClick(Column: TColumn);
    procedure BitBtn22Click(Sender: TObject);
    procedure BitBtn17Click(Sender: TObject);
    procedure BitBtn21Click(Sender: TObject);
    procedure BitBtn23Click(Sender: TObject);
    procedure BitBtn19Click(Sender: TObject);
    procedure BitBtn18Click(Sender: TObject);
    procedure BitBtn24Click(Sender: TObject);



  private
    { Private declarations }

  public
    { Public declarations }

  end;

var
  Form1: TForm1;
  str,str2,str3:string;     //定义表的名称
  Dir,dirb,dirgxc: WideString;  //设置各种路径
  str_sql,str_xy:string;                 //sql语句
  a,sc_visible:boolean;
  dir_open,str_open:string;

  SsnoSea:string;
  str_text:string;
  str_xb:string;
  str_jsgs1,str_jsgs2:string;


implementation

//uses two;

{$R *.dfm}

procedure TForm1.ComboBox1Change(Sender: TObject);
begin
   combobox2.Clear ;

 { if ComboBox1.Text='理工学院'
then
begin
combobox2.Items.Clear();
combobox2.Items.Add('计算机');
combobox2.Items.Add('数学');
combobox2.Items.Add('物理');
combobox2.Items.Add('信息');  //i选择系别
end;
if ComboBox1.Text='人文学院'
then
begin
combobox2.Items.Clear();
combobox2.Items.Add('文学');
combobox2.Items.Add('地理');    //i选择系别

end; }

end;

procedure TForm1.ComboBox2DropDown(Sender: TObject);
 var  i:integer;
begin
if
 ComboBox1.Text='学院'
then
   application.MessageBox('请选择学院','提示',$00000040); //判断选择学院
   if
   combobox2.Text=''
   then
   BitBtn1.Enabled:=true; //激活填写计划表按纽
   BitBtn2.Enabled:=true;//激活打开培养计划按纽


  begin

 str_xy:='select * from 系别 where 学院 like '''+combobox1.Text+'''';
 combobox2.Clear;

with adoquery2 do
 begin close;
  sql.Clear;
sql.Add(str_xy);
open;
end;
for i:=adoquery2.RecordCount  downto 1 do
begin
 SsnoSea:= adoquery2.Fields[0].AsString;
ComboBox2.Items.Add( SsnoSea);
adoquery2.next;
adoquery2.ExecSQL;

end;

end;








end;


procedure TForm1.ComboBox1DropDown(Sender: TObject);
var  i:integer;
begin
BitBtn1.Enabled:=false; //填写计划表按纽 变灰
 BitBtn2.Enabled:=false;  //填写按纽 变灰
 BitBtn4.Enabled:=false;  //打开培养计划按纽 变灰
 BitBtn3.Enabled:=false;    //打开保存计划按纽 变灰

 begin

 str_xy:='select 学院 from 学院';
 combobox1.Clear;

with adoquery2 do
 begin close;
  sql.Clear;
sql.Add(str_xy);
open;
end;
for i:=adoquery2.RecordCount  downto 1 do
begin
 SsnoSea:= adoquery2.Fields[0].AsString;
ComboBox1.Items.Add( SsnoSea);
adoquery2.next;
adoquery2.ExecSQL;

end;

end;




end;

procedure TForm1.BitBtn2Click(Sender: TObject);
begin
   str:=ComboBox1.Text+ComboBox2.Text ;   //取得excel标题
   str2:=ComboBox1.Text+ComboBox2.Text;
   a:=true;
 begin
  try
    excelapplication1.Connect;     //建立连接
  except
   messagedlg('excel是否正确安装',mtError,[mbok],0);  //提示出错并结束程序
   abort;
   end; 

    {try
    ExcelApplication1.Workbooks.Open(dir+'\'+'开课计划.xls',
    EmptyParam,EmptyParam,EmptyParam,EmptyParam,
    EmptyParam,EmptyParam,EmptyParam,EmptyParam,
    EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
    except
    messagedlg('无'+str+'计划表，请查找或重新设置目录',mterror,[mbok],0) ;
     //提示错误  }

  //打开模板excel


    try
    ExcelApplication1.Workbooks.Open(dir+'\'+'开课计划.xls',
    EmptyParam,EmptyParam,EmptyParam,EmptyParam,
    EmptyParam,EmptyParam,EmptyParam,EmptyParam,
    EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);   //office 2003
    except
    messagedlg('无'+str+'计划表，请查找或重新设置目录',mterror,[mbok],0) ;
    end;
     //提示错误

    excelapplication1.Visible[0]:=true; //设置为可视状态
    excelapplication1.Caption:=str;  //设置excel的标题
    //excelapplication1.Workbooks.Add(null,0);
    excelworkbook1.ConnectTo(excelapplication1.Workbooks[1]);  //连接工作簿
    excelworksheet1.ConnectTo(excelworkbook1.Worksheets[1] as _worksheet); //连接工作表
     BitBtn3.Enabled:=true; //激活保存计划表按纽


end;

end;


procedure TForm1.BitBtn1Click(Sender: TObject);
begin


str:=ComboBox1.Text+ComboBox2.Text ;  //根据下拉菜单取得文件名
begin
 try
    excelapplication1.Connect;
 except
   messagedlg('excel是否正确安装',mtError,[mbok],0);  //提示出错并结束程序
   abort;
   end;
   try
    ExcelApplication1.Workbooks.Open(dir+'\'+str+'培养计划.xls', //给定路径
    EmptyParam,EmptyParam,EmptyParam,EmptyParam,
    EmptyParam,EmptyParam,EmptyParam,EmptyParam,
    EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
    excelapplication1.Visible[0]:=true;
    except
    messagedlg('无'+str+'培养计划表，请查找或重新设置目录',mterror,[mbok],0) ;
     //提示错误
end;
end;
end;


procedure TForm1.selectdirClick(Sender: TObject);
begin
Dir:=label1.Caption;
if SelectDirectory('设置培养计划书路径','',Dir) then

Label1.Caption:=dir;   //设置路径

end;
procedure TForm1.FormCreate(Sender: TObject);
var year,month,day:word ;
present:tdatetime;
begin

ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=总开课计划表\text.mdb;Persist Security Info=False';
Dir:=label1.Caption;


Dirb:=label2.Caption;
present:=now;
decodedate(present,year,month,day);
ComboBox15.Items.Clear;
ComboBox15.Text:=inttostr(year);//设置那一年
ComboBox15.Items.Add(inttostr(year+1));
ComboBox15.Items.Add(inttostr(year));
ComboBox15.Items.Add(inttostr(year-1));
ComboBox15.Items.Add(inttostr(year-2));
ComboBox15.Items.Add(inttostr(year-3));
ComboBox15.Items.Add(inttostr(year-4));


WITH adoquery3 do
close;
adoquery3.SQL.clear;
adoquery3.SQL.add('select * from 工作量测量 order by 授课教师');
adoquery3.Open;

 DBGrid2.Columns[0].Width:=52;
DBGrid2.Columns[1].Width:=40;
DBGrid2.Columns[2].Width:=80;
DBGrid2.Columns[3].Width:=74;
DBGrid2.Columns[4].Width:=62;
DBGrid2.Columns[5].Width:=40;
DBGrid2.Columns[6].Width:=50;
DBGrid2.Columns[7].Width:=50;
DBGrid2.Columns[8].Width:=40;
DBGrid2.Columns[9].Width:=30;
DBGrid2.Columns[10].Width:=98;
DBGrid2.Columns[11].Width:=98;
DBGrid2.Columns[12].Width:=50;
DBGrid2.Columns[13].Width:=120;
DBGrid2.Columns[14].Width:=90;





end;




procedure TForm1.BitBtn3Click(Sender: TObject);

begin
SaveDialog1.Filter:='excel files (*.xls)';//保存类型
savedialog1.FileName:=str;           //取得保存文件名
savedialog1.InitialDir:=dirb+'\';   //保存路径
if
savedialog1.Execute
then
excelworksheet1.SaveAs(savedialog1.FileName);
 BitBtn4.Enabled:=true;
end;

procedure TForm1.selectdirbClick(Sender: TObject);