导出数据 ldb blog ctrl 出错 合并 lint sage
Sheet.Name := qry1.Name;
Sheet := XLApp.Workbooks[1].Worksheets.Add;
if index <> 0 then
index := 1;
Sheet := XLApp.Workbooks[1].Worksheets[1];
end;
XLApp.Workbooks[1].Worksheets[index].Delete;
begin
for index := XLApp.SheetsInNewWorkbook downto 2 do
// 删除多余的 worksheet
XLApp.Workbooks.Add;
XLApp.DisplayAlerts := False;
XLApp.Visible := False;
try
xlApp := CreateOleObject(‘Excel.Application‘);
DeleteFile(fileName);
if FileExists(fileName) then
t1:= GetTickCount;//开始计时
lbl1.Caption := ‘0‘;
fileName := ‘d:\数据.xls‘;
begin
end;
result := Data;
end;
ds.EnableControls;
finally
end;
Application.ProcessMessages;
ds.Next;
Inc(i);
end;
Data[i,j + 1] := ds.Fields[j].AsString;
begin
for j := 0 to colCount - 1 do
begin
while not ds.Eof do
ds.First;
try
ds.DisableControls;
Inc(i);
end;
Data[i,j + 1] := ds.Fields[j].DisplayLabel;
continue;
if not ds.Fields[j].Visible then
begin
for j := 0 to colCount - 1 do
i := 1;
Data := VarArrayCreate([1, rowCount + 1, 1, colCount], varVariant); //1,rowCount 表示第一维数组的上下标,1,colCount表示第二维数组的上下标
colCount := ds.FieldCount;
rowCount := ds.RecordCount;
begin
i,j : Integer;
Data: OLEVariant;
var
function getData(ds: TDataSet): OleVariant;
end;
Result := Chr(Ord(‘A‘) + ACount - 1) + Chr(Ord(‘A‘) + APos - 1) + IntToStr(RowID);
if ACount > 1 then
Result := ‘A‘ + Chr(Ord(‘A‘) + APos - 1) + IntToStr(RowID);
if ACount = 1 then
Result := Chr(Ord(‘A‘) + ColID - 1) + IntToStr(RowID);
if ACount = 0 then
end;
APos := 26;
ACount := ACount - 1;
begin
if APos = 0 then
APos := ColID mod 26;
ACount := ColID div 26;
begin
ACount, APos: Integer;
var
function RefToCell(RowID, ColID: Integer): string;
t1,t2: Int64;
rowCount, Colcount, index: Integer;
xlApp, Sheet: OleVariant;
fileName: string;
var
先讲TDateSet中的数据保存为二维OLEVariant数组中,再保存到Excel Sheet中 ///使用OLE方式保存procedure TForm1.btn_OleVariantClick(Sender: TObject);
方法六:使用OLE方法导入。
*********************************************************************************************************
---------------------------------------------------------------------------------------------------------
end;
lbl2.Caption := IntToStr(t2 - t1);
Eclapp:= Unassigned;
Eclapp.Quit;
eclapp.Workbooks[1].SaveAs(filename);
eclapp.visible := false;
t2:= GetTickCount;
qry1.EnableControls;
end;
application.ProcessMessages;
qry1.Next;
inc(n);
//为了简单,只添加了4个栏位
eclapp.cells[n,4] := qry1.Fields[3].AsString;
eclapp.cells[n,3] := qry1.Fields[2].AsString;
eclapp.cells[n,2] := qry1.Fields[1].AsString;
eclapp.cells[n,1] := qry1.Fields[0].AsString;
begin
while not qry1.Eof do
n:=2;
qry1.First;
qry1.DisableControls;
t1:= GetTickCount;
DeleteFile(fileName);
if FileExists(fileName) then
lbl2.Caption := ‘0‘;
filename :=‘d:\数据1.xls‘;
Eclapp.Visible:= False;
Eclapp.WorkBooks.Add;
Eclapp := CreateOleObject(‘Excel.Application‘);
begin
t1,t2: Int64;
filename: string;
n:integer;
Eclapp:variant;
var
procedure TForm1.btn_WhileClick(Sender: TObject);
//使用ADO循环方式保存
在下面代码中没有仔细注意语法(比如没有使用try..finally结构体),如果需要使用,请注意:
使用TADOQuery + Varaint方法,循环遍历数据集中数据,直接插入到Excel的WookBook单元。这是初学者最易懂和易接受的方法。
方法五:
使用TADOConnect,TADOQuery查询数据。
闲来无事,跑到网上搜集了几种导出DataSet至Excel的几种方法。另外使用GetTickcount函数计算时差,以便比较。(本来使用Timer控件,但是Timer不适合做高精度时间计算)
delphi导出数据至Excel的三种方法及比较
**************************************************************************************************
--------------------------------------------------------------------------------------------------
end;
end;
end;
end;
ExWrst1.Free;
ExWrbk1.Free;
ExApp1.Free;
ExApp1.Quit;
ExApp1.Disconnect;
finally
end;
abort;
Showmessage(‘導出失敗!‘);
except
Showmessage(‘數據已成功導出!‘);
//ExWrst.SaveAs(formatdatetime(‘YYYYMMDDHHMMSS‘,now())+reportname);;
ExWrst1.SaveAs(wpath+reportname);
end;
Main_FM.ADOQuery_TEMP.Next;
end;
ExWrst1.Cells.Item[i,j+1] := Main_FM.ADOQuery_TEMP.Fields[j].Value;
begin
for j := 0 to Main_FM.ADOQuery_TEMP.FieldCount-1 do
begin
for i := 2 to Main_FM.ADOQuery_TEMP.RecordCount+1 do
end;
//
ExWrst1.Cells.Item[1,j+1] := Main_FM.ADOQuery_TEMP.Fields[j].DisplayName;
begin
for j := 0 to Main_FM.ADOQuery_TEMP.FieldCount-1 do
Main_FM.ADOQuery_TEMP.First;
ExWrst1.ConnectTo(ExWrbk1.Worksheets[1] as _worksheet);
ExWrbk1.ConnectTo(ExApp1.Workbooks[1]);
ExApp1.Workbooks.Add(EmptyParam,0);
try
try
end;
Abort;
Showmessage(‘電腦沒裝Excel!無法導出!‘);
except
ExApp1.Connect;
ExWrst1 := TExcelWorksheet.Create(application);
ExWrbk1 := TExcelWorkbook.Create(application);
ExApp1 := TExcelApplication.Create(application);
try
//showmessage(wpath);
wpath := ExtractFilePath(Main_FM.savedialog1.FileName);
//reportname := formatdatetime(‘YYYYMMDDHHMMSS‘,now())+‘‘;
reportname := formatdatetime(‘YYYYMMDDHHMMSS‘,now())+ExtractFileName(Main_FM.savedialog1.FileName);
//savedialog1.FileName := formatdatetime(‘YYYYMMDDHHMMSS‘,now())+‘md_orderqc_list.xls‘;
begin
if Main_FM.savedialog1.Execute then
Main_FM.SaveDialog1.FileName := ‘qcreport‘;
begin
else
end
Exit;
Showmessage(‘沒有可導出的資料!‘);
begin
if Main_FM.ADOQuery_TEMP.IsEmpty then
begin
ExWrst1 : TExcelWorksheet;
ExWrbk1 : TExcelWorkbook;
ExApp1 : TExcelApplication;
reportname, wpath : string;
i,j : integer;
var
procedure TFIND_FM.Button1Click(Sender: TObject);
四;
ADODB, DB, DBGrids, clipbrd;
CheckLst, excel97, ExcelXP, OleServer, comobj, excel2000, mmsystem,
Dialogs, ExtCtrls, Mask, ComCtrls, StdCtrls, Buttons, Grids, ValEdit, IdBaseComponent,
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
uses
********************************************************************************************************************
--------------------------------------------------------------------------------------------------------------------
end;
end;
shellexecute(0,‘open‘,PChar(ExtractFileName(TemFileName)),nil,PChar(ExtractFilePath(TemFileName)),SW_Show);
FExcel := unassigned;
FExcel.quit; //关闭Excel
//FExcel.visible:=true;
MessageBox(Handle,‘导出成功‘,‘提示‘,MB_OK);
Application.ProcessMessages;
FExcel.workbooks[1].close; //关闭工作表
FExcel.WorkBooks[1].saveas(TemFileName);//保存文件
Screen.Cursor:=CrDefault;
Application.ProcessMessages;
range.borders.linestyle:=1;//华线
range:=Temsheet.Range[Temsheet.cells[7,1],Temsheet.cells[TemInt,10]];//选定表格
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[TemInt,4],Temsheet.cells[TemInt,10]];//选定表格
Temsheet.Cells[TemInt,3]:=Trim(Edit3.Text);
Temsheet.Cells[TemInt,1]:=‘总利润:‘;
Range.Characters.Font.FontStyle :=‘加粗‘;
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[TemInt,1],Temsheet.cells[TemInt,2]];//选定表格
TemInt:=TemInt+1;
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[TemInt,4],Temsheet.cells[TemInt,10]];//选定表格
Temsheet.Cells[TemInt,3]:=Trim(Edit2.Text);
Temsheet.Cells[TemInt,1]:=‘出库总额:‘;
Range.Characters.Font.FontStyle :=‘加粗‘;
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[TemInt,1],Temsheet.cells[TemInt,2]];//选定表格
TemInt:=TemInt+1;
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[TemInt,4],Temsheet.cells[TemInt,10]];//选定表格
Temsheet.Cells[TemInt,3]:=Trim(Edit1.Text);
Temsheet.Cells[TemInt,1]:=‘入库总额:‘;
Range.Characters.Font.FontStyle :=‘加粗‘;
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[TemInt,1],Temsheet.cells[TemInt,2]];//选定表格
TemInt:=TemInt+1;
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[TemInt,1],Temsheet.cells[TemInt,10]];//选定表格
TemInt:=TemInt+1;
TemInt:=TemInt+ DBGrid2.DataSource.DataSet.RecordCount;
end;
j:=j+1;
DBGrid2.DataSource.DataSet.Next;
end;
range.borders.linestyle:=1;//华线
range:=Temsheet.Range[Temsheet.cells[TemInt+j,i+2],Temsheet.cells[TemInt+j,i+2]];//选定表格
Temsheet.Cells[TemInt+j,i+2].Value:=DBGrid2.Fields[i].AsString;
begin
for i:=0 to DBGrid2.Columns.Count - 1 do
range.borders.linestyle:=1;//华线
range:=Temsheet.Range[Temsheet.cells[TemInt+j,1],Temsheet.cells[TemInt+j,1]];//选定表格
Temsheet.Cells[TemInt+j,1].HorizontalAlignment:=-4108; //字居中
Temsheet.Cells[TemInt+j,1].Value:=j+1;
begin
while not DBGrid2.DataSource.DataSet.Eof do
DBGrid2.DataSource.DataSet.First;
j:=0;
//////////////////////////////////////////////
TemInt:=TemInt+1;
end;
range.borders.linestyle:=1;//华线
range:=Temsheet.Range[Temsheet.cells[TemInt,i+2],Temsheet.cells[TemInt,i+2]];//选定表格
Temsheet.Cells[TemInt,i+2].Interior.Color:=clGray; //单元格背景色
Temsheet.Cells[TemInt,i+2].HorizontalAlignment:=-4108; //字居中
Temsheet.Cells[TemInt,i+2]:=DBGrid2.Columns[i].Title.Caption;
begin
for i:=0 to DBGrid2.Columns.Count - 1 do
range.borders.linestyle:=1;//华线
range:=Temsheet.Range[Temsheet.cells[TemInt,1],Temsheet.cells[TemInt,1]];//选定表格
Temsheet.Cells[TemInt,1].Interior.Color:=clGray; //单元格背景色
Temsheet.Cells[TemInt,1].HorizontalAlignment:=-4108; //字居中
Temsheet.Cells[TemInt,1]:=‘序号‘;
TemInt:=TemInt+1;
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[TemInt,3],Temsheet.cells[TemInt,10]];//选定表格
Temsheet.Cells[TemInt,1]:=‘出库信息:‘;
Range.Characters.Font.FontStyle :=‘加粗‘;
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[TemInt,1],Temsheet.cells[TemInt,2]];//选定表格
TemInt:=TemInt+1;
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[TemInt,1],Temsheet.cells[TemInt,10]];//选定表格
TemInt:=9+ DBGrid1.DataSource.DataSet.RecordCount;
end;
j:=j+1;
DBGrid1.DataSource.DataSet.Next;
end;
range.borders.linestyle:=1;//华线
range:=Temsheet.Range[Temsheet.cells[9+j,i+2],Temsheet.cells[9+j,i+2]];//选定表格
Temsheet.Cells[9+j,i+2].Value:=DBGrid1.Fields[i].AsString;
begin
for i:=0 to DBGrid1.Columns.Count - 1 do
range.borders.linestyle:=1;//华线
range:=Temsheet.Range[Temsheet.cells[9+j,1],Temsheet.cells[9+j,1]];//选定表格
Temsheet.Cells[9+j,1].HorizontalAlignment:=-4108; //字居中
Temsheet.Cells[9+j,1].Value:=j+1;
begin
while not DBGrid1.DataSource.DataSet.Eof do
DBGrid1.DataSource.DataSet.First;
j:=0;
//////////////////////////////////////////////
end;
range.borders.linestyle:=1;//华线
range:=Temsheet.Range[Temsheet.cells[8,i+2],Temsheet.cells[8,i+2]];//选定表格
Temsheet.Cells[8,i+2].Interior.Color:=clGray; //单元格背景色
Temsheet.Cells[8,i+2].HorizontalAlignment:=-4108; //字居中
Temsheet.Cells[8,i+2]:=DBGrid1.Columns[i].Title.Caption;
begin
for i:=0 to DBGrid1.Columns.Count - 1 do
range.borders.linestyle:=1;//华线
range:=Temsheet.Range[Temsheet.cells[8,1],Temsheet.cells[8,1]];//选定表格
Temsheet.Cells[8,1].Interior.Color:=clGray; //单元格背景色
Temsheet.Cells[8,1].HorizontalAlignment:=-4108; //字居中
Temsheet.Cells[8,1]:=‘序号‘;
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[7,3],Temsheet.cells[7,10]];//选定表格
Temsheet.Cells[7,1]:=‘入库信息:‘;
Range.Characters.Font.FontStyle :=‘加粗‘;
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[7,1],Temsheet.cells[7,2]];//选定表格
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[6,1],Temsheet.cells[6,10]];//选定表格
Temsheet.Cells[5,6]:=ComFax;
Temsheet.Cells[5,5]:=‘传真:‘;
Temsheet.Cells[4,6]:=ComPhone;
Temsheet.Cells[4,5]:=‘电话:‘;
Temsheet.Cells[3,5]:=‘联系人:‘;
Temsheet.Cells[2,5]:=ComName;
Temsheet.Cells[2,5].HorizontalAlignment:=-4108; //字居中
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[2,5],Temsheet.cells[2,6]];//选定表格
Temsheet.Cells[4,3]:=ComEName;
Temsheet.Cells[4,3].HorizontalAlignment:=-4108; //字居中
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[4,3],Temsheet.cells[4,4]];//选定表格
Temsheet.Cells[2,3]:=ComSName;
Temsheet.Cells[2,3].HorizontalAlignment:=-4108; //字居中
Range.Characters.Font.FontStyle :=‘加粗‘;
range.merge;
range.select;
range:=Temsheet.Range[Temsheet.cells[2,3],Temsheet.cells[3,4]];//选定表格
FPicture:=null;
FPicture.height:=50;
FPicture.width:=50;
FPicture.Top:=5;
FPicture.Left:=20;
FPicture:=Temsheet.Pictures.Insert(tmpstr);
tmpstr:=ExtractFilePath(ParamStr(0))+‘tem.jpg‘; //添加图片
range.merge; //合并单元格
range.select;
range:=Temsheet.Range[Temsheet.cells[1,1],Temsheet.cells[5,2]];//选定表格
Temsheet.Columns[10].ColumnWidth:=15;
Temsheet.Columns[9].ColumnWidth:=20;
Temsheet.Columns[8].ColumnWidth:=10;
Temsheet.Columns[7].ColumnWidth:=10;
Temsheet.Columns[6].ColumnWidth:=10;
Temsheet.Columns[5].ColumnWidth:=10;
Temsheet.Columns[4].ColumnWidth:=10;
Temsheet.Columns[3].ColumnWidth:=16;
Temsheet.Columns[2].ColumnWidth:=10;
Temsheet.Columns[1].ColumnWidth:=4;//设置列宽度
Temsheet.Select;
Temsheet.Name:=‘利润统计‘;
Temsheet:=FWorkBook.Worksheets.Add;
FWorkBook:=FExcel.WorkBooks.Add(-4167); //新的工作表
FExcel:= CreateoleObject(‘excel.Application‘);
TemInt:=0;
Screen.Cursor:=CrHourGlass;
TemFileName:=SaveDialog1.FileName+‘.xls‘;
begin
if SaveDialog1.Execute then
SaveDialog1.Filter:=‘.xls‘;
begin
TemFileName:String;
i,j,TemInt:integer;
range:variant;//范围
tmpstr:String;
FPicture:OleVariant;//图片
Temsheet:OleVariant; //工作薄
FWorkBook :OleVariant; //工作表
FExcel:OleVariant; //excel应用程序
Var
ADODB, DB, DBGrids, clipbrd;
CheckLst, excel97, ExcelXP, OleServer, ComObj, excel2000, mmsystem, ShellAPI,
Dialogs, ExtCtrls, Mask, ComCtrls, StdCtrls, Buttons, Grids, ValEdit, IdBaseComponent,
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
uses
delphi导出EXCEL
三;
****************************************************************************************************************************************
-----------------------------------------------------------------------------------------------------------------------------------------------
end;
MessageBox(GetActiveWindow(), ‘EXCEL数据导出成功!‘, ‘提示信息‘, MB_OK +MB_ICONWARNING);
end;
Application.MessageBox(‘导入数据出错!请检查文件的格式是否正确!‘, ‘提示信息‘, MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
except
Excelid.Quit;
Excelid.worksheets[1].Range[s].Borders.LineStyle := 1;
Excelid.worksheets[1].Range[s].Font.size := 9;
Excelid.worksheets[1].Range[s].Font.Name := ‘宋体‘;
s := ‘A2:f‘+ IntToStr(k+2);
end;
Adoquery1.Next;
Inc(h);
Excelid.WorkSheets[1].Cells[h,3].Value := Adoquery1.FieldByName(‘Fdept_name‘).AsString;
Excelid.WorkSheets[1].Cells[h,2].Value := Adoquery1.FieldByName(‘Ffdept_id‘).AsString;
begin Excelid.WorkSheets[1].Cells[h,1].Value := Adoquery1.FieldByName(‘Fdept_id‘).AsString;
while not ADOQuery1.Eof do
ADOQuery1.First;
h:=3;
Excelid.worksheets[1].Range[‘A2:c2‘].VerticalAlignment := $FFFFEFF4;
Excelid.worksheets[1].Range[‘A2:c2‘].HorizontalAlignment := $FFFFEFF4;
Excelid.worksheets[1].Range[‘A2:c2‘].Font.Size := 9;
Excelid.worksheets[1].range[‘A1:c2‘].font.bold:=true;
Excelid.worksheets[1].Range[‘A1:c1‘].Font.Size := 9;
Excelid.worksheets[1].Range[‘A1:c1‘].Font.Name := ‘宋体‘;
Excelid.WorkSheets[1].Cells[2,3].Value := ‘组别名称‘;
Excelid.WorkSheets[1].Cells[2,2].Value := ‘公司编号‘;
Excelid.WorkSheets[1].Cells[2,1].Value := ‘组别编号‘;
Excelid.worksheets[1].Range[‘a1:a1‘].VerticalAlignment := $FFFFEFF4;
Excelid.worksheets[1].Range[‘a1:a1‘].HorizontalAlignment := $FFFFEFF4;
Excelid.WorkSheets[1].Cells[1,1].Value :=‘部门编码表‘ ;
Excelid.worksheets[1].range[‘A1:c1‘].Merge(True);
Excelid.WorkBooks.Add;
Excelid.Visible := True;
k:=ADOQuery1.RecordCount;
ADOQuery1.Open;
ADOQuery1.SQL.Add(‘select * from jj_department‘);
ADOQuery1.SQL.Clear;
ADOQuery1.Close;
try
end;
Exit;
Application.MessageBox(‘Excel没有安装!‘, ‘提示信息‘, MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
except
Excelid := CreateOLEObject(‘Excel.Application‘);
try
begin
s: string;
Excelid: OleVariant;
var h,k:integer;
procedure TForm1.Button1Click(Sender: TObject);
首先在Uses处加上ComObj
delphi如何导出EXCEL,代码。非第3方控件
二;
*************************************************************************************************
-------------------------------------------------------------------------------------------------
ToExcel(‘D:\a.xsl‘,QueryToExcel);//路径可以自定义
调用:
end;
Excel := UnAssigned;
Excel.Quit;
Excel.Visible := false; //true会自动打开已经保存的excel
end;
exit;
screen.cursor:=crDefault;
Excel.Quit;
except
Excel.ActiveWorkbook.SaveAs(FileName, xlNormal, ‘‘, ‘‘, False, False);
FileName:=FileName+‘.xls‘;
if copy(FileName,length(FileName)-3,4)<>‘.xls‘ then
try
MessageBox(Application.Handle,‘数据导出完毕!‘,‘系统提示‘,MB_ICONINFORMATION or MB_OK);
aSheet.Paste;
end;
tsList.Free;
finally
end;
result:=false;
except
Clipboard.AsText:=tsList.Text;
end;
ADOQuery.next;
tsList.Add(s);
end;
Application.ProcessMessages;
s:=s+ADOQuery.Fields[y].AsString+#9;
begin
for y:=0 to ADOQuery.FieldCount-1 do
s:=‘‘;
begin
While Not ADOQuery.Eof do
ADOQuery.First;
try
try
tsList.Add(s);
end;
Application.ProcessMessages;
s:=s+adoQuery.Fields.Fields[y].FieldName+#9 ;
begin
for y := 0 to adoquery.fieldCount - 1 do
s:=‘‘; //加入字段名
//tsList.Add(‘查询结果‘); //加入标题
tsList:=TStringList.Create;
aSheet:=excel.Worksheets.Item[1];
end;
exit;
//screen.cursor:=crDefault;
Excel.Quit;
result:=true;
begin
if filename=‘‘ then
savedialog.free;
end;
filename:=savedialog.FileName;
end;
Exit;
screen.cursor:=crDefault;
savedialog.free;
Excel.Quit;
except
end;
Exit;
//screen.cursor:=crDefault;
savedialog.free;
Excel.Quit;
begin
else
DeleteFile(PChar(savedialog.FileName))
if application.messagebox(‘该文件已经存在,要覆盖吗?‘,‘询问‘,mb_yesno+mb_iconquestion)=idyes then
try
if FileExists(savedialog.FileName) then
begin
if savedialog.Execute then
savedialog.Filter:=‘Excel文件(*.xls)|*.xls‘;
savedialog.FileName:=sfilename; //存入文件
savedialog:=tsavedialog.Create(nil);
end;
exit;
showmessage(‘无法调用Excel!‘);
//screen.cursor:=crDefault;
except
excel.workbooks.add;
excel:=CreateOleObject(‘Excel.Application‘);
try
Result := true;
begin
savedialog :tsavedialog;
excel :OleVariant;
aSheet :Variant;
s,filename :string;
tsList : TStringList;
y : integer;
var
xlNormal=-4143;
const
function ToExcel(sfilename:string; ADOQuery:TADOQuery):boolean;
uses ComObj,clipbrd;
delphi 快速导出excel
一;
//Sheet.Columns.NumberFormatLocal :=[email protected]
; //设置单元格式为文本end;
lbl3.Caption:= IntToStr(t2 - t1);
t2:= GetTickCount;
ExportExcelFile(‘d:\数据2.xls‘,true,qry1);
t1:= GetTickCount;
lbl3.Caption := ‘0‘;
begin
t1,t2: Int64;
var
procedure TForm1.btn_FileStreamClick(Sender: TObject);
//调用:
end;
end;
ADataSet.EnableControls;
AFileStream.Free;
Finally
//if ADataSet.BookmarkValid(ABookMark) then aDataSet.GotoBookmark(ABookMark);
AFileStream.WriteBuffer(arXlsEnd, SizeOf(arXlsEnd));
//写文件尾
end;
Application.ProcessMessages;
aDataSet.Next;
end;
WriteStringCell(aDataSet.Fields[i].AsString);
else
WriteFloatCell(aDataSet.Fields[i].AsFloat)
ftFloat, ftCurrency, ftBCD:
WriteIntegerCell(aDataSet.Fields[i].AsInteger);
ftSmallint, ftInteger, ftWord, ftAutoInc, ftBytes:
case ADataSet.Fields[i].DataType of
for i := 0 to aDataSet.FieldCount - 1 do
begin
while not aDataSet.Eof do
aDataSet.First ;
//ABookMark := aDataSet.GetBookmark;
aDataSet.DisableControls;
end; //写数据集中的数据
WriteStringCell(aDataSet.Fields[i].FieldName);
for i := 0 to aDataSet.FieldCount - 1 do
begin
if bWriteTitle then
Col := 0; Row := 0;
aFileStream.WriteBuffer(arXlsBegin, SizeOf(arXlsBegin)); //写列头
Try //写文件头
aFileStream := TFileStream.Create(FileName, fmCreate);
if FileExists(FileName) then DeleteFile(FileName); //文件存在,先删除
begin
//......
//......
aFileStream: TFileStream;
ABookMark: TBookMark;
Col , row: word;
i,j: integer;
var
Procedure ExportExcelFile(FileName: string; bWriteTitle: Boolean; aDataSet: TDataSet);
end;
IncColRow;
aFileStream.WriteBuffer(AValue, 8);
aFileStream.WriteBuffer(arXlsNumber, SizeOf(arXlsNumber));
arXlsNumber[3] := Col;
arXlsNumber[2] := Row;
begin
procedure WriteFloatCell(AValue: double );//写浮点数
end;
IncColRow;
aFileStream.WriteBuffer(V, 4);
V := (AValue shl 2) or 2;
aFileStream.WriteBuffer(arXlsInteger, SizeOf(arXlsInteger));
arXlsInteger[3] := Col;
arXlsInteger[2] := Row;
begin
V: Integer;
var
procedure WriteIntegerCell(AValue: integer);//写整数
end;
IncColRow;
aFileStream.WriteBuffer(Pointer(AValue)^, L);
aFileStream.WriteBuffer(arXlsString, SizeOf (arXlsString));
arXlsString[5] := L;
arXlsString[3] := Col;
arXlsString[2] := Row;
arXlsString[1] := 8 + L;
L := Length(AValue);
begin
L: Word;
var
procedure WriteStringCell(AValue: string);//写字符串数据
end;
Inc(Col);
else
end
Col :=0;
Inc(Row);
begin
if Col = ADataSet.FieldCount - 1 then
begin
procedure incColRow; //增加行列号
//使用文件流
{$R *.dfm}
implementation
Procedure ExportExcelFile(FileName: string; bWriteTitle: Boolean; aDataSet: TDataSet);
arXlsBlank: array[0..4] of Word = ($201, 6, 0, 0, $17);
arXlsInteger: array[0..4] of Word = ($27E, 10, 0, 0, 0);
arXlsNumber: array[0..4] of Word = ($203, 14, 0, 0, 0);
arXlsString: array[0..5] of Word = ($204, 0, 0, 0, 0, 0);
arXlsEnd: array[0..1] of Word = ($0A, 00);
arXlsBegin: array[0..5] of Word = ($809, 8, 0, $10, 0, 0);
Form1: TForm1;
var
.....
方法七:现在最流行的文件流方法
*******************************************************************************************************
-------------------------------------------------------------------------------------------------------
end;
end;
end;
lbl1.Caption := IntToStr( t2 - t1);
t2:= GetTickCount;
application.ProcessMessages;
Sheet := Unassigned;
XLAPP := Unassigned;
XLApp.Quit;
begin
if not VarIsEmpty(XLApp) then
finally
XLApp.Workbooks[1].SaveAs(fileName);
Sheet.Range[RefToCell(1, 1), RefToCell(rowCount + 1, colCount)].Value := getData(qry1);
http://blog.csdn.net/zang141588761/article/details/52275948
Delphi 导出数据至Excel的7种方法
来源: http://www.bubuko.com/infodetail-2025608.html