ในบทความนี้จะมาพูดถึงเรื่องการทํางานกับ file Excel ด้วย VB.NET โดยจะสามารถเข้าถึงข้อมูล แก้ไขข้อมูล และเพิ่มข้อมูล ด้วยคําสั่ง SQL เหมือนกับเป็น Database ตัวนึง ซึ่งการใช้งานจะต้องใช้ libraries ที่ชื่อว่า OleDb ใน Virtual Studio จะมีให้อยู่แล้ว

วิธีการใช้งานจะแบ่งเป็นสองแบบดังนี้

1. การใช้งานโดยไม่ต้องการผลลัพธ์ของข้อมูลเช่น insert, update, delete
2. การใช้งานโดยต้องการผลลัพธ์ของข้อมูลเช่น Query ข้อมูลจาก Excel(คําสั่ง Select)

ตัวอย่างการใช้งานในแบบที่1

Try
    Dim filePath = "test.xls" 'ชื่อของ File Excel ที่ต้องการ
    Dim conStr= String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", filePath)
    Dim cn As New System.Data.OleDb.OleDbConnection()
    Dim cm As New System.Data.OleDb.OleDbCommand()

    cn.ConnectionString = conStr
    cn.Open()

    With cm
        .Connection = cn
        'insert ข้อมูลลง Sheet1 2 column
        .CommandText = "INSERT INTO [Sheet1$] values ('111', 'ABC')"
        .ExecuteNonQuery() 'ทําการ Execute หรือ รัน SQL Command
    End With

    cn.Close() 'ปิดการเชื่อมต่อกับ File Excel
Catch ex As Exception
    errmsg = ex.Message
End Try

output: ในไฟล์ test.xls จะถูกเพิ่มข้อมูลลงใปใน “Sheet1” 1 row

ตัวอย่างการใช้งานในแบบที่2

Try
    Dim filePath = "test.xls" 'ชื่อของ File Excel ที่ต้องการ
    Dim conStr= String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", filePath)
    Dim MyConnection As System.Data.OleDb.OleDbConnection
    Dim DtSet As System.Data.DataSet
    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
    Dim cmd = ""
    Dim dt As New DataTable() 'สร้างตัวแปรสําหรับเก็บข้อมูลที่ Query ออกมา

    MyConnection = New System.Data.OleDb.OleDbConnection(conStr)
    cmd = "SELECT * FROM [Sheet1$]" 'SQL Command ที่ต้องการ
    MyCommand = New System.Data.OleDb.OleDbDataAdapter(cmd, MyConnection)
    DtSet = New System.Data.DataSet
    MyCommand.Fill(DtSet)
    dt = DtSet.Tables(0) 'นําข้อมุลที่ Query ออกมาได้ เก็บในตัวแปรโดยจะเป็นข้อมุลชนิด dataTable
    MyConnection.Close()
Catch ex As Exception
    errmsg = ex.Message
End Try

output: จะได้ข้อมูลจากไฟล์ test.xls ใน Sheet1 โดยข้อมูลจะอยู่ในตัวแปร dt เป็น dataTable สามารถทําข้อมูลไปใช้ได้ตามต้องการ