Add-Ins Cho Excel - Tổng Hợp Dữ Liệu Sheet Và File (11 người xem)

[Kiều Mạnh]

1/ Tôi thấy nhiều Bạn có nhu cầu Tổng hợp dữ liệu Sheet và File có cấu trúc dữ liệu như nhau

2/ Trong AddIns tôi úp phía dưới là 1 bộ siêu tập SQL trong đó bao gồm một số tiện ích khác

3/ phạm vi chủ đề này tôi chỉ giới thiệu 1 tiện ích nhỏ tổng hợp dữ liệu theo Sheet và File có cấu trúc dữ liệu như nhau theo đúng mô tả của tiêu đề thớt này

4/ là bản thử nghiệm và đang phát triển ... tôi Úp lên đây thăm dò nhu cầu sử dụng của người dùng thực tế muốn gì vvv....

5/ Sau khi hoàn tất tôi sẽ xóa hết các File úp trên này và chuyển nó vào kho lưu trữ trên github.com

6/ Quá trình sử dụng ai có nhu cầu mong muốn điều chỉnh viết thêm gì + Lỗi thì cứ mô tả chi tiết càng tốt ....
Tôi sẽ xem xét nếu trong phạm vi điều chỉnh và phù hợp với cái chung nhất tôi sẽ viết lại trong khả năng của mình

7/ Quá trình sử dụng ai có mong muốn mang tính cá nhân thì liên hệ riêng ...
Phạm vi thớt này chỉ điều chỉnh phục phụ nhu cầu chung nhất mang tính đại chúng...

Tiện ích nhỏ giới thiệu cho thớt này theo hình sau



Danh mục hổ trợ + From SQL Builder:



1/ Lấy dữ liệu File *.csv và gộp dữ liệu từ nhiều File CSV khác nhau vào 1 File duy nhất gán kết quả lên Sheet
2/ Lấy dữ liệu file Excel + Tổng hợp file + Sheet với tùy chọn
3/ lấy dữ liệu file Access + tổng hợp file + Tablename với tùy chọn
4/ Lấy dữ liệu File SQLite + tổng hợp file + Tablename với tùy chọn
5/ Lấy dữ liệu qua Internet + tổng hợp file + Tablename với tùy chọn
6/ Lấy dữ liệu Ms Server + tổng hợp Tablename với tùy chọn
7/ Hổ trợ builder SQL với các CSDL nêu trên ( Thiếu CSV bổ sung sau )

8/ Hổ trợ truy xuất File trong Folder là tiếng Việt có dấu ( Unicode ) ..... trừ App Server sẽ điều chỉnh sau

9/ Thêm phím tắt tạm dừng khi tìm kiếm files đệ quy folder lớn trên 15 GB bằng phím Esc

Hổ trợ Tìm kiếm nhanh có cải thiện tốc độ nhanh hơn ... để truy xuất dữ liệu nhanh hơn khi cần tìm

1/ Chọn Folder ... sau khi chọn thành công Path Folder sẽ lưu vào CSDL Registry ... lần sau đó ko cần nữa trừ khi chọn mới
2/ Chọn mục File Type với các phần mở rộng tích hợp sẳn Or gõ trực tiếp vào đó
3/ Tùy chọn duyệt Folder đệ quy hay ko đệ quy folder
4/ Hổ trợ tìm kiếm nhanh trong Folder trên 10 GB ... lúc trước trên máy cùi của tôi duyệt Folder 7GB có 29.185 File chạy hết 35 giây thì nay còn 23.469 giây nhanh hơn khoãng 30 % .... Nếu máy cấu hình tốt chỉ mất trên 5 giây

5/ Hổ trợ Mở Folder của file cần tìm kiếm khi kích đúp chuột lên CheckListbox ....

4/ Xem hình sau




Chỉ dẫn sơ bộ sử dụng From SQL Power Query Builder Vui lòng xem bài số #35

Hướng dẫn sử dụng:
1/ Để sử dụng được COM Add-Ins thì bắt buộc các bạn phải đăng ký sử dụng nó với Windows

2/ Tùy thuộc vào Bạn đang sử dụng Office 32 hay 64 bit thì đăng ký theo DLL 32 bit hay 64 bit

3/ Nếu sau khi đăng ký mà thay đổi thư mục đăng ký thì thực hiện lại

4/ Nếu trước đó đã đăng ký mà tải DLL mới thì xóa DLL củ đi copy DLL mới thay thế vào là ok mà ko cần đăng ký nữa

5/ Đính kèm theo DLL là các File *.vbs . Tùy theo máy Bạn cài Office 32 bit hay 64 bit thì sử dụng nó cho phù hợp

6/ Để đăng ký được DLL Bạn phải có quyền Admin trên PC đó khi chạy file *.vbs trong cùng Folder chọn Yes
--------------------------------------------------------------------
dự án code tiếp tục phát triển viết thêm tiếp, điều chỉnh , bổ sung or loại bỏ chức năng thừa + Fix lỗi nếu phát hiện ra ...

 

