Board index » delphi » Exporting Data in Spreadsheet Format

Exporting Data in Spreadsheet Format

Hi!

My database has thousands of records and I need to export certain
information from selected records in a Lotus 123 or Excel format.

How on earth do I go about doing that!  Do I need a component and  where
can I find it?  I don't need extensive spreadsheet formatting just
arranging certain data in columns and rows as .XLS or .WK1

Any advice or help is appreciated.

regards, John
--
John Matthews

 

Re:Exporting Data in Spreadsheet Format


Quote
John Matthews wrote:

> Hi!

> My database has thousands of records and I need to export certain
> information from selected records in a Lotus 123 or Excel format.

> How on earth do I go about doing that!  Do I need a component and  where
> can I find it?  I don't need extensive spreadsheet formatting just
> arranging certain data in columns and rows as .XLS or .WK1

If you export the data to a .DBF format, Excel will open just fine
like this:

var  
   ExcelApp : Variant;
begin
   Application.ProcessMessages;
   ExcelApp:= CreateOleObject('Excel.Application');
   ExcelApp.Visible := False;

   try
      ExcelApp.WorkBooks.Open('C:\Data\Exported\11-19-1998.dbf');

The export could be easily achieved with the TBatchMove component.

Hope that helps

Rkr
--
                   \|||/
                   /'^'\
                  ( 0 0 )
