Cách sử dụng Excel để đọc và ghi cơ sở dữ liệu bên ngoài

Excel hầu như có thể làm được mọi thứ; liệu nó có nên được thực hiện để làm mọi thứ hay không là một vấn đề khác. Mặc dù bảng tính rất mạnh trong việc thao tác dữ liệu, nhưng nó không quá tốt trong việc lưu trữ dữ liệu chuẩn hóa. Khai thác Excel vào cơ sở dữ liệu quan hệ như SQL Server tăng cường sức mạnh của ứng dụng.

Trước tiên, bạn sẽ cần MS Access hoặc phần mềm ổn định và miễn phí hơn – SQL Server Thể hiện. Giả định rằng người đọc đã hiển thị dải băng Nhà phát triển Excel và quen thuộc với Trình soạn thảo VBA và Ngôn ngữ truy vấn có cấu trúc (SQL). Bài viết này sử dụng SQL Server các chuỗi kết nối. Đối với MS-Access, hãy tham khảo Google.

Mặc dù Excel có các quy trình tích hợp sẵn để nhận thông tin từ SQL Server vào (giả sử) một bảng tổng hợp, ví dụ của chúng tôi sẽ linh hoạt hơn trong việc lựa chọn dữ liệu.

Chuỗi kết nối

Tôi sẽ sử dụng cơ sở dữ liệu riêng tư; chèn thông tin trình điều khiển của riêng bạn vào vị trí của tôi trong quy trình phụ ConnectDatabase. Sau đó chúng tôi sử dụng connDB như một kênh liên lạc tới cơ sở dữ liệu của chúng tôi – trong trường hợp của tôi để trả về kết quả từ một thủ tục được lưu trữ. Bạn có thể sử dụng các câu lệnh SQL tiêu chuẩn hơn như “Chọn * từ …”

Thứ tự kinh doanh

Đầu tiên, chúng tôi sẽ tải các lựa chọn hộp tổ hợp từ SQL Server Khi sổ làm việc được mở, sử dụng macro Auto_open và sao chép dữ liệu vào trang tính “ComboData”. Cho dù máy chủ đặt trên đám mây hay cục bộ, sẽ không có độ trễ đáng kể nào khi khởi động Excel – miễn là cơ sở dữ liệu có thể truy cập được từ máy trạm.

Tiếp theo, chúng tôi sẽ trích xuất dữ liệu đã lọc từ cơ sở dữ liệu và thả nó vào Excel, các cột từ F đến K.

Giao diện

Của tôi có hộp thả xuống để lọc thông tin từ cơ sở dữ liệu. Các Vai trò hộp tổ hợp kích hoạt tìm kiếm để điền vào bảng ở bên phải.

Đổi tên “Sheet1” thành “Main”. Thêm ít nhất một hộp tổ hợp.

Mật mã

Public connDB As New ADODB.Connection
Public rstNew As New ADODB.Recordset
Public rs As New ADODB.Recordset
Public strSQL As String
Public nID As Integer

Sub auto_Open()
    Call PopulateComboData     'kicks off the first process  on Open
End Sub

Sub PopulateComboData()
    Sheets("ComboData").Range("A3:C100").ClearContents
    Call ConnectDatabase      'use the ConnectDatabase routine
    strSQL = "Select DeptID, Department, Phase from tblDept Order by Department"
    Set rs = connDB.Execute(strSQL)
    ActiveSheet.Range("A3").CopyFromRecordset rs      'copies the recordset in bulk
End Sub

Sub ReadData()
    intRole = Sheets("main").Range("D7")
    Sheets("Main").Range("F4:L100").ClearContents
    Call ConnectDatabase
    strSQL = "EXEC DBTest " & intRole    'calls a stored proc with parameter
    Set rs = connDB.Execute(strSQL)
    ActiveSheet.Range("F4").CopyFromRecordset rs      'copies the recordset in bulk
End Sub

Sub ConnectDatabase()
    On Error GoTo ErrConnect
    If connDB.State = 1 Then connDB.Close     'closes connection if already open
    strServer = "197.200.28.164" 
    strDBase = "Qcrew_sql"
    strUser = "joesoap_sql"
    strPWD = "frU6ra!@"
    If strPWD > "" Then 
        strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & _
        ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPWD & _
        ";Connection Timeout=30;"
    Else        'Use windows authentication
        strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & _
        ";Trusted_Connection=yes;DATABASE=" & strDBase
    End If
    connDB.Open strConnectionstring
Exit Sub
ErrConnect:
    MsgBox Err.Description
End Sub

Định dạng điều khiển hộp tổ hợp để đọc trang tính “ComboData”. Sau đó nhấp chuột phải vào hộp tổ hợp để gán thủ tục phụ ReadData cho nó. Khi một mục được chọn trong hộp tổ hợp, hãy viết khóa của mục đó vào trang tính “Chính”, ô D7. Mã VBA sẽ sử dụng khóa này làm bộ lọc (xem intRole, ở trên).

Tham chiếu đến thư viện dll

Sử dụng Tools > References trong cửa sổ mã để tham chiếu đến thư viện Microsoft Active X Data Objects. Điều này sẽ cho phép Excel sử dụng các đối tượng ADODB được khai báo trong mã.

Quy trình phụ ReadData ở trên sử dụng cấu trúc dữ liệu quan hệ, được hiển thị ở bên dưới, rất khó đạt được chỉ trong Excel.

Các thay đổi dữ liệu khác có thể kích hoạt ghi lại cơ sở dữ liệu, với câu lệnh Cập nhật SQL thích hợp theo sau là connDB.execute(strSQL).

Cuối cùng, bảo vệ mã của bạn khỏi bị xem hoặc thay đổi:  Công cụ>Thuộc tính>Bảo vệ.

Xử lý các vấn đề về Excel:

Đôi khi, đặc biệt là khi chứa các chương trình phức tạp, Excel có thể gặp sự cố và không thể khôi phục đúng cách. trong trường hợp của một hư hỏng xlsx Việc sở hữu một công cụ phục hồi dữ liệu hiệu quả sẽ giải quyết hầu hết các vấn đề.

Giới thiệu tác giả:

Felix Hooker là một chuyên gia phục hồi dữ liệu trong DataNumen, Inc., công ty hàng đầu thế giới về công nghệ khôi phục dữ liệu, bao gồm sửa rar lôi và các sản phẩm phần mềm phục hồi sql. Để biết thêm thông tin, hãy truy cập www.datanumennăm