導(dǎo)出到Excel的7種方法.docx

目錄導(dǎo)出到Excel的7種方法11.delphi 快速導(dǎo)出excel12.非第3方控件33.54.105.循環(huán)遍歷數(shù)據(jù)，插入到Excel的WookBook116.使用OLE方法導(dǎo)入127.文件流方法15導(dǎo)出到Excel的7種方法后面三種：使用GetTickcount函數(shù)計(jì)算時(shí)差，使用TADOConnect，TADOQuery查詢(xún)數(shù)據(jù)。測(cè)試數(shù)據(jù)city表3179行7列記錄【但是第七章種不支持2007格式,不知為什么】第五種：112941148212121114191332211856第六種：174718401716213716701856第七種：265250234234218234默認(rèn)導(dǎo)出的地址是D盤(pán)根目錄1. delphi 快速導(dǎo)出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;tryecel:=CreateOleObject(Excel.Application);excel.workbooks.add;except/screen.cursor:=crDefault;showmessage(無(wú)法調(diào)用Excel！);exit;end;savedialog:=tsavedialog.Create(nil);savedialog.FileName:=sfilename;savedialog.Filter:=Excel文件(*.xlsx)|*.xlsx;if savedialog.Execute thenbeginif FileExists(savedialog.FileName) thentryif application.messagebox(該文件已經(jīng)存在，要覆蓋嗎？,詢(xún)問(wèn),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.Item1;tsList:=TStringList.Create;/tsList.Add(查詢(xún)結(jié)果); /加入標(biāo)題s:=; /加入字段名for y := 0 to adoquery.fieldCount - 1 dobegins:=s+adoQuery.Fields.Fieldsy.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.Fieldsy.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,數(shù)據(jù)導(dǎo)出完畢！,系統(tǒng)提示,MB_ICONINFORMATION or MB_OK);tryif copy(FileName,length(FileName)-3,4).xlsx thenFileName:=FileName+.xlsx;Excel.ActiveWorkbook.SaveAs(FileName, xlNormal, , , False, False);exceptExcel.Quit;screen.cursor:=crDefault;exit;end;Excel.Visible :=false; /true會(huì)自動(dòng)打開(kāi)已經(jīng)保存的excelExcel.Quit;Excel := UnAssigned;end;2. 非第3方控件procedure TForm1.Button1Click(Sender: TObject);varh,k:integer;Excelid: OleVariant;s: string;begintryExcelid := CreateOLEObject(Excel.Application);except Application.MessageBox(Excel沒(méi)有安裝！, 提示信息, MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);Exit;end;tryADOQuery1.Close;ADOQuery1.SQL.Clear;ADOQuery1.SQL.Add(select * from jj_department);ADOQuery1.Open;k:=ADOQuery1.RecordCount;Excelid.Visible := True;Excelid.WorkBooks.Add;Excelid.worksheets1.rangeA1:c1.Merge(True);Excelid.WorkSheets1.Cells1,1.Value :=部門(mén)編碼表 ;Excelid.worksheets1.Rangea1:a1.HorizontalAlignment := $FFFFEFF4;Excelid.worksheets1.Rangea1:a1.VerticalAlignment := $FFFFEFF4;Excelid.WorkSheets1.Cells2,1.Value := 組別編號(hào);Excelid.WorkSheets1.Cells2,2.Value := 公司編號(hào);Excelid.WorkSheets1.Cells2,3.Value := 組別名稱(chēng);Excelid.worksheets1.RangeA1:c1.Font.Name := 宋體;Excelid.worksheets1.RangeA1:c1.Font.Size := 9;Excelid.worksheets1.rangeA1:c2.font.bold:=true;Excelid.worksheets1.RangeA2:c2.Font.Size := 9;Excelid.worksheets1.RangeA2:c2.HorizontalAlignment := $FFFFEFF4;Excelid.worksheets1.RangeA2:c2.VerticalAlignment := $FFFFEFF4;h:=3;ADOQuery1.First;while not ADOQuery1.Eof dobeginExcelid.WorkSheets1.Cellsh,1.Value := Adoquery1.FieldByName(Fdept_id).AsString;Excelid.WorkSheets1.Cellsh,2.Value := Adoquery1.FieldByName(Ffdept_id).AsString;Excelid.WorkSheets1.Cellsh,3.Value := Adoquery1.FieldByName(Fdept_name).AsString;Inc(h);Adoquery1.Next;end;s := A2:f+ IntToStr(k+2);Excelid.worksheets1.Ranges.Font.Name := 宋體;Excelid.worksheets1.Ranges.Font.size := 9;Excelid.worksheets1.Ranges.Borders.LineStyle := 1;Excelid.Quit;exceptApplication.MessageBox(導(dǎo)入數(shù)據(jù)出錯(cuò)！請(qǐng)檢查文件的格式是否正確！, 提示信息, MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);end;MessageBox(GetActiveWindow(), EXCEL數(shù)據(jù)導(dǎo)出成功!, 提示信息, MB_OK +MB_ICONWARNING);end;3.usesWindows, 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應(yīng)用程序FWorkBook :OleVariant; /工作表Temsheet:OleVariant; /工作薄FPicture:OleVariant;/圖片tmpstr:String;range:variant;/范圍i,j,TemInt:integer;TemFileName:String;beginSaveDialog1.Filter:=.xlsx;if SaveDialog1.Execute thenbeginTemFileName:=SaveDialog1.FileName+.xlsx;Screen.Cursor:=CrHourGlass;TemInt:=0;FExcel:= CreateoleObject(excel.Application);FWorkBook:=FExcel.WorkBooks.Add(-4167); /新的工作表Temsheet:=FWorkBook.Worksheets.Add;Temsheet.Name:=利潤(rùn)統(tǒng)計(jì);Temsheet.Select;Temsheet.Columns1.ColumnWidth:=4;/設(shè)置列寬度Temsheet.Columns2.ColumnWidth:=10;Temsheet.Columns3.ColumnWidth:=16;Temsheet.Columns4.ColumnWidth:=10;Temsheet.Columns5.ColumnWidth:=10;Temsheet.Columns6.ColumnWidth:=10;Temsheet.Columns7.ColumnWidth:=10;Temsheet.Columns8.ColumnWidth:=10;Temsheet.Columns9.ColumnWidth:=20;Temsheet.Columns10.ColumnWidth:=15;range:=Temsheet.RangeTemsheet.cells1,1,Temsheet.cells5,2;/選定表格range.select;range.merge; /合并單元格tmpstr:=ExtractFilePath(ParamStr(0)+tem.jpg; /添加圖片F(xiàn)Picture:=Temsheet.Pictures.Insert(tmpstr);FPicture.Left:=20;FPicture.Top:=5;FPicture.width:=50;FPicture.height:=50;FPicture:=null;range:=Temsheet.RangeTemsheet.cells2,3,Temsheet.cells3,4;/選定表格range.select;range.merge;Range.Characters.Font.FontStyle :=加粗;Temsheet.Cells2,3.HorizontalAlignment:=-4108; /字居中Temsheet.Cells2,3:=ComSName;range:=Temsheet.RangeTemsheet.cells4,3,Temsheet.cells4,4;/選定表格range.select;range.merge;Temsheet.Cells4,3.HorizontalAlignment:=-4108; /字居中Temsheet.Cells4,3:=ComEName;range:=Temsheet.RangeTemsheet.cells2,5,Temsheet.cells2,6;/選定表格range.select;range.merge;Temsheet.Cells2,5.HorizontalAlignment:=-4108; /字居中Temsheet.Cells2,5:=ComName;Temsheet.Cells3,5:=聯(lián)系人：;Temsheet.Cells4,5:=電話(huà)：;Temsheet.Cells4,6:=ComPhone;Temsheet.Cells5,5:=傳真：;Temsheet.Cells5,6:=ComFax;range:=Temsheet.RangeTemsheet.cells6,1,Temsheet.cells6,10;/選定表格range.select;range.merge;range:=Temsheet.RangeTemsheet.cells7,1,Temsheet.cells7,2;/選定表格range.select;range.merge;Range.Characters.Font.FontStyle :=加粗;Temsheet.Cells7,1:=入庫(kù)信息：;range:=Temsheet.RangeTemsheet.cells7,3,Temsheet.cells7,10;/選定表格range.select;range.merge;Temsheet.Cells8,1:=序號(hào);Temsheet.Cells8,1.HorizontalAlignment:=-4108; /字居中Temsheet.Cells8,1.Interior.Color:=clGray; /單元格背景色range:=Temsheet.RangeTemsheet.cells8,1,Temsheet.cells8,1;/選定表格range.borders.linestyle:=1;/華線(xiàn)for i:=0 to DBGrid1.Columns.Count - 1 dobeginTemsheet.Cells8,i+2:=DBGrid1.Columnsi.Title.Caption;Temsheet.Cells8,i+2.HorizontalAlignment:=-4108; /字居中Temsheet.Cells8,i+2.Interior.Color:=clGray; /單元格背景色range:=Temsheet.RangeTemsheet.cells8,i+2,Temsheet.cells8,i+2;/選定表格range.borders.linestyle:=1;/華線(xiàn)end;/j:=0;DBGrid1.DataSource.DataSet.First;while not DBGrid1.DataSource.DataSet.Eof dobeginTemsheet.Cells9+j,1.Value:=j+1;Temsheet.Cells9+j,1.HorizontalAlignment:=-4108; /字居中range:=Temsheet.RangeTemsheet.cells9+j,1,Temsheet.cells9+j,1;/選定表格range.borders.linestyle:=1;/華線(xiàn)for i:=0 to DBGrid1.Columns.Count - 1 dobeginTemsheet.Cells9+j,i+2.Value:=DBGrid1.Fieldsi.AsString;range:=Temsheet.RangeTemsheet.cells9+j,i+2,Temsheet.cells9+j,i+2;range.borders.linestyle:=1;/華線(xiàn)end;DBGrid1.DataSource.DataSet.Next;j:=j+1;end;TemInt:=9+ DBGrid1.DataSource.DataSet.RecordCount;range:=Temsheet.RangeTemsheet.cellsTemInt,1,Temsheet.cellsTemInt,10;/選表格range.select;range.merge;TemInt:=TemInt+1;range:=Temsheet.RangeTemsheet.cellsTemInt,1,Temsheet.cellsTemInt,2;/選表格range.select;range.merge;Range.Characters.Font.FontStyle :=加粗;Temsheet.CellsTemInt,1:=出庫(kù)信息：; range:=Temsheet.RangeTemsheet.cellsTemInt,3,Temsheet.cellsTemInt,10;/選表格range.select;range.merge;TemInt:=TemInt+1;Temsheet.CellsTemInt,1:=序號(hào);Temsheet.CellsTemInt,1.HorizontalAlignment:=-4108; /字居中Temsheet.CellsTemInt,1.Interior.Color:=clGray; /單元格背景色range:=Temsheet.RangeTemsheet.cellsTemInt,1,Temsheet.cellsTemInt,1;/選表格range.borders.linestyle:=1;/華線(xiàn)for i:=0 to DBGrid2.Columns.Count - 1 dobeginTemsheet.CellsTemInt,i+2:=DBGrid2.Columnsi.Title.Caption;Temsheet.CellsTemInt,i+2.HorizontalAlignment:=-4108; /字居中Temsheet.CellsTemInt,i+2.Interior.Color:=clGray; /單元格背景色range:=Temsheet.RangeTemsheet.cellsTemInt,i+2,Temsheet.cellsTemInt,i+2;range.borders.linestyle:=1;/華線(xiàn)end;TemInt:=TemInt+1;/j:=0;DBGrid2.DataSource.DataSet.First;while not DBGrid2.DataSource.DataSet.Eof dobeginTemsheet.CellsTemInt+j,1.Value:=j+1;Temsheet.CellsTemInt+j,1.HorizontalAlignment:=-4108; /字居中range:=Temsheet.RangeTemsheet.cellsTemInt+j,1,Temsheet.cellsTemInt+j,1;range.borders.linestyle:=1;/華線(xiàn)for i:=0 to DBGrid2.Columns.Count - 1 dobeginTemsheet.CellsTemInt+j,i+2.Value:=DBGrid2.Fieldsi.AsString;range:=Temsheet.RangeTemsheet.cellsTemInt+j,i+2,Temsheet.cellsTemInt+j,i+2;range.borders.linestyle:=1;/華線(xiàn)end;DBGrid2.DataSource.DataSet.Next;j:=j+1;end;TemInt:=TemInt+ DBGrid2.DataSource.DataSet.RecordCount;TemInt:=TemInt+1; range:=Temsheet.RangeTemsheet.cellsTemInt,1,Temsheet.cellsTemInt,10;/選表格range.select;range.merge;TemInt:=TemInt+1;range:=Temsheet.RangeTemsheet.cellsTemInt,1,Temsheet.cellsTemInt,2;/選表格range.select;range.merge;Range.Characters.Font.FontStyle :=加粗;Temsheet.CellsTemInt,1:=入庫(kù)總額：;Temsheet.CellsTemInt,3:=Trim(Edit1.Text);range:=Temsheet.RangeTemsheet.cellsTemInt,4,Temsheet.cellsTemInt,10;/選表格range.select;range.merge;TemInt:=TemInt+1;range:=Temsheet.RangeTemsheet.cellsTemInt,1,Temsheet.cellsTemInt,2;/選表格range.select;range.merge;Range.Characters.Font.FontStyle :=加粗;Temsheet.CellsTemInt,1:=出庫(kù)總額：;Temsheet.CellsTemInt,3:=Trim(Edit2.Text);range:=Temsheet.RangeTemsheet.cellsTemInt,4,Temsheet.cellsTemInt,10;/選表格range.select;range.merge;TemInt:=TemInt+1;range:=Temsheet.RangeTemsheet.cellsTemInt,1,Temsheet.cellsTemInt,2;/選表格range.select;range.merge;Range.Characters.Font.FontStyle :=加粗;Temsheet.CellsTemInt,1:=總利潤(rùn)：;Temsheet.CellsTemInt,3:=Trim(Edit3.Text);range:=Temsheet.RangeTemsheet.cellsTemInt,4,Temsheet.cellsTemInt,10;/選表格range.select;range.merge; range:=Temsheet.RangeTemsheet.cells7,1,Temsheet.cellsTemInt,10;/選定表格range.borders.linestyle:=1;/華線(xiàn)Application.ProcessMessages;Screen.Cursor:=CrDefault;FExcel.WorkBooks1.saveas(TemFileName);/保存文件FExcel.workbooks1.close; /關(guān)閉工作表Application.ProcessMessages;MessageBox(Handle,導(dǎo)出成功,提示,MB_OK);/FExcel.visible:=true;FExcel.quit; /關(guān)閉ExcelFExcel := unassigned; shellexecute(0,open,PChar(ExtractFileName(TemFileName),nil,PChar(ExtractFilePath(TemFileName),SW_Show); end;end;4.usesWindows, 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 Tform1.Button1Click(Sender: TObject);vari,j : integer;reportname, wpath : string;ExApp1 : TExcelApplication;ExWrbk1 : TExcelWorkbook;ExWrst1 : TExcelWorksheet; beginif Main_FM.ADOQuery_TEMP.IsEmpty thenbeginShowmessage(沒(méi)有可導(dǎo)出的資料!);Exit;endelsebeginMain_FM.SaveDialog1.FileName := qcreport;if Main_FM.savedialog1.Execute thenbegin/savedialog1.FileName := formatdatetime(yyyy-mm-dd,now()+md_orderqc_list.xlsx;reportname := formatdatetime(yyyy-mm-dd,now()+ExtractFileName(Main_FM.savedialog1.FileName);/reportname := formatdatetime(yyyymmdd,now()+;wpath := ExtractFilePath(Main_FM.savedialog1.FileName);/showmessage(wpath);tryExApp1 := TExcelApplication.Create(application);ExWrbk1 := TExcelWorkbook.Create(application);ExWrst1 := TExcelWorksheet.Create(application);ExApp1.Connect;exceptShowmessage(電腦沒(méi)裝Excel!無(wú)法導(dǎo)出!);Abort;end;trytryExApp1.Workbooks.Add(EmptyParam,0);ExWrbk1.ConnectTo(ExApp1.Workbooks1);ExWrst1.ConnectTo(ExWrbk1.Worksheets1 as _worksheet);Main_FM.ADOQuery_TEMP.First;for j := 0 to Main_FM.ADOQuery_TEMP.FieldCount-1 dobeginExWrst1.Cells.Item1,j+1 := Main_FM.ADOQuery_TEMP.Fieldsj.DisplayName;end;for i := 2 to Main_FM.ADOQuery_TEMP.RecordCount+1 dobeginfor j := 0 to Main_FM.ADOQuery_TEMP.FieldCount-1 dobeginExWrst1.Cells.Itemi,j+1 := Main_FM.ADOQuery_TEMP.Fieldsj.Value;end;Main_FM.ADOQuery_TEMP.Next;end;ExWrst1.SaveAs(wpath+reportname);/ExWrst.SaveAs(formatdatetime(YYYYMMDDHHMMSS,now()+reportname);Showmessage(數(shù)據(jù)已成功導(dǎo)出!);exceptShowmessage(導(dǎo)出失敗!);abort;end;finallyExApp1.Disconnect;ExApp1.Quit;ExApp1.Free;ExWrbk1.Free;ExWrst1.Free;end;end;end;end;5. 循環(huán)遍歷數(shù)據(jù)，插入到Excel的WookBook在下面代碼中沒(méi)有仔細(xì)注意語(yǔ)法（比如沒(méi)有使用try.finally結(jié)構(gòu)體），如果需要使用，請(qǐng)注意：/使用ADO循環(huán)方式保存procedure TForm1.button1Click(Sender: TObject);varEclapp:variant;n:integer;filename: string;t1,t2: Int64;beginEclapp := CreateOleObject(Excel.Application);Eclapp.WorkBooks.Add;Eclapp.Visible:= False;filename := D:第5種方法+ FormatDateTime(yyyy-mm-dd,Now()+.xlsx;label1.Caption := 0;if FileExists(fileName) thenif application.messagebox(該文件已經(jīng)存在，要覆蓋嗎？,詢(xún)問(wèn),mb_yesno+mb_iconquestion)=idyes thenDeleteFile(FileName)刪除舊文件elseexit;t1:= GetTickCount;ADOQuery1.DisableControls;for n:=0 to DBGrideh1.Columns.Count-1 doeclApp.Cells(1,n+1):=DBGrideh1.Columnsn.FieldName; /打印列名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.Fields3.AsString;eclapp.cellsn,5 := ADOQuery1.Fields4.AsString;eclapp.cellsn,6 := ADOQuery1.Fields5.AsString;eclapp.cellsn,7 := ADOQuery1.Fields6.AsString;inc(n);ADOQuery1.Next;application.ProcessMessages;end;ADOQuery1.EnableControls;t2:= GetTickCount;eclapp.visible := false;eclapp.Workbooks1.SaveAs(filename);Eclapp.Quit;Eclapp:= Unassigned;label1.Caption := IntToStr(t2 - t1);end;6. 使用OLE方法導(dǎo)入 先講TDateSet中的數(shù)據(jù)保存為二維OLEVariant數(shù)組中，再保存到Excel Sheet中 procedure TForm1.button2Click(Sender: TObject);varfileName: string;xlApp, Sheet: OleVariant;rowCount, Colcount, index: Integer;t1,t2: Int64;function RefToCell(RowID, ColID: Integer): string;varACount, APos: Integer;beginACount := ColID div 26;APos := ColID mod 26;if APos = 0 thenbeginACount := ACount - 1;APos := 26; end;if ACount = 0 thenResult := Chr(Ord(A) + ColID - 1) + IntToStr(RowID);if ACount = 1 thenResult := A + Chr(Ord(A) + APos - 1) + IntToStr(RowID);if ACount 1 thenResult := Chr(Ord(A) + ACount - 1) + Chr(Ord(A) + APos - 1) + IntToStr(RowID);end;function getData(ds: TDataSet): OleVariant;varData: OLEVariant;i,j : Integer;beginrowCount := ds.RecordCount;colCount := ds.FieldCount;Data := VarArrayCreate(1, rowCount + 1, 1, colCount, varVariant); /1，rowCount 表示第一維數(shù)組的上下標(biāo)，1，colCount表示第二維數(shù)組的上下標(biāo)i := 1;for j := 0 to colCount - 1 dobeginif not ds.Fieldsj.Visible thencontinue;Datai,j + 1 := ds.Fieldsj.DisplayLabel;end;Inc(i);ds.DisableControls;tryds.First;while not ds.Eof dobeginfor j := 0 to colCount - 1 dobeginDatai,j + 1 := ds.Fieldsj.AsString;end;Inc(i);ds.Next;Application.ProcessMessages;end;finallyds.EnableControls;end;result := Data;end;beginfileName := D:第6種方法+ FormatDateTime(yyyy-mm-dd,Now()+.xlsx;label2.Caption := 0;t1:= GetTickCount;/開(kāi)始計(jì)時(shí)if FileExists(fileName) thenif application.messagebox(該文件已經(jīng)存在，要覆蓋嗎？,詢(xún)問(wèn),mb_yesno+mb_iconquestion)=idyes thenDeleteFile(FileName)刪除舊文件elseexit;xlApp := CreateOleObject(Excel.Application);tryXLApp.Visible := False;XLApp.DisplayAlerts := False;XLApp.Workbooks.Add;/ 刪除多余的 worksheetfor index := XLApp.SheetsInNewWorkbook downto 2 dobeginXLApp.Workbooks1.Worksheetsindex.Delete;end;Sheet := XLApp.Workbooks1.Worksheets1;index := 1;if index 0 thenSheet := XLApp.Workbooks1.Worksheets.Add;Sheet.Name := ADOQuery1.Name;/Sheet.Columns.NumberFormatLocal := ; /設(shè)置單元格式為文本Sheet.RangeRefToCell(1, 1), RefToCell(rowCount + 1, colCount).Value := getData(ADOQuery1);XLApp.Workbooks1.SaveAs(fileName); finallyif not VarIsEmpty(XLApp) thenbeginXLApp