Lập Trình Excel Server Và Client - Phương Thức TCP/IP

Kiều Mạnh · 11/2/19

I/ Chuẩn bị Control VB6 để tiến hành lập trình VBA :
Server & Client - Phương Thức TCP/IP

1/ Nếu Bạn nào đã cài đặt VB6 trên máy thì bỏ qua Mục I này
2/ Nếu Bạn nào chưa cài VB6 thì tìm trên Google tải về giải nén ra Tìm trong File ISO theo đường dẫn sau:

VB6.ISO\os\system\MSWINSCK.OCX ( Control )

VB6.ISO\os\system\MSWINSCK.OCA ( Control )

3/ Áp dụng cho Windows_x64

Mã:

Copy 2 File (MSWINSCK.OCX & MSWINSCK.oca ) Vào c:\windows\SysWOW64\

4/ Áp dụng cho Windows_x32

Mã:

Copy 2 File (MSWINSCK.OCX & MSWINSCK.oca ) Vào c:\windows\system32\

5/ Xong đăng ký sử dụng thực hiện: Run cmd Run As: ( cmd Run: Administrator )

Áp dụng cho Windows_x64:

Mã:

regsvr32 c:\windows\SysWOW64\MSWINSCK.OCX

Áp dụng cho Windows_x32:

Mã:

regsvr32 c:\windows\System32\MSWINSCK.OCX

II/ Sử dụng Microsoft Winsock Control 6.0 (SP6) Từ Excel (VBA)

1/ Tools/References/Browse.../c:\windows\SysWOW64\MSWINSCK.OCX/OK
2/ Khai báo sử dụng trên VBA
*/ Sự kiện trên Sheet

Mã:

Private WithEvents tcpClient As MSWinsockLib.Winsock

*/ Tạo kết nối

Mã:

Set tcpClient = New MSWinsockLib.Winsock

3/ Xong thử code xem là ok chưa ta chép code sau và chạy nó
***/ Khai báo kết nối muộn

Mã:

Sub Test_VBA_WinSock()
    With CreateObject("MSWinSock.WinSock")
        MsgBox "IP La :" & Space(1) & .LocalIP & vbCrLf _
            & vbCrLf & "ComputerName La: " & Space(1) & .LocalHostName
    End With
End Sub

***/ Khai báo kết nối Sớm và khi viết tcpClient. (Chấm) Nó sổ ra List của Hàm (Gợi ý khi viết code)

Mã:

Sub Test_VBA_WinSock_2()
    Dim tcpClient As MSWinsockLib.Winsock
    Set tcpClient = New MSWinsockLib.Winsock
    MsgBox tcpClient.LocalIP ''Lay IP LAN
    MsgBox tcpClient.LocalHostName''Lay Ten Computer
End Sub

Nếu chạy code trên thấy cái IP Address LAN và Tên Máy tính của Mình nổi lên là OK .... vậy là xong cái khâu chuẩn bị

III/ Tiến hành khai báo sử dụng và Viết Server & Client

1/ Tạo một File tạm keo là: Server.xlsb
2/ Tạo một File tạm keo là: Client.xlsb
3/ Viết code kết nối Server và Client với nhau thông Qua IP .... chát chít qua lại xem ok chưa
3/ Tạo kết nối từ Client To Server ( Từ Server kết nối Tới Database.accdb)
4/ Từ Client truyền 1 tham số Tạm keo Query sang Server ... xong Server lưu vào Database.accdb
5/ lấy dữ liệu từ Server về ...vvv

IV/ Khi các phần trên làm xong ta chuyển toàn bộ các phần đã làm trên Từ Control VB6 Sang Lập trình bằng Hàm Windows API

1/ Làm lại các bước đã làm viết bằng Hàm Windows API
2/ Vì khi ta viết API nó sẻ xài trên các máy khác nhau mà ko phụ thuộc vào cài Control VB6 kia đã về nghỉ hưu rồi (công nghệ bỏ quên trên 10 năm tuổi)

