Skip to main content
 首页 » 编程设计

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

2025年05月04日13EasonJim

前提:我在 libreoffice calc 中工作,需要通过 python 宏向我知道正在监听 TCP 端口的另一个程序发送指令。 我期待来自监听程序的发票行数据列表,并希望将这些行插入到 libreoffice 电子表格中,它可能有也可能没有合并单元格。

请您参考如下方法:

通过搜索 stackoverflow 获得了多次帮助,我想我会发布一个解决问题的解决方案,这个问题需要付出很多努力才能解决。 该代码将数据分成几行,每行分为由发送程序按制表符分隔的数据项。从光标当前所在的单元格开始插入数据。每个后续数据项都被插入到下一列中,并且对于后续数据的每一行,为下一组插入增加该行。 找到合并的单元格“范围”是一件特别难发现如何做的事情,我没有在其他地方找到这个记录。 最后,测试每个数据项以查看它是否应该作为数字或文本插入,如果您希望电子表格对插入的数据执行计算,这是至关重要的。

最后一行数据标有“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