Macros/Basic/Calc/Rangos

From The Document Foundation Wiki
Jump to: navigation, search
This page is a translated version of the page Macros/Basic/Calc/Ranges and the translation is 100% complete.

Other languages:
English • ‎español

Return to Index

Rangos

Celda por nombre

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

Celda por posición

1     sheet = ThisComponent.CurrentController.ActiveSheet
2 
3     'Cell C6
4     column = 2
5     row = 5
6     cell = sheet.getCellByPosition(column, row)

Rango por nombre

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

Rango por posición

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)

Obtener dirección como cadena

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

Obtener dirección como estructura

  • Solo para celdas
1     sheet = ThisComponent.CurrentController.ActiveSheet
2 
3     cell = sheet.getCellRangeByName("B2")
4     address = cell.CellAddress
5 
6     Msgbox address.Column
7     Msgbox address.Row
  • Para rangos
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

Grupo de rangos

  • Crear y agregar rangos
 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
  • Podemos usar un array
1     ranges = Array(range1.RangeAddress, range2.RangeAddress, range3.RangeAddress)
2     group.addRangeAddresses(ranges, False)
  • Borrar
1     group.removeRangeAddress(range2.RangeAddress)

Columnas

  • Contar
1     sheet = ThisComponent.CurrentController.ActiveSheet
2     range = sheet.getCellRangeByName("D5:F10")
3 
4     MsgBox range.Columns.Count
  • Acceso por índice
1     MsgBox range.Columns(0).Name
  • Acceso por nombre
1     MsgBox range.Columns("E").Name

Filas

  • Contar
1     sheet = ThisComponent.CurrentController.ActiveSheet
2     range = sheet.getCellRangeByName("D5:F10")
3 
4     MsgBox range.Rows.Count
  • Acceso por índice
1     MsgBox range.Rows(1).AbsoluteName

Selección actual

1     selection = ThisComponent.CurrentSelection
2     MsgBox selection.ImplementationName

PRECAUCIÓN: La selección actual pueden ser muchas cosas.

Si la selección es: ImplementationName
Celda ScCellObj
Rango ScCellRangeObj
Rangos ScCellRangesObj
Columnas ScTableColumnsObj
Filas ScTableRowsObj
Forma com.sun.star.drawing.SvxShapeCollection

Rangos con nombre

  • Administrar nombres Ctrl+F3
1     sheet = ThisComponent.CurrentController.ActiveSheet
2     range = sheet.getCellRangeByName("Address")
3 
4     MsgBox range.AbsoluteName
  • Contar
1     names = ThisComponent.NamedRanges
2     MsgBox names.Count
  • Acceso por índice
1     range = names(0)
2     MsgBox range.Name
  • Acceso por nombre
1     range = names("Address")
2     MsgBox range.Name
  • Agregar nuevo
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)

Para el argumento flags mira: NamedRangeFlag

  • Borrar
1     names.removeByName("NewData")
  • Exportar lista
1     names.outputList(cell.CellAddress)

Cursores

  • Crear desde rango
1     sheet = ThisComponent.CurrentController.ActiveSheet
2     cell = sheet.getCellRangeByName("A1")
3 
4     cursor = sheet.createCursorByRange(cell)
5     MsgBox cursor.ImplementationName
  • Mover a la siguiente celda
1     cursor.gotoNext()
  • Mover a la celda previa.
1     cursor.gotoPrevious()
  • Mover a (Columnas, Filas).
1     cursor.gotoOffset(2, 5)
  • Mover al inicio
1     cursor.gotoStart()
  • Mover al final
1     cursor.gotoEnd()
  • Expandir a la región actual
1     cursor.collapseToCurrentRegion()

Tip: Obtener la última fila usada en el rango.

1     cursor.gotoEnd()
2     MsgBox cursor.RangeAddress.EndRow
3 
4     'Or
5 
6     cursor.collapseToCurrentRegion()
7     MsgBox cursor.RangeAddress.EndRow
  • Expandir a la matriz actual
1     cursor.collapseToCurrentArray()
  • Expandir a las celdas combinadas.
