VB.Net开发Excel COM AddIn

        今天把以往在Excel中用VBA写的的一些实例翻出来了看了看，大部分都是些Excel下的AddIn文件即加载宏(.xla)。突然间很想把这些实例转换为COM AddIn文件。在VB6下开发Excel的COM AddIn感觉比较复杂，看了些相关方面的书，但是一直没有着手自己编写一个实例出来。反正也没有更高的需求，也就没有在深入探讨这方面的东西了。今天闲来无事，就试试用VB.Net做一下开发试试。试过之后，感觉要比VB6方便多了。这里给出的示例是，实现工作簿中隐藏与取消隐藏工作表。

（1）首先开启VB.Net然后新建一个项目。依次选择“其他项目类型”--“扩展性”--“共享的外接程序”。修改好名称、位置后，选择确定按钮。

（2）在随后的连续设置对话框中1号对话框选择“使用VB创建外接程序”。二号对话框选择“Microsoft Excel”。三号对话框中设置外接程序名称和说明。四号对话框勾选两个复选框。五号对话框选择完成按钮。

（3）在资源管理器窗口中右击工程，然后选择属性。选择引用栏，查看是否引用了以下内容。如果没有选择添加按钮，然后依次将这些引用添加进来。

Extensibility（V7.0.3300.0）

Microsoft Excel 12.0 Object Library（ActiveX   V1.6.0.0）

Microsoft Office 12.0 Object Library（ActiveX   V2.4.0.0）

Microsoft Visual Basic for Applications Extensibility 5.3（ActiveX   V5.3.0.0）

System

System.Data

System.Drawing

System.Windows.Forms

System.Xml

（4）在资源管理器窗口中找到connect.vb并打开其代码窗口。然后在该其中输入以下代码

