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

素颜马尾好姑娘i 2022-09-11 07:10 118阅读 0赞

设计界面：

代码：

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教程][vb.net] 目录

————————————————  
版权声明：本文为CSDN博主「VB.Net」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。

[vb.net]: https://www.xin3721.com/eschool/vbnetxin3721/