Using Delphi To Export SQL Query Data TO Excel

[Jamie Sutherland]

Hi,
Could somebody help. I have an app that I need to export some data to Excel
from. The data is taken from a SQL Query. I have used Quickreports so I can
see the Data That Nedds Exporting to Excel. Could somebody Please help.

Thanks
Jamie Sutherland

[Murtaza Ali]

Check this out

try
qryDATA.First;
while not qryData.EOF do
begin
for Cnt := 0 to qryDATA.FieldCount-1 do
begin
CPos := pvCnt + 1;
xlSheet.Cells[(qryDATA.RecNo),CPos]:=
qryDATA.Fields.Fields[Cnt].AsString;
end;//for
qryDATA.Next;
end;//while
finally
end;

"Jamie Sutherland" <jamie.su...@talk21.com> wrote in message
news:3c4bef4a$1_1@dnews...

[Jamie Sutherland]

Murtaza,

Thanks For The Help. Could You Help me further. Below is the exact code I
have used fot this and I am getting The Following errors:
[Error] Reports.pas(38): Undeclared identifier: 'Cnt'
[Error] Reports.pas(40): Undeclared identifier: 'CPos'
[Error] Reports.pas(41): Undeclared identifier: 'xlsheet'
[Error] Reports.pas(45): 'END' expected but 'FINALLY' found
[Fatal Error] Project1.dpr(14): Could not compile used unit 'Report.pas'

I forgot to mention before it is Delphi 4 Prof that I am Using.
Thanks
Jamie

procedure Tfrm_Reports.Button2Click(Sender: TObject);
begin
DataMod.Qry_DetailsInSITS.First;
while not DataMod.Qry_DetailsInSITS.EOF do
begin
for Cnt := 0 to DataMod.Qry_DetailsInSITS.FieldCount-1 do

begin
CPos := pvCnt + 1;

xlSheet.Cells[(DataMod.Qry_DetailsInSITS.RecNo),CPos]:=
DataMod.Qry_DetailsInSITS.Fields.Fields[Cnt].AsString;
end;//for
DataMod.Qry_DetailsInSITS.Next;
end;//while
finally
end;

Murtaza Ali <mur...@global.com.kw> wrote in message
news:3c4c0e94_1@dnews...

[Todd]

"Jamie Sutherland" <jamie.su...@talk21.com> wrote in message

news:3c4c1f0b$1_1@dnews...

> Murtaza,
>
> Thanks For The Help. Could You Help me further. Below is the exact code I
> have used fot this and I am getting The Following errors:
> [Error] Reports.pas(38): Undeclared identifier: 'Cnt'
> [Error] Reports.pas(40): Undeclared identifier: 'CPos'
> [Error] Reports.pas(41): Undeclared identifier: 'xlsheet'
> [Error] Reports.pas(45): 'END' expected but 'FINALLY' found
> [Fatal Error] Project1.dpr(14): Could not compile used unit 'Report.

The code you used was a sample.. There needed to be a lot more to make it work. Check out
Borland.Public.delphi.oleautomation for more in depth answers.

[Eduardo Martinez O.]

Maybe not waht you need but have you taked a look to Microsoft Query. We use
it when we need data to be used by Excel from a simple query. Even some of
my advanced users have learned some basic SQL

Eduardo Martinez

[Warren Pearson]

Jamie You need to go the following site. He has a great little free utility
that will export a data set straight out to Excel. It works really well.

Warren Pearson

Author : Stefan Cruysberghs
* Email : stef...@yucom.be
* Website : http://www.stefancr.yucom.be

"Jamie Sutherland" <jamie.su...@talk21.com> wrote in message

news:3c4bef4a$1_1@dnews...

[Edrian Luyt]

I understand that you can not implement this code as is, but surely the crux
of this code is xlsheet.
One need to know what type it is, and what units needs to get used for this
"Excel" type.

From what I could gather, there's not a lot more to be implemented for this
code to work.
declare Cnt, Cpos as integer variables and declare Xlsheet as "?" include
"?" unit and viola!, it should work.

Edrian Luyt (Development)

Todd <Topp...@compunetresources.com> wrote in message
news:3c4c2a43_1@dnews...

[Andrew Skinner]

I do this.

