Macros/Basic/Calc/Ranges

From The Document Foundation Wiki
Jump to: navigation, search

Template:TopMenu/en Template:Menu/en Template:Menu.Macros/en

Other languages:

Return to Index

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)