How do I write a python macro in libreoffice calc to send and receive data

Question

The premise: I am working in libreoffice calc and need to send an instruction to another program that I know to be listening on a TCP port, via a macro. I am expecting a reply from the listening program and want to insert the reply data into the libreoffice spreadsheet.

Answer 1

Having been helped many times over by searching stackoverflow, I thought that I would post a solution to a problem which took much effort to resolve. The code splits the data into lines and inserts into the cell in which the cursor is presently positioned and for each line of subsequent data increments the row for the next insert.

Configobj is a package that reads parameters from a flat file. In this example, I am using that file to store the TCP port to be used. Both the listening program and this code are reading the port number from the same configuration file. It could have been hard coded.

Here is a python macro that works for me, I trust that it will point others in the right direction.

def fs2ClientdataCalc(*args):

    desktop = XSCRIPTCONTEXT.getDesktop()
    model = desktop.getCurrentComponent()
    try:
        sheets = model.getSheets()
    except AttributeError:
        raise Exception("This script is for Calc Spreadsheets only")
    #sheet = sheets.getByName('Sheet1')
    sheet = model.CurrentController.getActiveSheet()
    oSelection = model.getCurrentSelection()
    oArea = oSelection.getRangeAddress()
    first_row = oArea.StartRow
    last_row = oArea.EndRow
    first_col = oArea.StartColumn
    last_col = oArea.EndColumn
    #get the string from Footswitch2 via a TCP port
    import os, socket, time
    from configobj import ConfigObj
    configuration_dir = os.environ["HOME"]
    config_filename = configuration_dir + "/fs2.cfg"
    if  os.access(config_filename, os.R_OK):
        pass
    else:
        return None
    cfg = ConfigObj(config_filename)
    #define values to use from the configuration file
    tcp_port = int(cfg["control"]["TCP_PORT"])
    sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    sock.settimeout(0.5)
    try:
        sock.connect(("localhost", tcp_port))
    except:
        return None
    sock.settimeout(10)
    try:
        sock.send(bytes('client\n', 'UTF-8'))
    except:
        return None
    try:
        time.sleep(1.0)
        s_list = sock.recv(4096).decode('UTF-8')
        s_list = s_list.split("\n")
    except:
        return None
    lines_in_response = len(s_list)
    if lines_in_response is None:
        return None
    column =['A','B','C','D','E','F','G','H','I','J','K','L','M',\
             'N','O','P','Q','R','S','T','U','V','W','X','Y','Z']
    for x in range(0,lines_in_response):
        insert_table = s_list[x].split("\n")
        parts = len(insert_table)
        for y in range(0,parts):
            it = insert_table[y]
            cell_name = column[first_col + y]+str(x +1 +first_row)
            cell = sheet.getCellRangeByName(cell_name)
            cell.String = it

    sock.close()
    return None