VB.NET Excel Vsto 任务窗格_数据可视化处理

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

https://vbee.lanzoui.com/ixR6Nq98ev