We help IT Professionals succeed at work.

Copying data from Excel to Delphi (StringGrid)

wimmeyvaert
wimmeyvaert asked
on
Hi Experts,

I'm using Delphi 5 Pro + Excel 2000
I want an application which does the following :
  - Start Excel (in background/invisible)
  - Open a specific WorkBook (variable)
  - Select a specific WorkSheet of this WorkBook (variable)
  - Copy all the data of the WorkSheet to a StringGrid in my Delphi-Application

Hope someone can get me out with this. I've already spent several hours on it, but can't get it to work.

If it is not too much asked, please give me some code too.

Thanx in advance,
The Mayor
Comment
Watch Question

Commented:
Hi wim,
try something like this:
(please note: I'm a little rusty so double-check my syntax)


function TForm1.GetExcelData(ExcelFile, ExcelSheet: Widestring): boolean;
const
  MAXROWS: integer = 100;
  MAXCOLS: integer = 40;
var
  E, wb, ws, cells: OleVariant;
  rowcount, colcount: integer;
  r, c: integer;
begin
  result := true;
  try
    E := CreateOleObject('Excel.Application');
    E.Visible := False;
    wb := E.Workbooks.Open(ExcelFile);
    ws := wb.Worksheets.Item(ExcelSheet);
    cells := ws.Cells;
    rowcount := cells.Rows.Count;
    colcount := cells.Columns.Count;
    if rowcount > MAXROWS then
      rowcount := MAXROWS;
    if colcount > MAXCOLS then
      colcount := MAXCOLS;
    cells.Resize(rowcount,colcount);

    ResizeGrid(rowcount, colcount);

    for r := 1 to rowcount do
      for c := 1 to colcount do
      begin
        StringGrid1.Cells[c,r] := cells.Item(r,c).Text;
      end;
  except
    result := false;
  end;
  E := Unassigned;
end;

procedure ResizeGrid(rs, cs: integer);
begin
  with StringGrid1 do
  begin
    FixedRows := 0;
    FixedCols := 0;
    Rowcount := 0;
    Colcount := 0;
    Rowcount := rs + 1;
    Colcount := cs + 1;
    FixedRows := 1;
    FixedCols := 1;
  end;
end;

Commented:
hi,
does this unit help :
http://www.advdelphisys.com/codelib/ads_excel_u.html

it has a function ExcelCopyToStringGrid that may be ok.

Author

Commented:
Hi rondi,

I tried your suggestion, but I get the error 'Member not found' on the command :
    ws := wb.Worksheets.Item(strWsName);

strWsName contains '999' and there is a sheet in the WB that has this name.

Any suggestions ?

To inthe,
I'm going to try out your suggestion in a minute. I'll be back.


Best regards,
The Mayor.

Commented:
Minor problem. Try this

ws := wb.Worksheets(strWsName);

or

ws := wb.Worksheets[strWsName];


The best thing to do would be to import the Excel type
library so you don't have to guess late-bound methods & props.

rondi

Author

Commented:
Hi inthe,

Do you know what what I should give as parameter 'Excel' ?

I get an EOleVariantError.

Thanx,
The Mayor.


------------------------------

Hi rondi,

Still getting the same 'Member Not found' error.
I'm getting depressed now ;-)

The Mayor.

Author

Commented:
Ho Rondi,

What do you mean by 'Importing the Excel Type Library' ?

Commented:
If you're using D3-6 and you've got Office installed, you can open your project and in Delphi, click Import type library. from the list of installed libraries, choose Microsoft Excel 10 Object Library (or 9 depending on Office version).

Delphi will create a .pas file that has the Excel object
definitions so you can see what you can and can't call.`
Hi guys,


Looks like I found a way to get it to work :


procedure TfrmBeheerZendNota.sbtnImportFromExcelClick(Sender: TObject);
var
  strWBName     : String;
  strWsName     : String;
  WorkBk        : _WorkBook;
  WorkSheet     : _WorkSheet;
  K, R, X, Y    : Integer;
  intX          : Integer;
  RangeMatrix   : Variant;
  blnSheetExist : Boolean;
begin
  { Initialise some variables. }
  strWBName := 'MyWorkBook';
  strWsName := 'Test';
  { Start Excel-Connection }
  XLApp.Connect;
  // Open the Excel File
  XLApp.WorkBooks.Open(strWBName,EmptyParam,EmptyParam,EmptyParam,EmptyParam,
                       EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,
                       EmptyParam,EmptyParam,EmptyParam,0);
  WorkBk := XLApp.WorkBooks.Item[1];
  blnSheetExist := False;
  intX:=1;
  While intX <= XLApp.Worksheets.Count do
  begin
    WorkSheet := WorkBk.Worksheets.Get_Item(intX) as _WorkSheet;
    if WorkSheet.Name <> strWsName then
      Inc(IntX)
    else
    begin
      blnSheetExist := True;
      Break;
    end;
  end;

  if not blnSheetExist then
  begin
    MessageDlg('No Sheet exist with name : ' + strWsName, mtError, [mbOK], 0);
  end
  else
  begin
    WorkSheet := WorkBk.Worksheets.Get_Item(intX) as _WorkSheet;
    // In order to know the dimension of the WorkSheet, i.e the number of rows and the
    // number of columns, we activate the last non-empty cell of it
    WorkSheet.Activate(intX);
    WorkSheet.Cells.SpecialCells(xlCellTypeLastCell,EmptyParam).Activate;
    // Get the value of the last row + column
    X := XLApp.ActiveCell.Row;
    Y := XLApp.ActiveCell.Column;
    // Define the number of the columns in the TStringGrid
    StringGrid1.ColCount := Y;
    // Assign the Variant associated with the WorkSheet to the Delphi Variant Matrix
    RangeMatrix := XLApp.Range['A1',XLApp.Cells.Item[X,Y]].Value;
    // Quit Excel and Disconnect the Server
    XLApp.Workbooks.Close(1);
    XLApp.Quit;
    XLApp.Disconnect;
    //  Define the loop for filling in the TStringGrid
    K := 1;
    repeat
      for R := 1 to Y do
        StringGrid1.Cells[(R - 1),(K - 1)] := RangeMatrix[K,R];
      Inc(K,1);
      StringGrid1.RowCount := K + 1;
    until K > X;
    // Unassign the Delphi Variant Matrix
    RangeMatrix := Unassigned;
  end;
end;

Since I haven't used anything of you suggestions, I'm going to delete this question. I hop you don't mind guys.
Anyway, thank you very much for you fast replies.

The Mayor.

Commented:
Hello everyone,

If the Experts don't object, how about I refund your points and move this to the PAQ as a zero point question for future reference wimmeyvaert?

darinw
Roving Moderator

Author

Commented:
Fine by me ...
wimmeyvaert:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.

Author

Commented:
I just asked to refund my points since I gave the answer myself ...
Best regards,

The Mayor.

Author

Commented:
I just asked to refund my points since I gave the answer myself ...
Best regards,

The Mayor.