[Kiều Mạnh]

Chức năng tùy chọn theo Sheet ... theo thời gian sẻ bổ sung từ từ bao gồm cả Tổng hợp file qua Internet thông qua SQL TCP/IP

 

[Kiều Mạnh]

sau lần bị virus mã hóa dữ liệu mất sạch làm tôi ám ảnh diết .... Vì vậy khuyên ai đó nếu tải file ở bất kỳ nơi nào về mà trình duyệt chặn lại hay trình diệt virus mặc định của Windows10 xóa đi thì cũng ko nên luyến tiếc nữa ... hạy xóa nó đi trừ khi bạn biết chắc chắn nó vô hại ... ko nên quá tin vào ai đó sẽ ám ảnh hoài như tôi

File bài số 1 đã check Virus như hình sau

VirusTotal

VirusTotal

www.virustotal.com

 

[Kiều Mạnh]

Mới viết thêm hàm tổng hợp tất cả các File trong Folder To Sheet

Thong thả chơi là chính tôi sẽ mô phỏng 1 số chức năng viết gần giống theo Power Query và Power BI của Ms

lấy dữ liệu thô chưa xử lý lên Sheet .... còn lại ai đó tùy chỉnh lại cho phù hợp với nhu cầu sử dụng

Mcode cảm giác thấy bốc mùi lắm .... khó chơi

Video 2022 11 27 192355

Image Video 2022 11 27 192355 hosted in Gifyu

gifyu.com

 

[tieutuvodanh192]

Mới viết thêm hàm tổng hợp tất cả các File trong Folder To Sheet

Thong thả chơi là chính tôi sẽ mô phỏng 1 số chức năng viết gần giống theo Power Query và Power BI của Ms

lấy dữ liệu thô chưa xử lý lên Sheet .... còn lại ai đó tùy chỉnh lại cho phù hợp với nhu cầu sử dụng

Mcode cảm giác thấy bốc mùi lắm .... khó chơi

Video 2022 11 27 192355

Image Video 2022 11 27 192355 hosted in Gifyu

gifyu.com

Em đã thử cài addins của anh, nhưng khi click vào nút Form Folder thì bị báo lỗi như vậy:

 

[Kiều Mạnh]

Em đã thử cài addins của anh, nhưng khi click vào nút Form Folder thì bị báo lỗi như vậy:

View attachment 283966

đây he ... File bài này khác bài số 1 mới bổ sung thêm nó

1/ Chon Folder bất kỳ nó sẽ lấy hết dữ liệu trong Folder đó lên Sheet ... Lưu ý nó duyệt Folder đệ Quy sẽ lấy hết dữ Liệu File Excel + Access trong Folder chọn
2/ đang test thử sau khi hoàn thiện sẽ thêm tùy chọn duyệt Folder đệ quy hay không đệ quy
3/ kế hoạch tiếp theo sẽ cho lên Form tùy chọn File và tùy chon Sheet cần lấy

VD: trong File có 10 Sheet nhưng ta chỉ cần lấy 3 Sheet thôi vvv...

Xem hình duyệt File + tổng hợp như sau



Demos ... Lưu ý toàn bộ DLL úp trên này là thử nghiệm ... sau khi hoàn thiện cho lên kho Lưu trữ và xóa hết File trên thớt này

 

[tieutuvodanh192]

Em đã test thử với dữ liệu lớn khoảng 250k dòng và 40 cột thì tốc độ nhỉnh hơn Power Query xíu, mất 18s, PQ mất khoảng 25s.

 

[Kiều Mạnh]

Em đã test thử với dữ liệu lớn khoảng 250k dòng và 40 cột thì tốc độ nhỉnh hơn Power Query xíu, mất 18s, PQ mất khoảng 25s.

chắc chắn sẽ nhanh hơn và viết các tùy chọn thân thiện khác .... mà Power Query chưa hổ trợ tự động hóa ( nếu có ta phải mò viết code )

VD: tùy chọn lấy vài Sheet trong 1 File = checkBox = rất nhanh với kích chuột

Hãy thử kéo với Số dòng lên 1048570 test thử mới thấy hết .... Còn vài ngàn dòng ko tính

chạy code xong chờ uống cafe

 

[Mr.hieudoanxd]

Tên anh ở GiiHub là gì ạ. Để em tìm, xem lượm thêm được cái gì không anh Kiều Mạnh

 

[Kiều Mạnh]

Tên anh ở GiiHub là gì ạ. Để em tìm, xem lượm thêm được cái gì không anh Kiều Mạnh

bấm vào chữ ký đó

 

[tieutuvodanh192]

Em đã test kỹ hơn thì thấy rằng file tổng hợp của anh không yêu cầu tiêu đề phải đồng nhất giữa các file con 100%, chỉ copy dữ liệu từ các file con và nối vào nhau trong file tổng theo thứ tự các cột. Điều này khá tốt so với PQ.

