笔记：不依赖Office组件的Excel文件的读取

Microsoft.Office.Interop.Excel.dll是个接口，.NET和Office组件间的接口。

所以在没有安装Office的环境中，用Microsoft.Office.Interop.Excel.dll来操作Excel的代码是运行不了的。

不依赖Office组件的Excel操作lib有很多，但据说最靠谱的是NPOI。

openXml是针对Office2007及之后的版本的，它操作不了2003。

NPOI的2.0版本支持2003和2007以后版本。但1.25版只支持2003版。

从它新增的dll来看，2.0版对2007的支持是通过openXml来实现的。

缺点是：处理2007及以后版本的速度很慢。

下面是一个NPOI的封装类。它只具有读取功能。

 

Imports System.IO
Imports NPOI.SS.UserModel
Imports NPOI.HSSF.UserModel
Imports NPOI.XSSF.UserModel

''' <summary>
''' エクセル扱うクラス
''' </summary>
Public Class Excel

    Private ExcelFileStream As System.IO.FileStream
    Private ExcelFileInfo As FileInfo
    Private ExcelWorkBook As IWorkbook
    Private ExcelSheets As List(Of ExcelSheet)

    ''' <summary>
    ''' シート
    ''' </summary>
    Public ReadOnly Property Sheets() As List(Of ExcelSheet)
        Get
            Return ExcelSheets
        End Get
    End Property

    ''' <summary>
    ''' ファイル情報
    ''' </summary>
    Public ReadOnly Property FileInfo() As FileInfo
        Get
            Return ExcelFileInfo
        End Get
    End Property

    Sub New(ByVal filePath As String)

        Try

            ExcelFileStream = New System.IO.FileStream(filePath, FileMode.Open, FileAccess.Read)

        Catch ex As IOException

            Throw ex
        End Try

        ExcelFileInfo = New FileInfo(filePath)

        If ".xls".Equals(ExcelFileInfo.Extension) Then

            'office2003及びその前のバージョンのエクセルの場合
            ExcelWorkBook = New HSSFWorkbook(ExcelFileStream)
        Else
            'office2007及びその後のバージョンのエクセルの場合
            ExcelWorkBook = New XSSFWorkbook(ExcelFileStream)
        End If

        ExcelSheets = New List(Of ExcelSheet)

        For i As Integer = 0 To ExcelWorkBook.NumberOfSheets - 1

            Dim sh As New ExcelSheet

            sh.Sheet = ExcelWorkBook.GetSheetAt(i)

            ExcelSheets.Add(sh)
        Next

    End Sub

    ''' <summary>
    ''' エクセルファイルをクローズする
    ''' </summary>
    Public Sub Close()

        ExcelFileInfo = Nothing
        ExcelFileStream.Close()
        ExcelWorkBook = Nothing
        ExcelSheets = Nothing
    End Sub

#Region "シートクラス"

    Public Class ExcelSheet

        Private _sheet As ISheet

        ''' <summary>
        ''' シートオブジェクト
        ''' </summary>
        Public Property Sheet() As ISheet
            Get
                Return _sheet
            End Get
            Set(ByVal value As ISheet)
                _sheet = value
            End Set
        End Property

#Region "セルの値を取得する"

        ''' <summary>
        ''' セルの値を取得する処理
        ''' </summary>
        ''' <param name="position">行列の文字列</param>
        ''' <returns>セルの値</returns>
        Public Function Cells(ByVal position As String) As Object

            Dim row As Long
            Dim col As Long

            '文字列を数字にコンバートする
            ConvertStrToRowCol(position, row, col)

            Dim excelRow = Sheet.GetRow(row)

            If excelRow Is Nothing Then

                Return String.Empty
            End If

            Dim cellValue = excelRow.GetCell(col)

            If cellValue Is Nothing Then
                Return String.Empty
            Else
                Return cellValue.ToString()
            End If

        End Function
#End Region
        

#Region "文字列から列行に変換する処理"
        ''' <summary>
        ''' 文字列から列行に変換する処理
        ''' </summary>
        ''' <param name="str ">文字列</param>
        ''' <param name="row ">行</param>
        ''' <param name="col ">列</param>
        ''' <returns>true</returns>
        Private Function ConvertStrToRowCol(ByVal str As String, ByRef row As Long, ByRef col As Long) As Boolean

            Dim TmpRow As Long = 0
            Dim TmpCol As Long = 0
            Dim offsetNum As Long = 0

            If str.Length <= 0 Then
                Return False
            End If

            str = UCase(str)

            Dim index As Integer = 1

            While index <= str.Length

                Dim tchar As Char = Convert.ToChar(Mid(str, index, 1))

                If Asc(tchar) >= Asc("A") And Asc(tchar) <= Asc("Z") Then

                    TmpCol = (TmpCol + offsetNum) * (Asc("Z") - Asc("A") + 1)
                    TmpCol = TmpCol + (Asc(tchar) - Asc("A"))
                    offsetNum = 1

                ElseIf Asc(tchar) >= Asc("0") And Asc(tchar) <= Asc("9") Then

                    TmpRow = TmpRow * (Asc("9") - Asc("0") + 1)
                    TmpRow = TmpRow + (Asc(tchar) - Asc("0"))
                Else
                    Exit While
                End If

                index = index + 1
            End While

            row = TmpRow - 1
            col = TmpCol

            Return True
        End Function
#End Region

    End Class

#End Region
End Class

这个类可以通过类似如下的方式来读取单元格的值。

 

Dim excelFile = New Excel("D:\読込先\S001.xls")

Dim v = excelFile.Sheets(0).Cells("A33")

需要注意的地方：

ISheet的GetRow方法在读取没有被访问过的单元格时会返回Nothing。