vb.net 教程 8-4 excel操作 2

版权声明：本文为博主原创文章，转载请在显著位置标明本文出处以及作者网名，未经作者允许不得用于商业目的。

设计界面：

代码：

1、引用OleDb

Imports System.Data.OleDb

2、定义两个全局变量：

    Dim filename As String = "d:\销售.xls"

    Dim conn As OleDbConnection

3、窗体载入的时候：

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        conn = New OleDbConnection()
        conn.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0;data source=" & filename & ";Extended Properties='Excel 12.0 XML;HDR=YES;'" 
        conn.Open()
    End Sub

4、显示所有数据，使用OleDbDataReader逐一读取，参看：vb.net 教程 8-3 数据库操作2，请大家注意select语句，使用方括号"[工作表名$]"来表示要操作的工作表。

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim sql As String = "select * from [Sheet1$]"
        Dim cmd As New OleDbCommand(sql, conn)


        Dim dr As OleDbDataReader = cmd.ExecuteReader
        Dim fc As Integer = dr.FieldCount

        Dim shuju As String = ""

        If dr.HasRows Then
            Dim strtemp As String = ""
            Do While dr.Read
                strtemp = ""
                For i As Integer = 0 To fc - 1
                    strtemp &= dr(i) & " "
                Next
                strtemp &= ControlChars.CrLf
                shuju &= strtemp
            Loop

        End If
        TextBox1.Text = shuju
        dr.Close()
    End Sub

执行后：

5、同样的，如果选择单价7000以上的电脑：

    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        Dim sql As String = "select * from [Sheet1$] where 价格>7000"
        Dim cmd As New OleDbCommand(sql, conn)


        Dim dr As OleDbDataReader = cmd.ExecuteReader
        Dim fc As Integer = dr.FieldCount

        Dim shuju As String = ""

        If dr.HasRows Then
            Dim strtemp As String = ""
            Do While dr.Read
                strtemp = ""
                For i As Integer = 0 To fc - 1
                    strtemp &= dr(i) & " "
                Next
                strtemp &= ControlChars.CrLf
                shuju &= strtemp
            Loop

        End If
        TextBox1.Text = shuju
        dr.Close()
    End Sub

运行时：

6、插入新数据：

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim sql As String = "insert into [Sheet1$] values(7,'华硕','飞行堡垒',8099,17)"
        Dim cmd As New OleDbCommand(sql, conn)

        Dim fc As Integer = cmd.ExecuteNonQuery()

    End Sub

运行时：

7、更新数据：

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Dim sql As String = "update [Sheet1$] set 型号='玩家国度',价格=10799  where 编号=7"
        Dim cmd As New OleDbCommand(sql, conn)

        Dim fc As Integer = cmd.ExecuteNonQuery()
    End Sub

运行时：

8、删除数据，请看完后面的说明。

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim sql As String = "delete * from [Sheet1$] where 日期=20180301"
        Dim cmd As New OleDbCommand(sql, conn)

        Dim fc As Integer = cmd.ExecuteNonQuery()
    End Sub

非常遗憾，删除的时候会出错：

经过多方查证，使用操作数据库的方法对excel表是不能执行删除的。

以后在讲office操作的时候会讲到如何删除数据。

 

 

 

由于.net平台下C#和vb.NET很相似，本文也可以为C#爱好者提供参考。

学习更多vb.net知识，请参看vb.net 教程 目录