LibreOfficeでPostgreSQLデータベースにJDBCで接続

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:
On Error Resume Next
statement.close()
connection.close()
connection.dispose()
If InStr(Error$, "org.postgresql.Driver") > 0 Then
    MsgBox("ツール→オプション→LibreOffice→詳細→クラスパスからPostgreSQLのJDBCドライバーを追加してください。", 64, "初期設定")
Elseif Len(Error$) > 0 Then
    MsgBox(Error$, 16, "エラー")
End If

LibreOffice Calcでダイアログを作成して表示する

「ツール」→「マクロ」→「ダイアログの管理」から追加した”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 filePicker As Object
Dim saveFiles() As Variant
Dim saveFileUrl As String
Dim saveFilePath As String

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
    saveFiles = filePicker.getFiles()
    saveFileUrl = saveFiles(0)
    saveFilePath = ConvertFromURL(saveFileUrl)
    MsgBox(saveFilePath) '拡張子付いてないかも
End If

ファイルの選択ダイアログを表示する

Dim filePicker As Object
Dim loadFiles() As Variant
Dim loadFileUrl As String
Dim loadFilePath As String

filePicker = createUnoService("com.sun.star.ui.dialogs.FilePicker")
filePicker.initialize(Array(com.sun.star.ui.dialogs.TemplateDescription.FILEOPEN_SIMPLE))

filePicker.appendFilter("テキストファイル(*.txt)", "*.txt")
filePicker.appendFilter("HTMLファイル(*.htm, *.html)", "*.htm;*.html")
filePicker.appendFilter("すべてのファイル(*.*)", "*.*")

If filePicker.execute() = 1 Then
    loadFiles = filePicker.getFiles()
    loadFileUrl = loadFiles(0)
    loadFilePath = ConvertFromURL(loadFileUrl)
    MsgBox(loadFilePath)
End If

LibreOffice Calcでセルの行番号/列番号を取得する

Dim startRow As Integer
startRow = ThisComponent.CurrentSelection.RangeAddress.StartRow

Dim endRow As Integer 'endRowは2になる
endRow = ThisComponent.Sheets.getByIndex(0).getCellRangeByName("A1:A3").RangeAddress.EndRow

Dim startColumn As Integer 'startColumnは1になる
startColumn = ThisComponent.Sheets.getByName("sheet1").getCellRangeByName("B1:C6").RangeAddress.StartColumn

Dim endColumn As Integer 'endColumnは3になる
endColumn = ThisComponent.Sheets.getByName("sheet2").getCellRangeByName("A1:D9").RangeAddress.EndColumn

ADODBでデータベースに接続する(Windows限定)

Dim connection As Object
Dim recordset As Object
Dim command As Object
connection = CreateObject("ADODB.Connection")
recordset = CreateObject("ADODB.Recordset")
command = CreateObject("ADODB.Command")

'SQLServerに接続してみる
connection.Open "Provider=SQLOLEDB;Data Source=DB_SERVER\SQLEXPRESS;Initial Catalog=DB_NAME;User ID=sa;Password=123456;"

command.ActiveConnection = connection
command.CommandTimeout = 0
command.CommandText = "SELECT * FROM TABLE_NAME;"
recordset = command.Execute

If recordset.RecordCount > 0 Then
    If recordset.Eof = false Then
       recordset.MoveFirst
     MsgBox(recordset.Fields.Item("field1").Value)
       'MsgBox(recordset.Fields.Item("field1").Name) ←これは使えない
 End If
End if

ディレクトリ選択ダイアログを表示する

Dim folderPicker As Object
Dim isAccept As Integer
Dim folders()
Dim folderUrl as String
Dim folderName as String
folderPicker = createUnoService("com.sun.star.ui.dialogs.FolderPicker")
folderPicker.setTitle ("ディレクトリの選択")
folderPicker.setDescription("ディレクトリを選択してください。")
folderPicker.setDisplayDirectory("C:\Users\Test\Desktop")
isAccept = folderPicker.execute()
If isAccept = 1 Then
    folders() = folderPicker.getDirectory()
    folderUrl = folders(0)
    folderName = ConvertFromURL(folderUrl)
    MsgBox(folderName)
Else
    folderPicker.cancel()
End If

LibreOffice CalcのStyleを変更する

Dim styleFamilies As Object
Dim styles As Object
Dim style As Object
styleFamilies = ThisComponent.StyleFamilies
styles = styleFamilies.getByName("PageStyles")
style = styles.getByName("標準")
style.HeaderIsOn = false 'ヘッダーOFF
style.FooterIsOn = false 'フッターOFF
style.CenterHorizontally = true '横方向中央配置
style.Height = 25000 '用紙高さ250mm
style.Width = 17600 '用紙幅176mm
style.scaleToPages = 1 '1ページに収める

主なプロパティ

