function TReport.QueryToXLS(
Query: TZQuery; FilePath:
string): boolean;
procedure WriteXLSCell(XLS: TStream;
const Row, Col: Word;
const Value:
string);
var
L: Word;
XLSLabel:
array[0..5]
of Word;
begin
L := Length(Value);
XLSLabel[0] := $204;
XLSLabel[1] := 8 + L;
XLSLabel[2] := Row;
XLSLabel[3] := Col;
XLSLabel[4] := 0;
XLSLabel[5] := L;
XLS.WriteBuffer(XLSLabel, SizeOf(XLSLabel));
XLS.WriteBuffer(Pointer(Value)^, L);
end;
const
XLSBof:
array[0..5]
of Word = ($809, 8, 00, $10, 0, 0);
XLSEof:
array[0..1]
of Word = ($0A, 00);
var
XLS: TFileStream;
Row, Col: Integer;
Columns: TStrings;
begin
XLS := TFileStream.Create(FilePath, fmCreate);
Columns := TSTringList.Create;
Row := 0;
Result := true;
try
// First we write the "Beginning of File"
XLS.WriteBuffer(XLSBof, SizeOf(XLSBof));
// Then we write the columns
Query.GetFieldNames(Columns);
for Col := 0
to Columns.Count - 1
do
begin
WriteXLSCell(XLS, Row, Col, GetHeader(Columns[col]));
end;
// Then the actual content
while not Query.Eof
do
begin
Row := Row + 1;
for Col := 0
to Columns.Count - 1
do
begin
WriteXLSCell(XLS, Row, Col,
Query.FieldByName(Columns[col]).AsString);
end;
Query.Next;
end;
// And last but not least the "End of File"
XLS.WriteBuffer(XLSEof, SizeOf(XLSEof));
finally
XLS.Free;
Columns.Free;
end;
end;
function TReport.QueryToCSV(
Query: TZQuery): TStringList;
var
Columns: TStrings;
Row:
string;
i: integer;
begin
Columns := TStringList.Create;
Result := TStringList.Create;
Row := '
';
Query.GetFieldNames(Columns);
for i := 0
to Query.FieldCount - 1
do
begin
Row := Row + GetHeader(Columns.Strings[i]) + #9;
end;
SetLength(Row, Length(Row) - 1);
Result.Add(Row);
while not Query.Eof
do
begin
Row := '
';
for i := 0
to Columns.Count - 1
do
begin
Row := Row + AnsiReplaceStr(AnsiReplaceStr(
Query.FieldByName(Columns[i]).AsString, #13#10, '
'), #9, '
') + #9;
end;
SetLength(Row, Length(Row) - 1);
Result.Add(Row);
Query.Next;
end;
FreeAndNil(Columns);
end;