Important properties and methods of Delphi to control Excel

Important properties and methods of Delphi to control Excel

There are four ways to call excel in delphi. We choose one of them to use OleObject to load an excel worksheet to talk about the important properties and methods of delphi to control excel.

First, we give some main code steps created through OLE for a brief description:

Create an OLE object:

Var olecon: TOleContainer;

Olecon:= TOleContainer.Create(self);

Olecon.oleobject:= Olecon.CreateObject('excel.sheet',false);

Or choose to import an excel file to create an OLE object:

Olecon.oleobject:= Olecon.CreateObjectFromFile(xlsname,false);

It is best to hide several toolbars of Excel, so that it is just a table embedded in your program:

Olecon.OleObject.application.CommandBars['Standard'].Visible:=false;

Olecon.OleObject.application.CommandBars['Formatting'].Visible:=false;

Olecon.OleObject.application.CommandBars['Reviewing'].Visible:=false;

Then display and activate the excel table to the object defined by TOleContainer:

Olecon.show;

Olecon.doverb(0);

This is basically OK, but there is a bad thing about TOleContainer, which is that when you click on other controls, it loses focus and then automatically exits. In fact, it does not really exit, it just needs to activate it again. The key is When it loses focus, the excel object disappears. You can use the Timage control to cut off the area image of the EXCEL area where the TOleContainer is located to deceive users. We are not talking about this here, so we won't explain it in detail.

Next, we will start to talk about the common properties and methods of interfaces in Excel_TLB, mainly for exporting and setting report formats.

Read and write properties of a cell:

olecon.OleObject.application.cells.item[1,1];

olecon.OleObject.application.cells(1,1);

olecon.OleObject.application.cells[1,1].Value;

All three above can read and write the 'A1' unit of the worksheet.

In delphi, operations on cells (sets), areas, worksheets, etc. are implemented by Variant.

Assign the selected area in your own program to Range:

Var range,sheet:Variant;

Range:= olecon.OleObject.application.Range['A1:C3'];

or:

Sheet:= olecon.OleObject.application.Activesheet;

Range:= olecon.OleObject.application.Range[sheet.cells[1,1],sheet.cells[3,3]];

Merge cells for the above Range:

Range.merge;

Range. FormulaR 1C 1:='Merge area'; //Write text after merge

Note that in the future, the text in the merged cell is the text in the upper left corner of the merged area.

Assign the selected area in the excel table to range:

range:=excel_grid1.OleObject.application.selection;

Split cells:

Range.unmerge;

After merge, set the format of cells (sets):

Range.HorizontalAlignment:= xlCenter;// Text horizontal centering method

Range.VerticalAlignment:= xlCenter//Text vertical centering method

Range.WrapText:=true;// Text wraps automatically

Range.Borders.LineStyle:=1//Add borders

Range.Interior.ColorIndex:=39;//Fill color is lilac

Range.Font.name:='Lishu';//Font

Range.Font.Color:=clBlue;//Font color

These are the common formats, and the above are also applicable to a single cell.

Look for cells in the excel table:

Var u1,u2,u3,u4,u5:Variant;

U1:=olecon.oleobject.application.activecell;//Get the current cell;

U2:=u1.PRevious;//In non-special cases, it is the one grid on the left of u1;

U3:=ui.next;//In non-special cases, it is the grid on the right side of u2;

U4:=olecon.oleobject.application.cells[u1.cells.row-1,u1.cells.column];//No special case is the above grid

U5:=olecon.oleobject.application.cells[u1.cells.row+1,u1.cells.column];//No special case is the following grid

Delete and insert a row and a column:

Olecon.oleobject.application.rows[2].delete;

Olecon.oleobject.application.columns[2].delete;

Olecon.oleobject.application.rows[2].insert;

Olecon.oleobject.application.columns[2].insert;

Copy the specified area:

Olecon.oleobject.application.range['A1:C 3' ].copy;

Start pasting from the specified cell:

Olecon.oleobject.application.range['A 4' ].PasteSpecial;

These are the common ones, which are applicable to the EXEL controls under the server panel in delphi and the way to create EXCEL.Application COM objects.

Author: Feiya

time: 2004-9-14