1 Create an Excel file To control Excel in Delphi, you must use OLE Automation. OLE2 is now generally used to create an OLE object. When an OLE object is activated, the server program is only activated within the container program, which is the so-called "in-place activity". When you create an Excel file, create an OLE object, then create a worksheet Worksheet in the object, such as the function CreateExcel: 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 controls the control of Excel form, mainly including data import, modification; cell merge, border control; table copy Paste, etc. When the report format is certain, the table is replicated, and the paste is particularly important so that one file template can be made first, and then the multi-page report can be output according to actual needs. (1) 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 merge, border control (linestyleControl) cell The merge is carried out in the case where the merge range is selected. Border control can operate whether the border line is displayed. Other ways of control can be made in the process of following the process. Procedure LinesTyleControl; Var v, Sheet, Range: Variant; Begin V: = Createexecl; Sheet: = v.Workbooks [1] .sheets [1]; Range: = Sheet.Range [Sheet.cells [1,1], Sheet .Cells [39, 30]]; // Select the table Range.select; Range.Merge; // Merge cell Range.Borders.LineStyle: = xlcontinuous; // Set the border line can be seen Range.Font.Size: = 9 ; // change the text font size end in the table;
Example 4. The 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 - Sample'; Sheet1.cells.Item [2, 1]: = 'Week'; Sheet1.cells.Item [2 , 2]: = 'Monday'; Sheet1.cells.Item [2, 3]: = 'Tuesday'; Sheet1.cells.Item [2, 4]: = 'Wednesses'; Sheet1.cells.Item [2, 5]: = 'Thursday'; Sheet1.cells.Item [2, 6]: = 'Friday -'; Sheet1.cells.Item [2, 7]: = 'Saturday'; Sheet1.cells.Item [2, 8 ]: = 'Sunday'; Sheet1.cells.Item [3, 1]: = 'Sales volume'; Sheet1.cells.Item [3, 2]: = 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]; // Set the CHART category holder axis Value range Range2: = sheet1.range [Cell3, Cell4]; // Set the value range of the Chart value border axis RANGE1.BORDERS.COLOR: = 27; Range2.Borders.color: = 6; // Add to worksheet The inline chart, the four parameters in the add method indicate the left distance, top margin, and the width of the left side, top margins, and charts, respectively; Sheet1.ChartObjects.Add (10, 60, 500, 280); Sheet1.ChartObjects [1] .activate;
// Activate the current chart sheet1.ChartObjects [1] .Chart.chartType: = XL3DColumnClustered; // Specify chart type: Stereo clustered straight graph chart1.ChartObjects [1] .CHARTOBJECTS [1] .CHART.SERIESCOLLECTION.ADD [Range2]; // Establish a new Case SHEET1.CHARTOBJECTS [1] .CHARTOBJECTS [1] .CHART.SERIESCOLLECTION [1] .values: = RANGE2; // Specify new number value sheet1.ChartObjects [1] .CHART.SERIESCOLLECTION [1] .hasDatalabels: = true; // Display Data tag in the chart; Sheet1.ChartObjects [1] .CHART.AXES [XLValue] .minimumscale: = 100; // Set the minimum scale value of the value of the value of the value; Sheet1.ChartObjects [1] .CHART.AXES [XLValue]. Maximumuscale: = 200; // Set the maximum scale value of the value of the value of the branch; Sheet1.ChartObjects [1] .CHART.AXES [XLValue] .majorUnit: = 10; // Set the main unit of the numeric standard; Sheet1.ChartObjects [1]. Chart.axes [xlvalue] .minorUnit: = 10; // Set the secondary unit of the numeric standard; Sheet1.ChartObjects [1] .Chart.axes [xlcategory] .hastitle: = true; Sheet1.ChartObjects [1] .CHART. Axes [xlcategory] .axistry.text: = 'Week'; // Class Category Spot Tag. Sheet1.ChartObjects [1] .CHART.AXES [xlcategory] .categoryNames: = Range1; Sheet1.Chartobjects [1] .CHART.HASLEGEND: = false; // does not display the legend Sheet1.ChartObjects [1] .ChartObjects [1] .chart.chartarea.fill .Visible: = true; // Chart area Fills Sheet1.ChartObjects [1] .Chart.Chartarea.Fill.ForeColor.Schemecolor: = 28; // Prospects; Sheet1.ChartObjects [1] .chart.chartarea.Fill. BackColor.Schemecolor: = 42; // Background Color; Sheet1.ChartObjects [1] .CHART.ROTATION: = 44; // Entry into the rotation value of the stereo chart view; Sheet1.ChartObjects [1]. Chart.walls.interior.ColorIndex: = 28; // If you specify the coordinate axis of the chart as a right angle, it is true to the corner or elevation angle of the chart. .CHARTOBJECTS [1] .CHART.Rightangleaxes: = true; sheet1.ChartObjects [1] .CHART.CHARTGROUPS (1) .varybycategories: = true; // Specify different colors or patterns for each data label.
SHEET1.RANGE ['A20', 'C20']. Column.horizontalalignment: = XlGeneral; Sheet1.Range ['A20', 'C20']. Column.VerticalAlignment: = XlCenter; Sheet1.Range ['A20', 'C20 '] .Columns.wraptext: = false; Sheet1.Range [' A20 ',' C20 ']. Column.Rientation: = 0; Sheet1.Range [' A20 ',' C20 ']. Column.Add Ltd: = FALSE; Sheet1.Range ['A20', 'C20']. Column.indentlevel: = 0; Sheet1.Range ['A20', 'C20']. Column.Mergecells: = true; sheet1.range ['A20', 'A20 '] .Value: =' test ';
5. Examples 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]: = 'SEP'; 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']. Column.horizontalalignment: = XlGeneral; Sheet1.Range ['A20', 'C20']. Column.VerticalAlignment: = XlCenter; Sheet1.Range ['A20', 'C20 '] .Columns.wraptext: = false; Sheet1.Range [' A20 ',' C20 ']. Column.Rientation: = 0; Sheet1.Range [' A20 ',' C20 ']. Column.Add Ltd: = FALSE; Sheet1.Range ['A20', 'C20']. Column.indentlevel: = 0; Sheet1.Range ['A20', 'C20']. Column.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;
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] .CHARTOBJECTS [1] .CHART.PLOTBY: = XLROWS; Sheet1.ChartObjects [1] .CHART.AXES (XLVALUE, XLPRIMARY) .hartiti: = 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) .axistry.characters.text: = 'x'; Sheet1.ChartObjects [1] .chart.chartarea.font.size: = 10; // Sheet1.ChartObjects [1] .chart.haslegend : = True; // Sheet1.ChartObjects [1] .CHART.LGEND.POSITION: = Xltop; Sheet1.ChartObjects [1] .CHART.hastitle: = FALSE; Sheet1.range ['A20', 'C20']. Columns.horizontalalignment: = XlCenter; Sheet1.Range ['A20', 'C20']. Column.VerticalAlignment: = XlCenter; Sheet1.Range ['A20', 'C20 '] .Columns.wraptext: = false; Sheet1.Range [' A20 ',' C20 ']. Column.Rientation: = 0; Sheet1.Range [' A20 ',' C20 ']. Column.Add Ltd: = FALSE; Sheet1.Range ['A20', 'C20']. Column.indentlevel: = 0; Sheet1.Range ['A20', 'C20']. Column.Mergecells: = true; sheet1.range ['A20', 'C20 '] .Value: =' Test '; Sheet1.Range ['
A20 ',' c20 ']. Column.Borders.ColorIndex: = 0;
6. Others
There are four ways to call Excel in Delphi, and we select an important properties and methods for Delphi Control Excel to use oleObject to load the Excel worksheet.
First give some major code steps created by OLE to make 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 tool bars of Excel, which 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 display and activate the Excel table, the object defined for the toleContainer:
Olecon.show; Olecon.doverb (0);
This is basic, but toleContainer has a bad place, that is, when you click on other controls, it will lose the focus, then automatically exit, but don't really quit, just to activate it again, the key is When it loses the focus, the Excel object is invisible, you can use the TIMAGE control to cut the area picture of the toleContainer to see the area picture of Excel to deceive the user, and we are mainly not telling this here, it will not be detailed.
Let's start talking about the common attributes and methods of interfaces in Excel_TLB, mainly for some interface elements for exporting and setting report formats.
The read and write properties of the cell:
Olecon.oleObject.Application.cells.Item [1,1]; olecon.oleObject.Application.cells (1, 1); olecon.oleObject.Application.cells [1,1] .value;
The above three can read and write the 'A1' unit of the worksheet.
In Delphi, the operation of all objects such as cells (set), area, and worksheet is required to be Variant.
The selected area is assigned to the Range in your own program:
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]];
For the above Range merge unit:
Range.mege; ing. Formular1c1: = 'merge area'; // After writing text
Note that the text in the cell to read the merged cell is the text of the cell of the upper left corner of the merge area.
Assign the selected area in the Excel table to assign the Range:
Range: = Excel_Grid1.oleObject.Application.selection;
Split cell:
Range.unmerge;
Set the format of the cell (set) after the merger:
Range.horzontalalignment: = xlcenter; // Text Level Housing Range.Vertical_Mext Vertical Core.WrapText: = true; // Text Automatic Wrap Range.Borders.LineStyle: = 1 // Plus Box Range .Interior.colorindex: = 39; // Fill color is lavender Range.font.name:= '书'; // font range.font.color: = CLBLUE; // font color
These often use these, these these are also applicable to a single cell.
Look for the first and lower cells in the Excel table:
Var U1, U2, U3, U4, U5: Variant; U1: = Olecon.oleObject.Application.Activecell; // Gets the current grid; u2: = u1.previous; // Non-special case is a one of U1; U3 : = ui.next; // Non-special case is a one of U2 to the right; u4: = olecon.oleObject.Application.cells [u1.cells.row-1, u1.cells.column]; // Non-special circumstances On top of a single u5: = Olecon.oleObject.Application.cells [u1.cells.row 1, u1.cells.column]; // Non-special cases are below
Delete and insert a row and a column:
Olecon.object.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: C3']. Copy;
Paste from the specified cell:
Olecon.oleObject.Application.range ['A4']. Pastespecial;
Commonly used these, the EXEL control under the Server panel in Delphi and the way to create an Excel.Application COM object is applicable.