读EXCEL文件时应注意的几个问题

Public Class ExcelImport

#Region "成员变量定义"

    ''' <summary>
    ''' 

    ''' </summary>
    ''' <remarks></remarks>
    Private _xlApp As Excel.Application

    ''' <summary>
    ''' 

    ''' </summary>
    ''' <remarks></remarks>
    Private _xlBooks As Excel.Workbooks

    ''' <summary>
    ''' 

    ''' </summary>
    ''' <remarks></remarks>
    Private _xlBook As Excel.Workbook

    ''' <summary>
    ''' 

    ''' </summary>
    ''' <remarks></remarks>
    Private _xlSheet As Excel.Worksheet

 

    Private eMassage As String

#End Region

 

#Region "构造函数"
    Public Sub ExcelImport()

    End Sub
#End Region

 

#Region "属性"
    Public ReadOnly Property ExMassage() As String
        Get
            Return eMassage
        End Get
    End Property
#End Region

 

#Region "方法"

    ''' <summary>
    ''' 读入指定Excel文件的第一个WorkSheet   

   ''' </summary>
    ''' <param name="sFullFilePath">读入Excel文件的路径</param>
    ''' <returns>读入EXECL文件的数据</returns>
    ''' <remarks></remarks>
    Public Function ReadExcel(ByVal sFullFilePath As String) As System.Data.DataTable
        Dim dtImport As System.Data.DataTable = Nothing
        Dim sSheetName As String
        Try

            '打开Excel进程

            _xlApp = OpenExcel(_xlApp)

            'Excel窗口是否显示
            _xlApp.Visible = False

            '打开Excel工作簿

            _xlBook = OpenBook(_xlApp, sFullFilePath)

            '取得WorkSheet

            _xlSheet = CType(_xlBook.Worksheets(1), Excel.Worksheet)

            '取得WorkSheet的名称

            sSheetName = _xlSheet.Name

            '通过ODBC连接读入数据
            dtImport = ReadExcelByOleDb(sFullFilePath, sSheetName)

        Catch ex As Exception
            Throw ex
        Finally

            '关闭工作簿
            _xlBook.Close(False, sFullFilePath, True)

            '释放Excel资源

            EndImport()
        End Try
        Return dtImport
    End Function

 

    ''' <summary>
    ''' 读入指定EXCEL文件指定的WorkSheet的数据

    ''' </summary>
    ''' <param name="sFullFilePath">读入Excel文件的路径</param>
    ''' <param name="iSheetPage">指定WorkSheet的页码</param>
    ''' <returns>读入EXECL文件的数据</returns>
    ''' <remarks></remarks>
    Public Function ReadExcel(ByVal sFullFilePath As String, ByVal iSheetPage As Integer) As System.Data.DataTable
        Dim dtImport As System.Data.DataTable = Nothing
        Dim sSheetName As String
        Try
            '打开Excel进程
            _xlApp = OpenExcel(_xlApp)

              'Excel窗口是否显示
            _xlApp.Visible = False

             '打开Excel工作簿

            _xlBook = OpenBook(_xlApp, sFullFilePath)

            '判断指定的WorkSheets是否存在
            If iSheetPage > _xlBook.Worksheets.Count And iSheetPage < 0 Then

                'ｍｓｇ

            End If

             '取得WorkSheet

            _xlSheet = CType(_xlBook.Worksheets(iSheetPage), Excel.Worksheet)

              '取得WorkSheet的名称

            sSheetName = _xlSheet.Name

             '通过ODBC连接读入数据
            dtImport = ReadExcelByOleDb(sFullFilePath, sSheetName)
        Catch ex As Exception
            Throw ex
        Finally

             '关闭工作簿
            _xlBook.Close(False, sFullFilePath, True)

            '释放Excel资源

            EndImport()
        End Try
        Return dtImport
    End Function

 

    ''' <summary>
    ''' 读入指定EXCEL文件的所有数据

    ''' </summary>
    ''' <param name="sFullFilePath">读入Excel文件的路径</param>
    ''' <returns>读入EXECL文件的数据</returns>
    ''' <remarks></remarks>
    Public Function ReadExcel(ByVal sFullFilePath As String, ByVal bFlag As Boolean) As System.Data.DataSet
        Dim dsImport As System.Data.DataSet = Nothing
        Dim sSheetName As String
        Dim startTime As System.DateTime
        Dim afterTime As System.DateTime
        Dim mProcessList() As System.Diagnostics.Process
        Dim fileName As String
        Dim sMassage As String
        Try

            '获取所有打开的Excel进程

            mProcessList = System.Diagnostics.Process.GetProcessesByName("EXCEL")

 

            '获取指定Excel的文件名

            fileName = GetFileName(sFullFilePath)

           

            '判断指定的Excel文件是否有同名的Excel文件打开

            For Each tmpProcess As System.Diagnostics.Process In mProcessList
                If (tmpProcess.MainWindowTitle.Contains(fileName)) Then

                    '有同名的Excel文件打开，抛出异常
                    Throw New Exception(sMassage)
                End If
            Next

 

            If (_xlApp Is Nothing) Then

                '打开Excel进程之前的时间
                startTime = System.DateTime.Now.AddSeconds(-1)

                '打开Excel进程

                _xlApp = OpenExcel(_xlApp)

                '打开Excel进程之后的时间

                afterTime = System.DateTime.Now.AddSeconds(1)
            End If

 

            'Excel窗口是否显示
            _xlApp.Visible = bFlag

               '打开Excel工作簿

            _xlBook = OpenBook(_xlApp, sFullFilePath)

            dsImport = New System.Data.DataSet

            For Each xlSheet As Excel.Worksheet In _xlBook.Worksheets
                Dim dtImport As System.Data.DataTable

                 '取得WorkSheet的名称

                sSheetName = xlSheet.Name

 

               '通过遍历EXCEL读入数据
                dtImport = ReadExcelByTraversal(xlSheet)

                dtImport.TableName = sSheetName

                dsImport.Tables.Add(dtImport)
            Next

        Catch exx As System.Runtime.InteropServices.COMException
            eMassage = exx.Message
            Throw exx
        Catch ex As Exception
            eMassage = ex.Message
            Throw ex
        Finally

             '关闭工作簿
            _xlBook.Close(False, False)

             '释放Excel资源

            EndImport()

           

            '杀死Excel进程

            KillProcess("EXCEL", startTime, afterTime)
        End Try
        Return dsImport
    End Function

 

    ''' <summary>
    '''通过ODBC连接读入数据
    ''' </summary>
    ''' <param name="sFullFilePath">读入Excel文件的路径</param>
    ''' <param name="sSheetName">WorkSheet的名称</param>
    ''' <returns>读入EXECL文件的数据</returns>
    ''' <remarks></remarks>
    Private Function ReadExcelByOleDb(ByVal sFullFilePath As String, ByVal sSheetName As String) As System.Data.DataTable
        Dim dtImport As System.Data.DataTable = Nothing
        Dim myConn As System.Data.OleDb.OleDbConnection
        Dim myAdapter As System.Data.OleDb.OleDbDataAdapter
        Dim strSql As String

        '判断EXCEL的版本
        If (_xlApp.Version = "12.0") Then
            myConn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & sFullFilePath & ";Extended Properties = 'Excel " & _xlApp.Version & ";HDR =Yes;IMEX=1;'")
        Else
            myConn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & sFullFilePath & ";Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1;'")
        End If
        strSql = "select * from " & "[" & sSheetName & "$] "

         myAdapter = New System.Data.OleDb.OleDbDataAdapter(strSql, myConn)
        dtImport = New System.Data.DataTable
        myAdapter.Fill(dtImport)
        Return dtImport
    End Function

 

     ''' <summary>
    '''通过ODBC连接读入数据
    ''' </summary>
    ''' <param name="xlSheet ">要读取得WorkSheet</param>
   ''' <returns>读入EXECL文件的数据</returns>
    ''' <remarks></remarks>
    Private Function ReadExcelByTraversal(ByVal xlSheet As Excel.Worksheet) As System.Data.DataTable
        Dim dtImport As System.Data.DataTable = Nothing
        Dim r As Excel.Range
        Dim col As Integer
        Dim row As Integer
        col = GetLastCol(xlSheet)
        row = GetLastRow(xlSheet)
        Dim o(col, row) As Object
        r = xlSheet.Range("A1", xlSheet.Cells(row, col))
        o = r.Value
        dtImport = WriteToTable(CreateTable(col), o, col, row)
        Return dtImport
    End Function

 

    Private Function CreateTable(ByVal maxCol As Integer) As System.Data.DataTable
        Dim dt As System.Data.DataTable = Nothing
        dt = New System.Data.DataTable
        For i As Integer = 0 To maxCol
            dt.Columns.Add(New DataColumn(i.ToString()))
        Next
        Return dt
    End Function

 

    Private Function WriteToTable(ByVal dt As System.Data.DataTable, ByVal pData(,) As Object, ByVal maxCol As Integer, ByVal maxRow As Integer) As System.Data.DataTable
        Dim obj() As Object
        Dim oneLineIsNotNothing As Boolean = False
        For i As Integer = 1 To maxRow - 1
            obj = New Object(maxCol) {}
            For j As Integer = 0 To maxCol - 1
                If (pData(i, j + 1) IsNot Nothing AndAlso pData(i, j + 1).ToString() <> "") Then
                    oneLineIsNotNothing = True
                End If
                obj(j) = pData(i, j + 1)
            Next
            If (oneLineIsNotNothing) Then
                dt.Rows.Add(obj)
            End If
            oneLineIsNotNothing = False
        Next
        Return dt
    End Function

 

   '获取Excel的最大活动的列

    Private Function GetLastCol(ByVal xlSheet As Excel.Worksheet) As Integer
               Return xlSheet.UsedRange.Columns.Count
    End Function

 

    '获取Excel的最大活动的行

    Private Function GetLastRow(ByVal xlSheet As Excel.Worksheet) As Integer
        Return xlSheet.UsedRange.Rows.Count
    End Function

 

    ''' <summary>
    '''    打开Excel工作簿  

    ''' </summary>
    ''' <param name="xlApp">应用程序</param>
    ''' <param name="sFullFilePath">Excel文件的路径</param>
    ''' <returns>ワークブック</returns>
    ''' <remarks></remarks>
    Private Function OpenBook(ByVal xlApp As Excel.Application, ByVal sFullFilePath As String) As Excel.Workbook

        Return xlApp.Workbooks.Open(sFullFilePath _
                                    , System.Reflection.Missing.Value _
                                    , System.Reflection.Missing.Value _
                                    , System.Reflection.Missing.Value _
                                    , System.Reflection.Missing.Value _
                                    , System.Reflection.Missing.Value _
                                    , System.Reflection.Missing.Value _
                                    , System.Reflection.Missing.Value _
                                    , System.Reflection.Missing.Value _
                                    , System.Reflection.Missing.Value _
                                    , System.Reflection.Missing.Value _
                                    , System.Reflection.Missing.Value _
                                    , System.Reflection.Missing.Value)
    End Function

 

    ''' <summary>
    ''' 打开Excel的进程
    ''' </summary>
    ''' <param name="xlApp">应用程序</param>
    ''' <returns>Excel的进程</returns>
    ''' <remarks></remarks>
    Private Function OpenExcel(ByVal xlApp As Excel.Application) As Excel.Application
        Dim sStr As String = Nothing
        Dim sVersion As String
        Dim sMessage As String = "没有发现Excel程序。/n请确认以下版本Microsoft Excel是否安装。/n・Excel97/n・Excel2000/n・ExcelXP/n・Excel2003/n・Excel2007"
        Try
            xlApp = New Excel.Application()
            xlApp.DisplayAlerts = False
            sVersion = xlApp.Version
        Catch ex As System.Runtime.InteropServices.COMException
            Throw New Exception(sMessage)
        End Try
        Dim strArray As String()
        Dim ch As Char = CChar(".")
        strArray = sVersion.Split(ch)
        If (strArray.Length > 0) Then
            sVersion = strArray(0)
        End If
        sStr = sVersion
        If ((sStr = Nothing) Or ((((sStr <> "8") And (sStr <> "9")) And ((sStr <> "10") And (sStr <> "11"))) And (sStr <> "12"))) Then
            Throw New Exception(sMessage)
        End If
        Return xlApp
    End Function

    ''' <summary>
    ''' 释放使用中的资源

    ''' </summary>
    ''' <remarks></remarks>
    Public Sub EndImport()
        If (Not (_xlApp Is Nothing)) Then
            _xlApp.Quit()
        End If
        If (Not (_xlBooks Is Nothing)) Then
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_xlBooks)
        End If
        If (Not (_xlBook Is Nothing)) Then
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_xlBook)
        End If
        If (Not (_xlSheet Is Nothing)) Then
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_xlSheet)
        End If
        Me._xlBooks = Nothing
        Me._xlBook = Nothing
        Me._xlSheet = Nothing
        If (Not (Me._xlApp Is Nothing)) Then
            Dim iGen As Integer
            iGen = System.GC.GetGeneration(_xlApp)
            Me._xlApp = Nothing
            System.GC.Collect(iGen)
        End If
    End Sub

 

 '杀死Excel进程
    Private Sub KillProcess(ByVal processName As String, ByVal beforeStartTime As System.DateTime, ByVal afterStartTime As System.DateTime)
        Dim mProcessList() As System.Diagnostics.Process
        Dim mProcessStartTime As System.DateTime
        mProcessList = System.Diagnostics.Process.GetProcessesByName(processName)
        For Each tmpProcess As System.Diagnostics.Process In mProcessList
            mProcessStartTime = tmpProcess.StartTime
            If (mProcessStartTime.CompareTo(beforeStartTime) > 0 And mProcessStartTime.CompareTo(afterStartTime) < 0) Then
                tmpProcess.Kill()
                Exit Sub
            End If
        Next
    End Sub

    ''' <summary>
    ''' 从文件的全路径中获取文件名

    ''' </summary>
    ''' <param name="sFullFilePath">ファイルのフルパス</param>
    ''' <returns>ファイル名</returns>
    ''' <remarks></remarks>
    Function GetFileName(ByVal sFullFilePath As String) As String
        Dim I As Integer
        Dim strFileName As String
        Dim str As String()
        If (Not (System.IO.File.Exists(sFullFilePath))) Then
            Return Nothing
        End If
        For I = Len(sFullFilePath) To 1 Step -1
            If CBool(InStr("/", Mid$(sFullFilePath, I, 1))) Then Exit For
        Next
        strFileName = Mid$(sFullFilePath, I + 1, Len(sFullFilePath) - I)
        str = strFileName.Split(CChar("."))
        strFileName = str(0)
        Return strFileName
    End Function

#End Region
End Class