做excel插件开发,可能需要为excel添加自定义的工具栏和菜单。这个也是非常的简单的。
自定义工具栏的代码:
Public
Class Connect
Class Connect
Implements Extensibility.IDTExtensibility2
Dim app As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim addInInstance As Object
Dim pasteText As Microsoft.Office.Core.CommandBarButton ‘定义一个工具栏按钮
Public Sub OnBeginShutdown()Sub OnBeginShutdown(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnBeginShutdown
End Sub
Public Sub OnAddInsUpdate()Sub OnAddInsUpdate(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnAddInsUpdate
End Sub
Public Sub OnStartupComplete()Sub OnStartupComplete(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnStartupComplete
End Sub
Public Sub OnDisconnection()Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection
End Sub
Public Sub OnConnection()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
If TypeOf (application) Is Excel.Application Then app = CType(application, Excel.Application)
addInInstance = addInInst
wb = app.ActiveWorkbook
ws = CType(wb.ActiveSheet, Excel.Worksheet)
Dim toolbar As Microsoft.Office.Core.CommandBar = Nothing
If Not app Is Nothing Then
toolbar = AddToolbar(app, "专用工具栏") '添加工具栏
End If
' 建立按钮添加文本
pasteText = MakeANewButton(toolbar, "Insert text", 1044, AddressOf pasteText_Click) '添加工具栏按钮并帮定事件
End Sub
'添加工具栏
Private Function AddToolbar()Function AddToolbar(ByVal ex As Excel.Application, _
ByVal toolbarName As String) As Microsoft.Office.Core.CommandBar
Dim toolBar As Microsoft.Office.Core.CommandBar = Nothing
Try
' 为add-in建立一个命令条
toolBar = CType(ex.CommandBars.Add(toolbarName, Microsoft.Office.Core.MsoBarPosition.msoBarTop, , True), Microsoft.Office.Core.CommandBar)
toolBar.Visible = True
Return toolBar
Catch
Return Nothing
End Try
End Function
'在工具栏上添加一个按钮
Private Function MakeANewButton()Function MakeANewButton(ByVal commandBar As Microsoft.Office.Core.CommandBar, ByVal caption As String, ByVal faceID As Integer, ByVal clickHandler As Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler) As Microsoft.Office.Core.CommandBarButton
Try
Dim newButton As Microsoft.Office.Core.CommandBarButton
newButton = CType(commandBar.Controls.Add(Microsoft.Office.Core.MsoControlType.msoControlButton), Microsoft.Office.Core.CommandBarButton)
newButton.Caption = caption
newButton.FaceId = faceID
AddHandler newButton.Click, clickHandler
Return newButton
Catch ex As System.Exception
Return Nothing
End Try
End Function
'点击工具条按钮时应该执行的程序
Public Sub pasteText_Click()Sub pasteText_Click(ByVal barButton As Microsoft.Office.Core.CommandBarButton, ByRef someBool As Boolean)
Dim text As String = ""
Dim data As System.Windows.Forms.IDataObject = System.Windows.Forms.Clipboard.GetDataObject()
If data.GetDataPresent(System.Windows.Forms.DataFormats.Text) Then
text = data.GetData(System.Windows.Forms.DataFormats.Text).ToString()
If (Not app Is Nothing) Then
Me.app.ActiveCell.Value = text
End If
End If
End Sub
End Class
Implements Extensibility.IDTExtensibility2
Dim app As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim addInInstance As Object
Dim pasteText As Microsoft.Office.Core.CommandBarButton ‘定义一个工具栏按钮
Public Sub OnBeginShutdown()Sub OnBeginShutdown(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnBeginShutdown
End Sub
Public Sub OnAddInsUpdate()Sub OnAddInsUpdate(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnAddInsUpdate
End Sub
Public Sub OnStartupComplete()Sub OnStartupComplete(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnStartupComplete
End Sub
Public Sub OnDisconnection()Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection
End Sub
Public Sub OnConnection()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
If TypeOf (application) Is Excel.Application Then app = CType(application, Excel.Application)
addInInstance = addInInst
wb = app.ActiveWorkbook
ws = CType(wb.ActiveSheet, Excel.Worksheet)
Dim toolbar As Microsoft.Office.Core.CommandBar = Nothing
If Not app Is Nothing Then
toolbar = AddToolbar(app, "专用工具栏") '添加工具栏
End If
' 建立按钮添加文本
pasteText = MakeANewButton(toolbar, "Insert text", 1044, AddressOf pasteText_Click) '添加工具栏按钮并帮定事件
End Sub
'添加工具栏
Private Function AddToolbar()Function AddToolbar(ByVal ex As Excel.Application, _
ByVal toolbarName As String) As Microsoft.Office.Core.CommandBar
Dim toolBar As Microsoft.Office.Core.CommandBar = Nothing
Try
' 为add-in建立一个命令条
toolBar = CType(ex.CommandBars.Add(toolbarName, Microsoft.Office.Core.MsoBarPosition.msoBarTop, , True), Microsoft.Office.Core.CommandBar)
toolBar.Visible = True
Return toolBar
Catch
Return Nothing
End Try
End Function
'在工具栏上添加一个按钮
Private Function MakeANewButton()Function MakeANewButton(ByVal commandBar As Microsoft.Office.Core.CommandBar, ByVal caption As String, ByVal faceID As Integer, ByVal clickHandler As Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler) As Microsoft.Office.Core.CommandBarButton
Try
Dim newButton As Microsoft.Office.Core.CommandBarButton
newButton = CType(commandBar.Controls.Add(Microsoft.Office.Core.MsoControlType.msoControlButton), Microsoft.Office.Core.CommandBarButton)
newButton.Caption = caption
newButton.FaceId = faceID
AddHandler newButton.Click, clickHandler
Return newButton
Catch ex As System.Exception
Return Nothing
End Try
End Function
'点击工具条按钮时应该执行的程序
Public Sub pasteText_Click()Sub pasteText_Click(ByVal barButton As Microsoft.Office.Core.CommandBarButton, ByRef someBool As Boolean)
Dim text As String = ""
Dim data As System.Windows.Forms.IDataObject = System.Windows.Forms.Clipboard.GetDataObject()
If data.GetDataPresent(System.Windows.Forms.DataFormats.Text) Then
text = data.GetData(System.Windows.Forms.DataFormats.Text).ToString()
If (Not app Is Nothing) Then
Me.app.ActiveCell.Value = text
End If
End If
End Sub
End Class
自定义菜单的代码
Public
Class Connect
Class Connect
Implements Extensibility.IDTExtensibility2
Dim app As Excel.Application
Dim addInInstance As Object
Dim mainMenuBar As Microsoft.Office.Core.CommandBar
Dim newEntryBar As Microsoft.Office.Core.CommandBarPopup
Dim subMenu1 As Microsoft.Office.Core.CommandBarButton
Dim addPosition As Integer
Public Sub OnBeginShutdown()Sub OnBeginShutdown(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnBeginShutdown
End Sub
Public Sub OnAddInsUpdate()Sub OnAddInsUpdate(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnAddInsUpdate
End Sub
Public Sub OnStartupComplete()Sub OnStartupComplete(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnStartupComplete
End Sub
Public Sub OnDisconnection()Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection
End Sub
Public Sub OnConnection()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
app = CType(application, Excel.Application)
addInInstance = addInInst
addMenu()
End Sub
Public Sub addMenu()Sub addMenu()
mainMenuBar = CType(app.CommandBars.ActiveMenuBar, Microsoft.Office.Core.CommandBar)
addPosition = 2
newEntryBar = CType(mainMenuBar.Controls.Add( _
Microsoft.Office.Core.MsoControlType.msoControlPopup, Before:=addPosition, Temporary:=True), Microsoft.Office.Core.CommandBarPopup)
newEntryBar.Caption = "我的菜单"
newEntryBar.Visible = True
newEntryBar.Enabled = True
subMenu1 = CType(newEntryBar.Controls.Add( _
Microsoft.Office.Core.MsoControlType.msoControlButton, Temporary:=True), _
Microsoft.Office.Core.CommandBarButton)
subMenu1.Caption = "我是谁"
subMenu1.Visible = True
subMenu1.Enabled = True
AddHandler subMenu1.Click, AddressOf Me.subMenu1Command_Click
End Sub
Private Sub subMenu1Command_Click()Sub subMenu1Command_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean)
app.ActiveCell.Value = "我是第 " & app.ActiveCell.Row.ToString() & " 行,第 " & app.ActiveCell.Column.ToString & " 列"
End Sub
End Class
Implements Extensibility.IDTExtensibility2
Dim app As Excel.Application
Dim addInInstance As Object
Dim mainMenuBar As Microsoft.Office.Core.CommandBar
Dim newEntryBar As Microsoft.Office.Core.CommandBarPopup
Dim subMenu1 As Microsoft.Office.Core.CommandBarButton
Dim addPosition As Integer
Public Sub OnBeginShutdown()Sub OnBeginShutdown(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnBeginShutdown
End Sub
Public Sub OnAddInsUpdate()Sub OnAddInsUpdate(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnAddInsUpdate
End Sub
Public Sub OnStartupComplete()Sub OnStartupComplete(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnStartupComplete
End Sub
Public Sub OnDisconnection()Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection
End Sub
Public Sub OnConnection()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
app = CType(application, Excel.Application)
addInInstance = addInInst
addMenu()
End Sub
Public Sub addMenu()Sub addMenu()
mainMenuBar = CType(app.CommandBars.ActiveMenuBar, Microsoft.Office.Core.CommandBar)
addPosition = 2
newEntryBar = CType(mainMenuBar.Controls.Add( _
Microsoft.Office.Core.MsoControlType.msoControlPopup, Before:=addPosition, Temporary:=True), Microsoft.Office.Core.CommandBarPopup)
newEntryBar.Caption = "我的菜单"
newEntryBar.Visible = True
newEntryBar.Enabled = True
subMenu1 = CType(newEntryBar.Controls.Add( _
Microsoft.Office.Core.MsoControlType.msoControlButton, Temporary:=True), _
Microsoft.Office.Core.CommandBarButton)
subMenu1.Caption = "我是谁"
subMenu1.Visible = True
subMenu1.Enabled = True
AddHandler subMenu1.Click, AddressOf Me.subMenu1Command_Click
End Sub
Private Sub subMenu1Command_Click()Sub subMenu1Command_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean)
app.ActiveCell.Value = "我是第 " & app.ActiveCell.Row.ToString() & " 行,第 " & app.ActiveCell.Column.ToString & " 列"
End Sub
End Class
这样程序就完成了。赶紧动手体验吧!