《导出到excel的7种方法》由会员分享，可在线阅读，更多相关《导出到excel的7种方法（18页珍藏版）》请在金锄头文库上搜索。
1、目录目录导出到 Excel 的 7 种方法1 1.delphi 快速导出 excel.1 2.非第 3 方控件 3 35 4105.循环遍历数据，插入到 Excel 的 WookBook11 6.使用 OLE 方法导入12 7.文件流方法 15导出到导出到 Excel 的的 7 种方法种方法后面三种： 使用GetTickcount函数计算时差，使用TADOConnect，TADOQuery查询数据。测试数 据city表3179行7列记录【但是第七章种不支持2007格式,不知为什么】 第五种：112941148212121114191332211856 第六种：17471840171621371
2、6701856 第七种：265250234234218234 默认导出的地址是D盘根目录1. delphi 快速导出快速导出 exceluses 加上 ComObj,clipbrd; function ToExcel(sfilename:string; ADOQuery:TADOQuery):boolean; const xlNormal=-4143; var y :integer; tsList :TStringList; s,filename :string; aSheet :Variant; excel :OleVariant; savedialog :tsavedialog; begi
3、n Result := true; try ecel:=CreateOleObject(Excel.Application); excel.workbooks.add;except /screen.cursor:=crDefault;showmessage(无法调用 Excel！); exit; end; savedialog:=tsavedialog.Create(nil); savedialog.FileName:=sfilename;savedialog.Filter:=Excel 文件(*.xlsx)|*.xlsx; if savedialog.Execute then begin i
4、f FileExists(savedialog.FileName) then tryif application.messagebox(该文件已经存在，要覆盖吗？,询问 ,mb_yesno+mb_iconquestion)=idyes then DeleteFile(PChar(savedialog.FileName) else begin Excel.Quit; savedialog.free; /screen.cursor:=crDefault; Exit; end; except Excel.Quit; savedialog.free; screen.cursor:=crDefault;
5、 Exit; end; filename:=savedialog.FileName; end; savedialog.free; if filename= then begin result:=true; Excel.Quit; /screen.cursor:=crDefault; exit; end; aSheet:=excel.Worksheets.Item1; tsList:=TStringList.Create;/tsList.Add(查询结果); /加入标题 s:=; /加入字段名 for y := 0 to adoquery.fieldCount - 1 do begins:=s+
6、adoQuery.Fields.Fieldsy.FieldName+#9 ; Application.ProcessMessages; end; tsList.Add(s); try try ADOQuery.First; While Not ADOQuery.Eof do begin s:=; for y:=0 to ADOQuery.FieldCount-1 do begin s:=s+ADOQuery.Fieldsy.AsString+#9; Application.ProcessMessages; end; tsList.Add(s); ADOQuery.next; end; Clip
7、board.AsText:=tsList.Text; except result:=false; end; finally tsList.Free; end; aSheet.Paste;MessageBox(Application.Handle,数据导出完毕！,系统提示,MB_ICONINFORMATION or MB_OK);try if copy(FileName,length(FileName)-3,4) 1 then Result := Chr(Ord(A) + ACount - 1) + Chr(Ord(A) + APos - 1) + IntToStr(RowID); end; f
8、unction getData(ds: TDataSet): OleVariant; var Data: OLEVariant; i,j : Integer; begin rowCount := ds.RecordCount; colCount := ds.FieldCount;Data := VarArrayCreate(1, rowCount + 1, 1, colCount, varVariant); /1，rowCount 表示第 一维数组的上下标，1，colCount 表示第二维数组的上下标 i := 1;for j := 0 to colCount - 1 do begin if
9、not ds.Fieldsj.Visible then continue; Datai,j + 1 := ds.Fieldsj.DisplayLabel; end; Inc(i); ds.DisableControls; try ds.First; while not ds.Eof do begin for j := 0 to colCount - 1 do begin Datai,j + 1 := ds.Fieldsj.AsString; end; Inc(i); ds.Next; Application.ProcessMessages; end; finally ds.EnableCont
10、rols; end; result := Data; end; beginfileName := D:第 6 种方法+ FormatDateTime(yyyy-mm-dd,Now()+.xlsx; label2.Caption := 0;t1:= GetTickCount;/开始计时 if FileExists(fileName) thenif application.messagebox(该文件已经存在，要覆盖吗？,询问 ,mb_yesno+mb_iconquestion)=idyes thenDeleteFile(FileName)删除旧文件 else exit; xlApp := Cre
11、ateOleObject(Excel.Application); try XLApp.Visible := False; XLApp.DisplayAlerts := False; XLApp.Workbooks.Add;/ 删除多余的 worksheet for index := XLApp.SheetsInNewWorkbook downto 2 do begin XLApp.Workbooks1.Worksheetsindex.Delete;end; Sheet := XLApp.Workbooks1.Worksheets1; index := 1; if index 0 then Sh
12、eet := XLApp.Workbooks1.Worksheets.Add; Sheet.Name := ADOQuery1.Name;/Sheet.Columns.NumberFormatLocal := ; /设置单元格式为文本 Sheet.RangeRefToCell(1, 1), RefToCell(rowCount + 1, colCount).Value := getData(ADOQuery1); XLApp.Workbooks1.SaveAs(fileName); finally if not VarIsEmpty(XLApp) then begin XLApp.Quit;
13、XLAPP := Unassigned; Sheet := Unassigned; application.ProcessMessages; t2:= GetTickCount; label2.Caption := IntToStr( t2 - t1); end; end; end;7. 文件流方法文件流方法. var Form1: TForm1; arXlsBegin: array05 of Word = ($809, 8, 0, $10, 0, 0); arXlsEnd: array01 of Word = ($0A, 00); arXlsString: array05 of Word =
14、 ($204, 0, 0, 0, 0, 0); arXlsNumber: array04 of Word = ($203, 14, 0, 0, 0); arXlsInteger: array04 of Word = ($27E, 10, 0, 0, 0); arXlsBlank: array04 of Word = ($201, 6, 0, 0, $17); Procedure ExportExcelFile(FileName: string; bWriteTitle: Boolean; aDataSet: TDataSet); implementation $R *.dfm Procedur
15、e ExportExcelFile(FileName: string; bWriteTitle: Boolean; aDataSet: TDataSet);/*以下全部是定义*/ var i,j: integer; Col , row: word; ABookMark: TBookMark; aFileStream: TFileStream;procedure incColRow; /增加行列号 begin if Col = ADataSet.FieldCount - 1 then begin Inc(Row); Col :=0; end else Inc(Col); end;procedur
16、e WriteStringCell(AValue: string);/写字符串数据 var L: Word; begin L := Length(AValue); arXlsString1 := 8 + L; arXlsString2 := Row; arXlsString3 := Col; arXlsString5 := L; aFileStream.WriteBuffer(arXlsString, SizeOf (arXlsString); aFileStream.WriteBuffer(Pointer(AValue), L); IncColRow; end;procedure Write
