ExcelADO chứng tỏ làm thế nào để sử dụng ADO để đọc và ghi dữ liệu trong bảng tính Excel


Tóm tắt


Mẫu ExcelADO.exe mô tả cách bạn có thể sử dụng các đối tượng dữ liệu ActiveX (ADO) của Microsoft Jet OLE DB 4.0 nhà cung cấp để đọc và ghi dữ liệu vào sổ làm việc Microsoft Excel.

Thông tin


Tại sao sử dụng ADO?

Sử dụng ADO truyền dữ liệu hoặc truy xuất dữ liệu từ một bảng tính Excel cho bạn, nhà phát triển, một số lợi ích trong tự động hóa Excel:
  • Hiệu năng. Microsoft Excel là một máy chủ ActiveX ra quá trình. ADO chạy trong quá trình, và tiết kiệm chi phí chi phí cuộc gọi ra quá trình.
  • Khả năng mở rộng. Đối với các ứng dụng Web, không luôn luôn mong muốn tự động Microsoft Excel. ADO trình bày một giải pháp khác scaleable xử lý dữ liệu trong một bảng tính.
ADO có thể được sử dụng đúng để nguyên dữ liệu sang sổ làm việc. Bạn không thể sử dụng ADO để áp dụng định dạng hoặc công thức vào ô. Tuy nhiên, bạn có thể chuyển dữ liệu sang sổ làm việc được định dạng trước và duy trì các định dạng. Nếu bạn cần định dạng "điều kiện" sau khi dữ liệu được đưa vào, bạn có thể thực hiện định dạng này tự động hoặc với macro trong sổ làm việc.

Máy bay phản lực OLE DB nhà cung cấp chi tiết cụ thể cho sổ làm việc Excel

Cơ sở dữ liệu Microsoft Jet có thể được sử dụng để truy cập dữ liệu trong các định dạng tệp cơ sở dữ liệu, chẳng hạn như sổ làm việc Excel qua cài đặt trình điều khiển lập chỉ mục thứ tự truy cập phương pháp (ISAM). Để mở các định dạng bên ngoài được hỗ trợ bởi Microsoft phản lực 4,0 OLE DB nhà cung cấp, bạn chỉ định loại cơ sở dữ liệu trong các thuộc tính mở rộng kết nối. Các máy bay phản lực OLE DB nhà cung cấp hỗ trợ loại cơ sở dữ liệu sau cho sổ làm việc Microsoft Excel:
  • Excel 3.0
  • Excel 4.0
  • Excel 5.0
  • Excel 8.0
Chú ý: sử dụng Excel 5.0 nguồn cơ sở dữ liệu nhập sổ làm việc Microsoft Excel 5.0 và 7.0 (95) và sử dụng Excel 8.0 nguồn cơ sở dữ liệu nhập sổ làm việc Microsoft Excel 8.0 (97) và 9.0 (2000). Mẫu ExcelADO.exe sử dụng sổ làm việc Excel trong Excel 97 và Excel 2000 định dạng.


Mẫu sau chứng tỏ việc ADO kết nối với một Excel 97 (hoặc 2000):
Dim oConn As New ADODB.Connection
With oConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open "C:\Book1.xls"
'....
.Close
End With
- hoặc -
Dim oConn As New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;" & _
"Extended Properties=""Excel 8.0;"""
oConn.Close
Quy ước đặt tên bảng

Có một số cách bạn có thể tham khảo bảng (hay nhiều) trong một bảng tính Excel:
  • Sử dụng tên trang theo một dấu (ví dụ: [Sheet1$] hoặc [của tôi $ bảng]). Bàn làm việc được tham chiếu theo cách này bao gồm phạm vi được sử dụng toàn bộ bảng tính.
    oRS.Open "Select * from [Sheet1$]", oConn, adOpenStatic
  • Sử dụng một loạt các tên được định nghĩa (ví dụ: [Table1]).
    oRS.Open "Select * from Table1", oConn, adOpenStatic
  • Sử dụng nhiều địa chỉ cụ thể (ví dụ: [Sheet1$ A1: B10]).
    oRS.Open "Select * from [Sheet1$A1:B10]", oConn, adOpenStatic
Tiêu đề bảng

Với sổ làm việc Excel, dòng đầu tiên trong phạm vi được coi là dòng tiêu đề (hoặc tên trường) theo mặc định. Nếu phạm vi đầu tiên không có tiêu đề, bạn có thể chỉ định HDR = NO tính mở rộng trong chuỗi kết nối của bạn. Nếu hàng đầu tiên có tiêu đề, nhà cung cấp OLE DB tự động đặt tên trường cho bạn (trong đó F1 sẽ biểu thị trường đầu tiên, F2 sẽ biểu thị trường thứ hai, và như vậy cứ).

