使用 Python 使用 Uno 访问 LibreOffice Calc [英] Using Python to access LibreOffice Calc using Uno
问题描述
我正在尝试编写一个脚本来使用 Python 3.7 操作 Ubuntu Mint _LibreOffice Calc 表.出于某种原因，当我尝试导入 Uno 时出现错误.我可以从 Calc 内部运行一个宏调用这个
I'm trying to write a script to manipulate a Ubuntu Mint _LibreOffice Calc sheet using Python 3.7,. For some reason I'm getting an error when I try to import Uno. I can run a macro from inside Calc calling this
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_
but can't directly from the VS_code IDE, interact with the sheet. Is there a path that I need to set to Uno_
使用 LibreOffice 6.4、Python 3.7 和 APSO.我对 LibreOffice 感到非常沮丧，似乎对新手的 Python 支持很少.
Using LibreOffice 6.4, Python 3.7 and APSO. I'm so frustrated with LibreOffice there seems to be very little python support for newbies.
推荐答案
经过大量研究，我找到了如何使用 Python 3.7、PyCharm 和 MySQL 操作 Ubuntu LibreOffice Calc 表的方法.我是这一切的新手，所以请原谅我的编码，我还在学习.我提供了我用来完成这个项目的信息来源的链接.
After a lot of research, I have figured out how to manipulate a Ubuntu LibreOffice Calc sheet using Python 3.7 and PyCharm and MySQL. I'm a Newbie at all of this, so please excuse my coding, I'm still learning. I have supplied links to information sources I used to complete this project.
导致我出现问题的主要障碍是不明白在通过套接字连接之前需要启动 LibreOffice.之后，我能够使用 Uno 和其他模块与 Calc 电子表格进行交互.我提供了所有 Py 脚本的副本.希望这能帮助像我这样刚接触这个环境的人.
The main stumbling block that was causing me problems, was not understanding that LibreOffice needed to be initiated prior to connecting through the socket. After that I was able to use Uno and other modules to interact with the Calc spreadsheet. I have supplied copies of all my Py scripts. Hopefully this will help anyone who like me is new to this environment.
# 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://pjryan126.github.io/jupyter-db-connections/
https://pynative.com/python-mysql-database-connection/
感谢所有帮助过的人.
这篇关于使用 Python 使用 Uno 访问 LibreOffice Calc的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
