导出到Excel的7种方法.docx

1、目录导出到 Excel 的 7 种方法 11. delphi 快速导出 excel .12. 非第 3 方控件 33. .54. .105. 循环遍历数据，插入到 Excel 的 WookBook116. 使用 OLE 方法导入 127. 文件流方法 15导出到 Excel 的 7 种方法后面三种：使用 GetTickcount 函数计算时差，使用 TADOConnect， TADOQuery 查询数据。测试数据 city 表 3179 行 7 列记录 【 但是第七章种不支持 2007 格式 ,不知为什么 】第五种： 11294 11482 12121 11419 13322 11856第六种

2、： 1747 1840 1716 2137 1670 1856第七种： 265 250 234 234 218 234默认导出的地址是 D 盘根目录1. 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 :t

3、savedialog;beginResult := true;tryecel:=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 thenbeg

4、inif 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;fi

5、lename:=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(查询结果); /加入标题s:=; /加入字段名for y := 0 to adoquery.fieldCount - 1 dobegins:=s+adoQuery.Fields.Fieldsy.Fiel

6、dName+#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;

7、end;finallytsList.Free;end;aSheet.Paste;MessageBox(Application.Handle,数据导出完毕！,系统提示,MB_ICONINFORMATION or MB_OK);tryif copy(FileName,length(FileName)-3,4) 1 thenResult := Chr(Ord(A) + ACount - 1) + Chr(Ord(A) + APos - 1) + IntToStr(RowID);end;function getData(ds: TDataSet): OleVariant;varData: OLEVar

8、iant;i,j : Integer;beginrowCount := 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 dobeginif not ds.Fieldsj.Visible thencontinue;Datai,j + 1 := ds.Fieldsj.Displ

9、ayLabel;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(

10、)+.xlsx;label2.Caption := 0;t1:= GetTickCount;/开始计时if FileExists(fileName) thenif application.messagebox(该文件已经存在，要覆盖吗？,询问,mb_yesno+mb_iconquestion)=idyes thenDeleteFile(FileName) 删除旧文件elseexit;xlApp := CreateOleObject(Excel.Application);tryXLApp.Visible := False;XLApp.DisplayAlerts := False;XLApp.Wo

11、rkbooks.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 := ;

12、/设置单元格式为文本Sheet.RangeRefToCell(1, 1), RefToCell(rowCount + 1, colCount).Value := getData(ADOQuery1);XLApp.Workbooks1.SaveAs(fileName); finallyif not VarIsEmpty(XLApp) thenbeginXLApp.Quit;XLAPP := Unassigned;Sheet := Unassigned;application.ProcessMessages;t2:= GetTickCount;label2.Caption := IntToStr(

13、 t2 - t1);end;end;end;7. 文件流方法.varForm1: TForm1;arXlsBegin: array05 of Word = ($809, 8, 0, $10, 0, 0);arXlsEnd: array01 of Word = ($0A, 00);arXlsString: array05 of Word = ($204, 0, 0, 0, 0, 0);arXlsNumber: array04 of Word = ($203, 14, 0, 0, 0);arXlsInteger: array04 of Word = ($27E, 10, 0, 0, 0);arXl

14、sBlank: array04 of Word = ($201, 6, 0, 0, $17);Procedure ExportExcelFile(FileName: string; bWriteTitle: Boolean; aDataSet: TDataSet);implementation$R *.dfmProcedure ExportExcelFile(FileName: string; bWriteTitle: Boolean; aDataSet: TDataSet);/*以下全部是定义*/vari,j: integer;Col , row: word;ABookMark: TBook

15、Mark;aFileStream: TFileStream;procedure incColRow; /增加行列号beginif Col = ADataSet.FieldCount - 1 thenbeginInc(Row);Col :=0;endelseInc(Col);end;procedure WriteStringCell(AValue: string);/写字符串数据varL: Word;beginL := Length(AValue);arXlsString1 := 8 + L;arXlsString2 := Row;arXlsString3 := Col;arXlsString5

16、 := L;aFileStream.WriteBuffer(arXlsString, SizeOf (arXlsString);aFileStream.WriteBuffer(Pointer(AValue), L);IncColRow;end;procedure WriteIntegerCell(AValue: integer);/写整数varV: Integer;beginarXlsInteger2 := Row;arXlsInteger3 := Col;aFileStream.WriteBuffer(arXlsInteger, SizeOf(arXlsInteger);V := (AVal

17、ue shl 2) or 2;aFileStream.WriteBuffer(V, 4);IncColRow;end;procedure WriteFloatCell(AValue: double );/写浮点数beginarXlsNumber2 := Row;arXlsNumber3 := Col;aFileStream.WriteBuffer(arXlsNumber, SizeOf(arXlsNumber);aFileStream.WriteBuffer(AValue, 8);IncColRow;end;/*以上全部是定义*/beginif FileExists(FileName) the

18、n if application.messagebox(该文件已经存在，要覆盖吗？,询问,mb_yesno+mb_iconquestion)=idyes thenDeleteFile(FileName) 删除旧文件elseexit;aFileStream := TFileStream.Create(FileName, fmCreate);Try /写文件头 aFileStream.WriteBuffer(arXlsBegin, SizeOf(arXlsBegin); /写列头 Col := 0; Row := 0;if bWriteTitle thenbeginfor i := 0 to aD

19、ataSet.FieldCount - 1 doWriteStringCell(aDataSet.Fieldsi.FieldName);end; /写数据集中的数据 aDataSet.DisableControls;/ABookMark := aDataSet.GetBookmark;aDataSet.First ;while not aDataSet.Eof dobeginfor i := 0 to aDataSet.FieldCount - 1 docase ADataSet.Fieldsi.DataType offtSmallint, ftInteger, ftWord, ftAutoI

20、nc, ftBytes:WriteIntegerCell(aDataSet.Fieldsi.AsInteger);ftFloat, ftCurrency, ftBCD:WriteFloatCell(aDataSet.Fieldsi.AsFloat)elseWriteStringCell(aDataSet.Fieldsi.AsString);end;aDataSet.Next;Application.ProcessMessages;end;/写文件尾 AFileStream.WriteBuffer(arXlsEnd, SizeOf(arXlsEnd);/if ADataSet.BookmarkV

21、alid(ABookMark) then aDataSet.GotoBookmark(ABookMark);FinallyAFileStream.Free;ADataSet.EnableControls;end;end;procedure TForm1.button3Click(Sender: TObject);vart1,t2: Int64;beginlabel3.Caption := 0;t1:= GetTickCount;ExportExcelFile(D:第 7 种方法 + FormatDateTime(yyyy-mm-dd,Now()+.xlsx;,true,ADOQuery1);t2:= GetTickCount;label3.Caption:= IntToStr(t2 - t1);end;end.