delphi导出数据至Excel的几种方法及比较

合集下载

delphidbgrid导出Excel表

delphi dbgrid 导出Excel表///////// 利用剪贴板，速度很快！适合装有Excel的机器///////////////////// USES Clipbrd,ComObj;procedure TForm1.Button1Click(Sender: TObject);varstr:string;i:Integer;excelapp,sheet:Variant;begin// lbl2.Caption:=DateTimeToStr(Now);str:='';dbgrd1.DataSource.DataSet.DisableControls;for i:=0 to dbgrd1.DataSource.DataSet.FieldCount-1 dostr:=str+dbgrd1.DataSource.DataSet.fields[i].DisplayLabel+char(9);str:=str+#13;dbgrd1.DataSource.DataSet.First;while not(dbgrd1.DataSource.DataSet.eof) do beginfor i:=0 to dbgrd1.DataSource.DataSet.FieldCount-1 dostr:=str+dbgrd1.DataSource.DataSet.Fields[i].AsString+char(9);str:=str+#13;dbgrd1.DataSource.DataSet.next;lbl1.Caption:=IntToStr(dbgrd1.DataSource.DataSet.RecNo);Application.ProcessMessages;end;//end whiledbgrd1.DataSource.DataSet.EnableControls;clipboard.Clear;Clipboard.Open;Clipboard.AsText:=str;Clipboard.Close;excelapp:=createoleobject('excel.application');excelapp.workbooks.add(1); // excelapp.workbooks.add(-4167);sheet:=excelapp.workbooks[1].worksheets[1];:='sheet1';sheet.paste;Clipboard.Clear;// :='宋体';// sheet.columns.font.size:=9;// sheet.Columns.AutoFit;excelapp.visible:=true;// lbl3.Caption:=DateTimeToStr(Now);end;/////////////////////////////////////////////////////////利用TStringList，速度很快！适合没有装Excel的机器////////////////////////procedure TForm1.Button1Click(Sender: TObject);vars:TStringList;str:string;i:Integer;begin// lbl1.Caption:=DateTimeToStr(Now);str:='';dbgrd1.DataSource.DataSet.DisableControls;for i:=0 to dbgrd1.DataSource.DataSet.FieldCount-1 dostr:=str+dbgrd1.DataSource.DataSet.fields[i].DisplayLabel+char(9);str:=str+#13;dbgrd1.DataSource.DataSet.First;while not(dbgrd1.DataSource.DataSet.eof) do beginfor i:=0 to dbgrd1.DataSource.DataSet.FieldCount-1 dostr:=str+dbgrd1.DataSource.DataSet.Fields[i].AsString+char(9);str:=str+#13;dbgrd1.DataSource.DataSet.next;// lbl3.Caption:=IntToStr(dbgrd1.DataSource.DataSet.RecNo);// Application.ProcessMessages;end;//end whiledbgrd1.DataSource.DataSet.EnableControls;s:=TStringList.Create;s.Add(str);s.SaveToFile('c:\temp.xls');//保存到c:\temp.xlss.Free;// lbl2.Caption:=DateTimeToStr(Now);end;////////////////////////////////////////////////***********************************************************(Delphi)Excel的快速导入***********************************************************(Delphi)Excel的快速导入//怎样可以提高EXCEL的导出速度？uses ADODB,excel97,adoint;function TForm1.ExportToExcel: Boolean;varxlApp,xlBook,xlSheet,xlQuery: Variant;adoConnection,adoRecordset: Variant;beginadoConnection := CreateOleObject('ADODB.Connection');adoRecordset := CreateOleObject('ADODB.Recordset');adoConnection.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Tree.mdb;Persist Security Info=False');adoRecordset.CursorLocation := adUseClient;adoRecordset.Open('SELECT * FROM tree',adoConnection,1,3);tryxlApp := CreateOleObject('Excel.Application');xlBook := xlApp.Workbooks.Add;xlSheet := xlBook.Worksheets['sheet1'];//设置这一列为文本列，让"00123" 正确显示，而不是自动转换为"123"xlSheet.Columns['C:C'].NumberFormatLocal := '@';xlApp.Visible := True;//把查询结果导入EXCEL数据xlQuery := xlSheet.QueryTables.Add(adoRecordset,xlSheet.Range['A1']); //关键是这一句xlQuery.FieldNames := True;xlQuery.RowNumbers := False;xlQuery.FillAdjacentFormulas := False;xlQuery.PreserveFormatting := True;xlQuery.RefreshOnFileOpen := False;xlQuery.BackgroundQuery := True;//xlQuery.RefreshStyle := xlInsertDeleteCells;xlQuery.SavePassword := True;xlQuery.SaveData := True;xlQuery.AdjustColumnWidth := True;xlQuery.RefreshPeriod := 0;xlQuery.PreserveColumnInfo := True;xlQuery.FieldNames := True;xlQuery.Refresh;xlBook.SaveAs('d:\fromD.xls',xlNormal,'','',False,False);finallyif not VarIsEmpty(XLApp) then beginXLApp.displayAlerts:=false;XLApp.ScreenUpdating:=true;XLApp.quit;end;end;end;///////////////////////////////////////////////////procedure saveToExcel();varEclapp,workbook:variant;i,n:integer;beginif not adoquery1.Active then exit;if adoquery1.RecordCount<=0 then exit;if application.MessageBox('确认导出excel表吗？','提示',mb_okcancel+mb_iconinformation)=idcancel then exit;Eclapp := createoleobject('Excel.Application');Eclapp.workbooks.add;for i:=0 to dbgrid2.FieldCount-1 dobeginEclapp.cells[1,i+1]:=dbgrid2.Columns[i].Title.Caption;end;Eclapp.cells[1,5]:='签字';adoquery1.First;n:=2;while not adoquery1.Eof doeclapp.cells[n,1] := adoquery1.Fields[0].AsString;eclapp.cells[n,2] := adoquery1.Fields[1].AsString;eclapp.cells[n,3] := adoquery1.Fields[2].AsString;eclapp.cells[n,4] := adoquery1.Fields[4].AsString;eclapp.cells[n,6] :=' ';inc(n);adoquery1.Next;end;eclapp.cells[n,1] := '满足条件记录的总数为：'+inttostr(adoquery1.RecordCount)+'条';application.MessageBox('数据导出完成!','提示',mb_ok+mb_iconinformation);eclapp.visible := true;end;方法二procedure CopyDbDataToExcel(Args: array of const);variCount, jCount: Integer;XLApp: Variant;Sheet,range: Variant;I: Integer;beginScreen.Cursor := crHourGlass;if not VarIsEmpty(XLApp) thenbeginXLApp.DisplayAlerts := False;XLApp.Quit;VarClear(XLApp);end;tryXLApp:=CreateOleObject(Excel.Application);exceptScreen.Cursor := crDefault;Exit;end;XLApp.WorkBooks.Add;XLApp.SheetsInNewWorkbook := High(Args) + 1;for I := Low(Args) to High(Args) doXLApp.WorkBooks[1].WorkSheets[I+1].Name := TDBGrid(Args[I].VObject).Name;Sheet := XLApp.Workbooks[1].WorkSheets[TDBGrid(Args[I].VObject).Name];if not TDBGrid(Args[I].VObject).DataSource.DataSet.Active thenbeginScreen.Cursor := crDefault;Exit;end;TDBGrid(Args[I].VObject).DataSource.DataSet.first;for iCount := 0 to TDBGrid(Args[I].VObject).Columns.Count - 1 dorange:=sheet.range[sheet.cells[1,1],sheet.cells[1,iCount + 1]];range.select;range.merge;sheet.cells[1,1]:=[+fqueryhuman.dbedit2.text+]+个人报销记录（普通报销、特殊报销）查询;jCount :=2;for iCount := 0 to TDBGrid(Args[I].VObject).Columns.Count - 1 doSheet.Cells[2, iCount + 1]:=TDBGrid(Args[I].VObject).Columns.Items[iCount].Title.Caption; while not TDBGrid(Args[I].VObject).DataSource.DataSet.Eof dobeginfor iCount := 0 to TDBGrid(Args[I].VObject).Columns.Count - 1 doSheet.Cells[jCount + 1, iCount + 1] :=TDBGrid(Args[I].VObject).Columns.Items[iCount].Field.AsString;Inc(jCount);TDBGrid(Args[I].VObject).DataSource.DataSet.Next;end;XlApp.Visible := True;end;Screen.Cursor := crDefault;end;方法三delphi导入/导出excel2008年03月02日星期日16:39从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:/*===================================================================*/--如果接受数据导入的表已经存在insert into 表select * fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)--如果导入数据并生成表select * into 表fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)/*===================================================================*/--如果从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)select * from 表--如果Excel文件不存在,也可以用BCP来导成类Excel的文件,注意大小写:--导出表的情况EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名out "c:\test.xls" /c -/S"服务器名" /U"用户名" -P"密码"'--导出查询的情况EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout "c:\test.xls" /c -/S"服务器名" /U"用户名" -P"密码"'/*--说明:c:\test.xls 为导入/导出的Excel文件名.sheet1$ 为Excel文件的工作表名,一般要加上$才能正常使用.--*/--下面是导出真正Excel文件的方法:if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[p_exporttb]GO/*--数据导出EXCEL导出表中的数据到Excel,包含字段名,文件为真正的Excel文件,如果文件不存在,将自动创建文件,如果表不存在,将自动创建表基于通用性考虑,仅支持导出标准数据类型--邹建2003.10(引用请保留此信息)--*//*--调用示例p_exporttb @tbname='地区资料',@path='c:\',@fname='aa.xls'--*/create proc p_exporttb@tbname sysname, --要导出的表名@path nvarchar(1000), --文件存放目录@fname nvarchar(250)='' --文件名,默认为表名asdeclare @err int,@src nvarchar(255),@desc nvarchar(255),@out intdeclare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)--参数检测if isnull(@fname,'')='' set @fname=@tbname+'.xls'--检查文件是否已经存在if right(@path,1)<>'\' set @path=@path+'\'create table #tb(a bit,b bit,c bit)set @sql=@path+@fnameinsert into #tb exec master..xp_fileexist @sql--数据库创建语句set @sql=@path+@fnameif exists(select 1 from #tb where a=1)set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE' +';CREATE_DB="'+@sql+'";DBQ='+@sqlelseset @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES' +';DATABASE='+@sql+'"'--连接数据库exec @err=sp_oacreate 'adodb.connection',@obj outif @err<>0 goto lberrexec @err=sp_oamethod @obj,'open',null,@constrif @err<>0 goto lberr/*--如果覆盖已经存在的表,就加上下面的语句--创建之前先删除表/如果存在的话select @sql='drop table ['+@tbname+']'exec @err=sp_oamethod @obj,'execute',@out out,@sql--*/--创建表的SQLselect @sql='',@fdlist=''select @fdlist=@fdlist+',['++']',@sql=@sql+',['++'] '+casewhen like '%char'then case when a.length>255 then 'memo'else 'text('+cast(a.length as varchar)+')' endwhen like '%int' or ='bit' then 'int'when like '%datetime' then 'datetime'when like '%money' then 'money'when like '%text' then 'memo'else endFROM syscolumns a left join systypes b on a.xtype=b.xusertypewhere not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp') and object_id(@tbname)=idselect @sql='create table ['+@tbname+']('+substring(@sql,2,8000)+')',@fdlist=substring(@fdlist,2,8000)exec @err=sp_oamethod @obj,'execute',@out out,@sqlif @err<>0 goto lberrexec @err=sp_oadestroy @obj--导入数据set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;IMEX=1 ;DATABASE='+@path+@fname+''',['+@tbname+'$])'exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)returnlberr:exec sp_oageterrorinfo 0,@src out,@desc outlbexit:select cast(@err as varbinary(4)) as 错误号,@src as 错误源,@desc as 错误描述select @sql,@constr,@fdlistgoif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[p_exporttb]GO/*--数据导出EXCEL导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件如果文件不存在,将自动创建文件如果表不存在,将自动创建表基于通用性考虑,仅支持导出标准数据类型--邹建2003.10(引用请保留此信息)--*//*--调用示例p_exporttb @sqlstr='select * from 地区资料',@path='c:\',@fname='aa.xls',@sheetname='地区资料'--*/create proc p_exporttb@sqlstr varchar(8000), --查询语句,如果查询语句中使用了order by ,请加上top 100 percent@path nvarchar(1000), --文件存放目录@fname nvarchar(250), --文件名@sheetname varchar(250)='' --要创建的工作表名,默认为文件名asdeclare @err int,@src nvarchar(255),@desc nvarchar(255),@out intdeclare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)--参数检测if isnull(@fname,'')='' set @fname='temp.xls'if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')--检查文件是否已经存在if right(@path,1)<>'\' set @path=@path+'\'create table #tb(a bit,b bit,c bit)set @sql=@path+@fnameinsert into #tb exec master..xp_fileexist @sql--数据库创建语句set @sql=@path+@fnameif exists(select 1 from #tb where a=1)set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'+';CREATE_DB="'+@sql+'";DBQ='+@sqlelseset @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES' +';DATABASE='+@sql+'"'--连接数据库exec @err=sp_oacreate 'adodb.connection',@obj outif @err<>0 goto lberrexec @err=sp_oamethod @obj,'open',null,@constrif @err<>0 goto lberr--创建表的SQLdeclare @tbname sysnameset @tbname='##tmp_'+convert(varchar(38),newid())set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'exec(@sql)select @sql='',@fdlist=''select @fdlist=@fdlist+',['++']',@sql=@sql+',['++'] '+casewhen like '%char'then case when a.length>255 then 'memo'else 'text('+cast(a.length as varchar)+')' endwhen like '%int' or ='bit' then 'int'when like '%datetime' then 'datetime'when like '%money' then 'money'when like '%text' then 'memo'else endFROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertypewhere not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp') and a.id=(select id from tempdb..sysobjects where name=@tbname)if @@rowcount=0 returnselect @sql='create table ['+@sheetname+']('+substring(@sql,2,8000)+')',@fdlist=substring(@fdlist,2,8000)exec @err=sp_oamethod @obj,'execute',@out out,@sqlif @err<>0 goto lberrexec @err=sp_oadestroy @obj--导入数据set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES ;DATABASE='+@path+@fname+''',['+@sheetname+'$])'exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')set @sql='drop table ['+@tbname+']'exec(@sql)returnlberr:exec sp_oageterrorinfo 0,@src out,@desc outlbexit:select cast(@err as varbinary(4)) as 错误号,@src as 错误源,@desc as 错误描述select @sql,@constr,@fdlistgo。