Tuy nhiên sau khi tổng hợp số liệu bằng Addins này xong thì không thể edit các file con lại trong khi vẫn đang mở file Tổng, với PQ thì hoàn toàn có thể Edit file con và Refresh lại data ngay ở file tổng đang mở.
Tiêu đề cột nếu có dấu chấm "." khi tổng hợp file bằng Addins thì sẽ bị đổi thành dấu "#".

 

[Kiều Mạnh]

Em đã test kỹ hơn thì thấy rằng file tổng hợp của anh không yêu cầu tiêu đề phải đồng nhất giữa các file con 100%, chỉ copy dữ liệu từ các file con và nối vào nhau trong file tổng theo thứ tự các cột. Điều này khá tốt so với PQ.

Tuy nhiên sau khi tổng hợp số liệu bằng Addins này xong thì không thể edit các file con lại trong khi vẫn đang mở file Tổng, với PQ thì hoàn toàn có thể Edit file con và Refresh lại data ngay ở file tổng đang mở.
Tiêu đề cột nếu có dấu chấm "." khi tổng hợp file bằng Addins thì sẽ bị đổi thành dấu "#".

1/ cái đó ko khó lắm sẽ thêm tùy chon lấy tiêu đề hoặc ko lấy tiêu đề sau ... đang test quan trọng có chạy ra kết quả hay ko cái đã

2/ sau khi chạy xong code Close mọi kết nối là xong tha hồ Edit file con

3/ Thích sẽ lưu cái Path vào registry xong chỉ cần Refresh lại data là nó lấy mặc định cái File Or Folder đã lưu trước đó

4/ hãy làm thử 3 file mỗi File có 3 Sheet ... tổng số dòng của 3 File đó cộng lại = 1048570 dòng xong chạy code thử xem sao

5/ Hoặc chọn 1 File có 3 Sheet với tổng số dòng của 3 Sheet + lại = 1048570 x 50 cột thử chạy code

hóng kết quả ???

 

[Kiều Mạnh]

Sáng tới giờ có ai rảnh thử với Power Query số dòng = 1048570 x 50 cột chưa vậy .... xong thử chạy AddIns của tôi viết xem sao ???

Phạm vi Excel nên thử tải mọi cái với Số dòng hiện có xem sao = lòi hết ra thôi .... còn vài trăm ngàn dòng ko tính

Nhanh hay siêu của siêu nhanh cũng lòi ra hết

 

[Kiều Mạnh]

tieutuvodanh192​

Tôi vừa Fix lại theo yêu cầu và úp lại File bài số 1

Sau khi chạy code xong thì đóng mọi kết nối cho phép Edit các File con và mở lên chỉnh sửa file con

Tạm tới đó ... mai mốt tính tiếp

Phải công nhận là có tư duy và khả năng test code rất tốt ... Tôi lại thích điều đó

Chứ ko như ai đó có ai đó soi ra cái điểm yếu của code là mặt sưng lên như cái thớt và sau đó là ồn ào

Cảm ơn rất nhiều

 

[huhumalu]

Có cách nào không cần đăng ký mà vẫn dùng được như add-in thông thường không anh ?
Vì việc chạy đăng ký bằng file *.vbs yêu cầu phải full control như tài khoản Administrator.

 

[Kiều Mạnh]

Có cách nào không cần đăng ký mà vẫn dùng được như add-in thông thường không anh ?
Vì việc chạy đăng ký bằng file *.vbs yêu cầu phải full control như tài khoản Administrator.

Viết *.xll mà add nó vào là xong ... còn DLL kiểu bài 1 liên quan XML thì khó chưa mò ra

Hoặc dùng hàm API của tôi viết xong khai báo API trong File *.xlam mà dùng là ko cần đăng ký nữa

Còn RegFreeCOM thì úp bên thớt khác có rồi

 

[huhumalu]

Để em xem lại mấy cái RegFreeCOM của anh.

 

[Kiều Mạnh]

ai rảnh thử với Porver Query + BI với dữ liệu 1048570 dòng x 50 cột xem .... chắc chắn là xì khói ....

Còn lèo tèo vài trăm ngàn dòng ko tính

 

[tieutuvodanh192]

ai rảnh thử với Porver Query + BI với dữ liệu 1048570 dòng x 50 cột xem .... chắc chắn là xì khói ....

Còn lèo tèo vài trăm ngàn dòng ko tính

Em vừa chạy thử tổng hợp 50 file con, mỗi file 40 cột, tổng số dòng gộp lại vượt 1 triệu và đã đạt giới hạn của 1 sheet.
Thời gian của PQ hết 140 s, còn Addins của anh em chạy hết 122 s anh nha.

 

[huhumalu]

Em vừa chạy thử tổng hợp 50 file con, mỗi file 40 cột, tổng số dòng gộp lại vượt 1 triệu và đã đạt giới hạn của 1 sheet.
Thời gian của PQ hết 140 s, còn Addins của anh em chạy hết 122 s anh nha.

View attachment 284059

Cho xin bộ file mẫu thử nghiệm và PQ với