Imports System.Data.OleDb
Public Class Form1
'需要先引入ado 库
'功能函数,读取excel
'这个函数可以不用管内部的实现方法
'传入 excel名称,与excel sheet表名,'从excel中读取数据,返回dataset
'dataset 是一种数据结构,使用方法在下一个函数
Function ReadExcel(FileName As String, SheetName As String)
Dim _Connectstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FilePath>;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"""
Dim excConn As New OleDb.OleDbConnection(_Connectstring.Replace("<FilePath>", FileName))
'上两行打开一个读取excel的链接
Dim mydataset As DataSet = New DataSet
Using da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM [" & SheetName & "$]", excConn)
Try
da.Fill(mydataset, SheetName)
Catch ex As Exception
Console.WriteLine(ex)
End Try
End Using
'上面一段将数据写入到一个dataset中
Return mydataset
End Function
'从dataset中读取一个数据
'传入上个函数返回的dataset ,sheet名, 第几行,第几列, 返回数据
'用法看内部
Function ReadCell(dataset As DataSet, SheetName As String, row As Integer, col As Integer)
Return dataset.Tables(SheetName).Rows(row).Item(col)
End Function
'下面函数包含了一个示例=——=
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim ExcelDataSet As DataSet = New DataSet
'调用ReadExcel 函数从excel读取到dataset
ExcelDataSet = ReadExcel("D:\Orders1.xls", "Orders")
For i = 1 To 10
For j = 1 To 2
'调用readcell 从dataset中读取某一单元格的数据
Console.WriteLine(ReadCell(ExcelDataSet, "Orders", i, j))
TextBox1.AppendText(ReadCell(ExcelDataSet, "Orders", i, j))
'换行
TextBox1.AppendText(vbCrLf)
Next j
Next i
End Sub
End Class
网友评论