main.pas

自己做的用delphi开发的学生成绩管理系统。

          QueryXS.First;
          while not QueryXS.Eof do
          begin
            if dbgrid1.Columns.Items[i].PickList.IndexOf(QueryXS.FieldByName('班级名称').AsString)=-1 then
              dbgrid1.Columns.Items[i].PickList.Add(QueryXS.FieldByName('班级名称').AsString);
            QueryXS.Next;
          end;
        end;
      end
      else if AnsiCompareText(dbgrid1.Columns.Items[i].FieldName,'学号')=0 then
      begin
        dbgrid1.Columns.Items[i].PickList.Clear;
        QueryXS.First;
        while not QueryXS.Eof do
        begin
          if length(QueryXS.FieldByName('学号').AsString)>0 then
            dbgrid1.Columns.Items[i].PickList.Add(QueryXS.FieldByName('学号').AsString);
          QueryXS.Next;
        end;
      end
      else if AnsiCompareText(dbgrid1.Columns.Items[i].FieldName,'姓名')=0 then
      begin
        dbgrid1.Columns.Items[i].PickList.Clear;
        QueryXS.First;
        while not QueryXS.Eof do
        begin
          if length(QueryXS.FieldByName('姓名').AsString)>0 then
            dbgrid1.Columns.Items[i].PickList.Add(QueryXS.FieldByName('姓名').AsString);
          QueryXS.Next;
        end;
      end;
    end;
    QueryXS.Free;
    QueryKC.Free;
    dbgrid1.Visible:=true;
    dbgrid1.ReadOnly:=False;
    dbgrid1.FooterRowCount:=0;
    dbgrid1.FieldColumns['成绩'].DisplayFormat:='#.0';
    self.StatusBar1.Panels[1].Text:='学生成绩输入';
    for i:=0 to dbgrid1.Columns.Count-1 do
      dbgrid1.Columns[i].Title.TitleButton:=true;
  end;

end;

procedure Tfrmmain.FormKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if (ssCtrl in Shift)and(Key=13) then
    if (dbgrid1.DataSource.State=dsInsert) or (dbgrid1.DataSource.State=dsEdit) then
    begin
      dbgrid1.DataSource.DataSet.Post;
    end;
  if (ssCtrl in Shift)and(Key=ord('J')) then
    dbgrid1.AutoFitColWidths:=not dbgrid1.AutoFitColWidths;

end;

procedure Tfrmmain.N7Click(Sender: TObject);
var sql:string;
    score:double;
    Dt:TDateTime;
    foot:TColumnFooterEh;
    i:integer;
    valid:boolean;
