Accessing Excel Cells through Ole2 - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Accessing Excel Cells through Ole2

zibadianzibadian Posts: 6,349Member
How can you access an excel cell, when the book is loaded into the TOleContainer? I need to know the properties and methods of the Excel.Sheet.8 Ole2 interface.

Comments

  • softmansoftman Posts: 157Member
    : How can you access an excel cell, when the book is loaded into the TOleContainer? I need to know the properties and methods of the Excel.Sheet.8 Ole2 interface.
    :
    Hi!
    [code]
    OleContainer1.OleObject.ActiveSheet.Cells(1,1) := 'HELLO';
    [/code]

    I hope I did not misunderstood your question, and that's what you are looking for!
    \///
    /O O
    | | |
    | _ |
    ___/
    SoftMan

  • zibadianzibadian Posts: 6,349Member
    : : How can you access an excel cell, when the book is loaded into the TOleContainer? I need to know the properties and methods of the Excel.Sheet.8 Ole2 interface.
    : :
    : Hi!
    : [code]
    : OleContainer1.OleObject.ActiveSheet.Cells(1,1) := 'HELLO';
    : [/code]
    :
    : I hope I did not misunderstood your question, and that's what you are looking for!
    : \///
    : /O O
    : | | |
    : | _ |
    : ___/
    : SoftMan
    :
    :
    That's almost what I was looking for. The problem is that it needs to be type-cast, and this gives an EIntfCastError with the message 'Interface not supported'. I already tried to create a type-library unit for the Excel.Sheet.8 interface, but I cannot find the correct type-library. The MSDK doesn't provide any usable answers either.
  • softmansoftman Posts: 157Member
    : : : How can you access an excel cell, when the book is loaded into the TOleContainer? I need to know the properties and methods of the Excel.Sheet.8 Ole2 interface.
    : : :
    : : Hi!
    : : [code]
    : : OleContainer1.OleObject.ActiveSheet.Cells(1,1) := 'HELLO';
    : : [/code]
    : :
    : : I hope I did not misunderstood your question, and that's what you are looking for!
    : : \///
    : : /O O
    : : | | |
    : : | _ |
    : : ___/
    : : SoftMan
    : :
    : :
    : That's almost what I was looking for. The problem is that it needs to be type-cast, and this gives an EIntfCastError with the message 'Interface not supported'. I already tried to create a type-library unit for the Excel.Sheet.8 interface, but I cannot find the correct type-library. The MSDK doesn't provide any usable answers either.
    :
    Hi!

    I'v tried read and write different type of data to the cells, but all worked fine:
    [code]
    procedure TForm1.Button1Click(Sender: TObject);
    var
    s : string;
    begin
    OleContainer1.DoVerb(ovShow);
    s := OleContainer1.OleObject.ActiveSheet.Cells[1,1];
    s := InputBox('TEXT','>',s);
    OleContainer1.OleObject.ActiveSheet.Cells[2,1] := s;
    OleContainer1.OleObject.ActiveSheet.Cells[3,1] := 'HELLO';
    OleContainer1.OleObject.ActiveSheet.Cells[4,1] := 16384;
    OleContainer1.OleObject.ActiveSheet.Cells[5,1] := '=sum(A1:D1)';
    ShowMessage(OleContainer1.OleObject.ActiveSheet.Cells[3,1]);
    ShowMessage(IntTOStr(OleContainer1.OleObject.ActiveSheet.Cells[4,1]));
    ShowMessage(FloatToStr(OleContainer1.OleObject.ActiveSheet.Cells[5,1]));
    end;

    [/code]

    A little sapmle code would help me to understand the question.
    SoftMan
  • zibadianzibadian Posts: 6,349Member
    : : : : How can you access an excel cell, when the book is loaded into the TOleContainer? I need to know the properties and methods of the Excel.Sheet.8 Ole2 interface.
    : : : :
    : : : Hi!
    : : : [code]
    : : : OleContainer1.OleObject.ActiveSheet.Cells(1,1) := 'HELLO';
    : : : [/code]
    : : :
    : : : I hope I did not misunderstood your question, and that's what you are looking for!
    : : : \///
    : : : /O O
    : : : | | |
    : : : | _ |
    : : : ___/
    : : : SoftMan
    : : :
    : : :
    : : That's almost what I was looking for. The problem is that it needs to be type-cast, and this gives an EIntfCastError with the message 'Interface not supported'. I already tried to create a type-library unit for the Excel.Sheet.8 interface, but I cannot find the correct type-library. The MSDK doesn't provide any usable answers either.
    : :
    : Hi!
    :
    : I'v tried read and write different type of data to the cells, but all worked fine:
    : [code]
    : procedure TForm1.Button1Click(Sender: TObject);
    : var
    : s : string;
    : begin
    : OleContainer1.DoVerb(ovShow);
    : s := OleContainer1.OleObject.ActiveSheet.Cells[1,1];
    : s := InputBox('TEXT','>',s);
    : OleContainer1.OleObject.ActiveSheet.Cells[2,1] := s;
    : OleContainer1.OleObject.ActiveSheet.Cells[3,1] := 'HELLO';
    : OleContainer1.OleObject.ActiveSheet.Cells[4,1] := 16384;
    : OleContainer1.OleObject.ActiveSheet.Cells[5,1] := '=sum(A1:D1)';
    : ShowMessage(OleContainer1.OleObject.ActiveSheet.Cells[3,1]);
    : ShowMessage(IntTOStr(OleContainer1.OleObject.ActiveSheet.Cells[4,1]));
    : ShowMessage(FloatToStr(OleContainer1.OleObject.ActiveSheet.Cells[5,1]));
    : end;
    :
    : [/code]
    :
    : A little sapmle code would help me to understand the question.
    : SoftMan
    :
    Last time I didn't call the DoVerb(). I just accessed the ActiveSheet using the line you gave me in an OnClick(). My goal is to access the Excel sheet behind the scenes, invisible to the user. I'm currently in the test fase of this, so all the code I have are a few OnClick()s to load a sheet and try some ideas using the OleContainer. This is the first time I'm attempting such a program, and Ole2 has always been poorly documentented in the help files. And I haven't been able to find some good online source, which I can use.
  • softmansoftman Posts: 157Member
    : : : : : How can you access an excel cell, when the book is loaded into the TOleContainer? I need to know the properties and methods of the Excel.Sheet.8 Ole2 interface.
    : : : : :
    : : : : Hi!
    : : : : [code]
    : : : : OleContainer1.OleObject.ActiveSheet.Cells(1,1) := 'HELLO';
    : : : : [/code]
    : : : :
    : : : : I hope I did not misunderstood your question, and that's what you are looking for!
    : : : : \///
    : : : : /O O
    : : : : | | |
    : : : : | _ |
    : : : : ___/
    : : : : SoftMan
    : : : :
    : : : :
    : : : That's almost what I was looking for. The problem is that it needs to be type-cast, and this gives an EIntfCastError with the message 'Interface not supported'. I already tried to create a type-library unit for the Excel.Sheet.8 interface, but I cannot find the correct type-library. The MSDK doesn't provide any usable answers either.
    : : :
    : : Hi!
    : :
    : : I'v tried read and write different type of data to the cells, but all worked fine:
    : : [code]
    : : procedure TForm1.Button1Click(Sender: TObject);
    : : var
    : : s : string;
    : : begin
    : : OleContainer1.DoVerb(ovShow);
    : : s := OleContainer1.OleObject.ActiveSheet.Cells[1,1];
    : : s := InputBox('TEXT','>',s);
    : : OleContainer1.OleObject.ActiveSheet.Cells[2,1] := s;
    : : OleContainer1.OleObject.ActiveSheet.Cells[3,1] := 'HELLO';
    : : OleContainer1.OleObject.ActiveSheet.Cells[4,1] := 16384;
    : : OleContainer1.OleObject.ActiveSheet.Cells[5,1] := '=sum(A1:D1)';
    : : ShowMessage(OleContainer1.OleObject.ActiveSheet.Cells[3,1]);
    : : ShowMessage(IntTOStr(OleContainer1.OleObject.ActiveSheet.Cells[4,1]));
    : : ShowMessage(FloatToStr(OleContainer1.OleObject.ActiveSheet.Cells[5,1]));
    : : end;
    : :
    : : [/code]
    : :
    : : A little sapmle code would help me to understand the question.
    : : SoftMan
    : :
    : Last time I didn't call the DoVerb(). I just accessed the ActiveSheet using the line you gave me in an OnClick(). My goal is to access the Excel sheet behind the scenes, invisible to the user. I'm currently in the test fase of this, so all the code I have are a few OnClick()s to load a sheet and try some ideas using the OleContainer. This is the first time I'm attempting such a program, and Ole2 has always been poorly documentented in the help files. And I haven't been able to find some good online source, which I can use.
    :


    I'v already created some projects, using excel as an ole object, but without OleContainer. Here I post a code of a component I'v written some time ago. It export's the data of a TDataset, behind the scenes. Not well commented, but maybe you can get some information of it.

    [code]
    unit ExportToExcel;

    interface

    uses
    SysUtils, Classes, db , dbtables, dialogs, Windows, ComObj;
    type TProgressChange = procedure(sender:TObject;Progress:Integer) of object;
    type
    TExportToExcel = class(TComponent)
    private
    FDataSet: TDataSet;
    FPrompt: Boolean;
    FAutoQuit: Boolean;
    FAutoFit: Boolean;
    FOnProgressChange: TProgressChange;
    Running : boolean;
    FVisibleAfter: Boolean;
    procedure SetDataSet(const Value: TDataSet);
    procedure SetPrompt(const Value: Boolean);
    procedure ExportExcelTable(excel:variant);
    procedure SetAutoQuit(const Value: Boolean);
    procedure SetAutoFit(const Value: Boolean);
    procedure SetOnProgressChange(const Value: TProgressChange);
    procedure SetVisibleAfter(const Value: Boolean);
    { Private declarations }
    protected
    { Protected declarations }
    public
    procedure ExportToExcel(filename:string);
    function ExcelAvaliable:boolean;
    procedure Stop;
    { Public declarations }
    published
    property DataSet : TDataSet read FDataSet write SetDataSet;
    property Prompt : Boolean read FPrompt write SetPrompt default true;
    property AutoQuit : Boolean read FAutoQuit write SetAutoQuit default false;
    property AutoFit : Boolean read FAutoFit write SetAutoFit default true;
    property OnProgressChange : TProgressChange read FOnProgressChange write SetOnProgressChange;
    property VisibleAfter : Boolean read FVisibleAfter write SetVisibleAfter;
    { Published declarations }
    end;

    procedure Register;

    implementation

    procedure Register;
    begin
    RegisterComponents('OSoft', [TExportToExcel]);
    end;

    { TExportToExcel }

    function TExportToExcel.ExcelAvaliable: boolean;
    var
    Excel : Variant;
    begin
    result := false;
    try
    Excel := CreateOleObject('Excel.Application');
    except
    exit;
    end;
    result := true;
    end;

    procedure TExportToExcel.ExportExcelTable(excel: variant);
    var
    i,rc,rcnt,trs,rs : integer;
    begin
    running := true;
    with DataSet do
    begin
    rs := 0;
    rcnt := RecordCount+2;
    Disablecontrols;
    for i:=0 to fields.Count-1 do
    begin
    excel.cells(1,1+i) := fields[i].FieldName;
    end;
    first;
    rc := 2;
    while not eof do
    begin
    for i:=0 to fields.Count-1 do
    begin
    excel.cells(rc,1+i) := fields[i].AsString;
    end;
    next;
    inc(rc);
    trs := trunc(rc/rcnt*100);
    if rs<>trs then
    begin
    rs := trs;
    if Assigned(FOnProgressChange) then FOnProgressChange(self,rs);
    end;
    if running = false then
    begin
    EnableControls;
    exit;
    end;
    end;
    EnableControls;
    end;


    end;

    procedure TExportToExcel.ExportToExcel(filename: string);
    var
    excel : Variant;
    begin
    if FDataSet=nil then exit;
    if FileName='' then exit;
    if FDataSet.Active=false then
    begin
    ShowMessage('Nothing to export!'+#13+#10+'The table is closed!');
    exit;
    end;
    if FileExists(FileName) then
    if Prompt then
    if MessageBox(0,'The file already exists, overwrite it?','Warning',1) <> 1 then
    exit;
    try
    Excel := CreateOleObject('Excel.Application');
    except
    ShowMessage('The excel application is not avaliable!');
    exit;
    end;
    Excel.Workbooks.Add;
    if not FVisibleAfter then Excel.Application.Visible := true;
    ExportExcelTable(Excel);
    if FAutoFit then
    begin
    Excel.Cells.Select;
    Excel.Cells.EntireColumn.AutoFit;
    Excel.Range['1:1'].Select;
    Excel.Selection.Font.Bold := True;
    Excel.Selection.Font.Italic := True;
    Excel.Range['A1'].Select;
    end;
    if FVisibleAfter then Excel.Application.Visible := true;
    Excel.ActiveSheet.SaveAs(Filename);
    if FAutoQuit then Excel.Quit;
    end;

    procedure TExportToExcel.SetAutoFit(const Value: Boolean);
    begin
    FAutoFit := Value;
    end;

    procedure TExportToExcel.SetAutoQuit(const Value: Boolean);
    begin
    FAutoQuit := Value;
    end;

    procedure TExportToExcel.SetDataSet(const Value: TDataSet);
    begin
    FDataSet := Value;
    end;

    procedure TExportToExcel.SetOnProgressChange(const Value: TProgressChange);
    begin
    FOnProgressChange := Value;
    end;

    procedure TExportToExcel.SetPrompt(const Value: Boolean);
    begin
    FPrompt := Value;
    end;

    procedure TExportToExcel.SetVisibleAfter(const Value: Boolean);
    begin
    FVisibleAfter := Value;
    end;

    procedure TExportToExcel.Stop;
    begin
    running := false;
    end;

    end.

    [/code]

    SoftMan
  • zibadianzibadian Posts: 6,349Member
    : : : : : : How can you access an excel cell, when the book is loaded into the TOleContainer? I need to know the properties and methods of the Excel.Sheet.8 Ole2 interface.
    : : : : : :
    : : : : : Hi!
    : : : : : [code]
    : : : : : OleContainer1.OleObject.ActiveSheet.Cells(1,1) := 'HELLO';
    : : : : : [/code]
    : : : : :
    : : : : : I hope I did not misunderstood your question, and that's what you are looking for!
    : : : : : \///
    : : : : : /O O
    : : : : : | | |
    : : : : : | _ |
    : : : : : ___/
    : : : : : SoftMan
    : : : : :
    : : : : :
    : : : : That's almost what I was looking for. The problem is that it needs to be type-cast, and this gives an EIntfCastError with the message 'Interface not supported'. I already tried to create a type-library unit for the Excel.Sheet.8 interface, but I cannot find the correct type-library. The MSDK doesn't provide any usable answers either.
    : : : :
    : : : Hi!
    : : :
    : : : I'v tried read and write different type of data to the cells, but all worked fine:
    : : : [code]
    : : : procedure TForm1.Button1Click(Sender: TObject);
    : : : var
    : : : s : string;
    : : : begin
    : : : OleContainer1.DoVerb(ovShow);
    : : : s := OleContainer1.OleObject.ActiveSheet.Cells[1,1];
    : : : s := InputBox('TEXT','>',s);
    : : : OleContainer1.OleObject.ActiveSheet.Cells[2,1] := s;
    : : : OleContainer1.OleObject.ActiveSheet.Cells[3,1] := 'HELLO';
    : : : OleContainer1.OleObject.ActiveSheet.Cells[4,1] := 16384;
    : : : OleContainer1.OleObject.ActiveSheet.Cells[5,1] := '=sum(A1:D1)';
    : : : ShowMessage(OleContainer1.OleObject.ActiveSheet.Cells[3,1]);
    : : : ShowMessage(IntTOStr(OleContainer1.OleObject.ActiveSheet.Cells[4,1]));
    : : : ShowMessage(FloatToStr(OleContainer1.OleObject.ActiveSheet.Cells[5,1]));
    : : : end;
    : : :
    : : : [/code]
    : : :
    : : : A little sapmle code would help me to understand the question.
    : : : SoftMan
    : : :
    : : Last time I didn't call the DoVerb(). I just accessed the ActiveSheet using the line you gave me in an OnClick(). My goal is to access the Excel sheet behind the scenes, invisible to the user. I'm currently in the test fase of this, so all the code I have are a few OnClick()s to load a sheet and try some ideas using the OleContainer. This is the first time I'm attempting such a program, and Ole2 has always been poorly documentented in the help files. And I haven't been able to find some good online source, which I can use.
    : :
    :
    :
    : I'v already created some projects, using excel as an ole object, but without OleContainer. Here I post a code of a component I'v written some time ago. It export's the data of a TDataset, behind the scenes. Not well commented, but maybe you can get some information of it.
    :
    : [code]
    : unit ExportToExcel;
    :
    : interface
    :
    : uses
    : SysUtils, Classes, db , dbtables, dialogs, Windows, ComObj;
    : type TProgressChange = procedure(sender:TObject;Progress:Integer) of object;
    : type
    : TExportToExcel = class(TComponent)
    : private
    : FDataSet: TDataSet;
    : FPrompt: Boolean;
    : FAutoQuit: Boolean;
    : FAutoFit: Boolean;
    : FOnProgressChange: TProgressChange;
    : Running : boolean;
    : FVisibleAfter: Boolean;
    : procedure SetDataSet(const Value: TDataSet);
    : procedure SetPrompt(const Value: Boolean);
    : procedure ExportExcelTable(excel:variant);
    : procedure SetAutoQuit(const Value: Boolean);
    : procedure SetAutoFit(const Value: Boolean);
    : procedure SetOnProgressChange(const Value: TProgressChange);
    : procedure SetVisibleAfter(const Value: Boolean);
    : { Private declarations }
    : protected
    : { Protected declarations }
    : public
    : procedure ExportToExcel(filename:string);
    : function ExcelAvaliable:boolean;
    : procedure Stop;
    : { Public declarations }
    : published
    : property DataSet : TDataSet read FDataSet write SetDataSet;
    : property Prompt : Boolean read FPrompt write SetPrompt default true;
    : property AutoQuit : Boolean read FAutoQuit write SetAutoQuit default false;
    : property AutoFit : Boolean read FAutoFit write SetAutoFit default true;
    : property OnProgressChange : TProgressChange read FOnProgressChange write SetOnProgressChange;
    : property VisibleAfter : Boolean read FVisibleAfter write SetVisibleAfter;
    : { Published declarations }
    : end;
    :
    : procedure Register;
    :
    : implementation
    :
    : procedure Register;
    : begin
    : RegisterComponents('OSoft', [TExportToExcel]);
    : end;
    :
    : { TExportToExcel }
    :
    : function TExportToExcel.ExcelAvaliable: boolean;
    : var
    : Excel : Variant;
    : begin
    : result := false;
    : try
    : Excel := CreateOleObject('Excel.Application');
    : except
    : exit;
    : end;
    : result := true;
    : end;
    :
    : procedure TExportToExcel.ExportExcelTable(excel: variant);
    : var
    : i,rc,rcnt,trs,rs : integer;
    : begin
    : running := true;
    : with DataSet do
    : begin
    : rs := 0;
    : rcnt := RecordCount+2;
    : Disablecontrols;
    : for i:=0 to fields.Count-1 do
    : begin
    : excel.cells(1,1+i) := fields[i].FieldName;
    : end;
    : first;
    : rc := 2;
    : while not eof do
    : begin
    : for i:=0 to fields.Count-1 do
    : begin
    : excel.cells(rc,1+i) := fields[i].AsString;
    : end;
    : next;
    : inc(rc);
    : trs := trunc(rc/rcnt*100);
    : if rs<>trs then
    : begin
    : rs := trs;
    : if Assigned(FOnProgressChange) then FOnProgressChange(self,rs);
    : end;
    : if running = false then
    : begin
    : EnableControls;
    : exit;
    : end;
    : end;
    : EnableControls;
    : end;
    :
    :
    : end;
    :
    : procedure TExportToExcel.ExportToExcel(filename: string);
    : var
    : excel : Variant;
    : begin
    : if FDataSet=nil then exit;
    : if FileName='' then exit;
    : if FDataSet.Active=false then
    : begin
    : ShowMessage('Nothing to export!'+#13+#10+'The table is closed!');
    : exit;
    : end;
    : if FileExists(FileName) then
    : if Prompt then
    : if MessageBox(0,'The file already exists, overwrite it?','Warning',1) <> 1 then
    : exit;
    : try
    : Excel := CreateOleObject('Excel.Application');
    : except
    : ShowMessage('The excel application is not avaliable!');
    : exit;
    : end;
    : Excel.Workbooks.Add;
    : if not FVisibleAfter then Excel.Application.Visible := true;
    : ExportExcelTable(Excel);
    : if FAutoFit then
    : begin
    : Excel.Cells.Select;
    : Excel.Cells.EntireColumn.AutoFit;
    : Excel.Range['1:1'].Select;
    : Excel.Selection.Font.Bold := True;
    : Excel.Selection.Font.Italic := True;
    : Excel.Range['A1'].Select;
    : end;
    : if FVisibleAfter then Excel.Application.Visible := true;
    : Excel.ActiveSheet.SaveAs(Filename);
    : if FAutoQuit then Excel.Quit;
    : end;
    :
    : procedure TExportToExcel.SetAutoFit(const Value: Boolean);
    : begin
    : FAutoFit := Value;
    : end;
    :
    : procedure TExportToExcel.SetAutoQuit(const Value: Boolean);
    : begin
    : FAutoQuit := Value;
    : end;
    :
    : procedure TExportToExcel.SetDataSet(const Value: TDataSet);
    : begin
    : FDataSet := Value;
    : end;
    :
    : procedure TExportToExcel.SetOnProgressChange(const Value: TProgressChange);
    : begin
    : FOnProgressChange := Value;
    : end;
    :
    : procedure TExportToExcel.SetPrompt(const Value: Boolean);
    : begin
    : FPrompt := Value;
    : end;
    :
    : procedure TExportToExcel.SetVisibleAfter(const Value: Boolean);
    : begin
    : FVisibleAfter := Value;
    : end;
    :
    : procedure TExportToExcel.Stop;
    : begin
    : running := false;
    : end;
    :
    : end.
    :
    : [/code]
    :
    : SoftMan
    :

    Thanx. I'm sure I will figure it out.
Sign In or Register to comment.