In python, while working on excel documents we have scripts to auto width update column and converting files from xlsx to xlsb like
from win32com.client import Dispatch
xl = Dispatch(“Excel.Application”)
and doing various operation.Like that is there anyway to use libre office to operate on excel files using python.
LibreOffice have support Python, what you need exactly?
Hi From python i want to call libre office to autoadjust width of columns and then to convert xlsx file to xlsb format
Hello,
The analog for the com connection you are doing with excel is connecting with a running soffice, only that obviously the methods in a soffice instance are not the same as the ones in an excel instance.
I personally created python classes to wrap spreadsheet actions, one for excel and one for calc, so that they share the same method names for doing the same tasks, and then I can interchange them (I use excel while in Windows and calc while in Linux).
I use the following to get an instance of libreoffice:
class CalcObject:
def __init__(self):
self.name = 'Calc'
self._start_lo_server()
self.desktop = self._connect_to_lo_server(2002)
# The following 'shared' attribute is intended to provide safe
# objects sharing among the calc related objects. Ex: to copy a
# range from one sheet of the workbook to another one
self.shared = {'range': None}
def _start_lo_server(self):
starter = (['soffice', '--accept=socket,host=localhost,port=2002;urp;',
'--norestore', '--nologo', '--nodefault',
'--headless',
])
self.lo_process = subprocess.Popen(starter)
def _connect_to_lo_server(self, port):
for i in range(6):
try:
desktop = pyoo.Desktop('localhost', port)
return desktop
except:
time.sleep(1)
return 'Unable to connect to LibreOffice server'
Note that I am using the pyoo library to get the desktop component, which is something similar to the com connection you established for excel as xl. I combine this pyoo methods with the raw UNO ones, because sometimes I don’t find what I need in pyoo. For example, to open a workbook I do:
def open_file(self, file_path):
workbook = self.desktop.open_spreadsheet(file_path)
context = self.desktop.remote_context
smgr = context.ServiceManager
pyuno_desktop = smgr.createInstanceWithContext("com.sun.star.frame.Desktop", context)
pyuno_workbook = pyuno_desktop.getCurrentComponent()
return CalcWorkbook(pyuno_workbook, self.shared)
Which is a method within the same class. CalcWorkbook is another class I created to wrap workbook methods, and so on.
I have not done the autosize in calc. To save into another format (workbook method), you have to define the desired format from a predefined list. The saveas xlsb is ‘Calc MS Excel 2007 Binary’ according to filters - OpenGrok cross reference for /core/filter/source/config/fragments/filters/
def save_workbook_as(self, file_path):
url = uno.systemPathToFileUrl(os.path.abspath(file_path))
filters = self._get_filter(file_path)
self._workbook.storeAsURL(url, filters)
def _get_filter(self, file_path):
format_filter = uno.createUnoStruct('com.sun.star.beans.PropertyValue')
format_filter.Name = 'FilterName'
pattern = '.*\.([a-zA-Z]{3,4})$'
searcher = re.search(pattern, file_path)
extension = searcher[1]
if extension == 'xls':
format_filter.Value = 'MS Excel 97'
elif extension == 'xlsx':
format_filter.Value = 'Calc MS Excel 2007 XML'
elif extension == 'xlsb':
format_filter.Value = 'Calc MS Excel 2007 Binary'
else:
format_filter.Value = 'Office Open XML Spreadsheet'
return (format_filter,)
So you shall first define the saveas path, which ends in ‘.xlsb’ and _get_filter will create the filter needed for the workbook function storeAsURL.
So, to sum up, you need to import pyoo, uno. And you need translations from excel-vba funtions to libreoffice, which tends to be more strict, but I feel is also more reliable. Sometimes the excel com instances pop from the background and I have to end them via taskmanager, which is annoying. I don’t have this problem with libreoffice.
To end, some useful links for LO documentation:
Great introduction to libreOffice ways