Common Excel operations in vbnet

First, you must reference: Add reference-> com-> Microsoft Excel 9.0 Object Library (this is excel2000) in the project)

1 open Excel: dim myexcel as new excel. Application () myexcel. Visible = true

2. Add a new workbook: myexcel. workbooks. Add ()

3. Set the second worksheet as an active Worksheet: myexcel. worksheets (2). acivate ()

4. Open the specified Excel file myexcel. workbooks. Open ("C: \ my.xls ")

5. Show the Excel window: myexcel. Visible = true

6. Change the title bar of Excel: myexcel. Caption = "Welcome, welcome! "

7. assign a value to the Excel cell: myexcel. cells (1, 4 ). value = 100 this statement makes the fourth column of the first row of the current Excel worksheet, that is, the D1 cell is equal to 100, you can also write: myexcel. range ("d1 "). value = 100

8. Set the width of the specified column (unit: number of characters): myexcel. activesheet. colums (1). columnwidth = 20. Set the width of the 1st column of the current worksheet to 20.

9 set the height of the specified row (unit: lbs): myexcel. activesheet. Rows (1). rowheight = 1/0. 035 1 lb = 0.035 cm set the height of 1st rows to 1 cm

10 insert pagination character: myexcel. activesheet. Rows (20). pagebreak = 1 insert a pagination character before the first row of 20th

11 delete a Page Break: myexcel. activesheet. Columns (20). pagebreak = 0 Delete the page break before the 20th Column

12. Specify the border line width: myexcel. activesheet. range ("B3: D3 "). borders (1 ). weight = 3 where the borders parameter specifies the cell border position: 1: Left 2: Right 3: Top 4: bottom 5: Oblique \ 6: Oblique/

13. Specify the border line type: myexcel. activesheet. range ("B1: D3 "). borders (2 ). linestyle = 1 This statement sets the right border of cell B1: D3 of the current worksheet to the solid line linestyle parameter: 1: solid line 2: dotted line 3: dotted line 4: double solid line

14 set the footer: myexcel. activesheet. pagesetup. centerfooter = "Page & P" NOTE: When setting the header and footer, ensure that a printer is installed on the computer; otherwise, an error occurs!

15 set the header: myexcel. activesheet. pagesetup. centerfooter = "Page & P"

16 set the disconnection distance from the header to the top to 2 cm: myexcel. activesheet. pagesetup. headermargin = 2/0. 035

17 set the footer end distance to 2 cm: myexcel. activesheet. pagesetup. footermargin = 2/0. 035

18 set the top edge margin to 2 cm: myexcel. activesheet. pagesetup. topmargin = 2/0. 035

19 set the bottom edge margin to 2 cm: myexcel. activesheet. pagesetup. bottommargin = 2/0. 035

20 set the left margin to 2 cm: myexcel. activesheet. pagesetup. leftmargin = 2/0. 035

21. Set the right margin to 2 cm: myexcel. activesheet. pagesetup. rightmargin = 2/0. 035.

22. Set page horizontal center: myexcel. activesheet. pagesetup. centerhorizontally = true

23 set the vertical center of the page: myexcel. activesheet. pagesetup. centervertically = true

24 set the page size (1, narrow rows 8.5*11; 39, wide rows 14*11): myexcel. activesheet. pagesetup. papersize = 1

25 print cell gridlines: myexcel. activesheet. pagesetup. printgridlines = true

26. Copy the entire Worksheet: myexcel. activesheet. usedrange. Copy

27 copy the specified region: myexcel. activesheet. Range ("A1: B5"). Copy

28 paste: myexcel. worksheets ("sheet2"). Range ("A1"). pastespecial

29 insert a row before row 2nd: myexcel. activesheet. Rows (2). insert

30 Insert a column before Column 2nd: myexcel. activesheet. columns (2). insert

31 merge C4: D4 cells: myexcel. activesheet. Range ("C4: D4"). Merge ()

32 automatically adjust the column width of 2nd: myexcel. activesheet. columns (2). autofit

33 set the font: myexcel. activesheet. cells (). Font. Name = ""

34. Set the font size: myexcel. activesheet. cells (). Font. size = 25

35 set the font to italic: myexcel. activesheet. cells (). Font. italic = true

36. Set the font to bold: myexcel. activesheet. cells (). Font. Bold = true

37. Clear the cell content: myexcel. activesheet. cells (). clearcontents

38. print preview Worksheet: myexcel. activesheet. printpreview

39 print the worksheet: myexcel. activesheet. Printout

40 Save the worksheet as myexcel. activeworkbook. saveas ("C: \ book2.xls ")

41 discard storage: myexcel. activeworkbook. Saved = false

42 close the workbook: myexcel. workbooks. Close

43 exit Excel: myexcel. Quit

With these commands clear, it is easy to operate Excel!