3/ Sử dụng Hàm API là rất khó ... cực khó luôn .... vì vậy ta làm quen cái dễ trước đã hiểu được phương thức kết nối và truyền tham số xong ... ta bước qua nghiên cứu Hàm API viết Server & Client

V/ Trên Tinh thần Mạnh vừa mò vừa viết rất mong các bạn tham gia xây dựng viết 1 cái Server và Client trên Excel để ứng dụng tốt vào công việc

1/ Bước đầu ta kết nối chat chít Qua IP LAN, Internet thành công
2/ Lấy và ghi dữ liệu Qua IP LAN, Internet thành công
3/ Chuyển toàn bộ code trên viết trên API ....
....................
Định hướng nghiên cứu là vậy .... khó khăn và phát sinh tới đâu ta cùng nhau mò tới đó .... sai ta viết lại cho đúng
Nghiên cứu mà có mất cái chi mà sợ sai hay thất bại ....?????????????!!!!!!!!!!!!
Quan trọng là ta có tinh thần, đam mê và dám nhảy xuống Sông Sài Gòn bơi hay ko thui he $===\.$

Vui lòng chưa trích dẫn bài này vì Mạnh đang viết cái kế hoạch nghiên cứu chưa hoàn thiện ...
còn điều chỉnh bổ sung thêm .... xong mới chính thức bước vào nghiên cứu ...

Mục tiêu sơ bộ là vậy .... Bạn nào có ý kiến cứ viết bài xuống dưới he ....

Rất mong có nhiều thành viên tham gia cùng nhau nghiên cứu Lập trình Excel Server & Client - Phương Thức TCP/IP

Chúc Thành công !

ongke0711 · 16/2/19

Nguyễn Duy Tuân đã viết:
Viết Client Server trên Excel tôi đã làm cách đây 9 năm và rất nhiều người đã biết và ứng dụng nó. Các bạn cố học làm đc là tốt chứ không cÀn chứng minh việc này nữa, nó đã có lâu rồi. Để làm đc những cái như báo cáo động, share range excel đều là những việc rất khó và gian nan, cần nhiều thời gian.

Tôi rất nể các công trình ngâm cứu của BlueSofts để ra các sản phẩm bổ sung cho Excel những cái mà bản thân Excel thuần không hỗ trợ. Nó không hề đơn giản,không thể chỉ học vài ngôn ngữ làm được.
Đối với tôi thì chỉ chọn giải pháp đơn giản hơn chút là khi đã xác định ứng dụng phân phối qua mạng thì cứ dùng ngôn ngữ, nền tảng nào hỗ trợ tốt cho nó thì mình chọn nó để học và lập trình. Tất nhiên tôi cũng chỉ làm những ứng dụng đơn giản trong khả năng tự học được thôi.

Các ứng dụng nhỏ nhỏ đơn giản thì tôi cứ đùng Access làm Front end và SQL server làm Back end thì LAN hay WAN đều chạy tốt. Dễ phát triển, dễ bảo trì. Các báo cáo (Report) Access quá tệ thì export sang Excel, Word template là cũng đáp ứng nhu cầu.

anhtuanle123 · 16/2/19

thuyyeu99 đã viết:
Còn có điểm này mình chưa hình dung ra.
1. Ví dụ File Excel gồm công thức và Value cả ngàn dòng, bao gồm 10 Sheet, như vậy Server làm sao Share được.
2. Hình như mình có thể sử dụng MySQL cho Acces thì phải.

nói chung theo mình thì đồng bộ dữ liệu chỉ thích hợp với các loại chương trình cần cập nhật dữ liệu thường xuyên trong thời gian thực ,

mình ví dụ : ở trong bệnh viện , sảnh chờ khám bệnh , có vài màn hình máy tính , chạy chương trình excel , sẽ thể hiện danh sách các bệnh nhân đến lượt vào khám , y tá chỉ cần ngồi ở 1 máy tính nào đó nhập tên bệnh nhân lên lưới Excel thì các màn hình đều được đồng bộ dữ liệu , nó cao lắm thì cần khoảng 20-30 dòng dữ liệu là cùng !