I use a dBgrid to see the data and write code to iterate through the
TQuery data and write out as a *.csv file, which works with most
spreadsheets. It is a huge lot faster the ole control of Excel, which
I've also used. Use the text file procedures to write the file.

If you want it to look seamless you can use ole to open the file in
Excel, which is still quicker.

Andrew

[Vassiliev V.V.]

If you are working with Jet provider (Access db) you could just execute the
SQL query:

Select * into Sheet1 in
""[Excel 8.0;Database=C:\My Documents\myxls.xls;]
from (Select * from MyTable)

where (Select * from MyTable) is your query.

From SQL Server, use sp_addlinkedserver and

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\MyData\DistExcl.xls',
NULL,
'Excel 8.0'
GO

Or, OPENQUERY or OPENROWSET.

Then, work with Excel sheets.

Regards,
Vassiliev V.V.
http://www.oledbidrect.com

"Jamie Sutherland" <jamie.su...@talk21.com> сообщил/сообщила в новостях
следующее: news:3c4bef4a$1_1@dnews...

[Billy Bob]

On Mon, 21 Jan 2002 10:36:31 -0000, Tony wrote:
Go for a comma or tab delimted file. Excel will deal with them quite
happily. Just did this my self after work upgraded to Office 2000 and
the old ole stuff just blew up on me. I could have fixed that but then
it would n't have worked with the non 2000 users. Not even Micr$s$oft
will change the spec for a csv file.

[Topp_rolmc]

Edrian Luyt wrote:
>
> I understand that you can not implement this code as is, but surely the crux
> of this code is xlsheet.
> One need to know what type it is, and what units needs to get used for this
> "Excel" type.
>
> From what I could gather, there's not a lot more to be implemented for this
> code to work.
> declare Cnt, Cpos as integer variables and declare Xlsheet as "?" include
> "?" unit and viola!, it should work.

And that can be answered in b.p.d.oleautomation. The XLSheet can be one
of many objects, and may require more than just "adding a unit"....

There is also the correct use of Createing the Excel instance that the
spreadsheet is in......

[Topp_rolmc]

Edrian Luyt wrote:
>
> I understand that you can not implement this code as is, but surely the crux
> of this code is xlsheet.
> One need to know what type it is, and what units needs to get used for this
> "Excel" type.
>
> From what I could gather, there's not a lot more to be implemented for this
> code to work.
> declare Cnt, Cpos as integer variables and declare Xlsheet as "?" include
> "?" unit and viola!, it should work.

And that can be answered in b.p.d.oleautomation. The XLSheet can be one

[Bruce]

Try this snippet code. Put the path to the .csv file you've made from the
data set in the "theFile" variable, and add OleCtrls, Variants to your uses
clause.

try

try
ExcelApp:= GetActiveOLEObject( 'Excel.Application' ) As
_Application
except
ExcelApp:= CreateOLEObject( 'Excel.Application' ) As
_Application;
end;

with ExcelApp do
begin

Visible:=true;
OLEFile:=theFile;
Documents.Open(OLEFile, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam,
EmptyParam);

Activate;
Result:=true;
OLEFile:= UnAssigned;
end;

except
Result:=false;
end;

"Jamie Sutherland" <jamie.su...@talk21.com> wrote in message

news:3c4bef4a$1_1@dnews...

[Tomislav Kardaš]

Hi Jamie!

On Mon, 21 Jan 2002 10:36:31 -0000, "Jamie Sutherland"
<jamie.su...@talk21.com> wrote:

>Could somebody help. I have an app that I need to export some data to Excel
>from. The data is taken from a SQL Query. I have used Quickreports so I can
>see the Data That Nedds Exporting to Excel. Could somebody Please help.

I found out that for me the best sollution is over clipboard. Just
place tab separated values text in a clipboard and paste it in excell
where ever you want. It is not completely automatic but is the easiest
way I would say.

tomi.

[Alessandro Loffredo]

On Mon, 21 Jan 2002 10:36:31 -0000, "Jamie Sutherland"
<jamie.su...@talk21.com> wrote:

Just download my component TALSDatasetExport.
It allows you to export a dataset to XLS format without OLE or DDE
(and a lot of other native formats like DBF,TXT,CSV,HTML,TDV)

You can find it on
http://www.geocities.com/a_l_software/TALSDatasetExportD5.zip

It's precompiled for D5, if you need it compiled for other delphi
version just let me know or buy sources :)