How best to export a DBGrid to EXCEL?
Michael Fullerton
>Hello
>(Please let me know if this is the proper newsgroup to ask this question and
>if not, which newsgroup would this question be appropriate?) Using DELPHI
>5.0 version
>
>Question: I have a number of Pascal Tables that when the user views them,
>would like to export the table to EXCEL format and open the table in EXCEL.
>
>Does one have to save the file first as an *.xls file before exporting the
>file?
>Can this procedure be done in one push of a button?
>
>Would appreciate a sample code and/or a source to view.
You can save the data as a CSV file which can be read by Excel. This
should give you some ideas.
var
i: integer;
strT: string;
slst: TStringList;
begin
slst:= TStringList.Create;
Try
With DBGrid1.DataSource.DataSet Do
begin
First;
while not Eof do
begin
strT:= '"'+Fields[0].AsString+'"';
for i:= 1 to FieldCount-1 do
strT:= strT+',"'+Fields[i].AsString+'"';
slst.Add(strT);
Next;
end;
First;
end;
slst.SaveToFile('C:\test.csv');
Finally
slst.Free;
End;
end;
___
The Delphi Compendium
http://www.cyber-matrix.com/delphi.htm
Paul Ferrara
Paul / ColumbuSoft
www.columbusoft.com
Michael Fullerton <cma...@spam-killer-remove-home.com> wrote in message
news:39c7ea7f....@newsgroups.borland.com...
Hans Prasch
Paul Ferrara <pa...@nospam.columbusoft.com> schrieb in im Newsbeitrag:
8q8thf$6b...@bornews.borland.com...
> You could write them to the F1Book component which can save a file as xls.
>
> Paul / ColumbuSoft
> www.columbusoft.com
>
Hans
Walter Prins
You can also do it on the fly by instantiating a copy of excel and dumping
it straight, without any intervening files, something like this (This is not
the most efficient way of doing this btw, but should illustrate the idea.
Also remember to add the uses Excel clause.):
uses Excel97;
procedure TMainForm.DumpToExcelButtonClick(Sender: TObject);
var
Save_Cursor:TCursor;
XLApp: Variant;
Sheet: Variant;
iCol,jRow : Integer;
v : Variant;
begin
Save_Cursor := Screen.Cursor;
Screen.Cursor := crHourglass;
try
// Create a worksheet in a new instance of excel and get a handle to
it to use below
XLApp := CreateOLEObject('Excel.Application');
XLApp.Visible := True;
XLApp.Workbooks.Add[XLWBatWorksheet];
XLApp.Workbooks[1].Worksheets[1].Name := 'Delphi Data';
Sheet := XLApp.Workbooks[1].Worksheets['Delphi Data'];
// Set the header line (jRow) and populate based on dataset
jRow := 1;
for iCol := 1 to SQLQuery.FieldCount do
begin
v := SQLQuery.Fields[iCol-1].FieldName; // we need this
intermediate variant because of weird exceptions without...
Sheet.Cells[jRow, iCol] := v;
SQLQuery.Next;
end;
// Move to the next row (Inc(jRow)) and populate data in row first
order
SQLQuery.First;
Inc(jRow);
while not SQLQuery.EOF do
begin
for iCol := 1 to SQLQuery.FieldCount do
begin
v := SQLQuery.Fields[iCol-1].Value;
Sheet.Cells[jRow, iCol] := v;
end;
Inc(jRow); // move the next row on the sheet and the dataset
SQLQuery.Next;
end;
// set the first line to bold
XLApp.Workbooks[1].WorkSheets['Delphi Data'].Rows.Item[iCol].Font.Bold
:= True;
// auto size the columns
for iCol := 1 to SQLQuery.FieldCount do
XLApp.Workbooks[1].WorkSheets['Delphi
Data'].Columns[iCol].EntireColumn.Autofit;
finally
Screen.Cursor := Save_Cursor;
end;
end;
Paul Ferrara
Paul / ColumbuSoft
www.columbusoft.com
Hans Prasch <hans....@t-online.de> wrote in message
news:8qbdhj$q0...@bornews.borland.com...