I
import
Unregistered / Unconfirmed
GUEST, unregistred user!
//****************************
// 数据输出
// Autumn 2002 in guangzhou
// delphi1999@21cn.com
//****************************
unit Unit2;
interface
uses
classes,db,graphics;
type
//temp**************************
{ TdxDBGridExportWrapper=class(tcomponent)
end;
TdxTreeListNode=class(tcomponent)
end;
}
//temp end**********************
TaExport = class
private
FFileName: string;
// FGrid: TdxDBGridExportWrapper;
// FSaveAll: Boolean;
FaDataSet: TDataSet;///////////
protected
TotalCount: Integer;
Stream: TStream;
procedure DoBeginWriting; virtual;
procedure DoEndWriting; virtual;
procedure DoWriteHeader; virtual;
procedure DoWriteFooter; virtual;
procedure DoWriteRecord; virtual;
public
procedure Execute; virtual;
procedure SaveToStream(Stream: TStream); virtual;
property aDataSet:TDataSet read FaDataSet write FaDataSet;////////
property FileName: string read FFileName write FFileName;
// property Grid: TdxDBGridExportWrapper read FGrid write FGrid;
// property SaveAll: Boolean read FSaveAll write FSaveAll;
end;
TaExportExcel = class(TaExport)
protected
// VisColIndexes: array [0..255] of Integer;
// VisColCount: Integer;
FFont:TFont;
procedure DoBeginWriting; override;
procedure DoEndWriting; override;
procedure DoWriteHeader; override;
procedure DoWriteFooter; override;
procedure DoWriteRecord; override;
public
constructor Create(AFont:TFont);virtual;
end;
implementation
// Excel format
const
XLSBOF: array[0..4] of Word = ($409, 6, 0, $10, 0);
XLSGUTS: array[0..5] of Word = ($80, 8, 0, 0, 0, 0);
XLSPalette: array[0..2] of Word = ($92, 0, 0);
XLSDimension: array [0..6] of Word = ($200, $0A, 0, $FFFF, 0, $FF, 0);
XLSEOF: array[0..1] of Word = ($0A, 0);
XLSFONTH: array[0..9] of Byte = ($31, 2, 0, 0, 0, 0, 0, 0, 8, 0);
XLSFONTG: array[0..9] of Byte = ($31, 2, 0, 0, 0, 0, 0, 0, 10, 0);
XLSFONT: array[0..9] of Byte = ($31, 2, 0, 0, 0, 0, 0, 0, 0, 0);
XLSXF1: array[0..15] of Byte = ($43, 4, $0C, 0, 0, 0, $F5, $FF, $20, 0, 0, $CE, 0, 0, 0, 0);
XLSXF2: array[0..15] of Byte = ($43, 4, $0C, 0, 1, 0, $F5, $FF, $20, $F4, 0, $CE, 0, 0, 0, 0);
XLSXF3: array[0..15] of Byte = ($43, 4, $0C, 0, 2, 0, $F5, $FF, $20, $F4, 0, $CE, 0, 0, 0, 0);
XLSXF4: array[0..15] of Byte = ($43, 4, $0C, 0, 0, 0, $F5, $FF, $20, $F4, 0, $CE, 0, 0, 0, 0);
XLSXF5: array[0..15] of Byte = ($43, 4, $0C, 0, 0, 0, 1, 0, $20, 0, 0, $CE, 0, 0, 0, 0);
XLSXF6: array[0..15] of Byte = ($43, 4, $0C, 0, 1, $21, $F5, $FF, $20, $F8, 0, $CE, 0, 0, 0, 0);
XLSXF7: array[0..15] of Byte = ($43, 4, $0C, 0, 1, $1F, $F5, $FF, $20, $F8, 0, $CE, 0, 0, 0, 0);
XLSXF8: array[0..15] of Byte = ($43, 4, $0C, 0, 1, $20, $F5, $FF, $20, $F8, 0, $CE, 0, 0, 0, 0);
XLSXF9: array[0..15] of Byte = ($43, 4, $0C, 0, 1, $1E, $F5, $FF, $20, $F8, 0, $CE, 0, 0, 0, 0);
XLSXF10: array[0..15] of Byte = ($43, 4, $0C, 0, 1, $0D, $F5, $FF, $20, $F8, 0, $CE, 0, 0, 0, 0);
XLSXF: array[0..15] of Byte = ($43, 4, $0C, 0, 5, 0, 1, 0, $21, $78, $41, 3, 0, 0, 0, 0);
XLSXFB: array[0..15] of Byte = ($43, 4, $0C, 0, 8, 0, 1, 0, $22, $78, $41, 2, $71, $71, $71, 0);
XLSXFH: array[0..15] of Byte = ($43, 4, $0C, 0, 6, 0, 1, 0, $22, $78, $41, 2, $71, $71, $71, $71);
XLSXFG: array[0..15] of Byte = ($43, 4, $0C, 0, 7, 0, 1, 0, $21, $78, $C1, 2, 0, 0, 0, 0);
XLSXFF: array[0..15] of Byte = ($43, 4, $0C, 0, 5, 0, 1, 0, $22, $78, $C1, 2, $B9, $B9, $B9, $B9);
XLSXFF1: array[0..15] of Byte = ($43, 4, $0C, 0, 5, 0, 1, 0, $22, $78, $C1, 2, $B9, 0, $B9, 0);
XLSXFRF: array[0..15] of Byte = ($43, 4, $0C, 0, 9, 0, 1, 0, $21, $78, $C1, 2, $B9, $B9, $B9, $B9);
XLSXFRF1: array[0..15] of Byte = ($43, 4, $0C, 0, 9, 0, 1, 0, $21, $78, $C1, 2, $B9, 0, $B9, 0);
XLSCOL: array[0..7] of Word = ($7D, $0C, 0, 0, 0, $F, 0, 0);
XLSSFONT: array[0..15] of Byte = ($31, 2, $0C, 0, $C8, 0, 0, 0, $FF, $7F, 5, $41, $72, $69, $61, $6C);
XLSLabel: array[0..5] of Word = ($204, 0, 0, 0, 0, 0);
XLSBlank: array[0..4] of Word = ($201, 6, 0, 0, $17);
XLSBlankF: array[0..4] of Word = ($201, 6, 0, 0, $19);
XLSBlankRF: array[0..4] of Word = ($201, 6, 0, 0, $1C);
// XLSBlankH: array[0..4] of Word = ($201, 6, 0, 0, $16);
// XLSBlankB: array[0..4] of Word = ($201, 6, 0, 0, $1A);
XLSNumber: array[0..4] of Word = ($203, 14, 0, 0, 0);
XLSRK: array[0..4] of Word = ($27E, 10, 0, 0, 0);
XLSFormula: array[0..15] of Word =($406, $1D, 0, 0, $18, 0, 0, 0, 0, 3, $0B, $25, 0, 0, 0, 0);
{---------TaExport ------------------------------------------------------------}
procedure TaExport.DoBeginWriting;
begin
end;
procedure TaExport.DoEndWriting;
begin
end;
procedure TaExport.DoWriteFooter;
begin
end;
procedure TaExport.DoWriteHeader;
begin
end;
procedure TaExport.DoWriteRecord;
begin
end;
procedure TaExport.Execute;
begin
Stream := TFileStream.Create(FileName, fmCreate);
try
SaveToStream(Stream);
finally
Stream.Free;
Stream := nil;
end;
end;
procedure TaExport.SaveToStream(Stream: TStream);
var
Current: TBookmarkStr;
i: Integer;
begin
if Assigned(FaDataSet) then
begin
FaDataSet.DisableControls;
Current := FaDataSet.Bookmark;
DoBeginWriting;
try
TotalCount := 0;
// write header
DoWriteHeader;
FaDataSet.First;
while not FaDataSet.EOF do
begin
// processing record
DoWriteRecord;
Inc(TotalCount);
FaDataSet.Next;
end;
DoWriteFooter;
finally
DoEndWriting;
FaDataSet.Bookmark := Current;
FaDataSet.EnableControls;
end;
end;
end;
{ --------TaExportExcel -------------------------------------------------------}
constructor TaExportExcel.Create(AFont: TFont);
begin
FFont:=AFont;
end;
procedure TaExportExcel.DoBeginWriting;
var
C: LongInt;
S: string;
B: Byte;
i, j, k: Integer;
begin
inherited;
//with Grid do
begin
{ VisColCount := 0;
for i := 0 to GetBandCount - 1 do
for j := 0 to GetHeaderRowCount(i) - 1 do
for k := 0 to GetHeaderColCount(i, j) - 1 do
if IsHeaderVisible(GetHeaderAbsoluteIndex(i,j,k)) then
begin
VisColIndexes[VisColCount] := GetHeaderAbsoluteIndex(i,j,k);
Inc(VisColCount);
end;}
Stream.WriteBuffer(XLSBOF, SizeOf(XLSBOF));
Stream.WriteBuffer(XLSGUTS, SizeOf(XLSGUTS));
// XLS Palette
XLSPalette[1] := 54;
XLSPalette[2] := 13;
Stream.WriteBuffer(XLSPalette, SizeOf(XLSPalette));
//if HeaderFont.Color = clNone then
C := 0;
// C := ColorToRGB(HeaderFont.Color);
Stream.WriteBuffer(C, SizeOf(C));
//if HeaderColor = clNone then
C:=ColorToRGB(clBtnface);
Stream.WriteBuffer(C, SizeOf(C));
//if GroupNodeTextColor = clNone then
Stream.WriteBuffer(C, SizeOf(C));
//if GroupNodeColor = clNone then
Stream.WriteBuffer(C, SizeOf(C));
//if Font.Color = clNone then
C:=ColorToRGB(clBlack);
Stream.WriteBuffer(C, SizeOf(C));
//if Color = clNone then
C:=ColorToRGB(clWhite);
Stream.WriteBuffer(C, SizeOf(C));
C := ColorToRGB(clWindowFrame);
Stream.WriteBuffer(C, SizeOf(C));
C := ColorToRGB(clWindow);
Stream.WriteBuffer(C, SizeOf(C));
//if BandFont.Color = clNone then
Stream.WriteBuffer(C, SizeOf(C));
//if BandColor = clNone then
C:=ColorToRGB(clBtnface);
Stream.WriteBuffer(C, SizeOf(C));
//if RowFooterTextColor = clNone then
C:=ColorToRGB(clBlack);
Stream.WriteBuffer(C, SizeOf(C));
//if RowFooterColor = clNone then
C:=ColorToRGB(clBtnface);
Stream.WriteBuffer(C, SizeOf(C));
//if GridLineColor = clNone then
C:=ColorToRGB(clBlack);
Stream.WriteBuffer(C, SizeOf(C));
// XLS Font
Stream.WriteBuffer(XLSSFont, SizeOf(XLSSFont));
Stream.WriteBuffer(XLSSFont, SizeOf(XLSSFont));
Stream.WriteBuffer(XLSSFont, SizeOf(XLSSFont));
Stream.WriteBuffer(XLSSFont, SizeOf(XLSSFont));
// Grid Font
S := FFont.Name;
B := Length(S);
XLSFont[2] := B + 7;
XLSFont[4] := FFont.Size*20 mod 256;
XLSFont[5] := FFont.Size*20 div 256;
XLSFont[6] := 0;
if (fsBold in FFont.Style) then XLSFont[6] := XLSFont[6] + 1;
if (fsItalic in FFont.Style) then XLSFont[6] := XLSFont[6] + 2;
if (fsUnderline in FFont.Style) then XLSFont[6] := XLSFont[6] + 4;
if (fsStrikeOut in FFont.Style) then XLSFont[6] := XLSFont[6] + 8;
XLSFont[8] := 12;
Stream.WriteBuffer(XLSFont, SizeOf(XLSFont));
Stream.WriteBuffer(B, SizeOf(B));
Stream.WriteBuffer(Pointer(S)^, Length(S));
// Header Font
S := FFont.Name;
B := Length(S);
XLSFontH[2] := B + 7;
XLSFontH[4] := FFont.Size*20 mod 256;
XLSFontH[5] := FFont.Size*20 div 256;
XLSFontH[6] := 0;
if (fsBold in FFont.Style) then XLSFontH[6] := XLSFontH[6] + 1;
if (fsItalic in FFont.Style) then XLSFontH[6] := XLSFontH[6] + 2;
if (fsUnderline in FFont.Style) then XLSFontH[6] := XLSFontH[6] + 4;
if (fsStrikeOut in FFont.Style) then XLSFontH[6] := XLSFontH[6] + 8;
Stream.WriteBuffer(XLSFontH, SizeOf(XLSFontH));
Stream.WriteBuffer(B, SizeOf(B));
Stream.WriteBuffer(Pointer(S)^, Length(S));
// Group Font
S := FFont.Name;
B := Length(S);
XLSFontG[2] := B + 7;
XLSFontG[4] := FFont.Size*20 mod 256;
XLSFontG[5] := FFont.Size*20 div 256;
XLSFontG[6] := 0;
if (fsBold in FFont.Style) then XLSFontG[6] := XLSFontG[6] + 1;
if (fsItalic in FFont.Style) then XLSFontG[6] := XLSFontG[6] + 2;
if (fsUnderline in FFont.Style) then XLSFontG[6] := XLSFontG[6] + 4;
if (fsStrikeOut in FFont.Style) then XLSFontG[6] := XLSFontG[6] + 8;
Stream.WriteBuffer(XLSFontG, SizeOf(XLSFontG));
Stream.WriteBuffer(B, SizeOf(B));
Stream.WriteBuffer(Pointer(S)^, Length(S));
// Band Font
S := FFont.Name;
B := Length(S);
XLSFont[2] := B + 7;
XLSFont[4] := FFont.Size*20 mod 256;
XLSFont[5] := FFont.Size*20 div 256;
XLSFont[6] := 0;
if (fsBold in FFont.Style) then XLSFont[6] := XLSFont[6] + 1;
if (fsItalic in FFont.Style) then XLSFont[6] := XLSFont[6] + 2;
if (fsUnderline in FFont.Style) then XLSFont[6] := XLSFont[6] + 4;
if (fsStrikeOut in FFont.Style) then XLSFont[6] := XLSFont[6] + 8;
XLSFont[8] := 16;
Stream.WriteBuffer(XLSFont, SizeOf(XLSFont));
Stream.WriteBuffer(B, SizeOf(B));
Stream.WriteBuffer(Pointer(S)^, Length(S));
// Footer Font
S := FFont.Name;
B := Length(S);
XLSFont[2] := B + 7;
XLSFont[4] := FFont.Size*20 mod 256;
XLSFont[5] := FFont.Size*20 div 256;
XLSFont[6] := 0;
if (fsBold in FFont.Style) then XLSFont[6] := XLSFont[6] + 1;
if (fsItalic in FFont.Style) then XLSFont[6] := XLSFont[6] + 2;
if (fsUnderline in FFont.Style) then XLSFont[6] := XLSFont[6] + 4;
if (fsStrikeOut in FFont.Style) then XLSFont[6] := XLSFont[6] + 8;
XLSFont[8] := 18;
Stream.WriteBuffer(XLSFont, SizeOf(XLSFont));
Stream.WriteBuffer(B, SizeOf(B));
Stream.WriteBuffer(Pointer(S)^, Length(S));
// Columns Fonts
for i := 0 to FaDataSet.Fields.Count - 1 do
begin
S := FFont.Name;
B := Length(S);
XLSFont[2] := B + 7;
XLSFont[4] := FFont.Size*20 mod 256;
XLSFont[5] := FFont.Size*20 div 256;
XLSFont[6] := 0;
if (fsBold in FFont.Style) then XLSFont[6] := XLSFont[6] + 1;
if (fsItalic in FFont.Style) then XLSFont[6] := XLSFont[6] + 2;
if (fsUnderline in FFont.Style) then XLSFont[6] := XLSFont[6] + 4;
if (fsStrikeOut in FFont.Style) then XLSFont[6] := XLSFont[6] + 8;
XLSFont[8] := 12;
Stream.WriteBuffer(XLSFont, SizeOf(XLSFont));
Stream.WriteBuffer(B, SizeOf(B));
Stream.WriteBuffer(Pointer(S)^, Length(S));
end;
// Grid
Stream.WriteBuffer(XLSXF1, SizeOf(XLSXF1));
Stream.WriteBuffer(XLSXF2, SizeOf(XLSXF2));
Stream.WriteBuffer(XLSXF2, SizeOf(XLSXF2));
Stream.WriteBuffer(XLSXF3, SizeOf(XLSXF3));
Stream.WriteBuffer(XLSXF3, SizeOf(XLSXF3));
for i := 0 to 9 do
Stream.WriteBuffer(XLSXF4, SizeOf(XLSXF4));
Stream.WriteBuffer(XLSXF5, SizeOf(XLSXF5));
Stream.WriteBuffer(XLSXF6, SizeOf(XLSXF6));
Stream.WriteBuffer(XLSXF7, SizeOf(XLSXF7));
Stream.WriteBuffer(XLSXF8, SizeOf(XLSXF8));
Stream.WriteBuffer(XLSXF9, SizeOf(XLSXF9));
Stream.WriteBuffer(XLSXF10, SizeOf(XLSXF10));
//if ShowGrid then
begin
XLSXF[12] := $A1;
XLSXF[13] := $A1;
XLSXF[14] := $A1;
XLSXF[15] := $A1;
end;
Stream.WriteBuffer(XLSXF, SizeOf(XLSXF));
Stream.WriteBuffer(XLSXFH, SizeOf(XLSXFH));
Stream.WriteBuffer(XLSXFG, SizeOf(XLSXFG));
Stream.WriteBuffer(XLSXFF, SizeOf(XLSXFF));
Stream.WriteBuffer(XLSXFF1, SizeOf(XLSXFF1));
Stream.WriteBuffer(XLSXFB, SizeOf(XLSXFB));
Stream.WriteBuffer(XLSXFRF, SizeOf(XLSXFRF));
Stream.WriteBuffer(XLSXFRF1, SizeOf(XLSXFRF1));
for i := 0 to FaDataSet.Fields.Count - 1 do
begin
XLSXF[4] := i + 10;
{case Columns[VisColIndexes].Alignment of
taLeftJustify : }XLSXF[8] := $21;
{taRightJustify : XLSXF[8] := $23;
taCenter : XLSXF[8] := $22;
end;}
Stream.WriteBuffer(XLSXF, SizeOf(XLSXF));
end;
// Group Columns
{for i := 0 to GroupColumnCount - 1 do
begin
XLSCOL[2] := i;
XLSCOL[3] := i;
XLSCOL[4] := 400;
Stream.WriteBuffer(XLSCOL, SizeOf(XLSCOL));
end;}
// VsibleColumns
for i := 0 to FaDataSet.Fields.Count - 1 do
begin
XLSCOL[2] := i + 0;//GroupColumnCount;
XLSCOL[3] := i + 0;//GroupColumnCount;
XLSCOL[4] := 36 * 50;//GetHeaderBoundsWidth(VisColIndexes);
Stream.WriteBuffer(XLSCOL, SizeOf(XLSCOL));
end;
Stream.WriteBuffer(XLSDimension, SizeOf(XLSDimension));
end;
end;
procedure TaExportExcel.DoEndWriting;
begin
inherited;
Stream.WriteBuffer(XLSEOF, SizeOf(XLSEOF));
end;
procedure TaExportExcel.DoWriteFooter;
{function FindSummaryCol(const FieldName: string; var Column: TdxDBTreeListColumn): Integer;
var
i: Integer;
begin
Result := -1;
for i := 0 to VisColCount - 1 do
if AnsiCompareText(Grid.Columns[VisColIndexes].FieldName, FieldName) = 0 then
begin
Result := i;
Exit;
end;
end;}
var
i, ColIndx: Integer;
// Col: TdxDBTreeListColumn;
b: Byte;
begin
inherited;
//with Grid do
//if ShowSummaryFooter then
begin
{for i := 0 to GroupColumnCount - 1 do
begin
XLSBlankF[2] := TotalCount;
XLSBlankF[3] := i;
Stream.WriteBuffer(XLSBlankF, SizeOf(XLSBlankF));
end;}
for i := 0 to FaDataSet.Fields.Count - 1 do
begin
// with TdxDBGridColumn(Columns[VisColIndexes]) do
begin
{case SummaryFooterType of
cstAvg: XLSFormula[15] := $501;
cstCount: XLSFormula[15] := $A901;
cstMax: XLSFormula[15] := $701;
cstMin: XLSFormula[15] := $601;
cstSum:} XLSFormula[15] := $10;
//end;
//ColIndx := FindSummaryCol(SummaryFooterField, Col);
//if (SummaryFooterType = cstCount) then
ColIndx := i;
if not (FaDataSet.Fields.DataType in [ftSmallInt,ftInteger,ftWord,ftFloat,
ftCurrency,ftBCD]) then
//(SummaryFooterType = cstNone) or (ColIndx = -1) then
begin
XLSBlankF[2] := TotalCount;
XLSBlankF[3] := //GroupColumnCount
0 + i;
Stream.WriteBuffer(XLSBlankF, SizeOf(XLSBlankF));
end
else
begin
XLSFormula[2] := TotalCount;
XLSFormula[3] := //GroupColumnCount
0 + i;
XLSFormula[11] := $25;
//if ShowBands then XLSFormula[11] := XLSFormula[11] + $100;
//if ShowHeader then
XLSFormula[11] := XLSFormula[11] + $100;
XLSFormula[12] := ((TotalCount - 1) shl 8) + $C0;
XLSFormula[13] := ((0
//GroupColumnCount
+ ColIndx) shl 8) + (((TotalCount - 1) shr 8) or $C0);
// if SummaryFooterType = cstSum then
XLSFormula[14] := $1900 + 0
//GroupColumnCount
+ ColIndx;
// else XLSFormula[14] := $4200 + GroupColumnCount + ColIndx;
Stream.WriteBuffer(XLSFormula, SizeOf(XLSFormula));
b := 0;
Stream.WriteBuffer(b, SizeOf(b));
end;
end;
end;
end;
end;
procedure TaExportExcel.DoWriteHeader;
var
i, j, k: Integer;
S: string;
begin
inherited;
//with Grid do
begin
// Draw Bands
{if ShowBands then
begin
{for i := 0 to GroupColumnCount - 1 do
begin
XLSBlank[2] := TotalCount;
XLSBlank[3] := i;
Stream.WriteBuffer(XLSBlank, SizeOf(XLSBlank));
end;
for j := 0 to FaDataSet.Fields.Count - 1 do
begin
XLSBlank[2] := TotalCount;
XLSBlank[3] := j + 0;//GroupColumnCount;
Stream.WriteBuffer(XLSBlank, SizeOf(XLSBlank));
end;
k := 0;
for i := 0 to GetBandCount - 1 do
begin
S := GetBandText(i);
XLSLAbel[1] := Length(S) + 8;
XLSLabel[2] := TotalCount;
XLSLabel[3] := k + GroupColumnCount;
XLSLabel[4] := $1A;
XLSLabel[5] := Length(S);
Stream.WriteBuffer(XLSLabel, SizeOf(XLSLabel));
Stream.WriteBuffer(Pointer(S)^, Length(S));
for j := 0 to GetHeaderRowCount(i) - 1 do
k := k + GetHeaderColCount(i,j);
end;
Inc(TotalCount);
end;}
// Draw Heders
//if ShowHeader then
begin
{for i := 0 to GroupColumnCount - 1 do
begin
XLSBlank[2] := TotalCount;
XLSBlank[3] := i;
Stream.WriteBuffer(XLSBlank, SizeOf(XLSBlank));
end;}
for i := 0 to FaDataSet.Fields.Count - 1 do
begin
S := FaDataSet.Fields.DisplayName; //Columns[VisColIndexes].Caption;
{if Columns[VisColIndexes].DisableCaption then
S := '';}
XLSLAbel[1] := Length(S) + 8;
XLSLabel[2] := TotalCount;
XLSLabel[3] := i + 0;//GroupColumnCount;
XLSLabel[4] := $16;
XLSLabel[5] := Length(S);
Stream.WriteBuffer(XLSLabel, SizeOf(XLSLabel));
Stream.WriteBuffer(Pointer(S)^, Length(S));
end;
Inc(TotalCount);
end;
end;
end;
procedure TaExportExcel.DoWriteRecord;
var
C: LongInt;
F: Double;
i, j: Integer;
S: string;
V: Variant;
begin
inherited;
//with Grid do
begin
{if (GroupColumnCount > 0) and (Node <> nil) then
begin
for i := 0 to GroupColumnCount - 1 do
if i = Node.Level then
begin
S := Node.Strings[GroupColumns[Node.Level].Index];
XLSLAbel[1] := Length(S) + 8;
XLSLabel[2] := TotalCount;
XLSLabel[3] := i;
XLSLabel[4] := $17;
XLSLAbel[5] := Length(S);
Stream.WriteBuffer(XLSLabel, SizeOf(XLSLabel));
Stream.WriteBuffer(Pointer(S)^, Length(S));
end
else
begin
XLSBlank[2] := TotalCount;
XLSBlank[3] := i;
Stream.WriteBuffer(XLSBlank, SizeOf(XLSBlank));
end;
end;}
for i := 0 to FaDataSet.Fields.Count - 1 do
begin
{if (Node <> nil) and Node.HasChildren then
begin
XLSBlank[2] := TotalCount;
XLSBlank[3] := GroupColumnCount + i;
Stream.WriteBuffer(XLSBlank, SizeOf(XLSBlank));
end
else
if (Columns[VisColIndexes].FieldName = '') or
not Assigned(Columns[VisColIndexes].Field ) then
begin
XLSLAbel[1] := 8;
XLSLabel[2] := TotalCount;
XLSLabel[3] := i;
XLSLabel[4] := $1D + i;
XLSLAbel[5] := 0;
Stream.WriteBuffer(XLSLabel, SizeOf(XLSLabel));
end
else}
begin
with FaDataSet.Fields do//Columns[VisColIndexes].Field do
begin
{if Node <> nil then
V := Node.Values[Columns[VisColIndexes].Index]
else} V := Value;
// Convert Value
{if Columns[VisColIndexes].ConvertExportValue(V, False) then
case VarType(V) of
varSmallint, varInteger:
begin
if VarIsNull(V) then C := 0
else C := V;
C := C shl 2 + 2;
XLSRK[2] := TotalCount;
XLSRK[3] := GroupColumnCount + i;
XLSRK[4] := $1D + i;
Stream.WriteBuffer(XLSRK, SizeOf(XLSRK));
Stream.WriteBuffer(C, SizeOf(C));
end;
varSingle, varDouble, varCurrency:
begin
if VarIsNull(V) then F := 0.0
else F := V;
XLSNumber[2] := TotalCount;
XLSNumber[3] := GroupColumnCount + i;
XLSNumber[4] := $1D + i;
Stream.WriteBuffer(XLSNumber, SizeOf(XLSNumber));
Stream.WriteBuffer(F, SizeOf(F));
end;
else //String//
begin
if VarIsNull(V) then S := ''
else S := V;
if DataType = ftMemo then
S := Copy(S, 0, 255);
XLSLAbel[1] := Length(S) + 8;
XLSLabel[2] := TotalCount;
XLSLabel[3] := GroupColumnCount + i;
XLSLabel[4] := $1D + i;
XLSLAbel[5] := Length(S);
Stream.WriteBuffer(XLSLabel, SizeOf(XLSLabel));
Stream.WriteBuffer(Pointer(S)^, Length(S));
end;
end
else} {old}
begin
case DataType of
ftSmallint, ftInteger, ftWord, ftAutoInc, ftBytes:
begin
//if Node = nil then
C := AsInteger;
{else
begin
V := TdxDBGridNode(Node).VariantValues[Columns[VisColIndexes].Index];
if V = Null then C := 0
else C := V;
end;}
C := C shl 2 + 2;
XLSRK[2] := TotalCount;
XLSRK[3] := //GroupColumnCount
0 + i;
XLSRK[4] := $1D + i;
Stream.WriteBuffer(XLSRK, SizeOf(XLSRK));
Stream.WriteBuffer(C, SizeOf(C));
end;
ftFloat, ftCurrency, ftBCD:
begin
//if Node = nil then
F := AsFloat;
{else
begin
V := TdxDBGridNode(Node).VariantValues[Columns[VisColIndexes].Index];
if V = Null then F := 0.0
else F := V;
end;}
XLSNumber[2] := TotalCount;
XLSNumber[3] := //GroupColumnCount
0 + i;
XLSNumber[4] := $1D + i;
Stream.WriteBuffer(XLSNumber, SizeOf(XLSNumber));
Stream.WriteBuffer(F, SizeOf(F));
end;
else
begin
//if Node = nil then
S := DisplayText;
{else S := TdxDBGridNode(Node).Strings[Columns[VisColIndexes].Index];}
if DataType = ftMemo then
S := Copy(S, 0, 255);
XLSLAbel[1] := Length(S) + 8;
XLSLabel[2] := TotalCount;
XLSLabel[3] := //GroupColumnCount
0 + i;
XLSLabel[4] := $1D + i;
XLSLAbel[5] := Length(S);
Stream.WriteBuffer(XLSLabel, SizeOf(XLSLabel));
Stream.WriteBuffer(Pointer(S)^, Length(S));
end;
end;
end;
end;
end;
end;
{for j := 0 to GetRowFooterCount(Node) - 1 do
begin
Inc(TotalCount);
for i := 0 to GetNodeFooterLevel(Node,j) do
begin
XLSBlank[2] := TotalCount;
XLSBlank[3] := i;
Stream.WriteBuffer(XLSBlank, SizeOf(XLSBlank));
end;
for i := 0 to VisColCount - 1 do
if not IsExistRowFooterCell(Node,VisColIndexes,j) then
begin
XLSBlankRF[2] := TotalCount;
XLSBlankRF[3] := GroupColumnCount + i;
Stream.WriteBuffer(XLSBlankRF, SizeOf(XLSBlankRF));
end
else
begin
S := GetFooterCellText(Node,VisColIndexes,j);
XLSLabel[1] := Length(S) + 8;
XLSLabel[2] := TotalCount;
XLSLabel[3] := GroupColumnCount + i;
if (i = 0) and (GroupColumnCount - GetNodeFooterLevel(Node,j) - 1 > 0) then
XLSLabel[3] := GetNodeFooterLevel(Node,j) + 1
XLSLabel[4] := $1B;
XLSLabel[5] := Length(S);
Stream.WriteBuffer(XLSLabel, SizeOf(XLSLabel));
Stream.WriteBuffer(Pointer(S)^, Length(S));
end;
for i := 1 to GroupColumnCount - GetNodeFooterLevel(Node,j) - 1 do
begin
XLSBlankRF[2] := TotalCount;
XLSBlankRF[3] := GroupColumnCount - i + 1;
Stream.WriteBuffer(XLSBlankRF, SizeOf(XLSBlankRF));
end;
end;}
end;
end;
end.
使用：
var
A:TaExportExcel;
begin
A:=TaExportExcel.Create(一个你想要在Excel中显示的字体对象);
A.FileName:=一个excel文件的路径加文件名;
A.aDataSet:=想要导出到excel的DataSet对象
A.Execute;//就导出了
end;
// 数据输出
// Autumn 2002 in guangzhou
// delphi1999@21cn.com
//****************************
unit Unit2;
interface
uses
classes,db,graphics;
type
//temp**************************
{ TdxDBGridExportWrapper=class(tcomponent)
end;
TdxTreeListNode=class(tcomponent)
end;
}
//temp end**********************
TaExport = class
private
FFileName: string;
// FGrid: TdxDBGridExportWrapper;
// FSaveAll: Boolean;
FaDataSet: TDataSet;///////////
protected
TotalCount: Integer;
Stream: TStream;
procedure DoBeginWriting; virtual;
procedure DoEndWriting; virtual;
procedure DoWriteHeader; virtual;
procedure DoWriteFooter; virtual;
procedure DoWriteRecord; virtual;
public
procedure Execute; virtual;
procedure SaveToStream(Stream: TStream); virtual;
property aDataSet:TDataSet read FaDataSet write FaDataSet;////////
property FileName: string read FFileName write FFileName;
// property Grid: TdxDBGridExportWrapper read FGrid write FGrid;
// property SaveAll: Boolean read FSaveAll write FSaveAll;
end;
TaExportExcel = class(TaExport)
protected
// VisColIndexes: array [0..255] of Integer;
// VisColCount: Integer;
FFont:TFont;
procedure DoBeginWriting; override;
procedure DoEndWriting; override;
procedure DoWriteHeader; override;
procedure DoWriteFooter; override;
procedure DoWriteRecord; override;
public
constructor Create(AFont:TFont);virtual;
end;
implementation
// Excel format
const
XLSBOF: array[0..4] of Word = ($409, 6, 0, $10, 0);
XLSGUTS: array[0..5] of Word = ($80, 8, 0, 0, 0, 0);
XLSPalette: array[0..2] of Word = ($92, 0, 0);
XLSDimension: array [0..6] of Word = ($200, $0A, 0, $FFFF, 0, $FF, 0);
XLSEOF: array[0..1] of Word = ($0A, 0);
XLSFONTH: array[0..9] of Byte = ($31, 2, 0, 0, 0, 0, 0, 0, 8, 0);
XLSFONTG: array[0..9] of Byte = ($31, 2, 0, 0, 0, 0, 0, 0, 10, 0);
XLSFONT: array[0..9] of Byte = ($31, 2, 0, 0, 0, 0, 0, 0, 0, 0);
XLSXF1: array[0..15] of Byte = ($43, 4, $0C, 0, 0, 0, $F5, $FF, $20, 0, 0, $CE, 0, 0, 0, 0);
XLSXF2: array[0..15] of Byte = ($43, 4, $0C, 0, 1, 0, $F5, $FF, $20, $F4, 0, $CE, 0, 0, 0, 0);
XLSXF3: array[0..15] of Byte = ($43, 4, $0C, 0, 2, 0, $F5, $FF, $20, $F4, 0, $CE, 0, 0, 0, 0);
XLSXF4: array[0..15] of Byte = ($43, 4, $0C, 0, 0, 0, $F5, $FF, $20, $F4, 0, $CE, 0, 0, 0, 0);
XLSXF5: array[0..15] of Byte = ($43, 4, $0C, 0, 0, 0, 1, 0, $20, 0, 0, $CE, 0, 0, 0, 0);
XLSXF6: array[0..15] of Byte = ($43, 4, $0C, 0, 1, $21, $F5, $FF, $20, $F8, 0, $CE, 0, 0, 0, 0);
XLSXF7: array[0..15] of Byte = ($43, 4, $0C, 0, 1, $1F, $F5, $FF, $20, $F8, 0, $CE, 0, 0, 0, 0);
XLSXF8: array[0..15] of Byte = ($43, 4, $0C, 0, 1, $20, $F5, $FF, $20, $F8, 0, $CE, 0, 0, 0, 0);
XLSXF9: array[0..15] of Byte = ($43, 4, $0C, 0, 1, $1E, $F5, $FF, $20, $F8, 0, $CE, 0, 0, 0, 0);
XLSXF10: array[0..15] of Byte = ($43, 4, $0C, 0, 1, $0D, $F5, $FF, $20, $F8, 0, $CE, 0, 0, 0, 0);
XLSXF: array[0..15] of Byte = ($43, 4, $0C, 0, 5, 0, 1, 0, $21, $78, $41, 3, 0, 0, 0, 0);
XLSXFB: array[0..15] of Byte = ($43, 4, $0C, 0, 8, 0, 1, 0, $22, $78, $41, 2, $71, $71, $71, 0);
XLSXFH: array[0..15] of Byte = ($43, 4, $0C, 0, 6, 0, 1, 0, $22, $78, $41, 2, $71, $71, $71, $71);
XLSXFG: array[0..15] of Byte = ($43, 4, $0C, 0, 7, 0, 1, 0, $21, $78, $C1, 2, 0, 0, 0, 0);
XLSXFF: array[0..15] of Byte = ($43, 4, $0C, 0, 5, 0, 1, 0, $22, $78, $C1, 2, $B9, $B9, $B9, $B9);
XLSXFF1: array[0..15] of Byte = ($43, 4, $0C, 0, 5, 0, 1, 0, $22, $78, $C1, 2, $B9, 0, $B9, 0);
XLSXFRF: array[0..15] of Byte = ($43, 4, $0C, 0, 9, 0, 1, 0, $21, $78, $C1, 2, $B9, $B9, $B9, $B9);
XLSXFRF1: array[0..15] of Byte = ($43, 4, $0C, 0, 9, 0, 1, 0, $21, $78, $C1, 2, $B9, 0, $B9, 0);
XLSCOL: array[0..7] of Word = ($7D, $0C, 0, 0, 0, $F, 0, 0);
XLSSFONT: array[0..15] of Byte = ($31, 2, $0C, 0, $C8, 0, 0, 0, $FF, $7F, 5, $41, $72, $69, $61, $6C);
XLSLabel: array[0..5] of Word = ($204, 0, 0, 0, 0, 0);
XLSBlank: array[0..4] of Word = ($201, 6, 0, 0, $17);
XLSBlankF: array[0..4] of Word = ($201, 6, 0, 0, $19);
XLSBlankRF: array[0..4] of Word = ($201, 6, 0, 0, $1C);
// XLSBlankH: array[0..4] of Word = ($201, 6, 0, 0, $16);
// XLSBlankB: array[0..4] of Word = ($201, 6, 0, 0, $1A);
XLSNumber: array[0..4] of Word = ($203, 14, 0, 0, 0);
XLSRK: array[0..4] of Word = ($27E, 10, 0, 0, 0);
XLSFormula: array[0..15] of Word =($406, $1D, 0, 0, $18, 0, 0, 0, 0, 3, $0B, $25, 0, 0, 0, 0);
{---------TaExport ------------------------------------------------------------}
procedure TaExport.DoBeginWriting;
begin
end;
procedure TaExport.DoEndWriting;
begin
end;
procedure TaExport.DoWriteFooter;
begin
end;
procedure TaExport.DoWriteHeader;
begin
end;
procedure TaExport.DoWriteRecord;
begin
end;
procedure TaExport.Execute;
begin
Stream := TFileStream.Create(FileName, fmCreate);
try
SaveToStream(Stream);
finally
Stream.Free;
Stream := nil;
end;
end;
procedure TaExport.SaveToStream(Stream: TStream);
var
Current: TBookmarkStr;
i: Integer;
begin
if Assigned(FaDataSet) then
begin
FaDataSet.DisableControls;
Current := FaDataSet.Bookmark;
DoBeginWriting;
try
TotalCount := 0;
// write header
DoWriteHeader;
FaDataSet.First;
while not FaDataSet.EOF do
begin
// processing record
DoWriteRecord;
Inc(TotalCount);
FaDataSet.Next;
end;
DoWriteFooter;
finally
DoEndWriting;
FaDataSet.Bookmark := Current;
FaDataSet.EnableControls;
end;
end;
end;
{ --------TaExportExcel -------------------------------------------------------}
constructor TaExportExcel.Create(AFont: TFont);
begin
FFont:=AFont;
end;
procedure TaExportExcel.DoBeginWriting;
var
C: LongInt;
S: string;
B: Byte;
i, j, k: Integer;
begin
inherited;
//with Grid do
begin
{ VisColCount := 0;
for i := 0 to GetBandCount - 1 do
for j := 0 to GetHeaderRowCount(i) - 1 do
for k := 0 to GetHeaderColCount(i, j) - 1 do
if IsHeaderVisible(GetHeaderAbsoluteIndex(i,j,k)) then
begin
VisColIndexes[VisColCount] := GetHeaderAbsoluteIndex(i,j,k);
Inc(VisColCount);
end;}
Stream.WriteBuffer(XLSBOF, SizeOf(XLSBOF));
Stream.WriteBuffer(XLSGUTS, SizeOf(XLSGUTS));
// XLS Palette
XLSPalette[1] := 54;
XLSPalette[2] := 13;
Stream.WriteBuffer(XLSPalette, SizeOf(XLSPalette));
//if HeaderFont.Color = clNone then
C := 0;
// C := ColorToRGB(HeaderFont.Color);
Stream.WriteBuffer(C, SizeOf(C));
//if HeaderColor = clNone then
C:=ColorToRGB(clBtnface);
Stream.WriteBuffer(C, SizeOf(C));
//if GroupNodeTextColor = clNone then
Stream.WriteBuffer(C, SizeOf(C));
//if GroupNodeColor = clNone then
Stream.WriteBuffer(C, SizeOf(C));
//if Font.Color = clNone then
C:=ColorToRGB(clBlack);
Stream.WriteBuffer(C, SizeOf(C));
//if Color = clNone then
C:=ColorToRGB(clWhite);
Stream.WriteBuffer(C, SizeOf(C));
C := ColorToRGB(clWindowFrame);
Stream.WriteBuffer(C, SizeOf(C));
C := ColorToRGB(clWindow);
Stream.WriteBuffer(C, SizeOf(C));
//if BandFont.Color = clNone then
Stream.WriteBuffer(C, SizeOf(C));
//if BandColor = clNone then
C:=ColorToRGB(clBtnface);
Stream.WriteBuffer(C, SizeOf(C));
//if RowFooterTextColor = clNone then
C:=ColorToRGB(clBlack);
Stream.WriteBuffer(C, SizeOf(C));
//if RowFooterColor = clNone then
C:=ColorToRGB(clBtnface);
Stream.WriteBuffer(C, SizeOf(C));
//if GridLineColor = clNone then
C:=ColorToRGB(clBlack);
Stream.WriteBuffer(C, SizeOf(C));
// XLS Font
Stream.WriteBuffer(XLSSFont, SizeOf(XLSSFont));
Stream.WriteBuffer(XLSSFont, SizeOf(XLSSFont));
Stream.WriteBuffer(XLSSFont, SizeOf(XLSSFont));
Stream.WriteBuffer(XLSSFont, SizeOf(XLSSFont));
// Grid Font
S := FFont.Name;
B := Length(S);
XLSFont[2] := B + 7;
XLSFont[4] := FFont.Size*20 mod 256;
XLSFont[5] := FFont.Size*20 div 256;
XLSFont[6] := 0;
if (fsBold in FFont.Style) then XLSFont[6] := XLSFont[6] + 1;
if (fsItalic in FFont.Style) then XLSFont[6] := XLSFont[6] + 2;
if (fsUnderline in FFont.Style) then XLSFont[6] := XLSFont[6] + 4;
if (fsStrikeOut in FFont.Style) then XLSFont[6] := XLSFont[6] + 8;
XLSFont[8] := 12;
Stream.WriteBuffer(XLSFont, SizeOf(XLSFont));
Stream.WriteBuffer(B, SizeOf(B));
Stream.WriteBuffer(Pointer(S)^, Length(S));
// Header Font
S := FFont.Name;
B := Length(S);
XLSFontH[2] := B + 7;
XLSFontH[4] := FFont.Size*20 mod 256;
XLSFontH[5] := FFont.Size*20 div 256;
XLSFontH[6] := 0;
if (fsBold in FFont.Style) then XLSFontH[6] := XLSFontH[6] + 1;
if (fsItalic in FFont.Style) then XLSFontH[6] := XLSFontH[6] + 2;
if (fsUnderline in FFont.Style) then XLSFontH[6] := XLSFontH[6] + 4;
if (fsStrikeOut in FFont.Style) then XLSFontH[6] := XLSFontH[6] + 8;
Stream.WriteBuffer(XLSFontH, SizeOf(XLSFontH));
Stream.WriteBuffer(B, SizeOf(B));
Stream.WriteBuffer(Pointer(S)^, Length(S));
// Group Font
S := FFont.Name;
B := Length(S);
XLSFontG[2] := B + 7;
XLSFontG[4] := FFont.Size*20 mod 256;
XLSFontG[5] := FFont.Size*20 div 256;
XLSFontG[6] := 0;
if (fsBold in FFont.Style) then XLSFontG[6] := XLSFontG[6] + 1;
if (fsItalic in FFont.Style) then XLSFontG[6] := XLSFontG[6] + 2;
if (fsUnderline in FFont.Style) then XLSFontG[6] := XLSFontG[6] + 4;
if (fsStrikeOut in FFont.Style) then XLSFontG[6] := XLSFontG[6] + 8;
Stream.WriteBuffer(XLSFontG, SizeOf(XLSFontG));
Stream.WriteBuffer(B, SizeOf(B));
Stream.WriteBuffer(Pointer(S)^, Length(S));
// Band Font
S := FFont.Name;
B := Length(S);
XLSFont[2] := B + 7;
XLSFont[4] := FFont.Size*20 mod 256;
XLSFont[5] := FFont.Size*20 div 256;
XLSFont[6] := 0;
if (fsBold in FFont.Style) then XLSFont[6] := XLSFont[6] + 1;
if (fsItalic in FFont.Style) then XLSFont[6] := XLSFont[6] + 2;
if (fsUnderline in FFont.Style) then XLSFont[6] := XLSFont[6] + 4;
if (fsStrikeOut in FFont.Style) then XLSFont[6] := XLSFont[6] + 8;
XLSFont[8] := 16;
Stream.WriteBuffer(XLSFont, SizeOf(XLSFont));
Stream.WriteBuffer(B, SizeOf(B));
Stream.WriteBuffer(Pointer(S)^, Length(S));
// Footer Font
S := FFont.Name;
B := Length(S);
XLSFont[2] := B + 7;
XLSFont[4] := FFont.Size*20 mod 256;
XLSFont[5] := FFont.Size*20 div 256;
XLSFont[6] := 0;
if (fsBold in FFont.Style) then XLSFont[6] := XLSFont[6] + 1;
if (fsItalic in FFont.Style) then XLSFont[6] := XLSFont[6] + 2;
if (fsUnderline in FFont.Style) then XLSFont[6] := XLSFont[6] + 4;
if (fsStrikeOut in FFont.Style) then XLSFont[6] := XLSFont[6] + 8;
XLSFont[8] := 18;
Stream.WriteBuffer(XLSFont, SizeOf(XLSFont));
Stream.WriteBuffer(B, SizeOf(B));
Stream.WriteBuffer(Pointer(S)^, Length(S));
// Columns Fonts
for i := 0 to FaDataSet.Fields.Count - 1 do
begin
S := FFont.Name;
B := Length(S);
XLSFont[2] := B + 7;
XLSFont[4] := FFont.Size*20 mod 256;
XLSFont[5] := FFont.Size*20 div 256;
XLSFont[6] := 0;
if (fsBold in FFont.Style) then XLSFont[6] := XLSFont[6] + 1;
if (fsItalic in FFont.Style) then XLSFont[6] := XLSFont[6] + 2;
if (fsUnderline in FFont.Style) then XLSFont[6] := XLSFont[6] + 4;
if (fsStrikeOut in FFont.Style) then XLSFont[6] := XLSFont[6] + 8;
XLSFont[8] := 12;
Stream.WriteBuffer(XLSFont, SizeOf(XLSFont));
Stream.WriteBuffer(B, SizeOf(B));
Stream.WriteBuffer(Pointer(S)^, Length(S));
end;
// Grid
Stream.WriteBuffer(XLSXF1, SizeOf(XLSXF1));
Stream.WriteBuffer(XLSXF2, SizeOf(XLSXF2));
Stream.WriteBuffer(XLSXF2, SizeOf(XLSXF2));
Stream.WriteBuffer(XLSXF3, SizeOf(XLSXF3));
Stream.WriteBuffer(XLSXF3, SizeOf(XLSXF3));
for i := 0 to 9 do
Stream.WriteBuffer(XLSXF4, SizeOf(XLSXF4));
Stream.WriteBuffer(XLSXF5, SizeOf(XLSXF5));
Stream.WriteBuffer(XLSXF6, SizeOf(XLSXF6));
Stream.WriteBuffer(XLSXF7, SizeOf(XLSXF7));
Stream.WriteBuffer(XLSXF8, SizeOf(XLSXF8));
Stream.WriteBuffer(XLSXF9, SizeOf(XLSXF9));
Stream.WriteBuffer(XLSXF10, SizeOf(XLSXF10));
//if ShowGrid then
begin
XLSXF[12] := $A1;
XLSXF[13] := $A1;
XLSXF[14] := $A1;
XLSXF[15] := $A1;
end;
Stream.WriteBuffer(XLSXF, SizeOf(XLSXF));
Stream.WriteBuffer(XLSXFH, SizeOf(XLSXFH));
Stream.WriteBuffer(XLSXFG, SizeOf(XLSXFG));
Stream.WriteBuffer(XLSXFF, SizeOf(XLSXFF));
Stream.WriteBuffer(XLSXFF1, SizeOf(XLSXFF1));
Stream.WriteBuffer(XLSXFB, SizeOf(XLSXFB));
Stream.WriteBuffer(XLSXFRF, SizeOf(XLSXFRF));
Stream.WriteBuffer(XLSXFRF1, SizeOf(XLSXFRF1));
for i := 0 to FaDataSet.Fields.Count - 1 do
begin
XLSXF[4] := i + 10;
{case Columns[VisColIndexes].Alignment of
taLeftJustify : }XLSXF[8] := $21;
{taRightJustify : XLSXF[8] := $23;
taCenter : XLSXF[8] := $22;
end;}
Stream.WriteBuffer(XLSXF, SizeOf(XLSXF));
end;
// Group Columns
{for i := 0 to GroupColumnCount - 1 do
begin
XLSCOL[2] := i;
XLSCOL[3] := i;
XLSCOL[4] := 400;
Stream.WriteBuffer(XLSCOL, SizeOf(XLSCOL));
end;}
// VsibleColumns
for i := 0 to FaDataSet.Fields.Count - 1 do
begin
XLSCOL[2] := i + 0;//GroupColumnCount;
XLSCOL[3] := i + 0;//GroupColumnCount;
XLSCOL[4] := 36 * 50;//GetHeaderBoundsWidth(VisColIndexes);
Stream.WriteBuffer(XLSCOL, SizeOf(XLSCOL));
end;
Stream.WriteBuffer(XLSDimension, SizeOf(XLSDimension));
end;
end;
procedure TaExportExcel.DoEndWriting;
begin
inherited;
Stream.WriteBuffer(XLSEOF, SizeOf(XLSEOF));
end;
procedure TaExportExcel.DoWriteFooter;
{function FindSummaryCol(const FieldName: string; var Column: TdxDBTreeListColumn): Integer;
var
i: Integer;
begin
Result := -1;
for i := 0 to VisColCount - 1 do
if AnsiCompareText(Grid.Columns[VisColIndexes].FieldName, FieldName) = 0 then
begin
Result := i;
Exit;
end;
end;}
var
i, ColIndx: Integer;
// Col: TdxDBTreeListColumn;
b: Byte;
begin
inherited;
//with Grid do
//if ShowSummaryFooter then
begin
{for i := 0 to GroupColumnCount - 1 do
begin
XLSBlankF[2] := TotalCount;
XLSBlankF[3] := i;
Stream.WriteBuffer(XLSBlankF, SizeOf(XLSBlankF));
end;}
for i := 0 to FaDataSet.Fields.Count - 1 do
begin
// with TdxDBGridColumn(Columns[VisColIndexes]) do
begin
{case SummaryFooterType of
cstAvg: XLSFormula[15] := $501;
cstCount: XLSFormula[15] := $A901;
cstMax: XLSFormula[15] := $701;
cstMin: XLSFormula[15] := $601;
cstSum:} XLSFormula[15] := $10;
//end;
//ColIndx := FindSummaryCol(SummaryFooterField, Col);
//if (SummaryFooterType = cstCount) then
ColIndx := i;
if not (FaDataSet.Fields.DataType in [ftSmallInt,ftInteger,ftWord,ftFloat,
ftCurrency,ftBCD]) then
//(SummaryFooterType = cstNone) or (ColIndx = -1) then
begin
XLSBlankF[2] := TotalCount;
XLSBlankF[3] := //GroupColumnCount
0 + i;
Stream.WriteBuffer(XLSBlankF, SizeOf(XLSBlankF));
end
else
begin
XLSFormula[2] := TotalCount;
XLSFormula[3] := //GroupColumnCount
0 + i;
XLSFormula[11] := $25;
//if ShowBands then XLSFormula[11] := XLSFormula[11] + $100;
//if ShowHeader then
XLSFormula[11] := XLSFormula[11] + $100;
XLSFormula[12] := ((TotalCount - 1) shl 8) + $C0;
XLSFormula[13] := ((0
//GroupColumnCount
+ ColIndx) shl 8) + (((TotalCount - 1) shr 8) or $C0);
// if SummaryFooterType = cstSum then
XLSFormula[14] := $1900 + 0
//GroupColumnCount
+ ColIndx;
// else XLSFormula[14] := $4200 + GroupColumnCount + ColIndx;
Stream.WriteBuffer(XLSFormula, SizeOf(XLSFormula));
b := 0;
Stream.WriteBuffer(b, SizeOf(b));
end;
end;
end;
end;
end;
procedure TaExportExcel.DoWriteHeader;
var
i, j, k: Integer;
S: string;
begin
inherited;
//with Grid do
begin
// Draw Bands
{if ShowBands then
begin
{for i := 0 to GroupColumnCount - 1 do
begin
XLSBlank[2] := TotalCount;
XLSBlank[3] := i;
Stream.WriteBuffer(XLSBlank, SizeOf(XLSBlank));
end;
for j := 0 to FaDataSet.Fields.Count - 1 do
begin
XLSBlank[2] := TotalCount;
XLSBlank[3] := j + 0;//GroupColumnCount;
Stream.WriteBuffer(XLSBlank, SizeOf(XLSBlank));
end;
k := 0;
for i := 0 to GetBandCount - 1 do
begin
S := GetBandText(i);
XLSLAbel[1] := Length(S) + 8;
XLSLabel[2] := TotalCount;
XLSLabel[3] := k + GroupColumnCount;
XLSLabel[4] := $1A;
XLSLabel[5] := Length(S);
Stream.WriteBuffer(XLSLabel, SizeOf(XLSLabel));
Stream.WriteBuffer(Pointer(S)^, Length(S));
for j := 0 to GetHeaderRowCount(i) - 1 do
k := k + GetHeaderColCount(i,j);
end;
Inc(TotalCount);
end;}
// Draw Heders
//if ShowHeader then
begin
{for i := 0 to GroupColumnCount - 1 do
begin
XLSBlank[2] := TotalCount;
XLSBlank[3] := i;
Stream.WriteBuffer(XLSBlank, SizeOf(XLSBlank));
end;}
for i := 0 to FaDataSet.Fields.Count - 1 do
begin
S := FaDataSet.Fields.DisplayName; //Columns[VisColIndexes].Caption;
{if Columns[VisColIndexes].DisableCaption then
S := '';}
XLSLAbel[1] := Length(S) + 8;
XLSLabel[2] := TotalCount;
XLSLabel[3] := i + 0;//GroupColumnCount;
XLSLabel[4] := $16;
XLSLabel[5] := Length(S);
Stream.WriteBuffer(XLSLabel, SizeOf(XLSLabel));
Stream.WriteBuffer(Pointer(S)^, Length(S));
end;
Inc(TotalCount);
end;
end;
end;
procedure TaExportExcel.DoWriteRecord;
var
C: LongInt;
F: Double;
i, j: Integer;
S: string;
V: Variant;
begin
inherited;
//with Grid do
begin
{if (GroupColumnCount > 0) and (Node <> nil) then
begin
for i := 0 to GroupColumnCount - 1 do
if i = Node.Level then
begin
S := Node.Strings[GroupColumns[Node.Level].Index];
XLSLAbel[1] := Length(S) + 8;
XLSLabel[2] := TotalCount;
XLSLabel[3] := i;
XLSLabel[4] := $17;
XLSLAbel[5] := Length(S);
Stream.WriteBuffer(XLSLabel, SizeOf(XLSLabel));
Stream.WriteBuffer(Pointer(S)^, Length(S));
end
else
begin
XLSBlank[2] := TotalCount;
XLSBlank[3] := i;
Stream.WriteBuffer(XLSBlank, SizeOf(XLSBlank));
end;
end;}
for i := 0 to FaDataSet.Fields.Count - 1 do
begin
{if (Node <> nil) and Node.HasChildren then
begin
XLSBlank[2] := TotalCount;
XLSBlank[3] := GroupColumnCount + i;
Stream.WriteBuffer(XLSBlank, SizeOf(XLSBlank));
end
else
if (Columns[VisColIndexes].FieldName = '') or
not Assigned(Columns[VisColIndexes].Field ) then
begin
XLSLAbel[1] := 8;
XLSLabel[2] := TotalCount;
XLSLabel[3] := i;
XLSLabel[4] := $1D + i;
XLSLAbel[5] := 0;
Stream.WriteBuffer(XLSLabel, SizeOf(XLSLabel));
end
else}
begin
with FaDataSet.Fields do//Columns[VisColIndexes].Field do
begin
{if Node <> nil then
V := Node.Values[Columns[VisColIndexes].Index]
else} V := Value;
// Convert Value
{if Columns[VisColIndexes].ConvertExportValue(V, False) then
case VarType(V) of
varSmallint, varInteger:
begin
if VarIsNull(V) then C := 0
else C := V;
C := C shl 2 + 2;
XLSRK[2] := TotalCount;
XLSRK[3] := GroupColumnCount + i;
XLSRK[4] := $1D + i;
Stream.WriteBuffer(XLSRK, SizeOf(XLSRK));
Stream.WriteBuffer(C, SizeOf(C));
end;
varSingle, varDouble, varCurrency:
begin
if VarIsNull(V) then F := 0.0
else F := V;
XLSNumber[2] := TotalCount;
XLSNumber[3] := GroupColumnCount + i;
XLSNumber[4] := $1D + i;
Stream.WriteBuffer(XLSNumber, SizeOf(XLSNumber));
Stream.WriteBuffer(F, SizeOf(F));
end;
else //String//
begin
if VarIsNull(V) then S := ''
else S := V;
if DataType = ftMemo then
S := Copy(S, 0, 255);
XLSLAbel[1] := Length(S) + 8;
XLSLabel[2] := TotalCount;
XLSLabel[3] := GroupColumnCount + i;
XLSLabel[4] := $1D + i;
XLSLAbel[5] := Length(S);
Stream.WriteBuffer(XLSLabel, SizeOf(XLSLabel));
Stream.WriteBuffer(Pointer(S)^, Length(S));
end;
end
else} {old}
begin
case DataType of
ftSmallint, ftInteger, ftWord, ftAutoInc, ftBytes:
begin
//if Node = nil then
C := AsInteger;
{else
begin
V := TdxDBGridNode(Node).VariantValues[Columns[VisColIndexes].Index];
if V = Null then C := 0
else C := V;
end;}
C := C shl 2 + 2;
XLSRK[2] := TotalCount;
XLSRK[3] := //GroupColumnCount
0 + i;
XLSRK[4] := $1D + i;
Stream.WriteBuffer(XLSRK, SizeOf(XLSRK));
Stream.WriteBuffer(C, SizeOf(C));
end;
ftFloat, ftCurrency, ftBCD:
begin
//if Node = nil then
F := AsFloat;
{else
begin
V := TdxDBGridNode(Node).VariantValues[Columns[VisColIndexes].Index];
if V = Null then F := 0.0
else F := V;
end;}
XLSNumber[2] := TotalCount;
XLSNumber[3] := //GroupColumnCount
0 + i;
XLSNumber[4] := $1D + i;
Stream.WriteBuffer(XLSNumber, SizeOf(XLSNumber));
Stream.WriteBuffer(F, SizeOf(F));
end;
else
begin
//if Node = nil then
S := DisplayText;
{else S := TdxDBGridNode(Node).Strings[Columns[VisColIndexes].Index];}
if DataType = ftMemo then
S := Copy(S, 0, 255);
XLSLAbel[1] := Length(S) + 8;
XLSLabel[2] := TotalCount;
XLSLabel[3] := //GroupColumnCount
0 + i;
XLSLabel[4] := $1D + i;
XLSLAbel[5] := Length(S);
Stream.WriteBuffer(XLSLabel, SizeOf(XLSLabel));
Stream.WriteBuffer(Pointer(S)^, Length(S));
end;
end;
end;
end;
end;
end;
{for j := 0 to GetRowFooterCount(Node) - 1 do
begin
Inc(TotalCount);
for i := 0 to GetNodeFooterLevel(Node,j) do
begin
XLSBlank[2] := TotalCount;
XLSBlank[3] := i;
Stream.WriteBuffer(XLSBlank, SizeOf(XLSBlank));
end;
for i := 0 to VisColCount - 1 do
if not IsExistRowFooterCell(Node,VisColIndexes,j) then
begin
XLSBlankRF[2] := TotalCount;
XLSBlankRF[3] := GroupColumnCount + i;
Stream.WriteBuffer(XLSBlankRF, SizeOf(XLSBlankRF));
end
else
begin
S := GetFooterCellText(Node,VisColIndexes,j);
XLSLabel[1] := Length(S) + 8;
XLSLabel[2] := TotalCount;
XLSLabel[3] := GroupColumnCount + i;
if (i = 0) and (GroupColumnCount - GetNodeFooterLevel(Node,j) - 1 > 0) then
XLSLabel[3] := GetNodeFooterLevel(Node,j) + 1
XLSLabel[4] := $1B;
XLSLabel[5] := Length(S);
Stream.WriteBuffer(XLSLabel, SizeOf(XLSLabel));
Stream.WriteBuffer(Pointer(S)^, Length(S));
end;
for i := 1 to GroupColumnCount - GetNodeFooterLevel(Node,j) - 1 do
begin
XLSBlankRF[2] := TotalCount;
XLSBlankRF[3] := GroupColumnCount - i + 1;
Stream.WriteBuffer(XLSBlankRF, SizeOf(XLSBlankRF));
end;
end;}
end;
end;
end.
使用：
var
A:TaExportExcel;
begin
A:=TaExportExcel.Create(一个你想要在Excel中显示的字体对象);
A.FileName:=一个excel文件的路径加文件名;
A.aDataSet:=想要导出到excel的DataSet对象
A.Execute;//就导出了
end;