Macroの杜
(LibreOffice Basic編)

< 前 Calc No.1(Cell Insert/Delete) 次 >

Cell操作

[ 挿入・削除 ]

CCI-)[Calc]Cellの挿入 ⇔ Insert the Cell [ com.sun.star.sheet.CellInsertMode.DOWN / com.sun.star.sheet.CellInsertMode.RIGHT　/ .uno:InsertCellDown / .uno:InsertCellRight / .uno:InsertCell ]
上に挿入

現在のセルは下に移動(Shift down)


sub InsertCellDown()
    Dim Doc As Object, oSheet As Object
    Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
    	oDoc = ThisComponent
    	oSheet =oDoc.getSheets().getByIndex(0)
    	CellRangeAddress.Sheet = 0
    	CellRangeAddress.StartColumn = 2
    	CellRangeAddress.StartRow = 2
    	CellRangeAddress.EndColumn = 4
    	CellRangeAddress.EndRow = 4
    	oSheet.insertCells(CellRangeAddress, com.sun.star.sheet.CellInsertMode.DOWN)
    	msgbox "Success"
End Sub
			

解説(Explanation )

【 解説 】
このLibreOfficeマクロは、指定したシートにC3～E5にセルを挿入するものです。
元のセルは下方向に移動します。

(1)最初に　Calcのセル範囲を表わすオブジェクトを設定
		Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
		
(2)シートインデックスを指定して　対象シートを指定
		CellRangeAddress.Sheet = 0
		
(3)Cellを挿入する範囲を指定
    	CellRangeAddress.StartColumn ～ CellRangeAddress.EndRow
		
(4)現状のセルをどの方向に移動するか指定
		com.sun.star.sheet.CellInsertMode.DOWN　　：　下方向


(3)の範囲を変更すると、任意の場所にセルを挿入することができます。
				

【マクロの記録】上に挿入

【マクロの記録】現在のセルは下に移動(Shift down)

Sub UnoInsertCell()
    Dim oDoc as Object, oCtrl as Object, oFrame as Object
    Dim oDispatcher as Object
	Dim oProp(0) as new com.sun.star.beans.PropertyValue
		oFrame = ThisComponent.CurrentController.Frame
		oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
		'
		oProp(0).Name = "ToPoint"
		oProp(0).Value = "A1"		' "1:1" → 行全体を下げる挿入 
		oDispatcher.executeDispatch(oFrame,  ".uno:GoToCell", "", 0, oProp())
		' Insert Cell ( Direction of Existed Cell is Down )
		oDispatcher.executeDispatch(oFrame,  ".uno:InsertCellsDown", "", 0, Array())
		'
		msgbox "InsertCellsDown" & Chr$(10) & "(Dispatcher)",0,"Insert Cells"
End Sub
			

左に挿入

現在のセルは右方向に移動Shift right)
Sub InsertCellRight()
    Dim Doc As Object, oSheet As Object
    Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
    	oDoc = ThisComponent
    	oSheet =oDoc.getSheets().getByIndex(0)
    	CellRangeAddress.Sheet = 0
    	CellRangeAddress.StartColumn = 2
    	CellRangeAddress.StartRow = 2
    	CellRangeAddress.EndColumn = 4
    	CellRangeAddress.EndRow = 4
    	oSheet.insertCells(CellRangeAddress, com.sun.star.sheet.CellInsertMode.RIGHT)
    	msgbox "Success"
End Sub
			

【マクロの記録】左に挿入

【マクロの記録】現在のセルは右方向に移動Shift right)

Sub UnoInsertCell()
    Dim oDoc as Object, oCtrl as Object, oFrame as Object
    Dim oDispatcher as Object
	Dim oProp(0) as new com.sun.star.beans.PropertyValue
		oFrame = ThisComponent.CurrentController.Frame
		oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
		'
		oProp(0).Name = "ToPoint"
		oProp(0).Value = "A1"				'  "A:A" で 列全体を右に移動
		oDispatcher.executeDispatch(oFrame,  ".uno:GoToCell", "", 0, oProp())
		' Insert Cell ( Direction of Existed Cell is Right )
		oDispatcher.executeDispatch(oFrame,  ".uno:InsertCellsRight", "", 0, Array())
		'
		msgbox "InsertCellsRight" & Chr$(10) & "(Dispatcher)",0,"Insert Cells"
End Sub
			

【マクロの記録】その２

【マクロの記録】現在のセルは右･下に移動

Sub UnoInsertCell()
    Dim oDoc as Object, oCtrl as Object, oFrame as Object
    Dim oDispatcher as Object
	Dim oProp(0) as new com.sun.star.beans.PropertyValue
		oFrame = ThisComponent.CurrentController.Frame
		oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
		'
		oProp(0).Name = "ToPoint"
		oProp(0).Value = "B3:C5" 
		oDispatcher.executeDispatch(oFrame,  ".uno:GoToCell", "", 0, oProp())
		'
		oProp(0).Name = "Flags"
		oProp(0).Value = ">"
		oDispatcher.executeDispatch(oFrame,  ".uno:InsertCell", "", 0, oProp())
		'
		msgbox "Success", 0,"Uno / Insert"
