delphi控制excel大全(完全版)

下面是我写的通用文档微机管理系统的打印部分原代码，包括合并execl单元格，加入分页符号，设置行高，列宽，设置execl页头，设置每页记录数，画单元格边框，调用execl模板等等

procedure TfrmMain.Button3Click(Sender:TObject);//动态打印excel报表

var

tiaojian:string;//tiaojian为一个sql查询语句,全宗号,目录号,起始日期为一数据库字段begin

tiaojian:=';

if Edit7.Text<>'then

begin

tiaojian:=tiaojian+'(全宗号='+EDit7.Text+')and';

end;

if Edit8.Text<>'then

begin

tiaojian:=tiaojian+'(目录号='+Edit8.Text+')and';

end;

if Edit9.Text<>'then

begin

tiaojian:=tiaojian+'(起始日期>='+Edit9.Text+')and';

end

else tiaojian:=tiaojian+'(起始日期>=0)and';

if Edit10.Text<>'then

begin

tiaojian:=tiaojian+'(起始日期<='+Edit10.Text+')and';

end;

tiaojian:=copy(tiaojian,1,length(tiaojian)-3);//tiaojian为一个sql查询语句Printanjuan(tiaojian);//调用打印过程

ShellExecute(Handle,'Open',PChar(GetCurpath+'temp.xls'),nil,nil,sw_shownormal);//调用excel查看生成的文件

ProgressBar1.Position:=0;//ProgressBar1为一个进程条控件

end;

procedure TfrmMain.Printanjuan(tiaojiao:string);

var

nowhangi,i,jilushu,LCID:integer;//nowhangi当前execl的所在行，jilushu当前的记录

位置

ssql,PathName:string;

oldcur:tcursor;

ExcelApplication1:TExcelApplication;

ExcelWorkbook1:TExcelWorkbook;

ExcelWorkSheet1:TExcelWorksheet;

xl,Cell1:olevariant;

begin

LCID:=LOCALE_USER_DEFAULT;

if FileExists(GetCurpath+'temp.xls')then//删除动态生成的临时temp.xls文件try

DeleteFile(GetCurpath+'temp.xls');

except

end;

ssql:=';//执行sql查询

ssql:='select*from mainanjuan where('+tiaojiao+')order by系统编号'; doadosql(dm1.queryanjuan,ssql);

begin

try

ExcelApplication1:=TExcelApplication.Create(Self);

ExcelWorkbook1:=TExcelWorkbook.Create(Self);

ExcelWorkSheet1:=TExcelWorksheet.Create(Self);

except

showmessage('对不起，您没有安装Excel2000!');

abort;

end;

end;

ExcelApplication1.Connect;

ExcelApplication1.Workbooks.Add(null,0);

//如果调用一个模板，改动该句如：

//ExcelApplication1.Workbooks.Add('c:\zichang.xls',0);

ExcelWorkBook1.ConnectTo(ExcelApplication1.Workbooks[1]); ExcelWorkSheet1.ConnectTo(ExcelWorkBook1.Sheets[1]as_WorkSheet); //写入execl

ProgressBar1.Position:=5;

if dm1.queryanjuan.RecordCount>0then

begin

//设置列宽，行高

nowhangi:=1;

ExcelApplication1.Range['A1','A1'].ColumnWidth:=3.5;

ExcelApplication1.Range['B1','B1'].ColumnWidth:=3.5;

ExcelApplication1.Range['C1','C1'].ColumnWidth:=3.5;

ExcelApplication1.Range['D1','D1'].ColumnWidth:=8;

ExcelApplication1.Range['E1','E1'].ColumnWidth:=20;

ExcelApplication1.Range['F1','F1'].ColumnWidth:=8.75;

ExcelApplication1.Range['G1','G1'].ColumnWidth:=8.75;

ExcelApplication1.Range['H1','H1'].ColumnWidth:=3.0;

ExcelApplication1.Range['I1','I1'].ColumnWidth:=3.0;

ExcelApplication1.Range['J1','J1'].ColumnWidth:=3.0;

//设置每页的记录数目

for jilushu:=1to(dm1.queryanjuan.RecordCount)do

begin//ProgressBar1为一个进程条控件

ProgressBar1.Position:=(jilushu*100)div(dm1.queryanjuan.RecordCount);

try

if jilushu mod strtoint(edit14.Text)=1then//edit14.Text的值为一个数字，表示每页的记录数目

begin//打印每页的页头

ExcelApplication1.Range['A'+inttostr(nowhangi),'J'+

inttostr(nowhangi)].Merge(xl);//合并execl单元格

ExcelWorkSheet1.cells.Item[nowhangi,1]:=fontselectbox.FontName;

ExcelWorkSheet1.cells.Item[nowhangi,1].font.size:=24;

ExcelWorkSheet1.cells.Item[nowhangi,1].RowHeight:=32;

ExcelWorkSheet1.cells.Item[nowhangi,1]:='案卷目录';

ExcelWorkSheet1.cells.Item[nowhangi,1].HorizontalAlignment:=xlCenter;

ExcelWorkSheet1.cells.Item[nowhangi+1,1]:=

fontselectbox.FontName;

ExcelWorkSheet1.cells.Item[nowhangi+1,1].font.size:=12;

ExcelWorkSheet1.cells.Item[nowhangi+1,1].Font.Bold:=True;

ExcelWorkSheet1.cells.Item[nowhangi+1,1].Orientation:=xlVertical;

ExcelWorkSheet1.cells.Item[nowhangi+1,1].VerticalAlignment:=xlTop;