delphi数据导入excel

procedure Tdaochu.BitBtn1Click(Sender: TObject);vari,row,column,icount:integer;ExcelApplication1: TExcelApplication;ExcelWorkbook1: TExcelWorkbook;ExcelWorksheet1: TExcelWorksheet;begindm1.ADOQuery5.close;dm1.ADOQuery5.SQL.Clear ;dm1.ADOQuery5.SQL.text:='select * from 记录where 日期between #'+formatdatetime('yyyy-mm-dd',datetimepicker1.date)+'# and#'+formatdatetime('yyyy-mm-dd',datetimepicker2.date)+'# ';dm1.ADOQuery5.Open;//导出TryExcelApplication1:=TExcelApplication.Create(Application);ExcelWorksheet1:=TExcelWorksheet.Create(Application);ExcelWorkbook1:=TExcelWorkbook.Create(Application);ExcelApplication1.Connect;ExceptMessageDlg('Excel没有安装!',mtError, [mbOk], 0);Abort;End;//ExcelApplication1.Visible[0] := false; //不显示过程ExcelApplication1.Visible[0]:=true; //显示过程ExcelApplication1.Caption:='Excel Application';ExcelApplication1.Workbooks.Add(Null,0);ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);DBGrid1.DataSource.DataSet.Open;row := 2;DBGrid1.DataSource.DataSet.first;for iCount:= 0 to DBGrid1.Columns.Count-1 dobeginExcelWorksheet1.cells.Item[1,iCount+1]:=DBGrid1.Columns.Items[iCount].Title.Caption;end;While Not (DBGrid1.DataSource.DataSet.Eof) dobegincolumn:=1;for i :=1 to DBGrid1.DataSource.DataSet.FieldCount dobeginExcelWorksheet1.Cells.Item[row,column]:=DBGrid1.DataSource.DataSet.field s[i-1].AsString;column:=column+1;end;DBGrid1.DataSource.DataSet.Next;row:=row+1;end;ExcelApplication1.Visible[0]:=True;ExcelApplication1.Disconnect;//ExcelApplication1.Quit;ExcelApplication1.Free;ExcelWorksheet1.Free;ExcelWorkbook1.Free;messagebox(getactivewindow(),'导出结算数据到EXCEL成功！','提示',MB_OK+MB_ICONINFORMATION);end ;关于读取Excel文件数据到数据库,以及从数据库导出数据到Excel的例子数据库:sqlserver表名称:tperson 字段:code varchar(10) name varchar(20)创建一个结构体:typePPerson = ^TPerson;TPerson = recordFCode: String;FName: string;end;相关函数及变量FPerson: PPerson;FPersonList: TList; //用来存数据function LoadExcel: Boolean; //加载Excelfunction SaveToDB: Boolean; //写到数据库function DBToExcel: Boolean; //导出数据两个按钮,一个adoconnection,一个adoquery看到好多人问关于Excel方面的问题,所以写了这个比较简单的例子,有好多细节也没考虑,供参考在真正写的时候,要注意指针的释放等.全部源码:unit Unit1;interfaceusesWindows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,Dialogs, StdCtrls, ComOBJ, DB, ADODB;typePPerson = ^TPerson;TPerson = recordFCode: String;FName: string;end;typeTForm1 = class(TForm)Button1: TButton;ADOConnection1: TADOConnection;ADOQuery1: TADOQuery;Button2: TButton;procedure FormCreate(Sender: TObject);procedure FormDestroy(Sender: TObject);procedure Button1Click(Sender: TObject);procedure Button2Click(Sender: TObject);private{ Private declarations }FPerson: PPerson;FPersonList: TList;function LoadExcel: Boolean;function SaveToDB: Boolean;function DBToExcel: Boolean;public{ Public declarations }end;varForm1: TForm1;implementation{$R *.dfm}Function TForm1.LoadExcel: Boolean;VarRange Matrix: Variant;OpenDialog1: Tope Dialog;iRE: integer;IN_TYPE, vStockName: String;SStockName, sCompany: String;i: Integer;MsExcel, MsExcelWorkBook, MsExcelWorkSheet: Variant;Col1, Col2: String;BeginOpenDialog1:=TOpenDialog.Create (nil);TryOpenDialog1.FileName:='*.CSV;*.xls';If not OpenDialog1.Execute thenBeginResult: = false;Exit;End;MsExcel: = CreateOleObject ('Excel. Application');MsExcelWorkBook: = msExcel.Workbooks.Open (OpenDialog1.FileName);MsExcelWorkSheet: = msExcel.Worksheets.Item [1];ExceptResult: = false;Exit;End;//开始从EXCEL文件读取相关的信息TryTryApplication.ProcessMessages;// 防止进程阻塞，使程序能够响应消息队列中的其他事件。

DxDbGrid与DbGridEh表格使用及导出Excel

DxDbGrid与DbGridEh表格使用及导出Excel分类：Delphi 2011-05-16 09:47 110人阅读评论(0) 收藏举报前言：二者都是非常不错的第三方表格控件，都可实现多表头的表格及分组汇总功能；在导出 Excel 方面，个人觉得 DxDbGrid 做的比DbGridEh 出色，几乎是 Grid 原样导出， DbGridEh 导出表格模式的单元格与 Grid 显示有所出入。

一、所用版本及安装：1 、版本： DevExQuantumGrid v3.22 Pro for D7 、 EhLib 5.2.842 、安装： DevExQuantumGrid 直接 Setup.exe 就 OK ； EhLib 在Delphi7 的安装稍微啰嗦点，具体步骤参考 readme.txt 或如下步骤：(1). 将 EhLib 5.2.84 解压缩到目标目录。

(2). 打开 Delphi 7 ，将 EhLib 的 /Delphi7 子目录加到 Delphi 的 Library path 。

( 菜单操作路径为：Tools|Environment Options...|Library|Library path)(3). 将 EhLib 目标安装目录中的 common 和 DataService子目录的文件移动到 EhLib 的 /Delphi7 子目录中。

(4). 在 Delphi 7 中打开 EhLib70.dpk ，编译，但不要安装。

(5). 在 Delphi 7 中打开 DclEhLib70.dpk ，编译并安装。

(6). 组件面板中出现一个 EhLib 的组件页。

(7). 打开附带的 DEMOS ，编译并运行，测试安装成功。

二、使用 DxDbGrid1 、窗体拖入 dxDBGrid1 、 ADOConnection1 、 ADOQuery1 、DataSource1 、 Button1 、 SaveDialog1 ，然后完成数据的链接及相关控件关联，如何操作，你应该懂的；接下来完成如下图所示的一个表格：2 、双击 dxDBGrid1 ，在 Bands 栏增加 TdxTreeListBand 并填写Caption ，注意要将 dxDBGrid1 的 ShowBands 属性设置为 True 才能显示 Bands 栏；同样双击 dxDBGrid1 ，在 Columns 栏，添加多个dxGridColumn （根据需要选择不同的类型）并使其与数据库字段形成关联，涉及如下几个属性， BandIndex 选择对应的 Band 从而形成二级表头， Caption 、 FieldName 、 HeaderAlignment 、 width 等，如此完成了基本的表格设计，如果喜欢表头平滑更改 LookAndFeel 属性为 lfFlat 即可。

delphi导出数据至Excel的几种方法及比较

delph‎i导出数据‎至E xce‎l的几种方‎法及比较一、delph‎i快速导出e‎x celuses ComOb‎j,clipb‎r d;funct‎i on ToExc‎e l(sfile‎n ame:strin‎g; ADOQu‎e ry:TADOQ‎u ery):boole‎a n;const‎xlNor‎m al=-4143;vary : integ‎e r;tsLis‎t : TStri‎n gLis‎t;s,filen‎a me :strin‎g;aShee‎t :Varia‎n t;excel‎:OleVa‎r iant‎;saved‎i alog‎ :tsave‎d ialo‎g;begin‎Resul‎t := true;trye xcel‎:=Creat‎e OleO‎b ject‎('Excel‎.Appli‎c atio‎n');e xcel‎.workb‎o oks.add;excep‎t//scree‎n.curso‎r:=crDef‎a ult;showm‎e ssag‎e('无法调用E‎x cel！');exit;end;saved‎i alog‎:=tsave‎d ialo‎g.Creat‎e(nil);saved‎i alog‎.FileN‎ame:=sfile‎n ame; //存入文件saved‎i alog‎.Filte‎r:='Excel‎文件(*.xls)|*.xls';if saved‎i alog‎.Execu‎t e thenbegin‎i f FileE‎x ists‎(saved‎i alog‎.FileN‎a me) thentryif appli‎catio‎n.messa‎g ebox‎('该文件已经‎存在，要覆盖吗？','询问',mb_ye‎sno+mb_ic‎onque‎stion‎)=idyes‎thenDelet‎e File‎(PChar‎(saved‎i alog‎.FileN‎a me))elsebegin‎Excel‎.Quit;saved‎i alog‎.free;//scree‎n.curso‎r:=crDef‎a ult;Exit;end;excep‎tExcel‎.Quit;scree‎n.curso‎r:=crDef‎a ult;Exit;end;f ilen‎a me:=saved‎i alog‎.FileN‎a me;end;saved‎i alog‎.free;if filen‎a me='' thenbegin‎resul‎t:=true;Excel‎.Quit;//scree‎n.curso‎r:=crDef‎a ult;exit;end;aShee‎t:=excel‎.Works‎h eets‎.Item[1];tsLis‎t:=TStri‎n gLis‎t.Creat‎e;//tsLis‎t.Add('查询结果'); //加入标题s:=''; //加入字段名‎for y := 0 to adoqu‎e ry.field‎C ount‎- 1 dobegin‎s:=s+adoQu‎e ry.Field‎s.Field‎s[y].Field‎N ame+#9 ; Appli‎c atio‎n.Proce‎s sMes‎s ages‎;end;tsLis‎t.Add(s);trytryA DOQu‎e ry.First‎;While‎Not ADOQu‎e ry.Eof dob egin‎s:='';for y:=0 to ADOQu‎e ry.Field‎C ount‎-1 dobegin‎s:=s+ADOQu‎e ry.Field‎s[y].AsStr‎i ng+#9;Appli‎c atio‎n.Proce‎s sMes‎s ages‎;end;tsLis‎t.Add(s);ADOQu‎e ry.next;end;C lipb‎o ard.AsTex‎t:=tsLis‎t.Text;e xcep‎tr esul‎t:=false‎;end;final‎l yt sLis‎t.Free;aShee‎t.Paste‎;Messa‎g eBox‎(A ppli‎ca tio‎n.Handl‎e,'数据导出完‎毕！','系统提示',MB_IC‎O NINF‎O RMA T‎ION or MB_OK‎);tryi f copy(FileN‎a me,lengt‎h(FileN‎a me)-3,4)<>'.xls' thenFileN‎a me:=FileN‎a me+'.xls';E xcel‎.Activ‎e Work‎b ook.SaveA‎s(FileN‎a me, xlNor‎m al, '', '', False‎, False‎);excep‎tExcel‎.Quit;s cree‎n.curso‎r:=crDef‎a ult;exit;end;Excel‎.V isib‎l e := false‎; //true会‎自动打开已‎经保存的e‎x celExcel‎.Quit;Excel‎:= UnAss‎i gned‎;end;调用:ToExc‎el('D:\a.xsl',Query‎T oExc‎el);//路径可以自‎定义-------------------------------------------------------------------------------------------------******************************************************************************************* ******二、delph‎i如何导出‎E XCEL‎，代码。

