python-3.x - 使用 Python 使用 Uno 访问 LibreOffice Calc

Question

我正在尝试编写一个脚本来使用 Python 3.7 操作 Ubuntu Mint _LibreOffice Calc 表。出于某种原因，当我尝试导入 Uno 时出现错误。我可以从 Calc 内部运行一个宏调用这个

    desktop = XSCRIPTCONTEXT.getDesktop()
    model = desktop.getCurrentComponent()
    active_sheet = model.CurrentController.ActiveSheet
    write 'Hello World' in A1
    active_sheet.getCellRangeByName("A1").String = "Hello World!

”

但不能直接从 VS_code IDE 与工作表进行交互。是否有我需要设置为 Uno_ 的路径

使用 LibreOffice 6.4、Python 3.7 和 APSO。我对 LibreOffice 感到非常沮丧，似乎对新手的 Python 支持很少。

Answer 1

经过大量研究，我找到了如何使用 Python 3.7、PyCharm 和 MySQL 操作 Ubuntu LibreOffice Calc 表的方法。我是这一切的新手，所以请原谅我的编码，我仍在学习。我提供了我用来完成这个项目的信息源的链接。
导致我出现问题的主要障碍是不明白在通过套接字连接之前需要启动 LibreOffice。之后，我能够使用 Uno 和其他模块与 Calc 电子表格进行交互。我提供了所有 Py 脚本的副本。希望这能帮助像我这样刚接触这个环境的人。

# opens connection to mysql database GVWC
import mysql.connector
import pandas as pd
from gui import validateLogin

def conn_mysql():
    validateLogin #opens input box for username and password
    us, pw=validateLogin() #returns login info
    cnx = mysql.connector.connect(user=us, password=pw ,host='localhost', database= 'GVWC') # connects to DB
    pd.set_option('display.max_columns', None)
    df = pd.read_sql("SELECT * FROM GVWC.2020", cnx) #runs query
    return df, pd #returns data
print("Database connection resolved.")

# connection to libreoffice

import uno
import os
import time

def lo_conn():
    #Open socket to LibraOffice with delay to allow program to wait for connection
    os.popen('/usr/lib/libreoffice/program/soffice --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"')
    time.sleep(3)  # Sleep for 3 seconds

    #=======================================================================================================================================
    # get the uno component context from the PyUNO runtime
    localContext = uno.getComponentContext()

    # create the UnoUrlResolver
    resolver = localContext.ServiceManager.createInstanceWithContext(
        "com.sun.star.bridge.UnoUrlResolver", localContext)

    # connect to the running office
    ctx = resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")
    smgr = ctx.ServiceManager

# create input box for login info
from tkinter import *
from functools import partial

def validateLogin(username, password):
    #print("username entered :", username.get())
    #print("password entered :", password.get())
    global pw
    pw = password.get()
    global us
    us = username.get()
    return us, pw

#window
tkWindow = Tk()
tkWindow.geometry("500x100+500+300")
tkWindow.title('Tkinter Login Form - pythonexamples.org')

#username label and text entry box
usernameLabel = Label(tkWindow, text="User Name").grid(row=0, column=0)
username = StringVar()
usernameEntry = Entry(tkWindow, textvariable=username).grid(row=0, column=1)

#password label and password entry box
passwordLabel = Label(tkWindow,text="Password").grid(row=1, column=0)
password = StringVar()
passwordEntry = Entry(tkWindow, textvariable=password, show='*').grid(row=1, column=1)

validateLogin = partial(validateLogin, username, password)

#login button
loginButton = Button(tkWindow, text="Login", command=validateLogin).grid(row=4, column=0)

tkWindow.mainloop()


#main program to create spreedsheet and fill with data from mysql

import os
from workingConn3 import conn_mysql
from lo_conn_3 import lo_conn

def main():
    conn_mysql # call mysql def connection
    df,pd=conn_mysql() # pass table data
    lo_conn #connect to Libreoffice

    # open calc and fill spreedsheet
    #=====================================================================================================================================
    writer = pd.ExcelWriter('Test_DF.xlsx',
                                engine='xlsxwriter',
                                datetime_format='mm/dd/yyyy')

    df.to_excel(writer, index=False, sheet_name='TEST',
                   startrow=5, header = 4)

    #Get the xlsxwriter objects from the dataframe writer object.
    workbook  = writer.book
    worksheet = writer.sheets['TEST']



    #Add a header format.
    header_format = workbook.add_format({
        'font_color': 'white',
        'text_wrap': True,
        'font_name': 'Calibri',
        'font_size': 11,
        'fg_color': '#44546a'})

    # Write the column headers with the defined format.
    for col_num, value in enumerate(df.columns.values):
        worksheet.write(5, col_num, value, header_format)

    colwidths = {}

    # Store the defaults.
    for col in range(14):
        colwidths[col] = 15



    # Then set the column widths.
    for col_num, width in colwidths.items():
        worksheet.set_column(col_num, col_num, width)


    #Center text of column
    cell_format = workbook.add_format()
    cell_format.set_align('center')
    worksheet.set_column('E1:E100',15,cell_format)
    worksheet.set_column('F1:F100',15,cell_format)
    worksheet.set_column('M1:M100',15,cell_format)

    #  Freeze pane on the header row.
    #
    worksheet.freeze_panes(6, 0)
    worksheet.autofilter('A6:O6')

    #Button text
    worksheet.write('A3', 'Press the button to say hello.')

    # Add a button tied to a macro in the VBA project.
    worksheet.insert_button('A1', {'macro': 'start',
                                   'caption': 'Press Me',
                                   'width': 80,
                                   'height': 30})

    # Close the Pandas Excel writer and output the Excel file.
    writer.save()
    writer.close()
    #reopen excel file
    os.popen('/usr/lib/libreoffice/program/soffice Test_DF.xlsx  --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"')


if __name__== "__main__" :
    main()

https://medium.com/analytics-vidhya/macro-programming-in-openoffice-libreoffice-with-using-python-en-a37465e9bfa5
https://medium.com/analytics-vidhya/starting-libreoffice-with-python-macro-programming-in-openoffice-libreoffice-with-using-10310f9e69f1
https://pjryan126.github.io/jupyter-db-connections/
http://christopher5106.github.io/office/2015/12/06/openoffice-libreoffice-automate-your-office-tasks-with-python-macros.html
https://help.libreoffice.org/6.4/en-US/text/shared/guide/start_parameters.html?&DbPAR=WRITER&System=UNIX
https://www.saltycrane.com/blog/2010/02/using-python-write-excel-openoffice-calc-spreadsheet-ubuntu-linux/
Add dataframe and button to same sheet with XlsxWriter
https://pynative.com/python-mysql-database-connection/
感谢所有帮助过的人。