Page 1 of 1

[Solved] Read another .ods file in one .ods file with python

PostPosted: 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

PostPosted: 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   Expand viewCollapse view
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

PostPosted: 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   Expand viewCollapse view
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)