Solved

Delphi & Excel

Posted on 2003-10-28
12
52,054 Views
Last Modified: 2011-08-18
In Delphi 6.0 with Excel 8.0(2000)
How I can read(or write) a value (or field value) from(in)
workBook in cells of excell.
(How I can creat a relation with excel and delphi)

Thanks
best regards
0
Comment
Question by:fhbexpir
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +5
12 Comments
 
LVL 6

Accepted Solution

by:
GloomyFriar earned 50 total points
ID: 9635071
Use TExcelApplication from "Servers" tab.
0
 
LVL 6

Expert Comment

by:GloomyFriar
ID: 9635620
0
 
LVL 1

Expert Comment

by:JackNaif
ID: 9638342
A small demo programme that show almost everything you can do:

http://www.phidels.com/php/zip/oleexcel.zip

Sources and captions are pretty self explanatory but in French...

If your French is enough to understand "ouvrir", "lire", and "fermer" you should pretty much understand all of it.

Hope it helps:

Jack
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:swift99
ID: 9638721
Look in the Excel VBA documentation.  Once an excel notebook is instantiated as a Variant, you can do anything.  I recently posted a large chunk of code that constructs a spreadsheet with formulae, named areas, constant values, and links to other worksheets from a database query.
0
 

Author Comment

by:fhbexpir
ID: 9649967
thanks for your usefull guid

But, I want to show  Excel Application in My form

and it appear into my form no other window

Thanks
best regards
0
 
LVL 1

Expert Comment

by:JackNaif
ID: 9650928
OK, then this is about the way to go:
Get a new form.
Put an OleContainer in it, call it OleContainer1.
Put also a TExcelApplication, call it xlsAp.
Put a TExcelWorkbook. Call it xlsWB.
Put a TExcelWorksheet. Call it xlsWS.

Put an edit and button to open a file.

The click event handler should be something like:



procedure TForm1.Button6Click(Sender: TObject);
var lcid: integer;
 K, R, X, Y : Integer;
 RangeMatrix : Variant;
begin
olecontainer1.CreateLinkToFile(edit1.text,false);
  { No need to start Excel ourselves, since the ExcelApplication's AutoConnect
    property is True }
  lcid := GetUserDefaultLCID;
  xlsApp.Visible[lcid]:=true;

  xlsApp.Workbooks.Open(edit1.text,null,null,null,null,null,null,null,null,null,null,null,null,lcid);
{ To open a new file:
  xlsWB.ConnectTo(xlsApp.Workbooks.Add(TOleEnum(xlWBATWorksheet), lcid)); }
  xlsWB.ConnectTo(xlsApp.Workbooks[1] as _Workbook);
  xlsWS.ConnectTo(xlsWB.Worksheets[1] as _Worksheet);
  xlsWS.Activate;

  xlsWS.Cells.SpecialCells(xlCellTypeLastCell,EmptyParam).Activate;

{ Some stuff you can do: }

  // Get the value of the last row
  X := xlsApp.ActiveCell.Row;
  // Get the value of the last column
  Y := xlsApp.ActiveCell.Column;

{ Lets say you also had a TStringGrid on the form:

  StringGrid1.Cells[0,0]:= 'X: '+inttostr(X);
  StringGrid1.Cells[1,0]:= 'Y: '+inttostr(Y);

  StringGrid1.RowCount:= Y;
  StringGrid1.ColCount:= Y;
  R := StringGrid1.RowCount;


  RangeMatrix := xlsApp.Range['A1',xlsApp.Cells.Item[X,Y]].Value;
//  Define the loop for filling in the TStringGrid
  K := 1;
  repeat
    for R := 1 to Y do
      StringGrid1.Cells[(R - 1),(K)] := RangeMatrix[K,R];
    Inc(K,1);
    StringGrid1.RowCount := K + 1;
  until K > X;

}

//  xlsWS.Range['A1','A1'].Value := 'Hello World'; //That's how to write on it.


{ Even more fun stuff: }
{

  vMSExcel.ActiveWorkbook.Worksheets[1].Range['A1'].Value := 'Hello World';
  vMSExcel.range['a2']:='11:29:42';
  vMSExcel.Selection.EntireRow.Insert;
  vMSExcel.ActiveCell.FormulaR1C1 := '1/20/2003';
  vMSExcel.Range['A2','b3'].Select;
  vMSExcel.Selection.Interior.ColorIndex := 6;

 }

end;


