Category:COM & ActiveX
(I) Use dynamic creation method
First create an Excel object, using ComObj:
var ExcelApp: Variant;
ExcelApp := CreateOleObject( 'Excel.application' );
1) Display the current window:
ExcelApp.Visible := True;
2) Change the Excel title bar:
ExcelApp.Caption := 'Application calls Microsoft Excel';
3) Add a new workbook:
ExcelApp.WorkBooks.Add;
4) Open an existing workbook:
ExcelApp.WorkBooks.Open( 'C:/Excel/Demo.xls' );
5) Set the second worksheet as the active worksheet:
ExcelApp.WorkSheets[2].Activate;
or
ExcelApp.WorksSheets[ 'Sheet2' ].Activate;
6) Assign values to cells:
ExcelApp.Cells[1,4].Value := 'First row, fourth column';
7) Set the width of the specified column (unit: number of characters), taking the first column as an example:
ExcelApp.ActiveSheet.Columns[1].ColumnsWidth := 5;
8) Set the height of the specified row (unit: pound) (1 pound = 0.035 cm), and use the second behavior example:
ExcelApp.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1 cm
9) Insert page break before line 8:
ExcelApp.WorkSheets[1].Rows[8].PageBreak := 1;
10) Delete the page break before column 8:
ExcelApp.ActiveSheet.Columns[4].PageBreak := 0;
11) Specify the border line width:
ExcelApp.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;
1-Left 2-Right 3-Top 4-Bottom 5-Slant ( / ) 6-Slant ( / )
12) Clear the cell formula of the first row and the fourth column:
ExcelApp.ActiveSheet.Cells[1,4].ClearContents;
13) Set the font properties of the first line:
ExcelApp.ActiveSheet.Rows[1].Font.Name := 'Lishu';
ExcelApp.ActiveSheet.Rows[1].Font.Color := clBlue;
ExcelApp.ActiveSheet.Rows[1].Font.Bold := True;
ExcelApp.ActiveSheet.Rows[1].Font.UnderLine := True;
14) Make page settings:
a. Header:
ExcelApp.ActiveSheet.PageSetup.CenterHeader := 'Report Demo';
b. Footer:
ExcelApp.ActiveSheet.PageSetup.CenterFooter := 'Page&P';
c. The margin to the top is 2cm:
ExcelApp.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;
d. The margin of the footer to the bottom end is 3cm:
ExcelApp.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;
e. Top margin 2cm:
ExcelApp.ActiveSheet.PageSetup.TopMargin := 2/0.035;
f. Bottom margin 2cm:
ExcelApp.ActiveSheet.PageSetup.BottomMargin := 2/0.035;
g.Left margin 2cm:
ExcelApp.ActiveSheet.PageSetup.LeftMargin := 2/0.035;
h. Right margin 2cm:
ExcelApp.ActiveSheet.PageSetup.RightMargin := 2/0.035;
i. The page is centered horizontally:
ExcelApp.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
j. The page is vertically centered:
ExcelApp.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
k. Print cell mesh cable:
ExcelApp.ActiveSheet.PageSetup.PRintGridLines := True;
15) Copy operation:
a. Copy the entire worksheet:
ExcelApp.ActiveSheet.Used.Range.Copy;
b. Copy the specified area:
ExcelApp.ActiveSheet.Range[ 'A1:E2' ].Copy;
c. Start pasting from position A1:
ExcelApp.ActiveSheet.Range.[ 'A1' ].PasteSpecial;
d. Start pasting from the end of the file:
ExcelApp.ActiveSheet.Range.PasteSpecial;
16) Insert a row or column:
a. ExcelApp.ActiveSheet.Rows[2].Insert;
b. ExcelApp.ActiveSheet.Columns[1].Insert;
17) Delete a row or column:
a. ExcelApp.ActiveSheet.Rows[2].Delete;
b. ExcelApp.ActiveSheet.Columns[1].Delete;
18) Print preview worksheet:
ExcelApp.ActiveSheet.PrintPreview;
19) Printout worksheet:
ExcelApp.ActiveSheet.PrintOut;
20) Worksheet saving:
if not ExcelApp.ActiveWorkBook.Saved then
ExcelApp.ActiveSheet.PrintPreview;
21) Save the worksheet as:
ExcelApp.SaveAs( 'C:/Excel/Demo1.xls' );
22) Give up saving:
ExcelApp.ActiveWorkBook.Saved := True;
23) Close the workbook:
ExcelApp.WorkBooks.Close;
24) Exit Excel:
ExcelApp.Quit;
(II) Use Delphi control method
Put ExcelApplication, ExcelWorkbook and ExcelWorksheet in Form respectively.
1) Open Excel
ExcelApplication1.Connect;
2) Display the current window:
ExcelApplication1.Visible[0]:=True;
3) Change the Excel title bar:
ExcelApplication1.Caption:= 'Application calls Microsoft Excel';
4) Add a new workbook:
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Add(EmptyParam,0));
5) Add a new worksheet:
var Temp_Worksheet: _WorkSheet;
Begin
Temp_Worksheet:=ExcelWorkbook1.
WorkSheets.Add(EmptyParam,EmptyParam,EmptyParam,EmptyParam,0) as _WorkSheet;
ExcelWorkSheet1.ConnectTo(Temp_WorkSheet);
End;
6) Open an existing workbook:
ExcelApplication1.Workbooks.Open (c:/a.xls
EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,0)
7) Set the second worksheet as the active worksheet:
ExcelApplication1.WorkSheets[2].Activate; or
ExcelApplication1.WorksSheets[ 'Sheet2' ].Activate;
8) Assign values to cells:
ExcelApplication1.Cells[1,4].Value := 'First row, fourth column';
9) Set the width of the specified column (unit: number of characters), taking the first column as an example:
ExcelApplication1.ActiveSheet.Columns[1].ColumnsWidth := 5;
10) Set the height of the specified row (unit: pound) (1 pound = 0.035 cm), and use the second behavior example:
ExcelApplication1.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1 cm
11) Insert page break before line 8:
ExcelApplication1.WorkSheets[1].Rows[8].PageBreak := 1;
12) Delete the page break before column 8:
ExcelApplication1.ActiveSheet.Columns[4].PageBreak := 0;
13) Specify the border line width:
ExcelApplication1.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;
1-Left 2-Right 3-Top 4-Bottom 5-Slant ( / ) 6-Slant ( / )
14) Clear the cell formula of the first row and the fourth column:
ExcelApplication1.ActiveSheet.Cells[1,4].ClearContents;
15) Set the font properties of the first line:
ExcelApplication1.ActiveSheet.Rows[1].Font.Name := 'Lishu';
ExcelApplication1.ActiveSheet.Rows[1].Font.Color := clBlue;
ExcelApplication1.ActiveSheet.Rows[1].Font.Bold := True;
ExcelApplication1.ActiveSheet.Rows[1].Font.UnderLine := True;
16) Make page settings:
a. Header:
ExcelApplication1.ActiveSheet.PageSetup.CenterHeader := 'Report Demo';
b. Footer:
ExcelApplication1.ActiveSheet.PageSetup.CenterFooter := 'Page&P';
c. The margin to the top is 2cm:
ExcelApplication1.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;
d. The margin of the footer to the bottom end is 3cm:
ExcelApplication1.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;
e. Top margin 2cm:
ExcelApplication1.ActiveSheet.PageSetup.TopMargin := 2/0.035;
f. Bottom margin 2cm:
ExcelApplication1.ActiveSheet.PageSetup.BottomMargin := 2/0.035;
g.Left margin 2cm:
ExcelApplication1.ActiveSheet.PageSetup.LeftMargin := 2/0.035;
h. Right margin 2cm:
ExcelApplication1.ActiveSheet.PageSetup.RightMargin := 2/0.035;
i. The page is centered horizontally:
ExcelApplication1.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
j. The page is vertically centered:
ExcelApplication1.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
k. Print cell mesh cable:
ExcelApplication1.ActiveSheet.PageSetup.PrintGridLines := True;
17) Copy operation:
a. Copy the entire worksheet:
ExcelApplication1.ActiveSheet.Used.Range.Copy;
b. Copy the specified area:
ExcelApplication1.ActiveSheet.Range[ 'A1:E2' ].Copy;
c. Start pasting from position A1:
ExcelApplication1.ActiveSheet.Range.[ 'A1' ].PasteSpecial;
d. Start pasting from the end of the file:
ExcelApplication1.ActiveSheet.Range.PasteSpecial;
18) Insert a row or column:
a. ExcelApplication1.ActiveSheet.Rows[2].Insert;
b. ExcelApplication1.ActiveSheet.Columns[1].Insert;
19) Delete a row or column:
a. ExcelApplication1.ActiveSheet.Rows[2].Delete;
b. ExcelApplication1.ActiveSheet.Columns[1].Delete;
20) Print preview worksheet:
ExcelApplication1.ActiveSheet.PrintPreview;
21) Printout worksheet:
ExcelApplication1.ActiveSheet.PrintOut;
22) Worksheet saving:
if not ExcelApplication1.ActiveWorkBook.Saved then
ExcelApplication1.ActiveSheet.PrintPreview;
23) Save the worksheet as:
ExcelApplication1.SaveAs( 'C:/Excel/Demo1.xls' );
24) Give up saving:
ExcelApplication1.ActiveWorkBook.Saved := True;
25) Close the workbook:
ExcelApplication1.WorkBooks.Close;
26) Exit Excel:
ExcelApplication1.Quit;
ExcelApplication1.Disconnect;
(III) Use Delphi to control Excel 2D diagrams
Put ExcelApplication, ExcelWorkbook and ExcelWorksheet in Form respectively
var shell1,acart, range:variant;
1) Select the first worksheet when the first workbook
shell1:=ExcelApplication1.Workbooks[1].Worksheets[1];
2) Add a two-dimensional graph
achart:=sheet1.chartobjects.add(100,100,200,200);
3) Select the shape of the two-dimensional graph
achart.chart.charttype:=4;
4) Assign values to a two-dimensional graph
series:=acart.chart.seriescollection;
range:=sheet1!r2c3:r3c9;
series.add(range,true);
5) Add the title of the two-dimensional graph
achart.Chart.HasTitle:=True;
achart.Chart.ChartTitle.Characters.Text:='Excle 2D diagram'
6) Change the title font size of the two-dimensional graph
achart.Chart.ChartTitle.Font.size:=6;
7) Add a description to the two-dimensional figure
achart.Chart.Axes(xlCategory, xlPrimary).HasTitle := True;
achart.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text := 'Subscript description';
8) Add a left mark to the two-dimensional figure
achart.Chart.Axes(xlValue, xlPrimary).HasTitle := True;
achart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text := 'Left Mark Description';
9) Add a right mark to the two-dimensional figure
achart.Chart.Axes(xlValue, xlSecondary).HasTitle := True;
achart.Chart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text := 'right mark description';
10) Change the display area size of the two-dimensional graph
achart.Chart.PlotArea.Left := 5;
achart.Chart.PlotArea.Width := 223;
achart.Chart.PlotArea.Height := 108;
11) Add instructions to the coordinate axis of the two-dimensional graph
achart.chart.seriescollection[1].NAME:='Axis Description';