カテゴリー: LibreOffice Calc

Print GetPathSeparator()

Dim saveFilePath As String
saveFilePath = "/tmp/test.txt"
Dim lines as Object
lines = New Collection
lines.Add("test1")
lines.Add("test2")
Dim simpleFileAccess As Object
simpleFileAccess = CreateUnoService("com.sun.star.ucb.SimpleFileAccess")
If simpleFileAccess.exists(saveFilePath) Then
    simpleFileAccess.kill(saveFilePath)
End If
Dim textOutputStream As Object
textOutputStream = CreateUnoService("com.sun.star.io.TextOutputStream")
Dim outputStream As Object
outputStream = simpleFileAccess.openFileWrite(saveFilePath)
textOutputStream.setOutputStream(outputStream)
textOutputStream.setEncoding("UTF-8")
For Each line In lines
    textOutputStream.writeString(line)
    textOutputStream.writeString(Chr(10))
Next line
textOutputStream.closeOutput()

Dim collection as Object
collection = new Collection
collection.Add("test1")
collection.Add("test2")
Print(collection.Count)
Print(collection.Item(1)) 'インデックスは1から
collection.Remove(1)
For Each value In collection
    Print(value)
Next value

Version: 7.3.7.2

住所から郵便番号を返すWebAPIの結果をWEBSERVICE関数でセルに表示する。下記はExcelAPIを利用する関数の使用例。

=WEBSERVICE("http://api.excelapi.org/post/zipcode?address="&ENCODEURL(A1))

Calcを使用して「test.ods」から「呼び出されるドキュメント.ods」のFunctionを呼び出して結果を取得するのが目的。Version: LibreOffice 6.4.7.2

呼び出されるドキュメント.odsのFunctionは下記の通り。

Function Test(a, b)
    Print a
    b = "test3"
    Test = 1
End Function

test.odsから呼び出して結果を取得する。

Dim document As Object
document = StarDesktop.loadComponentFromURL(ConvertToUrl("/home/user/Desktop/呼び出されるドキュメント.ods"), "_blank", 0, Array())
Dim scriptProvider As Object
scriptProvider = document.getScriptProvider()
Dim url As String
url ="vnd.sun.star.script:Standard.Module1.Test?language=Basic&location=document"
Dim script As Object
script = scriptProvider.getScript(url)
Dim args As Object
args = array("test1", "test2")
Dim argsAfterFinishing As Object
argsAfterFinishing = array()
'test1と1が表示される
Print script.invoke(args, 0, argsAfterFinishing)
'test2が表示される
Print args(1)
'test3が表示される
Print argsAfterFinishing(1)

On Error Goto ErrorHandler
'Properties
Dim properties(2) As New com.sun.star.beans.PropertyValue
properties(0).Name = "user"
properties(0).value = "UserName"
properties(1).Name = "password"
properties(1).value = "Pass"
properties(2).name = "JavaDriverClass"
properties(2).value = "org.postgresql.Driver"
'Driver manager
Dim driverManager As Object
driverManager = createUnoService("com.sun.star.sdbc.DriverManager")
'Connection
Dim connection As Object
connection = driverManager.getConnectionWithInfo("jdbc:postgresql://192.168.0.1:5432/database-name", properties())
'Statement
Dim statement As Object
statement = connection.createStatement()
'Execute and number of update records
Print statement.executeUpdate("DELETE FROM table1;")
'ResultSet
Dim resultSet As Object
resultSet = statement.executeQuery("SELECT * FROM table2;")
While resultSet.Next
    Print resultSet.getString(1)
Wend
'Close
statement.close()
connection.close()
connection.dispose()
Exit Sub
'Error handle
ErrorHandler:
If IsNull(statement) = false Then
    statement.close()
End If
If IsNull(connection) = false Then
    If connection.isClosed() = false Then
        connection.close()
        connection.dispose()
    End If
End If
If InStr(Error$, "org.postgresql.Driver") > 0 Then
    MsgBox("ツール→オプション→LibreOffice→詳細→クラスパスからPostgreSQLのJDBCドライバーを追加してください。", 64, "初期設定")
Elseif Len(Error$) > 0 Then
    MsgBox(Error$, 16, "エラー")
End If

「ツール」→「マクロ」→「ダイアログの管理」から追加した”Dialog1″を表示してダイアログ内のテキストフィールドの値を取得するコード。ちなみにダイアログをコードで閉じるにはdialogオブジェクトのendexecute()メソッドを呼ぶ。

'Show dialog
DialogLibraries.LoadLibrary("Standard")
dialog = CreateUnoDialog(DialogLibraries.Standard.Dialog1)
dialog.execute()
'Get text
Dim textField As Object
textField = dialog.getControl("TextField1")
Dim value As String
value = textField.getText()

Dim properties(1) as new com.sun.star.beans.PropertyValue
properties(0).Name = "CopyCount"
properties(0).Value = 2
properties(1).Name = "Collate"
properties(1).Value = True
ThisComponent.print(properties)

Dim dispatcher As Object
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(ThisComponent.getCurrentController().getFrame(), ".uno:Print", "", 0, Array())

Dim filePicker As Object
filePicker = createUnoService("com.sun.star.ui.dialogs.FilePicker")
filePicker.initialize(Array(com.sun.star.ui.dialogs.TemplateDescription.FILESAVE_SIMPLE))
filePicker.appendFilter("テキストファイル(*.txt)", "*.txt")
If filePicker.execute() = 1 Then
    Dim saveFiles() As String
    saveFiles = filePicker.getFiles()
    Dim saveFileUrl As String
    saveFileUrl = saveFiles(0)
    Dim saveFilePath As String
    saveFilePath = ConvertFromURL(saveFileUrl)
    MsgBox(saveFilePath) '拡張子付いてないかも
End If