Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Export query output to excel format

Posted on 2001-07-02
7
Medium Priority
?
230 Views
Last Modified: 2010-04-04
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
0
Comment
Question by:Pandian012000
  • 3
  • 2
  • 2
7 Comments
 
LVL 7

Accepted Solution

by:
Motaz earned 75 total points
ID: 6247347
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
 
LVL 7

Expert Comment

by:Motaz
ID: 6247354
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
 
LVL 9

Expert Comment

by:ITugay
ID: 6248022
Hi Motaz,
perfect :-)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Pandian012000
ID: 6248462
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
 

Author Comment

by:Pandian012000
ID: 6248623
Hi Motaz

Thank you, It works

regards
Pandian
0
 
LVL 7

Expert Comment

by:Motaz
ID: 6251260
Igor, what is your current working e-mail?
0
 
LVL 9

Expert Comment

by:ITugay
ID: 6251902
Hi Motaz,
igor@aif.kz
0

Featured Post

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question