vb.net 教程 10-2 Excel操作3 查看多个工作表

版权声明：本文为博主原创文章，转载请在显著位置标明本文出处以及作者网名，未经作者允许不得用于商业目的。

要想同时查看多个工作表，最先想到的就是使用TabControl控件（相关教程请查看：vb.net 教程 3-8 窗体编程 容器 2 TabControl 1），就像excel一样可以分选项卡显示。代码中使用循环即可获得每一个工作表。

设计界面：

由于代码简单，这里只列出：

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim filename As String
        Dim ofd As New OpenFileDialog()
        ofd.Filter = "表格文件|*.xls;*.xlsx"
        If ofd.ShowDialog <> DialogResult.OK Then
            Exit Sub
        Else
            filename = ofd.FileName
        End If

        Dim xls As New Excel.Application
        Dim Wbook As Excel.Workbook = xls.Workbooks.Open(filename)

        Dim sheetCount As Integer
        Dim Wsheet As Excel.Worksheet
        sheetCount = Wbook.Worksheets.Count

        Tc.TabPages.Clear()


        For i As Integer = 1 To sheetCount
            Wsheet = Wbook.Worksheets(i)
            Dim tp As New TabPage()
            tp.Name = "tp" & i
            tp.Text = Wsheet.Name
            Tc.TabPages.Add(tp)

            Dim lvExcel As New ListView()
            lvExcel.Name = "lvExcel" & i
            lvExcel.Dock = DockStyle.Fill
            lvExcel.View = View.Details
            tp.Controls.Add(lvExcel)

            Dim maxColumn As Integer
            Dim maxRow As Integer

            '读入第一行数据作为column
            If Wsheet.Rows.Count > 1 Then
                lvExcel.Columns.Clear()
                maxColumn = Wsheet.UsedRange.Columns.Count
                maxRow = Wsheet.UsedRange.Rows.Count

                For columnCount As Integer = 1 To maxColumn
                    If IsNothing(Wsheet.Cells(1, columnCount).Value) Then
                        '无数据
                    Else
                        lvExcel.Columns.Add(Wsheet.Cells(1, columnCount).Value)
                    End If
                Next


                For j As Integer = 2 To maxRow
                    Dim Lv As New ListViewItem()
                    Dim LvText As String

                    If IsNothing(Wsheet.Cells(j, 1).Value) Then
                        LvText = "(空)"
                    Else
                        LvText = Wsheet.Cells(j, 1).Value
                    End If
                    Lv.Text = LvText

                    Dim subLvText As String
                    For k As Integer = 2 To maxColumn
                        If IsNothing(Wsheet.Cells(j, k).Value) Then
                            subLvText = "(空)"
                        Else
                            subLvText = Wsheet.Cells(j, k).Value
                        End If
                        Lv.SubItems.Add(subLvText)
                    Next

                    lvExcel.Items.Add(Lv)
                Next
            End If

        Next
        xls.Quit()
    End Sub

由于.net平台下C#和vb.NET很相似，本文也可以为C#爱好者提供参考。

学习更多vb.net知识，请参看vb.net 教程 目录