Macros/Basic/Calc/Rangos
< Macros | Basic/Calc
Jump to navigation
Jump to search
TDF LibreOffice en español Document Liberation Blogs comunitarios Weblate Nextcloud Redmine Preguntas y respuestas Donar
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)