[VB.NET] Kết nối ADO để thao tác với Excel

[tuhocvba]

1. Thiết định thư viện:
Bạn xem thao tác

Bạn cần đăng nhập để thấy link

.
Bạn hãy add các thư viện sau vào COM:
Một là,Microsoft ActiveX Data Objects 6.1 Library .
Hai là, Microsoft OLEDB Service Component 1.0 Type Library .
2. Kết nối tới Excel:

Dim cn As ADODB.Connection
Dim sFilePath As String
 
cn = New ADODB.Connection
sFilePath = "Link file Excel"
 
'# Thong tin ket noi
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                      "Data Source=" & sFilePath & ";" &
                      "Extended Properties=""Excel 12.0;HDR=Yes;"""
 
'#　Ket noi
cn.Open()

Provider : Microsoft.ACE.OLEDB.12.0 có thể làm việc với cả Office 32bit và Office 64 bit.
Microsoft.Jet.OLEDB.4.0 thì chỉ làm việc với Office 32bit.
Data Source : Hãy chỉ định đường dẫn fullpath của file excel. vd: D:\NhanSu\tuhocvba.xlsx
Extended Properties : Chủng loại file mà chúng ta sẽ thao tác. Từ excel 2007 trở về đây thì chỉ định là : 12.0
HDR=Yes : Coi dòng đầu tiên là dòng tiêu đề, không phải dữ liệu. Nếu coi dòng đầu tiên là data thì chỉ định là No.
Nếu file Excel cùng thư mục với file Tool thì bạn có thể viết:

sFilePath = Application.StartupPath & "\TuhocVBA.xlsx"

3. Thao tác với Excel

Dim rs As ADODB.Recordset
Dim sSql As String
 
rs = New ADODB.Recordset
'# Cu phap SQL
sSql = "Cú pháp SQL"
 
rs.Open(sSql, cn, adOpenDynamic, adLockOptimistic, adCmdText)

Trong thực tế lưu ý với các bạn rằng, tôi cho vô VB.NET là không chạy vì nó không hiểu các hằng số trên.
Bạn có thể thay trực tiếp trị số vào code trên. Cụ thể:
adOpenDynamic = 2. Nó có ý nghĩa như sau: Nếu có người khác thêm hay xóa dữ liệu thì nó vẫn nhìn thấy được điều đó.
Tham khảo thêm

Bạn cần đăng nhập để thấy link

, vì là tiếng nhật nên anh em có thể cho vô google translate dịch giúp mình và chia sẻ điều bạn thu hoạch được trong topic này giúp mình.

adLockOptimistic = 3. Khóa không cho người khác update trong khi ta đọc dữ liệu.
Tham khảo thêm

Bạn cần đăng nhập để thấy link

.
adCmdText = 1. Định nghĩa lệnh như là Text.
Tham khảo thêm

Bạn cần đăng nhập để thấy link

.
Như vậy lệnh trên viết lại là:

rs.Open(sSql, cn, 2, 3, 1)

Vậy là OK rồi đấy.
4. Cú pháp SQL:
Tùy mục đích bạn muốn lấy dữ liệu như thế nào. Bạn có thể tham khảo thêm

Bạn cần đăng nhập để thấy link

.
Ví dụ:

sSql = "SELECT DISTINCT SignalName FROM Test"

5. Chỉ định adUseClient :
Khi thực thi truy vấn thông qua cú pháp SQL, giá trị trả về sẽ là một bản ghi.
Nếu không chỉ định điều này thì xử lý sẽ không trơn tru mượt mà.
Vì chúng ta trả dữ liệu về Client(Local) nên tôi sẽ xử lý code như dưới đây.

Dim rs As ADODB.Recordset
Dim sSql As String
 
rs = New ADODB.Recordset
'Cu phap SQL
sSql = "Cu phap SQL"
 
'Chi dinh Client
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
 
rs.Open(sSql, cn, 2, 3, 1)

6. Không tìm thấy Provider.
Môi trường phát triển của tôi là trên win 64bit, office 64bit.
Tuy nhiên khi chia sẻ tool cho người khác thì không chạy được. Họ dùng office 32bit.
Trong trường hợp này hãy cài đặt AccessDatabaseEngine.exe .
Download ở đây:

https://www.microsoft.com/ja-jp/download/details.aspx?id=13255

7. Biên dịch Exe:
Không liên quan ADODB lắm, nhưng với AnyCPU, thì nó sẽ ưu tiên biên dịch theo win64 của tôi, do đó bạn hãy thực hiện biên dịch hai lần x86 và x64 thì tốt hơn.

Bài viết tham khảo

Bạn cần đăng nhập để thấy link

và dựa trên trải nghiệm thực tế từ bản thân tôi. Viết lại chia sẻ cho các bạn, cũng là để bản thân sau này có thể coi lại không lo quên mất.

 

[NhanSu]

Code đơn giản của mình test lấy dữ liệu excel đổ vào datagridview, chương trình lấy được cả xls.

Imports System.Data
Imports System.Data.OleDb

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim cnn As New OleDbConnection(TextBox1.Text)
        Dim cmd As New OleDbCommand(TextBox2.Text, cnn)
        Dim da As New OleDbDataAdapter(cmd)
        Dim ds As New DataSet()
        da.Fill(ds)
        Dim dt As New DataTable()
        dt = ds.Tables(0)
        DataGridView1.DataSource = dt
        cnn.Close()
    End Sub
End Class

Bạn cần đăng nhập để thấy đính kèm

 

[tuhocvba]

datagridview hay nhỉ.
Vậy nếu mình gắn datagridview trên form2 chỉ để hiển thị, nhưng vẫn thao tác trên form1 thì có được không @NhanSu ?

 

[NhanSu]

Mình chưa thử nhưng chắc là được, datagridview chỉ hiển thị nội dung, còn lại vẫn làm trên DataSet và datatable

 

[Deleted member 199]

Code đơn giản của mình test lấy dữ liệu excel đổ vào datagridview, chương trình lấy được cả xls.

Imports System.Data
Imports System.Data.OleDb

Bác cho em hỏi hai cái dòng này bác tra cứu ở đâu vây?
Như em tra ADODB thì nó là:

Imports System.Data.OleDb
Imports ADODB

Nguồn

Bạn cần đăng nhập để thấy link

.
Không biết có cách nào tự tra trên máy tính thay vì google không bác?

 

[NhanSu]

@duyhieu61 bài đó là ado, ado không phải thành phần trong .net. ADO.NET không có recordset. Mình thích dùng hàng chính chủ của .NET hơn. Còn lệnh đó ở đâu thì mình cũng không rõ, mình đọc nhiều nguồn nhưng chủ yếu là C# với từ khóa using, mình sửa đại thành Imports thôi.

 

[tuhocvba]

Nếu như trong Module 1 cũng dùng tới ADO thì có cần phải viết lại

Imports System.Data.OleDb
Imports ADODB

trên Module 1 không nhỉ?

 

[NhanSu]

Cần chứ. Lệnh imports không add reference thư viện mà chỉ có tác dụng rút gọn code bằng cách bỏ qua namespace. Nếu không imports thì phải khai báo đầy đủ namespace:
Dim cn as System.Data.OleDb.OleDbConnection
Do ADODB không phải thư viện chuẩn của .NET nên cần add reference.

 

[Lương Tuấn Ba]

hoặc các bạn có thể dùng chuỗi kết nối sau

Driver={Microsoft Excel Driver (*.xls)};Dbq=C:\MyExcel.xls;ReadOnly=0;
Tham khảo thêm tại đây

Bạn cần đăng nhập để thấy link