Just a warning: this is *sort of* the way to do it. But this code is probably flawed and will either not compile or raise exceptions. You will need to read some helps to get it to work, but I hope it wil put you on the right path. =)

Hope it helps.

Regards:

Jack
0
 

Author Comment

by:fhbexpir
ID: 9656024
Hi

very very thanks for your guids.
sorry,don't soloution my problem.
I want to show excel environment in my form
please tel me,what help should be read.

please send your E-Mail Address to send pic of my request
thanks best regards
0
 
LVL 1

Expert Comment

by:JackNaif
ID: 9657557
You want to have all the buttons and menu options of Excel in your app?
Something similar to what happens when you open an xls with Internet Explorer? (Or a pdf and you get an instance of Acrobat Reader running within IE).

I've never done that.

My guess is you do it with OLE Automation just like the last example but that you have to fiddle a little with the properties of the TOLEContainer.

The helps for TOLEContainer (for Delphi 5) mention the possibility of doing it. Here goes an excerpt from the description for the TOLEContainer. Reading about it's properties, methods and events should lead you where you're going in no time at all. (I hope...)

-------------------------------------------
Description

Use TOleContainer to handle many of the complexities of OLE 2.0. TOleContainer lets the user choose an OLE object to insert by simply calling the InsertObjectDialog method. TOleContainer can create either an embedded OLE object or a linked OLE object.

TOleContainer automatically handles menu merging -- the process of combining the container form's menu with that of an in-place activated OLE object's server application. The menu items' GroupIndex property settings control how menus are merged. Those main menu items with GroupIndex values of 0, 2, and 4 remain; TOleContainer merges the server application's menus and replaces the main menu items with GroupIndex values of 1, 3, and 5 (if they exist).

OLE objects that are activated in-place add their servers' toolbars directly into the container application's window. Normally, any panels used for toolbars are replaced by the OLE object's server's toolbars. Prevent this by setting a panel's Locked property to True.

When using TOleContainer in an SDI application (the main form's FormStyle property is fsNormal rather than fsMDIForm), place the TOleContainer component inside a panel whose Align property is set to alClient. TOleContainer replaces an SDI form's toolbars as described above, and using a panel automatically adjusts the amount of space available to the OLE container.
-------------------------------------------

If you still feel I haven't understood you correctly, you can mail me that image at <wgrillo@fi.uba.ar>

Regards:

Jack
0
 
LVL 5

Expert Comment

by:DeerBear
ID: 9660435
Hi,

I've played quite a bit with OLEContainers... :-)
If I understand where your problem is, the point
relies on the fact you don't see the File menu, right?

Unfortunately, File menu is always hidden, thus you
have to show it through automation.

HTH,

Andrew
0
 
LVL 2

Expert Comment

by:joepezt
ID: 10183270
hi, You can use ADO with delphi

Put an ADOQuery on your form
then

MyAdoQuery.Connectionstring := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyExcelFile.xls;Extended Properties=Excel 8.0';


then

MyADOQuery := 'SELECT * FROM [$sheet1]';
MyADoQuery.open;

handle data as normal
0
 

Expert Comment

by:BlueTom
ID: 10796964
uses word2000
const
FLCID = LOCALE_USER_DEFAULT;
....
  function TExlApp.OpenWorkbook(AWorkbookPath: string = '';
                              ANewWorkbook: boolean = true): IXLWorkbook;
var
    FIWorkbook := Workbooks.Add(TemplateDirPath + AWorkbookPath, FLCID);
  if (Trim(AWorkbookPath) <> '') and not(ANewWorkbook) then
    FIWorkbook := Workbooks.Open(AWorkbookPath, EmptyParam, EmptyParam,
      EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam,
      EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, FLCID);
end;
procedure wirteExcel(WorkSheetName:string;yourPar:string;RowNum,ColNum:integer)
var ISheet: _Worksheet
begin
  ISheet:=FIWorkbook.Worksheets.Item[AWorksheetName]as _Worksheet;
 ISheet.Cells.Item[RowNum,ColNum].FormulaR1C1:=yourPar;
end;

...
{you can use VBA like this,Hope it helps.}
0
 

Expert Comment

by:d43m0n
ID: 11106220
Hi
I think ADO is the best solution in this case. Unfortunatly (as I know) you can't format the cells with ADO ( I mean: background color, etc...).
With ADO you can handle excel files like TTables. You can put some ADODataSet's to a form, and the cells is like in excel.

Bye.


0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question