xjkprint.pas

excel import excel importe xcel importexcel import

            Application.ProcessMessages ;
            ///////
            isBKCX :=false;
            //判断是不是补考
            if (ADOcj.Fields[7].AsString='01') then
               begin
                  ExpExcel.cells[row,col].value:=ADOcj.Fields[1].AsString;
                  isBKCX :=false;
               end
            else if (ADOcj.Fields[7].AsString='02') then
               begin
                  ExpExcel.cells[row,col].value:=ADOcj.Fields[1].AsString+'[补]';
                  isBKCX :=true;
               end
            else
               begin
                  row := row -1;
                  ExpExcel.cells[row,col].value:=ADOcj.Fields[1].AsString+'[重]';
                  isBKCX :=true;
               end;
             if (Length(ExpExcel.cells[row,col].value)>20) then
                ExpExcel.worksheets[1].range[Chr(rCells)+IntToStr(row)+':'+Chr(rCells+1)+IntToStr(row)].font.size:=8;

            ExpExcel.cells[row,col+2].value:=ADOcj.Fields[2].AsString ;
            if (trim(ADOcj.Fields[2].AsString)='必修课') then
            begin
               if (isBKCX=false) then
               begin
                  bxkZXF:= bxkZXF +strtofloat(ADOcj.Fields[5].AsString);
                  bxkZXS:= bxkZXS +strtoint(ADOcj.Fields[4].AsString);
               end;
            end;
            //分数方式
            if (ADOcj.Fields[6].AsString<>'0') then      //正常分数值
            begin
               fsfsStr :='select cj_dj__mc from cj_dj where cj_dj__bj='''+ADOcj.Fields[6].AsString+''' and cj_dj__fs1<='''+ADOcj.Fields[3].AsString +''' and cj_dj__fs2>'''+ADOcj.Fields[3].AsString+'''';
               QueryData(ADOfsfs,fsfsStr);
               ExpExcel.cells[row,col+3].value:=ADOfsfs.Fields[0].AsString;
            end else     //不正常
               ExpExcel.cells[row,col+3].value:=ADOcj.Fields[3].AsString ;
            ExpExcel.worksheets[1].range[Chr(rCells+3)+IntToStr(row)+':'+Chr(rCells+3)+IntToStr(row)].horizontalalignment:=xlHAlignCenter;
            ExpExcel.worksheets[1].range[Chr(rCells+5)+IntToStr(row)+':'+Chr(rCells+5)+IntToStr(row)].horizontalalignment:=xlHAlignCenter;

            ///////////////////////////////////////////////////////////////////////
            ExpExcel.worksheets[1].range[Chr(rCells)+IntToStr(row)+':'+Chr(rCells+1)+IntToStr(row)].merge(true);
            //////////////////////////////////////////////////////////////合并单元格 课程

            ExpExcel.cells[row,col+4].value:=ADOcj.Fields[4].AsString ;
            ExpExcel.cells[row,col+5].value:=ADOcj.Fields[5].AsString ;

            if (isBKCX=false) then     //正常考试
            begin
               xqZXS := xqZXS + strtoint(ADOcj.Fields[4].AsString);
            end;
            xqZXF := xqZXF + strtofloat(ADOcj.Fields[5].AsString);
            row :=row +1;
            ADOcj.Next;
         end;
      end;//>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
      ////////////////////////合并成绩单元//////////////////////////////////////
      i := 0;
      while (row+i)<xqRow do
      begin
         cellStr :=Chr(rCells)+IntToStr(row+i)+':'+Chr(rCells+1)+IntToStr(row+i);
         ExpExcel.worksheets[1].range[cellStr].merge(true);  //合并单元格
         i :=i +1;
      end;
      //输出学期总学时、总学分
      if not ADOexe.Eof then
         ExpExcel.cells[xqRow,xqCol].value:='学期学时：'+inttostr(xqZXS) +'       学期学分' + floattostr(xqZXF);
      ZXS :=ZXS +xqZXS;
      ZXF :=ZXF +xqZXF;
      rCells :=rCells +6;
      col := col+6;

      xnxq8 := xnxq8 +1;
      RowXNXQ := RowXNXQ +1;
      ADOexe.Next;
   end;

   ExpExcel.cells[xqRow+2,1].value:='总学时：' ;
   ExpExcel.worksheets[1].range['A'+IntToStr(xqRow+2)+':'+'A'+IntToStr(xqRow+2)].horizontalalignment:=xlHAlignRight;
   ExpExcel.cells[xqRow+2,2].value:=inttostr(ZXS) ;
   ExpExcel.worksheets[1].range['A'+IntToStr(xqRow+2)+':'+'A'+IntToStr(xqRow+2)].horizontalalignment:=xlHAlignLeft;
   ExpExcel.cells[xqRow+2,3].value:='总学分：' ;
   ExpExcel.worksheets[1].range['C'+IntToStr(xqRow+2)+':'+'D'+IntToStr(xqRow+2)].merge(true);
   ExpExcel.worksheets[1].range['C'+IntToStr(xqRow+2)+':'+'C'+IntToStr(xqRow+2)].horizontalalignment:=xlHAlignRight;
   ExpExcel.cells[xqRow+2,5].value:=floattostr(ZXF) ;
   ExpExcel.worksheets[1].range['E'+IntToStr(xqRow+2)+':'+'F'+IntToStr(xqRow+2)].merge(true);
   ExpExcel.worksheets[1].range['E'+IntToStr(xqRow+2)+':'+'F'+IntToStr(xqRow+2)].horizontalalignment:=xlHAlignLeft;
   ExpExcel.cells[xqRow+2,7].value:='必修课总学时：' ;
   ExpExcel.worksheets[1].range['G'+IntToStr(xqRow+2)+':'+'H'+IntToStr(xqRow+2)].merge(true);
   ExpExcel.worksheets[1].range['G'+IntToStr(xqRow+2)+':'+'H'+IntToStr(xqRow+2)].horizontalalignment:=xlHAlignRight;
   ExpExcel.cells[xqRow+2,9].value:=inttostr(bxkZXS) ;
   ExpExcel.worksheets[1].range['I'+IntToStr(xqRow+2)+':'+'I'+IntToStr(xqRow+2)].horizontalalignment:=xlHAlignLeft;
   ExpExcel.cells[xqRow+2,12].value:='必修课总学分：' ;
   ExpExcel.worksheets[1].range['L'+IntToStr(xqRow+2)+':'+'M'+IntToStr(xqRow+2)].merge(true);
   ExpExcel.worksheets[1].range['L'+IntToStr(xqRow+2)+':'+'M'+IntToStr(xqRow+2)].horizontalalignment:=xlHAlignRight;
   ExpExcel.cells[xqRow+2,14].value:=floattostr(bxkZXF) ;
   ExpExcel.worksheets[1].range['N'+IntToStr(xqRow+2)+':'+'N'+IntToStr(xqRow+2)].horizontalalignment:=xlHAlignLeft;
   //ExpExcel.cells[xqRow+3,1].value:='毕业设计(论文)题目：' ;
   //ExpExcel.cells[xqRow+3,7].value:='毕业设计(论文)成绩：' ;
   ExpExcel.cells[xqRow+3,19].value:='福建工程学院' ;
   ExpExcel.cells[xqRow+4,19].value:='日期：' ;
   ExpExcel.cells[xqRow+4,20].value:=Date() ;
   ExpExcel.worksheets[1].range['T'+IntToStr(xqRow+4)+':'+'U'+IntToStr(xqRow+4)].merge(true);
   ExpExcel.worksheets[1].range['T'+IntToStr(xqRow+4)+':'+'U'+IntToStr(xqRow+4)].horizontalalignment:=xlHAlignLeft;

   ExpExcel.Visible := true;
   //ExpExcel.Quit;
   ExpExcel := Unassigned;
   ExpExcel := null;
   screen.Cursor := crDefault;
end;

//导出数据-班级
procedure TForm1.ExcelExportBJ;
var
   ExpExcel: Variant;
   bjEdit,bjStr,oneStr:String;
   pageRow,pageCol,pageCount,pageRecord:integer;
   tempStr :String;
   RowXNXQ ,xnxq8:integer;   //当前学年学期
   CurrentRow:integer;      //当前行号
   xqZXS:Integer;      //学期总学分，学时
   xqZXF:Single;
   ZXS:Integer;//总学时、分
   ZXF:Single;
   cellStr :String;
   rCells :Integer;
   xqRow,xqCol,i:Integer;
   bxkZXS:Integer;
   bxkZXF:Single;
   isBKCX :boolean;
begin
   screen.Cursor := crHourGlass;
   try
      ExpExcel := CreateOleObject('Excel.Application');
      ExpExcel.WorkBooks.Add;
   except
      MessageDlg('Can not creat Excel 2000 file !', mtWarning, [mbOK], 0);
      screen.Cursor := crDefault;
      exit;
   end;
   pageRow :=1;        //当前行数
   tempStr :='';       //字符串
   pageCount :=1;      //当前页数
   pageRecord :=50;    //每页记录数
   cellStr :='';
   bjEdit :=trim(SnEdit2.Text) ;
   bjStr :='select xj_xjk__xh,xj_xjk__bh,xj_xjk__xm from xj_xjk where xj_xjk__bh='''+bjEdit+'''';
   QueryData(ADOexe,bjStr);    //查询班级的所有人员
   ADOexe.First ;
   SnProgressBar1.Max :=ADOexe.RecordCount ;
   SnProgressBar1.Min :=0;
   SnProgressBar1.Position :=0;
   while not ADOexe.Eof do
   begin
      RowXNXQ := 1;       //学年学期数
      pageCol :=1;        //列数
      ZXF :=0;
      ZXS :=0;
      bxkZXF :=0;
      bxkZXS :=0;
      rCells :=65;        //
      ExpExcel.Caption := '福建工程学院学生成绩单';
      ExpExcel.worksheets[1].cells[pageRow,pageCol].value:='福建工程学院学生成绩单';

      tempStr :='A'+inttostr(pageRow)+':'+'X'+IntToStr(pageRow);
      ExpExcel.worksheets[1].range[tempStr].merge(true);               //合并
      ExpExcel.worksheets[1].range[tempStr].font.color:=clblue;
      ExpExcel.worksheets[1].range[tempStr].font.name:='宋体';
      ExpExcel.worksheets[1].range[tempStr].font.size:=30;
      ExpExcel.worksheets[1].range[tempStr].horizontalalignment:=xlHAlignCenter;
      ExpExcel.worksheets[1].range[tempStr].VerticalAlignment:=2;

      ExpExcel.worksheets[1].Rows[pageRow].RowHeight := 2/0.035;
      tempStr :='A'+intToStr(pageRow+1)+':'+'X'+IntToStr(pageRow+53);
      ExpExcel.worksheets[1].range[tempStr].font.size:=10;

      pageRow := pageRow +1;
      oneStr := 'select a.xj_xjk__xh,a.xj_xjk__xm,a.xj_xjk__bh,c.sys_yx__yxm,b.SYS_ZY__ZYM,e.XJ_BYK__BYH from xj_xjk a,SYS_ZY b,SYS_YX c,SYS_BJ d,XJ_BYK e';
      oneStr := oneStr + '  where a.xj_xjk__bh=d.sys_bj__bh and  b.sys_zy__yxh=c.sys_yx__yxh and d.sys_bj__zyh=b.sys_zy__zyh and b.sys_zy__yxh=c.sys_yx__yxh and a.XJ_XJK__XH*=e.XJ_BYK__XH and a.xj_xjk__xh='''+ADOexe.Fields[0].AsString+'''';
      QueryData(ADOcj,oneStr);
      ADOcj.First ;
      while not ADOcj.Eof do  //学生学籍情况
      begin
         SnStatusBar1.SimpleText :='..........正在处理:  ['+ADOexe.Fields[0].AsString+']';
         tempStr :='A'+inttostr(pageRow)+':'+'B'+IntToStr(pageRow);
         ExpExcel.worksheets[1].Range[tempStr].NumberFormatLocal := '@';  //设置数值的格式'000010'

         tempStr :='A'+IntToStr(pageRow)+':'+'X'+IntToStr(pageRow);
         ExpExcel.worksheets[1].range[tempStr].VerticalAlignment:=2;
         ExpExcel.worksheets[1].Rows[pageRow].RowHeight := 1/0.035;

         ExpExcel.cells[pageRow,1].value:='学号:';
         ExpExcel.cells[pageRow,4].value:='姓名:';
         ExpExcel.cells[pageRow,7].value:='班级:';
         ExpExcel.cells[pageRow,10].value:='院系:';
         ExpExcel.cells[pageRow,15].value:='专业:';
         ExpExcel.cells[pageRow,19].value:='毕业证号:';
         tempStr :='T'+inttostr(pageRow)+':'+'X'+IntToStr(pageRow);
         ExpExcel.worksheets[1].Range[tempStr].NumberFormatLocal := '@';  //设置数值的格式'000010'
         //ExpExcel.cells[pageRow,20].value:='页号:'+IntToStr(pageCount);
         ExpExcel.cells[pageRow,2].value:=ADOcj.Fields[0].AsString ;
         ExpExcel.cells[pageRow,5].value:=ADOcj.Fields[1].AsString ;
         ExpExcel.cells[pageRow,8].value:=ADOcj.Fields[2].AsString ;
         ExpExcel.cells[pageRow,11].value:=ADOcj.Fields[3].AsString ;
         ExpExcel.cells[pageRow,16].value:=ADOcj.Fields[4].AsString ;
         ExpExcel.cells[pageRow,20].value:=ADOcj.Fields[5].AsString ;
         ////////////////////////////////第一行///////////////////////////////////////
         cellStr :=Chr(rCells+1)+IntToStr(pageRow)+':'+Chr(rCells+2)+IntToStr(pageRow);
         ExpExcel.worksheets[1].range[cellStr].merge(true);  //合并单元格
         cellStr :=Chr(rCells+4)+IntToStr(pageRow)+':'+Chr(rCells+5)+IntToStr(pageRow);
         ExpExcel.worksheets[1].range[cellStr].merge(true);  //合并单元格
         cellStr :=Chr(rCells+7)+IntToStr(pageRow)+':'+Chr(rCells+8)+IntToStr(pageRow);
         ExpExcel.worksheets[1].range[cellStr].merge(true);  //合并单元格
         cellStr :=Chr(rCells+10)+IntToStr(pageRow)+':'+Chr(rCells+13)+IntToStr(pageRow);
         ExpExcel.worksheets[1].range[cellStr].merge(true);  //合并单元格
         cellStr :=Chr(rCells+15)+IntToStr(pageRow)+':'+Chr(rCells+17)+IntToStr(pageRow);
         ExpExcel.worksheets[1].range[cellStr].merge(true);  //合并单元格

         cellStr :=Chr(rCells+19)+IntToStr(pageRow)+':'+Chr(rCells+23)+IntToStr(pageRow);
         ExpExcel.worksheets[1].range[cellStr].merge(true);  //合并单元格
         ///////////////////////////////////////////////////////////////////////////

         pageRow :=pageRow +1;
         CurrentRow := pageRow ;//记录当前行状态
         //学年学期
         tempStr :='select distinct a.CJ_XSCJD__XNXQ, b.sys_xnxq__xnxqwz from cj_xscjd a,SYS_XNXQ b where a.cj_xscjd__xnxq=b.sys_xnxq__xnxqh and a.cj_xscjd__xh='''+ADOexe.Fields[0].AsString +''' order by a.cj_xscjd__xnxq ';
         QueryData(ADOfsfs,tempStr);
         ADOfsfs.First ;
         SnProgressBar2.Max :=ADOfsfs.RecordCount ;
         SnProgressBar2.Min :=0;
         SnProgressBar2.Position :=0;
         xnxq8 :=0;
         //while not ADOfsfs.Eof do     // 学年学期
         while xnxq8<8 do
         begin
            pageRow := CurrentRow ;
            xqZXS :=0;              //每学年中总学时
            xqZXF :=0;              //每学年中总学分
            if ((RowXNXQ mod 5)=0) then    //一行满4个学期就换一行
            begin
               pageRow :=pageRow + 20;     //20
               pageCol := 1;
               CurrentRow := pageRow;     //学期换行后保存行
               rCells :=65;
            end;
            ////合并学分学时单元格
            cellStr :=Chr(rCells)+IntToStr(pageRow +19) +':'+Chr(rCells+5)+IntToStr(pageRow+19);//学时、分
            ExpExcel.worksheets[1].range[cellStr].merge(true);

            xqRow :=pageRow+19;   //19
            xqCol :=pageCol;
            if not ADOfsfs.Eof then
               ExpExcel.cells[pageRow,pageCol].value:=ADOfsfs.Fields[1].AsString ;
            cellStr :=Chr(rCells)+IntToStr(pageRow)+':'+Chr(rCells+5)+IntToStr(pageRow);   //合并学年学期
            //showmessage(cellStr);
            ExpExcel.worksheets[1].range[cellStr].merge(true);                             //合并单元格   学期
            ExpExcel.worksheets[1].range[cellStr].horizontalalignment:=xlHAlignCenter;     //中间对齐
            ////////////////////////////////////////////////////////////////////
            cellStr :='A'+IntToStr(pageRow)+':'+'X'+IntToStr(pageRow+1);
            ExpExcel.worksheets[1].Range[cellStr].Borders[1].Weight := 2;
            ExpExcel.worksheets[1].Range[cellStr].Borders[2].Weight := 2;
            ExpExcel.worksheets[1].Range[cellStr].Borders[3].Weight := 2;
            ExpExcel.worksheets[1].Range[cellStr].Borders[4].Weight := 2;
            /////////////////////////////////////////////////////////////////////
            cellStr :='A'+IntToStr(pageRow+1)+':'+'X'+IntToStr(pageRow+18);
            ExpExcel.worksheets[1].Range[cellStr].Borders[1].Weight := 2;
            ExpExcel.worksheets[1].Range[cellStr].Borders[2].Weight := 2;
            ////////////////////////////////////////////////////////////////////
            cellStr :='A'+IntToStr(pageRow+19)+':'+'X'+IntToStr(pageRow+19);
            ExpExcel.worksheets[1].Range[cellStr].Borders[1].Weight := 2;
            ExpExcel.worksheets[1].Range[cellStr].Borders[2].Weight := 2;
            ExpExcel.worksheets[1].Range[cellStr].Borders[3].Weight := 2;
            ExpExcel.worksheets[1].Range[cellStr].Borders[4].Weight := 2;
            //////////////////////////////////////////////////////////////////
            pageRow := pageRow +1;
            //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
            cellStr :=Chr(rCells)+IntToStr(pageRow)+':'+Chr(rCells+1)+IntToStr(pageRow);        //合并课程
            ExpExcel.worksheets[1].range[cellStr].merge(true);

            ExpExcel.cells[pageRow,pageCol].value:='课程名称' ;
            ExpExcel.cells[pageRow,pageCol+2].value:='类别';