Addin VSTO dùng ADO.NET truy vấn Excel

NhanSu

SMod
Thành viên BQT
Chào các bạn. Lâu rồi mới quay lại diễn đàn. Mình xin đóng góp addin dùng để truy vấn Excel. Cách sử dụng: cài đặt xong, chạy addin ADO.NET Excel, form xuất hiện để lựa chọn file cần truy vấn. Nhập câu truy vấn vào text box, với lệnh SELECT bấm nút Run SQL, nếu INSERT hay UPDATE thì bấm nút Insert or Update. Bấm nút Save sẽ lưu kết quả vào ô hiện hành. Mình upload source và bộ cài (nằm trong folder bin\release\publish) trước, nếu các bạn có VS thì có thể build project, nếu không thì chạy cài đặt cũng được.
- Nếu muốn không lấy tiêu đề thì sửa chuỗi kết nối HDR từ YES thành NO
- Chú ý nếu truy vấn file đang mở thì có thể nút Run không chạy, các bạn bấm vào sheet rồi quay lại form sẽ chạy bình thường (mình chưa rõ tại sao). Không kết nối được file trên onedrive đang mở, các bạn cần đóng file trước.
Một số câu lệnh ví dụ:
SELECT * FROM [Sheet1$]
SELECT * FROM [Sheet1$] x INNER JOIN [Sheet2$] c ON x.a=c.a
SELECT * FROM [Sheet1$A5:C10]
INSERT INTO [Sheet2$] SELECT * FROM [Sheet1$] WHERE a=1 (bấm nút Insert Update)


Bạn cần đăng nhập để thấy hình ảnh
 
Sửa lần cuối:

NhanSu

SMod
Thành viên BQT
Mình đang dùng trên Excel 365 64 bit nhưng cũng thử cài và chạy được trên Excel 2010 (hình như 2010 chỉ có 32 bit). Bạn thử xem sao. Nếu không ổn thì có thể biên dịch lại để target là 32 bit.
P/S mình kiểm tra lại thì Excel 2010 có cả 64 bit. File cài đặt mình đang để target là 64 bit, bạn nào cần 32 bit thì dùng VS build lại hoặc nếu không quen VS thì mình sẽ up thêm.
 
Sửa lần cuối:

NhanSu

SMod
Thành viên BQT
Ví dụ sử dụng addin: đây là file Excel dữ liệu thương tích mình làm ví dụ. Cột Date là ngày bị thương, cột Injury location là vị trí bị thương trên cơ thể, cột Incident cost là số tiền. Câu hỏi: tính tổng số lượt, tổng số tiền theo từng vị trí của các trường hợp giới tính nam, bị thương sau 07/6/2020, địa chỉ ở các bang Alabama hoặc Texas, chỉ lấy các trường hợp có số lượt > 2?
Đối với những câu hỏi kiểu như trên, làm bằng công thức, filter, pivot sẽ mất nhiều thời gian, dễ sai sót trong khi nếu dùng sql thì chỉ cần câu lệnh đơn giản
Mã:
SELECT count(1) as So_Luot, sum([Incident cost])  as Tong_Tien, [Injury Location]   FROM
(
SELECT * FROM [Sheet1$] WHERE date>dateserial(2020,6,7) and gender='Male'  AND plant in ('Alabama', 'Texas')
)
GROUP BY  [Injury Location]
HAVING count(1)>2
Bạn cần đăng nhập để thấy hình ảnh

Bạn cần đăng nhập để thấy hình ảnh
 

NhanSu

SMod
Thành viên BQT
Câu hỏi 2: cũng file dữ liệu trên, thống kê số lượt, số tiền theo bang, tháng, năm, giới tính. Việc này có thể dùng pivot nhưng với SQL thì chỉ 1 lệnh ngắn gọn:
Mã:
SELECT Plant, Month, Year, Gender, count(1), sum([Incident cost]) FROM [Sheet1$] 
GROUP BY Plant, Month, Year, Gender
Bạn cần đăng nhập để thấy hình ảnh
 

NhanSu

SMod
Thành viên BQT
Ví dụ tiếp phức tạp hơn: danh sách bệnh nhân đi khám bệnh, cột ID là duy nhất, cột ma_bn thể hiện mã thẻ bảo hiểm (mỗi bệnh nhân 1 mã, hai BN khác nhau có mã khác nhau), cột ngay_vao, ngay_ra là ngày vào ra viện dạng yyyymmddhhmm. Câu hỏi: tìm các bệnh nhân trùng thời gian khám chữa bệnh (có thể trùng một phần hoặc toàn bộ)?
Mã:
SELECT * FROM [Sheet1$] a
INNER JOIN [Sheet1$] b
ON a.ID < b.ID and a.ma_bn = b.ma_bn
WHERE IIF(a.ngay_vao < b.ngay_vao, b.ngay_vao, a.ngay_vao) <= IIF(a.ngay_ra > b.ngay_ra, b.ngay_ra, a.ngay_ra)
Giải thích kết quả sau truy vấn, trường hợp dòng trên cùng: 2 lượt khám khác nhau (có ID khác nhau) của cùng 1 BN (mã BN 52256)
Lượt 1: từ 14h 36ph ngày 25/7/2019 đến 8h00 ngày 06/8/2019
Lượt 2: từ 7h41 ngày 06/8/2019 đến 09h57 ngày 06/8/2019
2 lượt khám trên trùng một phần (từ 7h41 đến 8h00 ngày 06/8)
Bạn cần đăng nhập để thấy hình ảnh
 
Sửa lần cuối:
@NhanSu
Về addIn này mình thấy cũng rất tiện ích, truy vấn lọc dữ liệu bằng ngôn ngữ SQL , do đó cần phải hiểu được câu lệnh truy vấn bằng SQL.
Mình có chút thắc mắc là AddIn này bạn có phát triển lên nữa làm sao các câu lệnh SQL tự sinh ra thay vì phải viết tay?
 

NhanSu

SMod
Thành viên BQT
@Nguyen Kha Nam mình không có ý định đó. Thứ nhất, mình làm addin để tự phục vụ là chính và chỉ dùng khi dữ liệu nhỏ. Với dữ liệu lớn hơn (vài triệu record), mình thường dùng SQLite. Mình không làm với big data. Truy vấn sql thông thường dễ hơn Excel. Thứ 2, thiết kế tool dạng SQL builder rất khó.
 
Có lẽ chủ nhân file không muốn chia sẻ rộng rãi, vì vậy cũng không nên xin lại làm gì cho mất công.
 
Top