Imports Extensibility精英博客.qE zuY#[2B6H
Imports System.Runtime.InteropServices精英博客7g;e`K:VIH1x-l
Imports Microsoft.Office.Core
yN(V^#L5i;h0Imports Microsoft.Office.Core.MsoControlType

精英博客e)xl1RSNI
#Region " Read me for Add-in installation and setup information. "精英博客S7AO.}U$Ekx
' When run, the Add-in wizard prepared the registry for the Add-in.
sOG;ccu$C+o+X0' At a later time, if the Add-in becomes unavailable for reasons such as:
&v:g2n9EB{
D0vp0'   1) You moved this project to a computer other than which is was originally created on.
XHnF_?MgW0'   2) You chose 'Yes' when presented with a message asking if you wish to remove the Add-in.精英博客e&aJ Xa.T)D
'   3) Registry corruption.
pf V@2ZN [;|0' you will need to re-register the Add-in by building the $SAFEOBJNAME$Setup project, 精英博客x5Wh^h
' right click the project in the Solution Explorer, then choose install.精英博客:T*FI#dy&F
#End Region

<GuidAttribute("4C1CFBC7-551D-4B81-B282-7E4A7D9D71DD"), ProgIdAttribute("MyCOMAddin.Connect")> _
n;MkFl2Qf!Xt0Public Class Connect
1jj7|w ^7~DN0 
0wKt:]&] |.e9}3`0 Implements Extensibility.IDTExtensibility2
b3@@"MSx0    Dim WithEvents MyButton As CommandBarButton
.e/{O7mg0    Dim applicationObject As Object
(AS*TvnK&o0    Dim addInInstance As Object精英博客{#M\J#o9yU(vd
 
^BIMD ^P0r0    Public Sub OnBeginShutdown(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnBeginShutdown精英博客LKl]H.w%z
        On Error Resume Next
Vu-`}]H)TbX2PY}6D0        MyButton.Delete()精英博客9H$T.s9YnkI/Z C
        MyButton = Nothing
P|i(m j0    End Sub精英博客 [;wl(v+ryM_ bP
 
N
tVj h0    Public Sub OnAddInsUpdate(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnAddInsUpdate精英博客P7^:uC3??*ZL
M%Z
    End Sub精英博客T9nv5]uFVJr
 
mV"vP8[};i/S0    Public Sub OnStartupComplete(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnStartupComplete精英博客o2@)?8^,_ [8P
        Dim oCommandBars As CommandBars
P(@z^;B$J}+_1E0        Dim oStandardBar As CommandBar
:pt"d[7mC/EZ0        Dim oBarPopup As CommandBarPopup

        On Error Resume Next
3? S"^.m4p+VN0P8k0        CommandBars = applicationObject.CommandBars精英博客"l%f,G3Z;N L-GY
        If oCommandBars Is Nothing Then精英博客C'^}(^:d2v"?$[}
            CommandBars = applicationObject.ActiveExplorer.CommandBars精英博客0`m0D9N-w
z
        End If精英博客?C.l&G0{k2l
        StandardBar = oCommandBars.Item("Standard")精英博客3C,pRT$ip9hW
        If oStandardBar Is Nothing Then
"A3{]/c0h&X;A0            StandardBar = oCommandBars.Item("Database")精英博客NL hz]P9`
        End If
awdM|8a,n g0        BarPopup = oStandardBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)精英博客0dzbi1R
        oBarPopup.Caption = "COMAddIn"
E^C1}D5A0        MyButton = oBarPopup.Controls.Add(Type:=msoControlButton)
6R8i2mE%hzh0        With MyButton精英博客#jT6Yi,h@
            .Caption = "隐藏与取消隐藏工作表"精英博客 pS&Iy c&qbhL-b \
            .Style. = MsoButtonStyle.msoButtonCaption精英博客&D[/?N.\:A!]|
            .Tag = "My Custom Button"精英博客7z.q7hmB!W:^2b9}(h
            .OnAction = "!<MyCOMAddin.Connect>"
M;j9b mK]K0            .Visible = True精英博客+~%cv WI'I,kEzn+s
        End With精英博客sR,I ?p?"?] C
        StandardBar = Nothing
:FXBi{r0        CommandBars = Nothing精英博客 E2qT.?!F,p^dB
    End Sub精英博客b4nj W!?/eA;GO
 
taH'R4X-}4V(N%n0    Public Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection
|&D1k;w?0        On Error Resume Next精英博客\%qk!B!m
`2G|
        If RemoveMode <> Extensibility.ext_DisconnectMode.ext_dm_HostShutdown Then _精英博客:~fu.U9g}+w
            OnBeginShutdown(custom)
*~[n+jqy|A0        applicationObject = Nothing精英博客Ys9h2o,K#IG}
    End Sub精英博客LtKviN
 精英博客*QG$e}0x@
 Public Sub OnConnection(ByVal application As Object, ByVal connectMode As Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnConnection
'Dy7gd f J&D0        applicationObject = application精英博客;g^\R }
        addInInstance = addInInst
b d x3m\.A)r0        If (connectMode <> Extensibility.ext_ConnectMode.ext_cm_Startup) Then _精英博客u!XN5\j&N](e
            OnStartupComplete(custom)
e$f9u5Sa{T0    End Sub

    Private Sub MyButton_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean) Handles MyButton.Click
yO~v0dnu0        Dim fm As New frmHide
Brd\1d^RQ0        fm.m_appObject = applicationObject精英博客%eEbhQ6S9},~
        fm.Show()精英博客6rX,?-N~;dj&j
    End Sub精英博客'_cva(I+NIr
End Class

（5）在资源管理器中右击项目，然后依次选择“添加”--“Windows窗体”。插入一个窗体并为该窗体添加控件如下图所示。

该窗体的控件类型以及控件名称如下表所示：

控件类型	控件名称
窗体	frmHide
标签	label1
复合框	cmb表类型
列表框	lst表名
复选框	chk深度隐藏
按钮	btnOK
按钮	btnClose

frmHide.vb的代码如下所示。

Imports Microsoft.Office.Interop.Excel

Public Class frmHide

    Enum XlSheetVisibility精英博客y$[^&l/Wq2{
        xlSheetVisible = -1精英博客Z/g2X.dt:y
        xlSheetHidden = 0
1S7XR[%z9R%J0        xlSheetVeryHidden = 2
,yX'lRJThM0    End Enum

    Public WithEvents m_appObject As Application

    Private Sub btnClose_Click(ByVal sender As Object, _
B
h4DAPGf0                               ByVal e As System.EventArgs) Handles btnClose.Click精英博客h+h2z2`1z5I2mB
        Me.Close()精英博客d9PjVqE3yj
    End Sub

    Private Sub frmHide_Load(ByVal sender As Object, _
z?.LTF0Q7k0                             ByVal e As System.EventArgs) Handles Me.Load精英博客/R%b%jK'p oe
        With cmb表类型                                  '初始化表类型复合框精英博客w9IpgrTX#Z
            .Items.Add("可见表")                        '为复合框添加第一个项目
Az o'W[7apU5w4g(P0            .Items.Add("隐藏表")                        '为复合框添加第二个项目
K1isY3Y0            .SelectedIndex = 0                          '设置复合框初始显示项目
F,x8l"t;O$u.n0        End With精英博客g%J c&B&W
        chk深度隐藏.Checked = False                     '设置复选框的勾选状态精英博客#L8W T b5O%_&i-U]-q
    End Sub

    Private Sub 刷新表列表()
I4]H7KZ)N0        Dim st As worksheet精英博客!B8nT1B+A/w(}"d
        lst表名.Items.Clear()                                                    '清除列表框所有项目
