Solved

Copy data from DBGrid into Excel

Posted on 1998-07-22
21
3,229 Views
Last Modified: 2010-05-18
Hi

Could anyone advise of a simple way to copy the results of a query into Excel so that they can be translated using Excel's graph facilities.

I need to know how to select all the resultant records generated by the query, and then how to copy them or export them into Excel.

Thanks in advance

Regards John
0
Comment
Question by:tam97
  • 10
  • 6
  • 3
  • +2
21 Comments
 
LVL 8

Accepted Solution

by:
ZifNab earned 250 total points
Comment Utility
Hi John,

 we could use OLE to transfer the data to Excel. (Or the older method DDE, but I prefer OLE, b'cose it's the newer method).

Starting OLE communication is just one line, transferring data is as easy as a snip...

Then we could iterate throught the result query and just transfer everything to Excel.

Well, I can give you some more information about this :

ExcelApp: Variant;

To open Ole connection :

ExcelApp:= CreateOleObject('Excel.Application');
ExcelApp.Visible := True;

To Close OLE connection :

ExcelApp.Quit;

To send data to active cell in Excel :

try
  ExcelApp.ActiveCell := 1; {active cell is filled with number 1}
 except
  on E:EOleSysError do begin
   showmessage('OLESysError '+E.Message+IntToStr(E.ErrorCode));
  end
   else showmessage('unknown error, please report : ');
 end;

BUT I advise you to also look at these fine components which already do your stuff and have some extra features. If you need more advise or want to make your own component, just say it and we'll help you.

BUT look at them first! They are really learnfull!

{ export table, query, stringgrid to excel }
http://sunsite.icm.edu.pl/delphi/ftp/d20free/oleexcel.zip


{ export query to word, excel }
http://sunsite.icm.edu.pl/delphi/ftp/d20free/qexport.zip
which needs http://sunsite.icm.edu.pl/delphi/ftp/free/excel.zip

Zif.
0
 
LVL 8

Expert Comment

by:ZifNab
Comment Utility
forgot to mention something about selecting result of the query...

That 's easy too :

1. Query1.First
2. Copy all the fields of the record to the Excel sheet.
3. Query1.Next
4. Same as 2.
5. Repeat Until Query1.EOF

Delphi code :
var sheet: variant;
    i,j : integer;

begin
 Sheet := XLApp.Workbooks[1].WorkSheets['Delphi Data'];
 Query1.First;
 j := 0;
 While not Query1.EOF do begin
  i := 0;
  {iterate through your fields of record}
   begin    
    Sheet.Cells[j, i] := {field value};
    inc(i);
   end;
  Query1.Next;
  inc(j);
 end;
end;

look for more at the examples I gave you.

Kind regards, Zif.
0
 
LVL 5

Expert Comment

by:ronit051397
Comment Utility
I have already answered this question, see:
http://www.experts-exchange.com/topics/comp/lang/delphi/Q.10045961
0
 

Author Comment

by:tam97
Comment Utility
Hi Zifnab

Thanks for your reply I'll check it out and get back to you.

Rgds John
0
 
LVL 4

Expert Comment

by:jeurk
Comment Utility
You know your stuff with OleExcell Zif ;)
0
 
LVL 1

Expert Comment

by:venks
Comment Utility
Dear tam97
For this I generally use Formula1 components that ships with delphi 3 on the activexpage.
Simply copy the results of the query to the formula1 cells and then use the f1book method savefiledlg as given below.

Syntax

F1Book1.SaveFileDlg pTitle, pBuf, pFileType

Part      Type      Description
pTitle      String      Title of the dialog box. Use a null string for the default title.
pBuf      String      Variable, passed by reference, that receives the returned name by which the workbook is saved.
pFileType      Integer      Variable, passed by reference, that receives the returned file type selected when the file is saved. Following are the F1FileTypeConstants:
Constants      Description
F1FileFormulaOne      Formula One format
F1FileExcel4      Excel 4.0 format
F1FileExcel5      Excel 5.0 format
F1FileFormulaOne3      Formula One v.3 format
Remarks
For more details refer to formula1 help.

regards
venks
The Save As dialog box allows you to save and name a file.
0
 

Author Comment

by:tam97
Comment Utility
Hi Venks

Nice to hear from you again.

Will have a look at the Formula1 suggestion and get back to you.

Rgds John
0
 

Author Comment

by:tam97
Comment Utility
Hi Venks

Had a look at the Formula 1 help and component, it seems a bit complex to me (I am a bit of a beginner).

Would it be possible to provide sample code for say a query that selects all from a table into the formula 1 component.

It may make it easier for me to understand.

