下記の記事で、aws-cli で取得したJSONデータを、LibreOffice(calc)に読み込むことができました。
- awscliのdescribeで取得したjsonデータをLibreOffice(calc)に読み込むPythonマクロ
- LibreOffice(calc)のBasicマクロからPythonマクロを実行する
ここでは、Basicマクロでヘッダと罫線を作成してAMI一覧を完成させます。
「LibreOffice(calc)のBasicマクロからPythonマクロを実行する」で作成したマクロを修正して、以下のようにします。
REM ***** BASIC *****
Sub Main
' JSONデータ入力. Pythonマクロ(TestScript.py の amiList)を実行
Dim a(0),b(0),c(0) As Variant
scpr = ThisComponent.getScriptProvider
scmod = scpr.getScript("vnd.sun.star.script:TestScript.py$amiList?language=Python&location=user")
ret = scmod.invoke(a,b,c)
'ヘッダ
Dim oDoc as Object, oSheet as Object
oDoc = ThisComponent
oSheet = oDoc.CurrentController.ActiveSheet
oSheet.getCellRangeByName("A1").String = "入力位置"
oSheet.getCellRangeByName("B1").String = "VirtualizationType"
oSheet.getCellRangeByName("C1").String = "Name"
oSheet.getCellRangeByName("D1").String = "Hypervisor"
oSheet.getCellRangeByName("E1").String = "ImageId"
oSheet.getCellRangeByName("F1").String = "RootDeviceType"
oSheet.getCellRangeByName("G1").String = "Architecture"
oSheet.getCellRangeByName("H1").String = "[RootDevice]" & Chr$(10) & "Name"
oSheet.getCellRangeByName("I1").String = "[RootDevice]" & Chr$(10) & "DeleteOnTermination"
oSheet.getCellRangeByName("J1").String = "[RootDevice]" & Chr$(10) & "VolumeSize"
oSheet.getCellRangeByName("K1").String = "[RootDevice]" & Chr$(10) & "VolumeType"
oSheet.getCellRangeByName("L1").String = "[RootDevice]" & Chr$(10) & "Encrypted"
' ヘッダ属性
oSheet.getCellRangeByName("A1:L1").CellBackColor = RGB(0, 0, 100)
oSheet.getCellRangeByName("A1:L1").CharColor = RGB(255, 255, 255)
oSheet.getCellRangeByName("A1:L1").VertJustify = com.sun.star.table.CellVertJustify.TOP
' セル高さ＆幅の最適化
oSheet.getRows.OptimalHeight = True
oSheet.getColumns.OptimalWidth = True
'最終行
Dim oCursor as Object
Dim oRange As Object
Dim oEndRow as Long
oRange = oSheet.getCellRangeByName("A2")
oCursor = oSheet.createCursorByRange(oRange)
oCursor.gotoEndOfUsedArea(True)
oEndRow = oCursor.Rows.Count + 1
'msgbox(oEndRow,0,"最終行取得")
'罫線
Dim oCtrl as Object
Dim oSelRange as Object, oCellRange as Object
Dim oBorder1 as Object, oBorder2 as Object, oBorder3 as Object, oBorder4 as Object
oCtrl = oDoc.getCurrentController()
oSelRange = oCtrl.getActiveSheet().getCellRangeByName("A1:L" & oEndRow)
oCtrl.select( oSelRange )
'
oCellRange = oDoc.CurrentSelection(0)
' Border1 Property
oBorder1 = CreateUnoStruct("com.sun.star.table.BorderLine2")
oBorder1.Color = RGB(0, 0, 0)
oBorder1.LineWidth = 1
oBorder1.LineStyle = 2
' Border2 Property
oBorder2 = CreateUnoStruct("com.sun.star.table.BorderLine2")
oBorder2.Color = RGB(0, 0, 0)
oBorder2.LineWidth = 1
oBorder2.LineStyle = 2
' Border3 Property
oBorder3 = CreateUnoStruct("com.sun.star.table.BorderLine2")
oBorder3.Color = RGB(0, 0, 0)
oBorder3.LineWidth = 1
oBorder3.LineStyle = 2
' Border4 Property
oBorder4 = CreateUnoStruct("com.sun.star.table.BorderLine2")
oBorder4.Color = RGB(0, 0, 0)
oBorder4.LineWidth = 1
oBorder4.LineStyle = 2
' Set Border
oCellRange.BottomBorder = oBorder1
oCellRange.TopBorder = oBorder2
oCellRange.LeftBorder = oBorder3
oCellRange.RightBorder = oBorder4
'
oSelRange = oCtrl.getActiveSheet().getCellRangeByName( "A1" )
oCtrl.select( oSelRange )
End Sub
このマクロを実行すると、JSONデータを入力して、以下のようなＡＭＩ一覧が作成されます。
