Reading a Calc document with Powershell

[Stickybit]

Hi all

Having trouble with getting started with this one. I found a few old examples, which all starts something like this:

Code: Select all

$serviceManager = New-Object -ComObject "com.sun.star.ServiceManager"
$desktop = $serviceManager.createInstance("com.sun.star.frame.Desktop")

.. but createinstance does not seem to be a method of the COM object - so I'm stuck even before getting started.

Would anyone have a simple example on reading data from a sheet in Calc?

Br. Stickybit

[ms777]

Hi,

the ActiveX approach is a bit outdated. I prefer to bind the dlls directly.
Below is an example for LibreOffice. I prefer LibreOffice 64bit to avoid having to switch always to 32bit Powershell when working with Apache Openoffice.
It creates a file, sets one cell, and writes the file to disk.
Not very convenient, but it works ...

Good luck,

ms777

Code: Select all

if (-not [Environment]::Is64BitProcess) {
    Write-Error "this must be run from a 64 bit powershell window"
    exit
}
cls

$cliPath = "C:\Program Files\LibreOffice\sdk\cli"

('cli_basetypes', 'cli_uretypes', 'cli_oootypes', 'cli_ure', 'cli_cppuhelper') | foreach{[System.Reflection.Assembly]::Load([System.Reflection.AssemblyName]::GetAssemblyName("$($cliPath)\$($_).dll"))}

$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))

$fileProp = [unoidl.com.sun.star.beans.PropertyValue]::new()
$fileProp.Name = "Hidden"
$fileProp.Value = $false
$fileProps = [unoidl.com.sun.star.beans.PropertyValue[]] @($fileProp)

$calc = [unoidl.com.sun.star.frame.XComponentLoader].getMethod('loadComponentFromURL').invoke($desktop, @('private:factory/scalc', '_blank', 0, $fileProps))

$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.'))

[unoidl.com.sun.star.frame.XStorable].GetMethod('storeAsURL').Invoke($calc, @('file:///C:/Users/Martin/Downloads/myfile2.ods', ([unoidl.com.sun.star.beans.PropertyValue[]] @())) );

if you have installed AOO and want to stay with it replace the corresponding lines by

Code: Select all

if ([Environment]::Is64BitProcess) {
    Write-Error "this must be run from a 32 bit powershell window"
    exit
}
... and by ...
[System.Reflection.Assembly]::LoadWithPartialName('cli_cppuhelper')
[System.Reflection.Assembly]::LoadWithPartialName('cli_oootypes')
[System.Reflection.Assembly]::LoadWithPartialName('cli_ure')
[System.Reflection.Assembly]::LoadWithPartialName('cli_uretypes')