delphi导出数据至Excel的七种方法及比较以及一些EXCEL单元格的操作

Application.ProcessMessages;
end;
tsList.Add(s);
try
try
ADOQuery.First;
While Not ADOQuery.Eof do
s,filename :string;
aSheet :Variant;
excel :OleVariant;
savedialog :tsavedialog;
begin
Result := true;
try
excel:=CreateOleObject('Excel.Application');
var h,k:intering;
begin
try
Excelid := CreateOLEObject('Excel.Application');
except
MessageBox(Application.Handle,'数据导出完毕！','系统提示',MB_ICONINFORMATION or MB_OK);
try
if copy(FileName,length(FileName)-3,4)<>'.xls' then
aSheet.range['A1:H1'].Font.size := 20; //字体
设置A1到H1的单元格的字体大小为20，可以Column[n]设置某列的字体。
aSheet.range['A1:H1'].Font.bold := true;
设置A1到H1的单元格的字体加粗
一;

将DELPHI中把数据库中数据导出到EXCEL中

将DELPHI中把数据库中数据导出到EXCEL中使用Delphi 控件方法在Form中分别放入ExcelApplication, ExcelWorkbook和ExcelWorksheet。

1）打开ExcelExcelApplication1.Connect;2) 显示当前窗口：ExcelApplication1.Visible[0]:=True;3) 更改Excel 标题栏：ExcelApplication1.Caption := '应用程序调用Microsoft Excel';4) 添加新工作簿：ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Add(EmptyParam,0));5) 添加新工作表：var Temp_Worksheet: _WorkSheet;beginTemp_Worksheet:=ExcelWorkbook1.WorkSheets.Add(EmptyParam,EmptyParam,EmptyParam,EmptyParam,0) as _WorkSheet;ExcelWorkSheet1.ConnectTo(Temp_WorkSheet);End;6) 打开已存在的工作簿：ExcelApplication1.Workbooks.Open (c:\a.xlsEmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0)7) 设置第2个工作表为活动工作表：ExcelApplication1.WorkSheets[2].Activate; 或ExcelApplication1.WorksSheets[ 'Sheet2' ].Activate;8) 给单元格赋值：ExcelApplication1.Cells[1,4].V alue := '第一行第四列';9) 设置指定列的宽度（单位：字符个数），以第一列为例：ExcelApplication1.ActiveSheet.Columns[1].ColumnsWidth := 5;10) 设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：ExcelApplication1.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米11) 在第8行之前插入分页符：ExcelApplication1.WorkSheets[1].Rows[8].PageBreak := 1;12) 在第8列之前删除分页符：ExcelApplication1.ActiveSheet.Columns[4].PageBreak := 0;13) 指定边框线宽度：ExcelApplication1.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;1-左2-右3-顶4-底5-斜( \ ) 6-斜( / )14) 清除第一行第四列单元格公式：ExcelApplication1.ActiveSheet.Cells[1,4].ClearContents;15) 设置第一行字体属性：ExcelApplication1.ActiveSheet.Rows[1] := '隶书';ExcelApplication1.ActiveSheet.Rows[1].Font.Color := clBlue;ExcelApplication1.ActiveSheet.Rows[1].Font.Bold := True;ExcelApplication1.ActiveSheet.Rows[1].Font.UnderLine := True;16) 进行页面设置：a.页眉：ExcelApplication1.ActiveSheet.PageSetup.CenterHeader := '报表演示';b.页脚：ExcelApplication1.ActiveSheet.PageSetup.CenterFooter := '第&P页';c.页眉到顶端边距2cm：ExcelApplication1.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;d.页脚到底端边距3cm：ExcelApplication1.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;e.顶边距2cm：ExcelApplication1.ActiveSheet.PageSetup.TopMargin := 2/0.035;f.底边距2cm：ExcelApplication1.ActiveSheet.PageSetup.BottomMargin := 2/0.035;g.左边距2cm：ExcelApplication1.ActiveSheet.PageSetup.LeftMargin := 2/0.035;h.右边距2cm：ExcelApplication1.ActiveSheet.PageSetup.RightMargin := 2/0.035;i.页面水平居中：ExcelApplication1.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;j.页面垂直居中：ExcelApplication1.ActiveSheet.PageSetup.CenterV ertically := 2/0.035;k.打印单元格网线：ExcelApplication1.ActiveSheet.PageSetup.PrintGridLines := True;17) 拷贝操作：a.拷贝整个工作表：ed.Range.Copy;b.拷贝指定区域：ExcelApplication1.ActiveSheet.Range[ 'A1:E2' ].Copy;c.从A1位置开始粘贴：ExcelApplication1.ActiveSheet.Range.[ 'A1' ].PasteSpecial;d.从文件尾部开始粘贴：ExcelApplication1.ActiveSheet.Range.PasteSpecial;18) 插入一行或一列：a. ExcelApplication1.ActiveSheet.Rows[2].Insert;b. ExcelApplication1.ActiveSheet.Columns[1].Insert;19) 删除一行或一列：a. ExcelApplication1.ActiveSheet.Rows[2].Delete;b. ExcelApplication1.ActiveSheet.Columns[1].Delete;20) 打印预览工作表：ExcelApplication1.ActiveSheet.PrintPreview;21) 打印输出工作表：ExcelApplication1.ActiveSheet.PrintOut;22) 工作表保存：if not ExcelApplication1.ActiveWorkBook.Saved thenExcelApplication1.ActiveSheet.PrintPreview;23) 工作表另存为：ExcelApplication1.SaveAs( 'C:\Excel\Demo1.xls' );24) 放弃存盘：ExcelApplication1.ActiveWorkBook.Saved := True;25) 关闭工作簿：ExcelApplication1.WorkBooks.Close;26) 退出Excel：ExcelApplication1.Quit;ExcelApplication1.Disconnect;Top2 楼songlian（雨）回复于2005-04-29 16:37:15 得分0把数据集导如导excel,adsdata可以换成任意你用导的数据集WriteExcel(AdsData:Tclientdataset; sName, Title: string);varExcelApplication1: TExcelApplication;ExcelWorksheet1: TExcelWorksheet;ExcelWorkbook1: TExcelWorkbook;i, j: integer;filename: string;beginfilename := concat(sName, '.xls');tryExcelApplication1 := TExcelApplication.Create(Application);ExcelWorksheet1 := TExcelWorksheet.Create(Application);ExcelWorkbook1 := TExcelWorkbook.Create(Application);ExcelApplication1.Connect;exceptApplication.Messagebox('Excel没有安装!','Hello',MB_ICONERROR + mb_Ok);Abort;end;tryExcelApplication1.Workbooks.Add(EmptyParam, 0);ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _worksheet);AdsData.First;for j := 0 to AdsData.Fields.Count - 1 dobeginExcelWorksheet1.Cells.item[3, j + 1] := AdsData.Fields[j].DisplayLabel;ExcelWorksheet1.Cells.item[3, j + 1].font.size :='10';end;for i := 4 to AdsData.RecordCount + 3 dobeginfor j := 0 to AdsData.Fields.Count - 1 dobeginExcelWorksheet1.Cells.item[i, j + 1] :=AdsData.Fields[j].Asstring;ExcelWorksheet1.Cells.item[i, j + 1].font.size := '10';end;AdsData.Next;end;ExcelWorksheet1.Columns.AutoFit;ExcelWorksheet1.Cells.item[1, 2] := Title;ExcelWorksheet1.Cells.Item[1, 2].font.size :='14';ExcelWorksheet1.SaveAs(filename);Application.Messagebox(pchar('数据成功导出'+ filename),'信息化建设部',mb_Ok);finallyExcelApplication1.Disconnect;ExcelApplication1.Quit;ExcelApplication1.Free;ExcelWorksheet1.Free;ExcelWorkbook1.Free;end;end;。

Delphi快速导出Excel–指尖风暴TyphonFinger

7.iCount := SGAll.ColCount;
8.sheet.cells[19, 1] := 'Plant ' + cbbPlant.Text;
9.//一行一行写，每一行的格式可以设置
10.
11.for i := 1 to SGAll.RowCount – 2 do
12.begin
13.for j := 1 to iCount – 1 do
14.begin
15.sData[1,j]:= SGAll.Cells[j, i];//一行数据。也可以为多行数据
16.end;
17.range:=sheet.Range[sheet.cells[i , 1],sheet.cells[i , iCount-1]]; //选择要写的Execl的单元格
18.range.Value2:=sDataபைடு நூலகம் //写入数据
1.procedure Save_Exce(sheet: Variant);
2.var
3.range,sData: Variant;
4.i, j, citem,iCount: Integer;
5.begin
6.sData:=varArrayCreate([1,1,1,iCount-1],varVariant); //定义数组
每份excel里面都带有图表分多个sheet使用文件流的方式虽然速度很快但格式不好控制使用过nativeexcel与xlsreadwriteii控件都不理想
Delphi快速导出Excel–指尖风暴TyphonFinger
因工作中很多时候要导出Excel的Report.每份Excel里面都带有图表,分多个Sheet,使用文件流的方式虽然速度很快,但格式不好控制,使用过NativeExcel与XLSReadWriteII控件,都不理想.下面的方法,速度改善很多!

delphi dbgrid 保存为excel 简单方法

delphi dbgrid 保存为excel 简单方法你可以使用以下简单方法将Delphi的DBGrid保存为Excel：1. 首先，添加Excel的引用。

在Delphi的“工具”菜单下选择“导入类型库”，然后选择Microsoft Excel并点击“创建单元”。

这将在您的Delphi项目中添加对Excel的引用。

2. 在所需的单元（例如表单或数据模块）中，添加以下单元引用：- ComObj：用于与COM对象（例如Excel）进行交互的单元。

- DBGrids：用于访问和操作DBGrid的单元。

3. 创建一个导出数据的按钮（或其他事件），然后在事件处理程序中添加以下代码：```delphiusesComObj, DBGrids;procedure TForm1.Button1Click(Sender: TObject);varExcel, Workbook, Worksheet: Variant;i, j: Integer;begin// 创建Excel对象Excel := CreateOleObject('Excel.Application');Excel.Visible := True;// 创建Workbook和WorksheetWorkbook := Excel.Workbooks.Add;Worksheet := Workbook.Worksheets[1];// 输出DBGrid的标题行for i := 0 to DBGrid1.Columns.Count - 1 doWorksheet.Cells[1, i+1].Value :=DBGrid1.Columns[i].Title.Caption;// 输出DBGrid的数据行for i := 0 to DBGrid1.DataSource.DataSet.RecordCount - 1 dobeginfor j := 0 to DBGrid1.Columns.Count - 1 doWorksheet.Cells[i+2, j+1].Value :=DBGrid1.DataSource.DataSet.FieldByName(DBGrid1.Columns[j].FieldName).AsString;DBGrid1.DataSource.DataSet.Next;end;// 保存Workbook并关闭ExcelWorkbook.SaveAs('path\to\save\file.xlsx');Workbook.Close;Excel.Quit;end;```在上述代码中，使用`CreateOleObject`函数创建了Excel对象，并将其设置为可见。

delphi导出数据至Excel的七种方法及比较

