How do I write a python macro in libreoffice calc to cope with merged cells when inserting external 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 python macro. I am expecting a list of invoice line data from the listening program and want to insert the lines into the libreoffice spreadsheet which may or may not have merged cells.

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 each line into data items delimited by the sending program, by tab. The data is inserted, starting from the cell in which the cursor is presently positioned. Each subsequent data item is inserted into the next column and for each line of subsequent data increments the row for the next set of inserts. Finding the merged cell "range" was a particularly difficult thing to discover how to do and I have not found this documented elsewhere. Finally each data item is tested to see if it should be inserted as a numeric or text, this is vital if you wish the spreadsheet to perform calculations on the inserted data.

The last line of data is marked with the word "END". This final line of data contains, in this example, an Invoice number ( at position 1) and the specific Cell Name (at position 4) into which it should be put. If there is an error the data is written into the next row down as text so the user can cut and paste the data.

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 fs2InvoiceLinesCalc(*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('invoice\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']
    # merged rows are cumulative
    master_row_merge_adj = 0

    for x in range(0,lines_in_response):
        if s_list[x].startswith("END"):
            break
        row_merge_adj = master_row_merge_adj
        insert_table = s_list[x].split("\t")
        if s_list[x] == "":
            continue
        parts = len(insert_table)
    # merged columns are a simple adjustment for each item within x
        column_merge_adj = 0
        row_merge_done = 0
        for y in range(0,parts):
            it = insert_table[y]
            cell_name = column[first_col + y + column_merge_adj]+str(x +1 +first_row + row_merge_adj)
            cell = sheet.getCellRangeByName(cell_name)
            if cell.getIsMerged():
                cellcursor = sheet.createCursorByRange(cell)
                cellcursor.collapseToMergedArea()
                try:
                    # format AbsoluteName $Sheet1.$A$1:$D$2 for a merged cell of A1:D2
                    a,b,cell_range = cellcursor.AbsoluteName.partition(".")
                    start_cell, end_cell = cell_range.split(":")
                    a, start_col, start_row = start_cell.split("$")
                    a, end_col, end_row = end_cell.split("$")
                    column_merge_adj = column_merge_adj + (int(column.index(end_col)) - int(column.index(start_col)))
                    # merged rows are cumulative over each x
                    # merged row increment should only occur once within each x
                    # or data will not be in the top left of the merged cell
                    if row_merge_done == 0:
                        master_row_merge_adj = row_merge_adj + (int(end_row) - int(start_row))
                        row_merge_done = 1
                except:
                    #unable to compute - insert data off to the right so it's available for cut and paste
                    column_merge_adj = 10
            try:
                float(it)
                ins_numeric = True
            except:
                ins_numeric = False
            if ins_numeric:
                cell.Value = it
            else:
                cell.String = it
    if s_list[x].startswith("END"):
        insert_table = s_list[x].split("\t")
        try:
            invno = int(insert_table[1])
            cell_name = insert_table[4]
        except:
            pass
    try:
        cell = sheet.getCellRangeByName(cell_name)
        cell.Value = invno
    except:
        #The cell_name passed for Invoice number is incorrect, attempt to insert it in the next row, first selected column
        passed_cell_name = cell_name
        cell_name = column[first_col]+str(x +2 +first_row + row_merge_adj)
        cell = sheet.getCellRangeByName(cell_name)
        insert_text = "Invoice Number "+str(invno)+" Pos "+passed_cell_name+" Incorrect"
        cell.String = insert_text

    sock.close()
    return None