one.~pas

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

begin
Dirb:=label2.Caption;
if SelectDirectory('选择计划表的存放目录','',Dirb) then

Label2.Caption:=dirb;   //设置路径

end;
procedure TForm1.BitBtn5Click(Sender: TObject);
begin
dirgxc:=label3.Caption;
if SelectDirectory('选择各学期开课表的存放目录','',dirgxc) then

Label3.Caption:=dirgxc;   //设置路径

end;

procedure TForm1.BitBtn4Click(Sender: TObject);

begin
ExcelApplication1.Quit ;      //关闭excel
ExcelApplication1.Disconnect;   //解除连接
try
//aa.OpenCurrentDatabase(dirb+'\text.mdb',false);
 aa.OpenCurrentDatabaseOld(dirb+'\text.mdb',false);
aa.DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel8,
       str2,dirb+'\'+str,true,EmptyParam,EmptyParam);
       aa.Disconnect;
       showmessage('保存成功')
except
 messagedlg('打开数据库出错，如以打开数据库请先关闭',mterror,[mbok],0) ;
 abort
 end;
     //提示错误

       //aa.OpenCurrentDatabaseOld();    2003
       //aa.OpenCurrentDatabase('c:\aa.mdb')  2000
end;

procedure TForm1.ComboBox3Change(Sender: TObject);
begin
   

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

end;   }

end;


procedure TForm1.ComboBox4DropDown(Sender: TObject);
var  i:integer;
begin
DBGrid1.Visible:=false;
combobox5.Text:='';
BitBtn6.Enabled:=true;
BitBtn7.Enabled:=false;

if
 ComboBox3.Text='学院'