delphi导出数据至Excel的七种方法及比较一;delphi 快速导出exceluses ComObj,clipbrd;function ToExcel(sfilename:string; ADOQuery:TADOQuery):boolean;constxlNormal=-4143;vary : integer;tsList : TStringList;s,filename :string;aSheet :Variant;excel :OleVariant;savedialog :tsavedialog;beginResult := true;tryexcel:=CreateOleObject('Excel.Application');excel.workbooks.add;except//screen.cursor:=crDefault;showmessage('无法调用Excel！');exit;end;savedialog:=tsavedialog.Create(nil);savedialog.FileName:=sfilename; //存入文件savedialog.Filter:='Excel文件(*.xls)|*.xls';if savedialog.Execute thenbeginif FileExists(savedialog.FileName) thentryif application.messagebox('该文件已经存在，要覆盖吗？','询问',mb_yesno+mb_iconquestion)=idyes thenDeleteFile(PChar(savedialog.FileName))elsebeginExcel.Quit;savedialog.free;//screen.cursor:=crDefault;Exit;end;exceptExcel.Quit;savedialog.free;screen.cursor:=crDefault;Exit;end;filename:=savedialog.FileName;end;savedialog.free;if filename='' thenbeginresult:=true;Excel.Quit;//screen.cursor:=crDefault;exit;end;aSheet:=excel.Worksheets.Item[1];tsList:=TStringList.Create;//tsList.Add('查询结果'); //加入标题s:=''; //加入字段名for y := 0 to adoquery.fieldCount - 1 dobegins:=s+adoQuery.Fields.Fields[y].FieldName+#9 ; Application.ProcessMessages;end;tsList.Add(s);trytryADOQuery.First;While Not ADOQuery.Eof dobegins:='';for y:=0 to ADOQuery.FieldCount-1 dobegins:=s+ADOQuery.Fields[y].AsString+#9; Application.ProcessMessages;end;tsList.Add(s);ADOQuery.next;end;Clipboard.AsText:=tsList.Text;exceptresult:=false;end;finallytsList.Free;end;aSheet.Paste;MessageBox(Application.Handle,'数据导出完毕！','系统提示',MB_ICONINFORMATION or MB_OK);tryif copy(FileName,length(FileName)-3,4)<>'.xls' thenFileName:=FileName+'.xls';Excel.ActiveWorkbook.SaveAs(FileName, xlNormal, '', '', False, False);exceptExcel.Quit;screen.cursor:=crDefault;exit;end;Excel.Visible := false; //true会自动打开已经保存的excelExcel.Quit;Excel := UnAssigned;end;调用:ToExcel('D:\a.xsl',QueryToExcel);//路径可以自定义-------------------------------------------------------------------------------------------------*********************************************************************************** **************二;delphi如何导出EXCEL，代码。

把Delphi开发的排课表导出到Excel文件

把Delphi开发的排课表导出到Excel文件
马锡坤
【期刊名称】《中国医疗设备》
【年(卷),期】2005(020)011
【摘要】本文介绍了Delphi开发的排课系统中把课表导出到Excel文件的方法.在课表导出前,先制定一个Excel电子表格类型的课程表模板.使用排课系统把课表排好以后,把课表导出到Excel电子表格文件.课表导出时,排课系统所用的Access 数据根据日期与课程表模板中相应的单元格一一对应,系统自动完成.
【总页数】2页(P18-19)
【作者】马锡坤
【作者单位】南京军区南京总医院,江苏,南京,210002
【正文语种】中文
【中图分类】TP311.5
【相关文献】
1.排“活”课表，用活教育资源 [J], 王立生
2.利用VB实现将Access数据库导出到Excel文件中 [J], 王洪香;王志刚
3.利用VB实现将Access数据库导出到Excel文件中 [J], 王洪香;王志刚
4.大学课表计算机编排系统的预排处理 [J], 刘亚军;曾庆辉
5.非饱和导排层水分侧向导排作用模型试验验证与影响因素分析 [J], 焦卫国; 詹良通; 季永新; 贺明卫; 邓林恒
因版权原因，仅展示原文概要，查看原文内容请购买。

Delphi解决数据库与EXCEL之间数据导入导出方法比较

Delphi解决数据库与EXCEL之间数据导入导出方法比较廉祥丽
【期刊名称】《宿州教育学院学报》
【年(卷),期】2007(010)006
【摘要】用Delphi实现数据库与Excel之间数据导入导出常常使用server组控件、ole连接和ado等方法,本文对这三种常用的方法从使用方法、执行效率等方面进行了应用比较.希望本文能够抛砖引玉,大家在使用过程中灵活应用,总结出更多好的方法.
【总页数】3页(P136-138)
【作者】廉祥丽
【作者单位】安徽大学,安徽·合肥,230039
【正文语种】中文
【中图分类】TP31
【相关文献】
1.一种用Excel实现基于Delphi数据库查询结果输出的方法 [J], 高美蓉
2.用Delphi建立数据库与Excel之间的数据通信 [J], 涂晓斌;汤鹏志;祖建樱
3.用Delphi处理Excel数据的方法比较 [J], 杨兵
4.一种Excel和MySQL数据库之间数据转换的方法 [J], 徐辉
5.浅谈基于第三方工具的Excel到Oracle数据库之间的数据导入导出方法 [J], 董振
因版权原因，仅展示原文概要，查看原文内容请购买。

DELPHI如何将数据导出到指定格式的EXCEL模版

