Using Python To Access Libreoffice Calc Using Uno
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 macr
Solution 1:
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.
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 GVWCimport mysql.connector
import pandas as pd
from gui import validateLogin
defconn_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 queryreturn df, pd #returns dataprint("Database connection resolved.")
# connection to libreofficeimport uno
import os
import time
deflo_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 infofrom tkinter import *
from functools import partial
defvalidateLogin(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 mysqlimport os
from workingConn3 import conn_mysql
from lo_conn_3 import lo_conn
defmain():
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 inenumerate(df.columns.values):
worksheet.write(5, col_num, value, header_format)
colwidths = {}
# Store the defaults.for col inrange(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/
Add dataframe and button to same sheet with XlsxWriter
https://pynative.com/python-mysql-database-connection/
Thanks to everyone who helped.
Solution 2:
I had the same problem and I solved it like this:
- install libreoffice in Ubuntu 20:
sudo apt update
sudo apt install libreoffice --no-install-recommends
- check if uno is accessible (in my case this is all I've done):
python3
Python 3.8.5 (default, Jul 28 2020, 12:59:40)
[GCC 9.3.0] on linux
Type "help", "copyright", "credits" or "license"for more information.
>>> import uno
>>> print(uno.__file__)
/usr/lib/python3/dist-packages/uno.py
>>> exit()
- start libreoffice process with opened socket (more info here - page 117):
/usr/lib/libreoffice/program/soffice.bin --headless --invisible --nocrashreport --nodefault --nofirststartwizard --nologo --norestore --accept='socket,host=localhost,port=2002,tcpNoDelay=1;urp;StarOffice.ComponentContext'- open another terminal and enter python3 interactive mode:
import uno
local_ctx = uno.getComponentContext()
smgr_local = local_ctx.ServiceManager
resolver = smgr_local.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", local_ctx)
url = "uno:socket,host=localhost,port=2002,tcpNoDelay=1;urp;StarOffice.ComponentContext"- now try to connect:
uno_ctx = resolver.resolve(url)
- if succeeded,
uno contextis all you need - instantiate Desktop (the root frame for all applications - Calc, Writer, Draw, ...)
uno_smgr = uno_ctx.ServiceManager
desktop = uno_smgr.createInstanceWithContext("com.sun.star.frame.Desktop", uno_ctx )
- create a new Calc document (
private:factory/scalc):
PropertyValue = uno.getClass('com.sun.star.beans.PropertyValue')
inProps = PropertyValue( "Hidden" , 0 , True, 0 ), # this is a tuple
document = desktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, inProps )
- play with the document:
sheets = document.getSheets()
sheet_1 = sheets[0]
cell_1 = sheet_1.getCellByPosition(1,1)
value = cell_1.setString('Hello World!')
- save it and check the results:
# UNO requires absolute paths
import os
path = os.path.abspath('./testfile.xls')
uno_url = uno.systemPathToFileUrl(path)
# Filters used when saving document.# https://github.com/LibreOffice/core/tree/330df37c7e2af0564bcd2de1f171bed4befcc074/filter/source/config/fragments/filters
filter = uno.createUnoStruct('com.sun.star.beans.PropertyValue')
filter.Name = 'FilterName'
filter.Value = 'Calc MS Excel 2007 XML'
filters = (filter,)
# use filters = () to save file as *.ods
document.storeToURL(uno_url, filters)
- don't forget to terminate the process:
desktop.terminate()
- you can find my implementation here: https://github.com/CosminEugenDinu/libreoffice-python-library
- Good Luck !
Post a Comment for "Using Python To Access Libreoffice Calc Using Uno"