One, the method of calling Excel: In general, there are two ways to call Excel:
1, directly use the components of Delphi: In the form into ExcelApplication, ExcelWorkbook and Excelworksheet respectively.
2. Create Excel file dynamically: First create Excel object, use comobj,excel2000:
var excelapp:variant;
Excelapp: = Createoleobject (' Excel.Application ');
Second, import data: In the program, we can import the queried data (SQL, Access,) into Excel. For example, use Adoquery to query data in Access:
1, first to find the required data;
2, import: I:=1;
Adoquery.first;
While not adoquery.eof do
Begin
EXCELAPP.WORKSHEETS[1]. cells[i,1]. Value: = i;//The number of the added ordinal
EXCELAPP.WORKSHEETS[1]. cells[i,2]. Value: = Adoquery.fieldbyname (' Cp_name '). asstring;
......
INC (i);
Adoquery.next;
End;
Of course, you can import the data of adotable, Adoquery, Table, query and other components into Excel.
Third, the processing of Excel: If you know the Excel format, you can control Excel, as follows:
1. Display the current window: excelapp.visible: = True;
2. Change the Excel title bar: excelapp.caption: = ' title content ';
3, add a new workbook: ExcelApp.WorkBooks.Add;
4. Set the 2nd worksheet as the active sheet: excelapp.worksheets[2]. Activate;
5. Assign a value to the cell: excelapp.cells[1,1]. Value: = ' first row first column ';
6, set the width of the specified column (in number of characters), in the first column example:
EXCELAPP.ACTIVESHEET.COLUMNS[1]. Columnswidth: = 5;
7. Set the height of the specified line (in points) (1 lbs = 0.035 cm), with the second behavior example:
EXCELAPP.ACTIVESHEET.ROWS[2]. RowHeight: = 1/0.035; 1 cm
8, Text Horizontal center: excelid.worksheets[1]. ROWS[1]. HorizontalAlignment: = $FFFFEFF 4; If you do not specify a line coordinate, all row data will be centered.
Text vertically centered: excelid.worksheets[1]. ROWS[1]. VerticalAlignment: = $FFFFEFF 4;
9. Insert one row or column: A. EXCELAPP.ACTIVESHEET.ROWS[2]. Insert;
B. excelapp.activesheet.columns[1]. Insert;
10. Delete one row or column: A. EXCELAPP.ACTIVESHEET.ROWS[2]. Delete;
B. excelapp.activesheet.columns[1]. Delete;
11. Merge cell: Excelapp.worksheets[1].range[a1:f8 ']. Merge (ABC); NOTE: to declare variable abc:variant;
12, vertical lines display text: excelapp.worksheets[1]. cells.item[1,1]. Orientation:= xlvertical;
13, Cell plus edge: excelapp.worksheets[1]. RANGE[A1:F8]. Borders.LineStyle: = 1;
14. Insert a page break before line 8th: excelapp.worksheets[1]. ROWS[8]. PageBreak: = 1;
15. Delete the page break before the 4th column: Excelapp.activesheet.columns[4]. PageBreak: = 0;
16. Specify the width of the border line: excelapp.activesheet.range[' b3:d4 '. BORDERS[2]. Weight: = 3;
1-Left 2-right 3-top 4-bottom 5-oblique (\) 6-oblique (/)
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 end of the file: ExcelApplication1.ActiveSheet.Range.PasteSpecial;
18, clear the first row fourth column cell formula: excelapp.activesheet.cells[1,4]. clearcontents;
19. Worksheet Save: If not ExcelApp.ActiveWorkBook.Saved then
ExcelApp.ActiveSheet.PrintPreview;
20, the worksheet is saved as: Excelapp.saveas (' C:\Excel\Demo1.xls ');
21, discard the disk: ExcelApp.ActiveWorkBook.Saved: = True;
22. Close workbook: ExcelApp.WorkBooks.Close;
23, Exit Excel:ExcelApp.Quit;
Here are the statements about Print page control:
24. Set the first line Font property: 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;
EXCELAPP.ACTIVESHEET.ROWS[1]. font.size:=10;//sets the color of the specified cell Excelapp.cells[j,maxcol]. Interior.Color: = clred;
25, the page setup: A. Header: ExcelApp.ActiveSheet.PageSetup.CenterHeader: = ' report presentation ';
B. Footer: ExcelApp.ActiveSheet.PageSetup.CenterFooter: = ' Total &n page &p ';
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. Horizontal center of page: ExcelApp.ActiveSheet.PageSetup.CenterHorizontally: = 2/0.035;
J. Vertical center of page: ExcelApp.ActiveSheet.PageSetup.CenterVertically: = 2/0.035;
K. Print Cell network cable: ExcelApp.ActiveSheet.PageSetup.PrintGridLines: = True;
26. Print Preview worksheet: ExcelApp.ActiveSheet.PrintPreview;
27. Print out worksheet: ExcelApp.ActiveSheet.PrintOut;
Additional Controls for Excel:
28. Excel Multi-cell total function: Excelapp. Cells[arow, Acol]. Formula
: = ' = SUM ($+inttostr (beginrow) +:$ + inttostr (endrow) + ');
NOTE: Declare variable arow, Acol:integer;
29. Open an existing Excel file: ExcelApplication1.Workbooks.Open (path), 30, add annotation to Excel cell excelapp.worksheets[1]. Cells[j,maxcol]. AddComment (' re-working time is wrong! '); 31, ExcelApp.Cells.Formula Determine whether the cell is empty. 32, specify the hyperlink for the cell, this is to the URL. Excelapp.activesheet.cells[3,17].value: = ' =hyperlink ("http://www.sohu.com", "Sohu") '; This is the excelapp.activesheet.cells[3,7].value that can jump to sheet: = ' =hyperlink ("[templat.xls]sheet2! A1 "," your address displays the word ") '; Templat.xls is the name of the current Excel, Sheet2 is the cursor coordinates after the target sheet,a1 is skipped. This time add 32 article
Note: When the data is poured into Excel do not modify the side of the import side, this will make the time to add to Excel, it is generally better to pour the data into the format of Excel (such as page margins, cell properties, Text properties). Original reference: http://blog.csdn.net/u011354184/article/details/10966971. In this paper, some problems found in practical operation are partly modified. Http://www.cnblogs.com/azhqiang/p/3696418.html
Use Delphi to import data into Excel and control Excel
