Macros/Basic/Calc/Ranges
< Macros | Basic/Calc
Jump to navigation
Jump to search
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Ranges
Get cell by name
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 cell = sheet.getCellRangeByName("A1")
Get cell by position
1 sheet = ThisComponent.CurrentController.ActiveSheet
2
3 'Cell C6
4 column = 2
5 row = 5
6 cell = sheet.getCellByPosition(column, row)
Get range by name
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 range = sheet.getCellRangeByName("B5:D10")
Get range by position
1 sheet = ThisComponent.CurrentController.ActiveSheet
2
3 'Range B5:D10
4 start_column = 1
5 start_row = 4
6 end_column = 3
7 end_row = 9
8 range = sheet.getCellRangeByPosition(start_column, start_row, end_column, end_row)
Get address like string
1 sheet = ThisComponent.CurrentController.ActiveSheet
2
3 cell = sheet.getCellRangeByName("B2")
4 range = sheet.getCellRangeByName("B2:C5")
5
6 Msgbox cell.AbsoluteName
7 Msgbox range.AbsoluteName
Get address like struct
- Only for cells
1 sheet = ThisComponent.CurrentController.ActiveSheet
2
3 cell = sheet.getCellRangeByName("B2")
4 address = cell.CellAddress
5
6 Msgbox address.Column
7 Msgbox address.Row
- For ranges
1 sheet = ThisComponent.CurrentController.ActiveSheet
2
3 range = sheet.getCellRangeByName("B2:C5")
4 address = range.RangeAddress
5
6 Msgbox address.StartColumn
7 Msgbox address.StartRow
8 Msgbox address.EndColumn
9 Msgbox address.EndRow
Group of ranges
- Create and add ranges
1 doc = ThisComponent
2 sheet = doc.CurrentController.ActiveSheet
3 group = doc.createInstance("com.sun.star.sheet.SheetCellRanges")
4
5 range1 = sheet.getCellRangeByName("A1:B2")
6 range2 = sheet.getCellRangeByName("D5:F10")
7 range3 = sheet.getCellRangeByName("H15:J20")
8
9 group.addRangeAddress(range1.RangeAddress, False)
10 group.addRangeAddress(range2.RangeAddress, False)
11 group.addRangeAddress(range3.RangeAddress, False)
12
13 MsgBox group.RangeAddressesAsString
- We can use an array
1 ranges = Array(range1.RangeAddress, range2.RangeAddress, range3.RangeAddress)
2 group.addRangeAddresses(ranges, False)
- Remove
1 group.removeRangeAddress(range2.RangeAddress)
Columns
- Count
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 range = sheet.getCellRangeByName("D5:F10")
3
4 MsgBox range.Columns.Count
- Get by index
1 MsgBox range.Columns(0).Name
- Get by name
1 MsgBox range.Columns("E").Name
Rows
- Count
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 range = sheet.getCellRangeByName("D5:F10")
3
4 MsgBox range.Rows.Count
- Get by index
1 MsgBox range.Rows(1).AbsoluteName
Current selection
1 selection = ThisComponent.CurrentSelection
2 MsgBox selection.ImplementationName
CAUTION: Current selection can be many things.
If select is | ImplementationName |
---|---|
Cell | ScCellObj |
Range | ScCellRangeObj |
Ranges | ScCellRangesObj |
Columns | ScTableColumnsObj |
Rows | ScTableRowsObj |
Shape | com.sun.star.drawing.SvxShapeCollection |
Ranges with name
- Manage names Ctrl+F3
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 range = sheet.getCellRangeByName("Address")
3
4 MsgBox range.AbsoluteName
- Count
1 names = ThisComponent.NamedRanges
2 MsgBox names.Count
- Get by index
1 range = names(0)
2 MsgBox range.Name
- Get by name
1 range = names("Address")
2 MsgBox range.Name
- Add new
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 cell = sheet.getCellRangeByName("A1")
3 names = ThisComponent.NamedRanges
4 flag = 0
5 names.addNewByName("NewData", "$A$1:$B$7", cell.CellAddress, flag)
For flags look: NamedRangeFlag
- Remove
1 names.removeByName("NewData")
- Export list
1 names.outputList(cell.CellAddress)
Cursors
- Create by range
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 cell = sheet.getCellRangeByName("A1")
3
4 cursor = sheet.createCursorByRange(cell)
5 MsgBox cursor.ImplementationName
- Move to next cell
1 cursor.gotoNext()
- Move to previous cell
1 cursor.gotoPrevious()
- Move to (Columns, Rows)
1 cursor.gotoOffset(2, 5)
- Move to start
1 cursor.gotoStart()
- Move to end
1 cursor.gotoEnd()
- Expand to current region
1 cursor.collapseToCurrentRegion()
Tip: Get last used row in range
1 cursor.gotoEnd()
2 MsgBox cursor.RangeAddress.EndRow
3
4 'Or
5
6 cursor.collapseToCurrentRegion()
7 MsgBox cursor.RangeAddress.EndRow
- Expand to current array
1 cursor.collapseToCurrentArray()
- Expand to merged area
1 cursor.collapseToMergedArea()
- Expand to entire columns
1 cursor.expandToEntireColumns()
- Expand to entire rows
1 cursor.expandToEntireRows()
- Change size of range
1 columns = 2
2 rows = 5
3 cursor.collapseToSize(columns, rows)
- Move to start of used area
1 cursor.gotoStartOfUsedArea(False)
- Move to end of used area
1 cursor.gotoStartOfUsedArea(False)
Argument expand (True) or not (False) the range selection.
Special selections
Tip: All next methods, always get a group range (ScCellRangesObj)
- Get visible cells
1 selection = ThisComponent.CurrentSelection
2 ranges = selection.queryVisibleCells()
- Get empty cells
1 selection = ThisComponent.CurrentSelection
2 ranges = selection.queryEmptyCells()
- Get cells with text
1 selection = ThisComponent.CurrentSelection
2 ranges = selection.queryContentCells(com.sun.star.sheet.CellFlags.STRING)
Look flag options: CellFlags
Tip: You can sum two or more values.
- Get cells with error in formula
1 selection = ThisComponent.CurrentSelection
2 ranges = selection.queryFormulaCells(com.sun.star.sheet.FormulaResult.ERROR)
Look flag options: FormulaResult
- Get values differents by column.
1 selection = ThisComponent.CurrentSelection
2 source = selection.getCellByPosition(0, 0)
3 ranges = selection.queryColumnDifferences(source.CellAddress)
- Get values differents by rows.
1 selection = ThisComponent.CurrentSelection
2 source = selection.getCellByPosition(0, 0)
3 ranges = selection.queryRowDifferences(source.CellAddress)
- Get intersection cells
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 range1 = sheet.getCellRangeByName("B2:I11")
3 range2 = sheet.getCellRangeByName("G8:N17")
4
5 ranges = rango1.queryIntersection(rango2.RangeAddress)
Working with ranges
Copy
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 source = sheet.getCellRangeByName("A1:B3")
3 target = sheet.getCellRangeByName("D10")
4
5 sheet.copyRange(target.CellAddress, source.RangeAddress)
You can copy columns or rows, but make sure that the cell target it's the first cell.
1 source_columns = sheet.Columns(2)
2 source_rows = sheet.Rows(1)
3
4 target_column = sheet.getCellRangeByName("H1") 'First row
5 target_row = sheet.getCellRangeByName("A20") 'First column
Move
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 source = sheet.getCellRangeByName("A1:B3")
3 target = sheet.getCellRangeByName("D10")
4
5 sheet.moveRange(target.CellAddress, source.RangeAddress)
Insert
- Insert and cells down
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 target = CreateUnoStruct("com.sun.star.table.CellRangeAddress")
3
4 With target
5 .Sheet = sheet.RangeAddress.Sheet
6 .StartColumn = 1
7 .StartRow = 1
8 .EndColumn = 3
9 .EndRow = 3
10 End With
11
12 sheet.insertCells(target, com.sun.star.sheet.CellInsertMode.DOWN)
For other options look: CellInsertMode
- Insert columns
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 start = 4
3 columns = 2
4 sheet.Columns.insertByIndex(start, columns)
- Insert rows
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 start = 2
3 rows = 5
4 sheet.Rows.insertByIndex(start, rows)
Delete
- Delete and move cells up
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 selection = ThisComponent.CurrentSelection
3
4 sheet.removeRange(selection.RangeAddress, com.sun.star.sheet.CellDeleteMode.UP)
For other options look: CellDeleteMode
- Delete columns
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 start = 0
3 columns = 2
4 sheet.Columns.removeByIndex(start, columns)
- Delete rows
1 sheet = ThisComponent.CurrentController.ActiveSheet
2 start = 9
3 rows = 3
4 sheet.Columns.removeByIndex(start, rows)