delphi到處EXCEL代碼例子.doc

delphi dbgrid 導(dǎo)出Excel表 / 利用剪貼板，速度很快！適合裝有Excel的機(jī)器/ USES Clipbrd,ComObj; procedure TForm1.Button1Click(Sender: TObject); var str: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 do str:=str+dbgrd1.DataSource.DataSet.fieldsi.DisplayLabel+char(9); str:=str+#13; dbgrd1.DataSource.DataSet.First; while not(dbgrd1.DataSource.DataSet.eof) do begin for i:=0 to dbgrd1.DataSource.DataSet.FieldCount-1 do str:=str+dbgrd1.DataSource.DataSet.Fieldsi.AsString+char(9); str:=str+#13; dbgrd1.DataSource.DataSet.next; lbl1.Caption:=IntToStr(dbgrd1.DataSource.DataSet.RecNo); Application.ProcessMessages; end;/end while dbgrd1.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.workbooks1.worksheets1; :=sheet1; sheet.paste; Clipboard.Clear; / sheet.columns.font.Name:=宋體; / sheet.columns.font.size:=9; / sheet.Columns.AutoFit; excelapp.visible:=true; / lbl3.Caption:=DateTimeToStr(Now); end; /利用TStringList，速度很快！適合沒有裝Excel的機(jī)器/ procedure TForm1.Button1Click(Sender: TObject); var s:TStringList; str:string; i:Integer; begin / lbl1.Caption:=DateTimeToStr(Now); str:=; dbgrd1.DataSource.DataSet.DisableControls; for i:=0 to dbgrd1.DataSource.DataSet.FieldCount-1 do str:=str+dbgrd1.DataSource.DataSet.fieldsi.DisplayLabel+char(9); str:=str+#13; dbgrd1.DataSource.DataSet.First; while not(dbgrd1.DataSource.DataSet.eof) do begin for i:=0 to dbgrd1.DataSource.DataSet.FieldCount-1 do str:=str+dbgrd1.DataSource.DataSet.Fieldsi.AsString+char(9); str:=str+#13; dbgrd1.DataSource.DataSet.next; / lbl3.Caption:=IntToStr(dbgrd1.DataSource.DataSet.RecNo); / Application.ProcessMessages; end;/end while dbgrd1.DataSource.DataSet.EnableControls; s:=TStringList.Create; s.Add(str); s.SaveToFile(c:temp.xls);/保存到c:temp.xls s.Free; / lbl2.Caption:=DateTimeToStr(Now); end; /*(Delphi)Excel的快速導(dǎo)入*(Delphi)Excel的快速導(dǎo)入/怎樣可以提高EXCEL的導(dǎo)出速度？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.Worksheetssheet1;/設(shè)置這一列為 文本列 ，讓 00123 正確顯示， 而不是自動轉(zhuǎn)換為123xlSheet.ColumnsC:C.NumberFormatLocal := ;xlApp.Visible := True;/把查詢結(jié)果導(dǎo)入EXCEL數(shù)據(jù)xlQuery := xlSheet.QueryTables.Add(adoRecordset,xlSheet.RangeA1); /關(guān)鍵是這一句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(確認(rèn)導(dǎo)出excel表嗎？,提示,mb_okcancel+mb_iconinformation)=idcancel then exit;Eclapp := createoleobject(Excel.Application);Eclapp.workbooks.add;for i:=0 to dbgrid2.FieldCount-1 dobeginEclapp.cells1,i+1:=dbgrid2.Columnsi.Title.Caption;end;Eclapp.cells1,5:=簽字;adoquery1.First;n:=2;while not adoquery1.Eof dobegineclapp.cellsn,1 := adoquery1.Fields0.AsString;eclapp.cellsn,2 := adoquery1.Fields1.AsString;eclapp.cellsn,3 := adoquery1.Fields2.AsString;eclapp.cellsn,4 := adoquery1.Fields4.AsString;eclapp.cellsn,6 := ;inc(n);adoquery1.Next;end;eclapp.cellsn,1 := 滿足條件記錄的總數(shù)為：+inttostr(adoquery1.RecordCount)+條;application.MessageBox(數(shù)據(jù)導(dǎo)出完成!,提示,mb_ok+mb_iconinformation);eclapp.visible := true;end;方法二procedure CopyDbDataToExcel(Args: array of const); var iCount, jCount: Integer; XLApp: Variant; Sheet,range: Variant; I: Integer; begin Screen.Cursor := crHourGlass; if not VarIsEmpty(XLApp) then begin XLApp.DisplayAlerts := False; XLApp.Quit; VarClear(XLApp); end;try XLApp:=CreateOleObject(Excel.Application); except Screen.Cursor := crDefault; Exit; end;XLApp.WorkBooks.Add; XLApp.SheetsInNewWorkbook := High(Args) + 1;for I := Low(Args) to High(Args) do begin XLApp.WorkBooks1.WorkSheetsI+1.Name := TDBGrid(ArgsI.VObject).Name; Sheet := XLApp.Workbooks1.WorkSheetsTDBGrid(ArgsI.VObject).Name;if not TDBGrid(ArgsI.VObject).DataSource.DataSet.Active then begin Screen.Cursor := crDefault; Exit; end; TDBGrid(ArgsI.VObject).DataSource.DataSet.first; for iCount := 0 to TDBGrid(ArgsI.VObject).Columns.Count - 1 do range:=sheet.rangesheet.cells1,1,sheet.cells1,iCount + 1; range.select; range.merge; sheet.cells1,1:=+fqueryhuman.dbedit2.text+個人報(bào)銷記錄（普通報(bào)銷、特殊報(bào)銷）查詢; jCount :=2; for iCount := 0 to TDBGrid(ArgsI.VObject).Columns.Count - 1 do Sheet.Cells2, iCount + 1:=TDBGrid(ArgsI.VObject).Columns.ItemsiCount.Title.Caption; while not TDBGrid(ArgsI.VObject).DataSource.DataSet.Eof do begin for iCount := 0 to TDBGrid(ArgsI.VObject).Columns.Count - 1 do Sheet.CellsjCount + 1, iCount + 1 := TDBGrid(ArgsI.VObject).Columns.ItemsiCount.Field.AsString;Inc(jCount); TDBGrid(ArgsI.VObject).DataSource.DataSet.Next; end; XlApp.Visible := True; end; Screen.Cursor := crDefault; end;方法三delphi導(dǎo)入/導(dǎo)出excel2008年03月02日 星期日 16:39從Excel文件中,導(dǎo)入數(shù)據(jù)到SQL數(shù)據(jù)庫中,很簡單,直接用下面的語句:-如果接受數(shù)據(jù)導(dǎo)入的表已經(jīng)存在insert into 表 select * fromOPENROWSET(MICROSOFT.JET.OLEDB.4.0,Excel 5.0;HDR=YES;DATABASE=c:test.xls,sheet1$)-如果導(dǎo)入數(shù)據(jù)并生成表select * into 表 fromOPENROWSET(MICROSOFT.JET.OLEDB.4.0,Excel 5.0;HDR=YES;DATABASE=c:test.xls,sheet1$)-如果從SQL數(shù)據(jù)庫中,導(dǎo)出數(shù)據(jù)到Excel,如果Excel文件已經(jīng)存在,而且已經(jīng)按照要接收的數(shù)據(jù)創(chuàng)建好表頭,就可以簡單的用:insert into OPENROWSET(MICROSOFT.JET.OLEDB.4.0,Excel 5.0;HDR=YES;DATABASE=c:test.xls,sheet1$)select * from 表-如果Excel文件不存在,也可以用BCP來導(dǎo)成類Excel的文件,注意大小寫:-導(dǎo)出表的情況EXEC master.xp_cmdshell bcp 數(shù)據(jù)庫名.dbo.表名 out c:test.xls /c -/S服務(wù)器名 /U用戶名 -P密碼-導(dǎo)出查詢的情況EXEC master.xp_cmdshell bcp SELECT au_fname, au_lname FROM pubs.authors ORDER BY au_lname queryout c:test.xls /c -/S服務(wù)器名 /U用戶名 -P密碼-下面是導(dǎo)出真正Excel文件的方法:if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_exporttb) and OBJECTPROPERTY(id, NIsProcedure) = 1)drop procedure dbo.p_exporttbGOcreate proc p_exporttbtbname sysname, -要導(dǎo)出的表名path nvarchar(1000), -文件存放目錄fname nvarchar(250)= -文件名,默認(rèn)為表名asdeclare err int,src nvarchar(255),desc nvarchar(255),out intdeclare obj int,constr nvarchar(1000),sql varchar(8000),fdlist varchar(8000)-參數(shù)檢測if isnull(fname,)= set fname=tbname+.xls-檢查文件是否已經(jīng)存在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-數(shù)據(jù)庫創(chuàng)建語句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+-連接數(shù)據(jù)庫exec err=sp_oacreate adodb.connection,obj outif err0 goto lberrexec err=sp_oamethod obj,open,null,constrif err0 goto lberr-創(chuàng)建表的SQLselect sql=,fdlist=select fdlist=fdlist+,++,sql=sql+,++ +casewhen like %charthen case when a.length255 then memoelse text(+cast(a.length as varchar)+) endwhen like %int or =bit then intwhen like %datetime then datetimewhen like %money then moneywhen like %text then memoelse 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 err0 goto lberrexec err=sp_oadestroy obj-導(dǎo)入數(shù)據(jù)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(Ndbo.p_exporttb) and OBJECTPROPERTY(id, NIsProcedure) = 1)drop procedure dbo.p_exporttbGOcreate proc p_exporttbsqlstr varchar(8000), -查詢語句,如果查詢語句中使用了order by ,請加上top 100 percentpath nvarchar(1000), -文件存放目錄fname nvarchar(250), -文件名sheetname varchar(250)= -要創(chuàng)建的工作表名,默認(rèn)為文件名asdeclare err int,src nvarchar(255),desc nvarchar(255),out intdeclare obj int,constr nvarchar(1000),sql varchar(8000),fdlist varchar(8000)-參數(shù)檢測if isnull(fname,)= set fname=temp.xlsif isnull(sheetname,)= set sheetname=replace(fname,.,#)-檢查文件是否已經(jīng)存在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-數(shù)據(jù)庫創(chuàng)建語句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+-連接數(shù)據(jù)庫exec err=sp_oacreate adodb.connection,obj outif err0 goto lberrexec err=sp_oamethod obj,open,null,constrif err0 goto lberr-創(chuàng)建表的SQLdeclare tbname sysnameset tbname=#tmp_+convert(varchar(38),newid()set sql=select * into +tbname+ from(+sqlstr+) aexec(sql)select sql=,fdlist