Board index » delphi » Embed excel worksheet into form

Embed excel worksheet into form

1. Is there a way to embed an excel workbook or worksheet into a D5 form,
instead of having the appearance of a new Excel instance pop-up on the
user's screen?

2.  How does one format an Excel cell, numerically? so as to have currencies
display with comma and 2 decimal places, and set conditional format
parameters (eg bold if less that -$1million for ex)?

 

Re:Embed excel worksheet into form


<<J Smith:
1. Is there a way to embed an excel workbook or worksheet
into a D5 form,

Quote

Yes, you can use a TOleContainer component for that.

<<J Smith:
2.  How does one format an Excel cell, numerically?

Quote

Set the Numberformat property (or Formatconditions, for
conditional formatting) of the range.

For example, if WS is your worksheet:

var
  FC: FormatCondition;
..
  WS.Range['B1', 'B1'].NumberFormat := '#.##0,##0';
  WS.Range['A1', 'A1'].NumberFormat := 'dd.mm.yyyy';
  WS.Range['G4', 'G4'].EntireColumn.NumberFormat :=    
'#,##0.00_);[Red](#,##0.00)';
  WS.Range['A1', 'B1'].EntireColumn.NumberFormat :=
'General';

  FC := WS.Range['B1','B1'].FormatConditions.Add(
     xlCellValue, xlGreater, '=$A$1', EmptyParam);
  FC.Interior.Color := clRed;

--
Deborah Pate (TeamB) http://delphi-jedi.org

  TeamB don't see posts sent via Google or ISPs
  Use the real Borland server: newsgroups.borland.com
  http://www.borland.com/newsgroups/genl_faqs.html

Re:Embed excel worksheet into form


Thanks.  I tried the OLE container approach and it created a separate
instance of excel outside the form.  That instance became unresponsive as
soon as I treid to minimize it, btw.  What I am really trying to get at is
data I can access only through DDE, and Delphi's DDE's components don't seem
to work reliably.  I was going to DDE the data into Excel and do a
Cell.values "look" from D5.  Any better ideas?

Thanks again.

"Deborah Pate (TeamB)" <d.p...@blueyonder.co.not-this-bit.uk> wrote in
message news:VA.00001cb1.01409f0a@blueyonder.co.not-this-bit.uk...

Quote
> <<J Smith:
> 1. Is there a way to embed an excel workbook or worksheet
> into a D5 form,

> Yes, you can use a TOleContainer component for that.

> <<J Smith:
> 2.  How does one format an Excel cell, numerically?

> Set the Numberformat property (or Formatconditions, for
> conditional formatting) of the range.

> For example, if WS is your worksheet:

> var
>   FC: FormatCondition;
> ..
>   WS.Range['B1', 'B1'].NumberFormat := '#.##0,##0';
>   WS.Range['A1', 'A1'].NumberFormat := 'dd.mm.yyyy';
>   WS.Range['G4', 'G4'].EntireColumn.NumberFormat :=
> '#,##0.00_);[Red](#,##0.00)';
>   WS.Range['A1', 'B1'].EntireColumn.NumberFormat :=
> 'General';

>   FC := WS.Range['B1','B1'].FormatConditions.Add(
>      xlCellValue, xlGreater, '=$A$1', EmptyParam);
>   FC.Interior.Color := clRed;

> --
> Deborah Pate (TeamB) http://delphi-jedi.org

>   TeamB don't see posts sent via Google or ISPs
>   Use the real Borland server: newsgroups.borland.com
>   http://www.borland.com/newsgroups/genl_faqs.html

Re:Embed excel worksheet into form


<<J Smith:
I tried the OLE container approach and it created a
separate instance of excel outside the form.

Quote

If you have AllowActiveDoc and AllowInPlace set to true,
the workbook should open inside the TOlecontainer.

--
Deborah Pate (TeamB) http://delphi-jedi.org

  TeamB don't see posts sent via Google or ISPs
  Use the real Borland server: newsgroups.borland.com
  http://www.borland.com/newsgroups/genl_faqs.html

Re:Embed excel worksheet into form


I tried what you suggested and it works!  2 next steps:  1. there is no
excel menu, just the toolbars and buttons that appear on the form.  How do I
get an excel menu on the form?  2.  how do I enter and retrieve
values/formulas from the excel cells in the olecontainer?

Thank you.

JS

"Deborah Pate (TeamB)" <d.p...@blueyonder.co.not-this-bit.uk> wrote in
message news:VA.00001cb6.00351e15@blueyonder.co.not-this-bit.uk...

Quote
> <<J Smith:
> I tried the OLE container approach and it created a
> separate instance of excel outside the form.

> If you have AllowActiveDoc and AllowInPlace set to true,
> the workbook should open inside the TOlecontainer.

> --
> Deborah Pate (TeamB) http://delphi-jedi.org

>   TeamB don't see posts sent via Google or ISPs
>   Use the real Borland server: newsgroups.borland.com
>   http://www.borland.com/newsgroups/genl_faqs.html

Re:Embed excel worksheet into form


Excellent and thank you for the superfast reply!

JS

"Deborah Pate (TeamB)" <d.p...@blueyonder.co.not-this-bit.uk> wrote in
message news:VA.00001cb9.0117c15e@blueyonder.co.not-this-bit.uk...

