Macros/Basic/Calc/Ranges

From The Document Foundation Wiki
Jump to navigation Jump to search
Other languages:
English • ‎español • ‎italiano

Return to Index

Ranges

Get cell by name

sheet = ThisComponent.CurrentController.ActiveSheet
    cell = sheet.getCellRangeByName("A1")

Get cell by position

sheet = ThisComponent.CurrentController.ActiveSheet

    'Cell C6
    column = 2
    row = 5
    cell = sheet.getCellByPosition(column, row)

Get range by name

sheet = ThisComponent.CurrentController.ActiveSheet
    range = sheet.getCellRangeByName("B5:D10")

Get range by position

sheet = ThisComponent.CurrentController.ActiveSheet

    'Range B5:D10
    start_column = 1
    start_row = 4
    end_column = 3
    end_row = 9
    range = sheet.getCellRangeByPosition(start_column, start_row, end_column, end_row)

Get address like string

sheet = ThisComponent.CurrentController.ActiveSheet

    cell = sheet.getCellRangeByName("B2")
    range = sheet.getCellRangeByName("B2:C5")

    Msgbox cell.AbsoluteName
    Msgbox range.AbsoluteName

Get address like struct

  • Only for cells

sheet = ThisComponent.CurrentController.ActiveSheet

    cell = sheet.getCellRangeByName("B2")
    address = cell.CellAddress

    Msgbox address.Column
    Msgbox address.Row

  • For ranges

sheet = ThisComponent.CurrentController.ActiveSheet

    range = sheet.getCellRangeByName("B2:C5")
    address = range.RangeAddress

    Msgbox address.StartColumn
    Msgbox address.StartRow
    Msgbox address.EndColumn
    Msgbox address.EndRow

Group of ranges

  • Create and add ranges

doc = ThisComponent
    sheet = doc.CurrentController.ActiveSheet
    group = doc.createInstance("com.sun.star.sheet.SheetCellRanges")

    range1 = sheet.getCellRangeByName("A1:B2")
    range2 = sheet.getCellRangeByName("D5:F10")
    range3 = sheet.getCellRangeByName("H15:J20")

    group.addRangeAddress(range1.RangeAddress, False)
    group.addRangeAddress(range2.RangeAddress, False)
    group.addRangeAddress(range3.RangeAddress, False)

    MsgBox group.RangeAddressesAsString

  • We can use an array

ranges = Array(range1.RangeAddress, range2.RangeAddress, range3.RangeAddress)
    group.addRangeAddresses(ranges, False)

  • Remove

group.removeRangeAddress(range2.RangeAddress)

Columns

  • Count

sheet = ThisComponent.CurrentController.ActiveSheet
    range = sheet.getCellRangeByName("D5:F10")

    MsgBox range.Columns.Count

  • Get by index

MsgBox range.Columns(0).Name

  • Get by name

MsgBox range.Columns("E").Name

Rows

  • Count

sheet = ThisComponent.CurrentController.ActiveSheet
    range = sheet.getCellRangeByName("D5:F10")

    MsgBox range.Rows.Count

  • Get by index

MsgBox range.Rows(1).AbsoluteName

Current selection

selection = ThisComponent.CurrentSelection
    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

sheet = ThisComponent.CurrentController.ActiveSheet
    range = sheet.getCellRangeByName("Address")

    MsgBox range.AbsoluteName

  • Count

names = ThisComponent.NamedRanges
    MsgBox names.Count

  • Get by index

range = names(0)
    MsgBox range.Name

  • Get by name

range = names("Address")
    MsgBox range.Name

  • Add new

sheet = ThisComponent.CurrentController.ActiveSheet
    cell = sheet.getCellRangeByName("A1")
    names = ThisComponent.NamedRanges
    flag = 0
    names.addNewByName("NewData", "$A$1:$B$7", cell.CellAddress, flag)

For flags look: NamedRangeFlag

  • Remove

names.removeByName("NewData")

  • Export list

names.outputList(cell.CellAddress)

Cursors

  • Create by range

sheet = ThisComponent.CurrentController.ActiveSheet
    cell = sheet.getCellRangeByName("A1")

    cursor = sheet.createCursorByRange(cell)
    MsgBox cursor.ImplementationName

  • Move to next cell

cursor.gotoNext()

  • Move to previous cell

cursor.gotoPrevious()

  • Move to (Columns, Rows)

