Board index » delphi » Can someone please help me with this??? (Excel/Delphi 4.0)

Can someone please help me with this??? (Excel/Delphi 4.0)

Hello,
I need to know the best way for me to extract data from an Excel spreadsheet
using Delphi 4.0. The problem is I have a spread sheet that looks like the
following:

COMP_ID------COMP_NAME------ SF_ID------SF_NAME-------DATACOLUMNS

I need to use Delphi to Import certain columns of data into seperate tables of
the database which is Paradox. I would like to have an "Import" button to
perform this circus act. I have done something very similar to this using VB 5
and excel but I'm not sure how to achieve this using Delphi. In VB 5, I would
CreateObject("Excel.App), then open the spreadsheet using this object. Is ther
a similar way to do this?? My last of problems is that I can't even get the
Excel driver to work using BDE.
Any "Help" you can give would be much appreciated!

Thanks in advance,
Steve

 

Re:Can someone please help me with this??? (Excel/Delphi 4.0)


Quote
SR3365 wrote:

> Hello,
> I need to know the best way for me to extract data from an Excel spreadsheet
> using Delphi 4.0. The problem is I have a spread sheet that looks like the
> following:

> COMP_ID------COMP_NAME------ SF_ID------SF_NAME-------DATACOLUMNS

> I need to use Delphi to Import certain columns of data into seperate tables of
> the database which is Paradox. I would like to have an "Import" button to
> perform this circus act. I have done something very similar to this using VB 5
> and excel but I'm not sure how to achieve this using Delphi. In VB 5, I would
> CreateObject("Excel.App), then open the spreadsheet using this object. Is ther
> a similar way to do this?? My last of problems is that I can't even get the
> Excel driver to work using BDE.
> Any "Help" you can give would be much appreciated!

> Thanks in advance,
> Steve

I don't know off the top off my head which VBA script would be neccesary
to get Excel to export the database or spreadsheet into CSV format,
(Comma Separated Values).  But if you know you could create an use a
Variant object to talk to Excel via OLE.

You could use a TStringlist objects and their LoadFromFile method, or
CommaText property (a basic assignment) to get the data into memory so
that you can create and or populate the tables.  If you saved the column
names in the spreadsheet you could use the first item in the StringList
to create the table at runtime, or skip to directly import into the
table format..

HTH

Rkr

end;
--

                   \|||/
                   /'^'\
                  ( 0 0 )
--------------oOOO--(_)--OOOo--------------
. Reid Roman                              .
. 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) home (dot) com    .
-------------------------------------------

Re:Can someone please help me with this??? (Excel/Delphi 4.0)


SR3365 wrote <19990331131747.21605.00000...@ng-fi1.aol.com>...

Quote
>Hello,
>I need to know the best way for me to extract data from an Excel
spreadsheet
>using Delphi 4.0. The problem is I have a spread sheet that looks like the
>following:

>COMP_ID------COMP_NAME------ SF_ID------SF_NAME-------DATACOLUMNS

>I need to use Delphi to Import certain columns of data into seperate tables
of
>the database which is Paradox. I would like to have an "Import" button to
>perform this circus act. I have done something very similar to this using
VB 5
>and excel but I'm not sure how to achieve this using Delphi. In VB 5, I
would
>CreateObject("Excel.App), then open the spreadsheet using this object. Is
ther
>a similar way to do this?? My last of problems is that I can't even get the
>Excel driver to work using BDE.
>Any "Help" you can give would be much appreciated!

>Thanks in advance,
>Steve

Hello

I have a component, XLSReadWrite, that reads and writes Excel XLS files
(there is no need for having Excel installed).
Take a look at http://home.swipnet.se/axolot/XLSReadWrite/xls.htm

Regards

Lars Arvidsson

Re:Can someone please help me with this??? (Excel/Delphi 4.0)


Steve,

Here's a code snippet from a conversion application I needed:  Excel
data, needed to be moved to a given MS Access database - accessed via an
ODBC connection...

I've removed some fluff from the code, but it should give you the
idea...

HTH
--Craig

// Our Excel column references...only the ones we're interested in...
const
  exCOMPANY   = 1;
  exLOCATION  = 2;
  exUNIT      = 3;
  exNEWREF    = 4;

  exOLDCODE   = 5;
  exDELETE    = 9;  // If there's a "D" or a "d" in col I then remove
the record...
  exDESC      = 10;
  exQTY       = 11;
  exINSTALL   = 16;
  exESCAL     = 18;
  exVALUATION = 19;
  exMISCCOST  = 14;

var
  i                      : Integer;
  Sheet                  : Variant;
  XLApp                  : Variant;
  szSpare, szBUID, szOut : String;
  nRecs                  : Integer;

  nBUID,nSpare, Code     : Integer;
  nData                  : Boolean;
  nRowCount              : Integer;

begin
  nRecs:=0;
  i:=StrToiNT(edtStart.Text);

  Cont:=True;

  XLApp:= CreateOleObject('Excel.Application');
  XLApp.Visible := FALSE;

  XLApp.WorkBooks.Open(szSheetPath);

  Sheet := XLApp.Workbooks[1].WorkSheets[1];

  if Edit1.Text <> '' then
    nRowCount:=StrToInt(edit1.text)
  else
    nRowCount:=GetRowCount(Sheet);

  data:=(i<=nRowCount);

  edtProgress.Text:='Started...';
  while (Data and Cont) do begin

      if cbMonitor.Checked then Application.ProcessMessages;

      szBUID := String(Sheet.Cells[i,1]);
      Val( szBUID, nBUID, Code);
      if Code <> 0 then nBuid := 0;

      szSpare := String(Sheet.Cells[i,2]);
      Val( szSpare, nSpare, Code);
      if Code <> 0 then nBuid := 0;

      szSpare := String(Sheet.Cells[i,3]);
      Val( szSpare, nSpare, Code);
      if Code <> 0 then nBuid := 0;

      // Is this a valid row?
      if (nBUID > 0) and  (nBUID < 21) then
      begin

         if (Integer(Sheet.Cells[i,exCOMPANY])  <> g_LastBUID) or
            (Integer(Sheet.Cells[i,exLOCATION]) <> g_LastLoc) or
            (Integer(Sheet.Cells[i,exUNIT])     <> g_LastUnit) Then
         begin
              g_InsertRef:=1;
         end;

        If (String(Sheet.Cells[i,exNEWREF]) = '') And
(cbInsertNewRef.Checked) Then
        Begin
           Sheet.Cells[i,exNEWREF]:=g_InsertRef;
           g_InsertRef:=g_InsertRef+1;
        End;

        if String(Sheet.Cells[i,exMISCCOST])='' then
Sheet.Cells[i,exMISCCOST]:=0;

        If LocateRecord(String(Sheet.Cells[i,exOLDCODE])) then
        Begin
             If UpperCase(String(Sheet.Cells[i,exDELETE]))='D' Then
             Begin

               if cbAmend.Checked then Listbox3.Items.Add('Delete
'+szOut);

               with dmData.tblAdnoc do
                  Delete;
             End
             Else
             Begin
                 AmendRecord(Integer(Sheet.Cells[i,exCOMPANY]),
                             Integer(Sheet.Cells[i,exLOCATION]),
                             Integer(Sheet.Cells[i,exUNIT]),
                             Integer(Sheet.Cells[i,exNEWREF]),
                             String(Sheet.Cells[i,exDESC]),
                             Real(Sheet.Cells[i,exQTY]),
                             Real(Sheet.Cells[i,exINSTALL]),
                             Real(Sheet.Cells[i,exESCAL]),
                             Real(Sheet.Cells[i,exVALUATION]),
                             Real(Sheet.Cells[i,exMISCCOST]));

                 g_LastBUID:=Integer(Sheet.Cells[i,exCOMPANY]);
                 g_LastLoc:= Integer(Sheet.Cells[i,exLOCATION]);
                 g_LastUnit:=StrToInt(String(Sheet.Cells[i,exUNIT]));
             End;
        End
        Else
        Begin
          AddNewRecord(  Integer(Sheet.Cells[i,exCOMPANY]),
                         Integer(Sheet.Cells[i,exLOCATION]),
                         Integer(Sheet.Cells[i,exUNIT]),
                         Integer(Sheet.Cells[i,exNEWREF]),
                         String(Sheet.Cells[i,exDESC]),
                         Real(Sheet.Cells[i,exQTY]),
                         Real(Sheet.Cells[i,exINSTALL]),
                         Real(Sheet.Cells[i,exESCAL]),
                         Real(Sheet.Cells[i,exVALUATION]),
                         Real(Sheet.Cells[i,exMISCCOST]));
          nRecs:=nRecs+1;

        end;

      end;

      i:=i+1;
      data:=(i<=nRowCount);

    end;

    if not VarIsEmpty(XLApp) then begin
       XLApp.DisplayAlerts := False;  // Discard unsaved files....
       XLApp.Quit;
    end;

    ShowMessage('Finished!');
end;

Re:Can someone please help me with this??? (Excel/Delphi 4.0)


Hi,
How I can get VersionInfo numbers in Delphi 4?

Jerzy Szymanski

Re:Can someone please help me with this??? (Excel/Delphi 4.0)


Look at the WIN32 API for GetFileVersionInfo() and VerQueryValue().  I have
a tip on this at my Delphi Tip of the Day webpage located at
http://members.truepath.com/delphi

--
--
Lewis Howell
lewishow...@yahoo.com
Lou's Delphi Tip of the Day:
http://members.truepath.com/delphi
Lou's personal webpage:
http://members.truepath.com/LewisHowell

Quote
Jerzy Szymanski wrote in message ...
>Hi,
>How I can get VersionInfo numbers in Delphi 4?

>Jerzy Szymanski

Other Threads