.NET 处理Excel 2003+2007格式最佳方案

采用：NPOI[For 97-2003格式，.xls]+ EPPlus[For 2007格式，.xlsx]

 

函数实例1.

Public Shared Function Import2007(ByVal strFileName As String, ByVal SheetName As String) As DataTable
            Dim dt As New DataTable()
            Dim ColName As Object = ""
            Using file As New FileStream(strFileName, FileMode.Open, FileAccess.Read)
                Using package As ExcelPackage = New ExcelPackage(file)
                    Dim worksheet As ExcelWorksheet = package.Workbook.Worksheets(SheetName)
                    If worksheet Is Nothing Then
                        worksheet = package.Workbook.Worksheets(1)
                    End If
                    Dim iFrom As Integer = 1 : Dim iNull As Integer = 0
                    '读取列名
                    For i As Integer = worksheet.Dimension.Start.Column To worksheet.Dimension.End.Column
                        If worksheet.Cells(1, i).Value Is Nothing Then iNull += 1
                    Next
                    If iNull > 0 AndAlso iNull * 3 >= (worksheet.Dimension.End.Column - worksheet.Dimension.Start.Column + 1) * 2 Then iFrom += 1
                    For i As Integer = worksheet.Dimension.Start.Column To worksheet.Dimension.End.Column
                        ColName = worksheet.Cells(iFrom, i).Value
                        If ColName Is Nothing Then ColName = ""
                        Try
                            If worksheet.Cells(iFrom, i).StyleID <> 5 AndAlso IsDate(worksheet.Cells(iFrom, i).Text) Then
                                ColName = CDate(worksheet.Cells(iFrom, i).Text).ToString("yyyyMMdd")
                            End If
                        Catch ex As Exception
                        End Try
                        If IsDate(ColName) Then ColName = CDate(ColName).ToString("yyyyMMdd")
                        If ColName.ToString.Length = 0 Then
                            ColName = "F" & i.ToString("00")
                        End If
                        dt.Columns.Add(ColName.ToString)
                    Next
                    '读取数据行
                    For j As Integer = worksheet.Dimension.Start.Row + iFrom To worksheet.Dimension.End.Row
                        Dim dataRow As DataRow = dt.NewRow()
                        For i As Integer = worksheet.Dimension.Start.Column To worksheet.Dimension.End.Column
                            If worksheet.Cells(j, i).Value IsNot Nothing Then
                                If worksheet.Cells(j, i).StyleID <> 5 AndAlso IsDate(worksheet.Cells(j, i).Text) Then
                                    dataRow(i - worksheet.Dimension.Start.Column) = worksheet.Cells(j, i).Text
                                Else
                                    dataRow(i - worksheet.Dimension.Start.Column) = worksheet.Cells(j, i).Value
                                End If
                            End If
                        Next
                        dt.Rows.Add(dataRow)
                    Next
                End Using
            End Using
            Return dt
        End Function