begin
  if (datasource1.DataSet.State=dsEdit)or(datasource1.DataSet.State=dsInsert) then
  begin
    showmessage('正在编辑数据，请按Esc键取消或按Ctrl+Enter键确定');
    exit;
  end;

  valid:=false;
  if sorting then
    valid:=true
  else if frmQueryScore.showmodal=mrOk then
  begin
    sql:='select * from 学生成绩 where 1=1 ';
    if frmQueryScore.CheckBox1.Checked then
      sql:=sql+' and 班级名称='+''''+frmQueryScore.cmbClass.Text+'''';
    if frmQueryScore.CheckBox2.Checked then
      sql:=sql+' and 课程代码='+''''+frmQueryScore.cmbKCDM.Text+'''';
    if frmQueryScore.CheckBox3.Checked then
      sql:=sql+' and 学号='+''''+frmQueryScore.cmbXH.Text+'''';

    if frmQueryScore.CheckBox4.Checked then
    begin
      sql:=sql+' and 成绩';
      if frmQueryScore.cmbScore1.ItemIndex=0 then
        sql:=sql+'>='
      else if frmQueryScore.cmbScore1.ItemIndex=1 then
        sql:=sql+'<='
      else if frmQueryScore.cmbScore1.ItemIndex=2 then
        sql:=sql+'=';
      try
        score:=strtofloat(frmQueryScore.edtScore1.Text);
      except
        score:=-1;
      end;
      sql:=sql+floattostr(score);
      sql:=sql+' and 成绩';
      if frmQueryScore.cmbScore2.ItemIndex=0 then
        sql:=sql+'>='
      else if frmQueryScore.cmbScore2.ItemIndex=1 then
        sql:=sql+'<='
      else if frmQueryScore.cmbScore2.ItemIndex=2 then
        sql:=sql+'=';
      try
        score:=strtofloat(frmQueryScore.edtScore2.Text);
      except
        score:=-1;
      end;
      sql:=sql+floattostr(score);
    end;

    if frmQueryScore.CheckBox5.Checked then
    begin
      sql:=sql+' and 考试日期';
      if frmQueryScore.cmbDate1.ItemIndex=0 then
        sql:=sql+'>='
      else if frmQueryScore.cmbDate1.ItemIndex=1 then
        sql:=sql+'<=';
      Dt:=frmQueryScore.datetimepicker1.Date;
      sql:=sql+'#'+datetostr(dt)+'#';
      sql:=sql+' and 考试日期';
      if frmQueryScore.cmbDate2.ItemIndex=0 then
        sql:=sql+'>='
      else if frmQueryScore.cmbDate2.ItemIndex=1 then
        sql:=sql+'<=';
      Dt:=frmQueryScore.datetimepicker2.Date;
      sql:=sql+'#'+datetostr(dt)+'#';
    end;
    self.ADOQuery1.SQL.Text:=sql;
    valid:=true
  end;
  if valid then
  begin
    datasource1.DataSet.Close;
    datasource1.DataSet:=adoQuery1;
    self.ADOQuery1.Active:=true;
    adoQuery1.FieldByName('id').Visible:=false;
    dbgrid1.FieldColumns['班级名称'].Width:=150;
    dbgrid1.FieldColumns['成绩'].DisplayFormat:='#.0';
    dbgrid1.ReadOnly:=true;
    dbgrid1.Visible:=true;
    self.StatusBar1.Panels[1].Text:='成绩查询';

    dbgrid1.FooterRowCount:=2;
    dbgrid1.FooterColor:=clYellow;
    dbgrid1.SumList.Active:=true;
    DBGrid1.Columns[0].Footer.ValueType := fvtStaticText;
    DBGrid1.Columns[0].Footer.Value := '平均';
    DBGrid1.FieldColumns['成绩'].Footer.ValueType:=fvtAvg;
    foot:=DBGrid1.Columns[0].Footers.add;
    foot.ValueType := fvtStaticText;
    foot.Value:= '合计';
    foot:=DBGrid1.FieldColumns['成绩'].Footers.add;
    foot.ValueType:=fvtSum;
    for i:=0 to dbgrid1.Columns.Count-1 do
      dbgrid1.Columns[i].Title.TitleButton:=true;
  end;
end;

procedure Tfrmmain.C1Click(Sender: TObject);
begin
  DataSource1.DataSet.Close;
  dbgrid1.Visible:=false;
end;

procedure Tfrmmain.UpdateData(Sender:TObject;var Text:String;var Value:Variant;var UseText,Handled:Boolean);
var  Query:TAdoQuery;
begin
  if AnsiCompareText(dbgrid1.SelectedField.FieldName,'学号')=0 then
  begin
    Query:=TAdoQuery.Create(self);
    Query.connection:=self.ADOConnection1;
    Query.SQL.Text:='select 姓名 from 学生名单 where 班级名称='+''''+AdoTable1.fieldbyName('班级名称').asstring+''''+
                    ' and 学号='+''''+text+'''';
    Query.Active:=true;
    if not Query.IsEmpty then
      AdoTable1.FieldByName('姓名').AsString:=Query.FieldByName('姓名').AsString;
    Query.Free;
  end;
  if AnsiCompareText(dbgrid1.SelectedField.FieldName,'姓名')=0 then
  begin
    Query:=TAdoQuery.Create(self);
    Query.connection:=self.ADOConnection1;
    Query.SQL.Text:='select 学号 from 学生名单 where 班级名称='+''''+AdoTable1.fieldbyName('班级名称').asstring+''''+
                    ' and 姓名='+''''+text+'''';
    Query.Active:=true;
    if not Query.IsEmpty then
      AdoTable1.FieldByName('学号').AsString:=Query.FieldByName('学号').AsString;
    Query.Free;
  end;
  if AnsiCompareText(dbgrid1.SelectedField.FieldName,'课程代码')=0 then
  begin
    Query:=TAdoQuery.Create(self);
    Query.connection:=self.ADOConnection1;
    Query.SQL.text:='select 课程名称 from 课程目录 where 课程代码='+''''+Text+'''';
    Query.Active:=true;
    if not Query.IsEmpty then
      AdoTable1.FieldByName('课程名称').AsString:=Query.FieldByName('课程名称').AsString;
    Query.Free;
  end;
  if AnsiCompareText(dbgrid1.SelectedField.FieldName,'课程名称')=0 then
  begin
    Query:=TAdoQuery.Create(self);
    Query.connection:=self.ADOConnection1;
    Query.SQL.text:='select 课程代码 from 课程目录 where 课程名称='+''''+Text+'''';
    Query.Active:=true;
    if not Query.IsEmpty then
      AdoTable1.FieldByName('课程代码').AsString:=Query.FieldByName('课程代码').AsString;
    Query.Free;
  end;
