ในบทความนี้จะมาพูดถึงเรื่องการทํางานกับ file Excel ด้วย VB.NET โดยจะสามารถเข้าถึงข้อมูล แก้ไขข้อมูล และเพิ่มข้อมูล ด้วยคําสั่ง SQL เหมือนกับเป็น Database ตัวนึง ซึ่งการใช้งานจะต้องใช้ libraries ที่ชื่อว่า OleDb ใน Virtual Studio จะมีให้อยู่แล้ว
วิธีการใช้งานจะแบ่งเป็นสองแบบดังนี้
- การใช้งานโดยไม่ต้องการผลลัพธ์ของข้อมูลเช่น insert, update, delete
- การใช้งานโดยต้องการผลลัพธ์ของข้อมูลเช่น 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 สามารถทําข้อมูลไปใช้ได้ตามต้องการ