còn công việc xử lý vài ngàn dòng Excel của bạn đâu nhất thiết phải chạy trong thời gian thực , nó thuộc dạng công việc tính toán -> trả kết quả -> xác nhận -> chuyển tiếp , tức là cứ tuần tự mà làm , bạn cứ tính Excel của bạn , rồi trả file kết quả cho người kế tiếp tính toán thôi ,
vấn đề của bạn có chăng chỉ là việc quản lý số liệu , vì càng ngày nó càng phình ra , việc chuẩn hóa dữ liệu , nó là nhánh gọi là phân tích cơ sở dữ liệu , rồi nhờ IT làm thành 1 ứng dụng , hoặc bạn có thể tự viết chương trình theo mô hình dữ liệu tập trung bằng access hoặc Excel kết nối và nhập liệu vào access chẳng hạn , Excel đóng vai trò là input dữ liệu , tức là chỉ là chương trình quản lý bình thường thôi

thêm nữa là trong trường hợp có đồng bộ vài ngàn dòng trong thời gian thực đi nữa (tôi cũng chưa hình dung ra công việc gì ) thì lại không nên dùng access , acces lúc đó trở thành tối kiến , vì khi cập nhật dữ liệu vào database , ta chỉ có thể cập nhật từng dòng 1 , ví dụ vòng for từ 1 -> 2000 để update từng dòng 1 sẽ làm máy ì đi khoảng nửa phút ( từng thử máy tui) , vì máy tính phải chạy qua chạy lại giữa client và sever khoảng 2000 lần , lúc này ta lên làm file text , ghi dữ liệu bảng tính vào text , rồi copy lên ổ đĩa dùng chung , các máy client khác copy file text về và đọc vào lưới Excel , tốc độ cải thiện đáng kể !

thuyyeu99 · 16/2/19

anhtuanle123 đã viết:
thêm nữa là trong trường hợp có đồng bộ vài ngàn dòng trong thời gian thực đi nữa (tôi cũng chưa hình dung ra công việc gì ) thì lại không nên dùng access , acces lúc đó trở thành tối kiến , vì khi cập nhật dữ liệu vào database , ta chỉ có thể cập nhật từng dòng 1 , ví dụ vòng for từ 1 -> 2000 để update từng dòng 1 sẽ làm máy ì đi khoảng nửa phút ( từng thử máy tui) , vì máy tính phải chạy qua chạy lại giữa client và sever khoảng 2000 lần , lúc này ta lên làm file text , ghi dữ liệu bảng tính vào text , rồi copy lên ổ đĩa dùng chung , các máy client khác copy file text về và đọc vào lưới Excel , tốc độ cải thiện đáng kể !

Cái này khong nhất thiết phải chép nguyên filetext chỉ can chuyen xml hay json sentext la ok roi dâu cần gởi nguyên file text làm gi

ongke0711 · 16/2/19

anhtuanle123 đã viết:
thêm nữa là trong trường hợp có đồng bộ vài ngàn dòng trong thời gian thực đi nữa (tôi cũng chưa hình dung ra công việc gì ) thì lại không nên dùng access , acces lúc đó trở thành tối kiến , vì khi cập nhật dữ liệu vào database , ta chỉ có thể cập nhật từng dòng 1 , ví dụ vòng for từ 1 -> 2000 để update từng dòng 1 sẽ làm máy ì đi khoảng nửa phút ( từng thử máy tui) , vì máy tính phải chạy qua chạy lại giữa client và sever khoảng 2000 lần , lúc này ta lên làm file text , ghi dữ liệu bảng tính vào text , rồi copy lên ổ đĩa dùng chung , các máy client khác copy file text về và đọc vào lưới Excel , tốc độ cải thiện đáng kể !

Tôi đã test lấy 80.000 dòng (10 cột) từ Excel vào table Access trong 7s với ACE.OLEDB 12.0.
Nếu update hàng loạt thì nên dùng UpdateBatch của ADO sẽ cải thiện tốc độ đáng kể thay vì dùng vòng lặp và Insert Into...

