Hi,
Why not export the dataset straight to Excel. Here's a unit I hacked
together to do just that. Just call the function DS2Excel(MyDataSet) and
watch it happen. There's some optional parameters to tinker with. Check out
the Uses statement, I make use of some jcl libraries.
If you happen to make any improvements let me know.
unit ExportExcel;
{******************* DS2Excel (Dataset to Excel)
**************************}
{
}
{ Usage: Creates an Excel workbook using OLE automation from a dataset and
}
{ optionally saves it to a unique file name. The file in this case is save
to }
{ the User's Personal folder
}
{
}
{ PARAMETERS
}
{ MyDataset TDataset Any open dataset containingt be created
first }
{ #Filename TFilename If ''Excel remains open with the workbook
}
{ Otherwise saves workbook to Filename
}
{ #MyDateFormat String Format if Fieldtype is Date
}
{ #MyDateTimeFormat String Format if Fieldtype is DateTime
}
{ #MyTimeFormat String Format if Fieldtype is Time
}
{
}
{ Parameters marked # are optional whereupon default values are used
}
{
}
{ RETURNED Values boolean
}
{ true Operation Successful
}
{ false Operation Unsuccessful
}
{
}
{ Procedure / Function Author: Peter Gore
}
{ Last modified: 22/06/03
}
{
}
{***************************************************************************
***}
{
}
{ Revision History
}
{
}
{ Revision 0, Initial Release
}
{
}
{***************************************************************************
***}
interface
uses DB, Sysutils, Windows, Excel97, jclshell, jclFileUtils, jclSysInfo;
function DS2Excel(const MyDataSet: TDataSet; const Filename: TFilename = '';
MyDateFormat: string = 'dd/mm/yyyy'; MyDateTimeFormat: string = 'dd/mm/yyyy
hh:mm:ss'; MyTimeFormat: string = 'hh:mm:ss'): boolean;
implementation
uses
Dialogs, Variants;
function DS2Excel(const MyDataSet: TDataSet; const Filename: TFilename = '';
MyDateFormat: string = 'dd/mm/yyyy'; MyDateTimeFormat: string = 'dd/mm/yyyy
hh:mm:ss'; MyTimeFormat: string = 'hh:mm:ss'): boolean;
const
MAX_EXCEL_ROWCOUNT = 65536;
var
OLEDateFormat, OLEDateTimeFormat, OLETimeFormat: OleVariant;
LineNumber, LCID: Integer;
LineString: string;
DataVarArray: Variant;
HeadingVarArray: Variant;
Index: Integer;
Lastcolumn: string;
MyExcelApp: TExcelApplication;
Excel: string;
FileHandle: integer;
fTemp: TFilename;
//Function GetColumnLabel
//Returns the Column label from a column number
//eg 1 returns A, 2 Returns B, 27 Returns AA etc
function GetColumnLabel(Index: Integer): string;
var
Letter: string;
FirstCharIndex: byte;
SecondCharIndex: byte;
begin
Letter := 'abcdefghijklmnopqrstuvwxyz';
FirstCharIndex := Index div 26;
if index mod 26 = 0 then //must be column ?Z
begin
dec(FirstCharIndex);
SecondCharIndex := 26;
end
else
SecondCharIndex := index mod 26;
if FirstCharIndex = 0 then
Result := Letter[SecondCharIndex]
else
Result := Letter[FirstCharIndex] + Letter[SecondCharIndex];
end;
//Fuction Starts Here!
begin
Result := True;
//First Check that Excel is installed
//by assuming true if there is a valid file
//extension for .xls file
fTemp := PathAddSeparator(ExtractFilePath(ParamStr(0)))+'MyFile.xls';
FileHandle := FileCreate(fTemp);
excel := ShellFindExecutable(fTemp, '');
If FileHandle >= 0 then
begin
FileClose(FileHandle);
DeleteFile(PAnsiChar(fTemp));
end;
if Excel = '' then
begin
Result := false;
exit;
end;
OLEDateFormat := MyDateFormat;
OLEDateTimeFormat := MyDateTimeFormat;
OLETimeFormat := MyTimeFormat;
if not MyDataSet.Active then
begin
Result := False;
Exit;
end;
MyExcelApp := TExcelApplication.Create(nil);
MyDataSet.DisableControls;
DataVarArray := VarArrayCreate([0, MyDataSet.Fieldcount], varVariant);
//Create Column Headings
HeadingVarArray := VarArrayCreate([0, MyDataSet.Fieldcount], varVariant);
for Index := 0 to pred(MyDataSet.FieldCount) do
HeadingVarArray[Index] := MyDataSet.Fields[Index].FullName;
//Derive last Column
//Columns go A,b.....AA,AB....BA,BB etc
Lastcolumn := GetColumnLabel(MyDataSet.FieldCount);
LCID := GetUserDefaultLCID;
with MyExcelApp do
begin
connect;
try
try
//Only show Excel when not simply saving to a file
visible[LCID] := (Filename = '');
Workbooks.Add(xlWBATWorksheet, LCID);
Range['A1', Lastcolumn + '1'].Value := HeadingVarArray;
//Format Column Headings
with Range['A1', Lastcolumn + '1'] do
begin
HorizontalAlignment := xlcenter;
VerticalAlignment := xlBottom;
Wraptext := false;
Orientation := 0;
ShrinkTofit := false;
MergeCells := false;
Font.Bold := true;
end;
MyDataset.First;
LineNumber := 1;
while not MyDataset.Eof do
begin
Inc(lineNumber);
LineString := IntToStr(LineNumber);
for Index := 0 to pred(MyDataset.FieldCount) do
DataVarArray[Index] := MyDataset.Fields[Index].AsVariant;
Range['A' + LineString, Lastcolumn + LineString].Value :=
DataVarArray;
if LineNumber = MAX_EXCEL_ROWCOUNT then
Exit;
MyDataset.Next;
end;
LineString := IntToStr(LineNumber);
for Index := 0 to pred(MyDataSet.FieldCount) do
case MyDataSet.Fields[Index].DataType of
ftDate: Range[GetColumnLabel(Succ(Index)) + '2',
GetColumnLabel(Succ(Index)) + LineString].NumberFormat := OLEDateFormat;
ftTime: Range[GetColumnLabel(Succ(Index)) + '2',
GetColumnLabel(Succ(Index)) + LineString].NumberFormat := OLETimeFormat;
ftDateTime: Range[GetColumnLabel(Succ(Index)) + '2',
GetColumnLabel(Succ(Index)) + LineString].NumberFormat := OLEDateTimeFormat;
ftBCD: Range[GetColumnLabel(Succ(Index)) + '2',
GetColumnLabel(Succ(Index)) + LineString].NumberFormat := 'General';
end;
Range[GetColumnLabel(Succ(Index)) + '2',
GetColumnLabel(Succ(Index)) + LineString].NumberFormat := OLEDateFormat;
//Example Formatstrings
//Range['A2', 'L' + LineString].NumberFormat := OLEDateFormat;
//Range['H2','G'+LineString].NumberFormat := '0.00%';
//Range['G2','G'+LineString].NumberFormat := '$#,##0.00';
//Range['I2','I'+LineString].NumberFormat := '$#,##0.00';
//Range['J2','J'+LineString].NumberFormat := '$#,##0.00';
//Range['J2','J'+LineString].NumberFormat := '0.00E+00'
//Range['A1','J'+LineString].AutoFormat(xlRangeAutoFormatlist1,
true,
// true,true,true,true,true);
Range['A1', LastColumn + LineString].Columns.AutoFit;
if Filename <> '' then
begin
//Save File
try
ActiveWorkbook.SaveAs(FileName, xlNormal, '', '', false,
false,
xlNochange, xlUserResolution, False, EmptyParam, EmptyParam,
LCID);
except
MessageDlg(Filename + ' was not saved', mtError, [mbOK], 0);
Result := false;
end;
{SaveAs parameters
Filename: WideString;
FileFormat: OleVariant;
Password: OleVariant;
WriteResPassword: OleVariant;
ReadOnlyRecommended: OleVariant;
CreateBackup: OleVariant;
AddToMru: OleVariant;
TextCodepage: OleVariant;
TextVisualLayout: OleVariant;
lcid: Integer}
Quit; //Quit Excel
end;
except
Result := False;
end;
finally
disconnect; //From Excel Server
MyExcelApp.Free;
MyDataSet.EnableControls;
end; //try
end; //with MyExcelApp
end;
end.
Regards
Peter Gore
-----Original Message-----
From: Marselle Caston [mailto:[EMAIL PROTECTED]
Sent: 02 April 2005 00:04
To: [email protected]
Subject: [delphi-en] Memory Table Or StringGrid
I am building some querys on the fly and I would like
to know if anyone thinks I should populate the results
in a Memory table or a string grid. the reason for
this is that I am writing a program that will build a
Matrix and based on my results I need to store Table
Column Values in a String grid or Memory table and
then do Ole Automation and populate the fields in an
Excel Spread Sheet.
I am using SQl Server with Ado components..
or does someone know how I can store the values
directly into Excel if the user chooses to save the
Result set to Excel...? thanks
__________________________________
Do you Yahoo!?
Make Yahoo! your home page
http://www.yahoo.com/r/hs
-----------------------------------------------------
Home page: http://groups.yahoo.com/group/delphi-en/
To unsubscribe: [EMAIL PROTECTED]
Yahoo! Groups Links
-----------------------------------------------------
Home page: http://groups.yahoo.com/group/delphi-en/
To unsubscribe: [EMAIL PROTECTED]
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/delphi-en/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
