Delphi General Operations for Excel

information from the network
Cell settings
1. Set the cell line box
EXCEL.ACTIVESHEET.RANGE[B10:C13]. Borders[n]. LineStyle: = Xlnone
EXCEL.ACTIVESHEET.RANGE[B10:C13]. Borders[n]. Weight: = Xlthin

Type of border Borders[n]
Xledgeleft left =1
Xledgeright Right =2
Xledgetop Top =3
Xledgebottom Bottom =4
Xldiagonalup left upper right lower =5
Xldiagonaldown left lower right upper =6
Xledgeleft Outer left Border =7
Xledgetop External Top Border =8
Xledgebottom Outer bottom Border =9
Xledgeright Outer right Border =10
Xlinsidevertical Internal Vertical Line =11
Xlinsidehorizontal Internal Horizontal line =12
(where 1: Left 2: Right 3: Top 4: Bottom 5: oblique \ 6: Oblique/)

Line type LineStyle, Width weight
LineStyle of a single line: = Xlcontinuous
LineStyle of double lines: = xldouble
Dashed Xlhairline 1
Solid Line Xlthin
Medium Solid line Xlmedium
Thick Solid line Xlthick

2. Assign a value to a cell:
excel.cells[1,4]. Value: = Fourth column of the first row;
3. Set the first line font properties (official script, blue, Bold, underline):
EXCEL.ACTIVESHEET.ROWS[1]. Font.Name: = Official script;
EXCEL.ACTIVESHEET.ROWS[1]. Font.Color: = Clblue;
EXCEL.ACTIVESHEET.ROWS[1]. Font.Bold: = True;
EXCEL.ACTIVESHEET.ROWS[1]. Font.underline: = True;
4. Set the entire table font to 9
excel.cells.font.size:=9;
5. Insert/delete page breaks before line 8th:
EXCEL.WORKSHEETS[1]. ROWS[8]. PageBreak: = 1; (0 for deletion)
6. Clear the Cell Formula for column Fourth of the first row:
excel.activesheet.cells[1,4]. clearcontents;
7. Convert from a numeric type to a text type (without knowing the format string, record the macro and extract the formatted string from the macro.) )
Excelworksheet1. cells.item[row,10].numberformatlocal:= ' @ ';
Excelworksheet1. CELLS.ITEM[ROW,10]. NumberFormat: = ' hh:mm:ss '
Excelworksheet1. Cells.item[row,9].numberformatlocal:= ' $#,# #0.00; [Red]-$#,# #0.00 ';
8. Add formula (do not know formula format, record macro, extract formula format in macro.) )
Excelworksheet1. CELLS.ITEM[ROW,10]. formula:= ' =r[-1]c+rc[-1] ';
Exapp.cells[9+iloop,6].value:= ' =sum (G ' + inttostr (9+iloop) + ': H ' + inttostr (9+iloop) + ') ';


attached: Delphi Operation Excel Method
(i) Use of dynamically created methods
(ii) Use of the Delphi control method

A) Use the method of dynamic creation

First create the 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 new workbook: ExcelApp.WorkBooks.Add;
4) Open a workbook that already exists: ExcelApp.WorkBooks.Open (' C:\Excel\Demo.xls ');
5) Set the 2nd sheet to be the active sheet: excelapp.worksheets[2]. Activate;
or excelapp.workssheets[' Sheet2 '). Activate;
6) Assign a value to the cell: excelapp.cells[1,4]. Value: = ' first row fourth column ';
7) Set the width of the specified column (in number of characters), in the first column example: Excelapp.activesheet.columns[1]. ColumnWidth: = 5;
8) Set the height of the specified line (in points) (1 lbs = 0.035 cm), in the second act example: excelapp.activesheet.rows[2]. RowHeight: = 1/0.035; 1 cm
9) Insert a page break before line 8th: excelapp.worksheets[1]. Rows.pagebreak: = 1;
10) Delete the page break before the 8th column: 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-oblique (\) 6-oblique (/)
12) Clear the first row fourth column cell formula: excelapp.activesheet.cells[1,4]. clearcontents;
13) Set the first line font properties:
EXCELAPP.ACTIVESHEET.ROWS[1]. Font.Name: = ' official script ';
EXCELAPP.ACTIVESHEET.ROWS[1]. Font.Color: = Clblue;
EXCELAPP.ACTIVESHEET.ROWS[1]. Font.Bold: = True;
EXCELAPP.ACTIVESHEET.ROWS[1]. Font.underline: = True;

14) make Page setup:
A. Header:
ExcelApp.ActiveSheet.PageSetup.CenterHeader: = ' report presentation ';
B. Footer:
ExcelApp.ActiveSheet.PageSetup.CenterFooter: = ' &p page ';
C. Header to top margin 2cm:
ExcelApp.ActiveSheet.PageSetup.HeaderMargin: = 2/0.035;
D. Footer End margin 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 centered vertically:
ExcelApp.ActiveSheet.PageSetup.CenterVertically: = 2/0.035;
K. Print the Cell network 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 A1 position:
ExcelApp.ActiveSheet.Range. [' A1 ']. PasteSpecial;
D. Start pasting from the tail of the file:
ExcelApp.ActiveSheet.Range.PasteSpecial;

