ASP.NET如何读取Excel文件的多张工作表

测试环境：windows server 2003+visual studio 2008

 

    ''' <summary>

    ''' 获取Excel文件所有工作表的名称

    ''' </summary>

    ''' <param name="iSubPath">Excel文件路径，如：D:/test.xls</param>

    ''' <returns></returns>

    ''' <remarks></remarks>

    Public Function GetSheetName(ByVal iSubPath As String) As String

        Dim strRet = ""

        Dim objExcelConn As New OleDb.OleDbConnection

        Dim i As Integer

        Dim sSName As String = ""

        Dim dtTables As DataTable

        Dim sSql = ""

        Dim objAdapter As New OleDbDataAdapter

        Dim objDataSet As New DataSet

 

        'Selects File & Loops Through the Records

        objExcelConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & iSubPath & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""

        objExcelConn.Open()

 

        dtTables = objExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

 

        If dtTables.Rows.Count > 0 Then

            For i = 0 To dtTables.Rows.Count - 1

                sSName = dtTables.Rows(i)("TABLE_NAME")

                sSName = Replace(sSName, "'", "")

                sSName = Replace(sSName, "$", "")

 

                If sSName = "User Details" Then

                    sSql = ""

                    sSql = String.Format("select * from [{0}$]", sSName)

 

                    objAdapter = New OleDb.OleDbDataAdapter(sSql, objExcelConn)

                    objAdapter.Fill(objDataSet)

 

                    If objDataSet.Tables(0).Rows.Count <= 1 Then

                        ''''' Your Logic goes

                    End If

                End If

                '工作表名称换行显示

                strRet += sSName & "<br />"

 

            Next

        End If

        Return strRet

    End Function