Excel file correlation: XLS format file basic operations

1. Adding references

　Add Reference->com->microsoft Excel 9.0 Object Library (corresponds to Excel2000)

*microsoft Excel 11.0 Object Library (corresponds to Excel2003)

2. New Object

Instantiate Dim Myexcel as New excel.application ()

Add Workbook myExcel.Workbooks.Add ()

Open Workbook MyExcel.Workbooks.Open ("FileName as String")

Add Sheet MYEXCEL.WORKSHEETS.ADD ()

The value of the cell myexcel.worksheets (1). Cells (1, 1). Value

* The subscript for all element sets starts at 1

3. Save Close

Save Workbook Myexcel.workbooks (1). Save () ' 1 is the specified index

Save As Myexcel.workbooks (1). SaveAs ("FileName as String")

Close Workbook myExcel.Workbooks.close ()

Object Exit Myexcel.quit ()

* Be sure to close the workbook before exiting Excel

4. Utility Programs

1. Import Excel to a custom data type

Custom data types

    Structure datastructure         Dim  as String        Dim  as String    End Structure

Import Excel

    DimTotaldata () asdatastructurePrivate SubButton1_Click (ByValSender asSystem.Object,ByValE asSystem.EventArgs)HandlesButton1.ClickDimMyexcel as NewExcel.ApplicationDimMyWorkSheet1 asExcel.WorksheetDimRowlength as Integer=Ten 'number of rows read into data        DimCollength as Integer=Ten 'the number of columns to read into the dataMyExcel.Workbooks.Open ("C:\Users\temp.xls")        ReDimTotaldata (MyExcel.Worksheets.Count-1)         forc =1  tototaldata.length MyWorkSheet1=myexcel.worksheets (c)DimTempString (Rowlength-1, Collength-1) as String             fori =0  toRowlength-1                 forj =0  toCollength-1tempstring (i, J)= Myworksheet1.cells (i +1, J +1). ValueNext            NextTotaldata (c-1). Data =tempstring Totaldata (c-1). headstring = Myexcel.workbooks (1). Worksheets (c). NameNextmyExcel.Workbooks.Close () myexcel.quit ( )End Sub

2. Import custom type data to the ListView

    Private SubButton2_Click (ByValSender asSystem.Object,ByValE asSystem.EventArgs)HandlesButton2.clickDimRowlength as Integer=Ten 'number of rows read into data        DimCollength as Integer=Ten 'the number of columns to read into the data        DimSelectedIndex as Integer=0 'Worksheet Index        DimDatastring (Rowlength-1, Collength-1) as Stringdatastring=Totaldata (SelectedIndex). Data listview1.clear () forj =0  toCollength-1ListView1.Columns.Add (datastring (0, J)) Next         fori =1  toRowlength-1ListView1.Items.Add (datastring (i,0))             forj =1  to  -Listview1.items (ListView1.Items.Count-1). SubItems.Add (Datastring (i, j))Next        Next    End Sub

Appendix: Common Commands

1 Open Excel:dim Myexcel as New excel.application ()

2 Adding a new workbook: MyExcel.Workbooks.add ()

3 Set the second worksheet as the active sheet: myexcel.worksheets (2). Acivate ()

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

5 Display Excel window: myexcel.visible=true

6 Change the title bar of Excel: Myexcel.caption= "caption as String"

7 Assigning values to Excel cells: Myexcel.cells (1,4). value=100 This statement causes the first row in the fourth column of the Excel current worksheet, that is, the D1 cell equals 100, or you can write: Myexcel.range ("D1"). value= 100

8 Set the width of the specified column (in number of characters): MyExcel.ActiveSheet.colums (1). columnwidth=20 set the width of the 1th column of the current worksheet to 20

9 Set the height of the specified row (in points): MyExcel.ActiveSheet.rows (1). rowheight=1/0.035 1 lb = 0.035 cm Set the height of line 1th to 1CM

10 Insert a page break: MyExcel.Activesheet.rows. pagebreak=1 insert a page break before line 20th

11 Delete page breaks: myExcel.Activesheet.columns. pagebreak=0 Delete page breaks before column 20th

12 Specifies the width of the border line: MyExcel.Activesheet.range ("B3:d3"). Borders (1). Weight=3 where the borders parameter specifies the position of the border: 1: Left 2: Right 3: Top 4: Bottom 5: oblique \ 6: Oblique/

13 specifies the type of border line: MyExcel.Activesheet.range ("B1:d3"). Borders (2). linestyle=1 This statement sets the right border of the b1:d3 cell of the current worksheet to the solid line LineStyle parameter: 1: Thin Solid line 2: Thin dashed 3: Dotted line 4: Double-thin solid lines

14 Setting the footer: myexcel.activesheet.pagesetup.centerfooter= "page &p" NOTE: When you set up the header footer, make sure the printer is installed on the computer, or else an error occurs!

15 Set Header: myexcel.activesheet.pagesetup.centerfooter= "page &p"

16 setting the header to the top break distance is 2cm:myexcel.activesheet.pagesetup.headermargin=2/0.035

17 Setting the footer to the end distance is 2cm:myexcel.activesheet.pagesetup.footermargin=2/0.035

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

19 Set the bottom margin to 2cm:myexcel.activesheet.pagesetup.bottommargin=2/0.035

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

21 set the right margin to 2cm:myexcel.activesheet.pagesetup.rightmargin=2/0.035

22 Set Page Horizontal Center: myexcel.activesheet.pagesetup.centerhorizontally=true

23 Set Page Vertical Center: myexcel.activesheet.pagesetup.centervertically=true

24 Set page paper size (1, narrow row 8.5*11; 39, wide Line 14*11): myexcel.activesheet.pagesetup.papersize=1

25 Print cell grid lines: Myexcel.activesheet.pagesetup.printgridlines=true

26 Copying the entire worksheet: MyExcel.activesheet.Usedrange.Copy

27 Copy the specified range: MyExcel.activesheet.range ("A1:b5"). Copy

28 Paste: Myexcel.worksheets ("Sheet2"). Range ("A1"). PasteSpecial

29 Insert a line before line 2nd: MyExcel.activesheet.rows (2). Insert

30 insert a column before the 2nd column: MyExcel.Activesheet.Columns (2). Insert

31 Merging C4:d4 cells: MyExcel.Activesheet.Range ("C4:d4"). Merge ()

32 AutoFit 2nd Column width: myExcel.activesheet.Columns (2). AutoFit

33 Set Font: MyExcel.Activesheet.cells (2,1). Font.name= "Blackbody"

34 Set Font Size: MyExcel.Activesheet.cells (2,1). font.size=25

35 Set Font to italic: MyExcel.Activesheet.cells (2,1). Font. Italic=true

36 Set the font to bold: MyExcel.Activesheet.cells (2,1). Font. Bold=true

37 Clear cell Contents: MyExcel.activesheet.cells (2,1). ClearContents

38 Print Preview Sheet: MyExcel.Activesheet.PrintPreview

39 Print Sheet: myExcel.Activesheet.Printout

40 Worksheet Save As: MyExcel.ActiveWorkbook.saveas ("C:\book2.xls")

41 Discard Disk: Myexcel.activeworkbook.saved=false

42 Close Workbook: MyExcel.Workbooks.close

43 Exit Excel:myExcel.quit

Excel file correlation: XLS format file basic operations