笔记：不依赖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.IOImports NPOI.SS.UserModelImports NPOI.HSSF.UserModelImports 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 RegionEnd Class

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

 

Dim excelFile = New Excel("D:\読込先\S001.xls")Dim v = excelFile.Sheets(0).Cells("A33")

需要注意的地方：

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