VB.Net 中怎样读Excel

[reasonchan]

thanks

[poplarjvjv1981]

Dim lcConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FILENAME.XLS;Extended Properties=Excel 8.0;"
Dim lobjConn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(lcConnStr)
Dim lobjAdapter As System.Data.OleDb.OleDbDataAdapter
Dim lcSql As String = "select * from [Sheet1$]"
lobjConn.Open()
lobjAdapter = New System.Data.OleDb.OleDbDataAdapter(lcSql, lobjConn)
Dim ds As System.Data.DataSet = New DataSet
Try
lobjAdapter.Fill(ds)
Catch ex As Exception
Finally
lobjConn.Close()
End Try

[SeeSunSet]

Dim App As New Excel.Application()
Dim Wk As Workbook
Dim Ws As Worksheet
Wk = App.Workbooks.Open("D:\test.xls")
Ws = Wk.Sheets("Sheet1")
If ExecuteSql("delete from tmpWorkTime") = False Then
MsgBox("删除记录出错", MsgBoxStyle.Exclamation + MsgBoxStyle.OKOnly, "提示")
Exit Sub
End If
Dim i As Int16 : Dim Gstr As String
ProgressBar1.Maximum = endrow.Text - initrow.Text
ProgressBar1.Minimum = 0
For i = initrow.Text To endrow.Text

If ExecuteSql("insert into tmpWorkTime(workcode,workname,worktype,worktime,addtime) values('" & Ws.Range(dh.Text & i.ToString).Value & "','" & Ws.Range(xm.Text & i.ToString).Value & "','" & Ws.Range(gz.Text & i.ToString).Value & "'," & Ws.Range(sb.Text & i.ToString).Value & "," & Ws.Range(jb.Text & i.ToString).Value & ")") = False Then
MsgBox("删除记录出错", MsgBoxStyle.Exclamation + MsgBoxStyle.OKOnly, "提示")
Exit Sub
End If
ProgressBar1.Value = i - initrow.Text
Next
MsgBox("获取记录成功", MsgBoxStyle.Exclamation + MsgBoxStyle.OKOnly, "提示")
Wk.Close()
App = Nothing

[zhw2ll]

'自己实验出来的，一定没有问题。

Private Sub readexcel()
'创建一个数据链接
Dim strCon As String = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" & Application.StartupPath & "\config.xls;Extended Properties=Excel 8.0"

'初始化连接对象
Dim myConn As New OleDbConnection(strCon)

'定义查询字符串
Dim strCom As String = " SELECT * FROM [Sheet1$] "

'打开数据链接，得到一个数据集
myConn.Open()

'[依据查询字符串和连接对象来初始化sqlcommand对象]
Dim myCommand As New OleDbDataAdapter(strCom, myConn)

'创建一个 DataSet对象
Dim myDataSet = New DataSet()
'得到自己的DataSet对象

If myDataSet.Tables.Count > 0 Then
myDataSet.Tables(0).Clear()
End If

'获取数据填充数据集
myCommand.Fill(myDataSet, "[Sheet1$]")

'关闭此数据链接
myConn.Close()


'========================================================读出数据
'MsgBox(myDataSet.Tables(0).Rows.Count)
Dim newrow As DataRow
For Each newrow In myDataSet.Tables(0).Rows
Me.ListBox1.Items.Add(newrow.Item(0).ToString)
Me.ListBox1.Items.Add(newrow.Item(1).ToString)
Next

注意：Excel文档的工作薄名字为sheet1，其中有两列数据。

[sz_lgp]

二种典型方法上面都说了，都很好!

[zhw2ll]

'写入数据〔注意：工程必须引用Excel库〕============================

Dim App As New Excel.Application()
Dim Wk As Excel.Workbook
Dim Ws As Excel.Worksheet
Wk = App.Workbooks.Open(Application.StartupPath & "\config.xls")
Ws = Wk.Sheets("Sheet1")


Ws.Cells(4, 1) = "fffff"


Wk.Close(True)
App.Quit()
GC.Collect()

[youlinga]

挖哈哈 路过~!!