Hi,
I would like to be able to automate data entry to an Open Office spreadsheet using a PowerShell script, in the same way that Excel can be automated using PowerShell (see this Scripting Guy example). Is this possible?
Thanks, MagicAndi
Hi,
I would like to be able to automate data entry to an Open Office spreadsheet using a PowerShell script, in the same way that Excel can be automated using PowerShell (see this Scripting Guy example). Is this possible?
Thanks, MagicAndi
It is possible using the UNO CLI bindings. Unfortunately Powershell does not cope well with proxy objects, so you need to use reflection. For example,
[System.Reflection.Assembly]::LoadWithPartialName('cli_basetypes')
[System.Reflection.Assembly]::LoadWithPartialName('cli_cppuhelper')
[System.Reflection.Assembly]::LoadWithPartialName('cli_oootypes')
[System.Reflection.Assembly]::LoadWithPartialName('cli_ure')
[System.Reflection.Assembly]::LoadWithPartialName('cli_uretypes')
$localContext = [uno.util.Bootstrap]::bootstrap()
$multiComponentFactory = [unoidl.com.sun.star.uno.XComponentContext].getMethod('getServiceManager').invoke($localContext, @())
$desktop = [unoidl.com.sun.star.lang.XMultiComponentFactory].getMethod('createInstanceWithContext').invoke($multiComponentFactory, @('com.sun.star.frame.Desktop', $localContext))
$calc = [unoidl.com.sun.star.frame.XComponentLoader].getMethod('loadComponentFromURL').invoke($desktop, @('private:factory/scalc', '_blank', 0, $null))
$sheets = [unoidl.com.sun.star.sheet.XSpreadsheetDocument].getMethod('getSheets').invoke($calc, @())
$sheet = [unoidl.com.sun.star.container.XIndexAccess].getMethod('getByIndex').invoke($sheets, @(0))
$cell = [unoidl.com.sun.star.table.XCellRange].getMethod('getCellByPosition').invoke($sheet.Value, @(0,0))
[unoidl.com.sun.star.table.XCell].getMethod('setFormula').invoke($cell, @('A value in cell A1.'))
You could also try AODL which should be much easier to use, but doesn't look like it's been updated recently.