16) Insert one row or column:
A. excelapp.activesheet.rows[2]. Insert;
B. excelapp.activesheet.columns[1]. Insert;
17) Delete one row or column:
A. excelapp.activesheet.rows[2]. Delete;
B. excelapp.activesheet.columns[1]. Delete;
18) Print Preview worksheet:
ExcelApp.ActiveSheet.PrintPreview;
19) Print out worksheet:
ExcelApp.ActiveSheet.PrintOut;
20) Worksheet Save:
If not ExcelApp.ActiveWorkBook.Saved then
ExcelApp.ActiveSheet.PrintPreview;
21) Save the worksheet as:
Excelapp.saveas (' C:\Excel\Demo1.xls ');
22) Discard the disk:
ExcelApp.ActiveWorkBook.Saved: = True;
23) Close Workbook:
ExcelApp.WorkBooks.Close;
24) Exit Excel:
Excelapp.quit;
25) Lock Excel:
Excelapp.cells.select;//select All Cells
ExcelApp.Selection.Locked = true;//Lock Selected Cells

(ii) Use of the Delphi control method
Put ExcelApplication, ExcelWorkbook, and excelworksheet in the 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 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 a workbook that already exists:
ExcelApplication1.Workbooks.Open (C:\a.xls
Emptyparam,emptyparam,emptyparam,emptyparam,
Emptyparam,emptyparam,emptyparam,emptyparam,
emptyparam,emptyparam,emptyparam,emptyparam,0)

7) Set the 2nd worksheet as the active sheet:
EXCELAPPLICATION1.WORKSHEETS[2]. Activate; Or
excelapplication1.workssheets[' Sheet2 '. Activate;

8) Assign a value to the cell:
excelapplication1.cells[1,4]. Value: = ' first row fourth column ';

9) Set the width of the specified column (in number of characters), in the first column as an example:
EXCELAPPLICATION1.ACTIVESHEET.COLUMNS[1]. ColumnWidth: = 5;

10) Set the height of the specified line (in points) (1 lbs = 0.035 cm), with the second behavior example:
EXCELAPPLICATION1.ACTIVESHEET.ROWS[2]. RowHeight: = 1/0.035; 1 cm

11) Insert a page break before line 8th:
EXCELAPPLICATION1.WORKSHEETS[1]. Rows.pagebreak: = 1;

12) Delete the page break before the 8th column:
EXCELAPPLICATION1.ACTIVESHEET.COLUMNS[4]. PageBreak: = 0;

13) Specify the width of the border line:
excelapplication1.activesheet.range[' B3:d4 '. BORDERS[2]. Weight: = 3;
1-Left 2-right 3-top 4-bottom 5-oblique (\) 6-oblique (/)

14) Clear the cell Formula for column Fourth of the first row:
excelapplication1.activesheet.cells[1,4]. clearcontents;

15) Set the first line font properties:
EXCELAPPLICATION1.ACTIVESHEET.ROWS[1]. Font.Name: = ' official script ';
EXCELAPPLICATION1.ACTIVESHEET.ROWS[1]. Font.Color: = Clblue;
EXCELAPPLICATION1.ACTIVESHEET.ROWS[1]. Font.Bold: = True;
EXCELAPPLICATION1.ACTIVESHEET.ROWS[1]. Font.underline: = True;
Set the 9th column text to prevent AMT from being scientifically counted and, of course, the previous add ' also resolves this issue
Excelworksheet1. cells.item[row,9].numberformatlocal:= ' @ ';


16) make Page setup:
A. Header:
ExcelApplication1.ActiveSheet.PageSetup.CenterHeader: = ' report presentation ';
B. Footer:
ExcelApplication1.ActiveSheet.PageSetup.CenterFooter: = ' &p page ';
C. Header to top margin 2cm:
ExcelApplication1.ActiveSheet.PageSetup.HeaderMargin: = 2/0.035;
D. Footer End margin 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 centered vertically:
ExcelApplication1.ActiveSheet.PageSetup.CenterVertically: = 2/0.035;
K. Print the Cell network 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 A1 position:
ExcelApplication1.ActiveSheet.Range. [' A1 ']. PasteSpecial;

D. Start pasting from the tail of the file:
ExcelApplication1.ActiveSheet.Range.PasteSpecial;

18) Insert one row or column:
A. excelapplication1.activesheet.rows[2]. Insert;
B. excelapplication1.activesheet.columns[1]. Insert;

19) Delete one row or column:
A. excelapplication1.activesheet.rows[2]. Delete;
B. excelapplication1.activesheet.columns[1]. Delete;

20) Print Preview worksheet:
ExcelApplication1.ActiveSheet.PrintPreview;

21) Print out worksheet:
ExcelApplication1.ActiveSheet.PrintOut;

22) Worksheet Save:
If not ExcelApplication1.ActiveWorkBook.Saved then
ExcelApplication1.ActiveSheet.PrintPreview;

23) Save the worksheet as:
Excelapplication1.saveas (' C:\Excel\Demo1.xls ');

24) Discard the disk:
ExcelApplication1.ActiveWorkBook.Saved: = True;

25) Close Workbook:
ExcelApplication1.WorkBooks.Close;

26) Exit Excel:
Excelapplication1.quit;
Excelapplication1.disconnect;

Delphi General Operations for Excel