Page 1 of 1
[Solved] Read another .ods file in one .ods file with python
Posted: Wed Nov 01, 2017 3:53 pm
by strongleg
hi, I'm trying to write a python script to open and read another .ods file.
The other file name is given in a cell, the cell is in a sheet which the python script runs from.
will the XComponentLoader interface do this? I just want to read data from other .ods file, but don't want some window to show the other file.
Is there any python example to demo the use of XComponentLoader?
thanks!
Re: read another .ods file in one .ods file with python
Posted: Wed Nov 01, 2017 5:15 pm
by FJCC
This code reads a file URL (not just the file name) from Sheet1.A1 of the current document, opens as a hidden document the file at that URL, and copies the value of Sheet1.B1 from the hidden document to Sheet1.A10 of the original document. It then closes the hidden document.
Code: Select all
import uno
from com.sun.star.beans import PropertyValue
def Main():
ctx = uno.getComponentContext()
smgr = ctx.ServiceManager
oDesktop = smgr.createInstanceWithContext( 'com.sun.star.frame.Desktop',ctx)
oDoc = oDesktop.getCurrentComponent()
Sheets = oDoc.getSheets()
oSheet = oDoc.Sheets.getByName('Sheet1')
NameCell = oSheet.getCellRangeByName("A1") #A complete URL
FileName = NameCell.String
PropVal = PropertyValue()
PropVal.Name = 'Hidden'
PropVal.Value = True
oDataDoc = oDesktop.loadComponentFromURL(FileName, '_blank', 0, (PropVal,))
DataSheets = oDataDoc.getSheets()
DataSheet = DataSheets.getByName("Sheet1")
DataCell = DataSheet.getCellRangeByName("B1")
TargetCell = oSheet.getCellRangeByName("A10")
TargetCell.Value = DataCell.Value
oDataDoc.close(True)
Re: read another .ods file in one .ods file with python
Posted: Sat Nov 04, 2017 2:43 pm
by strongleg
this works fine! thanks
FJCC wrote:This code reads a file URL (not just the file name) from Sheet1.A1 of the current document, opens as a hidden document the file at that URL, and copies the value of Sheet1.B1 from the hidden document to Sheet1.A10 of the original document. It then closes the hidden document.
Code: Select all
import uno
from com.sun.star.beans import PropertyValue
def Main():
ctx = uno.getComponentContext()
smgr = ctx.ServiceManager
oDesktop = smgr.createInstanceWithContext( 'com.sun.star.frame.Desktop',ctx)
oDoc = oDesktop.getCurrentComponent()
Sheets = oDoc.getSheets()
oSheet = oDoc.Sheets.getByName('Sheet1')
NameCell = oSheet.getCellRangeByName("A1") #A complete URL
FileName = NameCell.String
PropVal = PropertyValue()
PropVal.Name = 'Hidden'
PropVal.Value = True
oDataDoc = oDesktop.loadComponentFromURL(FileName, '_blank', 0, (PropVal,))
DataSheets = oDataDoc.getSheets()
DataSheet = DataSheets.getByName("Sheet1")
DataCell = DataSheet.getCellRangeByName("B1")
TargetCell = oSheet.getCellRangeByName("A10")
TargetCell.Value = DataCell.Value
oDataDoc.close(True)