Quote
> <<J Smith:
> 1. there is no excel menu

> Pop a TMainMenu on your form and the Excel menu will merge
> with it.

> <<J. Smith:
> 2.  how do I enter and retrieve values/formulas from the
> excel cells in the olecontainer?

> The Olecontainer's OleObject property gives you access to
> the Excel Workbook object. So you can do things like this:
> var
>   WB, WS: OleVariant;

>   WB := Olecontainer1.Oleobject;
>   WS := WB.ActiveSheet;
>   Caption := WS.Cells.Item[1, 2].Value;
>   WS.Range['A1'] := 42;
>   WS.Range['B1:B2'].Interior.Color := clRed;
>   WS.Cells.Item[1, 2].Value := 'Try this';

> --
> Deborah Pate (TeamB) http://delphi-jedi.org

>   TeamB don't see posts sent via Google or ISPs
>   Use the real Borland server: newsgroups.borland.com
>   http://www.borland.com/newsgroups/genl_faqs.html

Re:Embed excel worksheet into form


<<J Smith:
1. there is no excel menu

Quote

Pop a TMainMenu on your form and the Excel menu will merge
with it.

<<J. Smith:
2.  how do I enter and retrieve values/formulas from the
excel cells in the olecontainer?

Quote

The Olecontainer's OleObject property gives you access to
the Excel Workbook object. So you can do things like this:
var
  WB, WS: OleVariant;

  WB := Olecontainer1.Oleobject;
  WS := WB.ActiveSheet;
  Caption := WS.Cells.Item[1, 2].Value;
  WS.Range['A1'] := 42;
  WS.Range['B1:B2'].Interior.Color := clRed;
  WS.Cells.Item[1, 2].Value := 'Try this';

--
Deborah Pate (TeamB) http://delphi-jedi.org

  TeamB don't see posts sent via Google or ISPs
  Use the real Borland server: newsgroups.borland.com
  http://www.borland.com/newsgroups/genl_faqs.html

Re:Embed excel worksheet into form


Sorry just another thing.  How do I display the Excel sheet in the
olecontained without the associated excel toolbars and buttons putting
themselves on my delphi form?  as I mentioned earlier, the only reason I'm
using excel in my form is as a way to access some dde data (as a workaround
for the delphi dde components performing erratically).

Thanks.

JS

"Deborah Pate (TeamB)" <d.p...@blueyonder.co.not-this-bit.uk> wrote in
message news:VA.00001cb9.0117c15e@blueyonder.co.not-this-bit.uk...

Quote
> <<J Smith:
> 1. there is no excel menu

> Pop a TMainMenu on your form and the Excel menu will merge
> with it.

> <<J. Smith:
> 2.  how do I enter and retrieve values/formulas from the
> excel cells in the olecontainer?

> The Olecontainer's OleObject property gives you access to
> the Excel Workbook object. So you can do things like this:
> var
>   WB, WS: OleVariant;

>   WB := Olecontainer1.Oleobject;
>   WS := WB.ActiveSheet;
>   Caption := WS.Cells.Item[1, 2].Value;
>   WS.Range['A1'] := 42;
>   WS.Range['B1:B2'].Interior.Color := clRed;
>   WS.Cells.Item[1, 2].Value := 'Try this';

> --
> Deborah Pate (TeamB) http://delphi-jedi.org

>   TeamB don't see posts sent via Google or ISPs
>   Use the real Borland server: newsgroups.borland.com
>   http://www.borland.com/newsgroups/genl_faqs.html

Re:Embed excel worksheet into form


"Deborah Pate (TeamB)" <d.p...@blueyonder.co.not-this-bit.uk> wrote in
message news:VA.00001cb6.00351e15@blueyonder.co.not-this-bit.uk...

Quote
> <<J Smith:
> I tried the OLE container approach and it created a
> separate instance of excel outside the form.

> If you have AllowActiveDoc and AllowInPlace set to true,
> the workbook should open inside the TOlecontainer.

I'm lurking on this thread. I have two panels, a Main menu. One panel
aligned top, the other aligned client. In the one aligned client, I palce a
TOleContainer, also aligned client. When I open the Excel sheet, top panel
disappears (sheet covers the whole form).

Robert

Re:Embed excel worksheet into form


<<Robert Kaplan:
When I open the Excel sheet, top panel
disappears (sheet covers the whole form).

Quote

Excel's toolbars will replace the top panel unless you set the
panel's Locked property to True.

--
Deborah Pate (TeamB) http://delphi-jedi.org

  Use Borland servers; TeamB don't see posts via ISPs
  http://www.borland.com/newsgroups/genl_faqs.html

Re:Embed excel worksheet into form


Quote
> The Olecontainer's OleObject property gives you access to
> the Excel Workbook object. So you can do things like this:
> var
>   WB, WS: OleVariant;

>   WB := Olecontainer1.Oleobject;
>   WS := WB.ActiveSheet;
>   Caption := WS.Cells.Item[1, 2].Value;
>   WS.Range['A1'] := 42;
>   WS.Range['B1:B2'].Interior.Color := clRed;
>   WS.Cells.Item[1, 2].Value := 'Try this';

Deborah,

How do I get a remote object *into* an ole container?

For example, if I create an object on a remote server, how do I
display it in a container and communicate with the object?

Other Threads