Macros/Basic/Calc/Ranges

    From The Document Foundation Wiki
    This page contains changes which are not marked for translation.

    TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

    Other languages:

    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
    For Each service In selection.SupportedServiceNames
        MsgBox service
    Next service

    CAUTION: Current selection can be many things. Use supportsService method to disambiguate.

    If select is Supported service name
    Cell com.sun.star.sheet.SheetCell
    Range com.sun.star.sheet.SheetCellRange
    Ranges com.sun.star.sheet.SheetCellRanges
    Columns com.sun.star.table.TableColumns
    Rows com.sun.star.table.TableRows
    Shape com.sun.star.drawing.ShapeCollection


    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)

    Retrieved from "https://wiki.documentfoundation.org/index.php?title=Macros/Basic/Calc/Ranges&oldid=768287"