プロパティ
CenterHorizontally boolean
CenterVertically boolean
PrintAnnotations boolean
PrintGrid boolean
PrintHeaders boolean
PrintCharts boolean
PrintObjects boolean
PrintDrawing boolean
PrintFormulas boolean
PrintZeroValues boolean
PrintDownFirst boolean
LeftPageHeaderContent XHeaderFooterContent
LeftPageFooterContent XHeaderFooterContent
RightPageHeaderContent XHeaderFooterContent
RightPageFooterContent XHeaderFooterContent
FirstPageNumber short
PageScale short
ScaleToPages short
ScaleToPagesX short
ScaleToPagesY short
BackColor ::com::sun::star::util::Color
BackGraphicURL string
BackGraphicFilter string
BackGraphicLocation GraphicLocation
BackTransparent boolean
LeftMargin long
RightMargin long
TopMargin long
BottomMargin long
LeftBorder ::com::sun::star::table::BorderLine
RightBorder ::com::sun::star::table::BorderLine
TopBorder ::com::sun::star::table::BorderLine
BottomBorder ::com::sun::star::table::BorderLine
LeftBorderDistance long
RightBorderDistance long
TopBorderDistance long
BottomBorderDistance long
ShadowFormat ::com::sun::star::table::ShadowFormat
IsLandscape boolean
NumberingType short
PageStyleLayout PageStyleLayout
PrinterPaperTray string
RegisterModeActive boolean
RegisterParagraphStyle string
Size ::com::sun::star::awt::Size
Width long
Height long
TextColumns ::com::sun::star::text::XTextColumns
UserDefinedAttributes ::com::sun::star::container::XNameContainer
HeaderBackColor [ maybevoid ] ::com::sun::star::util::Color
HeaderBackGraphicURL [ maybevoid ] string
HeaderBackGraphicFilter [ maybevoid ] string
HeaderBackGraphicLocation [ maybevoid ] GraphicLocation
HeaderLeftMargin [ maybevoid ] long
HeaderRightMargin [ maybevoid ] long
HeaderBackTransparent [ maybevoid ] boolean
HeaderLeftBorder [ maybevoid ] ::com::sun::star::table::BorderLine
HeaderRightBorder [ maybevoid ] ::com::sun::star::table::BorderLine
HeaderTopBorder [ maybevoid ] ::com::sun::star::table::BorderLine
HeaderBottomBorder [ maybevoid ] ::com::sun::star::table::BorderLine
HeaderLeftBorderDistance [ maybevoid ] long
HeaderRightBorderDistance [ maybevoid ] long
HeaderTopBorderDistance [ maybevoid ] long
HeaderBottomBorderDistance [ maybevoid ] long
HeaderShadowFormat [ maybevoid ] ::com::sun::star::table::ShadowFormat
HeaderBodyDistance [ maybevoid ] long
HeaderIsShared [ maybevoid ] boolean
HeaderHeight [ maybevoid ] long
HeaderIsDynamicHeight [ maybevoid ] boolean
HeaderIsOn boolean
HeaderText [ maybevoid ] ::com::sun::star::text::XText
HeaderTextLeft [ maybevoid ] ::com::sun::star::text::XText
HeaderTextRight [ maybevoid ] ::com::sun::star::text::XText
FooterBackColor [ maybevoid ] ::com::sun::star::util::Color
FooterBackGraphicURL [ maybevoid ] string
FooterBackGraphicFilter [ maybevoid ] string
FooterBackGraphicLocation [ maybevoid ] GraphicLocation
FooterLeftMargin [ maybevoid ] long
FooterRightMargin [ maybevoid ] long
FooterBackTransparent [ maybevoid ] boolean
FooterLeftBorder [ maybevoid ] ::com::sun::star::table::BorderLine
FooterRightBorder [ maybevoid ] ::com::sun::star::table::BorderLine
FooterTopBorder [ maybevoid ] ::com::sun::star::table::BorderLine
FooterBottomBorder [ maybevoid ] ::com::sun::star::table::BorderLine
FooterLeftBorderDistance [ maybevoid ] long
FooterRightBorderDistance [ maybevoid ] long
FooterTopBorderDistance [ maybevoid ] long
FooterBottomBorderDistance [ maybevoid ] long
FooterShadowFormat [ maybevoid ] ::com::sun::star::table::ShadowFormat
FooterBodyDistance [ maybevoid ] long
FooterIsDynamicHeight [ maybevoid ] boolean
FooterIsShared [ maybevoid ] boolean
FooterHeight [ maybevoid ] long
FooterIsOn boolean
FooterText [ maybevoid ] ::com::sun::star::text::XText
FooterTextLeft [ maybevoid ] ::com::sun::star::text::XText
FooterTextRight [ maybevoid ] ::com::sun::star::text::XText
FootnoteHeight long
FootnoteLineWeight short
FootnoteLineColor ::com::sun::star::util::Color
FootnoteLineRelativeWidth byte
FootnoteLineAdjust short
FootnoteLineTextDistance long
FootnoteLineDistance long
WritingMode short
GridMode short
GridColor ::com::sun::star::util::Color
GridLines short
GridBaseHeight long
GridRubyHeight long
GridRubyBelow boolean
GridPrint boolean
GridDisplay boolean
HeaderDynamicSpacing [ maybevoid ] boolean
FooterDynamicSpacing [ maybevoid ] boolean
BorderDistance long
FooterBorderDistance [ maybevoid ] long
HeaderBorderDistance [ maybevoid ] long