`?em3hO
[1a~0        For Each st In m_appObject.ActiveWorkbook.Worksheets                    '循环工作簿中所有工作表精英博客{([cv~Ut4E
            If cmb表类型.Text = "可见表" Then                                   '检查当前设置的表类型精英博客BzR RhecY1n{
                If st.Visible = XlSheetVisibility.xlSheetVisible Then精英博客e&ZF7cYP({'J
                    lst表名.Items.Add(st.Name)                                  '表可见时，添加工作表名称精英博客G/b+jdi
                End If
Py e^T/n}0            Else
(@6N&d8}&B"Hy6s0                If chk深度隐藏.Checked Then                                     '检查深度隐藏复选框勾选状态
l Fv9@ U0                    If st.Visible <> XlSheetVisibility.xlSheetVisible Then精英博客bH9| ?Or/n
                        lst表名.Items.Add(st.Name)                              '工作表不可见时，添加工作表名称精英博客4uQ8XKu
                    End If
9H
? sp^6G'p0                Else
'ni[ |y8a{,koSxC0                    If st.Visible = XlSheetVisibility.xlSheetHidden Then
&_%D7z\&}9Z2}1Q0                        lst表名.Items.Add(st.Name)                               '工作表隐藏时，添加工作表名称
sF2I N+}xH0                    End If
v/y OE%P9ym0                End If
hh H l-?gy0            End If
8h3Ai}5T"\5J-j9H'C0        Next精英博客'qqC|g _Qk/G%O
    End Sub

    Private Sub cmb表类型_TextChanged(ByVal sender As Object, _
lm%F0qy T9gD*k0                                    ByVal e As System.EventArgs) Handles cmb表类型.TextChanged精英博客._C6QT&J
        刷新表列表()                                            '刷新列表框控件
*Vf ^inN0        If cmb表类型.Text = "可见表" Then                       '检测当前表类型
b&\0nn J(k4H\h~0            btnOK.Text = "隐藏"                                 '修改确定按钮的Caption
_5I9m4U.GE$f6}0            chk深度隐藏.Text = "深度隐藏工作表"                 '修改复选框的Caption精英博客&o9[7K8VkZ7\
        Else精英博客.D(d }cY&A m,Yq/?
            btnOK.Text = "取消隐藏"                             '修改确定按钮的Caption
.Q#}P%@![Q,zF%~0            chk深度隐藏.Text = "包含深度隐藏工作表"             '修改复选框的Caption
?b1\;[z"J0        End If
@D{S9i t0q$XS6@9v0    End Sub

    Private Sub chk深度隐藏_Click(ByVal sender As Object, _
R7h,tqK0                                    ByVal e As System.EventArgs) Handles chk深度隐藏.Click

oOW9Y&q0        刷新表列表()精英博客Y9HDZeOtt3Rg
    End Sub

    Private Sub btnOK_Click(ByVal sender As Object, _
Ai H+Xg0                            ByVal e As System.EventArgs) Handles btnOK.Click
l~%p@A1X-} `0        Dim HideType As XlSheetVisibility, sheetCount As Integer, i As Integer

        If chk深度隐藏.Checked Then                                         '检测深度隐藏复选框状态
Jy%w z&p'X#~l0            HideType = XlSheetVisibility.xlSheetVeryHidden                  '深度隐藏精英博客"A? ZgZ]#r8@t
        Else
cbH9GSn5_0            HideType = XlSheetVisibility.xlSheetHidden                      '浅度隐藏精英博客&D'P1?:K0Z0hw-L'R
        End If
5QX%_&B)D;I0        For i = 0 To lst表名.Items.Count - 1                                '遍历列表框中所有项目精英博客P$gI `6T@'Xl8B*J
            If lst表名.GetSelected(i) Then                                  '检测索引号为i的项目是否选中
U? d!rf4_0                If btnOK.Text = "取消隐藏" Then                             '确定操作类型
A$nL F0Rj.P K2j!QT0                    m_appObject.ActiveWorkbook.Sheets( _
0^v-odL0                                lst表名.Items.Item(i)).Visible = True       '取消隐藏工作表
&B6N|l*i-I0                Else精英博客uyS @M xt3?-`YI
                    If lst表名.Items.Count - 1 = sheetCount Then Exit Sub '只有一个可见表时退出过程精英博客cVx&?^ n(E/Q
                    m_appObject.ActiveWorkbook.Worksheets(lst表名.Items.Item(i)).Visible = HideType       '浅度或深度隐藏工作表
~z7l4`i'I/e$v0                    sheetCount = sheetCount + 1                             '统计被隐藏表的数目精英博客 n%H*JujJ[%B
                End If
#P"S7e9P:K#CY;K,G0            End If精英博客 @%i)t-z+U.}$w[2}~
        Next
:q_Ec0G;o0        刷新表列表()                                                         '刷新列表框精英博客)WCU,_V
    End Sub

    Private Sub m_appObject_WorkbookActivate(ByVal Wb As Microsoft.Office.Interop.Excel.Workbook) Handles m_appObject.WorkbookActivate精英博客 VZ(DpwcU#h&V
        刷新表列表()
Ls*RHu1h:oaIQH2X0    End Sub
lB-t$m8|,o0End Class