then
   application.MessageBox('请选择学院','提示',$00000040); //判断选择学院
  combobox5.Items.Add('第1学期');           //选择学期
  combobox5.Items.Add('第2学期');
  combobox5.Items.Add('第3学期');
  combobox5.Items.Add('第4学期');
  combobox5.Items.Add('第5学期');
  combobox5.Items.Add('第6学期');
  combobox5.Items.Add('第7学期');
  combobox5.Items.Add('第8学期');
   begin

 str_xy:='select * from 系别 where 学院 like '''+combobox3.Text+'''';
 combobox4.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;
ComboBox4.Items.Add( SsnoSea);
adoquery2.next;
adoquery2.ExecSQL;

end;

end;
end;
procedure TForm1.ComboBox5DropDown(Sender: TObject);
begin
if
 (ComboBox4.Text='') or (ComboBox4.Text='系别' )
then
   application.MessageBox('请选择系别','提示',$00000040)
   else
BitBtn7.Enabled :=true;              //判断是否取得str
//combobox5.Items.Add('第1学期');


end;

procedure TForm1.ComboBox3DropDown(Sender: TObject);
var  i:integer;
begin
DBGrid1.Visible:=false;
BitBtn6.Enabled:=false;
BitBtn7.Enabled:=false;

begin

 str_xy:='select 学院 from 学院';
 combobox3.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;
ComboBox3.Items.Add( SsnoSea);
adoquery2.next;
adoquery2.ExecSQL;

end;

end;

end;

procedure TForm1.BitBtn6Click(Sender: TObject);

begin

try
   str2:=ComboBox3.Text+ComboBox4.Text;
   str_sql:='select * from ['+str2+']' ;
   with two.Form2.ADOQuery1 do
   two.Form2.adoquery1.Close;
   two.Form2.adoquery1.SQL.Clear;
   two.Form2.adoquery1.SQL.Add(str_sql);
   two.Form2.adoquery1.Open;
except
messagedlg('无'+str2+'总开课表，请查找或重新设置目录',mterror,[mbok],0) ;
abort;
end;

 form2.ShowModal;

end;


procedure TForm1.ComboBox5Change(Sender: TObject);
begin
str2:=ComboBox3.Text+ComboBox4.Text;
str3:=ComboBox5.Text;
try
with ADOQuery1 do
adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('select 课程编号,课程名称,公共课或专业课,必修或限选或任选,学分数,学时数,['+str3+'] as 周时数,起讫周,考核方式,课程开设单位 from ['+str2+'] where ['+str3+'] is not null');  // where 第一学期 is not null
adoquery1.Open;
DBGrid1.Visible:=true;
except
DBGrid1.Visible:=false;
 BitBtn7.Enabled:=false;
messagedlg('无'+str2+'总开课表，请先填写开课表',mterror,[mbok],0) ;

abort;
end;

DBGrid1.Columns[0].Width:=55;
DBGrid1.Columns[1].Width:=100;
DBGrid1.Columns[2].Width:=83;
DBGrid1.Columns[3].Width:=90;
DBGrid1.Columns[4].Width:=40;
DBGrid1.Columns[5].Width:=50;
DBGrid1.Columns[6].Width:=50;
DBGrid1.Columns[7].Width:=62;
DBGrid1.Columns[8].Width:=50;
DBGrid1.Columns[9].Width:=80;

end;

procedure TForm1.BitBtn7Click(Sender: TObject);
var
 i,row,column:integer;
begin
    adoquery1.Close;
    adoquery1.Open;
    excelapplication1.Connect;
    ExcelApplication1.Workbooks.Open(dir+'\开课表.xls',
    EmptyParam,EmptyParam,EmptyParam,EmptyParam,
    EmptyParam,EmptyParam,EmptyParam,EmptyParam,
    EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
    excelapplication1.Visible[0]:=sc_visible;
    excelworkbook1.ConnectTo(excelapplication1.Workbooks[1]);
    excelworksheet1.ConnectTo(excelworkbook1.Worksheets[1] as _Worksheet);
    excelworksheet1.Cells.Item[3,6]:=ComboBox3.Text+'-'+ComboBox4.Text+'系';
    excelworksheet1.Cells.Item[4,14]:=datetostr(now) ;

     row:=7;
    while not(adoquery1.eof) do
    begin
    column:=2;
    for i:=1 to adoquery1.FieldCount do
    begin
       excelworksheet1.Cells.Item[row,column]:=adoquery1.Fields[i-1].AsString;
       excelworksheet1.Cells.Item[row,1]:=row-6;
       column:=column+1;
       end;
     adoquery1.Next;
     row:=row+1;
    end;
 begin

   SaveDialog1.Filter:='excel files (*.xls)';//保存类型
   savedialog1.FileName:=str2+str3+'开课表';           //取得保存文件名
   savedialog1.InitialDir:=dirgxc+'\';   //保存路径

if
SaveDialog1.Execute
then
   excelworksheet1.SaveAs(SaveDialog1.FileName);
end;
begin
excelapplication1.Quit;
excelapplication1.Disconnect;


end;




end;

procedure TForm1.RadioButton1Click(Sender: TObject);
begin
GroupBox4.Visible :=true;
Panel1.Visible:=true;
GroupBox6.Visible:=false;
BitBtn9.Enabled:=false;
Button1.Enabled:=false;


end;

procedure TForm1.RadioButton2Click(Sender: TObject);
begin
GroupBox4.Visible :=false;
Panel1.Visible:=false;
GroupBox6.Visible:=true;


end;

procedure TForm1.BitBtn8Click(Sender: TObject);


begin
        BitBtn9.Enabled:=true;
        Button1.Enabled:=true;
opendialog1.Filter:='excel files (*.xls)';//读取类型
if OpenDialog1.Execute
 then
 begin

dir_open:=opendialog1.filename;

     str_open:=ExtractFileName(ChangeFileExt(Opendialog1.FileName,''));

  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_open,
    EmptyParam,EmptyParam,EmptyParam,EmptyParam,
    EmptyParam,EmptyParam,EmptyParam,EmptyParam,
    EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);   //office 2003
    except
    messagedlg('打开出错',mterror,[mbok],0) ;
    end;
     //提示错误

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


end;


end;




procedure TForm1.Button1Click(Sender: TObject);
begin
//ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=text.mdb;Persist Security Info=False';
ExcelApplication1.Quit ;      //关闭excel
ExcelApplication1.Disconnect;   //解除连接

with   adoquery1   do
        begin  
                close;  
                sql.clear;  
                sql.add('delete from ['+str_open+']');  
                execsql;  
        end;



try
//aa.OpenCurrentDatabase(dirb+'\text.mdb',false);
 aa.OpenCurrentDatabaseOld(dirb+'\text.mdb',false);
aa.DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel8,
       str_open,dir_open,true,EmptyParam,EmptyParam);
       aa.Disconnect;
       BitBtn9.Enabled:=false;
       Button1.Enabled:=false;

       showmessage('修改成功');

       except
 messagedlg('打开数据库出错，如已打开数据库请先关闭',mterror,[mbok],0) ;
 abort
 end;
     //提示错误

       //aa.OpenCurrentDatabaseOld();    2003
       //aa.OpenCurrentDatabase('c:\aa.mdb')  2000
end;

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

procedure TForm1.CheckBox1Click(Sender: TObject);
begin
if checkbox1.State=cbUnchecked
then 
sc_visible:=false
else
sc_visible:=true;

end;

procedure TForm1.Button2Click(Sender: TObject);
var  i:integer;
begin
if Edit1.Text=''
then showmessage('学院不能为空')
else
begin
with adoquery2 do
adoquery2.Close;
adoquery2.SQL.Clear;
adoquery2.SQL.Add('insert into 学院 values('''+Edit1.Text+''')');
adoquery2.ExecSQL;
showmessage('添加成功');
edit1.Text:='';
end;
begin
str_xy:='select * from 学院';
 one.Form1.ListBox1.Clear;

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

end;        //查找学院
end;




end;






procedure TForm1.RadioButton3Click(Sender: TObject);
var  i:integer;
begin
GroupBox9.Visible:=true;
GroupBox12.Visible:=false;
 begin
str_xy:='select * from 学院';
 one.Form1.ListBox1.Clear;

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

end;        //查找学院
end;
  end;


procedure TForm1.RadioButton4Click(Sender: TObject);
var  i:integer;
begin
GroupBox9.Visible:=false;
GroupBox12.Visible:=true;
ComboBox7.Text:='';

 begin
str_xy:='select * from 系别';
 one.Form1.ListBox2.Clear;

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

end;        //查找系别


end;
end;