Loại dữ liệu

Không giống như cơ sở dữ liệu truyền thống, không có cách nào trực tiếp để xác định loại dữ liệu cho các cột trong bảng Excel. Thay vào đó, các nhà cung cấp OLE DB quét một số trong một cột "đoán" kiểu dữ liệu cho trường. Số dòng quét mặc định là tám (8) hàng; bạn có thể thay đổi số dòng quét bằng cách xác định giá trị giữa một (1) và mười sáu (16) để cài đặt MAXSCANROWS vùng mở rộng chuỗi kết nối của bạn.

Tệp bao gồm các mẫu

Các tập tin ExcelADO.exe có một dự án Visual Basic tiêu chuẩn EXE, trang chủ hiện hoạt (ASP), Excel 97 và Excel 2000 sổ làm việc làm khuôn mẫu và cơ sở dữ liệu Microsoft Access 2000. Tệp bao gồm có như sau:

Tệp dự án tiêu chuẩn EXE Visual Basic
  • ExcelADO.vbp
  • Form1.frm
  • Form1.frx
Active Server Pages
  • EmpData.asp
  • Orders.asp
Sổ làm việc Microsoft Excel
  • OrdersTemplate.xls
  • EmpDataTemplate.xls
  • ProductsTemplate.xls
  • SourceData.xls
Cơ sở dữ liệu Microsoft Access
  • Data.mdb

Làm thế nào để sử dụng mẫu

Trích xuất nội dung của tệp .exe sang một thư mục.

Sử dụng các dự án Visual Basic:
  1. Trong Visual Basic, mở tệp ExcelADO.vbp.
  2. Trên Project menu, chọn tài liệu tham khảovà rồi đặt tham chiếu đến Microsoft ADO số q và bảo mậtThư viện đối tượng dữ liệu Microsoft ActiveX. Này hoạt động mã mẫu ADO 2.5 và ADO 2.6, nên chọn phiên bản phù hợp với máy tính của bạn.
  3. Nhấn phím F5 để chạy chương trình. Biểu mẫu cho diễn xuất hiện.
  4. Bấm vào mẫu 1. Mẫu này tạo một bản sao của OrdersTemplate.xls. Sau đó, nó sử dụng ADO để kết nối với sổ làm việc và mở một tập bản ghi vào bảng có nhiều quy định trong sổ làm việc. Tên phạm vi là Orders_Table. Sử dụng ADO AddNew/ phương phápCập Nhật thêm hồ sơ (hoặc hàng) phạm vi được xác định trong sổ làm việc. Khi bổ sung hàng hoàn tất, ADO nối đóng và sổ làm việc được hiển thị trong Microsoft Excel. Hãy làm theo các bước sau để thực hiện việc này:
    1. Chèn menu trong Excel, chọn tênvà chọn xác định.
    2. Trong danh sách được xác định tên, chọn Orders_Table. Chú ý tên được xác định đã phát triển để bao gồm các hồ sơ mới được thêm vào. Xác định tên được sử dụng, cùng với chức năng bù Excel, tính tổng dữ liệu vào bảng tính.
    3. Thoát khỏi Microsoft Excel và trở về ứng dụng Visual Basic.
  5. Bấm vào mẫu 2. Mẫu này tạo một bản sao của EmpDataTemplate.xls. Sử dụng ADO để kết nối với bảng tính và sử dụng phương pháp thực hiện kết nối ADO chèn dữ liệu (đưa vào trong SQL) vào sổ làm việc. Dữ liệu được thêm vào xác định phạm vi (hay bảng) trong sổ làm việc. Khi dữ liệu được chuyển giao, đóng kết nối và việc kết quả được hiển thị trong Excel. Sau khi bạn kiểm tra sổ làm việc, bỏ Microsoft Excel, và sau đó trở về ứng dụng Visual Basic.

  6. Bấm vào mẫu 3. Mẫu này tạo một bản sao của ProductsTemplate.xls. Sử dụng Microsoft ADO 2.1 mở rộng DLC và bảo mật thư viện đối tượng (ADOX) để thêm một mục mới (hoặc trang tính mới) vào sổ làm việc. Một ADO tập bản ghi sau đó được thu thập cho bảng mới và dữ liệu được thêm vào bằng cách sử dụng AddNew/ phương phápCập Nhật . Khi bổ sung hàng hoàn tất, ADO nối đóng và sổ làm việc được hiển thị trong Excel. Sổ làm việc chứa Visual Basic for Applications (VBA) mã macro trong sự kiện mở cho sổ làm việc. Macro chạy khi mở sổ làm việc; nếu bảng "Sản phẩm" mới tồn tại trong sổ làm việc, mã macro định dạng bảng tính và sau đó xoá mã macro. Kỹ thuật này trình bày cách cho nhà phát triển Web để di chuyển mã định dạng máy chủ Web và trên máy khách. Ứng dụng Web có thể dòng một sổ làm việc định dạng có chứa dữ liệu cho khách hàng và cho phép mã macro sẽ thực hiện bất kỳ "có điều kiện" định dạng có thể không thể trong một khuôn mẫu để chạy máy khách.

    Chú ý: để kiểm tra mã macro, xem mô-đun ThisWorkbook trong VBAProject ProductsTemplate.xls.


  7. Bấm vào Mẫu 4. Mẫu này tạo ra kết quả tương tự như mẫu 1, nhưng kỹ thuật được sử dụng để truyền dữ liệu là hơi khác nhau. Mẫu 1, ghi (hoặc hàng) được thêm vào bảng tính một lần. Mẫu 4 thêm hồ sơ với số lượng lớn đính kèm bảng Excel vào cơ sở dữ liệu truy cập và chạy một truy vấn phụ thêm (hoặc chèn vào... CHỌN từ) để thêm bản ghi bảng trong bảng truy cập vào bảng Excel. Sau khi chuyển xong, bảng Excel được tách ra từ cơ sở dữ liệu Access và việc kết quả được hiển thị trong Excel. Thoát khỏi Excel và trở về ứng dụng Visual Basic.


  8. Mẫu cuối cùng mô tả cách bạn có thể đọc dữ liệu từ một bảng tính Excel. Chọn một mục trong danh sách thả xuống, và sau đó nhấp vào mẫu 5. Cửa sổ ngay lập tức hiển thị nội dung của bảng mà bạn đã chọn. Nếu bạn chọn một bảng toàn bộ ("Sheet1$" hoặc "Sheet2$") bảng, cửa sổ ngay lập tức hiển thị nội dung tầm sử dụng bảng tính đó. Lưu ý rằng phạm vi sử dụng không nhất thiết phải bắt đầu trên dòng 1, cột 1 của bảng tính. Phạm vi sử dụng bắt đầu từ ô trên phần còn lại trong bảng chứa dữ liệu.


    Nếu bạn chọn một địa chỉ phạm vi cụ thể hoặc một loạt các định nghĩa, ngay lập tức cửa sổ hiển thị nội dung của chỉ đó trên trang tính.
Sử dụng trang chủ hiện hoạt (ASP):
  1. Tạo một thư mục mới có tên ExcelADO trong thư mục chính của máy chủ Web. Lưu ý rằng đường dẫn mặc định cho thư mục C:\InetPut\WWWRoot.
  2. Sao chép các tập tin bạn đã tạo ở bước trước:
    • EmpData.asp
    • Orders.asp
    • Data.mdb
    • EmpDataTemplate.xls
    • OrdersTemplate.xls

  3. Tập lệnh ASP trong mẫu tạo bản sao của mẫu sổ làm việc với phương pháp sao FileSystemObject. Phương pháp sao chép thành công, máy khách đang truy cập các tập lệnh phải truy nhập ghi vào thư mục chứa ASP.
  4. Điều hướng đến Orders.asp (, http://YourServer/ExcelADO/Orders.ASP), và lưu ý rằng trình duyệt Hiển thị một bảng tính Excel tương tự như một mẫu 1 ứng dụng Visual Basic.
  5. Điều hướng đến EmpData.asp (, http://YourServer/ExcelADO/EmpData.ASP), và lưu ý rằng trình duyệt Hiển thị một bảng tính Excel tương tự như một mẫu 2 ứng dụng Visual Basic.

(c) Microsoft Corporation 2000, tất cả các quyền. Đóng góp bởi Truc B. Turner, Microsoft Corporation.

Tham khảo


Để biết thêm thông tin, hãy bấm vào số bài viết sau để xem bài viết trong Cơ sở Kiến thức Microsoft:

195951 HOWTO: truy vấn và Cập Nhật Excel dữ liệu bằng ADO từ ASP

194124 PRB: Excel giá trị trả về là NULL sử dụng DAO OpenRecordset
193998 HOWTO: đọc và hiển thị dữ liệu nhị phân trong ASP
Thông tin 247412 : phương pháp truyền dữ liệu sang Excel từ Visual Basic
257819 HOWTO: sử dụng ADO với dữ liệu Visual Basic hoặc VBA Excel