Comprehensive control Excel
First create an Excel object, use COMOBJ:
DIM EXCELID AS Excel.Application
SET Excelid As New Excel.Application
1) Display the current window:
Excelid.visible: = true;
2) Change the Excel title bar:
Excelid.caption: = 'Application MICROFT EXCEL';
3) Add a new workbook:
ExcelID.Workbooks.Add;
4) Open an existing workbook:
Excelid.Workbooks.open ('c: /excel/demo.xls');
5) Set the second worksheet as an active worksheet:
Excelid.Worksheets [2] .activate;
Or Excelid.Worksheets ['Sheet2'] .actiVate;
6) Give a cellular value:
Excelid.cells [1,4] .value: = 'The first line of fourth column';
7) Set the width of the specified column (unit: characters), with the first list as an example:
Excelid.Activeesheet.columns [1] .Columnswidth: = 5;
8) Set the height of the specified line (unit: pound) (1 pound = 0.035 cm), with the second behavior:
Excelid.activeesheet.Rows [2]. RowHeight: = 1 / 0.035; // 1 cm
9) Insert the pagper in the 8th line:
Excelid.worksheets [1] .ROWS [8] .pagebreak: = 1;
10) Delete the paging before column 8:
Excelid.Activeesheet.columns [4] .pagebreak: = 0;
11) Specify the width of the edge of the frame:
Excelid.Activeesheet.Range ['B3: D4'] .borders [2] .weight: = 3;
1-left 2- Right 3-top 4-bottom 5-oblique (/) 6-oblique (/)
12) Clear the first line of fourth column formula:
Excelid.Activeesheet.cells [1,4] .clearcontents;
13) Set the first line of font properties:
Excelid.Activeesheet.Rows [1] .font.name: = '书';
Excelid.Activeesheet.Rows [1] .font.color: = CLBLUE
Excelid.activesheet.Rows [1] .font.bold: = true;
Excelid.Activeesheet.Rows [1] .font.underline: = true;
14) Make page settings:
a. Header:
Excelid.Activesheet.pagesetup.centerhead.pagesetup.centerheader: = 'Report Demo';
b. Foot:
Excelid.Activesheet.pagesetup.centerfooter: = 'P.';
c. Header to the top edge of 2cm:
Excelid.Activeesheet.pagesetup.headermargin: = 2 / 0.035;
d. Footer to the bottom side distance 3cm:
Excelid.Activeesheet.pagesetup.headermargin: = 3 / 0.035; E. Top distance 2cm:
Excelid.Activeesheet.pagesetup.topmargin: = 2 / 0.035;
f. bottom margins 2cm:
Excelid.Activeesheet.pagesetup.bottommargin: = 2 / 0.035;
g. Left margins 2cm:
Excelid.activesheet.pagesetup.Leftmargin: = 2 / 0.035;
h. Right margin 2cm:
Excelid.activeesheet.pagesetup.rightmargin: = 2 / 0.035;
i. Page levels:
Excelid.activesheet.pagesetup.centerhorizontally: = 2 / 0.035;
j. The page is vertical at:
Excelid.Activesheet.pagesetup.centerVertical: = 2 / 0.035;
k. Print unit cable:
Excelid.Activeesheet.pagesetup.printgridlines: = TRUE;
15) Copy operation:
a. Copy the entire worksheet:
Excelid.Activeesheet.Uses.range.copy;
b. Copy designated area:
Excelid.activesheet.Range ['A1: E2'] .copy;
c. Paste from the A1 position:
Excelid.activesheet.range. ['A1'] .pastespecial;
d. Paste from the end of the file:
Excelid.Activesheet.Range.Pastespecial;
16) Insert a row or a column:
a. Excelid.activesheet.Rows [2] .insert;
b. Excelid.Activeesheet.columns [1] .insert;
17) Delete a row or a column:
a. Excelid.Activeesheet.Rows [2] .delete;
b. Excelid.Activeesheet.columns [1] .deete;
18) Print preview worksheet:
Excelid.Activesheet.PrintPreview;
19) Print Output Worksheet:
Excelid.activeesheet.Printout;
20) Worksheet Save:
If not excelid.activeworkbook.saved dam
Excelid.Activeesheet.Printpreview
END IF
21) Worksheet is saved as:
Excelid.saves ('c: /excel/demo1.xls');
22) Abandon the deposit:
Excelid.Activeworkbook.saved: = true;
23) Close the workbook:
Excelid.workbooks.close;
24) Exit Excel:
Excelid.quit;
25) Set work table password:
Excelid.Activeesheet.Protect "123", DrawingObjects: = true, contents: = true, Scenarios: = true 26) Excel's display mode is maximized
Excelid.Application.windowState = XLmaximized
27) Work thin display mode is maximized
Excelid.ActiveWindow.WindowState = XLmaximized28) Set the number of works to open the default work
Excelid.sheetsinnewwbook = 3
29) 'Is it prompt to save (TRUE save; false does not save)
Excelid.displayAlerts = false
30) Set the split window, and the fixed line position
Excelid.ActiveWindow.Splitrow = 1
Excelid.activeWindow.FreezePanes = true
31) Set the fixed print content when printing
Excelid.Activeesheet.pagesetup.printtitlerows = "$ 1: $ 1"
32) Set print heading
Excelid.Activeesheet.pagesetup.printtitlecolumns = ""
33) Set display mode (paging mode display)
Excelid.activeWindow.View = XLpageBreakPreview
34) Set the display ratio
Excelid.ActiveWindow.Zoom = 100
35) Let Excel respond to DDE requests
EX.Application.IgnoreMoteRequests = FALSE
Use VB to operate Excel
Private submmand3_click ()
ON Error Goto Err1
DIM I as long
DIM J AS Long
DIM Objexl as Excel.Application 'declaration object variable
ME.MousePointer = 11 'Change the mouse style
Set objexl = new excel.Application 'initialization object variable
Objexl.sheetsinnewwbook = 1 'Sets the newly created number of works to 1
Objexl.Workbooks.add 'Add a work thin
Objexl.sheets (Object) .Name = "BOOK1" 'modified work thin name
Objexl.sheets.add, Objexl.sheets ("Book1") 'Added the second work after the first one
Objexl.sheets (objects.count) .Name = "BOOK2"
Objexl.sheets.add, objexl.sheets ("Book2") 'increases the third work thin in the second
Objexl.sheets (Object) .Name = "BOOK3"
Objexl.sheets ("Book1"). Select 'Select Works Bolt
FOR i = 1 to 50 'loop write data
For j = 1 to 5
IF i = 1 THEN
Objexl.selection.NumberformatLocal = "@" set format is text
Objexl.cells (i, j) = "e" & i & j
Else
Objexl.cells (i, j) = i & j
END IF
NEXT
NEXT
Objexl.Rows ("1: 1"). SELECT 'Select the first line
Objexl.selection.font.bold = true 'is set to bold objexl.selection.font.size = 24' Set font size
Objexl.cells.ntirecolumn.autofit 'automatic adjustment column width
Objexl.activeWindow.splitrow = 1 'split first line
Objexl.activeWindow. splitcolumn = 0 'split columns
Objexl.activeWindow.Freezepanes = true 'fixed split Objectup.PrinTtitlerows = "$ 1: $ 1" set print fixed line
Objexl.activeesheet.pagesetup.printtitlecolumns = "" "" "" "print title Objexl.activesheet.pagesetup.rightfooter =" print time: "& _
Format (now, "YYYY year MM month DD day hh: mm: ss")
Objexl.activeWindow.View = XLpageBreakPreview 'Set display mode
Objexl.activeWindow.Zoom = 100 'Sets the display size
'Add a password to worksheet
Objexl.activeesheet.Protect "123", DrawingObjects: = true, _
Contents: = true, Scenarios: = TRUE
Objexl.Application.IgnoreremoteRequests = false
Objexl.visible = true 'makes Excel visible
Objexl.application.windowState = XLmaximized 'Excel's display mode is maximized
Objexl.activeWindow.WindowState = XLmaximized 'Workfill display mode is maximized
Objexl.sheetsinnewwbook = 3 'changes the default new work thin number back to 3
Set Objexl = Nothing 'Clear Object
ME.MousePointer = 0 'Modify the mouse
EXIT SUB
Err1:
Objexl.sheetsinnewwbook = 3
Objexl.displayalerts = false 'is not prompted when shutting down
Objexl.quit 'Close Excel
Objexl.displayalerts = true 'Tips When prompted
Set objexl = Nothing
Me.MousePointer = 0
End Sub