End Sub
'
' [ Note ]
'  V :  Cellを下に移動
'  > :  Cellを右に移動
'  R :  行全体を下に移動
'  C :  列全体を右に移動
			


CCI-)[Calc]Cellの削除 ⇔ Delete the Cell [ com.sun.star.sheet.CellDeleteMode.UP / com.sun.star.sheet.CellDeleteMode.LEFT ]
上に詰める

Cellの削除(1)[既存データは上方向に移動](Shift up)
Sub DeleteCellUp()
    Dim Doc As Object, oSheet As Object
    Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
    	oSheet =ThisComponent.getSheets().getByIndex(iSheetindex)
    	CellRangeAddress.Sheet = 0
    	CellRangeAddress.StartColumn = 2
    	CellRangeAddress.StartRow = 2
    	CellRangeAddress.EndColumn = 5
    	CellRangeAddress.EndRow = 5
    	oSheet.removeRange(CellRangeAddress, com.sun.star.sheet.CellDeleteMode.UP)
End  Sub		
			

左に詰める

Cellの削除(2)[既存データは左方向に移動](Shift left)
Sub oDeleteCellLeft()
    Dim Doc As Object, oSheet As Object
    Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
    Dim iStart as integer
    Dim iRows as integer
    Dim iSheetindex as integer
    	 iSheetindex = 0
    	 iStart = 0
    	 iRows=3
    	oDoc = ThisComponent
    	oSheet =oDoc.getSheets().getByIndex(iSheetindex)
    	CellRangeAddress.Sheet = iSheetindex
    	CellRangeAddress.StartColumn = 0
    	CellRangeAddress.StartRow = iStart
    	CellRangeAddress.EndColumn = 2
    	CellRangeAddress.EndRow = iStart + iRows-1
    	oSheet.insertCells(CellRangeAddress, com.sun.star.sheet.CellDeleteMode.LEFT)
End Sub
			

行を上に詰める

Cellの削除(3)[行全体が上方向に移動]
Sub oDeleteCellUp()
    Dim Doc As Object, oSheet As Object
    Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
    Dim iStart as integer
    Dim iRows as integer
    Dim iSheetindex as integer
    	 iSheetindex = 0
    	 iStart = 0
    	 iRows=3
    	oSheet=ThisComponent.getSheets().getByIndex(iSheetindex)
    	CellRangeAddress.Sheet = iSheetindex
    	CellRangeAddress.StartColumn = 0
    	CellRangeAddress.StartRow = iStart
    	CellRangeAddress.EndColumn = 2
    	CellRangeAddress.EndRow = iStart + iRows-1
    	oSheet.insertCells(CellRangeAddress, com.sun.star.sheet.CellDeleteMode.ROWS)
End Sub
			

列を左に詰める

Cellの削除(4)[列全体が左方向に移動]
Sub oDeleteCellLeft()
    Dim Doc As Object, oSheet As Object
    Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
    Dim iStart as integer
    Dim iRows as integer
    Dim iSheetindex as integer
    	 iSheetindex = 0
    	 iStart = 0
    	 iRows=3
    	oSheet =ThisComponent.getSheets().getByIndex(iSheetindex)
    	CellRangeAddress.Sheet = iSheetindex
    	CellRangeAddress.StartColumn = 0
    	CellRangeAddress.StartRow = iStart
    	CellRangeAddress.EndColumn = 2
    	CellRangeAddress.EndRow = iStart + iRows-1
    	oSheet.insertCells(CellRangeAddress, com.sun.star.sheet.CellDeleteMode.COLUMNS)
End Sub
			

マクロの記録

Cellの削除(5)[左･上･行全体･列全体が移動]
Sub CalcDeleteCell()
	Dim oDoc as Object, oCtrl as Object, oFrame as Object
	Dim oDispatcher as Object
	Dim oProp1(0) as new com.sun.star.beans.PropertyValue
	Dim oProp(0) as new com.sun.star.beans.PropertyValue
		oDoc=ThisComponent
		oCtrl = oDoc.getCurrentController()
		oFrame = oCtrl.getFrame()
		oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
			oProp1(0).Name = "ToPoint"
			oProp1(0).Value = "A1:B6"
		oDispatcher.executeDispatch(oFrame, ".uno:GoToCell", "", 0, oProp1())
			oProp(0).Name = "Flags"
			oProp(0).Value = "U"
		oDispatcher.executeDispatch(oFrame, ".uno:DeleteCell", "", 0, oProp())
		msgbox "Success"
End Sub
'
' [ Flag Value ]
' U    : Cell を 上に移動 
' L    : Cell を 左に移動
' R    : 行全体を削除
' C    : 列全体を削除
			





コピー(Copy) に続く