--------------oOOO--(_)--OOOo--------------
. Reid Roman                              .
. Delphi Programmer / Analyst             .
. TVisualBasic:=class(None)               .
. May the Source be With You              .
-------------------------------------------
. Auto-By-Tel (http://www.autobytel.com)  .
. Irvine, CA U.S.A                        .
. E-Mail : rkroman (at) pacbell (dot) net .
. or reidr (at) autobytel (dot) com       .
-------------------------------------------

Re:Exporting Data in Spreadsheet Format


Dear John, (no it's not a dear John letter)

can you contact me at bostjan.pogla...@writeme.com

i could use some help with dealing with databases.

Quote
John Matthews wrote in message ...
>Hi!

>My database has thousands of records and I need to export certain
>information from selected records in a Lotus 123 or Excel format.

>How on earth do I go about doing that!  Do I need a component and  where
>can I find it?  I don't need extensive spreadsheet formatting just
>arranging certain data in columns and rows as .XLS or .WK1

>Any advice or help is appreciated.

>regards, John
>--
>John Matthews

Re:Exporting Data in Spreadsheet Format


Hi!

But I don't want to open Excel. I haven't even got it on my computer.  I
just need to provide some of my information in Excel format for someone
else.

In article <3654390A.217...@home.com>, Reid Roman <rkro...@home.com>
writes

Quote

>If you export the data to a .DBF format, Excel will open just fine
>like this:

--
John Matthews

Re:Exporting Data in Spreadsheet Format


Write all the data to an ascii-file.
Place a tab between each field.
Save the ascii file with the extention .XLS

double-clicking on this file from explorer will open Excel

HTH

Freddy Biets
h...@village.uunet.be

John Matthews heeft geschreven in bericht ...

Quote
>Hi!

>My database has thousands of records and I need to export certain
>information from selected records in a Lotus 123 or Excel format.

>How on earth do I go about doing that!  Do I need a component and  where
>can I find it?  I don't need extensive spreadsheet formatting just
>arranging certain data in columns and rows as .XLS or .WK1

>Any advice or help is appreciated.

>regards, John
>--
>John Matthews

Re:Exporting Data in Spreadsheet Format


In article <733dii$7b...@xenon.inbe.net>, Jos Houtmeyers
<h...@village.uunet.be> writes

Quote
>Write all the data to an ascii-file.
>Place a tab between each field.
>Save the ascii file with the extention .XLS

>double-clicking on this file from explorer will open Excel

Are you sure?  That is not the case with Lotus123.  The extension opens
up Lotus 123 but you get an invalid file report and no file is opened.

rgds, john
--
John Matthews

Re:Exporting Data in Spreadsheet Format


Quote
John Matthews wrote:

> Hi!

> My database has thousands of records and I need to export certain
> information from selected records in a Lotus 123 or Excel format.

For D3 and D4 versions, search the VCI Formula One Library from the
Delphi OCX subdirectory.

For D1 version, the same Formula One, Excel spreadsheet tool, is available
as a VBX component.

Markku Nevalainen

Re:Exporting Data in Spreadsheet Format


Quote
John Matthews wrote:

> In article <733dii$7b...@xenon.inbe.net>, Jos Houtmeyers
> <h...@village.uunet.be> writes
> >Write all the data to an ascii-file.
> >Place a tab between each field.
> >Save the ascii file with the extention .XLS

> >double-clicking on this file from explorer will open Excel

> Are you sure?  That is not the case with Lotus123.  The extension opens
> up Lotus 123 but you get an invalid file report and no file is opened.

> rgds, john
> --
> John Matthews

Well, you could also write the information to a TStringList, then write
out the CommaText property, then you'll have a comma-delimted file.  I
seem to remember (but my memory isn't always accurate!) that
tab-delimited is a MicroSoft thing, and comma-delimited files are more
common.  Give it a try anyway, and good luck.

Re:Exporting Data in Spreadsheet Format


In article <krvPCEA69CV2E...@johnmatthews.demon.co.uk>, John Matthews
wrote:
Quote
> My database has thousands of records and I need to export certain
> information from selected records in a Lotus 123 or Excel format.

Use cr/lf separated lines, with tab-separated columns, in a TXT file.  
Excel can import that just fine.

--
Fri, 20 Nov 1998 14:52 EST
Jim O'Brien, UnitOOPS Software
unito...@remove-this-prefix.unitoops.com
Check out our OLE Drag and Drop Components at http://www.unitoops.com/

Re:Exporting Data in Spreadsheet Format


On Thu, 19 Nov 1998 14:47:22 +0000, John Matthews

Quote
<J...@johnmatthews.demon.co.uk> wrote:
>Hi!

>My database has thousands of records and I need to export certain
>information from selected records in a Lotus 123 or Excel format.

>How on earth do I go about doing that!  Do I need a component and  where
>can I find it?  I don't need extensive spreadsheet formatting just
>arranging certain data in columns and rows as .XLS or .WK1

>Any advice or help is appreciated.

>regards, John
>--
>John Matthews

John,

I just save the results of an SQL as a .db file and then open it using
Quattro. I assume that Excel will have a simmilar capability.

Save1.execute;                                   //save file dialog

Result.TableName := Save1.FileName;
                             //set the result table from SQL filename
Screen.Cursor := crHourglass;

Move1.execute ;                   //use a batch move to populate table

Screen.Cursor := crDefault;

If Excel can't use a paradox table try setting the result table to
dbf or ASCII.

For end users you might try a query builing utility like EZQuery ( I
like it ).

Denis Riviere
RM Comp.

Re:Exporting Data in Spreadsheet Format


Hi again !

I am not explaining things well at all am I ?  
You have given many suggestions for creating comma delimited, tab
delimited, ascii files, CDR's. Perhaps all of these can be double-
clicked in explorer and Excel will jump into life......
............but I have to provide an excel spreadsheet format file.  

Not something close (which, I agree, will do in most cases).  I want to
produce a 21 carat xls file in Excel spreadsheet format.(I have to
provide this information every day under contract)

At present I export an ASCII file with comma delimiters. Open up
Lotus123 and save the file in XLS format.

All I am trying to do is avoid the ascii/Lotus step and export directly
from Delphi.  Is there a component that can do this?

My thanks for your assistance and my apologies for not explaining more
clearly beforehand.

Quote
>Use cr/lf separated lines, with tab-separated columns, in a TXT file.  
>Excel can import that just fine.

--
John Matthews

Re:Exporting Data in Spreadsheet Format


In article <krvPCEA69CV2E...@johnmatthews.demon.co.uk>, John Matthews

Quote
<J...@johnmatthews.demon.co.uk> writes:
>My database has thousands of records and I need to export certain
>information from selected records in a Lotus 123 or Excel format.

>How on earth do I go about doing that!  Do I need a component and  where
>can I find it?  I don't need extensive spreadsheet formatting just
>arranging certain data in columns and rows as .XLS or .WK1

>Any advice or help is appreciated.

Below is a procedure I used for putting database data on the clipboard or into
a separated file :-

procedure TDHLOFForm.CopyData(Sep : char; ToClip, ToFile : boolean);
{puts the data onto the clipboard and/or into a file for
 transfer of the data displayed}
var
  DataMemStream : TMemoryStream;
  TempString, FileName : string;
const
  Null : char = #0;
  CRLF : string[2] = #13#10;
begin
   if ToFile then begin
    with DirSelForm do begin      // gets file path name etc
      FileEdit.Text := SelDataTitle + '.DAT';
      {select file and directory}
      ShowModal;
      if not ModalResult = mrOK then
        Exit;
      if TabRadioBtn.Checked then
        Sep := #9
      else
        Sep := ',';
    end; {with DirSelForm do}
  end;  {if ToFile}
  DataMemStream := TMemoryStream.Create;
   with DataMemStream do begin
    Seek(0, 0);
    {write header line}
    TempString := 'Salut' + Sep + 'Inits' + Sep
                  + 'SName' + Sep + 'Addr1' + Sep
                  + 'Addr2' + Sep + 'Addr3' + Sep
                  + 'Town' + Sep + 'County' + Sep
                  +  'PostCode';
    TempString := TempString + Sep + SelDataTitle + CRLF;
    Write(TempString[1], Length(TempString));
    {go to the beginning of the data and write all records of selected data}
    MembList.First;
    while not MembList.EOF do begin
      case Seldata of
        {different salutation and inits depending on type displayed}
        sdAll, sdMembers : TempString := MembListSALUT.AsString + Sep
                                         + MembListINITS.AsString + Sep;
        sdCommittee :  TempString := MembListC_SALUT.AsString + Sep
                                     + MembListC_INITS.AsString + Sep;
        sdTrustee : TempString := MembListT_SALUT.AsString + Sep
                                  + MembListT_INITS.AsString + Sep;
      end;
      Write(TempString[1], Length(TempString));
      TempString := MembListSNAME.AsString + Sep;
        Write(TempString[1], Length(TempString));
      TempString := MembListADDR1.AsString + Sep
                    + MembListADDR2.AsString + Sep
                    + MembListADDR3.AsString + Sep;
        Write(TempString[1], Length(TempString));
      TempString := MembListTOWN.AsString + Sep
                    + MembListCOUNTY.AsString + Sep
                    + MembListPOSTCODE.AsString + CRLF;
        Write(TempString[1], Length(TempString));
      MembList.Next;
    end; {while not EOF}
    if ToClip then begin
      {end with a #0 for use as a PChar}
      Write(Null, 1);
      {put on clipboard}
      ClipBoard.SetTextBuf(DataMemStream.Memory);
    end;
    if ToFile then
      SaveToFile(DirSelForm.FileEdit.Text);
    Free;
  end; {with DataMemStream do}
end;

Alan Lloyd
alangll...@aol.com

Re:Exporting Data in Spreadsheet Format


In article <iGGeZCAe6vV2E...@johnmatthews.demon.co.uk>, John Matthews
wrote:
Quote
> All I am trying to do is avoid the ascii/Lotus step and export directly
> from Delphi.  Is there a component that can do this?

Sure.  Formula One.  There's a demo version of it installed with Delphi.
Or you could just do it using Excel, using OLE automation.

--
Sat, 21 Nov 1998 17:21 EST
Jim O'Brien, UnitOOPS Software
unito...@remove-this-prefix.unitoops.com
Check out our OLE Drag and Drop Components at http://www.unitoops.com/

Other Threads