Delphi control Excel

1. Create an Excel file

To control Excel in Delphi, OLE Automation is required. Ole2 is generally used to create an OLE object. When an OLE object is activated, the server program is only activated within the container program. This is called in-place activation ).

When creating an Excel file, create an OLE object and then create a worksheet in the object, as shown in the createexcel function:

Function createexcel: variant;

VaR

V: variant;

Sheet: variant;

Begin

V: = createoleobject ('excel. application'); // create an OLE object

V. Visible: = true;

V. workbooks. Add (-4167); // Add a worksheet

V. workbooks [1]. Sheets [1]. Name: = 'test ';

Sheet: = V. workbooks [1]. Sheets ['test'];

Return V;

End;

2. Data Table Control

Excel table control mainly includes data import and modification, cell merge and border control, and table copy and paste. When the report format is certain, it is particularly important to copy and paste tables. In this way, you can create a file template and then output multiple pages of reports as needed.

(1) import data (importdata)

Procedure importdata;

VaR

I, J: integer;

V: variant;

Begin

V: = createexcel; // create an Excel file test

For I: = 0 to maxcolumn do

Begin

For J: = 0 to maxrow do

V. workbooks [1]. Sheets [1]. cells [I, j]: = I * j; // import data

End;

End;

(2) cell merging and border control (linestylecontrol)

Cells are merged when the merging range is selected. Border control allows you to check whether border lines are displayed. Other control methods can follow the process below.

Procedure linestylecontrol;

VaR

V, sheet, range: variant;

Begin

V: = createexecl;

Sheet: = V. workbooks [1]. Sheets [1];