[url=https://flic.kr/p/24aQXXB]

[/url]

Kiều Mạnh · 17/2/19

@thuyyeu99
Nghĩ Mạnh nói chơi sao .... Xem caro nè ... Hình dưới trên GPE có đầy Mạnh Copy Từ GPE đó ??!!
Server và Client Share Range chơi Caro đó
Linh sau ... chịu khó nghiên cứu đi ha sẻ có 1 Cái ExTools đang hình thành rồi đó ???!!!

https://www.giaiphapexcel.com/diendan/threads/có-thể-sử-dụng-1-file-excel-cho-nhiều-người-cùng-update-không.9642/#post-373593

Hình sau Mạnh lấy từ Link Trên Của Add-ins Atools

thuyyeu99 · 17/2/19

kieu manh đã viết:
@thuyyeu99
Nghĩ Mạnh nói chơi sao .... Xem caro nè ... Hình đó trên GPE có đầy Mạnh Copy Từ GPE đó ??!!
Server và Client Share Range chơi Caro đó
Linh sau ... chịu khó nghiên cứu đi ha sẻ có 1 Cái ExTools đang hình thành rồi đó ???!!!
https://www.giaiphapexcel.com/diendan/threads/có-thể-sử-dụng-1-file-excel-cho-nhiều-người-cùng-update-không.9642/#post-373593

Hình sau Mạnh lấy từ Link Trên Của Add-ins Atools

View attachment 212307
View attachment 212308

dạo này anh thay em không da động tới client sever do em không có nhu cầu phát triển trên excel. Còn trên access biết rồi mà cũng không thích sài nên không phát triển tiếp. Em chỉ quan tâm nhập liệu và bao cao thoi hehe

thuyyeu99 · 20/11/19

Lại lòng vòng trở lại điểm ban đầu, gánh thêm 1 mớ hỗn độn _)()(-

Kiều Mạnh · 20/11/19

thuyyeu99 đã viết:
Lại lòng vòng trở lại điểm ban đầu, gánh thêm 1 mớ hỗn độn

sư phụ lại thích mò lại hả ... ta lại chơi thui
có điều sư phụ có chi share cho mạnh học với thì mạnh sẻ làm ngược lại như vậy he
đó thấy ai đó keo là tương tác cùng tiến bộ đó he

====== tám tí
mởi thử kiểu gõ VNI và TL trên Windows 10 thấy hay ghê ...vậy là UniKeyNT vào bảo tàng quá
chuyển kiểu gõ bấm tổ hợp phím Alt + Shift là xong

thuyyeu99 · 20/11/19

Hihihi Chưa tới lúc em Share, tới lúc thì tự khắc em sẽ se ++--//*

mờ mờ ảo nó mới hấp dẫn
Em đang vướng chỗ này chưa hình dung được cái chỗ connect open File Exell trên sever từ Client (giải được chỗ này thì khả năng cao lắm ah nha hihihih)

Kiều Mạnh · 20/11/19

thuyyeu99 đã viết:
Hihihi Chưa tới lúc em Share, tới lúc thì tự khắc em sẽ se mờ mờ ảo nó mới hấp dẫn
Em đang vướng chỗ này chưa hình dung được cái chỗ connect open File Exell trên sever từ Client (giải được chỗ này thì khả năng cao lắm ah nha hihihih)

coi kỹ lại bài #6 là hình dung ra thui mà

thuyyeu99 · 20/11/19

Anh text thử xem ví dụ trên Sever có các hàm thì Client mở lên có không
Còn truyền ghi các vùng thì em làm được

Anh nghiên cứu chỗ này giúp em, em đi công chuyện đã chiều họp mặt rồi :cc_chinese:

Kiều Mạnh · 17/12/19

thuyyeu99 đã viết:
Anh text thử xem ví dụ trên Sever có các hàm thì Client mở lên có không
Còn truyền ghi các vùng thì em làm được

Anh nghiên cứu chỗ này giúp em, em đi công chuyện đã chiều họp mặt rồi

Tình hình sao Rồi ???
Mạnh mới mò ra cái Server thấy hay hay ... nó keo kết nối khoãng 200 máy chạy ok
và quan trọng là biết lấy dữ liệu từ Server về rùi
Biết chút xài API và ko lệ thuộc vào OCX của bill nữa .... mò diết ra à

Nếu viết trên Delphi thì mạnh rối tinh hết lên ... Còn VB6 làm tạm ok ... vì vậy đang làm quen phương thức, cách thức của nó trên VB6 cho ngon lành xong sau đó tới Delphi sau

Có code mẫu API chạy trên Access đó ... mà Access hay Excel thì nó cũng thế
Không biết có ai có ý kiếm gì không nhỉ ... ta tính tiếp

thuyyeu99 · 17/12/19

U như kỹ

Kiều Mạnh · 29/9/20

To @ongke0711 + n nick có ai còn thích nghiên Cứu Server + Client lấy dữ liệu theo Phương thức TCP/IP không nhỉ ????
Mạnh Úp Code API x32 trên Access lên cho mà tham khảo .... chỉ là chát qua chát lại thôi .... còn ứng dụng sao thì tùy vào khả năng của từng Bạn

thuyyeu99 · 30/9/20

Tôi Vote cho 1 vé .

ongke0711 · 30/9/20

Kiều Mạnh đã viết:
To @ongke0711 + n nick có ai còn thích nghiên Cứu Server + Client lấy dữ liệu theo Phương thức TCP/IP không nhỉ ????
Mạnh Úp Code API x32 trên Access lên cho mà tham khảo .... chỉ là chát qua chát lại thôi .... còn ứng dụng sao thì tùy vào khả năng của từng Bạn

Thực ra thì mấy ứng dụng nho nhỏ của tôi làm chủ yếu về CSDL nên không có nhu cầu dùng phương thức này và cũng không ngâm cứu nó nữa. Cứ Access làm Front end, SQL Server làm Back end thì cứ kết nối qua internet chạy phà phà thôi, khỏi mất công code kiết gửi nhận gói tin. Còn các tính năng trò chuyện (Chat), gửi tin nhắn (instant message) giữa ứng dụng với nhau thì tôi thấy không cần thiết, làm cho vui thôi, chứ xài ứng dụng bên thứ 3 (zalo, fb messenger, viber...) tiện hơn nhiều.
Nếu bạn Kiều Mạnh thấy chia sẻ công sức code của mình được thì post lên, chia sẻ cho mọi người tham khảo học hỏi khi cần dùng đến.

Kiều Mạnh · 30/9/20

ongke0711 đã viết:
Thực ra thì mấy ứng dụng nho nhỏ của tôi làm chủ yếu về CSDL nên không có nhu cầu dùng phương thức này và cũng không ngâm cứu nó nữa. Cứ Access làm Front end, SQL Server làm Back end thì cứ kết nối qua internet chạy phà phà thôi, khỏi mất công code kiết gửi nhận gói tin. Còn các tính năng trò chuyện (Chat), gửi tin nhắn (instant message) giữa ứng dụng với nhau thì tôi thấy không cần thiết, làm cho vui thôi, chứ xài ứng dụng bên thứ 3 (zalo, fb messenger, viber...) tiện hơn nhiều.
Nếu bạn Kiều Mạnh thấy chia sẻ công sức code của mình được thì post lên, chia sẻ cho mọi người tham khảo học hỏi khi cần dùng đến.

vậy thôi nhé .... nó cũng ko thật sự cần thiết nữa rồi .... Có lẻ xóa Files đó trên máy của Mạnh đi cho nó Gọn thôi

Rất lấy làm tiếc ko ai nhắc lại bài này nữa he
Xin cảm Ơn

thuyyeu99 · 30/9/20

vậy thì làm nhanh và lẹ ngay và liền đi hihi @>><M?

Kiều Mạnh · 20/10/20

1/ Vậy là sau gần 2 năm tính từ ngày mở thớt này .... Việc lấy dữ liệu quan Internet Theo phương thức TCPIP gửi từng gói tin 1 thì đã biết từ lâu ==> Nó quá chậm chạp và rất khó ứng dụng vào thực tế và thân thiện với người sử dụng nó mà ko biết code trong đó viết gì khi xài nó

2/ Còn 1 cách là gửi 1 Array từ Server qua Client ..... cách này có nhiều hạn chế + khó khăn cho người sử dụng và lập trình với nó vv...

3/ Tới ngày hôm qua được sự chỉ dẫn của Google đã mò ra Phương Thức kết nối ADODB từ Client Tới Server ... Thì mọi vấn đề trở nên đơn giản như ta viết trên VBA vậy

4/ vậy là cái SQL phương thức TCPIP nằm chắc trong tầm tay rồi đó ... khi xong cái này nữa thì mọi cái sẻ rất Ok

Hiện tại đang là code trên VB6 Khi nào viết trên Delphi thành công sẻ úp tặng cho Ai đó cần mà sử dụng không giới hạn

Sử dụng rất đơn giản như VBA vậy
cách 1

Mã:

Public Sub GetDatabase_VB6()
    Dim Rst As ADODB.Recordset
    Dim Xnet As New Network
    Dim SQL As String
    Rem SQL = "select * from NhapXuatTon"
    Rem SQL = "select * from DataBaseNhap"
    SQL = "select * from DataBaseXuat"
    Rem SQL = Range("A8").Value
    Set Rst = Xnet.connect("192.168.1.9", 8181, SQL)
    Range("A1:M1000").ClearComments
    Range("A1").CopyFromRecordset Rst
    Rst.Close: Set Rst = Nothing
End Sub

cách 2

Mã:

Public Sub GetDatabase2_VB6()
    Dim Rst As ADODB.Recordset
    Dim Xnet As New Network
    Dim SQL As String, i As Long
    Rem SQL = "select * from NhapXuatTon"
    SQL = "select * from DataBaseNhap"
    Rem SQL = Range("A8").Value
    Set Rst = Xnet.connect("192.168.1.9", 8181, SQL)
    Range("A1:M1000").ClearComments
    For i = 0 To Rst.Fields.Count - 1
        Cells(1, 1 + i).Value = Rst.Fields(i).Name
    Next i
    Range("A2").CopyFromRecordset Rst
    Rst.Close: Set Rst = Nothing
End Sub

cách 3
có thể viết hàm trên Cells truyền tham số IP,Port,SQL vào cũng ok ... vì nó như viết trên VBA vậy
VD:
IP = 192.168.1.9
Port = 8181
SQL = "select * from NhapXuatTon"

Mã:

[A1] = Hàm (IP, Port, SQL)

Trả kết quả về là 1 hàm mảng ko xóa được vài Cells trong vùng dữ liệu của Hàm hay kiểu Office 365 cũng được hết

ongke0711 · 20/10/20

Kiều Mạnh đã viết:
3/ Tới ngày hôm qua được sự chỉ dẫn của Google đã mò ra Phương Thức kết nối ADODB từ Client Tới Server ... Thì mọi vấn đề trở nên đơn giản như ta viết trên VBA vậy
4/ vậy là cái SQL phương thức TCPIP nằm chắc trong tầm tay rồi đó ... khi xong cái này nữa thì mọi cái sẻ rất Ok

Chúc mừng bạn.
Quan trọng là bạn viết ra được cái Class Xnet như bác Tuân chưa? Và tôi nghĩ là đây chỉ là code để Client kết nối thôi, còn phải code phía Server nữa.

Lập Trình Excel Server Và Client - Phương Thức TCP/IP

I don't program, I beat code into submission!!!

Thành viên gắn bó

Thành viên thường trực

Trùm Nhiều Chuyện

Thành viên gắn bó

I don't program, I beat code into submission!!!

Trùm Nhiều Chuyện

Trùm Nhiều Chuyện

I don't program, I beat code into submission!!!

Trùm Nhiều Chuyện

I don't program, I beat code into submission!!!

Trùm Nhiều Chuyện

I don't program, I beat code into submission!!!

Trùm Nhiều Chuyện

I don't program, I beat code into submission!!!

Trùm Nhiều Chuyện

Thành viên gắn bó

I don't program, I beat code into submission!!!

Trùm Nhiều Chuyện

I don't program, I beat code into submission!!!

Thành viên gắn bó

Facebook

Group