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]https://www.360docs.net/doc/9811629724.html,:=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]https://www.360docs.net/doc/9811629724.html,:=

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;

ExcelWorkSheet1.cells.Item[nowhangi+1,1]:='顺序号';

ExcelWorkSheet1.cells.Item[nowhangi+1,2]https://www.360docs.net/doc/9811629724.html,:=

fontselectbox.FontName;

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

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

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

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

ExcelWorkSheet1.cells.Item[nowhangi+1,2]:='全宗号';

ExcelWorkSheet1.cells.Item[nowhangi+1,3]https://www.360docs.net/doc/9811629724.html,:=

fontselectbox.FontName;

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

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

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

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

ExcelWorkSheet1.cells.Item[nowhangi+1,3]:='目录号';

//加入分页符号

if(jilushu div strtoint(edit14.Text)>0)then

begin

Cell1:=ExcelWorksheet1.Cells.Item[nowhangi,11];

ExcelWorksheet1.VPageBreaks.Add(Cell1);

ExcelWorksheet1.HPageBreaks.Add(Cell1);

end;

nowhangi:=nowhangi+2;

end;

//打印查询数据

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

ExcelWorkSheet1.cells.Item[nowhangi,1]https://www.360docs.net/doc/9811629724.html,:=fontselectbox.FontName;

ExcelWorkSheet1.cells.Item[nowhangi,1].font.size:=strtoint(Edit11.text);

ExcelWorkSheet1.cells.Item[nowhangi,1].WrapText:=True;

ExcelWorkSheet1.cells.Item[nowhangi,1]:=inttostr(jilushu);

ExcelWorkSheet1.cells.Item[nowhangi,2]https://www.360docs.net/doc/9811629724.html,:=fontselectbox.FontName;

ExcelWorkSheet1.cells.Item[nowhangi,2].WrapText:=True;

ExcelWorkSheet1.cells.Item[nowhangi,2].font.size:=strtoint(Edit11.text);

ExcelWorkSheet1.cells.Item[nowhangi,2]:=dm1.queryanjuan.fieldbyname('全宗号').asstring;

ExcelWorkSheet1.cells.Item[nowhangi,3]https://www.360docs.net/doc/9811629724.html,:=fontselectbox.FontName;

ExcelWorkSheet1.cells.Item[nowhangi,3].WrapText:=True;

ExcelWorkSheet1.cells.Item[nowhangi,3].font.size:=strtoint(Edit11.text);

ExcelWorkSheet1.cells.Item[nowhangi,3]:=dm1.queryanjuan.fieldbyname('目录号').asstring;

except

end;

dm1.queryanjuan.Next;

nowhangi:=nowhangi+1;

end;

end;

try//插入页码

ExcelWorkSheet1.PageSetup.RightHeader:='&9第&P页共&N页';

except

showmessage('您可能没有安装打印机');

end;

for i:=1to nowhangi-1do//画单元格边框

if taoda.Checked then

begin

with ExcelApplication1.Range['A'+inttostr(i),'J'+inttostr(i)].Borders do

begin

LineStyle:=xlContinuous;

Weight:=xlThin;

ColorIndex:=xlAutomatic;

end;

end;

ExcelWorkBook1.SaveCopyAs(GetCurpath+'temp.xls');

ExcelApplication1.DisplayAlerts[LCID]:=False;

ExcelApplication1.Quit;

ExcelWorksheet1.Disconnect;

ExcelWorkbook1.Disconnect;

ExcelApplication1.Disconnect;

end;

procedure TfrmMain.DoAdoSql(CurADODS:TADODataSet;SSql:string); var

oldcur:tcursor;

begin

sb1.Panels[0].Text:='正在查询数据……';//sb1:Tstatusbar控件

sb1.Refresh;

oldcur:=Screen.Cursor;

screen.cursor:=crHourGlass;

try

if CurADODS.Active=true then CurADODS.Close;

https://www.360docs.net/doc/9811629724.html,mandText:=SSql;

CurADODS.Open;

finally

screen.Cursor:=oldcur;

end;

end;