在插入外部数据时，如何在libreoffice calc中编写python宏以应对合并的单元格

通过搜索stackoverflow得到了许多帮助，我认为我将发布解决该问题的解决方案，该解决方案花费了很多精力来解决。该代码将数据分为几行，每一行分为由发送程序按Tab分隔的数据项。从光标当前所在的单元格开始插入数据。每个后续数据项都插入到下一列，并且对于后续数据的每一行，该行将用于下一组插入。找到合并单元格的“范围”是发现如何做的特别困难的事情，而我在其他地方也没有发现这一点。最后，测试每个数据项以查看是否应将其作为数字或文本插入，如果您希望电子表格对插入的数据执行计算，这至关重要。

数据的最后一行用单词“ END”标记。在此示例中，最后一行数据包含应将其放入的发票编号（在位置1）和特定的单元名称（在位置4）。如果有错误，数据将以文本形式向下写入下一行，以便用户剪切和粘贴数据。

Configobj是一个从平面文件读取参数的软件包。在此示例中，我使用该文件存储要使用的TCP端口。侦听程序和此代码都从同一配置文件读取端口号。它可能已经被硬编码。

这是一个对我有用的python宏，我相信它将为其他人指明正确的方向

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