各位好。我是小白。从来没有用过delphi,但是有特别想做一个傻瓜式的数据库查询软件。用了delphi, 现在学了一天,能够执行查询了。但是,我现在需要的是将这些查询到数据导出到 EXCEL表中,我是小白,完全不懂。所以希望能详细点说,如果能够图文教程最好,谢谢大家。
这个我是已经做好的图,能够连接到数据库,并且点击查询还能够查询到数据。
现在唯一的问题就是,想实现 点击 导出到EXCEL的功能实现,把查询到数据直接导出到EXCEL表中。请问,如何实现呢?
我是小白,完全没有学过这个语言。 然后我在其他地方下载了一个uExportXls.pas 文件。但是却完全不知道怎么
用,在网上也找不到对应的视频,教程,所以只有跑这里来问问大家了。再次感谢!!
{ 背景:今天要把DataSet导入Excel,查询以前的贴子,一部分用Ole,速度太慢,
一部分用ADO连接到Excel文件,也很慢,一气之下把DBGrigEh的导出部分改了出来,
欢迎大家指教、改进。
功能:将数据集的数据导入Excel;
用法:With ExportXls.Create(TDataSet(ADOQuery1)) do
Try
Save2File(SaveDialog1.FileName, True);
finally
Free;
end;
作者:Caidao (核心代码来自Ehlib)
时间:2003-04-09
地点:汕头
}unit uExportXls;interfaceuses
DB, Classes;var
CXlsBof: array[0..5] of Word = ($809, 8, 0, $10, 0, 0);
CXlsEof: array[0..1] of Word = ($0A, 00);
CXlsLabel: array[0..5] of Word = ($204, 0, 0, 0, 0, 0);
CXlsNumber: array[0..4] of Word = ($203, 14, 0, 0, 0);
CXlsRk: array[0..4] of Word = ($27E, 10, 0, 0, 0);
CXlsBlank: array[0..4] of Word = ($201, 6, 0, 0, $17);type
TFldRec = record
Title: string;
Width: Integer;
end; ExportXls = class(TObject)
private
FCol: word;
FRow: word;
FDataSet: TDataSet;
Stream: TStream;
FWillWriteHead: boolean;
FBookMark: TBook;
procedure IncColRow;
procedure WriteBlankCell;
procedure WriteFloatCell(const AValue: Double);
procedure WriteIntegerCell(const AValue: Integer);
procedure WriteStringCell(const AValue: string);
procedure WritePrefix;
procedure WriteSuffix;
procedure WriteTitle;
procedure WriteDataCell; procedure Save2Stream(aStream: TStream);
public
procedure Save2File(FileName: string; WillWriteHead: Boolean);
constructor Create(aDataSet: TDataSet);
end;
function ExportToXLS(const FileName: string; DataSet: TDataSet): Boolean;
implementationuses SysUtils;function ExportToXLS(const FileName: string; DataSet: TDataSet): Boolean;
begin
Result := False;
with ExportXls.Create(DataSet) do try
Save2File(FileName, True);
Result := True;
finally
Free;
end;
end;constructor ExportXls.Create(aDataSet: TDataSet);
begin
inherited Create;
FDataSet := aDataSet;
end;procedure ExportXls.IncColRow;
begin
if FCol = FDataSet.FieldCount - 1 then begin
Inc(FRow);
FCol := 0;
end
else
Inc(FCol);
end;procedure ExportXls.WriteBlankCell;
begin
CXlsBlank[2] := FRow;
CXlsBlank[3] := FCol;
Stream.WriteBuffer(CXlsBlank, SizeOf(CXlsBlank));
IncColRow;
end;procedure ExportXls.WriteFloatCell(const AValue: Double);
begin
CXlsNumber[2] := FRow;
CXlsNumber[3] := FCol;
Stream.WriteBuffer(CXlsNumber, SizeOf(CXlsNumber));
Stream.WriteBuffer(AValue, 8);
IncColRow;
end;procedure ExportXls.WriteIntegerCell(const AValue: Integer);
var
V: Integer;
begin
CXlsRk[2] := FRow;
CXlsRk[3] := FCol;
Stream.WriteBuffer(CXlsRk, SizeOf(CXlsRk));
V := (AValue shl 2) or 2;
Stream.WriteBuffer(V, 4);
IncColRow;
end;procedure ExportXls.WriteStringCell(const AValue: string);
var
L: Word;
begin
L := Length(AValue);
CXlsLabel[1] := 8 + L;
CXlsLabel[2] := FRow;
CXlsLabel[3] := FCol;
CXlsLabel[5] := L;
Stream.WriteBuffer(CXlsLabel, SizeOf(CXlsLabel));
Stream.WriteBuffer(Pointer(AValue)^, L);
IncColRow;
end;procedure ExportXls.WritePrefix;
begin
Stream.WriteBuffer(CXlsBof, SizeOf(CXlsBof));
end;procedure ExportXls.WriteSuffix;
begin
Stream.WriteBuffer(CXlsEof, SizeOf(CXlsEof));
end;procedure ExportXls.WriteTitle;
var
n: word;
begin
for n := 0 to FDataSet.FieldCount - 1 do
WriteStringCell(FDataSet.Fields[n].DisplayLabel); //显示标签名
end;procedure ExportXls.WriteDataCell;
var
Idx: word;
begin
WritePrefix;
if FWillWriteHead then WriteTitle;
FDataSet.DisableControls;
FBookMark := FDataSet.GetBook;
FDataSet.First;
while not FDataSet.Eof do begin
for Idx := 0 to FDataSet.FieldCount - 1 do begin
if FDataSet.Fields[Idx].IsNull then
WriteBlankCell
else begin
case FDataSet.Fields[Idx].DataType of
ftSmallint, ftInteger, ftWord, ftAutoInc, ftBytes:
WriteIntegerCell(FDataSet.Fields[Idx].AsInteger);
ftFloat, ftCurrency, ftBCD:
WriteFloatCell(FDataSet.Fields[Idx].AsFloat);
else
if Assigned(FDataSet.Fields[Idx].OnGetText) then
WriteStringCell(FDataSet.Fields[Idx].Text)
else
WriteStringCell(FDataSet.Fields[Idx].AsString);
end;
end;
end;
FDataSet.Next;
end;
WriteSuffix;
if FDataSet.BookValid(FBookMark) then FDataSet.GotoBook(FBookMark);
FDataSet.EnableControls;
end;procedure ExportXls.Save2Stream(aStream: TStream);
begin
FCol := 0;
FRow := 0;
Stream := aStream;
WriteDataCell;
end;procedure ExportXls.Save2File(FileName: string; WillWriteHead: Boolean);
var
aFileStream: TFileStream;
begin
FWillWriteHead := WillWriteHead;
if FileExists(FileName) then DeleteFile(FileName);
aFileStream := TFileStream.Create(FileName, fmCreate);
try
Save2Stream(aFileStream);
finally
aFileStream.Free;
end;
end;end.
这个我是已经做好的图,能够连接到数据库,并且点击查询还能够查询到数据。
现在唯一的问题就是,想实现 点击 导出到EXCEL的功能实现,把查询到数据直接导出到EXCEL表中。请问,如何实现呢?
我是小白,完全没有学过这个语言。 然后我在其他地方下载了一个uExportXls.pas 文件。但是却完全不知道怎么
用,在网上也找不到对应的视频,教程,所以只有跑这里来问问大家了。再次感谢!!
{ 背景:今天要把DataSet导入Excel,查询以前的贴子,一部分用Ole,速度太慢,
一部分用ADO连接到Excel文件,也很慢,一气之下把DBGrigEh的导出部分改了出来,
欢迎大家指教、改进。
功能:将数据集的数据导入Excel;
用法:With ExportXls.Create(TDataSet(ADOQuery1)) do
Try
Save2File(SaveDialog1.FileName, True);
finally
Free;
end;
作者:Caidao (核心代码来自Ehlib)
时间:2003-04-09
地点:汕头
}unit uExportXls;interfaceuses
DB, Classes;var
CXlsBof: array[0..5] of Word = ($809, 8, 0, $10, 0, 0);
CXlsEof: array[0..1] of Word = ($0A, 00);
CXlsLabel: array[0..5] of Word = ($204, 0, 0, 0, 0, 0);
CXlsNumber: array[0..4] of Word = ($203, 14, 0, 0, 0);
CXlsRk: array[0..4] of Word = ($27E, 10, 0, 0, 0);
CXlsBlank: array[0..4] of Word = ($201, 6, 0, 0, $17);type
TFldRec = record
Title: string;
Width: Integer;
end; ExportXls = class(TObject)
private
FCol: word;
FRow: word;
FDataSet: TDataSet;
Stream: TStream;
FWillWriteHead: boolean;
FBookMark: TBook;
procedure IncColRow;
procedure WriteBlankCell;
procedure WriteFloatCell(const AValue: Double);
procedure WriteIntegerCell(const AValue: Integer);
procedure WriteStringCell(const AValue: string);
procedure WritePrefix;
procedure WriteSuffix;
procedure WriteTitle;
procedure WriteDataCell; procedure Save2Stream(aStream: TStream);
public
procedure Save2File(FileName: string; WillWriteHead: Boolean);
constructor Create(aDataSet: TDataSet);
end;
function ExportToXLS(const FileName: string; DataSet: TDataSet): Boolean;
implementationuses SysUtils;function ExportToXLS(const FileName: string; DataSet: TDataSet): Boolean;
begin
Result := False;
with ExportXls.Create(DataSet) do try
Save2File(FileName, True);
Result := True;
finally
Free;
end;
end;constructor ExportXls.Create(aDataSet: TDataSet);
begin
inherited Create;
FDataSet := aDataSet;
end;procedure ExportXls.IncColRow;
begin
if FCol = FDataSet.FieldCount - 1 then begin
Inc(FRow);
FCol := 0;
end
else
Inc(FCol);
end;procedure ExportXls.WriteBlankCell;
begin
CXlsBlank[2] := FRow;
CXlsBlank[3] := FCol;
Stream.WriteBuffer(CXlsBlank, SizeOf(CXlsBlank));
IncColRow;
end;procedure ExportXls.WriteFloatCell(const AValue: Double);
begin
CXlsNumber[2] := FRow;
CXlsNumber[3] := FCol;
Stream.WriteBuffer(CXlsNumber, SizeOf(CXlsNumber));
Stream.WriteBuffer(AValue, 8);
IncColRow;
end;procedure ExportXls.WriteIntegerCell(const AValue: Integer);
var
V: Integer;
begin
CXlsRk[2] := FRow;
CXlsRk[3] := FCol;
Stream.WriteBuffer(CXlsRk, SizeOf(CXlsRk));
V := (AValue shl 2) or 2;
Stream.WriteBuffer(V, 4);
IncColRow;
end;procedure ExportXls.WriteStringCell(const AValue: string);
var
L: Word;
begin
L := Length(AValue);
CXlsLabel[1] := 8 + L;
CXlsLabel[2] := FRow;
CXlsLabel[3] := FCol;
CXlsLabel[5] := L;
Stream.WriteBuffer(CXlsLabel, SizeOf(CXlsLabel));
Stream.WriteBuffer(Pointer(AValue)^, L);
IncColRow;
end;procedure ExportXls.WritePrefix;
begin
Stream.WriteBuffer(CXlsBof, SizeOf(CXlsBof));
end;procedure ExportXls.WriteSuffix;
begin
Stream.WriteBuffer(CXlsEof, SizeOf(CXlsEof));
end;procedure ExportXls.WriteTitle;
var
n: word;
begin
for n := 0 to FDataSet.FieldCount - 1 do
WriteStringCell(FDataSet.Fields[n].DisplayLabel); //显示标签名
end;procedure ExportXls.WriteDataCell;
var
Idx: word;
begin
WritePrefix;
if FWillWriteHead then WriteTitle;
FDataSet.DisableControls;
FBookMark := FDataSet.GetBook;
FDataSet.First;
while not FDataSet.Eof do begin
for Idx := 0 to FDataSet.FieldCount - 1 do begin
if FDataSet.Fields[Idx].IsNull then
WriteBlankCell
else begin
case FDataSet.Fields[Idx].DataType of
ftSmallint, ftInteger, ftWord, ftAutoInc, ftBytes:
WriteIntegerCell(FDataSet.Fields[Idx].AsInteger);
ftFloat, ftCurrency, ftBCD:
WriteFloatCell(FDataSet.Fields[Idx].AsFloat);
else
if Assigned(FDataSet.Fields[Idx].OnGetText) then
WriteStringCell(FDataSet.Fields[Idx].Text)
else
WriteStringCell(FDataSet.Fields[Idx].AsString);
end;
end;
end;
FDataSet.Next;
end;
WriteSuffix;
if FDataSet.BookValid(FBookMark) then FDataSet.GotoBook(FBookMark);
FDataSet.EnableControls;
end;procedure ExportXls.Save2Stream(aStream: TStream);
begin
FCol := 0;
FRow := 0;
Stream := aStream;
WriteDataCell;
end;procedure ExportXls.Save2File(FileName: string; WillWriteHead: Boolean);
var
aFileStream: TFileStream;
begin
FWillWriteHead := WillWriteHead;
if FileExists(FileName) then DeleteFile(FileName);
aFileStream := TFileStream.Create(FileName, fmCreate);
try
Save2Stream(aFileStream);
finally
aFileStream.Free;
end;
end;end.
dbgrideh,cxgrid等表格,支持导出到xls,效率要比自己写的高很多。
http://blog.csdn.net/zang141588761/article/details/52275948
var
ExcApp: OleVariant;
i,l: integer;
begin
ExcApp := CreateOleObject('Excel.Application');
ExcApp.Visible := True;
ExcApp.WorkBooks.Add;
DataSet.First;
l := 1;
DataSet.First;
while not DataSet.EOF do
begin
for i := 0 to DataSet.Fields.Count - 1 do
ExcApp.WorkBooks[1].Sheets[1].Cells[l,i + 1] :=
DataSet.Fields[i].DisplayText;
DataSet.Next;
l := l + 1;
end;
ExcApp.WorkBooks[1].SaveAs(AFilePath);
end;
参考这里:
http://blog.csdn.net/avan_lau/article/details/24470345
XLSReadWriteII4,配合dbgrideh,写个方法一劳永逸。procedure XLS_DBGridEh_EXCEL(Sender:TComponent;DBGrid:TDBGridEh;Title,FileName:string;
PageType,ShowZero,DoSum:integer;NoSum,NoCol:String;
percent:integer); // 通过XLS 生成Excel表
//Sender 主窗体
//DBGrid DBGridEh
//Title 标题
//FileName 生成文件名
//Pagetype 页面样式(0纵向,1横向)
//ShowZero 显示 0 (0不显示,1显示)
//DoSum 底栏统计汇总(0不,1可)
//NoSum 强制非汇总的列 格式:AAAA,BBBB,CCCC
//NoCol 强制非显示的列 同上
//percent 显示百分比DBGrid EXCEL
SaveDBGridEHTOExportFile(TdbgridehEXportasxls,dbgrideh1,'c:\查询结果.xls',true);