第一步,创建一个VSTO项目(excel,word,ppt)等操作一样
Public Class ThisAddIn
#Region "任务窗格对象"
''' <summary>
''' 任务窗格面板对象
''' </summary>
''' <returns></returns>
Private Property MotCtp As Microsoft.Office.Tools.CustomTaskPane
''' <summary>
''' 全局任务窗格控制对象
''' </summary>
''' <returns></returns>
Public ReadOnly Property CtpObj() As Microsoft.Office.Tools.CustomTaskPane
Get
Return MotCtp
End Get
End Property
#End Region
#Region "VSTO事件"
''' <summary>
''' vsto初始化事件
''' </summary>
Private Sub ThisAddIn_Startup() Handles Me.Startup
''--加载窗体到任务窗格
Dim frm As New Form_main
MotCtp = CustomTaskPanes.Add(frm, "VB小原码工具")
MotCtp.Visible = False ''默认隐藏
MotCtp.DockPosition = Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionLeft ''默认位置
MotCtp.Width = 300
''--添加任务窗格事件
AddHandler MotCtp.VisibleChanged, New EventHandler(AddressOf MotCtp_VisibleChanged)
AddHandler MotCtp.DockPositionChanged, New EventHandler(AddressOf MotCtp_DockPositionChanged)
End Sub
''' <summary>
''' vsto结束事件
''' </summary>
Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown
End Sub
#End Region
#Region "任务窗格事件"
''' <summary>
''' 任务窗格隐藏事件
''' </summary>
Private Sub MotCtp_VisibleChanged()
End Sub
''' <summary>
''' 任务窗格位置变更事件
''' </summary>
Private Sub MotCtp_DockPositionChanged()
End Sub
#End Region
End Class
第二步,添加一个,功能区,这里用的是可视化功能区
第三步,添加两个按钮,用来控制任务窗格,隐藏或者展开
Imports Microsoft.Office.Tools.Ribbon
Public Class Ribbon_main
''' <summary>
''' 功能区加载事件
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub Ribbon_main_Load(ByVal sender As System.Object, ByVal e As RibbonUIEventArgs) Handles MyBase.Load
End Sub
''' <summary>
''' 打开工具按钮事件
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub Button_open_Click(sender As Object, e As RibbonControlEventArgs) Handles Button_open.Click
If Globals.ThisAddIn.CtpObj.Visible = False Then
Globals.ThisAddIn.CtpObj.Visible = True
End If
End Sub
''' <summary>
''' 关闭工具按钮事件
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub Button_close_Click(sender As Object, e As RibbonControlEventArgs) Handles Button_close.Click
If Globals.ThisAddIn.CtpObj.Visible Then
Globals.ThisAddIn.CtpObj.Visible = False
End If
End Sub
End Class
第四步,添加一个用户控件窗体
Public Class Form_main
''' <summary>
''' excel对象
''' </summary>
''' <returns></returns>
Private Property Xlapp As Excel.Application
''' <summary>
''' excel活动工作簿
''' </summary>
''' <returns></returns>
Private Property Xlbook As Excel.Workbook
''' <summary>
''' 初始化窗格
''' </summary>
Public Sub New()
InitializeComponent()
''----------
Try
Xlapp = Globals.ThisAddIn.Application
Xlbook = Xlapp.ActiveWorkbook
Catch ex As Exception
Xlapp = Nothing
Xlbook = Nothing
End Try
End Sub
''' <summary>
''' 选取数据
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click
If Xlapp Is Nothing Then
Windows.Forms.MessageBox.Show("Excel对象获取失败!")
Return
End If
''----选取数据
DGV.Rows.Clear()
Try
Dim arr(,) As Object = Xlapp.InputBox("请选择单元格区域", Type:=64) ''选取数据
''添加列
For j As Integer = 1 To UBound(arr, 2)
DGV.Columns.Add(j, arr(1, j))
Next
''---数据写入DGV
For i As Integer = 1 To UBound(arr)
DGV.Rows.Add()
For j As Integer = 1 To UBound(arr, 2)
DGV.Rows(i - 1).Cells(j - 1).Value = arr(i, j)
Next
Next
Catch ex As Exception
Return
End Try
End Sub
''' <summary>
''' 输出数据
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub ToolStripButton2_Click(sender As Object, e As EventArgs) Handles ToolStripButton2.Click
If Xlapp Is Nothing Then
Windows.Forms.MessageBox.Show("Excel对象获取失败!")
Return
End If
''--------选取单元格
Dim rng As Excel.Range = Xlapp.InputBox("请选择单元格", Type:=8) ''选择书插入点
''------活动工作簿
Dim sht As Excel.Worksheet = Xlbook.ActiveSheet
Dim ii As Integer = rng.Address(ReferenceStyle:=Excel.XlReferenceStyle.xlR1C1).ToString.Split("C"c)(0).Replace("R", "")
Dim jj As Integer = rng.Address(ReferenceStyle:=Excel.XlReferenceStyle.xlR1C1).ToString.Split("C"c)(1)
''------数据写入Excel
With sht
For i As Integer = 0 To DGV.Rows.Count - 1
For j As Integer = 0 To DGV.Columns.Count - 1
.Cells(ii i, jj j) = DGV.Rows(i).Cells(j).Value
Next
Next
End With
End Sub
''' <summary>
''' 清空数据
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub ToolStripButton3_Click(sender As Object, e As EventArgs) Handles ToolStripButton3.Click
DGV.Rows.Clear()
End Sub
''' <summary>
''' 关闭
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub ToolStripButton4_Click(sender As Object, e As EventArgs) Handles ToolStripButton4.Click
If Globals.ThisAddIn.CtpObj.Visible Then
Globals.ThisAddIn.CtpObj.Visible = False
End If
End Sub
End Class
实例文件下载:
代码语言：javascript复制https://vbee.lanzoui.com/ixR6Nq98ev
