delphi控制excel大全(完全版)

下面是我写的通用文档微机管理系统的打印部分原代码，包括合并execl单元格，加入分页符号，设置行高，列宽，设置execl页头，设置每页记录数，画单元格边框，调用execl模板等等procedure TfrmMain.Button3Click(Sender:TObject);//动态打印excel报表vartiaojian:string;//tiaojian为一个sql查询语句,全宗号,目录号,起始日期为一数据库字段begintiaojian:=';if Edit7.Text<>'thenbegintiaojian:=tiaojian+'(全宗号='+EDit7.Text+')and';end;if Edit8.Text<>'thenbegintiaojian:=tiaojian+'(目录号='+Edit8.Text+')and';end;if Edit9.Text<>'thenbegintiaojian:=tiaojian+'(起始日期>='+Edit9.Text+')and';endelse tiaojian:=tiaojian+'(起始日期>=0)and';if Edit10.Text<>'thenbegintiaojian:=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);varnowhangi,i,jilushu,LCID:integer;//nowhangi当前execl的所在行，jilushu当前的记录位置ssql,PathName:string;oldcur:tcursor;ExcelApplication1:TExcelApplication;ExcelWorkbook1:TExcelWorkbook;ExcelWorkSheet1:TExcelWorksheet;xl,Cell1:olevariant;beginLCID:=LOCALE_USER_DEFAULT;if FileExists(GetCurpath+'temp.xls')then//删除动态生成的临时temp.xls文件tryDeleteFile(GetCurpath+'temp.xls');exceptend;ssql:=';//执行sql查询ssql:='select*from mainanjuan where('+tiaojiao+')order by系统编号'; doadosql(dm1.queryanjuan,ssql);begintryExcelApplication1:=TExcelApplication.Create(Self);ExcelWorkbook1:=TExcelWorkbook.Create(Self);ExcelWorkSheet1:=TExcelWorksheet.Create(Self);exceptshowmessage('对不起，您没有安装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); //写入execlProgressBar1.Position:=5;if dm1.queryanjuan.RecordCount>0thenbegin//设置列宽，行高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)dobegin//ProgressBar1为一个进程条控件ProgressBar1.Position:=(jilushu*100)div(dm1.queryanjuan.RecordCount);tryif 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;ExcelWorkSheet1.cells.Item[nowhangi+1,1]:='顺序号';ExcelWorkSheet1.cells.Item[nowhangi+1,2]:=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]:=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)thenbeginCell1:=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]:=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]:=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]:=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;exceptend;dm1.queryanjuan.Next;nowhangi:=nowhangi+1;end;end;try//插入页码ExcelWorkSheet1.PageSetup.RightHeader:='&9第&P页共&N页';exceptshowmessage('您可能没有安装打印机');end;for i:=1to nowhangi-1do//画单元格边框if taoda.Checked thenbeginwith ExcelApplication1.Range['A'+inttostr(i),'J'+inttostr(i)].Borders dobeginLineStyle:=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); varoldcur:tcursor;beginsb1.Panels[0].Text:='正在查询数据……';//sb1:Tstatusbar控件sb1.Refresh;oldcur:=Screen.Cursor;screen.cursor:=crHourGlass;tryif CurADODS.Active=true then CurADODS.Close;mandText:=SSql;CurADODS.Open;finallyscreen.Cursor:=oldcur;end;end;。