将数据集的数据导出Excel

{   背景：今天要把DataSet导入Excel，查询以前的贴子，一部分用Ole，速度太慢，
         一部分用ADO连接到Excel文件，也很慢，一气之下把DBGrigEh的导出部分改了出来，
         欢迎大家指教、改进。
   功能：将数据集的数据导入Excel;
   用法：With TDS2Excel.Create(TDataSet(ADOQuery1)) do
         Try
           Save2File(SaveDialog1.FileName, True);
         finally
           Free;
         end;
   作者：Caidao (核心代码来自Ehlib)
   时间：2003-04-09
   地点：汕头
}    


unit UntObject;

interface

Uses
 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
 TDS2Excel = Class(TObject)
 Private
   FCol: word;
   FRow: word;
   FDataSet: TDataSet;
   Stream: TStream;
   FWillWriteHead: boolean;
   FBookMark: TBookmark;
   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;

implementation

uses SysUtils;

Constructor TDS2Excel.Create(aDataSet: TDataSet);
begin
 inherited Create;
 FDataSet := aDataSet;
end;

procedure TDS2Excel.IncColRow;
begin
 if FCol = FDataSet.FieldCount - 1 then
 begin
   Inc(FRow);
   FCol :=0;
 end
 else
   Inc(FCol);
end;

procedure TDS2Excel.WriteBlankCell;
begin
 CXlsBlank[2] := FRow;
 CXlsBlank[3] := FCol;
 Stream.WriteBuffer(CXlsBlank, SizeOf(CXlsBlank));
 IncColRow;
end;

procedure TDS2Excel.WriteFloatCell(const AValue: Double);
begin
 CXlsNumber[2] := FRow;
 CXlsNumber[3] := FCol;
 Stream.WriteBuffer(CXlsNumber, SizeOf(CXlsNumber));
 Stream.WriteBuffer(AValue, 8);
 IncColRow;
end;

procedure TDS2Excel.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 TDS2Excel.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 TDS2Excel.WritePrefix;
begin
 Stream.WriteBuffer(CXlsBof, SizeOf(CXlsBof));
end;

procedure TDS2Excel.WriteSuffix;
begin
 Stream.WriteBuffer(CXlsEof, SizeOf(CXlsEof));
end;

procedure TDS2Excel.WriteTitle;
var
 n: word;
begin
 for n := 0 to FDataSet.FieldCount - 1 do
   WriteStringCell(FDataSet.Fields[n].FieldName);
end;

procedure TDS2Excel.WriteDataCell;
var
 n: word;
begin
 WritePrefix;
 if FWillWriteHead then WriteTitle;
 FDataSet.DisableControls;
 FBookMark := FDataSet.GetBookmark;
 FDataSet.First;
 while not FDataSet.Eof do
 begin
   for n := 0 to FDataSet.FieldCount - 1 do
   begin
     if FDataSet.Fields[n].IsNull then
       WriteBlankCell
     else begin
       case FDataSet.Fields[n].DataType of
         ftSmallint, ftInteger, ftWord, ftAutoInc, ftBytes:
             WriteIntegerCell(FDataSet.Fields[n].AsInteger);
         ftFloat, ftCurrency, ftBCD:
             WriteFloatCell(FDataSet.Fields[n].AsFloat);
       else
         WriteStringCell(FDataSet.Fields[n].AsString);
       end;
     end;
   end;
   FDataSet.Next;
 end;
 WriteSuffix;
 if FDataSet.BookmarkValid(FBookMark) then FDataSet.GotoBookmark(FBookMark);
 FDataSet.EnableControls;
end;

procedure TDS2Excel.Save2Stream(aStream: TStream);
begin
 FCol := 0;
 FRow := 0;
 Stream := aStream;
 WriteDataCell;
end;

procedure TDS2Excel.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.

--------------------------------------------------------------------------------

 2003-6-21 21:03:31    增加一个过程，用起来要方便一些

procedure TDS2Excel.Save2File(WillWriteHead: Boolean);
var
 SaveDialog1: TSaveDialog;
begin
 SaveDialog1 := TSaveDialog.Create(nil);
 Try
   SaveDialog1.Filter := 'Excel文档|*.xls';
   SaveDialog1.InitialDir := 'D:\';
   if not SaveDialog1.Execute then exit;
   Save2File(SaveDialog1.FileName, WillWriteHead);
 Finally
   SaveDialog1.Free;
 end;
end;