cursor.gotoOffset(2, 5)

  • Move to start

cursor.gotoStart()

  • Move to end

cursor.gotoEnd()

  • Expand to current region

cursor.collapseToCurrentRegion()

Tip: Get last used row in range

cursor.gotoEnd()
    MsgBox cursor.RangeAddress.EndRow

    'Or

    cursor.collapseToCurrentRegion()
    MsgBox cursor.RangeAddress.EndRow

  • Expand to current array

cursor.collapseToCurrentArray()

  • Expand to merged area

cursor.collapseToMergedArea()

  • Expand to entire columns

cursor.expandToEntireColumns()

  • Expand to entire rows

cursor.expandToEntireRows()

  • Change size of range

columns = 2
    rows = 5
    cursor.collapseToSize(columns, rows)

  • Move to start of used area

cursor.gotoStartOfUsedArea(False)

  • Move to end of used area

cursor.gotoEndOfUsedArea(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

selection = ThisComponent.CurrentSelection
    ranges = selection.queryVisibleCells()

  • Get empty cells

selection = ThisComponent.CurrentSelection
    ranges = selection.queryEmptyCells()

  • Get cells with text

selection = ThisComponent.CurrentSelection
    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

selection = ThisComponent.CurrentSelection
    ranges = selection.queryFormulaCells(com.sun.star.sheet.FormulaResult.ERROR)

Look flag options: FormulaResult

  • Get values differents by column.

selection = ThisComponent.CurrentSelection
    source = selection.getCellByPosition(0, 0)
    ranges = selection.queryColumnDifferences(source.CellAddress)

  • Get values differents by rows.

selection = ThisComponent.CurrentSelection
    source = selection.getCellByPosition(0, 0)
    ranges = selection.queryRowDifferences(source.CellAddress)

  • Get intersection cells

sheet = ThisComponent.CurrentController.ActiveSheet
    range1 = sheet.getCellRangeByName("B2:I11")
    range2 = sheet.getCellRangeByName("G8:N17")

    ranges = rango1.queryIntersection(rango2.RangeAddress)


Working with ranges

Copy

sheet = ThisComponent.CurrentController.ActiveSheet
    source = sheet.getCellRangeByName("A1:B3")
    target = sheet.getCellRangeByName("D10")

    sheet.copyRange(target.CellAddress, source.RangeAddress)

You can copy columns or rows, but make sure that the cell target it's the first cell.

source_columns = sheet.Columns(2)
    source_rows = sheet.Rows(1)

    target_column = sheet.getCellRangeByName("H1")  'First row
    target_row = sheet.getCellRangeByName("A20")    'First column

Move

sheet = ThisComponent.CurrentController.ActiveSheet
    source = sheet.getCellRangeByName("A1:B3")
    target = sheet.getCellRangeByName("D10")

    sheet.moveRange(target.CellAddress, source.RangeAddress)

Insert

  • Insert and cells down

sheet = ThisComponent.CurrentController.ActiveSheet
    target = CreateUnoStruct("com.sun.star.table.CellRangeAddress")

    With target
        .Sheet = sheet.RangeAddress.Sheet
        .StartColumn = 1
        .StartRow = 1
        .EndColumn = 3
        .EndRow = 3
    End With

    sheet.insertCells(target, com.sun.star.sheet.CellInsertMode.DOWN)

For other options look: CellInsertMode

  • Insert columns

sheet = ThisComponent.CurrentController.ActiveSheet
    start = 4
    columns = 2
    sheet.Columns.insertByIndex(start, columns)

  • Insert rows

sheet = ThisComponent.CurrentController.ActiveSheet
    start = 2
    rows = 5
    sheet.Rows.insertByIndex(start, rows)

Delete

  • Delete and move cells up

sheet = ThisComponent.CurrentController.ActiveSheet
    selection = ThisComponent.CurrentSelection

    sheet.removeRange(selection.RangeAddress, com.sun.star.sheet.CellDeleteMode.UP)

For other options look: CellDeleteMode

  • Delete columns

sheet = ThisComponent.CurrentController.ActiveSheet
    start = 0
    columns = 2
    sheet.Columns.removeByIndex(start, columns)

  • Delete rows

sheet = ThisComponent.CurrentController.ActiveSheet
    start = 9
    rows = 3
    sheet.Rows.removeByIndex(start, rows)