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。