Rgds John
0
 

Author Comment

by:tam97
Comment Utility
Hi Zif

I tried your coding and understand what its trying to achieve, but I came against some compiling problems.

CreateOleObject('Excel.Application');
Sheet := XLApp.Workbooks[1].WorkSheets['Delphi Data'];
Sheet.Cells[j, i] := {field value};

There were the area's that throw a wobbly, it's more than likely due to my lack of understanding.

I downloaded the Excel and qexport zips, but had a major problem loading them into Delphi, the Vcl30.dpl file kept getting corrupted, maybe its because my system is using Windows 98 and the disk is 32 bit converted, who knows.

Any ideas on the coding problems stated above.

Thanks again

Rgds John
0
 
LVL 8

Expert Comment

by:ZifNab
Comment Utility
Hi tam97,

 Ok, I see, some problems, because I forgot to say that you needed to have a sheet already called 'Delphi pages'. Well, since you're a beginner I better give you an URL of a guy who wrote a great article about this stuff. I've you've read it and still got some problems, just ask!

http://members.aol.com/charliecal/

got to OLE automation: Delphi, Word and Excel.

Happy reading! It's clearly written!

Zif.

Hi Jeurk! ;-)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:tam97
Comment Utility
Hi Zif

Thanks again for your patience with me, I'll download the article you have suggested.

Looks like I know what my bedtime reading is for tonight!.

Many regards,

John
0
 

Author Comment

by:tam97
Comment Utility
Hi Zif

Read through the article on OLE automation it was very helpful.

I now have a better understanding of what to do, so thanks for all your help, please accept the 250 points.

By the way I'd like to finish all the examples in the article but can not locate XLConst.dcu on my system, any ideas where I can get this file, it seems to be required for example 3 when it uses ComObj and XLConst.

Thanks again.

John
0
 
LVL 8

Expert Comment

by:ZifNab
Comment Utility
Hi John,

About XLConst, I believe you can download it from Charlies page too (they are included with the examples), or ... you can make it yourself. How to do this, you may ask?, well the explenation is also written in the article :

Finding the Constants Used in Excel

You can determine all the constants used by Excel by reading its type library. There are at least two simple ways to read a type library.

   1.You can read the type library with a third party tool, such as the OleView application that ships with the Microsoft SDK.
   2.You can ask Delphi to read the library for you, and to translate the information stored in the library into Object Pascal. Obviously, this is the preferred
     technique.

I have included the translations of the Excel and Word type libraries with this article. However, if you want to create your own versions of these libraries, then you
can select Project | Import Type Library from the Delphi menu, and then select the appropriate type library. A Delphi translation of the type library will be created
automatically. (Be sure you are using Delphi 3.01 or later when importing the type library, as some important fixes were made to this technology between Delphi 3.0
and Delphi 3.01.)

There will be a number of warnings at the top of the type library, but you can ignore them. Most of these warnings have to do with name conflicts. For instance, if
Excel uses an Object Pascal keyword in its code, then we need to make an alias for that keyword. For instance, Excel uses End in several places. This is a Delphi
keyword, and so we append a space on to the end of it, converting the word End to End_. This puts an end to name conflicts.

The files you want to import usually have either a TLB or EXE extension. When working with Office 97, however, you want one with an OLB extension. The file to
use with Word is MSWORD8.OLB, and the one to use with Excel is EXCEL8.OLB. On my system, I found these entries in the …\Microsoft Office\Office
directory.

The Pascal translations of the interfaces to all the objects used in Excel or Word are found in the files created by importing EXCEL8.OLB and MSWORD8.OLB.
Throughout this part of the paper, I will ignore these interfaces, and show you how to work directly with variant objects. However, in Part II of the paper I will
return to this subject, and show you how to work with interfaces. At that time, I will present a discussion of the relative merits of working with variants and
interfaces.

Regards, Zif.

Have a nice day!
0
 
LVL 1

Expert Comment