end;


procedure Tfrmmain.FormDestroy(Sender: TObject);
begin
  dbgrid1.Free;
end;

procedure Tfrmmain.ADOTable1NewRecord(DataSet: TDataSet);
var sclass,kcdm,kcmc:string;
begin
  if adoTable1.TableName='学生成绩' then
  begin
    sclass:=frmselInputScore.cmbClass.Text;
    kcdm:=frmselInputScore.cmbKCDM.Text;
    kcmc:=frmselInputScore.cmbKCMc.Text;
    if AdoTable1.FieldByName('班级名称').ReadOnly then
    begin
      AdoTable1.FieldByName('班级名称').ReadOnly:=false;
      AdoTable1.FieldByName('班级名称').AsString:=sclass;
      AdoTable1.FieldByName('班级名称').ReadOnly:=true;
    end;
    if AdoTable1.FieldByName('课程代码').ReadOnly then
    begin
      AdoTable1.FieldByName('课程代码').ReadOnly:=false;
      AdoTable1.FieldByName('课程代码').AsString:=kcdm;
      AdoTable1.FieldByName('课程代码').ReadOnly:=true;
    end;
    if AdoTable1.FieldByName('课程名称').ReadOnly then
    begin
      AdoTable1.FieldByName('课程名称').ReadOnly:=false;
      AdoTable1.FieldByName('课程名称').AsString:=kcmc;
      AdoTable1.FieldByName('课程名称').ReadOnly:=true;
    end;
  end;
end;

procedure Tfrmmain.ADOTable1AfterPost(DataSet: TDataSet);
var sql:string;
    query:TAdoQuery;
    count1,count2:integer;
begin
  if AnsiCompareText(adoTable1.TableName,'学生成绩')=0 then
  begin
    Query:=TAdoQuery.Create(self);
    Query.Connection:=self.ADOConnection1;
    sql:='select count(*) from (select distinct 班级名称,学号,姓名,课程代码,课程名称,考试日期 from '+adotable1.TableName+')';
    Query.SQL.Text:=sql;
    Query.Active:=true;
    count1:=Query.Fields[0].AsInteger;
    Query.Active:=false;
    sql:='select count(*) from (select 班级名称,学号,姓名,课程代码,课程名称,考试日期 from '+adotable1.TableName+')';
    Query.SQL.Text:=sql;
    Query.Active:=true;
    count2:=Query.Fields[0].AsInteger;
    if count2<>count1 then
      showmessage('有重复记录，请删除');
    Query.Active:=false;
    Query.Free;
  end;

end;

procedure Tfrmmain.TitleBtnClick(Sender: TObject; ACol: Integer;Column: TColumnEh);
var fieldname:string;
begin
  if DataSource1.DataSet<>adoQuery1 then
    exit;
  if column.Title.SortMarker=smUpEh then
  begin
    column.Title.SortMarker:=smDownEh;
    Desc:=true;
  end
  else
  begin
    column.Title.SortMarker:=smUpEh;
    Desc:=false;
  end;
  fieldname:=column.FieldName;
  sorting:=true;
  if pos('order by',adoQuery1.SQL.Text)>0 then
    adoQuery1.SQL.Text:=copy(adoQuery1.SQL.Text,1,pos('order by',adoQuery1.SQL.Text)-1);
  adoQuery1.SQL.Text:=trim(adoQuery1.SQL.Text);
  adoQuery1.SQL.Text:=adoQuery1.SQL.Text+' order by '+FieldName;
  if Desc then
    adoQuery1.SQL.Text:=adoQuery1.SQL.Text+' DESC'
  else
    adoQuery1.SQL.Text:=adoQuery1.SQL.Text+' ASC';
  adoQuery1.Active:=false;
  if AnsiCompareText(self.StatusBar1.Panels[1].Text,'学生名单输入')=0 then
    N4Click(self)
  else if AnsiCompareText(self.StatusBar1.Panels[1].Text,'成绩查询')=0 then
    N7Click(self)
  else
    exit;
  if Desc then
    dbgrid1.FieldColumns[fieldname].Title.SortMarker:=smDownEh
  else
    dbgrid1.FieldColumns[fieldname].Title.SortMarker:=smupEh;
  sorting:=false;
