I'm looking to implement an Excel RTD Server that sends a request to a server and retrieves the information. As an example, the idea is the following:
The user would type something like =GetDuration("US912828D804"), where the parameter is the ISIN code and would recieve the duration of the bond. Behind, excel would make a request to a python server -running in a different machine-where all the calculation is done.
So far, i've tried using Excel DNA to set up the excel RTD server following their example:
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Threading;
using System.Net.Sockets;
using System.Text;
using ExcelDna.Integration.Rtd;
namespace RTDExcel
{
[ComVisible(true)] // Required since the default template puts [assembly:ComVisible(false)] in the AssemblyInfo.cs
[ProgId(RtdExcelServer_Test.ServerProgId)] // If ProgId is not specified, change the XlCall.RTD call in the wrapper to use namespace + type name (the default ProgId)
public class RtdExcelServer_Test : ExcelRtdServer
{
public const string ServerProgId = "Test_Server_1";
List<Topic> _topics = new List<Topic>();
ServerClient serverClient = new ServerClient();
Timer timer;
int i = 0;
public void TimeServer()
{
timer = new Timer(Callback);
}
protected override bool ServerStart()
{
serverClient.ConnectToServer();
TimeServer();
return true;
}
protected override void ServerTerminate()
{
serverClient.CloseConnectionToServer();
timer.Dispose();
timer = null;
}
protected override object ConnectData(Topic topic, IList<string> topicInfo, ref bool newValues)
{
_topics.Add(topic);
timer.Change(10000, 10000);
return serverClient.AskServer("Hello");
}
protected override void DisconnectData(Topic topic)
{
_topics.Remove(topic);
timer.Change(-1, -1);
}
private void Callback(object o)
{
foreach (Topic topic in _topics)
{
i += 1;
topic.UpdateValue(serverClient.AskServer("Hello"));
}
}
}
}
public class ServerClient
{
TcpClient clientSocket = new TcpClient();
public void ConnectToServer()
{
clientSocket.Connect("127.0.0.1", 8080);
}
public string AskServer(string request)
{
NetworkStream serverStream = clientSocket.GetStream();
byte[] outStream = Encoding.ASCII.GetBytes(request);
serverStream.Write(outStream, 0, outStream.Length);
serverStream.Flush();
byte[] inStream = new byte[10025];
serverStream.Read(inStream, 0, 75);
string response = Encoding.ASCII.GetString(inStream);
return response;
}
public void CloseConnectionToServer()
{
clientSocket.Close();
}
}
Phython Server
import socketserver as ss
import datetime
import time
import pickle
class TCPHandler(ss.BaseRequestHandler):
def handle(self):
# self.request is the TCP socket connected to the client
self.data = self.request.recv(1024).decode('UTF-8')
print(self.data)
tmp = str(datetime.datetime.now())
print('Sending: ' + tmp)
print('Byte Size: ' + str(sys.getsizeof(tmp)))
self.request.sendall(tmp.encode())
if __name__ == "__main__":
HOST, PORT = 'localhost', 8080
# Create the server, binding to localhost on port 9999
server = ss.TCPServer((HOST, PORT), TCPHandler)
# Activate the server; this will keep running until you
print('Server is running...')
server.serve_forever()
# interrupt the program with Ctrl-C
The main idea in this example is to test the system. The function only ask the server current time, which is calculated at each request. Unfortunatelly, excel crashes every time the timer callbacks. Is this the right approach?
UPDATE:
Seemed that the problem was in the python server, a loop was needed in order to maintain the client updated:
class TCPHandler(ss.BaseRequestHandler):
def handle(self):
closed = False
while not closed:
try:
self.data = self.request.recv(1024).decode('UTF-8')
print(self.data[0])
if self.data[0] == 'Time':
tmp = str(datetime.datetime.now())
print('Sending: ' + tmp)
print('Byte Size: ' + str(sys.getsizeof(tmp)))
self.request.send(tmp.encode())
if self.data[0] == 'Exit':
closed = True
else:
self.request.send(str('No request.').encode())
except (KeyboardInterrupt, SystemExit):
raise
Now the issue i have is that the server is not recieving the complete messege from the client: instead of recieving "Time" or "Exit" i get only a "T" o "E".
