VB.Net程序设计：Excel的基本操作模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook  
Dim xlSheet As Excel.Worksheet  
Dim myRange As Excel.Range  
xlApp = New Excel.Application  
xlBook = xlApp.Workbooks.Add  
xlSheet = xlBook.Worksheets.Item(1) 
xlSheet.Range("F5")="Date:" & Today.Date
'...
xlBook.Save  'or xlBook.SaveAs
xlBook.Close
xlApp.Quit()
xlSheet = Nothing
xlBook = Nothing
xlApp = Nothing

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
Imports System
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports Microsoft.Office.Core
Public Class ExcelEdit
    Public mFilename As String
    Public app As Excel.Application
    Public wbs As Excel.Workbooks
    Public wb As Excel.Workbook
    Public wss As Excel.Worksheets
    Public ws As Excel.Worksheet
    Public Sub New()

    End Sub

    Public Sub Create()
        '创建一个Excel对象 
        app = New Excel.Application
        wbs = app.Workbooks
        wb = wbs.Add(True)
    End Sub
    Public Sub Open(ByVal FileName As String)
        '打开一个Excel文件 
        app = New Excel.Application
        wbs = app.Workbooks
        wb = wbs.Add(FileName)
        'wb   =   wbs.Open(FileName,     0,   true,   5, " ",   " ",   true,   Excel.XlPlatform.xlWindows,   "t ",   false,   false,   0,   true,Type.Missing,Type.Missing); 
        'wb   =   wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing); 
        mFilename = FileName
    End Sub

    Public Function GetSheet(ByVal SheetName As String) As Excel.Worksheet
        '获取一个工作表 
        Dim s As Excel.Worksheet = DirectCast(wb.Worksheets(SheetName), Excel.Worksheet)
        Return s
    End Function

    Public Function AddSheet(ByVal SheetName As String) As Excel.Worksheet
        '添加一个工作表 
        Dim s As Excel.Worksheet = DirectCast(wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing), Excel.Worksheet)
        s.Name = SheetName
        Return s
    End Function
    Public Sub InsertCurve(ByVal ws As String, ByVal Points As Point())
        '添加一条曲线
        If Points.Length = 0 Then Return
        With GetSheet(ws).Shapes.BuildFreeform(MsoEditingType.msoEditingAuto, Points(0).X, Points(0).Y)
            Dim i As Integer
            For i = 1 To Points.Length - 1
                .AddNodes(MsoSegmentType.msoSegmentCurve, MsoEditingType.msoEditingAuto, Points(i).X, Points(i).Y)
            Next
            .ConvertToShape.Select()
        End With
    End Sub
    Public Function Save() As Boolean
        '保存文档 
        If mFilename = "" Then
            Return False
        Else
            Try
                wb.Save()
                Return True
            Catch ex As Exception

                Return False
            End Try
        End If
    End Function
    Public Function SaveAs(ByVal FileName As Object) As Boolean
        '文档另存为 
        Try
            wb.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
            Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
            Return True
        Catch ex As Exception

            Return False
        End Try
    End Function
    Public Sub Close()
        '关闭一个Excel对象，销毁对象 
        'wb.Save(); 
        wb.Close(Type.Missing, Type.Missing, Type.Missing)
        wbs.Close()
        app.Quit()
        wb = Nothing
        wbs = Nothing
        app = Nothing
        GC.Collect()
    End Sub
End Class