ExcelReader
来源:互联网 发布:淘宝sku图片大小多少 编辑:程序博客网 时间:2024/03/28 17:42
Imports System.DataImports System.Data.OleDbImports System.IO''' <summary>''' 读取Excel''' </summary>Public Class ExcelReader Implements IDisposable '连接字符串说明 'HDR=Yes:将第一行作为DataTable的列名,根据该列的数据判断该列的数据类型 'HDR=No:将所有行都作为数据,所有的数据类型都是string,空值为空字符串"" 'IMEX=0:汇出模式,这个模式开启的Excel档案只能用来做“写入”用途。 'IMEX=1:汇入模式,这个模式开启的Excel档案只能用来做“读取”用途。 'IMEX=2:连結模式,这个模式开启的Excel档案可同时支持“读取”与“写入”用途。 Private Const connectionString As String = "Provider={0};Data Source={1};Extended Properties='{2};HDR={3};IMEX=1'" Private con As OleDbConnection Sub New(ByVal filename As String) Me.New(filename, True) End Sub Sub New(ByVal filename As String, ByVal hasHead As Boolean) ChangeExcel(filename, hasHead) End Sub Protected Sub ChangeExcel(ByVal filename As String, ByVal hasHead As Boolean) con = Nothing If ValidatePath(filename) Then con = New OleDbConnection con.ConnectionString = GetConnectionStr(filename, hasHead) End If End Sub Public Function ReadDataSet() As DataSet If con Is Nothing Then Return Nothing Dim sheetNames As List(Of String) = GetSheetNames() sheetNames.RemoveAll( Function(n) Return Not n.EndsWith("$") End Function) Return ReadDataSet(sheetNames) End Function Public Function ReadDataSet(ByVal sheetNames As IEnumerable(Of String)) As DataSet If con Is Nothing Then Return Nothing Dim ds As New DataSet For Each name In sheetNames Dim dt As DataTable = ReadDataTable(name) Try ds.Tables.Add(dt) Catch ex As Exception MsgBox(ex.Message) End Try Next Return ds End Function Public Function ReadDataTable(ByVal sheetName As String) As DataTable If con Is Nothing Then Return Nothing '过滤隐藏表,Oledb读取表会在表后面加上$符号,对于一些有公式的sheet,OleDb会创建一个隐藏表,但这些表没有加上$符号 If sheetName.EndsWith("$") Then sheetName = sheetName.Substring(0, sheetName.Length - 1) End If Dim dt As New DataTable(sheetName) Dim sqlText As String = String.Format("select * from [{0}$]", sheetName) Using da As New OleDbDataAdapter(sqlText, con) Try da.Fill(dt) Catch ex As Exception Return Nothing End Try End Using Return dt End Function ''' <summary> ''' 获取Sheet名称列表 ''' </summary> Public Function GetSheetNames() As IEnumerable(Of String) If con Is Nothing Then Return Nothing con.Open() Dim dt As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) Dim list As New List(Of String) For Each row As DataRow In dt.Rows list.Add(row("TABLE_NAME").ToString) 'System.Threading.Interlocked.Increment(i) Next con.Close() Return list End Function ''' <summary> ''' 验证文件Path ''' </summary> Protected Shared Function ValidatePath(ByVal excelPath As String) As Boolean If File.Exists(excelPath) Then Dim suffix As String = Path.GetExtension(excelPath).ToLower If suffix.Equals(".xls") OrElse suffix.Equals(".xlsx") Then Return True End If End If Return False End Function ''' <summary> ''' 构造连接字符串 ''' </summary> Protected Shared Function GetConnectionStr(ByVal excelPath As String, ByVal hasHead As Boolean) As String Dim constr As String = String.Empty If File.Exists(excelPath) Then Dim suffix As String = Path.GetExtension(excelPath).ToLower Dim excelVersion As String Dim provider As String If suffix.Equals(".xlsx") Then provider = "Microsoft.Ace.OleDb.12.0" excelVersion = "Excel 12.0" ElseIf suffix.Equals(".xls") Then provider = "Microsoft.Jet.OLEDB.4.0" excelVersion = "Excel 8.0" Else Return String.Empty End If Return String.Format(connectionString, provider, excelPath, excelVersion, If(hasHead, "yes", "no")) End If Return String.Empty End Function#Region "IDisposable Support" Private disposedValue As Boolean ' 检测冗余的调用 ' IDisposable Protected Overridable Sub Dispose(ByVal disposing As Boolean) If Not Me.disposedValue Then If disposing Then ' TODO: 释放托管状态(托管对象)。 End If con.Dispose() ' TODO: 释放非托管资源(非托管对象)并重写下面的 Finalize()。 ' TODO: 将大型字段设置为 null。 End If Me.disposedValue = True End Sub ' TODO: 仅当上面的 Dispose(ByVal disposing As Boolean)具有释放非托管资源的代码时重写 Finalize()。 'Protected Overrides Sub Finalize() ' ' 不要更改此代码。请将清理代码放入上面的 Dispose(ByVal disposing As Boolean)中。 ' Dispose(False) ' MyBase.Finalize() 'End Sub ' Visual Basic 添加此代码是为了正确实现可处置模式。 Public Sub Dispose() Implements IDisposable.Dispose ' 不要更改此代码。请将清理代码放入上面的 Dispose(ByVal disposing As Boolean)中。 Dispose(True) GC.SuppressFinalize(Me) End Sub#End RegionEnd Class