vb.net如何读取excel里面的sheet内容!代码已有,求其他方法
现在已有的代码如下
Public Sub ReadExcelForPayment(ByVal RemoveFormat As Boolean)
Dim ExcelOLEDataAdapter As New OleDbDataAdapter("Select * From [Sheet1$]", ExcelOLEConnection)
Dim ExcelDataRow As DataRow
Dim ExcelDataColumn As DataColumn
ExcelDataSet.Clear()
ExcelOLEDataAdapter.Fill(ExcelDataSet)
'ExcelOLEConnection.Close()
Try
'Check Null Column in Excel
For Each ExcelDataRow In ExcelDataSet.Tables(0).Rows
For Each ExcelDataColumn In ExcelDataSet.Tables(0).Columns
If ExcelDataRow.IsNull(ExcelDataColumn) Then
If ExcelDataColumn.DataType Is GetType(System.DateTime) Then
ExcelDataRow(ExcelDataColumn) = Windows.Forms.DateTimePicker.MinDateTime
ElseIf ExcelDataColumn.DataType Is GetType(System.Int32) Then
ExcelDataRow(ExcelDataColumn) = 0
ElseIf ExcelDataColumn.DataType Is GetType(System.Double) Then
ExcelDataRow(ExcelDataColumn) = 0.0
ElseIf ExcelDataColumn.DataType Is GetType(System.String) Then
ExcelDataRow(ExcelDataColumn) = ""
End If
End If
Next
Next
Catch e As Exception
Throw e
End Try
End Sub
----------------------
但上面代码只能仅限于excel里面的sheet名字系Sheet1的才能读取到,如果为其他名字就会出错,读取不了!
现在求方法,不管里面的sheet为什么名字,都能读取到里面的内容,能否在上面的代码稍微修改下??
在线等,谢谢了!~
vb.net
excel
select
--------------------编程问答--------------------
// Get the name of the first worksheet:
DataTable dbSchema = dbConnection.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null);
string firstSheetName = dbSchema.Rows [0] ["TABLE_NAME"].ToString ();
*****************************************************************************
签名档: http://feiyun0112.cnblogs.com/
--------------------编程问答--------------------
Quote: 引用 1 楼 feiyun0112 的回复:
// Get the name of the first worksheet:
DataTable dbSchema = dbConnection.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null);
string firstSheetName = dbSchema.Rows [0] ["TABLE_NAME"].ToString ();
*****************************************************************************
这里怎么在我上面的源代码进行修改添加呢?新手上路,请见谅!
--------------------编程问答--------------------
dim dbSchema as DataTable = ExcelOLEConnection.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null)
dim firstSheetName as string = dbSchema.Rows (0) ("TABLE_NAME").ToString ()
Dim ExcelOLEDataAdapter As New OleDbDataAdapter("Select * From " + firstSheetName , ExcelOLEConnection)
--------------------编程问答--------------------
引用 3 楼 feiyun0112 的回复: dim dbSchema as DataTable = ExcelOLEConnection.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null)
dim firstSheetName as string = dbSchema.Rows (0) ("TABLE_NAME").ToString ()
Dim ExcelOLEDataAdapter As New OleDbDataAdapter("Select * From " + firstSheetName , ExcelOLEConnection)
dim dbSchema as DataTable = ExcelOLEConnection.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null)
这里的最后的null,没有值,引用不了,怎么办??
--------------------编程问答--------------------
nothing
--------------------编程问答--------------------
Dim ExcelOLEDataAdapter As New OleDbDataAdapter("Select * From [Sheet1$]", ExcelOLEConnection)修改一下[Sheet1$]
--------------------编程问答--------------------
引用 6 楼 kxyzjm62 的回复: Dim ExcelOLEDataAdapter As New OleDbDataAdapter("Select * From [Sheet1$]", ExcelOLEConnection)修改一下[Sheet1$]
这个地方有改动吗???我的源代码就是这句,这里只能读取excel里面Sheet1名字的,如果为其他名命名就不能读取的了
--------------------编程问答--------------------
引用 5 楼 feiyun0112 的回复: nothing ...
Dim dbSchema As DataTable = ExcelOLEConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
用了这句就这个提示
GetOleDbSchemaTable requires an open and available Connection. The connection's current state is closed.
求助~~~~
--------------------编程问答--------------------
提示很清楚了
先ExcelOLEConnection.Open()
--------------------编程问答--------------------
除
--------------------编程问答--------------------
引用 9 楼 feiyun0112 的回复: 提示很清楚了
先ExcelOLEConnection.Open()
我用下面代码执行,执行到 ExcelOLEDataAdapter.Fill(ExcelDataSet) 出现了这个错误,
Syntax error in FROM clause.
代码如下:
-----------------------------
Public Sub ReadExcelForPaymentYS(ByVal RemoveFormat As Boolean)
ExcelOLEConnection.Open()
Dim dbSchema As DataTable = ExcelOLEConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim firstSheetName As String = dbSchema.Rows(0)("TABLE_NAME").ToString()
Dim ExcelOLEDataAdapter As New OleDbDataAdapter("Select * From " + firstSheetName, ExcelOLEConnection)
'Dim ExcelOLEDataAdapter As New OleDbDataAdapter("Select * From [Payment$]", ExcelOLEConnection)
Dim ExcelDataRow As DataRow
Dim ExcelDataColumn As DataColumn
ExcelDataSet.Clear()
ExcelOLEDataAdapter.Fill(ExcelDataSet)
'ExcelOLEConnection.Close()
Try
'Check Null Column in Excel
For Each ExcelDataRow In ExcelDataSet.Tables(0).Rows
For Each ExcelDataColumn In ExcelDataSet.Tables(0).Columns
If ExcelDataRow.IsNull(ExcelDataColumn) Then
If ExcelDataColumn.DataType Is GetType(System.DateTime) Then
ExcelDataRow(ExcelDataColumn) = Windows.Forms.DateTimePicker.MinDateTime
ElseIf ExcelDataColumn.DataType Is GetType(System.Int32) Then
ExcelDataRow(ExcelDataColumn) = 0
ElseIf ExcelDataColumn.DataType Is GetType(System.Double) Then
ExcelDataRow(ExcelDataColumn) = 0.0
ElseIf ExcelDataColumn.DataType Is GetType(System.String) Then
ExcelDataRow(ExcelDataColumn) = ""
End If
End If
Next
Next
Catch e As Exception
Throw e
End Try
End Sub
版主,求救啊!
--------------------编程问答--------------------
Dim ExcelOLEDataAdapter As New OleDbDataAdapter("Select * From [" + firstSheetName +"]", ExcelOLEConnection)
补充: .NET技术 , VB.NET