Export query output to excel format

Hi Friends

Long time no see!!!.
Could any one help me out my problem

I am opening a query at runtime. I dont want to display the output in report writer. Instead of report writer I need to display the query output in excel

Could anyone help me out with example and code

Regards
Pandian
Pandian012000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MotazCommented:
Use this unit:

{----------------------------------------------------------------------}
{  Excel unit : By Motaz Abdel Azeem                                  }
{  Created :  Saturday,  1, April, 2000                                }
{  Updated :  Sunday,   12, November, 2000                             }
{----------------------------------------------------------------------}

unit Excel;

interface

uses Graphics;

function ExcelCreate(SheetName: string; Show: Boolean = True): Variant;
procedure ExcelPut(ExcelObject: Variant; SheetName: string;
  Row, Col: Integer; Data: string; FontName: string = '';
  FontSize: Integer = 0; Color: Integer = clBlack; FontStyles: TFontStyles = []);
procedure ExcelClose(ExcelObject: Variant);

implementation

uses
  ComObj;

{-------------------------------------------------------------------------}
{  ExcelCreate: Open Excel application                                                                                     }
{-------------------------------------------------------------------------}

function ExcelCreate(SheetName: string; Show: Boolean = True): Variant;
const
  xlWBatWorkSheet = -4167;
var
  WorkBook: variant;
  V: Variant;
begin
  V:= CreateOleObject('Excel.Application');
  WorkBook := V.Workbooks.Add(xlWBatWorkSheet);
  WorkBook.WorkSheets[1].Name := SheetName;
  V.Visible := Show;
  Result:= V;
end;

{-------------------------------------------------------------------------}
{  ExcelPut: Put data into cells                                                       }
{-------------------------------------------------------------------------}

procedure ExcelPut(ExcelObject: Variant; SheetName: string;
  Row, Col: Integer; Data: string; FontName: string = '';
  FontSize: Integer = 0; Color: Integer = clBlack; FontStyles: TFontStyles = []);
var
  Sheet: variant;
begin
  Sheet:= ExcelObject.WorkBooks[1].WorkSheets[SheetName];
  Sheet.Cells[Row, Col].Value:= Data;
  Sheet.Cells[Row, Col].Font.Color:= Color;
  if FontName <> '' then
    Sheet.Cells[Row, Col].Font.Name:= FontName;
  if FontSize <> 0 then
    Sheet.Cells[Row, Col].Font.Size:= FontSize;
  if FontSize <> 0 then
    Sheet.Cells[Row, Col].Font.Size:= FontSize;
  Sheet.Cells[Row, Col].Font.Bold:= fsBold in FontStyles;
  Sheet.Cells[Row, Col].Font.Underline:= fsUnderline in FontStyles;
  Sheet.Cells[Row, Col].Font.Italic:= fsItalic in FontStyles;
end;

{-------------------------------------------------------------------------}
{  ExcelClose: Close opened application                                   }
{-------------------------------------------------------------------------}

procedure ExcelClose(ExcelObject: Variant);
begin
  ExcelObject.Quit;
end;

end.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MotazCommented:
This is example of how to use previous unit to copy data from your Query to excel sheet.

procedure TForm1.BitBtn1Click(Sender: TObject);
var
 i: Integer;
 Obj: OleVariant;
begin
 Obj:= ExcelCreate('Grid');
 Query1.DisableControls;
 Query1.First;
 for i:= 0 to Query1.Fields.Count - 1 do
   ExcelPut(Obj, 'Grid', 1, i + 1, Query1.Fields[i].FieldName, '', 0, clBlue, [fsBold]);
 with Query1 do
 while not Eof do
 begin
   for i:= 0 to Fields.Count - 1 do
     ExcelPut(Obj, 'Grid', RecNo + 1, i + 1, Fields[i].AsString);
   Next;
 end;
 Query1.EnableControls;
end;

Motaz
0
ITugayCommented:
Hi Motaz,
perfect :-)
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Pandian012000Author Commented:
Hi Motaz

Thanks for the code. I tried in my program. It prints only the query fields in the excel sheet not the query records. Am I doing anything wrong. Suggestion most welcome please

Regards

Pandian
0
Pandian012000Author Commented:
Hi Motaz

Thank you, It works

regards
Pandian
0
MotazCommented:
Igor, what is your current working e-mail?
0
ITugayCommented:
Hi Motaz,
igor@aif.kz
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.