1     cursor.collapseToMergedArea()
  • Expandir a columnas enteras.
1     cursor.expandToEntireColumns()
  • Expandir a filas enteras.
1     cursor.expandToEntireRows()
  • Cambiar el tamaño del rango.
1     columns = 2
2     rows = 5
3     cursor.collapseToSize(columns, rows)
  • Mover al inicio del área de usuario.
1     cursor.gotoStartOfUsedArea(False)
  • Mover al final del área de usuario.
1     cursor.gotoStartOfUsedArea(False)

Argumento para expandir (True) o no (False) la selección del rango.


Selecciones especiales

Tip: Todos los siguiente métodos, siempre regresan un grupo de rangos (ScCellRangesObj).

  • Obtener las celdas visibles.
1     selection = ThisComponent.CurrentSelection
2     ranges = selection.queryVisibleCells()
  • Obtener las celdas vacías.
1     selection = ThisComponent.CurrentSelection
2     ranges = selection.queryEmptyCells()
  • Obtener las celdas con texto.
1     selection = ThisComponent.CurrentSelection
2     ranges = selection.queryContentCells(com.sun.star.sheet.CellFlags.STRING)

Mira: CellFlags

Tip: Puedes sumar dos o más valores.

  • Obtener celdas con error en la formula.
1     selection = ThisComponent.CurrentSelection
2     ranges = selection.queryFormulaCells(com.sun.star.sheet.FormulaResult.ERROR)

Mira: FormulaResult

  • Obtener valores diferentes por columna.
1     selection = ThisComponent.CurrentSelection
2     source = selection.getCellByPosition(0, 0)
3     ranges = selection.queryColumnDifferences(source.CellAddress)
  • Obtener valores diferentes por fila.
1     selection = ThisComponent.CurrentSelection
2     source = selection.getCellByPosition(0, 0)
3     ranges = selection.queryRowDifferences(source.CellAddress)
  • Obtener la intersección de rangos.
1     sheet = ThisComponent.CurrentController.ActiveSheet
2     range1 = sheet.getCellRangeByName("B2:I11")
3     range2 = sheet.getCellRangeByName("G8:N17")
4 
5     ranges = rango1.queryIntersection(rango2.RangeAddress)


Trabajando con rangos

Copiar

1     sheet = ThisComponent.CurrentController.ActiveSheet
2     source = sheet.getCellRangeByName("A1:B3")
3     target = sheet.getCellRangeByName("D10")
4 
5     sheet.copyRange(target.CellAddress, source.RangeAddress)

Podemos copiar columnas y filas, pero hay que asegurarse que la celda destino es la primer celda.

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

Mover

1     sheet = ThisComponent.CurrentController.ActiveSheet
2     source = sheet.getCellRangeByName("A1:B3")
3     target = sheet.getCellRangeByName("D10")
4 
5     sheet.moveRange(target.CellAddress, source.RangeAddress)

Insertar

  • Insertar y mover celdas abajo
 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)

Para otras opciones ver: CellInsertMode

  • Insertar columnas
1     sheet = ThisComponent.CurrentController.ActiveSheet
2     start = 4
3     columns = 2
4     sheet.Columns.insertByIndex(start, columns)
  • Insertar filas
1     sheet = ThisComponent.CurrentController.ActiveSheet
2     start = 2
3     rows = 5
4     sheet.Rows.insertByIndex(start, rows)

Borrar

  • Borrar y mover las celdas arriba
1     sheet = ThisComponent.CurrentController.ActiveSheet
2     selection = ThisComponent.CurrentSelection
3 
4     sheet.removeRange(selection.RangeAddress, com.sun.star.sheet.CellDeleteMode.UP)

Para otras opciones ver: CellDeleteMode

  • Borrar columnas
1     sheet = ThisComponent.CurrentController.ActiveSheet
2     start = 0
3     columns = 2
4     sheet.Columns.removeByIndex(start, columns)
  • Borrar filas
1     sheet = ThisComponent.CurrentController.ActiveSheet
2     start = 9
3     rows = 3
4     sheet.Columns.removeByIndex(start, rows)