by:venks
Comment Utility
Dear tam97
Try the following component for exporting the resulys of a query to  a table.I just found it and hope you find it useful.
I am not further writing about the formula 1 since most of the functions are a little complex and frankly I never like OCXs.
If you want an example for this let me know your e mail address and I will post it to you
Venks -----------------------------------------------------------------{ An "Export Query" component for Delphi32.                                            }
{ Copyright 1996, Jean-Fabien Connault.  All Rights Reserved.                          }
{ This component can be freely used and distributed in commercial and private          }
{ environments, provided this notice is not modified in any way.                       }
{ -------------------------------------------------------------------------------------}
{ Feel free to contact me if you have any questions, comments or suggestions at        }
{   JFConnault@mail.dotcom.fr (Jean-Fabien Connault)                                   }
{ You can always find the latest version of this component at:                         }
{   http://www.worldnet.net/~cycocrew/delphi/                                          }
{ -------------------------------------------------------------------------------------}
{ Date last modified:  01/28/97                                                        }
{ -------------------------------------------------------------------------------------}

{ -------------------------------------------------------------------------------------}
{ TQExport v1.01                                                                       }
{ -------------------------------------------------------------------------------------}
{ Description:                                                                         }
{   A component that allows you to export the result of a Query to a Word              }
{   document or an Excel sheet.                                                        }
{ Properties:                                                                          }
{   property FileName: String;                                                         }
{   property Query: TQuery;                                                            }
{ Procedures and functions:                                                            }
{   function ExportWord:boolean;                                                       }
{   function ExportExcel:boolean;                                                      }
{ Needs:                                                                               }
{   Excels package version 2.0 from Tibor F. Liska (liska@sztaki.hu)                   }
{          and Stefan Hoffmeister (Stefan.Hoffmeister@Uni-Passau.de)                   }
{                                                                                      }
{ See example contained in example.zip file for more details.                          }
{ -------------------------------------------------------------------------------------}
{ Revision History:                                                                    }
{ 1.00:  + Initial release                                                             }
{ 1.01:  + Fixed to use Excels package version 2.0                                     }
{ -------------------------------------------------------------------------------------}

unit QExport;

interface

uses
  SysUtils, WinTypes, WinProcs, Messages, Classes, Graphics, Controls,
  Forms, Dialogs, DBTables, OleAuto, Excels, LibConst;

type
  TQExport = class(TComponent)
  private
    { Private-déclarations }
    {$IFNDEF INSTALLED}
    FExcel: TAdvExcel;
    {$ENDIF}
    FFileName: String;
    FQuery: TQuery;
  protected
    { Protected-déclarations }
  public
    { Public-déclarations }
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    function ExportWord:boolean;
    function ExportExcel:boolean;
  published
    { Published-déclarations }
    property FileName: String read FFileName write FFileName;
    property Query: TQuery read FQuery write FQuery;
  end;

procedure Register;

implementation


procedure Register;
begin
  RegisterComponents(LoadStr(srDAccess), [TQExport]);
end;

constructor TQExport.Create(AOwner: TComponent);
begin
  FExcel := TAdvExcel.create(self);
  inherited Create(AOwner);
end;

destructor TQExport.Destroy;
begin
  FExcel.free;
  inherited Destroy;
end;

{*****************************************************************************}
{* FUNCTION ExportWord                                                       *}
{*****************************************************************************}

function TQExport.ExportWord: boolean;
var
  S, Lang: string;
  MSWord: Variant;
  L,i: Integer;
begin
 { OLE Automation }
 result := true;
 try
    MsWord := CreateOleObject('Word.Basic');
 except
    ShowMessage('Could not start Microsoft Word.');
    result := false;
    Exit;
 end;

  { Détermination du langage utilisé }
  try
    Lang := MsWord.AppInfo(16);
  except
   MessageDlg('Cette version de Microsoft Word n''est pas supportée.',mtInformation,[mbOk],0);
   result := false;
   Exit;
  end;
  //Form1.Caption := Lang;

  { Requête }
  with FQuery do
   begin
    L := 0;
    { Construction de la chaine (titre) }
    for i := 0 to FQuery.FieldCount - 1 do
    begin
     S := S + FQuery.Fields[i].Fieldname;
     if i <> FQuery.FieldCount - 1 then  S := S + ';';
    end;
    S := S + #13;
    inc(L);

    Close;
    Open;
    try
     First;
     while not EOF do
      begin
       { Construction de la chaine (valeurs) }
       for i := 0 to FQuery.FieldCount - 1 do
        begin
         S := S + (FQuery.Fields[i]).AsString;
         if i <> FQuery.FieldCount - 1 then  S := S + ';';
        end;
       S := S + #13;

       Inc(L);
       Next;
      end;

      { Langue anglaise }
      if (Lang = 'English (US)') or (Lang = 'English (UK)') then
      begin
       MsWord.AppShow;
       MSWord.FileNew;
       MSWord.Insert(S);
       MSWord.LineUp(L, 1);
       MSWord.TextToTable(ConvertFrom := 2, NumColumns := FQuery.FieldCount);
       MSWord.FileSaveAs(FFileName, 0);
      end;

      { Langue française }
      if Lang = 'Français' then
      begin
       MsWord.FenAppAfficher;
       MsWord.FichierNouveau;
       MSWord.Insertion(S);
       MSWord.LigneVersHaut(L, 1);
       MSWord.TexteEnTableau(ConvertirDe := 2, NbColonnesTableau := FQuery.FieldCount);
       MSWord.FichierEnregistrerSous(FFileName, 0);
      end;

     finally
      Close;   // Close Query
    end;
  end;

end;

{*****************************************************************************}
{* FUNCTION ExportExcel                                                      *}
{*****************************************************************************}

function TQExport.ExportExcel: boolean;
var
  MSExcel: Variant;
  i, L, Row, Column: integer;
  S: string;
begin

 result := true;

 try
    MsExcel := CreateOleObject('Excel.Application');
 except
    ShowMessage('Could not start Microsoft Excel.');
    result := false;
    Exit;
 end;


 { Tentative de connexion à Excel }
 try
  FExcel.Connect;   { Same as Excel.Connected := True; }
 except
  result := false;
  Exit;
 end;

 FExcel.StartTable;        { Create new workbook }

 with FQuery do
  begin
   L := 1;
   { Construction de la chaine (titre) }
   for i := 0 to FQuery.FieldCount - 1 do
   begin
    S:= FQuery.Fields[i].Fieldname;
    Row := L;
    Column := i + 1;
    try
     FExcel.PutStrAt(Row, Column, S);
    except
     //ShowMessage('Error with string "'+S+'"');
    end;
   end;
   inc(L);

   Close;
   Open;
   try
    while not EOF do
     begin
      { Construction de la chaine (valeurs) }
      for i := 0 to FQuery.FieldCount - 1 do
       begin
        S := (FQuery.Fields[i]).AsString;
        Row := L;
        Column := i + 1;
        try
         FExcel.PutStrAt(Row, Column, S);
        except
         //ShowMessage('Error with string "'+S+'"');
        end;
       end;
       inc(L);
       Next;
     end;
    finally
     Close;  // Close Query
    end;
  end;

 FExcel.EndTable;          { Show excel table }

 FExcel.Exec('[SAVE.AS("' + FFileName + '";1;"";FALSE;FALSE)]');
 FExcel.Exec('[CLOSE(FALSE)]');
 FExcel.Exec('[QUIT]');

end;



end.

0
 
LVL 8

Expert Comment

by:ZifNab
Comment Utility
Hi venks,

believe it's the same component as  http://sunsite.icm.edu.pl/delphi/ftp/d20free/qexport.zip

Kind Regards, Zif.
0
 

Author Comment

by:tam97
Comment Utility
Thanks Guys for your replies.

Venks

I'm sure this is the component I downloaded which cause my system to hang when installed, I'll check it out again because at the time I was loading umpteen components at a time.

Zif

I imported the type libraries at the time I don't know if I appened the Pascal reserved word End, I'll give it another go.

By the way you guys have some amount of patience, it's much appreciated.

Rgds John
0
 

Author Comment

by:tam97
Comment Utility
Venks & Zif

Checked out your last comments.

Venks
Loaded Excels no problem, but QExport (the one that would really help) won't load due to the absnce of LibConst.dcu, any ideas ?, did you get yours to load OK and if so where the $%&6!! is LibConst.dcu.

Thanks for your help.

Zif
I tried again with the type library, discovered I'm on version 3.00 not 3.01 as the author stated was required, maybe I could download an upgrade patch, I'll try the Borland site.

Failing that I'll become a bus driver instead !

The one drawback to the net being worldwide is that if you two guys were helping me in my home town I'd buy you a drink or three, still if you ever drop into Scotland let me know.

Rgds John
0
 
LVL 8

Expert Comment

by:ZifNab
Comment Utility
Hi tam97, mmm drinks.... and in Scotland.... mmmmm.

Ok, About 3.01, you've to buy it! Yep, strange not?
When you've got 3.01 download 3.02 (this one = free!) as well.

Zif.
0
 
LVL 1

Expert Comment

by:venks
Comment Utility
Dear tam97
Same problem out here.Even you have lots of patience trynig out all our suggestions.
The file libconst.dcu is not traceable.I will let you know if something else comes up.
Venks
0
 

Author Comment

by:tam97
Comment Utility
Hi Zif

Thanks for your reply.

I'll have to save up my pennies to buy the updated version of Delphi, I think I'll wait till Delphi 4 is established.

Cheers
John
0
 

Author Comment

by:tam97
Comment Utility
Hi Venks

Thanks for your reply.

I'll try contact the author of the component as it seems as if this would do the job required.

If I get any info I'll let you know, by the way venks are you from Holland ? I'm sure your've helped me before.

Cheers
John
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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…
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now