DELPHI如何将数据导出到指定格式的EXCEL模版DELPHI如何将数据导出到指定格式的EXCEL模版2011-10-31 14:27445人阅读评论(0)收藏举报参考代码1Delphi(Pascal) codepath:=ExtractFilePath(Application.ExeName);if self.OpenDialog1.Execute thenfilename:=self.OpenDialog1.FileName;trySelf.ExcelApplication1:=TExcelApplication.Create(self);Self.ExcelApplication1.Connect;exceptmessagebox(application.Handle,'无法生成Excel报表，请确定安装了Excel后重试','信息',mb_ok or mb_iconinformation);exit;end;Self.ExcelApplication1.Visible[0]:=true;self.ExcelApplication1.DisplayAlerts[0]:=False;self.ExcelApplication1.Workbooks.Open(filename,EmptyPara m,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);self.ExcelWorkbook1.ConnectT o(Self.ExcelApplication1.Work books[1]);self.ExcelWorksheet1:=TExcelWorkSheet.Create(self);self.ExcelWorksheet1.ConnectT o(Self.ExcelWorkbook1.Works heets[1] as _worksheet);i:=self.StringGrid2.RowCount;for j:=1to i-1dobeginxh:=Self.StringGrid2.Cells[0,j];pscj:=self.StringGrid2.Cells[2,j];kscj:=Self.StringGrid2.Cells[4,j];zpcj:=Self.StringGrid2.Cells[5,j];self.ExcelWorksheet1.cells.Item[l+j,m]:=pscj;self.ExcelWorksheet1.cells.Item[l+j,n]:=kscj;self.ExcelWorksheet1.cells.Item[l+j,k]:=zpcj;end;Self.ExcelWorksheet1.SaveAs(filename);Self.ExcelApplication1.Disconnect;Self.ExcelWorkbook1.Disconnect;Self.ExcelWorksheet1.Disconnect;Delphi(Pascal) codepath:=ExtractFilePath(Application.ExeName);if self.OpenDialog1.Execute thenfilename:=self.OpenDialog1.FileName;trySelf.ExcelApplication1:=TExcelApplication.Create(self);Self.ExcelApplication1.Connect;exceptmessagebox(application.Handle,'无法生成Excel报表，请确定安装了Excel后重试','信息',mb_ok or mb_iconinformation);e…tryexcel := CreateOleObject('Excel.Application'); WorkBook := excel.Workbooks.Add('模板的路径.xls'); Sheet := WorkBook.Worksheets[1];exceptexcel := NULL;DJShow('请先安装Excel97/2000。

delphi数据导出到wps表格

delphi数据导出到wps表格delphi 导出数据到WPS表格的⽅法和导出到Excel⽅法雷同，都涉及使⽤Ole。

导出到Wps表格时需要创建这样的Ole组件实例：varetapp:olevariant;myworkbook: OleVariant; //定义⾦⼭表格的⼯作簿对象etapp:=createoleobject('et.application');//启动wps表格现在⽹上有⼀段现成的代码，它就是实现将⼀个表格⾥的数据导出到 wps的电⼦表格⽂档中。

具体代码如下：procedure TfrmMain.Button10Click(Sender: TObject);vari,row,column,j:integer;etapp:olevariant;myworkbook: OleVariant; //定义⾦⼭表格的⼯作簿对象beginetapp:=createoleobject('et.application');//启动wps表格etApp.Visible := true;if dbgrid1.DataSource.DataSet.RecordCount =0 then exit;etapp.Workbooks.Close;myworkbook :=etapp.Workbooks.add;myworkbook.WorkSheets['sheet1'].Activate;column:=1;for j:=0 to dbgrid1.FieldCount-1 dobeginIF dbgrid1.Columns[j].Visible =true thenbeginmyworkbook.worksheets['sheet1'].cells[1,column].value:=dbgrid1.columns.Items[j].Title.caption ;column:=column+1;end;end;row:=2;dbgrid1.DataSource.DataSet.First;While Not (dbgrid1.DataSource.DataSet.Eof) dobegincolumn:=1;for i:=0 to dbgrid1.Columns.Count-1 dobeginIF dbgrid1.Columns[I].Visible =true thenbeginmyworkbook.worksheets['sheet1'].cells[row,column].value:=dbgrid1.Columns[i].Field.AsString ;column:=column+1;end;end;dbgrid1.DataSource.DataSet.Next;row:=row+1;end;showmessage('导出完毕,请在wps表格中进⾏编辑、排版、打印等操作！');end;。

DBGridEh导入到Excel

-----------------------------------------HmExcel.pas-----------------------------------------Delphi中DBGridEh导出数据到Excel中，支持多级（最多三级）层级列标题、支持页脚汇总栏导出。

日期、数字等形式的字符串类型自动使用Text格式，保持原有数据类型，防止Excel自作多情删除数字前导0，或将“-”、识别为日期等。

用法：将HmExcel.pas放置在Delphi的搜索路径下，或程序路径下添加该单元到工程中，然后Use该单元，或者直接将HmExcel添加到uses中。

然后就可以使用ExpToExcel 函数了。

unit HmExcel;interfaceusesForms,ComObj,DBGridEh,SysUtils,DB,Variants;function ExpToExcel(dbg:TDBGridEh;title:string;subTitle:string=''):Boolean;implementation{编写:HanMon 2006-07-10 最后修改:2012-09-11功能:DBGridEh导出到Excel,支持多表头合并(最多三列)，合计栏导出声明:Procedure ExpToExcel(dbg:TDBGridEh;title:string;subTitle:string='');参数:1.dbg :控件,三方控件EhLib控件包中DBGridEh控件实例2.title :字符,导出Excel的第一行，默认字体24号3.subTitle :字符:子标题,默认为当前日期，可以传入导出的时间段注意:1.只支持最多三层标题的合并(多了也是浪费)2.多层标题中如(ABC|A,ABC,ABC|C)在表格中支持合并,即第二行第二列为空ABCA, ,C由于没有实际意义,本单元不支持,只支持如(ABC|A,ABC|B,ABC|C)严格以分割符'|'来识别3.如果有Footer,本单元只支持一行Footer导出}// 01.将数字列转换为Excel格式的字母列function getXlsCol(const col:integer):string;varm,n:integer;beginif (col<=0)or(col>256) thenresult:='Error'//异常(Error)else if col<=26 then beginm:=col+64;result:=chr(m);end else beginm:=(col-1)div 26;n:=col-26*m+64;result:=getXlsCol(m)+chr(n);end;end;{-------------------------------------------------}// 02.获得分层标题的层次数function getLevel(aTitle:string):integer;vari:integer;beginResult:=1;for i:=1 to length(aTitle) doif aTitle[i]='|' thenResult:=Result+1;end;{-------------------------------------------------}// 03.获得分层标题的最大层次function getMaxLevel(dbg:TDBGridEh):integer;varm,i:integer;beginResult:=1;for i:=0 to dbg.Columns.Count-1 do beginm:=getLevel(dbg.Columns[i].Title.Caption);if m>Result thenResult:=m;end;end;{-------------------------------------------------}// 04.获得同一层的结束位置(level=1顶层level=2次顶层)procedure getBeginEndCol(dbg:TDBGridEh;start,level:integer;var c1,c2:integer); vars,s1:string;beginc1:=start;c2:=start;s:=dbg.Columns[start].Title.Caption;if level=1 then beginif pos('|',s)=0 thenexit;s:=copy(s,1,pos('|',s))end else if level=2 then begins1:=copy(s,1,pos('|',s));s:=copy(s,pos('|',s)+1,maxInt);if pos('|',s)=0 thenexit;s:=s1+copy(s,1,pos('|',s));end;while (c1>=0)and(pos(s,dbg.Columns[c1].Title.Caption)>0) doc1:=c1-1;c1:=c1+1;while (c2<=(dbg.Columns.Count-1))and(pos(s,dbg.Columns[c2].Title.Caption)>0) do c2:=c2+1;c2:=c2-1;end;{-------------------------------------------------}// A.导出到Excel:参数1:第三方控件表格,参数2:标题,参数3:副标题function ExpToExcel(dbg:TDBGridEh;title:string;subTitle:string=''):Boolean;vari,j,k,col,maxLevel:integer;app,bok,sht:variant;s,s1:string;beginResult:=False;trytryapp:=getActiveOleObject('excel.application');exceptapp:=createOleObject('excel.application');end;exceptexit;end;trybok:=app.workbooks.add;sht:=app.worksheets[1];col:=dbg.Columns.Count;sht.cells(1,1):=title;if subTitle='' thensubTitle:=FormatDateTime('yyyy-mm-dd',date);sht.cells(2,1):=subTitle;// 可转换成数字或日期时间的字符串,Excel会自动转换成对应数据类型,可牵制设置成文本方式显示for i:=0 to dbg.Columns.Count-1 doif dbg.Columns[i].Field.DataType in [ftString,ftMemo,ftFixedChar,ftWideString,ftFmtMemo] then beginsht.range[format('%s:%s',[getXlsCol(i+1),getXlsCol(i+1)])].NumberFormatLocal:='@';end;//在Excel获得和分层表格相同的单元格合并// 例: ID AB|A AB|B CDE|CD|C CDE|CD|D CDE|E FmaxLevel:=getmaxLevel(dbg);for i:=0 to dbg.Columns.Count-1 do begin//所有数字以字符表示,可以调整为某部分列数字以文本表示s:=dbg.Columns[i].Title.Caption;//单层垂直合并if pos('|',s)=0 then beginsht.cells(3,i+1):=s;if maxLevel>1 thensht.range[format('%s3:%s%d',[getXlsCol(i+1),getXlsCol(i+1),maxLevel+2])].merge;continue;end elses1:=copy(s,1,pos('|',s)-1);s:=copy(s,pos('|',s)+1,maxInt);getBeginEndCol(dbg,i,1,j,k);//第三行合并if i=j then beginsht.cells(3,i+1):=s1;if j<>k thensht.range[format('%s3:%s3', [getXlsCol(j+1),getXlsCol(k+1)])].merge;end;//双层垂直合并if pos('|',s)=0 then beginsht.cells(4,i+1):=s;if maxLevel>2 thensht.range[format('%s4:%s%d',[getXlsCol(i+1),getXlsCol(i+1),maxLevel+2])].merge;continue;end;//三层getBeginEndCol(dbg,i,2,j,k);if i=j then beginsht.cells(4,i+1):=copy(s,1,pos('|',s)-1);if j<>k thensht.range[format('%s4:%s4', [getXlsCol(j+1),getXlsCol(k+1)])].merge;end;sht.cells(5,i+1):=copy(s,pos('|',s)+1,maxInt);end;//导出数据dbg.DataSource.DataSet.First;i:=2+MaxLevel;while not dbg.DataSource.DataSet.Eof do begini:=i+1;sht.cells(i,1):=i-3;for j:=0 to col-1 doif dbg.Columns[j].Field<>nil then //忽略不与数据库字段对应的列sht.cells(i,j+1):=dbg.Columns[j].Field.DisplayText;dbg.DataSource.DataSet.Next;end;dbg.DataSource.DataSet.First;//导出页脚统计数据数据if dbg.FooterRowCount>0 then begini:=i+1;for j:=0 to col-1 doif dbg.Columns[j].Footer.ValueType=fvtStaticText thensht.cells(i,j+1):=dbg.Columns[j].Footer.Valueelse if dbg.Columns[j].Footer.ValueType=fvtNon thencontinueelsesht.cells(i,j+1):=varToStr(dbg.Columns[j].Footer.SumValue);end;//设置Excel格式s:=format('A1:%s1', [getXlsCol(col)]); //主标题(第一行)sht.range[s].merge;sht.range[s].font.size:=14;s:=format('A2:%s2',[getXlsCol(col)]); //副标题(第二行)sht.range[s].merge;sht.range[s].font.size:=10;s:=format('A1:%s%d',[getXlsCol(col),2+MaxLevel]); //主标题，副标题，列名(前三行)水平垂直居中,粗体sht.range[s].HorizontalAlignment:=-4108;sht.range[s].VerticalAlignment:=-4108;sht.range[s].font.bold:=true;if dbg.FooterRowCount>0 then //列名，数据区加边框(不包括页脚区)sht.range[format('A3:%s%d',[getXlsCol(col),i-1])].borders.lineStyle:=1 elsesht.range[format('A3:%s%d',[getXlsCol(col),i])].borders.lineStyle:=1;s:=format('A3:%s%d',[getXlsCol(col),i]); //列名，数据，统计行字体10,自动换行，加边框sht.range[s].font.size:=10;sht.columns[format('A:%s',[getXlsCol(col)])].EntireColumn.AutoFit; //自动适应列宽bok.saved:=true;app.visible:=true;excepton e:Exception do beginApplication.MessageBox(PChar(e.Message+#13+' 数据导出异常，操作取消！'),'提示');bok.saved:=true;app.quit;end;end;sht:=unAssigned;bok:=unAssigned;app:=unAssigned;Result:=True;end;end.。

用Delphi将数据导入到Excel并控制Excel

用Delphi将数据导入到Excel并控制Excel 用Delphi将数据导入到Excel并控制Excel一、调用Excel的方法：一般情况下有两种方法调用Excel：1、直接使用Delphi自带的组件：在Form中分别放入ExcelApplication, ExcelWorkbook和ExcelWorksheet。

2、动态创建Excel文件：首先创建Excel 对象，使用ComObj,Excel2000:var ExcelApp: Variant;ExcelApp := CreateOleObject( 'Excel.Application' );二、导入数据：在程序中，我们可以将查询到的数据（SQL、Access、）导入到Excel中。

例如：用Adoquery查询Access中的数据：1、先查到所需的数据；2、导入：i:=1;Adoquery.First;while not Adoquery.Eof doBeginExcelApp.WorkSheets[1].Cells[i,1].Value := i;//添加序号的值ExcelApp.WorkSheets[1].Cells[i,2].Value := Adoquery.FieldByName('cp_name').AsString;……Inc(i);Adoquery.Next;End;当然也可以把Adotable、Adoquery、Table、Query等组件的数据导入到Excel中。

三、Excel的处理：如果在你已知Excel格式的情况下，可以控制Excel,如下：1、显示当前窗口：ExcelApp.Visible := True;2、更改 Excel 标题栏：ExcelApp.Caption := '标题内容';3、添加新工作簿：ExcelApp.WorkBooks.Add;4、设置第2个工作表为活动工作表：ExcelApp.WorkSheets[2].Activate;5、给单元格赋值：ExcelApp.Cells[1,1].Value := '第一行第一列';6、设置指定列的宽度（单位：字符个数），以第一列为例：ExcelApp.ActiveSheet.Columns[1].ColumnsWidth := 5;7、设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：ExcelApp.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米8、文字水平居中：Excelid.worksheets[1].Rows[1].HorizontalAlignment := $FFFFEFF4; 上面的文字如果不指定行坐标，那么就会设置所有行数据居中显示。

delphi--csv,txt文本转换成excel.

delphi--csv,txt⽂本转换成excel.由于系统使⽤导出的格式是csv，但是如果数字的长度太长的话，⽤excle打开会⽤科学技术法⾃动截断了。

所以开发了⼀个转换程序。

[java]1. unit Unit1;2.3.4.5. interface6.7.8.9. uses10.11. Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,12.13. Dialogs, StdCtrls, ExtCtrls, ComCtrls, ComObj, StrUtils, WinSkinData,14.15. WinSkinStore, Gauges, ShellApi, ClipBrd;16.17.18.19. type20.21. TForm1 = class(TForm)22.23. OpenDialog1: TOpenDialog;24.25. SaveDialog1: TSaveDialog;26.27. Panel1: TPanel;28.29. Edit1: TEdit;30.31. Edit2: TEdit;32.33. Button1: TButton;34.35. Button2: TButton;36.37. Button3: TButton;38.39. StatusBar1: TStatusBar;40.41. SkinData1: TSkinData;42.43. Timer1: TTimer;44.45. Gauge1: TGauge;46.47. progressBar: TProgressBar;48.49. procedure Button1Click(Sender: TObject);50.51. procedure Button2Click(Sender: TObject);52.53. procedure Button3Click(Sender: TObject);54.55. procedure FormPaint(Sender: TObject);56.57. procedure StatusBar1DrawPanel(StatusBar: TStatusBar;58.59. Panel: TStatusPanel; const Rect: TRect);60.61. procedure FormCreate(Sender: TObject);62.63. procedure Timer1Timer(Sender: TObject);64.65. private66.67. progressBarRect:TRect; // 进度条组件的尺⼨68.69. public70.71. { Public declarations }72.73. procedure DropFiles(var Message: TMessage); message WM_DropFiles;74.75. end;76.77.78.79. var80.81. Form1: TForm1;82.83.84.85. implementation86.87.88.89. {$R *.dfm}90.91.92.93. procedure TForm1.DropFiles(var Message: TMessage);94.95. var96.97. i,l: Integer;98.99. p: array[0..254] of Char;100.101. s: String;102.103. begin104.105. i := DragQueryFile(Message.wParam, $FFFFFFFF, nil, 0);106.107. for i := 0 to i - 1 do begin108.109. DragQueryFile(Message.wParam, i, p, 255);110.111. //ShowMessage(StrPas(p));112.113. s := StrPas(p);114.115. l := Pos('.csv',s);116.117. if (l > 0) then118.119. Edit1.Text := StrPas(p)120.121. else122.123. ShowMessage('请选择csv⽂件！');124.125. end;126.127. end;128.129.130.131. procedure TForm1.Button1Click(Sender: TObject);132.133. begin134.135. StatusBar1.Panels[0].Text :='';136.137. OpenDialog1.Execute;138.139. Edit1.Text := OpenDialog1.FileName;140.141. end;142.143.144.145. procedure TForm1.Button2Click(Sender: TObject);146.147. begin148.149. StatusBar1.Panels[0].Text:='';150.151. SaveDialog1.Execute;152.153. Edit2.Text := SaveDialog1.FileName;154.155. end;156.157.158.159. procedure TForm1.Button3Click(Sender: TObject);160.161. var162.163. Excel,WorkBook,xlQuery,A:Variant;164.165. f:TextFile;166.167. i,j,k,b,nLen:integer;168.169. s,xlsFile:string;170.171. pc:PChar;172.173. StepCount : Integer;174.175. vSL: TStringList;176.177. begin178.179. try180.181. if not FileExists(Edit1.Text) then182.183. begin184.185. StatusBar1.Panels[0].Text:='请选择CSV⽂件!!!!!!!'; 186.187. exit;188.189. end;190.191. xlsFile := Edit1.Text;192.193. xlsFile := AnsiReplaceText(xlsFile,'.csv','.xls');194.195. if xlsFile = '' then196.197. begin198.199. StatusBar1.Panels[0].Text:='请选择另存为Excel!!!!!!!'; 200.201. Exit;202.203. end;204.205. //AssignFile(f,Edit1.Text);206.207. //Reset(f);208.209. vSL := TStringList.Create;210.211. //vSL.Delimiter=',';212.213. vSL.LoadFromFile(Edit1.Text);214.215. try216.217. Excel:=CreateOleObject('Excel.Application');218.219. WorkBook:=CreateOleobject('Excel.Sheet');220.221. except222.223. ShowMessage('您的机器⾥未安装Microsoft Excel.'); 224.225. Exit;226.227. end;228.229. //动态创建进度条组件progressBar230.231.232.233. StepCount:=vSL.Count; // 循环的总数⽬234.235. timer1.Enabled:=true;236.237. with progressBar do238.239. begin240.241. // 先确定进度条组件的尺⼨和位置242.243. Top:=ProgressBarRect.Top;244.245. Left:=ProgressBarRect.Left;246.247. Width:=ProgressBarRect.Right-ProgressBarRect.Left; 248.249. Height:=ProgressBarRect.Bottom-ProgressBarRect.Top; 250.251. Parent:=StatusBar1; // parent属性设置为状态栏组件252.253. Visible:=True; // 使进度条可见254.255. Min:=0;// 设定进度条的范围和步长256.257. Max:=StepCount div 300;258.259. Step:=1;260.261. end;262.263. //pb.Visible := true;264.265. WorkBook := Excel.workbooks.add;266.267. Excel.worksheets[1].activate;268.269. Excel.Visible:=false;270.271. // Clipboard.AsText:=vSL.Text;272.273. //计算有多少列274.275. s:=vSL[0];276.277. pc := PChar(s);278.279. k:=0;280.281. b:=1;282.283. j:=1;284.285. nLen := strlen(pc);286.287. while k<nLen do288.289. begin;290.291. if pc[k] = ',' then292.293. begin294.295. inc(j);296.297. end;298.299. inc(k);300.301. end;302.303.304.305. A:=VarArrayCreate([0,j],varVariant);306.307. for i:=0 to j do308.309. A[i]:=2;310.311.312.313. xlQuery := Excel.worksheets[1].QueryTables.Add('TEXT;'+Edit1.Text,Excel.worksheets[1].Range['A1']); 314.315. // := '';316.317. xlQuery.FieldNames := True;318.319. xlQuery.RowNumbers := False;320.321. xlQuery.FillAdjacentFormulas := False;322.323. xlQuery.PreserveFormatting := True;324.325. xlQuery.RefreshOnFileOpen := False;326.327. //xlQuery.RefreshStyle := 'xlInsertDeleteCells';328.329. xlQuery.SavePassword := False;330.331. xlQuery.SaveData := True;332.333. xlQuery.AdjustColumnWidth := True;334.335. xlQuery.RefreshPeriod := 0;336.337. xlQuery.TextFilePromptOnRefresh := False;338.339. xlQuery.TextFilePlatform := 936;340.341. xlQuery.TextFileStartRow := 1;342.343. //xlQuery.TextFileParseType := 'xlDelimited';344.345. //xlQuery.TextFileTextQualifier := 'xlTextQualifierDoubleQuote';346.347. xlQuery.TextFileConsecutiveDelimiter := False;348.349. xlQuery.TextFileTabDelimiter := False;350.351. xlQuery.TextFileSemicolonDelimiter := False;352.353. xlQuery.TextFileCommaDelimiter := True;354.355. xlQuery.TextFileSpaceDelimiter := False;356.357. xlQuery.TextFileColumnDataTypes := A;358.359. xlQuery.TextFileTrailingMinusNumbers := True;360.361. xlQuery.Refresh;362.363. if FileExists(xlsFile) then364.365. DeleteFile(xlsFile);366.367. // Excel.worksheets[1].Paste;368.369. WorkBook.SaveAs(xlsFile);370.371. StatusBar1.Panels[0].Text:='转换成功!!!!!!!';372.373. progressBar.Visible:=false;374.375. finally376.377. if vSL<>nil then378.379. vSL.Free;380.381. if not VarIsEmpty(WorkBook) then WorkBook.close;382.383. if not VarIsEmpty(Excel) then Excel.quit;384.385. //if not VarIsEmpty(A) then varfree(A);386.387. timer1.Enabled:=false;388.389. end;390.391. end;392.393.394.395. procedure TForm1.FormPaint(Sender: TObject);396.397. begin398.399. StatusBar1.Panels[0].Text:='中国建设银⾏版权所有..........'; 400.401.402.403. end;404.405.406.407. procedure TForm1.StatusBar1DrawPanel(StatusBar: TStatusBar; 408.409. Panel: TStatusPanel; const Rect: TRect);410.411. begin412.413. progressBarRect:=Rect;414.415. end;416.417.418.419. procedure TForm1.FormCreate(Sender: TObject);420.421. begin422.423. DragAcceptFiles(Handle, True);424.425. end;426.427.428.429. procedure TForm1.Timer1Timer(Sender: TObject);430.431. begin433. progressBar.Stepit;434.435. //Application.ProcessMessages;436.437. //Sleep(ProgressBar.Position);438.439. end;440.441.442.443. end.原来使⽤的是[csharp]1. for i:=1 to StepCount do2.3. begin4.5. //Readln(f,s);6.7. progressBar.Stepit;// 循环使进度显⽰条累加8.9. s:=vSL[i-1];10.11. pc := PChar(s);12.13. k:=0;14.15. b:=1;16.17. j:=0;18.19. nLen := strlen(pc);20.21. while k<nLen do22.23. begin;24.25. if pc[k] = ',' then26.27. begin28.29. inc(j);30.31. Excel.cells[i,j].NumberFormat:='@';32.33. Excel.cells[i,j].value:=Copy(s,b,k-b+1);34.35. b:=k+2;36.37. end;38.39. inc(k);40.41. end;42.43. inc(j);44.45. Excel.cells[i,j].NumberFormat:='@';46.47. Excel.cells[i,j].value:=Copy(s,b,k-b+1);48.49. end;50.51. 上⾯的代码是遍历整个⽂件，判断是否有逗号，然后对每个格⼦插⼊数据。

Delphi—将TDBGrid数据导出至Excel（支持多sheet）

Delphi—将TDBGrid数据导出至Excel（支持多sheet）procedureTfrmList.SaveT oExcel(dg:TDBGrid;ado:TADOQuery);varMsExcel,sheet:variant;dialogSave:TSaveDialog;i:integer;s,str:string;strlist:TStringList;fExist:boolean;begindialogsave:=TSaveDialog.Create(Application);dialogsave.Filter:='Excel文件(*.xls) |*.xls';str:=formatdatetime('yymmddhhmmss',now);if dialogsave.Execute thenbeginscreen.Cursor:=crHourGlass;//****创建MSEXCEL对象tryMsExcel:=CreateOleObject('Excel.Application');exceptshowmessage('请确定您的计算机是否已正确安装Microsoft Excel ?');freeandnil(dialogsave);screen.Cursor:= crDefault;exit;end;try//****为新工作表命名，默认为当前的日期时间if InputQuery('输入工作表名称','该类文件允许以多个工作表的形式进行保存，请输入该工作表的名称',str) thenbegin//****以下代码先检测导出的文件是否已存在，如果已存在，则打开并增加一工作表，否则新建if fileExists(dialogsave.FileName) thenbeginfExist:=true;msexcel.workbooks.open(dialogsave.FileName); //打开已存在的文件sheet:=msexcel.worksheets.add; //新增一工作表endelsebeginfExist:=false;msexcel.workbooks.add; //新建一工作簿sheet:=msexcel.workbooks[1].worksheets[1];end;//****以下代码将DBGrid内容复制到粘贴板中strlist:=Tstringlist.Create;s:='';for i:=0 to dg.FieldCount-1 do //将标题行加入字符串S中s:=s+dg.Fields[i].FieldName+#9;strlist.Add(S); //将标题行加入至字符串列表strList中ado.First;while not ado.Eof do //穷举数据库，并加入字符串列表中begins:='';for i:=0 to dg.FieldCount-1 dos:= s+dg.Fields[i].AsString+#9;strlist.Add(s);ado.Next;end;clipboard.AsText:=strlist.Text; //将字符串列表内容加入到粘贴板sheet.cells.NumberFormatLocal:='@'; //设置工作表字体格式为文本sheet.cells.Font.Size:='10'; //设置字体大小sheet.Paste; //粘贴:=str; //为工作表命名if fExist then //保存文件msexcel.workbooks[1].saveelsemsexcel.workbooks[1].SaveAs(dialogsave.filename);showmessage(文件已成功导出至以下位置: '+dialogsave.filename);end;exceptshowmessage(文件不可用，请稍后重试！');freeandnil(strlist);msexcel.quit; //退出Excelmsexcel:=Unassigned ; //释放MSEXCEL对象freeandnil(dialogsave);screen.Cursor:= crDefault;exit;end;freeandnil(strlist);msexcel.quit; //退出Excelmsexcel:=Unassigned ; //释放MSEXCEL对象freeandnil(dialogsave);screen.Cursor:= crDefault; end;end;。

DelphiDBGridEh导出Excel

DelphiDBGridEh导出Excel unit Unit_DBGridEhToExcel;interfaceusesSysUtils, Variants, Classes, Graphics, Controls, Forms, Excel2000, ComObj,Dialogs, DB, DBGridEh, windows,ComCtrls,ExtCtrls;typeTDBGridEhToExcel = class(TComponent)privateFProgressForm: TForm; {进度窗体}FtempGauge: TProgressBar; {进度条}FShowProgress: Boolean; {是否显⽰进度窗体}FShowOpenExcel:Boolean; {是否导出后打开Excel⽂件}FDBGridEh: TDBGridEh;FTitleName: TCaption; {Excel⽂件标题}FUserName: TCaption; {制表⼈}procedure SetShowProgress(const Value: Boolean); {是否显⽰进度条}procedure SetShowOpenExcel(const Value: Boolean); {是否打开⽣成的Excel⽂件}procedure SetDBGridEh(const Value: TDBGridEh);procedure SetTitleName(const Value: TCaption); {标题名称}procedure SetUserName(const Value: TCaption); {使⽤⼈名称}procedure CreateProcessForm(AOwner: TComponent); {⽣成进度窗体}publicconstructor Create(AOwner: TComponent); override;destructor Destroy; override;procedure ExportToExcel; {输出Excel⽂件}publishedproperty DBGridEh: TDBGridEh read FDBGridEh write SetDBGridEh;property ShowProgress: Boolean read FShowProgress write SetShowProgress; //是否显⽰进度条property ShowOpenExcel: Boolean read FShowOpenExcel write SetShowOpenExcel; //是否打开Excelproperty TitleName: TCaption read FTitleName write SetTitleName;property UserName: TCaption read FUserName write SetUserName;end;implementationconstructor TDBGridEhToExcel.Create(AOwner: TComponent);begininherited Create(AOwner);FShowProgress := True;FShowOpenExcel:= True;end;procedure TDBGridEhToExcel.SetShowProgress(const Value: Boolean);beginFShowProgress := Value;end;procedure TDBGridEhToExcel.SetDBGridEh(const Value: TDBGridEh);beginFDBGridEh := Value;end;procedure TDBGridEhToExcel.SetTitleName(const Value: TCaption);beginFTitleName := Value;end;procedure TDBGridEhToExcel.SetUserName(const Value: TCaption);beginFUserName := Value;end;function IsFileInUse(fName: string ): boolean;varHFileRes: HFILE;beginResult :=false;if not FileExists(fName) then exit;HFileRes :=CreateFile(pchar(fName), GENERIC_READor GENERIC_WRITE,0, nil,OPEN_EXISTING,FILE_ATTRIBUTE_NORMAL, 0);Result :=(HFileRes=INVALID_HANDLE_VALUE);if not Result thenCloseHandle(HFileRes);end;procedure TDBGridEhToExcel.ExportToExcel;varXLApp: Variant;Sheet: Variant;s1, s2: string;Caption,Msg: String;Row, Col: integer;iCount, jCount: Integer;FBookMark: TBookmark;FileName: String;SaveDialog1: TSaveDialog;begin//如果数据集为空或没有打开则退出if not DBGridEh.DataSource.DataSet.Active then Exit;SaveDialog1 := TSaveDialog.Create(Nil);SaveDialog1.FileName := TitleName + '_' + FormatDateTime('YYMMDDHHmmSS', now);SaveDialog1.Filter := 'Excel⽂件|*.xls';if SaveDialog1.Execute thenFileName := SaveDialog1.FileName;SaveDialog1.Free;if FileName = ''then Exit;while IsFileInUse(FileName) dobeginif Application.MessageBox('⽬标⽂件使⽤中，请退出⽬标⽂件后点击确定继续！','注意', MB_OKCANCEL + MB_ICONWARNING) = IDOK thenbeginendelsebeginExit;end;end;if FileExists(FileName) thenbeginMsg := '已存在⽂件（' + FileName + '），是否覆盖？';if Application.MessageBox(PChar(Msg), '提⽰', MB_YESNO + MB_ICONQUESTION + MB_DEFBUTTON2) = IDYES then begin//删除⽂件DeleteFile(PChar(FileName))endelseexit;end;Application.ProcessMessages;Screen.Cursor := crHourGlass;//显⽰进度窗体if ShowProgress thenCreateProcessForm(nil);if not VarIsEmpty(XLApp) thenbeginXLApp.DisplayAlerts := False;XLApp.Quit;VarClear(XLApp);end;//通过ole创建Excel对象tryXLApp := CreateOleObject('Excel.Application');exceptMessageDlg('创建Excel对象失败，请检查你的系统是否正确安装了Excel软件！', mtError, [mbOk], 0);Screen.Cursor := crDefault;Exit;end;//⽣成⼯作页XLApp.WorkBooks.Add[XLWBatWorksheet];XLApp.WorkBooks[1].WorkSheets[1].Name := TitleName;Sheet := XLApp.Workbooks[1].WorkSheets[TitleName];//写标题sheet.cells[1, 1] := TitleName;sheet.range[sheet.cells[1, 1], sheet.cells[1, DBGridEh.Columns.Count]].Select; //选择该列XLApp.selection.HorizontalAlignment := $FFFFEFF4; //居中XLApp.selection.MergeCells := True; //合并//写表头Row := 1;jCount := 3;for iCount := 0to DBGridEh.Columns.Count - 1dobeginCol := 2;Row := iCount+1;Caption := DBGridEh.Columns[iCount].Title.Caption;while POS('|', Caption) > 0dobeginjCount := 4;s1 := Copy(Caption, 1, Pos('|',Caption)-1);if s2 = s1 thenbeginsheet.range[sheet.cells[Col, Row-1],sheet.cells[Col, Row]].Select;XLApp.selection.HorizontalAlignment := $FFFFEFF4;XLApp.selection.MergeCells := True;endelseSheet.cells[Col,Row] := Copy(Caption, 1, Pos('|',Caption)-1);Caption := Copy(Caption,Pos('|', Caption)+1, Length(Caption));Inc(Col);s2 := s1;end;Sheet.cells[Col, Row] := Caption;Inc(Row);end;//合并表头并居中if jCount = 4thenfor iCount := 1to DBGridEh.Columns.Count doif Sheet.cells[3, iCount].Value = ''thenbeginsheet.range[sheet.cells[2, iCount],sheet.cells[3, iCount]].Select;XLApp.selection.HorizontalAlignment := $FFFFEFF4;XLApp.selection.MergeCells := True;endelse beginsheet.cells[3, iCount].Select;XLApp.selection.HorizontalAlignment := $FFFFEFF4;end;//读取数据DBGridEh.DataSource.DataSet.DisableControls;FBookMark := DBGridEh.DataSource.DataSet.GetBookmark;DBGridEh.DataSource.DataSet.First;while not DBGridEh.DataSource.DataSet.Eof dobeginfor iCount := 1to DBGridEh.Columns.Count dobegin//Sheet.cells[jCount, iCount] :=DBGridEh.Columns.Items[iCount-1].Field.AsString;case DBGridEh.DataSource.DataSet.FieldByName(DBGridEh.Columns.Items[iCount-1].FieldName).DataType offtSmallint, ftInteger, ftWord, ftAutoInc, ftBytes:Sheet.cells[jCount, iCount] :=DBGridEh.Columns.Items[iCount-1].Field.asinteger;ftFloat, ftCurrency, ftBCD:Sheet.cells[jCount, iCount] :=DBGridEh.Columns.Items[iCount-1].Field.AsFloat;elseif DBGridEh.DataSource.DataSet.FieldByName(DBGridEh.Columns.Items[iCount-1].FieldName) is TBlobfield then// 此类型的字段(图像等)暂⽆法读取显⽰ Sheet.cells[jCount, iCount] :=DBGridEh.Columns.Items[iCount-1].Field.AsStringelseSheet.cells[jCount, iCount] :=''''+DBGridEh.Columns.Items[iCount-1].Field.AsString;end;end;Inc(jCount);//显⽰进度条进度过程if ShowProgress thenbeginFtempGauge.Position := DBGridEh.DataSource.DataSet.RecNo;FtempGauge.Refresh;end;DBGridEh.DataSource.DataSet.Next;end;if DBGridEh.DataSource.DataSet.BookmarkValid(FBookMark) thenDBGridEh.DataSource.DataSet.GotoBookmark(FBookMark);DBGridEh.DataSource.DataSet.EnableControls;//读取表脚if DBGridEh.FooterRowCount > 0thenbeginfor Row := 0to DBGridEh.FooterRowCount-1dobeginfor Col := 0to DBGridEh.Columns.Count-1doSheet.cells[jCount, Col+1] := DBGridEh.GetFooterValue(Row,DBGridEh.Columns[Col]);Inc(jCount);end;end;//调整列宽// for iCount := 1 to DBGridEh.Columns.Count do// Sheet.Columns[iCount].EntireColumn.AutoFit;sheet.cells[1, 1].Select;XlApp.Workbooks[1].SaveAs(FileName);XlApp.Visible := True;XlApp := Unassigned;if ShowProgress thenFreeAndNil(FProgressForm);Screen.Cursor := crDefault;end;destructor TDBGridEhToExcel.Destroy;begininherited Destroy;end;procedure TDBGridEhToExcel.CreateProcessForm(AOwner: TComponent);varPanel: TPanel;beginif Assigned(FProgressForm) thenexit;FProgressForm := TForm.Create(AOwner);with FProgressForm dobegintry := '宋体'; {设置字体}Font.Size := 10;BorderStyle := bsNone;Width := 300;Height := 30;BorderWidth := 1;Color := clBlack;Position := poScreenCenter;Panel := TPanel.Create(FProgressForm);with Panel dobeginParent := FProgressForm;Align := alClient;Caption := '正在导出Excel，请稍候......';Color:=$00E9E5E0;end;FtempGauge:=TProgressBar.Create(Panel);with FtempGauge dobeginParent := Panel;Align:=alClient;Min := 0;Max:= DBGridEh.DataSource.DataSet.RecordCount;Position := 0;end;exceptend;end;FProgressForm.Show;FProgressForm.Update;end;procedure TDBGridEhToExcel.SetShowOpenExcel(const Value: Boolean); beginFShowOpenExcel:=Value;end;end.调⽤:varDbOut : TDBGridEhToExcel;beginDbOut := TDBGridEhToExcel.Create(Self);DbOut.TitleName := Caption;DbOut.ShowProgress := True;DbOut.ShowOpenExcel := True;DbOut.DBGridEh := DBGridEh1;DbOut.ExportToExcel;FreeAndNil(DbOut);。

【Delphi】汇出Excel时,Excel单元格格式设置(附Delphi操作Excel方法)

【Delphi】汇出Excel时，Excel单元格格式设置（附Delphi操作Excel方法）一个例子：excelworksheet1.Cells.Item[row,3]. numberformatlocal:='$#,##0.00;[红色]-$#,##0.00';excelworksheet1.Cells.Item[row,3].Formula:='=SUM(R[-'+inttostr(row-rowflag+1)+']C:R[-1]C)';excelworksheet1.Cells.Item[row,3].Borders[3].LineStyle :=xlContinuous; excelworksheet1.Cells.Item[row,3].Borders[4].LineStyle :=xlDouble; excelworksheet1.Cells.Item[row,4].Borders[3].LineStyle :=xlContinuous; excelworksheet1.Cells.Item[row,4].Borders[4].LineStyle :=xlDouble; excelworksheet1.Cells.Item[row,4].Formula:='=R[-1]C';注：不知道公式及格式，均可通过录制宏找出对应公式及格式。

效果如下图：资料来自网络单元格设置1.设置单元格线框Excel.ActiveSheet.Range[B10:C13].Borders[N].LineStyle := xlNoneExcel.ActiveSheet.Range[B10:C13].Borders[N].Weight := xlThin边框的类型 Borders[N]xlEdgeLeft 左=1xlEdgeRight 右=2xlEdgeTop 顶=3xlEdgeBottom 底=4xlDiagonalUp 左上右下=5xlDiagonalDown 左下右上=6xlEdgeLeft 外部左边框=7xlEdgeTop 外部上边框=8xlEdgeBottom 外部下边框=9xlEdgeRight 外部右边框=10xlInsideVertical 内部竖线=11xlInsideHorizontal 内部横线=12(其中1：为左 2：右 3：顶 4：底 5：斜\ 6：斜/)线条类型LineStyle,宽度Weight单条线的LineStyle := xlContinuous双条线的LineStyle := xlDouble虚线 xlHairline 1实线 xlThin中实线 xlMedium粗实线 xlThick2.给单元格赋值：Excel.Cells[1,4].Value := 第一行第四列;3.设置第一行字体属性(隶书,蓝色,加粗,下划线)：Excel.ActiveSheet.Rows[1] := 隶书; Excel.ActiveSheet.Rows[1].Font.Color := clBlue; Excel.ActiveSheet.Rows[1].Font.Bold := True; Excel.ActiveSheet.Rows[1].Font.UnderLine := True;4.设置整个表字体为9Excel.Cells.Font.Size:=9;5.在第8行之前插入/删除分页符：Excel.WorkSheets[1].Rows[8].PageBreak := 1; (0为删除)6.清除第一行第四列单元格公式：Excel.ActiveSheet.Cells[1,4].ClearContents;7.从数字类型转换成文本类型(不知道格式化字符串，请录制宏，抽出宏中格式化字符串。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

delphi导出数据至Excel的几种方法及比较一、delphi 快速导出exceluses ComObj,clipbrd;function ToExcel(sfilename:string; ADOQuery:TADOQuery):boolean;constxlNormal=-4143;vary : integer;tsList : TStringList;s,filename :string;aSheet :V ariant;excel :OleV ariant;savedialog :tsavedialog;beginResult := true;tryexcel:=CreateOleObject('Excel.Application');excel.workbooks.add;except//screen.cursor:=crDefault;showmessage('无法调用Excel！');exit;end;savedialog:=tsavedialog.Create(nil);savedialog.FileName:=sfilename; //存入文件savedialog.Filter:='Excel文件(*.xls)|*.xls';if savedialog.Execute thenbeginif FileExists(savedialog.FileName) thentryif application.messagebox('该文件已经存在，要覆盖吗？','询问',mb_yesno+mb_iconquestion)=idyes thenDeleteFile(PChar(savedialog.FileName))elsebeginExcel.Quit;savedialog.free;//screen.cursor:=crDefault;Exit;end;exceptExcel.Quit;screen.cursor:=crDefault;Exit;end;filename:=savedialog.FileName;end;savedialog.free;if filename='' thenbeginresult:=true;Excel.Quit;//screen.cursor:=crDefault;exit;end;aSheet:=excel.Worksheets.Item[1];tsList:=TStringList.Create;//tsList.Add('查询结果'); //加入标题s:=''; //加入字段名for y := 0 to adoquery.fieldCount - 1 dobegins:=s+adoQuery.Fields.Fields[y].FieldName+#9 ;Application.ProcessMessages;end;tsList.Add(s);trytryADOQuery.First;While Not ADOQuery.Eof dobegins:='';for y:=0 to ADOQuery.FieldCount-1 dobegins:=s+ADOQuery.Fields[y].AsString+#9;Application.ProcessMessages;end;tsList.Add(s);ADOQuery.next;end;Clipboard.AsText:=tsList.Text;exceptresult:=false;end;finallytsList.Free;aSheet.Paste;MessageBox(Application.Handle,'数据导出完毕！','系统提示',MB_ICONINFORMA TION or MB_OK);tryif copy(FileName,length(FileName)-3,4)<>'.xls' thenFileName:=FileName+'.xls';Excel.ActiveWorkbook.SaveAs(FileName, xlNormal, '', '', False, False);exceptExcel.Quit;screen.cursor:=crDefault;exit;end;Excel.Visible := false; //true会自动打开已经保存的excelExcel.Quit;Excel := UnAssigned;end;调用:ToExcel('D:\a.xsl',QueryToExcel);//路径可以自定义-------------------------------------------------------------------------------------------------******************************************************************************************* ******二、delphi如何导出EXCEL，代码。

非第3方控件首先在Uses处加上ComObjprocedure TForm1.Button1Click(Sender: TObject);var h,k:integer;Excelid: OleVariant;s: string;begintryExcelid := CreateOLEObject('Excel.Application');exceptApplication.MessageBox('Excel没有安装！', '提示信息', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);Exit;end;tryADOQuery1.Close;ADOQuery1.SQL.Clear;ADOQuery1.Open;k:=ADOQuery1.RecordCount;Excelid.Visible := True;Excelid.WorkBooks.Add;Excelid.worksheets[1].range['A1:c1'].Merge(True);Excelid.WorkSheets[1].Cells[1,1].Value :='部门编码表' ;Excelid.worksheets[1].Range['a1:a1'].HorizontalAlignment := $FFFFEFF4;Excelid.worksheets[1].Range['a1:a1'].VerticalAlignment := $FFFFEFF4;Excelid.WorkSheets[1].Cells[2,1].Value := '组别编号';Excelid.WorkSheets[1].Cells[2,2].Value := '公司编号';Excelid.WorkSheets[1].Cells[2,3].Value := '组别名称';Excelid.worksheets[1].Range['A1:c1'] := '宋体';Excelid.worksheets[1].Range['A1:c1'].Font.Size := 9;Excelid.worksheets[1].range['A1:c2'].font.bold:=true;Excelid.worksheets[1].Range['A2:c2'].Font.Size := 9;Excelid.worksheets[1].Range['A2:c2'].HorizontalAlignment := $FFFFEFF4;Excelid.worksheets[1].Range['A2:c2'].VerticalAlignment := $FFFFEFF4;h:=3;ADOQuery1.First;while not ADOQuery1.Eof dobegin Excelid.WorkSheets[1].Cells[h,1].Value := Adoquery1.FieldByName('Fdept_id').AsString; Excelid.WorkSheets[1].Cells[h,2].Value := Adoquery1.FieldByName('Ffdept_id').AsString;Excelid.WorkSheets[1].Cells[h,3].Value := Adoquery1.FieldByName('Fdept_name').AsString;Inc(h);Adoquery1.Next;end;s := 'A2:f'+ IntToStr(k+2);Excelid.worksheets[1].Range[s] := '宋体';Excelid.worksheets[1].Range[s].Font.size := 9;Excelid.worksheets[1].Range[s].Borders.LineStyle := 1;Excelid.Quit;exceptApplication.MessageBox('导入数据出错！请检查文件的格式是否正确！', '提示信息', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);end;MessageBox(GetActiveWindow(), 'EXCEL数据导出成功!', '提示信息', MB_OK +MB_ICONW ARNING); end;-----------------------------------------------------------------------------------------------------------------------------------------------******************************************************************************************* *********************************************三、delphi导出EXCELWindows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, ExtCtrls, Mask, ComCtrls, StdCtrls, Buttons, Grids, ValEdit, IdBaseComponent, CheckLst, excel97, ExcelXP, OleServer, ComObj, excel2000, mmsystem, ShellAPI, ADODB, DB, DBGrids, clipbrd;VarFExcel:OleVariant; //excel应用程序FWorkBook :OleVariant; //工作表Temsheet:OleVariant; //工作薄FPicture:OleVariant;//图片tmpstr:String;range:variant;//范围i,j,TemInt:integer;TemFileName:String;beginSaveDialog1.Filter:='.xls';if SaveDialog1.Execute thenbeginTemFileName:=SaveDialog1.FileName+'.xls';Screen.Cursor:=CrHourGlass;TemInt:=0;FExcel:= CreateoleObject('excel.Application');FWorkBook:=FExcel.WorkBooks.Add(-4167); //新的工作表Temsheet:=FWorkBook.Worksheets.Add;:='利润统计';Temsheet.Select;Temsheet.Columns[1].ColumnWidth:=4;//设置列宽度Temsheet.Columns[2].ColumnWidth:=10;Temsheet.Columns[3].ColumnWidth:=16;Temsheet.Columns[4].ColumnWidth:=10;Temsheet.Columns[5].ColumnWidth:=10;Temsheet.Columns[6].ColumnWidth:=10;Temsheet.Columns[7].ColumnWidth:=10;Temsheet.Columns[8].ColumnWidth:=10;Temsheet.Columns[9].ColumnWidth:=20;Temsheet.Columns[10].ColumnWidth:=15;range:=Temsheet.Range[Temsheet.cells[1,1],Temsheet.cells[5,2]];//选定表格range.select;range.merge; //合并单元格FPicture:=Temsheet.Pictures.Insert(tmpstr);FPicture.Left:=20;FPicture.Top:=5;FPicture.width:=50;FPicture.height:=50;FPicture:=null;range:=Temsheet.Range[Temsheet.cells[2,3],Temsheet.cells[3,4]];//选定表格range.select;range.merge;Range.Characters.Font.FontStyle :='加粗';Temsheet.Cells[2,3].HorizontalAlignment:=-4108; //字居中Temsheet.Cells[2,3]:=ComSName;range:=Temsheet.Range[Temsheet.cells[4,3],Temsheet.cells[4,4]];//选定表格range.select;range.merge;Temsheet.Cells[4,3].HorizontalAlignment:=-4108; //字居中Temsheet.Cells[4,3]:=ComEName;range:=Temsheet.Range[Temsheet.cells[2,5],Temsheet.cells[2,6]];//选定表格range.select;range.merge;Temsheet.Cells[2,5].HorizontalAlignment:=-4108; //字居中Temsheet.Cells[2,5]:=ComName;Temsheet.Cells[3,5]:='联系人：';Temsheet.Cells[4,5]:='电话：';Temsheet.Cells[4,6]:=ComPhone;Temsheet.Cells[5,5]:='传真：';Temsheet.Cells[5,6]:=ComFax;range:=Temsheet.Range[Temsheet.cells[6,1],Temsheet.cells[6,10]];//选定表格range.select;range.merge;range:=Temsheet.Range[Temsheet.cells[7,1],Temsheet.cells[7,2]];//选定表格range.select;range.merge;Range.Characters.Font.FontStyle :='加粗';Temsheet.Cells[7,1]:='入库信息：';range:=Temsheet.Range[Temsheet.cells[7,3],Temsheet.cells[7,10]];//选定表格range.select;Temsheet.Cells[8,1]:='序号';Temsheet.Cells[8,1].HorizontalAlignment:=-4108; //字居中Temsheet.Cells[8,1].Interior.Color:=clGray; //单元格背景色range:=Temsheet.Range[Temsheet.cells[8,1],Temsheet.cells[8,1]];//选定表格range.borders.linestyle:=1;//华线for i:=0 to DBGrid1.Columns.Count - 1 dobeginTemsheet.Cells[8,i+2]:=DBGrid1.Columns[i].Title.Caption;Temsheet.Cells[8,i+2].HorizontalAlignment:=-4108; //字居中Temsheet.Cells[8,i+2].Interior.Color:=clGray; //单元格背景色range:=Temsheet.Range[Temsheet.cells[8,i+2],Temsheet.cells[8,i+2]];//选定表格range.borders.linestyle:=1;//华线end;//////////////////////////////////////////////j:=0;DBGrid1.DataSource.DataSet.First;while not DBGrid1.DataSource.DataSet.Eof dobeginTemsheet.Cells[9+j,1].Value:=j+1;Temsheet.Cells[9+j,1].HorizontalAlignment:=-4108; //字居中range:=Temsheet.Range[Temsheet.cells[9+j,1],Temsheet.cells[9+j,1]];//选定表格range.borders.linestyle:=1;//华线for i:=0 to DBGrid1.Columns.Count - 1 dobeginTemsheet.Cells[9+j,i+2].Value:=DBGrid1.Fields[i].AsString;range:=Temsheet.Range[Temsheet.cells[9+j,i+2],Temsheet.cells[9+j,i+2]];//选定表格range.borders.linestyle:=1;//华线end;DBGrid1.DataSource.DataSet.Next;j:=j+1;end;TemInt:=9+ DBGrid1.DataSource.DataSet.RecordCount;range:=Temsheet.Range[Temsheet.cells[TemInt,1],Temsheet.cells[TemInt,10]];//选定表格range.select;range.merge;TemInt:=TemInt+1;range.select;range.merge;Range.Characters.Font.FontStyle :='加粗';Temsheet.Cells[TemInt,1]:='出库信息：';range:=Temsheet.Range[Temsheet.cells[TemInt,3],Temsheet.cells[TemInt,10]];//选定表格range.select;range.merge;TemInt:=TemInt+1;Temsheet.Cells[TemInt,1]:='序号';Temsheet.Cells[TemInt,1].HorizontalAlignment:=-4108; //字居中Temsheet.Cells[TemInt,1].Interior.Color:=clGray; //单元格背景色range:=Temsheet.Range[Temsheet.cells[TemInt,1],Temsheet.cells[TemInt,1]];//选定表格range.borders.linestyle:=1;//华线for i:=0 to DBGrid2.Columns.Count - 1 dobeginTemsheet.Cells[TemInt,i+2]:=DBGrid2.Columns[i].Title.Caption;Temsheet.Cells[TemInt,i+2].HorizontalAlignment:=-4108; //字居中Temsheet.Cells[TemInt,i+2].Interior.Color:=clGray; //单元格背景色range:=Temsheet.Range[Temsheet.cells[TemInt,i+2],Temsheet.cells[TemInt,i+2]];//选定表格range.borders.linestyle:=1;//华线end;TemInt:=TemInt+1;//////////////////////////////////////////////j:=0;DBGrid2.DataSource.DataSet.First;while not DBGrid2.DataSource.DataSet.Eof dobeginTemsheet.Cells[TemInt+j,1].Value:=j+1;Temsheet.Cells[TemInt+j,1].HorizontalAlignment:=-4108; //字居中range:=Temsheet.Range[Temsheet.cells[TemInt+j,1],Temsheet.cells[TemInt+j,1]];//选定表格range.borders.linestyle:=1;//华线for i:=0 to DBGrid2.Columns.Count - 1 dobeginTemsheet.Cells[TemInt+j,i+2].Value:=DBGrid2.Fields[i].AsString;range:=Temsheet.Range[Temsheet.cells[TemInt+j,i+2],Temsheet.cells[TemInt+j,i+2]];//选定表格range.borders.linestyle:=1;//华线end;DBGrid2.DataSource.DataSet.Next;end;TemInt:=TemInt+ DBGrid2.DataSource.DataSet.RecordCount;TemInt:=TemInt+1;range:=Temsheet.Range[Temsheet.cells[TemInt,1],Temsheet.cells[TemInt,10]];//选定表格range.select;range.merge;TemInt:=TemInt+1;range:=Temsheet.Range[Temsheet.cells[TemInt,1],Temsheet.cells[TemInt,2]];//选定表格range.select;range.merge;Range.Characters.Font.FontStyle :='加粗';Temsheet.Cells[TemInt,1]:='入库总额：';Temsheet.Cells[TemInt,3]:=Trim(Edit1.Text);range:=Temsheet.Range[Temsheet.cells[TemInt,4],Temsheet.cells[TemInt,10]];//选定表格range.select;range.merge;TemInt:=TemInt+1;range:=Temsheet.Range[Temsheet.cells[TemInt,1],Temsheet.cells[TemInt,2]];//选定表格range.select;range.merge;Range.Characters.Font.FontStyle :='加粗';Temsheet.Cells[TemInt,1]:='出库总额：';Temsheet.Cells[TemInt,3]:=Trim(Edit2.Text);range:=Temsheet.Range[Temsheet.cells[TemInt,4],Temsheet.cells[TemInt,10]];//选定表格range.select;range.merge;TemInt:=TemInt+1;range:=Temsheet.Range[Temsheet.cells[TemInt,1],Temsheet.cells[TemInt,2]];//选定表格range.select;range.merge;Range.Characters.Font.FontStyle :='加粗';Temsheet.Cells[TemInt,1]:='总利润：';Temsheet.Cells[TemInt,3]:=Trim(Edit3.Text);range:=Temsheet.Range[Temsheet.cells[TemInt,4],Temsheet.cells[TemInt,10]];//选定表格range.select;range.merge;range:=Temsheet.Range[Temsheet.cells[7,1],Temsheet.cells[TemInt,10]];//选定表格range.borders.linestyle:=1;//华线Application.ProcessMessages;Screen.Cursor:=CrDefault;FExcel.WorkBooks[1].saveas(TemFileName);//保存文件FExcel.workbooks[1].close; //关闭工作表Application.ProcessMessages;MessageBox(Handle,'导出成功','提示',MB_OK);//FExcel.visible:=true;FExcel.quit; //关闭ExcelFExcel := unassigned;shellexecute(0,'open',PChar(ExtractFileName(TemFileName)),nil,PChar(ExtractFilePath(TemFileName)),SW_Sh ow);end;end;--------------------------------------------------------------------------------------------------------------------******************************************************************************************* *************************四、导出到ExcelusesWindows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,Dialogs, ExtCtrls, Mask, ComCtrls, StdCtrls, Buttons, Grids, ValEdit, IdBaseComponent,CheckLst, excel97, ExcelXP, OleServer, comobj, excel2000, mmsystem,ADODB, DB, DBGrids, clipbrd;procedure TFIND_FM.Button1Click(Sender: TObject);vari,j : integer;reportname, wpath : string;ExApp1 : TExcelApplication;ExWrbk1 : TExcelWorkbook;ExWrst1 : TExcelWorksheet;beginif Main_FM.ADOQuery_TEMP.IsEmpty thenbeginShowmessage('沒有可導出的資料!');Exit;endbeginMain_FM.SaveDialog1.FileName := 'qcreport';if Main_FM.savedialog1.Execute thenbegin//savedialog1.FileName := formatdatetime('YYYYMMDDHHMMSS',now())+'md_orderqc_list.xls';reportname := formatdatetime('YYYYMMDDHHMMSS',now())+ExtractFileName(Main_FM.savedialog1.FileName);//reportname := formatdatetime('YYYYMMDDHHMMSS',now())+'';wpath := ExtractFilePath(Main_FM.savedialog1.FileName);//showmessage(wpath);tryExApp1 := TExcelApplication.Create(application);ExWrbk1 := TExcelWorkbook.Create(application);ExWrst1 := TExcelWorksheet.Create(application);ExApp1.Connect;exceptShowmessage('電腦沒裝Excel!無法導出!');Abort;end;trytryExApp1.Workbooks.Add(EmptyParam,0);ExWrbk1.ConnectTo(ExApp1.Workbooks[1]);ExWrst1.ConnectTo(ExWrbk1.Worksheets[1] as _worksheet);Main_FM.ADOQuery_TEMP.First;for j := 0 to Main_FM.ADOQuery_TEMP.FieldCount-1 dobeginExWrst1.Cells.Item[1,j+1] := Main_FM.ADOQuery_TEMP.Fields[j].DisplayName;//end;for i := 2 to Main_FM.ADOQuery_TEMP.RecordCount+1 dobeginfor j := 0 to Main_FM.ADOQuery_TEMP.FieldCount-1 dobeginExWrst1.Cells.Item[i,j+1] := Main_FM.ADOQuery_TEMP.Fields[j].Value;end;Main_FM.ADOQuery_TEMP.Next;end;ExWrst1.SaveAs(wpath+reportname);//ExWrst.SaveAs(formatdatetime('YYYYMMDDHHMMSS',now())+reportname);;Showmessage('數據已成功導出!');exceptShowmessage('導出失敗!');abort;end;finallyExApp1.Disconnect;ExApp1.Quit;ExApp1.Free;ExWrbk1.Free;ExWrst1.Free;end;end;end;end;--------------------------------------------------------------------------------------------------******************************************************************************************* *******delphi导出数据至Excel的三种方法及比较闲来无事，跑到网上搜集了几种导出DataSet至Excel的几种方法。