end;

procedure Tfrmmain.Excel1Click(Sender: TObject);
begin
  if datasource1.DataSet.Active then
  begin
    if SaveDialog1.Execute then
    begin
      if fileexists(savedialog1.FileName) then
        if messagedlg('文件已存在，是否覆盖？',mtConfirmation,[mbYes,mbNo],0)=mrno then
          exit
        else
          deletefile(savedialog1.FileName);
      WriteExcel(datasource1.dataset,savedialog1.FileName,'',1);
    end;
  end
  else
    showmessage('没有打开数据');
end;

procedure TFrmMain.WriteExcel(AdsData: TDataSet; sName, Title:string;start:integer);
var
  ExcelApplication1: TExcelApplication;
  ExcelWorksheet1: TExcelWorksheet;
  ExcelWorkbook1: TExcelWorkbook;
  i, j: integer;
  filename: string;
begin
  filename :=sName;
  try
    ExcelApplication1 := TExcelApplication.Create(Application);
    ExcelWorksheet1 := TExcelWorksheet.Create(Application);
    ExcelWorkbook1 := TExcelWorkbook.Create(Application);
    ExcelApplication1.Connect;
  except
    Application.Messagebox('Excel 没有安装!','Hello',MB_ICONERROR + mb_Ok);
    Abort;
  end;
  try
    ExcelApplication1.Workbooks.Add(EmptyParam, 0);
    ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
    ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _worksheet);
    AdsData.First;
    for j := start to AdsData.Fields.Count - 1 do
      begin
        ExcelWorksheet1.Cells.item[3, j - start+1] := AdsData.Fields[j].DisplayLabel;
        ExcelWorksheet1.Cells.item[3, j - start+1].font.size :='10';
      end;
    for i := 4 to AdsData.RecordCount + 3 do
      begin
        for j := start to AdsData.Fields.Count - 1 do
          begin
            ExcelWorksheet1.Cells.item[i, j - start+1] :=AdsData.Fields[j].Asstring;
            ExcelWorksheet1.Cells.item[i, j - start+1].font.size := '10';
          end;
        AdsData.Next;
      end;
    ExcelWorksheet1.Columns.AutoFit;
    ExcelWorksheet1.Cells.item[1, 2] := Title;
    ExcelWorksheet1.Cells.Item[1, 2].font.size := '14';
    ExcelWorksheet1.SaveAs(filename);
    Application.Messagebox(pchar('数据成功导出' + filename), 'Hello',
      mb_Ok);
  finally
    ExcelApplication1.Disconnect;
    ExcelApplication1.Quit;
    ExcelApplication1.Free;
    ExcelWorksheet1.Free;
    ExcelWorkbook1.Free;
  end;
end;


procedure Tfrmmain.H1Click(Sender: TObject);
var s:string;
    Query:TAdoQuery;
begin
  s:='';
  Query:=TadoQuery.Create(self);
  Query.Connection:=self.ADOConnection1;
  Query.SQL.Text:='select 内容 from 备注 where 项目='+''''+'帮助'+'''';
  Query.Active:=true;
  if not Query.IsEmpty then
  begin
    Query.First;
    s:=Query.Fields[0].AsString;
  end;
  Query.Active:=false;
  Query.Free;
  messagedlg(s,mtInformation,[mbOk],0);
end;

procedure Tfrmmain.A1Click(Sender: TObject);
var s:string;
    Query:TAdoQuery;
begin
  s:='';
  Query:=TadoQuery.Create(self);
  Query.Connection:=self.ADOConnection1;
  Query.SQL.Text:='select 内容 from 备注 where 项目='+''''+'关于'+'''';
  Query.Active:=true;
  if not Query.IsEmpty then
  begin
    Query.First;
    s:=Query.Fields[0].AsString;
  end;
  Query.Active:=false;
  Query.Free;
  messagedlg(s,mtInformation,[mbOk],0);
end;

end.