📄 xjkprint.pas
字号:
unit XJKPrint;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, SnEdit, SnButton, DB, ADODB, SnLabel, DBCtrls,
SnDBListBox, Grids, DBGrids, SnDBGrid, SnForm, SnDBMemo, SnMemo,
SnListBox,Comobj,Excel2000, SnProgressBar, SnCustomComboBox, SnComboBox,
ComCtrls, SnStatusBar, ExtCtrls;
type
TForm1 = class(TForm)
SnButton1: TSnButton;
SnButton2: TSnButton;
SnEdit_user: TSnEdit;
SnEdit_pass: TSnEdit;
ADOQuery: TADOQuery;
ADOConn: TADOConnection;
SnEdit_server: TSnEdit;
SnEdit_Database: TSnEdit;
SnLabel1: TSnLabel;
SnLabel2: TSnLabel;
SnLabel3: TSnLabel;
SnLabel4: TSnLabel;
DataSource1: TDataSource;
SnForm1: TSnForm;
SnListBox1: TSnListBox;
DataSource2: TDataSource;
ADQxs: TADOQuery;
SnEdit1: TSnEdit;
SnLabel5: TSnLabel;
SnButton3: TSnButton;
SnComboBox1: TSnComboBox;
SnEdit2: TSnEdit;
SnLabel6: TSnLabel;
SnButton4: TSnButton;
ADOexe: TADOQuery;
SnButton5: TSnButton;
ADOcj: TADOQuery;
ADOfsfs: TADOQuery;
ADObk: TADOQuery;
SnStatusBar1: TSnStatusBar;
ADObj: TADOQuery;
ADOtwo: TADOQuery;
SnProgressBar1: TSnProgressBar;
SnProgressBar2: TSnProgressBar;
SnDBGrid1: TSnDBGrid;
adocl: TADOQuery;
procedure SnButton1Click(Sender: TObject);
procedure SnListBox1DblClick(Sender: TObject);
procedure SnButton3Click(Sender: TObject);
procedure SnButton4Click(Sender: TObject);
procedure SnComboBox1Change(Sender: TObject);
procedure SnButton5Click(Sender: TObject);
procedure SnButton2Click(Sender: TObject);
procedure SnDBGrid1CellClick(Column: TColumn);
private
{ Private declarations }
procedure ExpData;
procedure ExcelExport;
procedure ExcelExportBJ;
procedure QueryData(pQuery: TADOQuery;pSql: string);
//procedure InsertData(pQuery: TADOQuery;pSQl: string);
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.QueryData(pQuery: TADOQuery; pSql: string);
begin
pQuery.Close;
pQuery.SQL.Clear;
pQuery.SQL.Add(pSQL);
pQuery.Open;
end;
procedure TForm1.ExpData;
begin
//if not ADOQuery.active then ADOQuery.Open ;
SnListBox1.Items.Clear ;
QueryData(ADOQuery,'select SYS_BJ__BH from SYS_BJ');
while not ADOQuery.Eof do
begin
SnListBox1.Items.Add(ADOQuery.Fields[0].AsString);
ADOQuery.Next;
end;
end;
//导出数据-个人
procedure TForm1.ExcelExport;
var
ExpExcel: Variant;
row,i,col : integer;
RowXNXQ,tRow,xnxq8 :integer;
Str,xhStr : String;
fsfsStr :String; //分数方式
bkStr :String;//补考、重修
rCells:Integer; //合并单元格
cellStr,tempStr:String;
xqCells:String;
xqZXS: Integer;//总学时
xqZXF: Single;//总学分
bxkZXS:Integer;//必修课
bxkZXF:Single;
ZXS:Integer;
ZXF:Single;
xqRow,xqCol:integer;
isBKCX:boolean;
begin
screen.Cursor := crHourGlass;
Str :='';
xhStr :='';
fsfsStr :='';
bkStr :='';
rCells :=65;
cellStr :='';
xqCells :='';
ZXS:=0;
ZXF:=0;
bxkZXS :=0;
bxkZXF :=0;
xhStr :=trim(SnEdit2.Text);
try
ExpExcel := CreateOleObject('Excel.Application');
ExpExcel.WorkBooks.Add;
except
MessageDlg('Can not creat Excel 2000 file !', mtWarning, [mbOK], 0);
screen.Cursor := crDefault;
exit;
end;
ExpExcel.Caption := '福建工程学院学生成绩单';
ExpExcel.worksheets[1].cells[1,1].value:='福建工程学院学生成绩单';
ExpExcel.worksheets[1].Range['a2:b2'].NumberFormatLocal := '@'; //设置数值的格式'000010'
ExpExcel.worksheets[1].range['A1:X1'].merge(true); //合并
ExpExcel.worksheets[1].range['A1:X1'].font.color:=clblue;
ExpExcel.worksheets[1].range['A1:X1'].font.name:='宋体';
ExpExcel.worksheets[1].range['A1:X1'].font.size:=30;
ExpExcel.worksheets[1].range['A1:X1'].horizontalalignment:=xlHAlignCenter;
ExpExcel.worksheets[1].range['A1:X1'].VerticalAlignment:=2;
ExpExcel.worksheets[1].Rows[1].RowHeight := 2/0.035;
ExpExcel.worksheets[1].range['A2:X46'].font.size:=10;
//ExpExcel.worksheets[1].page.font.size:=10;
row := 2;
col := 1;
RowXNXQ := 1;
cellStr :='A'+IntToStr(row)+':'+'X'+IntToStr(row);
ExpExcel.worksheets[1].range[cellStr].VerticalAlignment:=2;
ExpExcel.worksheets[1].Rows[row].RowHeight := 1/0.035;
ExpExcel.cells[row,1].value:='学号:';
ExpExcel.cells[row,4].value:='姓名:';
ExpExcel.cells[row,7].value:='班级:';
ExpExcel.cells[row,10].value:='院系:';
ExpExcel.cells[row,15].value:='专业:';
ExpExcel.cells[row,19].value:='毕业证号:';
ExpExcel.worksheets[1].Range['T2:X2'].NumberFormatLocal := '@'; //设置数值的格式'000010'
Str :='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';
Str := Str +' 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';
Str := Str +' and a.xj_xjk__xh='''+xhStr+'''';
QueryData(ADOexe,Str);
ADOexe.First ;
if not ADOexe.Eof then //第一行
begin
ExpExcel.cells[row,2].value:=ADOexe.Fields[0].AsString ;
ExpExcel.cells[row,5].value:=ADOexe.Fields[1].AsString ;
ExpExcel.cells[row,8].value:=ADOexe.Fields[2].AsString ;
ExpExcel.cells[row,11].value:=ADOexe.Fields[3].AsString ;
ExpExcel.cells[row,16].value:=ADOexe.Fields[4].AsString ;
ExpExcel.cells[row,20].value:=ADOexe.Fields[5].AsString ;
end;
cellStr :=Chr(rCells+1)+IntToStr(row)+':'+Chr(rCells+2)+IntToStr(row);
ExpExcel.worksheets[1].range[cellStr].merge(true); //合并单元格
cellStr :=Chr(rCells+4)+IntToStr(row)+':'+Chr(rCells+5)+IntToStr(row);
ExpExcel.worksheets[1].range[cellStr].merge(true); //合并单元格
cellStr :=Chr(rCells+7)+IntToStr(row)+':'+Chr(rCells+8)+IntToStr(row);
ExpExcel.worksheets[1].range[cellStr].merge(true); //合并单元格
cellStr :=Chr(rCells+10)+IntToStr(row)+':'+Chr(rCells+13)+IntToStr(row);
ExpExcel.worksheets[1].range[cellStr].merge(true); //合并单元格
cellStr :=Chr(rCells+15)+IntToStr(row)+':'+Chr(rCells+17)+IntToStr(row);
ExpExcel.worksheets[1].range[cellStr].merge(true); //合并单元格
cellStr :=Chr(rCells+19)+IntToStr(row)+':'+Chr(rCells+23)+IntToStr(row);
ExpExcel.worksheets[1].range[cellStr].merge(true); //合并单元格
row :=row +1;
tRow := row;
Str :='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='''+xhStr+''' order by a.cj_xscjd__xnxq ';
QueryData(ADOexe,Str);
SnProgressBar1.Max :=ADOexe.RecordCount ;
SnProgressBar1.Min :=0;
SnProgressBar1.Position :=0;
xnxq8 :=0;
//while not ADOexe.Eof do // 学年学期
while xnxq8<8 do
begin
row := tRow ;
xqZXS :=0;
xqZXF :=0.0;
if ((RowXNXQ mod 5)=0) then //一行满4个学期就换一行
begin
row :=row + 20;
col := 1;
tRow :=row;
rCells :=65;
end;
if (SnProgressBar1.Position<SnProgressBar1.Max) then
SnProgressBar1.Position:=SnProgressBar1.Position+1 ;
Application.ProcessMessages ;
if not ADOexe.Eof then
ExpExcel.cells[row,col].value:=ADOexe.Fields[1].AsString ;
cellStr :=Chr(rCells)+IntToStr(row)+':'+Chr(rCells+5)+IntToStr(row); //合并学年学期
xqCells :=Chr(rCells)+IntToStr(row +19) +':'+Chr(rCells+5)+IntToStr(row+19);//总学时、分
ExpExcel.worksheets[1].range[xqCells].merge(true); //合并单元格
xqRow :=row+19;
xqCol :=col;
SnStatusBar1.SimpleText :='..........'+ADOexe.Fields[1].AsString+cellStr+':['+InttoStr(row)+','+inttostr(col)+']';
ExpExcel.worksheets[1].range[cellStr].merge(true); //合并单元格 学期
ExpExcel.worksheets[1].range[cellStr].horizontalalignment:=xlHAlignCenter; //中间对齐
ExpExcel.worksheets[1].Range['A3:X4'].Borders[1].Weight := 2;
ExpExcel.worksheets[1].Range['A3:X4'].Borders[2].Weight := 2;
ExpExcel.worksheets[1].Range['A3:X4'].Borders[3].Weight := 2;
ExpExcel.worksheets[1].Range['A3:X4'].Borders[4].Weight := 2;
ExpExcel.worksheets[1].Range['A5:X21'].Borders[1].Weight := 2;
ExpExcel.worksheets[1].Range['A5:X21'].Borders[2].Weight := 2;
ExpExcel.worksheets[1].Range['A22:X24'].Borders[1].Weight := 2;
ExpExcel.worksheets[1].Range['A22:X24'].Borders[2].Weight := 2;
ExpExcel.worksheets[1].Range['A22:X24'].Borders[3].Weight := 2;
ExpExcel.worksheets[1].Range['A22:X24'].Borders[4].Weight := 2;
ExpExcel.worksheets[1].Range['A25:X41'].Borders[1].Weight := 2;
ExpExcel.worksheets[1].Range['A25:X41'].Borders[2].Weight := 2;
ExpExcel.worksheets[1].Range['A42:X42'].Borders[1].Weight := 2;
ExpExcel.worksheets[1].Range['A42:X42'].Borders[2].Weight := 2;
ExpExcel.worksheets[1].Range['A42:X42'].Borders[3].Weight := 2;
ExpExcel.worksheets[1].Range['A42:X42'].Borders[4].Weight := 2;
row := row +1;
tempStr :=Chr(rCells)+IntToStr(row)+':'+Chr(rCells+1)+IntToStr(row); //合并课程
ExpExcel.worksheets[1].range[tempStr].merge(true);
ExpExcel.cells[row,col].value:='课程名称' ;
ExpExcel.cells[row,col+2].value:='类别';
ExpExcel.cells[row,col+3].value:='成绩' ;
ExpExcel.cells[row,col+4].value:='学时' ;
ExpExcel.cells[row,col+5].value:='学分' ;
//列宽
ExpExcel.Columns[col+3].ColumnWidth := 5;
ExpExcel.Columns[col+4].ColumnWidth := 4;
ExpExcel.Columns[col+5].ColumnWidth := 4;
row :=row +1 ;
if not ADOexe.Eof then
begin ///////////><<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//Str :='select a.cj_xscjd__xh,b.sys_kc__kcm,c.sys_kcxx__xxlbm,max(a.cj_xscjd__fs),max(a.cj_xscjd__xs),a.cj_xscjd__xf,a.cj_xscjd__fsfs,max(a.cj_xscjd__ksxz) from cj_xscjd a,SYS_KC b,SYS_KCXX c';
//Str :=Str +' where a.cj_xscjd__KCH=b.SYS_KC__KCH and a.CJ_XSCJD__XXLB=c.SYS_KCXX__XXLB and a.cj_xscjd__xh='''+xhStr+''' and a.CJ_XSCJD__XNXQ='''+ADOexe.Fields[0].AsString +''' group by a.cj_xscjd__xh,b.sys_kc__kcm,c.sys_kcxx__xxlbm,a.cj_xscjd__xf,a.cj_xscjd__fsfs';
Str :='select a.cj_xscjd__xh,b.sys_kc__kcm,c.sys_kcxx__xxlbm,a.cj_xscjd__fs,a.cj_xscjd__xs,a.cj_xscjd__xf,a.cj_xscjd__fsfs,a.cj_xscjd__ksxz from cj_xscjd a,SYS_KC b,SYS_KCXX c';
Str :=Str +' where a.cj_xscjd__KCH=b.SYS_KC__KCH and a.CJ_XSCJD__XXLB=c.SYS_KCXX__XXLB and a.cj_xscjd__xh='''+xhStr+''' and a.CJ_XSCJD__XNXQ='''+ADOexe.Fields[0].AsString +'''';
QueryData(ADOcj,Str);
ADOcj.First ;
SnProgressBar2.Max :=ADOcj.RecordCount ;
SnProgressBar2.Position :=0;
SnProgressBar2.Min :=0;
if (ADOcj.RecordCount>17) then
showmessage(ADOexe.Fields[0].AsString +'学期的课程超出'+IntToStr(ADOcj.RecordCount-17)+'条');
while not ADOcj.Eof do //学年学期中的成绩
begin
if (SnProgressBar2.Position<SnProgressBar2.Max) then
SnProgressBar2.Position:=SnProgressBar2.Position+1 ;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -