Scrape HTML Add-In là một Excel Add-In đơn giản cho phép bạn dễ dàng thu thập nội dung HTML từ các website mà không cần viết mã VBA. Hầu hết các công cụ/ add-in dùng để trích xuất dữ liệu rất phức tạp và không linh hoạt khi chỉ lấy một vài thông tin HTML đơn giản. Scrape HTML Add-In sử dụng các biểu thức chính quy thay vì XPath.
Trước khi tìm hiểu chi tiết, hãy cùng xem ví dụ để thấy việc trích title từ trang web Google dễ dàng như thế chỉ bằng cách sử dụng Add-Ins- được xây dựng thành các hàm:
Nó hoạt động như thế nào?
Dưới đây là ví dụ cho từng phần. Chức năng cốt lõi của Add-In là các hàm có sẵn trong Excel ngay sau khi Add-In được kích hoạt.
- GetElementById– trích từng nhân tố HTML đơn lẻ bằng id từ trang web.
- GetElementByName – trích từng nhân tố HTML đơn lẻ bằng tên từ trang web.
- GetElementByRegex- trích HTML từ web bằng các biểu thức chính quy
- GetRegex– trích một đoạn văn bản từ văn bản bất kỳ bằng các biểu thức chính quy
- RegexReplace- thay thế tất cả các mẫu biểu thức chính quy thành văn bản cụ thể
- And more…
Việc kết hợp các hàm này với nhau sẽ cho phép bạn trích dẫn được hầu hết được bất kỳ nội dung nào từ website. Sử dụng hàm GetElementByRegex để trích lấy phần đầu tiên của website rồi dùng hàm GetRegex để vét đến nội dung cuối cùng. Sau đó, sử dụng hàm RegexReplace cho đầu ra.
The Add-In
Khi Add-In được kích hoạt, nó sẽ xuất hiện trong Excel ribbon như thế này:
Add-In bao gồm những đặc tính sau:
- Insert function– chèn một trong những hàm Get* vào ô được chọn
- Cache settings– cho phép bạn thay đổi bộ nhớ đệm của HTML để tăng khả năng hoạt động
- Regex Tester– công cụ để kiểm tra các biểu thức chính quy
- Scrape HTML Tool– công cụ trích lấy tất cả các phần khớp với một mẫu cụ thể đã có
- Automatic Updating– công cụ cho phép tự động cập nhập với worksheet được chọn (chỉ áp dụng với các hàm GetElementBy*)
Regex Tester
Khi trích xuất dữ liệu từ trang website, thường có vấn đề xảy ra là bạn không thể xác định được các biểu thức chính quy có đúng không ngay từ ban đầu. Regex Tester cho phép bạn nhanh chóng kiểm tra từng ký tự. Công cụ này phản ứng với bất kì thay đổi nào trong các biểu thức được phê chuẩn cho văn bản được trích dẫn. Điều này giúp tiết kiệm thời gian. Hãy xem video sử dụng Regex Tester dưới đây:
Scrape HTML Tool
Các hàm Get* trích xuất những dữ liệu cụ thể từ website. Tuy nhiên, nếu bạn muốn nhanh chóng lấy được tất cả các thông tin theo một dạng nhất định thì công cụ Scrape HTML Tool sẽ rất có ích. Nó có rất nhiều các biểu thức chính quy được định nghĩa trước như là trích URLs và img src properties.
Xem video dưới đây về cách Scrape HTML Tool làm thế nào để giúp bạn:
Caching- lưu trữ trong bộ nhớ đệm
Ý tưởng chủ yếu của Add-In là giảm việc phải viết các mã VBA. Tuy nhiên, xảy ra vấn đề khi sử dụng các hàm Get* với cùng một URL bởi vì chúng sẽ quá tải khi phải download cùng một nội dung HTML nhiều lần. Điều này sẽ ảnh hưởng nghiêm trọng đến quá trình hoạt động. Để giải quyết vấn đề này, hãy sử dụng chức năng Cache- lưu trữ trong bộ nhớ đêm. Nếu bạn sử dụng cùng một URL với hơn một hàm Get* thì: với điều kiện là thời gian chờ của bộ nhớ Cache chưa hết, HTML content sẽ không bị refresh từ Web mà chỉ từ Cache. Tương tự, nếu bạn có 2 hàm Get* với cùng một URL thì trong 60s chờ, chỉ có hàm đầu tiên phải tải nội dung từ Web, trong khi hàm thứ hai không cần tải- với điều kiện là hàm thứ hai phải được gọi trước khi 60s chờ kết thúc. Bạn có thể thay đổi phần cài đặt này tại “Cache settings”.
Automatic Updating- Tự động cập nhập
Khi định file Excel để trích dẫn dữ liệu từ các websites HTML, thường có vấn đề xảy ra là làm thế nào để refresh (làm mới) dữ liệu từ web môt cách định kỳ. Ví dụ, bạn muốn lấy dữ liệu về giá cổ phiếu và refresh nó trong mỗi 2 phút. Bạn có thể làm thủ công, viết một mã VBA thông minh sử dụng chức năng Application.OnTime hay chỉ đơn giản là cài đặt chức năng refresh theo chu kỳ trong công cụ Automatic Update của Add-In.
Xem ví dụ dưới đây:
Ví dụ về trích lấy một bảng HTML
Sử dụng bảng HTML trên trang w3schools
Trích mỗi ô trong bảng vào từng ô Excel riêng biệt và điều này chỉ mất có vài phút
Thực hiện như thế nào?
Đầu tiên, hãy trích dẫn cả bảng vào ô B2 bằng cách sử dụng hàm GetElementbyRegex
=GetElementByRegex("http://www.w3schools.com/html/html_tables.asp";"<table class=""reference"" style=""width:100%"">([^""]*?)</table>") |
Thực hiện điều này trong một ô để tối ưu workbook (trong trường hợp worksheet thực hiện tính toán lại thì nội dung này sẽ không phải tải lại riêng cho mỗi ô). Chú ý regex ([^”]*?), nó có nghĩa là không lấy tất cả các ký tự, chỉ mỗi bảng này được trích dẫn trong biểu thức chứ không phải tất cả các bảng. Sử dụng ký tự (.*)? Là không đủ bởi vì dấu chấm không thích hợp với newlines.
Tiếp theo là lấy các ô header ( lấy các header tiếp theo bằng cách thay đổi số cuối cùng từ 0-3)
=GetRegex(GetRegex($B$1;"<tr>([^""]*?)</tr>";0);"<th>([^""]*?)</th>";0) |
Nó lấy ra dòng đầu tiên và trích xuất thành header đầu tiên
Tương tự với các ô khác (các cột và dòng)
=GetRegex(GetRegex($B$1;"<tr>([^""]*?)</tr>";1);"<td>([^""]*?)</td>";0) |
Nó lấy ra dòng thứ hai và trích xuất chúng vào ô đầu tiên
Tài liệu
Tải tài liệu hoàn chỉnh tại link sau:
- Bài viết được chia sẽ trên trang www.tuhocexcel.net , Chia sẽ vui lòng ghi rõ nguồn gốc bài đăng.
- Group tham gia Tự Học Excel : https://www.facebook.com/groups/1716543358373810/
- Clip Tự học Excel - Xem tại đây : http://www.tuhocexcel.net/videos
- Mọi thắc mắc, góp ý vui lòng mail về địa chỉ : tuhocexcel2018@gmail.com
hoặc liên hệ qua : http://www.tuhocexcel.net/p/lien-he-tu-hoc-excel.html
