VBA和C＃之间的远程集成（使用JSON） [英] Remote integration between VBA and C# (using JSON)
问题描述
我试图找到一个简单的方法来发送一个字符串（在我的例子中是JSON）从VBA数据提供者到C＃服务器。
我的服务器已经在服务器和客户端之间进行了高级别的通信。在我的情况下，我使用CORBA IIOP.NET，但我可以轻松地将其更改为WCF。
My Server already has a high level communication between server and client. In my case I use CORBA IIOP.NET, but I can easily change it to WCF.
我的问题是：如何发送一个字符串到我的C＃服务器？
从我的C＃服务器我得到代码从代码项目（未测试）
From my C# server I got the code from Code Project (not tested)
public void Server()
{
//start server
TcpListener tcpServerListener = new TcpListener(6666);
tcpServerListener.Start();
//block tcplistener to accept incoming connection
Socket serverSocket = tcpServerListener.AcceptSocket();
//open network stream on accepted socket
NetworkStream serverSockStream = new NetworkStream(serverSocket);
StreamReader serverStreamReader = new StreamReader(serverSockStream);
Console.writeLine(serverStreamReader.ReadeLine());
}
我很难通过VBA发送字符串。有人可以帮我吗
I'm having dificult to send the string via VBA. Can anyone help me with that.
PS：在C＃中解析JSON我正在使用 JSON .NET 和VBA我使用 VB JSON
PS: To parse JSON in C# I'm using JSON.NET and VBA I'm using VB JSON
推荐答案
现在我看到，我们不能要求良好的做法和理念来帮助VBA ，Excel-VBA标签。所以我做了我的研究，做出了我的选择。让我知道有人有更好的理想做这个远程集成。
Now I see that we can't ask for good practice and ideias to help in VBA, Excel-VBA tags. So I did my research and made my choices. Let me know if someone has a better ideia to do this remote integration.
我的示例有三个测试。一个服务器（C＃）和两个可以在下面看到的客户端（C＃和VBA）。
My example has three tests. One server (C#) and two clients (C# and VBA) that can be seen bellow.
我使用商店和公司的理念来为良好的继承。这是我的课程：
I use the ideia of Store and Company just to exemplify JSON for .NET works well with inheritance. Here is my classes:
public class Company {
public string Name { get; set; }
}
public class Store : Company {
public string Country { get; set; }
public Dictionary<String, Double> Salaries { get; set; }
}
我的C＃客户端只需创建两个 code>序列化并使用 TcpClient 发送到服务器。
My C# client just create two Stores serialize and send it to Server using TcpClient.
代码：
static void Main(string[] args) {
Store storeUSA = new Store {
Country = "USA",
Name = "Amazon.com",
Salaries = new Dictionary<string, double>{
{"Jones", 100000},
{"Deborah",200000}
}
};
Store storeBR = new Store {
Country = "Brazil",
Name = "Amazon.com",
Salaries = new Dictionary<string, double>{
{"Ricardo", 500000},
{"Math", 800000}
}
};
TcpClient tcpClient = new TcpClient("192.168.1.63", 6666);
Console.WriteLine("Connected to Server");
using (NetworkStream clientSockStream = tcpClient.GetStream()) {
using (StreamWriter sw = new StreamWriter(clientSockStream)) {
string output = JsonConvert.SerializeObject(storeUSA, Formatting.None);
sw.WriteLine(output);
sw.Flush();
System.Threading.Thread.Sleep(2000);
output = JsonConvert.SerializeObject(storeBR, Formatting.None);
sw.WriteLine(output);
sw.Flush();
Console.WriteLine("Closing");
}
}
Console.WriteLine("Done");
Console.ReadLine();
}
我的VBA客户端略有不同。但是这是一样的想法
My VBA client is a slightly different. But it's the same ideia
代码：
Sub main()
Dim tcp As New TCPClient
Dim ok As Boolean
ok = tcp.Connect("192.168.1.63", 6666)
If Not ok Then
Debug.Print "Error Connecting"
Exit Sub
End If
tcp.Send "{""Country"":""France"",""Salaries"":{""Peyton Manning"":800.0,""Blaine Gabbert"":200000.0},""Name"":""Amazon.net""}"
tcp.CloseConnection
End Sub
最后我的服务器真的很简单。它只是听一个端口，当一个消息来反序列化JSON：
Finally my server that is really simple. It just listen a port and when a message come it deserialize JSON:
static void Main(string[] args){
IPAddress ip = IPAddress.Parse("192.168.1.63");
TcpListener tcp = new TcpListener(ip, 6666);
tcp.Start();
Console.WriteLine("[DEBUG] End point is: " + tcp.LocalEndpoint);
Stopwatch time = new Stopwatch();
time.Start();
while (time.Elapsed < TimeSpan.FromSeconds(10)){
TcpClient client = tcp.AcceptTcpClient();
Console.WriteLine("Receiving message ... ");
using (NetworkStream networkStream = client.GetStream()){
using (StreamReader streamReader = new StreamReader(networkStream)){
while (!streamReader.EndOfStream){
var json = streamReader.ReadLine();
Console.WriteLine("JSON:" + json);
Store store = JsonConvert.DeserializeObject<Store>(json);
Console.WriteLine("Data:");
Console.WriteLine(store.ToString());
}
}
}
client.Close();
Console.WriteLine("... Message received");
}
tcp.Stop();
time.Stop();
Console.WriteLine("Server no longer working");
Console.ReadKey();
}
public class Company{
public string Name { get; set; }
}
public class Store : Company{
public string Country { get; set; }
public Dictionary<String, Double> Salaries { get; set; }
public override string ToString()
{
StringBuilder str = new StringBuilder();
str.Append(String.Format("Name= {0}; Country={1};\n Salaries:\n", Name, Country));
if (Salaries != null)
{
foreach (var salary in Salaries)
{
str.Append(String.Format(" '{0}' = {1}\n", salary.Key, salary.Value));
}
}
return str.ToString();
}
}
请注意，我使用 TcpClient 为VBA。关于我们：
Note that I use TcpClient for VBA. Thanks rory - code bellow:
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Const sckClosed = 0 ' Default. Closed
Const sckOpen = 1 ' Open
Const sckListening = 2 ' Listening
Const sckConnectionPending = 3 ' Connection pending
Const sckResolvingHost = 4 ' Resolving host
Const sckHostResolved = 5 ' Host resolved
Const sckConnecting = 6 ' Connecting
Const sckConnected = 7 ' Connected
Const sckClosing = 8 ' Peer is closing the connection
Const sckError = 9 ' Error
Const TIMEOUT_MAX As Integer = 10
Private host As String
Private port As String
Private socket As winsock
Function Connect(ByVal fhost As String, ByVal fport As Integer) As Boolean
Dim secs As Integer
host = fhost
port = fport
Debug.Print Now & " - Client Started"
Connect = False
'Create WinSock
On Error Resume Next
Set socket = CreateObject("MSWINSOCK.Winsock")
If Err.Number <> 0 Then
MsgBox "You need to add MsWinSock reference:" & vbCrLf & "Tools -> References -> Browse -> %SYSEM%\MSWINSCK.OCX"
Debug.Print Now & " - Winsock Object Error."
Exit Function
End If
On Error GoTo 0
'Connect
socket.RemoteHost = host
socket.RemotePort = port
socket.Connect
'Wait for connected state (or TIMEOUT_MAX)
While socket.State <> sckConnected And secs < TIMEOUT_MAX
DoEvents
secs = secs + 1
Debug.Print Now & " - Waitting for server [" & secs & "/" & TIMEOUT_MAX; "]."
Sleep 1000
Wend
If secs >= TIMEOUT_MAX Then
Debug.Print Now & " - Client Timed Out"
CloseConnection
End If
Connect = True
End Function
Sub Send(ByVal str As String)
If socket Is Nothing Then
Call Err.Raise(3222, "TCPClient::Send", "Socket not Initialized")
End If
If socket.State <> sckConnected Then
Call Err.Raise(3222, "TCPClient::Send", "Not connected")
End If
socket.SendData str
DoEvents
End Sub
Sub CloseConnection()
If socket.State <> sckClosed Then
socket.Close
End If
Set socket = Nothing
Debug.Print Now & " - Client Closed."
End Sub
' WINSOCK DATA ARRIVES
Sub winsock_dataArrival(bytesTotal)
Dim strData
winsock.GetData strData, vbString
RecieveData = strData
WriteData Now & " - Client Recieved: " & RecieveData
winsock.SendData "Test"
Wscript.Sleep 1000
WriteData Now & " - Client Sent Data"
Call ClientClose
End Sub
' WINSOCK ERROR
Sub winsock_Error(Number, Description, SCode, Source, HelpFile, HelpContext, CancelDisplay)
MsgBox "Cient Error: " & Number & vbCrLf & Description
WriteData Now & " - Cient Error: " & Number & ". " & Description
Call ClientClose
End Sub
我希望它可以用于有人。
I hope that it can be usefull for someone.
这篇关于VBA和C＃之间的远程集成（使用JSON）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