Range: = sheet. Range [sheet. cells [], sheet. cells []; // select a table

Range. Select;

Range. merge; // merge cells.

Range. Borders. linestyle: = xlcontinuous; // set the border to visible.

Range. Font. Size: = 9; // change the text font size in the table.

End;

4. Example
Procedure tform1.button1click (Sender: tobject );
VaR cell1, cell2, cell3, cell4, range1, range2: variant;
Excelapplication, sheet1: variant;
Begin
Try
Excelapplication: = createoleobject ('excel. application ');
Except
Showmessage ('Sorry, no Excel ');
Abort;
End;
Excelapplication. Visible: = true;
Excelapplication. workbooks. Add (xlwbatworksheet );
Sheet1: = excelapplication. workbooks [1]. worksheets ['sheet1'];
Sheet1.name: = 'delphi control Excel chart ';
Sheet1.cells. item [1, 1]: = 'excel chart-example ';
Sheet1.cells. item [2, 1]: = 'Week ';
Sheet1.cells. item [2, 2]: = 'monday ';
Sheet1.cells. item [2, 3]: = 'tuesday ';
Sheet1.cells. item [2, 4]: = 'weday ';
Sheet1.cells. item [2, 5]: = 'thurs ';
Sheet1.cells. item [2, 6]: = 'Friday -';
Sheet1.cells. item [2, 7]: = 'saturday ';
Sheet1.cells. item [2, 8]: = 'sunday ';
Sheet1.cells. item [3, 1]: = 'quantity sold ';
Sheet1.cells. item [2, 3]: = 115;
Sheet1.cells. item [3, 3]: = 112;
Sheet1.cells. item [3, 4]: = 156;
Sheet1.cells. item [3, 5]: = 148;
Sheet1.cells. item [3, 6]: = 132;
Sheet1.cells. item [3, 7]: = 196;
Sheet1.cells. item [3, 8]: = 162;
Cell1: = sheet1.cells. item [2, 2];
Cell2: = sheet1.cells. item [2, 8];
Cell3: = sheet1.cells. item [3, 2];
Cell4: = sheet1.cells. item [3, 8];
Range1: = sheet1.range [cell1, cell2]; // you can specify the value range of a chart parameter.
Range2: = sheet1.range [cell3, cell4]; // sets the value range of the Base Value of the chart.
Range1.borders. Color: = 27;
Range2.borders. Color: = 6;
// Add an internal table to the worksheet. The four data points in the add method indicate the left distance between the table A1 and the internal table. degrees of parallelism and height ﹔
Sheet1.chartobjects. Add (10, 60,500,280 );
Sheet1.chartobjects [1]. Activate; // activate the forward table
Sheet1.chartobjects [1]. Chart. charttype: = xl3dcolumnclustered; // specify the sequence table type: the vertical sequence set directly
Sheet1.chartobjects [1]. Chart. seriescollection. Add [range2]; // create a new data example
Sheet1.chartobjects [1]. Chart. seriescollection [1]. Values: = range2; // specify the new data value
Sheet1.chartobjects [1]. Chart. seriescollection [1]. hasdatalabels: = true; // displays the data standards of the Data columns in the tables Table ﹔
Sheet1.chartobjects [1]. Chart. Axes [xlvalue]. minimumscale: = 100; // sets the minimum scale value of the base parameter ﹔
Sheet1.chartobjects [1]. Chart. Axes [xlvalue]. maximumscale: = 200; // you can specify the maximum scale value of the base parameter ﹔
Sheet1.chartobjects [1]. Chart. Axes [xlvalue]. majorunit: = 10; // specify the primary single-digit ratio of a specific data base ﹔
Sheet1.chartobjects [1]. Chart. Axes [xlvalue]. minorunit: = 10; // sets the secondary single-bit sequence of the data base ﹔
Sheet1.chartobjects [1]. Chart. Axes [xlcategory]. hastitle: = true;
Sheet1.chartobjects [1]. Chart. Axes [xlcategory]. axistitle. Text: = 'Week'; // other standard labels.
Sheet1.chartobjects [1]. Chart. Axes [xlcategory]. categorynames: = range1;
Sheet1.chartobjects [1]. Chart. haslegend: = false; // The example is not shown.
Sheet1.chartobjects [1]. Chart. chartarea. Fill. Visible: = true; // fill in the upper field in the lower table.
Sheet1.chartobjects [1]. Chart. chartarea. Fill. forecolor. schemecolor: = 28; // foreground color watermark ﹔
Sheet1.chartobjects [1]. Chart. chartarea. Fill. backcolor. schemecolor: = 42; // background color watermark ﹔
Sheet1.chartobjects [1]. Chart. Rotation: = 44; // set the rotation value of the dynamic sequence in a single bit or a specific sequence table ﹔
Sheet1.chartobjects [1]. Chart. Wils. Interior. colorindex: = 28;
// If the base mark of the dimension table is set to a right angle and is irrelevant to the angle or elevation of angle of the dimension table, the value is true, used to scale up or down a line, between a line and a line ﹔
Sheet1.chartobjects [1]. Chart. rightangleaxes: = true;
Sheet1.chartobjects [1]. Chart. chartgroups (1). varybycategories: = true; // specify different colors or colors for each item.

Sheet1.range ['a20', 'c20']. Columns. horizontalalignment: = xlgeneral;
Sheet1.range ['a20', 'c20']. Columns. verticalignment: = xlcenter;
Sheet1.range ['a20', 'c20']. Columns. wraptext: = false;
Sheet1.range ['a20', 'c20']. Columns. Orientation: = 0;
Sheet1.range ['a20', 'c20']. Columns. addindent: = false;
Sheet1.range ['a20', 'c20']. Columns. indentlevel: = 0;
Sheet1.range ['a20', 'c20']. Columns. mergecells: = true;
Sheet1.range ['a20', 'a20']. Value: = 'test ';
End;

5. Example
Procedure tf_testchart.button1click (Sender: tobject );
VaR
Startx, I, X: integer;
Cell1, cell2, range: variant;
Begin
Try
Fexcelapplication: = createoleobject ('excel. application ');
Except
Messagedlg (rsnosetup, mtinformation, [mbok], 0 );
Exit;
End;
Fexcelapplication. workbooks. Add (xlwbatworksheet );
Sheet1: = fexcelapplication. workbooks [1]. worksheets ['sheet1'];
Fexcelapplication. Visible: = true;
Sheet1.cells. item [1, 1]: = 'monthly ';
Sheet1.cells. item [1, 2]: = 'Jan ';
Sheet1.cells. item [1, 3]: = 'feb ';
Sheet1.cells. item [1, 4]: = 'mar ';
Sheet1.cells. item [1, 5]: = 'apr ';
Sheet1.cells. item [1, 6]: = 'may ';
Sheet1.cells. item [1, 7]: = 'jun ';
Sheet1.cells. item [1, 8]: = 'jul ';
Sheet1.cells. item [1, 9]: = 'aug ';
Sheet1.cells. item [1, 10]: = 'sept ';
Sheet1.cells. item [1, 11]: = 'oct ';
Sheet1.cells. item [1, 12]: = 'nov ';
Sheet1.cells. item [1, 13]: = 'dec ';
Sheet1.cells. item [1, 13]: = 'dec ';

Sheet1.range ['a20', 'c20']. Columns. horizontalalignment: = xlgeneral;
Sheet1.range ['a20', 'c20']. Columns. verticalignment: = xlcenter;
Sheet1.range ['a20', 'c20']. Columns. wraptext: = false;
Sheet1.range ['a20', 'c20']. Columns. Orientation: = 0;
Sheet1.range ['a20', 'c20']. Columns. addindent: = false;
Sheet1.range ['a20', 'c20']. Columns. indentlevel: = 0;
Sheet1.range ['a20', 'c20']. Columns. mergecells: = true;
Sheet1.range ['a20', 'a20']. Value: = 'test ';

Startx: = 2;

For X: = 1 to 3 do
Begin
For I: = 1 to 12 do
Begin
Sheet1.cells. item [x + 1, 1]: = X;
Sheet1.cells. item [startx, 1 + I]: = (I + x) * 20;
End;
Startx: = startx + 1;
End;

Cell1: = sheet1.cells. item [1, 1];
Cell2: = sheet1.cells. item [startx-1, 13];
Range: = sheet1.range [cell1, cell2];
Sheet1.range [cell1, cell2]. Columns. Borders. colorindex: = 0;
Sheet1.chartobjects. Add (0,100,700,200 );
Sheet1.chartobjects [1]. Activate;
Sheet1.chartobjects [1]. Chart. charttype: = xllinemarkers;
Sheet1.chartobjects [1]. Chart. seriescollection. Add [range];
Sheet1.chartobjects [1]. Chart. plotby: = xlrows;
 
Sheet1.chartobjects [1]. Chart. axes (xlvalue, xlprimary). hastitle: = true;
Sheet1.chartobjects [1]. Chart. axes (xlvalue, xlprimary). axistitle. characters. Text: = 'y ';
Sheet1.chartobjects [1]. Chart. axes (xlcategory, xlprimary). hastitle: = true;
Sheet1.chartobjects [1]. Chart. axes (xlcategory, xlprimary). axistitle. characters. Text: = 'X ';

Sheet1.chartobjects [1]. Chart. chartarea. Font. Size: = 10;
// Sheet1.chartobjects [1]. Chart. haslegend: = true;
// Sheet1.chartobjects [1]. Chart. Legend. Position: = xltop;
Sheet1.chartobjects [1]. Chart. hastitle: = false;

Sheet1.range ['a20', 'c20']. Columns. horizontalalignment: = xlcenter;
Sheet1.range ['a20', 'c20']. Columns. verticalignment: = xlcenter;
Sheet1.range ['a20', 'c20']. Columns. wraptext: = false;
Sheet1.range ['a20', 'c20']. Columns. Orientation: = 0;
Sheet1.range ['a20', 'c20']. Columns. addindent: = false;
Sheet1.range ['a20', 'c20']. Columns. indentlevel: = 0;
Sheet1.range ['a20', 'c20']. Columns. mergecells: = true;
Sheet1.range ['a20', 'c20']. Value: = 'test ';
Sheet1.range ['a20', 'c20']. Columns. Borders. colorindex: = 0;

End;

6. Others

There are four ways to call excel in Delphi. We choose one of them to use oleobject to load Excel worksheet to talk about how Delphi controls important attributes and methods of Excel.

First, we will give a brief description of some major code steps created through OLE:

Create an OLE object:

VaR olecon: tolecontainer;
Olecon: = tolecontainer. Create (Self );
Olecon. oleobject: = olecon. Createobject ('excel. Sheet ', false );

Alternatively, you can import an Excel file to create an OLE object:

Olecon. oleobject: = olecon. createobjectfromfile (xlsname, false );

It is better to hide several Toolbar of Excel, so it is like 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, the Excel table is displayed and activated to define the tolecontainer object:

Olecon. show;
Olecon. doverb (0 );

This is basically okay, but tolecontainer has a bad point, that is, when you click another control, it will lose the focus and then automatically exit. In fact, it does not actually exit, you just need to activate it again. The key is that when it loses focus, the EXCEL object will disappear, you can use the timage control to intercept the image in the Excel area where tolecontainer is located and lie to the user. We will not discuss this here, so we will not detail it here.

Next we will start to talk about the common attributes and methods of the interfaces in excel_tlb, mainly for some interface elements for exporting and setting the report format.

Read/write attributes of cells:

Olecon. oleobject. application. cells. item [1, 1];
Olecon. oleobject. application. cells (1, 1 );
Olecon. oleobject. application. cells [1, 1]. value;

The preceding three methods can read and write the 'a1' unit of the worksheet.

Operations on cells (sets), areas, worksheets, and other objects in Delphi must be performed using variant.

The selected region in your program is assigned 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 [], sheet. cells [];

Merge cells in the preceding range:

Range. merge;
Range. formular1c1: = 'merge region'; // write the text after merging.

Note that the text in the cells to be read and merged will be the text of the cell in the upper-left corner of the merged area.

In the Excel table, the selected area is assigned to range:

Range: = excel_grid1.oleobject.application.selection;

Split cells:

Range. unmerge;

Set the format of cells (sets) after merging:

Range. horizontalalignment: = xlcenter; // horizontal text center Mode
Range. verticalignment: = xlcenter // text vertical center Mode
Range. wraptext: = true; // Text wrap
Range. Borders. linestyle: = 1 // Add a border
Range. Interior. colorindex: = 39; // fill color is lavender
Range. Font. Name: = 'shanghai'; // font
Range. Font. Color: = clblue; // font color

These are common formats. These are also applicable to a single cell.

Search for the front and back cells in the Excel table:

VaR u1, U2, U3, U4, U5: variant;
U1: = olecon. oleobject. application. activecell; // get the current cell;
U2: = u1.previous; // the cell on the left of U1 is a special case;
U3: = UI. Next; // a grid on the Right of U2 is not a special case;
U4: = olecon. oleobject. application. cells [u1.cells. Row-1, u1.cells. Column]; // The preceding format is not special.
U5: = olecon. oleobject. application. cells [u1.cells. Row + 1, u1.cells. Column]; // the following format is not used in special cases.

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 Region:

Olecon. oleobject. application. Range ['a1: c3']. copy;

Paste from the specified cell:

Olecon. oleobject. application. Range ['a4 ']. pastespecial;

This applies to the Exel control on the server panel in Delphi and the method for creating an Excel. Application COM object.