Rangos de datos
Los rangos de datos, son áreas rectangulares de rangos de celdas delimitados por al menos una fila y una columna en blanco a las cuales se les establece un nombre y tienen características especiales que facilitan su uso como si de una base de datos se tratara.
No confundas estos nombres, con los que puedes definir en el cuadro de nombres de la barra de formulas, ya que son distintos. Las mayor parte de las opciones que estudiaremos en este capitulo, son las presentes en el menú Datos, de la interfaz del usuario.
El uso de hojas de calculo con datos tratados como bases de datos, es, según mi experiencia, el uso más cotidiano dado a esta herramienta, y no es gratuito, las herramientas disponibles para trabajar con datos estructurados de esta manera, presentes en Calc, sin hacer uso de macros, es bastante amplio, poderoso y versátil, ahora, imagínate lo que se puede hacer, automatizando estos procesos.
Si bien este libro no es de bases de datos explícitamente, te ayudará mucho recordar que cada columna de tu rango de datos, lo podemos llamar; campo, y cada fila de estos; registros. Así mismo, en la primer fila de estos, se establecen los “títulos de campo”, normalmente en un formato diferente del resto de los datos, aunque esta primer fila no es indispensable, es mucho mejor tenerla.
También, es recomendable, no dejar ninguna fila en blanco, entre esta y los datos, las filas, es decir, los registros, es mejor si están completos, es decir, que todos sus campos contienen datos. De nuevo, esto no es indispensable, pero una base de datos se hace para llenarse de datos. Tampoco es recomendable dejar filas completas en blanco. Trata de que tus datos sean “consistentes”, esto quiere decir que si una columna (campo) lo llamas Edad, efectivamente se capturen números, o fechas si la calculas. En la actualidad hay una amplia fuente de información para una buena construcción de tus bases de datos, de tarea, busca algo acerca del siguiente tema: “normalización de bases de datos”, veras que tema tan interesante es.
Definiendo rangos
En el siguiente ejemplo, definimos un rango de bases de datos, en la primer hoja del documento, en el rango A1:D24.
Sub RangoDeDatos1()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
Dim oDir As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
'Accedemos al conjunto de los rangos de bases de datos
oRangosBD = oDoc.DataBaseRanges()
'El nombre del nuevo rango
sNombre = "Direcciones"
'La dirección del nuevo rango
With oDir
.Sheet = 0 'La hoja
.StartColumn = 0 'La columna de inicio
.EndColumn = 3 'La columna final
.StartRow = 0 'La fila de inicio
.EndRow = 23 'La fila final
End With
'Lo agregamos a la colección
oRangosBD.addNewByName( sNombre, oDir )
'Regresamos el rango recién agregado
oRBD = oRangosBD.getByName( sNombre )
With oRBD
.MoveCells = True 'Para que se actualice al insertar o eliminar celdas
.KeepFormats = True 'Para que mantenga los formatos
End With
End Sub
El nombre del nuevo rango no debe de existir o te dará un error, en el siguiente ejemplo, solicitamos al usuario el nombre del rango de datos y tomamos la selección actual como dirección para el nuevo rango de datos, también, verificamos que no exista el nuevo nombre.
Sub RangoDeDatos2()
Dim oDoc As Object
Dim oSel As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
Dim oDir As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
'Nos aseguramos de que sea un rango de celdas
If oSel.getImplementationNAme = "ScCellRangeObj" Then
'Solicitamos el nuevo nombre
sNombre = Trim( InputBox("Escribe el nombre del nuevo rango") )
'Si no esta vacío
If sNombre <> "" Then
oRangosBD = oDoc.DataBaseRanges()
'Verificamos que no exista el nombre
If Not oRangosBD.hasByName( sNombre ) Then
'Y lo agregamos. Observa como tomamos la dirección de la selección
oRangosBD.addNewByName( sNombre, oSel.getRangeAddress() )
oRBD = oRangosBD.getByName( sNombre )
With oRBD
.MoveCells = True
.KeepFormats = True
End With
Else
MsgBox "Ya existe el nombre del rango"
End If
Else
MsgBox "El nombre no puede estar vacío"
End If
Else
MsgBox "No es un rango de celdas"
End If
End Sub
Para borrar un rango de datos usamos el método removeByName, toma en cuenta que lo único que se borra es el nombre del rango de datos y sus propiedades, las celdas y sus valores se mantienen.
Sub RangoDeDatos3()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
'El nombre del rango a borrar
sNombre = "Pruebas"
'Si el nombre no existe obtendrás un error
If oRangosBD.hasByName( sNombre ) Then
'Lo removemos de la colección
oRangosBD.removeByName( sNombre )
MsgBox "Rango de datos borrado"
Else
MsgBox "El rango de datos no existe"
End If
End Sub
Podemos cambiar las propiedades de un rango de datos existente, por ejemplo, cambiarle el nombre.
Sub RangoDeDatos4()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
'El nombre del rango a modificar
sNombre = "Pruebas"
If oRangosBD.hasByName( sNombre ) Then
oRBD = oRangosBD.getByName( sNombre )
'Le cambiamos el nombre
oRBD.setName ("Nuevo nombre")
Else
MsgBox "El rango de datos no existe"
End If
End Sub
O cambiar su dirección.
Sub RangoDeDatos5()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
Dim oDir As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
'El nombre del rango a modificar
sNombre = "Direcciones"
If oRangosBD.hasByName( sNombre ) Then
oRBD = oRangosBD.getByName( sNombre )
'Cambiamos la dirección
With oDir
.Sheet = 0 'Cambiamos la hoja
.StartColumn = 2 'La columna de inicio
.EndColumn = 5 'La columna final
.StartRow = 5 'La fila de inicio
.EndRow = 19 'La fila final
End With
oRBD.setDataArea( oDir )
Else
MsgBox "El rango de datos no existe"
End If
End Sub
Cambia la dirección de un rango con cuidado, puede suceder que acabe apuntando a un rango vacío de celdas sin querer.
Ordenar datos
Para ordenar datos, no necesitas forzosamente un rango de datos con nombre, veamos como ordenar un rango de celdas con y sin nombre de rango de datos. Tomaremos como datos de ejemplo, los siguientes.
Nº | Nombre | Año | Edad |
---|---|---|---|
1 | nikole | 1976 | 33 |
2 | gloria | 1976 | 33 |
3 | antonio | 1977 | 32 |
4 | lidia | 1967 | 42 |
5 | paola | 1979 | 30 |
6 | vanessa | 1974 | 35 |
7 | paola | 1972 | 37 |
8 | paola | 1968 | 41 |
9 | paola | 1968 | 41 |
10 | lizette | 1978 | 31 |
11 | lizette | 1978 | 31 |
12 | lizette | 1978 | 31 |
13 | nikole | 1977 | 32 |
14 | gloria | 1975 | 34 |
15 | antonio | 1979 | 30 |
16 | lidia | 1977 | 32 |
17 | paola | 1968 | 41 |
18 | vanessa | 1978 | 31 |
19 | lizette | 1969 | 40 |
20 | nikole | 1970 | 39 |
21 | gloria | 1971 | 38 |
22 | antonio | 1973 | 36 |
23 | lidia | 1968 | 41 |
Vamos a ordenar los datos por nombre.
Sub OrdenarDatos1()
Dim oHoja As Object
Dim oRango As Object
Dim mCamposOrden(0) As New com.sun.star.table.TableSortField
Dim mDescriptorOrden()
'La hoja donde esta el rango a ordenar
oHoja = ThisComponent.getSheets.getByName("agosto")
'El rango a ordenar
oRango = oHoja.getCellRangeByName("A1:D24")
'Descriptor de ordenamiento, o sea, el "como"
mDescriptorOrden = oRango.createSortDescriptor()
'Los campos a orden, o sea, el "que"
'Los campos empiezan en 0
mCamposOrden(0).Field = 1
'Orden ascendente
mCamposOrden(0).IsAscending = True
'Sensible a MAYUSCULAS/minusculas
mCamposOrden(0).IsCaseSensitive = False
'Tipo de campo AUTOMATICO
mCamposOrden(0).FieldType = com.sun.star.table.TableSortFieldType.AUTOMATIC
'Indicamos si el rango contiene títulos de campos
mDescriptorOrden(1).Name = "ContainsHeader"
mDescriptorOrden(1).Value = True
'La matriz de campos a ordenar
mDescriptorOrden(3).Name = "SortFields"
mDescriptorOrden(3).Value = mCamposOrden
'Ordenamos con los parámetros establecidos
oRango.sort( mDescriptorOrden )
End Sub
Los puntos a los que debes poner atención son; el tipo de campo FieldType, puede tomar los siguientes valores.
com.sun.star.table.TableSortFieldType | Valor | Valor en Interfaz |
---|---|---|
com.sun.star.table.TableSortFieldType.AUTOMATIC | 0 | Automático |
com.sun.star.table.TableSortFieldType.NUMERIC | 1 | Numérico |
com.sun.star.table.TableSortFieldType.ALPHANUMERIC | 2 | Alfanumérico |
En mis pruebas, establecer en uno y en otro, no me ha dado muchas variantes en velocidad, esto es, supongo, por que he hecho pruebas con pocos datos, habría que hacer pruebas de rendimiento con grandes datos para ver su desempeño pues no creo que esta propiedad este de adorno.
En el descriptor de orden, si estableces la propiedad; ContainsHeader, en falso (False), y tu rango de datos efectivamente tiene títulos de campo, estos no serán tomados en cuenta y se ordenaran en relación con el resto de tus datos, mi recomendación es que siempre establezcas esta propiedad en verdadero (True) y efectivamente te asegures de tenerlos, claro, a menos de que no te sea indispensable o de plano no los necesites.
Si quieres ordenar por más de un campo (por ahora el limite es tres), no hay más que agregar el segundo criterio a la matriz de campos, como en el ejemplo siguiente que ordenamos por nombre ascendente y después por edad descendente.
Sub OrdenarDatos2()
Dim oHoja As Object
Dim oRango As Object
Dim mCamposOrden(1) As New com.sun.star.table.TableSortField
Dim mDescriptorOrden()
oHoja = ThisComponent.getSheets.getByName("agosto")
oRango = oHoja.getCellRangeByName("A1:D24")
mDescriptorOrden = oRango.createSortDescriptor()
mCamposOrden(0).Field = 1
mCamposOrden(0).IsAscending = True
mCamposOrden(0).IsCaseSensitive = False
mCamposOrden(0).FieldType = com.sun.star.table.TableSortFieldType.AUTOMATIC
'Agregamos un segundo campo
mCamposOrden(1).Field = 3
'Este es descendente
mCamposOrden(1).IsAscending = False
mCamposOrden(1).IsCaseSensitive = False
mCamposOrden(1).FieldType = com.sun.star.table.TableSortFieldType.AUTOMATIC
mDescriptorOrden(1).Name = "ContainsHeader"
mDescriptorOrden(1).Value = True
mDescriptorOrden(3).Name = "SortFields"
mDescriptorOrden(3).Value = mCamposOrden
'Ordenamos con los parámetros establecidos
oRango.sort( mDescriptorOrden )
End Sub
Una opción muy interesante, es la posibilidad de enviar el resultado a un destino diferente como en el siguiente ejemplo.
Sub OrdenarDatos3()
Dim oHoja As Object
Dim oRango As Object
Dim mCamposOrden(0) As New com.sun.star.table.TableSortField
Dim oDestino As Object
Dim mDescriptorOrden()
oHoja = ThisComponent.getSheets.getByName("agosto")
oRango = oHoja.getCellRangeByName("A1:D24")
oDestino = oHoja.getCellRangeByName("G1")
mDescriptorOrden = oRango.createSortDescriptor()
mCamposOrden(0).Field = 1
mCamposOrden(0).IsAscending = True
mCamposOrden(0).IsCaseSensitive = False
mCamposOrden(0).FieldType = com.sun.star.table.TableSortFieldType.AUTOMATIC
mDescriptorOrden(1).Name = "ContainsHeader"
mDescriptorOrden(1).Value = True
mDescriptorOrden(3).Name = "SortFields"
mDescriptorOrden(3).Value = mCamposOrden
'Establecemos que queremos copiar el resultado a otro lado
mDescriptorOrden(5).Name = "CopyOutputData"
mDescriptorOrden(5).Value = True
'Establecemos el destino de la copia
mDescriptorOrden(6).Name = "OutputPosition"
mDescriptorOrden(6).Value = oDestino.getCellAddress()
'Ordenamos con los parámetros establecidos
oRango.sort( mDescriptorOrden )
End Sub
Observa como establecemos el destino con una estructura getCellAddress, esta solo incluye la hoja destino, la columna y la fila de inicio. Si en el rango destino existen datos, estos serán totalmente reemplazados sin preguntarte nada.
Ahora, ordenamos, pero accediendo desde un rango de datos.
Sub OrdenarDatos4()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
Dim oRango As Object
Dim mCamposOrden(0) As New com.sun.star.table.TableSortField
Dim mDescriptorOrden()
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
sNombre = "Direcciones"
'Verificamos que exista el nombre del rango de datos
If oRangosBD.hasByName( sNombre ) Then
'Referencia al rango
oRBD = oRangosBD.getByName( sNombre )
'Referencia al origen del rango
oRango = oRBD.getReferredCells()
mDescriptorOrden = oRango.createSortDescriptor()
mCamposOrden(0).Field = 1
mCamposOrden(0).IsAscending = True
mCamposOrden(0).IsCaseSensitive = False
mCamposOrden(0).FieldType = com.sun.star.table.TableSortFieldType.AUTOMATIC
mDescriptorOrden(1).Name = "ContainsHeader"
mDescriptorOrden(1).Value = True
mDescriptorOrden(3).Name = "SortFields"
mDescriptorOrden(3).Value = mCamposOrden
oRango.sort( mDescriptorOrden )
Else
MsgBox "El rango de datos no existe"
End If
End Sub
La propiedad importante es; getReferredCells, que te da acceso al rango origen de los datos, esta propiedad te devuelve un objeto ScCellRangeObj, por lo que puedes tener acceso a todas las propiedades de manipulación y formato vistas de este objeto, por ejemplo, puedes aplicarle un autoformato de tabla a los datos.
Filtrar datos
Las opciones que estudiaremos en este capitulo, son las presentes en el menú Datos | Filtro -> y seguiremos usando los datos del tema anterior.
Doy por hecho que no tienes problemas, como usuario, con cada una de estas opciones, veamos como establecerlas por código. Primero, la más sencilla, el filtro automático, te agrega un control de lista desplegable en el encabezado de campo de tus datos, como en.
Para hacer lo mismo por código, usamos.
Sub FiltrarDatos1()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
sNombre = "Direcciones"
If oRangosBD.hasByName( sNombre ) Then
'Referencia al rango
oRBD = oRangosBD.getByName( sNombre )
'Mostramos el autofiltro
oRBD.AutoFilter = True
Else
MsgBox "El rango de datos no existe"
End If
End Sub
¿Y para quitarlos?, muy bien deducido, solo la establecemos en falso (False)
Sub FiltrarDatos2()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
sNombre = "Direcciones"
If oRangosBD.hasByName( sNombre ) Then
'Referencia al rango
oRBD = oRangosBD.getByName( sNombre )
'Mostramos el autofiltro
oRBD.AutoFilter = False
Else
MsgBox "El rango de datos no existe"
End If
End Sub
Pero cuidado, la macro anterior, solo te quitará las flechas para desplegar el filtro del campo, si tienes establecido un filtro automático, este permanecerá, para eliminar completamente un filtro, tienes que hacer dos cosas; primero, eliminar el filtro y después mostrar las filas ocultas como te muestro en el siguiente ejemplo.
Sub FiltrarDatos3()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
Dim oDesFiltro As Object
Dim mCamposFiltro() As New com.sun.star.sheet.TableFilterField
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
sNombre = "Direcciones"
If oRangosBD.hasByName( sNombre ) Then
oRBD = oRangosBD.getByName( sNombre )
'Obtenemos la descripción del filtro
oDesFiltro = oRBD.getFilterDescriptor()
'Le pasamos una matriz vacía con una estructura de campo de filtro
oDesFiltro.FilterFields = mCamposFiltro
'Quitamos las flechas
oRBD.AutoFilter = False
'Mostramos las filas ocultas
oRBD.getReferredCells.getRows.IsVisible = True
Else
MsgBox "El rango de datos no existe"
End If
End Sub
Entonces, cuando filtramos por código, no es indispensable mostrar las flechas de los campos del filtro, podemos filtrar directamente como en el siguiente ejemplo.
Sub FiltrarDatos4()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
Dim oDesFiltro As Object
Dim mCamposFiltro(0) As New com.sun.star.sheet.TableFilterField
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
sNombre = "Direcciones"
If oRangosBD.hasByName( sNombre ) Then
oRBD = oRangosBD.getByName( sNombre )
oDesFiltro = oRBD.getFilterDescriptor()
'El campo por el que queremos filtrar
mCamposFiltro(0).Field = 1
'El tipo de comparación
mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
'Si es un número
mCamposFiltro(0).IsNumeric = False
'El valor de comparación
mCamposFiltro(0).StringValue = "lizette"
'Le pasamos los campos
oDesFiltro.FilterFields = mCamposFiltro
'Refrescamos el rango para ver el resultado del filtro
oRBD.refresh()
Else
MsgBox "El rango de datos no existe"
End If
End Sub
Que comprobamos que lo hace correctamente:
Ahora, veamos como filtrar un campo con dos condiciones.
Sub FiltrarDatos5()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
Dim oDesFiltro As Object
Dim mCamposFiltro(1) As New com.sun.star.sheet.TableFilterField
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
sNombre = "Direcciones"
If oRangosBD.hasByName( sNombre ) Then
oRBD = oRangosBD.getByName( sNombre )
oDesFiltro = oRBD.getFilterDescriptor()
mCamposFiltro(0).Field = 1
mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
mCamposFiltro(0).IsNumeric = False
mCamposFiltro(0).StringValue = "lizette"
'Agregamos la segunda condición al mismo campo
mCamposFiltro(1).Field = 1
'Establecemos la relación con la condición anterior
mCamposFiltro(1).Connection = com.sun.star.sheet.FilterConnection.OR
mCamposFiltro(1).Operator = com.sun.star.sheet.FilterOperator.EQUAL
mCamposFiltro(1).IsNumeric = False
mCamposFiltro(1).StringValue = "paola"
oDesFiltro.FilterFields = mCamposFiltro
oRBD.refresh()
Else
MsgBox "El rango de datos no existe"
End If
End Sub
Observa muy bien la propiedad Connection, es muy importante establecer correctamente esta, para obtener el resultado deseado, solo tiene dos posibilidades, una “O” (OR) de disyunción o una “Y” (AND) de conjunción, muchos errores de filtros incorrectos, son por la incorrecta aplicación de este sencillo parámetro, comprobamos que nuestro filtro esta correcto, y veamos más ejemplos.
Ahora filtraremos con condiciones en dos campos diferentes.
Sub FiltrarDatos6()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
Dim oDesFiltro As Object
Dim mCamposFiltro(1) As New com.sun.star.sheet.TableFilterField
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
sNombre = "Direcciones"
If oRangosBD.hasByName( sNombre ) Then
oRBD = oRangosBD.getByName( sNombre )
oDesFiltro = oRBD.getFilterDescriptor()
mCamposFiltro(0).Field = 1
mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
mCamposFiltro(0).IsNumeric = False
mCamposFiltro(0).StringValue = "lidia"
'Agregamos la segunda condición a otro campo
mCamposFiltro(1).Field = 3
'Establecemos la relación con la condición anterior (Y)
mCamposFiltro(1).Connection = com.sun.star.sheet.FilterConnection.AND
mCamposFiltro(1).Operator = com.sun.star.sheet.FilterOperator.EQUAL
'Ahora si buscamos por número
mCamposFiltro(1).IsNumeric = True
mCamposFiltro(1).NumericValue = 32
oDesFiltro.FilterFields = mCamposFiltro
oRBD.refresh()
Else
MsgBox "El rango de datos no existe"
End If
End Sub
Y una vez más lo comprobamos.
En los siguientes ejemplos, ya no te mostraré el resultado, dando por entendido, que estas comprobando, como yo, que el resultado esperado es el correcto. En el siguiente ejemplo, filtramos a todos los que tengan entre 30 y 35 años.
Sub FiltrarDatos7()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
Dim oDesFiltro As Object
Dim mCamposFiltro(1) As New com.sun.star.sheet.TableFilterField
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
sNombre = "Direcciones"
If oRangosBD.hasByName( sNombre ) Then
oRBD = oRangosBD.getByName( sNombre )
oDesFiltro = oRBD.getFilterDescriptor()
mCamposFiltro(0).Field = 3
mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL
mCamposFiltro(0).IsNumeric = True
mCamposFiltro(0).NumericValue = 30
'Agregamos la segunda condición a otro campo
mCamposFiltro(1).Field = 3
mCamposFiltro(1).Connection = com.sun.star.sheet.FilterConnection.AND
mCamposFiltro(1).Operator = com.sun.star.sheet.FilterOperator.LESS_EQUAL
mCamposFiltro(1).IsNumeric = True
mCamposFiltro(1).NumericValue = 35
oDesFiltro.FilterFields = mCamposFiltro
oRBD.refresh()
Else
MsgBox "El rango de datos no existe"
End If
End Sub
Observa como hemos cambiado la propiedad “operador” (Operator) para satisfacer la condición, los posibles valores para esta propiedad, viene condicionados por la enumeración com.sun.star.sheet.FilterOperator, cuyos valores son.
com.sun.star.sheet.FilterOperator | Valor | Valor en Interfaz |
---|---|---|
com.sun.star.sheet.FilterOperator.EMPTY | 0 | Vacío |
com.sun.star.sheet.FilterOperator.NOT_EMPTY | 1 | No vacío |
com.sun.star.sheet.FilterOperator.EQUAL | 2 | Igual |
com.sun.star.sheet.FilterOperator.NOT_EQUAL | 3 | No igual |
com.sun.star.sheet.FilterOperator.GREATER | 4 | Mayor que |
com.sun.star.sheet.FilterOperator.GREATER_EQUAL | 5 | Mayor o igual que |
com.sun.star.sheet.FilterOperator.LESS | 6 | Menor que |
com.sun.star.sheet.FilterOperator.LESS_EQUAL | 7 | Menor o igual que |
com.sun.star.sheet.FilterOperator.TOP_VALUES | 8 | El mayor valor |
com.sun.star.sheet.FilterOperator.TOP_PERCENT | 9 | El mayor porcentaje |
com.sun.star.sheet.FilterOperator.BOTTOM_VALUES | 10 | El menor valor |
com.sun.star.sheet.FilterOperator.BOTTOM_PERCENT | 11 | El menor porcentaje |
En el siguiente ejemplo, seleccionamos los cinco registros con más edad.
Sub FiltrarDatos8()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
Dim oDesFiltro As Object
Dim mCamposFiltro(0) As New com.sun.star.sheet.TableFilterField
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
sNombre = "Direcciones"
If oRangosBD.hasByName( sNombre ) Then
oRBD = oRangosBD.getByName( sNombre )
oDesFiltro = oRBD.getFilterDescriptor()
mCamposFiltro(0).Field = 3
mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.TOP_VALUES
mCamposFiltro(0).IsNumeric = True
'Los cinco de más edad
mCamposFiltro(0).NumericValue = 5
oDesFiltro.FilterFields = mCamposFiltro
oRBD.refresh()
Else
MsgBox "El rango de datos no existe"
End If
End Sub
Ahora, filtremos todos los nombres que comiencen por la letra “L”.
Sub FiltrarDatos9()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
Dim oDesFiltro As Object
Dim mCamposFiltro(0) As New com.sun.star.sheet.TableFilterField
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
sNombre = "Direcciones"
If oRangosBD.hasByName( sNombre ) Then
oRBD = oRangosBD.getByName( sNombre )
oDesFiltro = oRBD.getFilterDescriptor()
mCamposFiltro(0).Field = 1
mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
mCamposFiltro(0).IsNumeric = False
'Observa el parametro de la condición
mCamposFiltro(0).StringValue = "l.*"
'Establecemos que use expresiones regulares
oDesFiltro.UseRegularExpressions = True
oDesFiltro.FilterFields = mCamposFiltro
oRBD.refresh()
Else
MsgBox "El rango de datos no existe"
End If
End Sub
Observa como hemos establecido la propiedad para usar expresiones regulares (UseRegularExpressions) del descriptor del filtro para que tenga efecto nuestra condición. Las expresiones regulares son un concepto muy poderoso como podrás averiguarlo en tu buscador favorito.
Otra posibilidad bastante interesante de los filtros, es poder copiar el resultado en otra posición y dejar el origen intacto, veamos como.
Sub FiltrarDatos10()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
Dim oDesFiltro As Object
Dim mCamposFiltro(0) As New com.sun.star.sheet.TableFilterField
Dim oDestino As Object
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
sNombre = "Direcciones"
If oRangosBD.hasByName( sNombre ) Then
oRBD = oRangosBD.getByName( sNombre )
oDesFiltro = oRBD.getFilterDescriptor()
mCamposFiltro(0).Field = 1
mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
mCamposFiltro(0).IsNumeric = False
mCamposFiltro(0).StringValue = "gloria"
'Le indicamos que queremos el resultado en otro lugar
oDesFiltro.CopyOutputData = True
'Y le indicamos donde, observa como obtenemos la dirección de la primer celda del rango
oDestino = oRBD.ReferredCells().getCellByPosition(0,0).getCellAddress()
'Después sumamos el ancho y alto del rango para dejar una columna y fila en blanco
oDestino.Column = oDestino.Column + oRBD.ReferredCells.getColumns.getCount + 1
oDestino.Row = oDestino.Row + oRBD.ReferredCells.getRows.getCount + 1
'Establecemos el destino
oDesFiltro.OutputPosition = oDestino
oDesFiltro.FilterFields = mCamposFiltro
oRBD.refresh()
Else
MsgBox "El rango de datos no existe"
End If
End Sub
En versiones anteriores a la 3.2, la propiedad para indicar que queremos el resultado en otra posición se llama: SaveOutputPosition, esta propiedad solo aplicaba para rangos de datos, a partir de la versión 3.2 se homologó para usar la misma propiedad tanto en rangos de datos como en rangos de celdas. Si las celdas destino no están vacías, estas, serán reemplazadas sin preguntarte nada, modifica la macro, para evaluar esto y se lo notifiques al usuario.
Otra característica muy poderosa de los filtros, es la posibilidad de filtrar los registros, omitiendo los duplicados, copia varios registros iguales para que notes la diferencia, se hace de la siguiente manera.
Sub FiltrarDatos11()
Dim oDoc As Object
Dim oRangosBD As Object
Dim sNombre As String
Dim oRBD As Object
Dim oDesFiltro As Object
Dim mCamposFiltro(0) As New com.sun.star.sheet.TableFilterField
Dim oDestino As Object
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
sNombre = "Direcciones"
If oRangosBD.hasByName( sNombre ) Then
oRBD = oRangosBD.getByName( sNombre )
oDesFiltro = oRBD.getFilterDescriptor()
mCamposFiltro(0).Field = 0
'Seleccionamos los registros NO vacíos
mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.NOT_EMPTY
'Le indicamos que solo queremos registros únicos
oDesFiltro.SkipDuplicates = True
oDesFiltro.CopyOutputData = True
oDestino = oRBD.ReferredCells.getCellByPosition(0,0).getCellAddress()
oDestino.Column = oDestino.Column + oRBD.ReferredCells.getColumns.getCount + 1
oDesFiltro.OutputPosition = oDestino
oDesFiltro.FilterFields = mCamposFiltro
oRBD.refresh()
Else
MsgBox "El rango de datos no existe"
End If
End Sub
Hasta ahora, hemos aplicados filtros, desde un rango de datos, pero los filtros no están limitados a estas áreas, al ser un método de rango de celdas (ScCellRangeObj), puedes aplicar un filtro a cualquier rango de celdas, la siguiente macro, toma el rango de celdas seleccionado y filtra los datos únicos dos columnas a la derecha, esta macro es muy útil para dejar listados únicos de lo que sea, verifícalo.
Sub FiltrarDatos12()
Dim oDoc As Object
Dim oSel As Object
Dim oDesFiltro As Object
Dim mCamposFiltro(0) As New com.sun.star.sheet.TableFilterField
Dim oDestino As Object
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
'Nos aseguramos de que sea un rango de celdas
If oSel.getImplementationName = "ScCellRangeObj" Then
'Creamos un nuevo descriptor de filtro vacio (True)
oDesFiltro = oSel.createFilterDescriptor(True)
'Establecemos los campos
mCamposFiltro(0).Field = 0
mCamposFiltro(0).Operator = com.sun.star.sheet.FilterOperator.NOT_EMPTY
'Establecemos el destino
oDestino = oSel.getCellByPosition(0,0).getCellAddress()
oDestino.Column = oSel.getRangeAddress().EndColumn + 2
'Establecemos las propiedades del filtro
oDesFiltro.ContainsHeader = False
oDesFiltro.SkipDuplicates = True
oDesFiltro.CopyOutputData = True
oDesFiltro.OutputPosition = oDestino
oDesFiltro.FilterFields = mCamposFiltro
'Y lo aplicamos
oSel.filter( oDesFiltro )
Else
MsgBox "No es un rango de celdas"
End If
End Sub
Hay diferencias importantes en comparación con los filtros en rango de datos; la primera es la forma de crear el descriptor de filtro, para un rango de celdas se usa el método createFilterDescriptor, al cual se le pasa el parámetro verdadero (True) si queremos que el nuevo descriptor este vacío y falso (False) si toma el existente en dicho rango de celdas, observa que también hemos establecido que este rango no tiene encabezados de campos (ContainsHeader), también cambia la propiedad para decirle que queremos copiar el resultado (CopyOutputData) en otra posición (homologada a partir de la versión 3.2, se usa la misma tanto en rangos de datos como en rangos de celda), y por ultimo, no refrescamos el rango, si no que aplicamos el filtro (filter), pasándole como parámetro el descriptor de filtro creado. Al igual que con los rangos de datos, si el destino contiene datos, serán reemplazados sin consultarte.
Veamos como usar las opciones del filtro especial, este tipo de filtro, nos permite establecer un rango como origen de los criterios para el filtro, lo que nos da la posibilidad de poder llegar a usar como criterios “todos” los campos de nuestra base de datos, dándonos absoluto control sobre los registros filtrados. Para demostrar el poder y versatilidad de esta opción, prepárate una buena tabla de datos, con al menos cinco campos y varias decenas de registros, si lo prefieres, puedes usar la hoja llamada “Películas”, presente en los ejemplos (descargar) que acompañan a estos apuntes.
El rango de nuestros datos es: “A1:G243”, establecemos un nombre a este rango; “Videoteca” si te parece, después, copia los títulos de los campos a la fila 250, selecciona el rango “A250:G255” y nómbrala como “Criterios”, por ultimo, selecciona la celda “A260” y la bautizamos como “Destino”, si es de tu agrado, todo lo anterior puedes hacerlo por código que ya sabes hacerlo, ¿verdad?. Recuerda que esta definición de nombres es en el menú Datos > Definir rango...
Nuestra tabla debe verse más o menos así.
La definición de criterios para el filtro especial, se puede hacer como lo hemos venido aprendiendo, por lo que no lo repetiremos aquí, concentrándonos en estudiar la forma de establecer estos criterios desde un rango de datos con nombre. Te sugiero, con esta macro en especifico, asignarle una combinación de teclas o un botón en alguna barra de herramientas con la finalidad de que podamos ejecutarla varias veces desde la interfaz del usuario e ir viendo en vivo y en directo los resultados que nos arrogue. Aquí la macro.
Sub FiltrarEspecial1()
Dim oDoc As Object
Dim oRangosBD As Object
Dim oVideoteca As Object
Dim oDestino As Object
Dim oCriterios As Object
Dim oDesFiltro As Object
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
'Nos aseguramos de que existen nuestros tres rangos
If oRangosBD.hasByName( "Videoteca" ) And oRangosBD.hasByName( "Criterios" ) And oRangosBD.hasByName( "Destino" ) Then
'Obtenemos una referencia a los rangos origen
oVideoteca = oRangosBD.getByName( "Videoteca" ).ReferredCells()
oCriterios = oRangosBD.getByName( "Criterios" ).ReferredCells()
oDestino = oRangosBD.getByName( "Destino" ).ReferredCells.getCellByPosition( 0,0 ).getCellAddress()
'Obtenemos el descriptor del filtro del rango de criterios a partir del rango de datos
oDesFiltro = oCriterios.createFilterDescriptorByObject( oVideoteca )
'Sin duplicados
oDesFiltro.SkipDuplicates = True
'Que pueda usar expresiones regulares
oDesFiltro.UseRegularExpressions = True
'Queremos el resultado en otra posición
oDesFiltro.CopyOutputData= True
'Le indicamos donde
oDesFiltro.OutputPosition = oDestino
'El rango contiene encabezados de campos
oDesFiltro.ContainsHeader = True
'Filtramos con las opciones seleccionadas
oVideoteca.filter( oDesFiltro )
Else
MsgBox "El rango de datos no existe"
End If
End Sub
Es importante notar que para el filtro especial usamos en nuevo método llamado createFilterDescriptorByObject, que se invoca desde el rango de criterios (oCriterios), pasándole como argumento, el rango de datos (oVideoteca), las restantes propiedades usadas en el ejemplo ya las hemos tratado.
Ahora, los criterios tienes que establecerlos en las celdas correspondientes, por ejemplo, para saber que películas tenemos de mi director favorito, usamos.
Ejecuta la macro y vemos el resultado.
Recuerda que la finalidad de establecer criterios y realizar filtros, es la de responder preguntas como por ejemplo, ¿cuantas películas tenemos del año 1974?, establece la condición y ejecuta la macro para responderla.
Nº | Titulo | Director | Genero | Año | País | Duración |
---|---|---|---|---|---|---|
1974 |
Si quieres establecer más de un criterio y lo haces en una misma fila, estas usando el operador de conjunción “Y” (And), como la respuesta a la pregunta; ¿cuantas películas tenemos, realizadas en Japón realizadas en 1980?
Nº | Titulo | Director | Genero | Año | País | Duración |
---|---|---|---|---|---|---|
1980 | Japón |
Si usas diferentes filas, estas usando el operador de disyunción “O” (Or), por ejemplo, para responder la pregunta; ¿cuantas películas tenemos de la India o del director Peter Greenaway?
Nº | Titulo | Director | Genero | Año | País | Duración |
---|---|---|---|---|---|---|
India | ||||||
Peter Greenaway |
Como declaramos en el descriptor de filtro, que se usarán expresiones regulares, puedes responder preguntas como; ¿cuantas películas empiezan con la letra “D”?
Nº | Titulo | Director | Genero | Año | País | Duración |
---|---|---|---|---|---|---|
d.* |
Como habrás notado, las posibilidades son enormes. Te queda de tarea, establecer las condiciones para responder la siguiente pregunta; ¿cuales películas duran entre 60 y 90 minutos?, la primer pista es; nota que estamos solicitando un rango especifico, por lo tanto, tienes que usar el operador “Y” (AND) para resolverlo, y ya casi te dije todo, recuerda que el rango de criterios es solo eso un rango y no estamos limitados a cambiar lo que queramos en este rango, con lo que ya te resolví la tarea, a trabajar.
Nº | Titulo | Director | Genero | Año | País | Duración |
---|---|---|---|---|---|---|
?? |
Subtotales
Los subtotales nos permiten obtener información de las áreas de datos, agrupar y realizar una operación determinada en un grupo de datos, en el siguiente ejemplo y continuando trabajando con nuestra base de datos de películas, obtenemos la cantidad de títulos por director.
Sub Subtotales1()
Dim oDoc As Object
Dim oRangosBD As Object
Dim oPeliculas As Object
Dim oRango As Object
Dim oDesSubTotal As Object
Dim mCamposSubTotal(0) As New com.sun.star.sheet.SubTotalColumn
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
'Nos aseguramos de que existe el rango
If oRangosBD.hasByName( "Peliculas" ) Then
'Obtenemos una referencia a los rangos origen
oPeliculas = oRangosBD.getByName( "Peliculas" )
'Referencia al rango de celdas origen
oRango = oPeliculas.ReferredCells()
'Creamos un nuevo subtotal
oDesSubTotal = oRango.createSubTotalDescriptor( True )
'Que ordene de forma ascendente el grupo seleccionado
oDesSubTotal.EnableSort = True
oDesSubTotal.SortAscending = True
'La columna en la que se usara la función especificada
mCamposSubTotal(0).Column = 1
mCamposSubTotal(0).Function = com.sun.star.sheet.GeneralFunction.COUNT
'Agregamos la operación, al grupo deseado
oDesSubTotal.addNew( mCamposSubTotal,2 )
'Aplicamos el subtotal
oRango.applySubTotals( oDesSubTotal, True )
End If
End Sub
El área de datos (DataBaseRanges), solo la utilizamos para acceder (ReferredCells) al rango de celdas origen, si al método del rango de celdas para crear el descriptor del subtotal (createSubTotalDescriptor), se le pasa como argumento un valor verdadero (True), te creara un nuevo descriptor vacío, si es falso (False), tomará el existente, si lo hay, del rango seleccionado. Es importante que establezcas que ordene el grupo, si es ascendente o descendente, queda a tu criterio y necesidades, pero si no ordenas el grupo, y dependiendo de como estén tus datos origen, te puede dar resultados incorrectos. En la matriz de columnas del subtotal (com.sun.star.sheet.SubTotalColumn), establecemos el campo (Column) en el cual queremos hacer una operación, determinada por la propiedad función (Function), a su vez, basada en la enumeración com.sun.star.sheet.GeneralFunction, que puede tomar los siguientes valores.
com.sun.star.sheet.GeneralFunction | Valor | Valor en Interfaz |
---|---|---|
com.sun.star.sheet.GeneralFunction.NONE | 0 | Ninguna |
com.sun.star.sheet.GeneralFunction.AUTO | 1 | Automático |
com.sun.star.sheet.GeneralFunction.SUM | 2 | Suma |
com.sun.star.sheet.GeneralFunction.COUNT | 3 | Cuenta |
com.sun.star.sheet.GeneralFunction.AVERAGE | 4 | Promedio |
com.sun.star.sheet.GeneralFunction.MAX | 5 | Máximo |
com.sun.star.sheet.GeneralFunction.MIN | 6 | Mínimo |
com.sun.star.sheet.GeneralFunction.PRODUCT | 7 | Producto |
com.sun.star.sheet.GeneralFunction.COUNTNUMS | 8 | Cuenta solo números |
com.sun.star.sheet.GeneralFunction.STDEV | 9 | Desviación estándar (Muestra) |
com.sun.star.sheet.GeneralFunction.STDEVP | 10 | Desviación estándar (Población) |
com.sun.star.sheet.GeneralFunction.VAR | 11 | Varianza (Muestra) |
com.sun.star.sheet.GeneralFunction.VARP | 12 | Varianza (Población) |
Al añadir los campos (addNew) al descriptor de subtotal, le pasamos como parámetros, la matriz de campos de columna del subtotal donde hemos establecido que campos y que operación haremos con ellos, ahí mismo, con el segundo parámetro, le indicamos por que campo queremos agrupar, por último, aplicamos (applySubTotals) los subtotales, cuyos parámetros son; el descriptor de subtotal y un valor booleano indicándole si deseamos reemplazar el subtotal actual (True), si lo hay, o agregamos al existente (False), usa con cuidado este parámetro, pues si agregas criterios “inconsistentes”, el resultado puede ser impreciso y desastroso visualmente hablando.
La siguiente macro, borra cualquier subtotal existente en el rango del área de datos.
Sub Subtotales2()
Dim oDoc As Object
Dim oRangosBD As Object
Dim oPeliculas As Object
Dim oRango As Object
oDoc = ThisComponent
oRangosBD = oDoc.DataBaseRanges()
If oRangosBD.hasByName( "Peliculas" ) Then
oPeliculas = oRangosBD.getByName( "Peliculas" )
oRango = oPeliculas.ReferredCells()
'Quitamos el subtotal
oRango.removeSubTotals()
End If
End Sub
Como se muestra en el siguiente ejemplo, no es indispensable usar un área de datos para hacer uso de los subtotales, al ser métodos implementados en rangos de celda, podemos invocarlos desde cualquiera de estos. La siguiente macro, nos da el total de títulos por genero y suma la cantidad de minutos del mismo.
Sub Subtotales3()
Dim oDoc As Object
Dim oSel As Object
Dim oDesSubTotal As Object
Dim mCamposSubTotal(1) As New com.sun.star.sheet.SubTotalColumn
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
'Si es una sola celda o un rango de celdas
If oSel.getImplementationName = "ScCellRangeObj" Or oSel.getImplementationName = "ScCellObj" Then
oSel = oSel.getSpreadSheet.createCursorByRange(oSel)
'Expandimos a la región actual
oSel.collapseToCurrentRegion()
'Creamos el descriptor a partir de la selección
oDesSubTotal = oSel.createSubTotalDescriptor( True )
oDesSubTotal.EnableSort = True
oDesSubTotal.SortAscending = True
'Columna de titulos
mCamposSubTotal(0).Column = 1
mCamposSubTotal(0).Function = com.sun.star.sheet.GeneralFunction.COUNT
'Columna de minutos
mCamposSubTotal(1).Column = 6
mCamposSubTotal(1).Function = com.sun.star.sheet.GeneralFunction.SUM
'Aplicamos al genero
oDesSubTotal.addNew( mCamposSubTotal,3 )
oSel.applySubTotals( oDesSubTotal, True )
Else
MsgBox "No es un rango de celdas"
End If
End Sub
El siguiente ejemplo, nos muestra cuantos títulos tenemos por país, y después otro subtotal nos indica cuantos títulos por genero, “dentro” de cada país tenemos.
Sub Subtotales4()
Dim oDoc As Object
Dim oSel As Object
Dim oDesSubTotal As Object
Dim mCamposSubTotal(0) As New com.sun.star.sheet.SubTotalColumn
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
'Si es una sola celda o un rango de celdas
If oSel.getImplementationName = "ScCellRangeObj" Or oSel.getImplementationName = "ScCellObj" Then
oSel = oSel.getSpreadSheet.createCursorByRange(oSel)
oSel.collapseToCurrentRegion()
oDesSubTotal = oSel.createSubTotalDescriptor( True )
oDesSubTotal.EnableSort = True
oDesSubTotal.SortAscending = True
'Columna de títulos
mCamposSubTotal(0).Column = 1
mCamposSubTotal(0).Function = com.sun.star.sheet.GeneralFunction.COUNT
'Aplicamos al país
oDesSubTotal.addNew( mCamposSubTotal,5 )
'Columna de títulos
mCamposSubTotal(0).Column = 1
mCamposSubTotal(0).Function = com.sun.star.sheet.GeneralFunction.COUNT
'Aplicamos al genero
oDesSubTotal.addNew( mCamposSubTotal,3 )
oSel.applySubTotals( oDesSubTotal, True )
Else
MsgBox "No es un rango de celdas"
End If
End Sub
Observa, que hemos establecido la matriz de campos dos veces y usado el método addNew también dos veces para lograr el resultado deseado. Solo puedes sacar subtotales por hasta un máximo de tres grupos. El orden en que establezcas y agregues los campos, será el orden de precedencia de los subtotales. La obtención de subtotales, tiene un valor agregado bastante útil, al realizar la operación establecida en algún campo, la herramienta hace una comparación “exacta” de cada valor, por lo que te podrás dar cuenta si tienes campos con, por ejemplo, espacios vacíos de más, al principio o al final de el, o en el caso de tener valores de campo muy similares, si un registro tiene solo un error de “dedo”, con los subtotales te podrás dar cuenta de forma visual muy fácilmente, sobre todo cuando haces uso de la función cuenta, revisa los registros que solo tengan un solo registro, por supuesto, tu debes determinar si es correcto o no. Para ejemplificar esto, si estas usando la misma base de datos que yo, saca un subtotal por genero, busca y observa los géneros “Comedia” y “Drama”, y cuéntame cual es el error. Esa es tu tarea que no es mucha.
Validando datos
A estas alturas ya te habrás dado cuenta de la importancia de validar la información que el usuario le proporciona a un programa, una gran cantidad de programas, fallan en este tema de, creo yo, alta prioridad. Calc cuenta con una herramienta llamada Validez, presente en el menú Datos, que nos permite establecer criterios en celdas para la captura de información por parte del usuario y limitarlo en la medida de lo posible, a capturar dados válidos en el programa, informando, si así lo deseas, con cuadros de mensaje para ayudarle a introducir la información correctamente.
Veamos como establecer estas opciones por código. En el siguiente ejemplo, establecemos que las celdas seleccionadas, solo puedan aceptar números enteros del 1 al 12, es decir, un entero correspondiente a un mes del año.
Sub ValidarDatos1()
Dim oDoc As Object
Dim oSel As Object
Dim oValidacion As Object
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
'Validamos que sea una celda o un rango de celdas
If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
'Obtenemos la estructura validación
oValidacion = oSel.getPropertyValue("Validation")
'Establecemos sus propiedades
With oValidacion
'El tipo de validación
.Type = com.sun.star.sheet.ValidationType.WHOLE
'El operador de la validación
.setOperator ( com.sun.star.sheet.ConditionOperator.BETWEEN )
'Establecemos la primer condición
.setFormula1 ( "1" )
'Establecemos la segunda
.setFormula2 ( "12" )
'Que ignore las celdas en blanco
.IgnoreBlankCells = True
'Que muestre un mensaje al seleccionar la celda
.ShowInputMessage = True
'El titulo del mensaje
.InputTitle = "Introduce el mes del año"
'El mensaje
.InputMessage = "Captura un número entre 1 y 12"
'Que muestre un mensaje si la condición no se cumple
.ShowErrorMessage = True
'El estilo del mensaje de alerta
.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
'El titulo del mensaje de error
.ErrorTitle = "Dato erroneo"
'El mensaje de error
.ErrorMessage = "El valor introducido no es un número entre 1 y 12"
End With
'Reinsertamos la propiedad para que surtan efecto los cambios
oSel.setPropertyValue("Validation", oValidacion)
End If
End Sub
Observa como estamos obteniendo la estructura de validación (getPropertyValue), pasándole como argumento, el nombre de la propiedad que nos interesa. Las restantes propiedades son: el tipo (Type), se refiere al tipo de validación que haremos y esta condicionado por la enumeración com.sun.star.sheet.ValidationType, cuyos posibles valores son.
com.sun.star.sheet.ValidationType | Valor | Valor en Interfaz |
---|---|---|
com.sun.star.sheet.ValidationType.ANY | 0 | Cualquier valor |
com.sun.star.sheet.ValidationType.WHOLE | 1 | Entero |
com.sun.star.sheet.ValidationType.DECIMAL | 2 | Decimal |
com.sun.star.sheet.ValidationType.DATE | 3 | Fecha |
com.sun.star.sheet.ValidationType.TIME | 4 | Hora |
com.sun.star.sheet.ValidationType.TEXT_LEN | 5 | Longitud de texto |
com.sun.star.sheet.ValidationType.LIST | 6 | Lista |
com.sun.star.sheet.ValidationType.CUSTOM | 7 | Formula |
Después, establecemos el operador (setOperator), para evaluar la condición, esta propiedad puede tomar los siguientes valores.
com.sun.star.sheet.ConditionOperator | Valor | Valor en Interfaz |
---|---|---|
com.sun.star.sheet.ConditionOperator.NONE | 0 | Ninguna |
com.sun.star.sheet.ConditionOperator.EQUAL | 1 | Igual |
com.sun.star.sheet.ConditionOperator.NOT_EQUAL | 2 | Distinta de |
com.sun.star.sheet.ConditionOperator.GREATER | 3 | Mayor que |
com.sun.star.sheet.ConditionOperator.GREATER_EQUAL | 4 | Mayor o igual |
com.sun.star.sheet.ConditionOperator.LESS | 5 | Menor que |
com.sun.star.sheet.ConditionOperator.LESS_EQUAL | 6 | Menor o igual |
com.sun.star.sheet.ConditionOperator.BETWEEN | 7 | Entre |
com.sun.star.sheet.ConditionOperator.NOT_BETWEEN | 8 | No entre |
com.sun.star.sheet.ConditionOperator.FORMULA | 9 | Formula |
Dependiendo de que operador selecciones, puede que necesites establecer el primer valor de comparación (setFormula1), y también el segundo (setFormula2), comúnmente los operadores “entre” (BETWEEN), y “no entre” (NOT_BETWEEN), son los que requieren los dos valores. Podemos determinar si la validación ignora o no, las celdas en blanco (IgnoreBlankCells), pero debes de saber que esta propiedad no determina el validar celdas vacía o no, si no que trabaja en conjunto la utilidad Detective del menú Herramientas, para determinar si una celda vacía se considera error o no. Podemos mostrar un mensaje (ShowInputMessage), cuando el usuario seleccione una celda, establecer su titulo (InputTitle), y el mensaje que verá (InputMessage), este texto orienta al usuario sobre los valores a introducir. En caso de que el valor capturado por el usuario, no satisfaga las condiciones de la validación, podemos mostrar un mensaje de error (ShowErrorMessage), de determinado estilo (ErrorAlertStyle), este estilo, determinará la acción a tomar con el valor capturado, este estilo esta determinado por los siguientes valores.
com.sun.star.sheet.ValidationAlertStyle | Valor | Valor en Interfaz |
---|---|---|
com.sun.star.sheet.ValidationAlertStyle.STOP | 0 | Stop |
com.sun.star.sheet.ValidationAlertStyle.WARNING | 1 | Advertencia |
com.sun.star.sheet.ValidationAlertStyle.INFO | 2 | Información |
com.sun.star.sheet.ValidationAlertStyle.MACRO | 3 | Macro |
Si estableces el valor en “stop” (STOP), se mostrara al usuario un mensaje (ErrorMessage) con un titulo (ErrorTitle) para informarle del error, al aceptar, la celda regresará al valor inmediato anterior, en los casos de “advertencia” (WARNING) e “información” (INFO), quedará a criterio del usuario si acepta o no el nuevo valor aun y cuando no cumpla la condición y en el caso de la opción “macro” (MACRO), puedes escoger una macro a ejecutar, para, por ejemplo, mostrar un mensaje más elaborado o realizar una operación más compleja. Por ultimo, es importante, “reinsertar” (setPropertyValue) la propiedad al objeto para que los cambios surtan efecto.
Para quitar una validación, solo establece el tipo (Type) en cualquier valor (ANY) y deshabilita el mensaje de entrada (ShowInputMessage).
Sub ValidarDatos2()
Dim oDoc As Object
Dim oSel As Object
Dim oValidacion As Object
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
'Validamos que sea una celda o un rango de celdas
If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
'Obtenemos la estructura validación
oValidacion = oSel.getPropertyValue("Validation")
'El tipo de validación
oValidacion.Type = com.sun.star.sheet.ValidationType.ANY
'Que muestre un mensaje al seleccionar la celda
oValidacion.ShowInputMessage = False
'Reinsertamos la propiedad para que surtan efecto los cambios
oSel.setPropertyValue("Validation", oValidacion)
End If
End Sub
El tipo decimal, te permite capturar números con decimales.
Sub ValidarDatos3()
Dim oDoc As Object
Dim oSel As Object
Dim oValidacion As Object
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
oValidacion = oSel.getPropertyValue("Validation")
With oValidacion
.Type = com.sun.star.sheet.ValidationType.DECIMAL
.setOperator ( com.sun.star.sheet.ConditionOperator.BETWEEN )
.setFormula1 ( "0" )
.setFormula2 ( "10" )
.IgnoreBlankCells = True
.ShowInputMessage = True
.InputTitle = "Introduce la calificacion"
.InputMessage = "Puedes usar decimales"
.ShowErrorMessage = True
.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.INFO
.ErrorTitle = "Dato erroneo"
.ErrorMessage = "El valor introducido no es válido"
End With
'Reinsertamos la propiedad para que surtan efecto los cambios
oSel.setPropertyValue("Validation", oValidacion)
End If
End Sub
En el siguiente ejemplo validamos que no sea una fecha futura, nota el uso de la función incorporada de Calc.
Sub ValidarDatos4()
Dim oDoc As Object
Dim oSel As Object
Dim oValidacion As Object
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
oValidacion = oSel.getPropertyValue("Validation")
With oValidacion
.Type = com.sun.star.sheet.ValidationType.DATE
.setOperator ( com.sun.star.sheet.ConditionOperator.LESS_EQUAL )
'Usamos una formula como condición
.setFormula1 ( "TODAY()" )
.IgnoreBlankCells = True
.ShowInputMessage = True
.InputTitle = "Fecha de Nacimiento"
.InputMessage = "La fecha no puede ser futura"
.ShowErrorMessage = True
.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.WARNING
.ErrorTitle = "Dato erroneo"
.ErrorMessage = "El valor introducido no es válido"
End With
oSel.setPropertyValue("Validation", oValidacion)
End If
End Sub
Ahora, solo puede capturar una hora que no sea entre 1 p.m. 3 p.m.
Sub ValidarDatos5()
Dim oDoc As Object
Dim oSel As Object
Dim oValidacion As Object
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
oValidacion = oSel.getPropertyValue("Validation")
With oValidacion
.Type = com.sun.star.sheet.ValidationType.TIME
.setOperator ( com.sun.star.sheet.ConditionOperator.NOT_BETWEEN )
'Introducir una hora que no sea entre 1 y 3 p.m.
.setFormula1 ( "TIME(13;0;0" )
.setFormula2 ( "TIME(15;0;0" )
.IgnoreBlankCells = True
.ShowInputMessage = True
.InputTitle = "Hora de salida"
.InputMessage = "La hora de salir"
.ShowErrorMessage = True
.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
.ErrorTitle = "Dato erróneo"
.ErrorMessage = "El valor introducido no es válido"
End With
oSel.setPropertyValue("Validation", oValidacion)
End If
End Sub
Observa que estamos haciendo uso de la función de Calc, TIEMPO (TIME), que te devuelve el número de serie de la hora pasada, esto es necesario para establecer los limites correctamente. En el siguiente ejemplo, establecemos que los valores los tome desde un rango de celdas, observa que las referencias al rango son absolutas.
Sub ValidarDatos6()
Dim oDoc As Object
Dim oSel As Object
Dim oValidacion As Object
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
oValidacion = oSel.getPropertyValue("Validation")
With oValidacion
.Type = com.sun.star.sheet.ValidationType.LIST
'Introducir valores desde un rango de celdas
.setFormula1 ( "$I$2:$I$8" )
.ShowErrorMessage = True
.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
.ErrorTitle = "Dato erróneo"
.ErrorMessage = "El valor introducido no es válido"
End With
oSel.setPropertyValue("Validation", oValidacion)
End If
End Sub
También podemos establecer el rango desde un rango de celdas con nombre.
Sub ValidarDatos7()
Dim oDoc As Object
Dim oSel As Object
Dim oValidacion As Object
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
oValidacion = oSel.getPropertyValue("Validation")
With oValidacion
.Type = com.sun.star.sheet.ValidationType.LIST
'Introducir valores desde un rango de celdas
.setFormula1 ( "valores" )
.ShowList = 2
.ShowErrorMessage = True
.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
.ErrorTitle = "Dato erróneo"
.ErrorMessage = "El valor introducido no es válido"
End With
oSel.setPropertyValue("Validation", oValidacion)
End If
End Sub
Recuerda que los rangos con nombre los establecemos, en la interfaz del usuario, desde el cuadro de nombres de la barra de formulas, o por código, como hemos aprendido en este libro, aunque también puedes usar un nombre de área de datos definida en el menú Datos > Definir..., de hecho, puedes usar cualquier texto o formula que te devuelva un rango de celdas válido, si el rango de celdas tiene más de una columna, solo se usara la primer columna.
Observa que hemos usado una nueva propiedad (ShowList), cuando el tipo de la validación esta establecida en “lista” (LIST), podemos determinar si mostramos la flecha de lista de selección, el valor 2 determina que se muestre con los valores ordenados de forma ascendente, el valor 1 que se muestre pero que no ordene los valores, que los muestre tal y como están en el origen, y el valor 0 que no se muestre la lista de selección, algo no muy recomendable cuando se usa una lista. Estos valores corresponden a.
com.sun.star.sheet.TableValidationVisibility | Valor | Valor en Interfaz |
---|---|---|
com.sun.star.sheet.TableValidationVisibility.INVISIBLE | 0 | No mostrar lista |
com.sun.star.sheet.TableValidationVisibility.UNSORTED | 1 | Mostrar desordenada |
com.sun.star.sheet.TableValidationVisibility.SORTEDASCENDING | 2 | Mostrar ordenada ascendente |
En el siguiente ejemplo, establecemos el origen de celdas con formulas de Calc, esta formula debe estar bien construida, si no, no te dará el rango correcto.
Sub ValidarDatos8()
Dim oDoc As Object
Dim oSel As Object
Dim oValidacion As Object
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
oValidacion = oSel.getPropertyValue("Validation")
With oValidacion
.Type = com.sun.star.sheet.ValidationType.LIST
'Introducir valores desde un rango de celdas
.setFormula1 ( "INDIRECT(ADDRESS(1;1)&"":""&ADDRESS(10;1))" )
.ShowList = 2
.ShowErrorMessage = True
.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
.ErrorTitle = "Dato erroneo"
.ErrorMessage = "El valor introducido no es válido"
End With
oSel.setPropertyValue("Validation", oValidacion)
End If
End Sub
Recuerda que la función DIRECCION (ADDRESS), nos devuelve una referencia en forma de texto, de acuerdo a los parámetros pasados, después, la función INDIRECTO (INDIRECT), nos devuelve, de una cadena de texto que tenga un rango de celda válido, la referencia a dicha celda. Como a la función DIRECCION se le pueda establecer entre sus parámetros el nombre de una hoja, te queda de tarea modificar la macro anterior para que el rango de celdas de la validación, este en una hoja diferente. El poder de hacerlo con funciones o desde código, es que la lista de validación la puedes actualizar dinámicamente, por ejemplo, una lista de clientes que va creciendo confirme vamos agregando registros o cualquier otro listado que vaya creciendo en sentido vertical.
Otra posibilidad, es introducir una lista fija de valores, o semifija, pues desde código la podemos actualizar siempre que queramos, veamos como.
Sub ValidarDatos9()
Dim oDoc As Object
Dim oSel As Object
Dim oValidacion As Object
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
oValidacion = oSel.getPropertyValue("Validation")
With oValidacion
.Type = com.sun.star.sheet.ValidationType.LIST
'Introducir valores fijos
.setFormula1 ( "VALOR1;VALOR2;VALOR3" )
.ShowList = 2
.ShowErrorMessage = True
.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
.ErrorTitle = "Dato erróneo"
.ErrorMessage = "El valor introducido no es válido"
End With
oSel.setPropertyValue("Validation", oValidacion)
End If
End Sub
Ve a la interfaz del usuario y observa como “aparentemente”, si agrego los valores, pero observa que están en minúsculas, cuando nosotros los agregamos en mayúsculas, verifica desde la interfaz de usuario, desde Datos > Validez..., que esta forma de introducir los datos fijos, la detecta como si fuera un intervalo de celdas, lo cual es incorrecto, para que realmente la detecte como una lista “fija” de valores, tienes que pasarle cada valor como una cadena, como en el siguiente ejemplo.
Sub ValidarDatos10()
Dim oDoc As Object
Dim oSel As Object
Dim oValidacion As Object
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
oValidacion = oSel.getPropertyValue("Validation")
With oValidacion
.Type = com.sun.star.sheet.ValidationType.LIST
'Introducir valores fijos
.setFormula1 ( """VALOR1"""&";"&"""VALOR2"""&";"&"""VALOR3""" )
.ShowList = 2
.ShowErrorMessage = True
.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
.ErrorTitle = "Dato erroneo"
.ErrorMessage = "El valor introducido no es válido"
End With
oSel.setPropertyValue("Validation", oValidacion)
End If
End Sub
Pero ve que cantidad de comillas, ¿y si son muchos valores?, podemos hacer una función que agregue las comillas por nosotros, como en el siguiente ejemplo.
Sub ValidarDatos11
Dim sTmp As String
Dim m
Dim oDoc As Object
Dim oSel As Object
Dim oValidacion As Object
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
oValidacion = oSel.getPropertyValue("Validation")
With oValidacion
'Valores a agregar
m = Array("Uno","Dos","Tres","Cuatro","Cinco")
'Los juntamos
sTmp = JuntarEnLista(m)
.Type = com.sun.star.sheet.ValidationType.LIST
.ShowList = 2
.setFormula1( sTmp )
End With
oSel.setPropertyValue("Validation", oValidacion)
End If
End Sub
'Toma una matriz y le agrega comillas a cada elemento
'Devuelve una cadena con los elementos, separados por ";"
Function JuntarEnLista( ByVal m ) As String
Dim co1 As Long
For co1 = LBound(m) To UBound(m)
m(co1) = """" & m(co1) & """"
Next
JuntarEnLista = Join( m, ";" )
End Function
Ahora si, es un poco más fácil agregar valores a una lista. El siguiente ejemplo te limita la entrada a una palabra de entre 5 y 10 letras, no te acepta solo números, solo letras o alfanuméricas.
Sub ValidarDatos12()
Dim oDoc As Object
Dim oSel As Object
Dim oValidacion As Object
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
If oSel.getImplementationName = "ScCellObj" Or oSel.getImplementationName = "ScCellRangeObj" Then
oValidacion = oSel.getPropertyValue("Validation")
With oValidacion
.Type = com.sun.star.sheet.ValidationType.TEXT_LEN
.setOperator ( com.sun.star.sheet.ConditionOperator.BETWEEN )
.setFormula1 ( "5" )
.setFormula2 ( "10" )
.IgnoreBlankCells = True
.ShowErrorMessage = True
.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
.ErrorTitle = "Longitud errónea"
.ErrorMessage = "La palabra debe ser de entre 5 y 10 caracteres"
End With
'Reinsertamos la propiedad para que surtan efecto los cambios
oSel.setPropertyValue("Validation", oValidacion)
End If
End Sub
Tanto en la interfaz del usuario y aun más desde código, la validación de datos es un tema central en la programación, no lo tomes como un tema menor, es preferible agregar unas cuantas líneas más de código a correr el riesgo de que un dato inconsistente nos devuelva información inconsistente. Tienes instrucciones limitadas pero suficientes para evitarlo y tienes algo ilimitado; imaginación, inteligencia, pero sobre todo, sentido común, explótalos.
Agrupando datos
Cuando se maneja una gran cantidad de información, encontrar una manera simple y rápida de agrupar datos, puede ser la diferencia entre eficiencia e ineficiencia, los filtros, el ordenar y los subtotales, son herramientas que pueden apoyarnos. Cuando simplemente necesitemos agrupar por un rango de columnas o filas (que no es más que ocultarlas y mostrarlas de acuerdo a nuestras necesidades) podemos probar lo siguiente.
Sub Agrupar1()
Dim oDoc As Object
Dim oHojaActiva As Object
Dim oRango As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oHojaActiva = oDoc.getCurrentController.getActiveSheet()
'Establecemos las propiedades del grupo
With oRango
'La hoja donde se creará
.Sheet = oHojaActiva.getRangeAddress.Sheet
'La columna de inicio
.StartColumn = 0
'La columan de fin
.EndColumn = 4
End With
'Creamos el grupo, 0 = por columnas
oHojaActiva.group( oRango, 0 )
End Sub
La macro anterior nos agrupará las columnas 1 a 5 en la interfaz del usuario, es muy importante que validez que la hoja y el rango de columnas establecidas, estén dentro de rangos válidos, por ejemplo, que el índice de hoja exista, si no, puedes provocar una caída de la aplicación, estamos verificando el ¿por que? de esto.
Observa como al agrupar (group), le pasamos una estructura de dirección de un rango (CellRangeAddress), vista muchas veces en este libro y como segundo argumento, si queremos que se agrupe por columnas (0) o por filas (1).
Ahora, agrupamos las primeras diez filas de la hoja activa.
Sub Agrupar2()
Dim oDoc As Object
Dim oHojaActiva As Object
Dim oRango As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oHojaActiva = oDoc.getCurrentController.getActiveSheet()
With oRango
.Sheet = oHojaActiva.getRangeAddress.Sheet
'Ahora establecemos el rango de filas
.StartRow = 0
.EndRow = 9
End With
'Y agrupamos, 1 = por filas
oHojaActiva.group( oRango, 1 )
End Sub
Por supuesto puedes establecer con la misma estructura, tanto las columnas como las filas como en el siguiente ejemplo.
Sub Agrupar3()
Dim oDoc As Object
Dim oHojaActiva As Object
Dim oRango As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oHojaActiva = oDoc.getCurrentController.getActiveSheet()
With oRango
.Sheet = oHojaActiva.getRangeAddress.Sheet
.StartColumn = 9
.EndColumn = 19
.StartRow = 9
.EndRow = 19
End With
'Agrupamos por columnas 10 a la 20
oHojaActiva.group( oRango, 0 )
'Agrupamos por filas 10 a 20
oHojaActiva.group( oRango, 1 )
End Sub
Desagrupar (ungroup) es trivial, usa los mismos argumentos que para agrupar.
Sub Agrupar4()
Dim oDoc As Object
Dim oHojaActiva As Object
Dim oRango As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oHojaActiva = oDoc.getCurrentController.getActiveSheet()
With oRango
.Sheet = oHojaActiva.getRangeAddress.Sheet
.StartColumn = 0
.EndColumn = 4
.StartRow = 0
.EndRow = 9
End With
'Deagrupamos filas y columnas
oHojaActiva.ungroup( oRango, 0 )
oHojaActiva.ungroup( oRango, 1 )
End Sub
El rango usado para desagrupar (ungroup), no tiene que coincidir exactamente, en tamaño al rango original de agrupamiento, es suficiente con que este “dentro” del rango con que se agrupó, antes de comprobarlo, desde la hoja donde estés haciendo estas pruebas, ve al menú Datos > Agrupar y Esquema > Eliminar, lo que borrara cualquier grupo creado, después, ejecuta la primer macro siguiente e inmediatamente después, la segunda.
Sub Agrupar5()
Dim oDoc As Object
Dim oHojaActiva As Object
Dim oRango As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oHojaActiva = oDoc.getCurrentController.getActiveSheet()
With oRango
.Sheet = oHojaActiva.getRangeAddress.Sheet
.StartColumn = 0
.EndColumn = 9
.StartRow = 0
.EndRow = 19
End With
'Agrupamos filas (1-20) y columnas (1-10)
oHojaActiva.group( oRango, 0 )
oHojaActiva.group( oRango, 1 )
End Sub
Sub Agrupar6()
Dim oDoc As Object
Dim oHojaActiva As Object
Dim oRango As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oHojaActiva = oDoc.getCurrentController.getActiveSheet()
With oRango
.Sheet = oHojaActiva.getRangeAddress.Sheet
'Solo indicamos la primer columna y fila
.StartColumn = 0
.EndColumn = 0
.StartRow = 0
.EndRow = 0
End With
'Desagrupamos filas y columnas
oHojaActiva.ungroup( oRango, 0 )
oHojaActiva.ungroup( oRango, 1 )
End Sub
Observa como agrupamos por un rango y desagrupamos por otro, donde solo nos tenemos que asegurar que, el rango a desagrupar, este “dentro” del rango agrupado.
Cuando eliminas los grupos desde la interfaz del usuario, si el grupo esta contraído, las columnas o filas ocultas, se mostrarán inmediatamente, no sucede lo mismo cuando lo haces por código, vuelve a realizar la prueba anterior, pero asegúrate, de que, antes de desagrupar, contrae el grupo para ocultar su contenido, ahora sí, ejecuta la macro para desagrupar, notaras que el rango usado seguirá oculto, para evitar esto, antes de desagrupar, asegúrate de mostrar el detalle del rango como en el siguiente ejemplo.
Sub Agrupar7()
Dim oDoc As Object
Dim oHojaActiva As Object
Dim oRango As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oHojaActiva = oDoc.getCurrentController.getActiveSheet()
With oRango
.Sheet = oHojaActiva.getRangeAddress.Sheet
'Solo indicamos la primer columna y fila
.StartColumn = 0
.EndColumn = 9
.StartRow = 0
.EndRow = 19
End With
'Expandimos el grupo contenido en el rango
oHojaActiva.showDetail( oRango )
'Desagrupamos filas y columnas
oHojaActiva.ungroup( oRango, 0 )
oHojaActiva.ungroup( oRango, 1 )
End Sub
El ejemplo anterior funcionará, siempre y cuando, el rango agrupado (group), corresponda “exactamente” con el rango mostrado (showDetail) y con el rango desagrupado (ungroup), lo cual, en ocasiones, podría no ser tan fácil de conocer. Si lo que quieres es eliminar cualquier grupo existente en una hoja, usamos.
Sub Agrupar8()
Dim oDoc As Object
Dim oHojaActiva As Object
Dim oRango As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oHojaActiva = oDoc.getCurrentController.getActiveSheet()
'Eliminamos cualquier grupo de la hoja
oHojaActiva.clearOutline()
End Sub
Con lo cual, es mucho más sencillo, eliminar cualquier grupo y volver a crear el necesario. Cuando agregas grupos, dependiendo de si exista o no previamente uno en el rango pasado, sucederá cualquiera de las siguientes acciones; si el rango ya contiene un grupo, se creará un nuevo nivel de agrupamiento, puedes crear hasta ocho niveles de ellos, si el rango no contiene un grupo, este, se agregará al mismo nivel, veámoslo con ejemplos, en el primero comprobamos que se agregan los grupos en niveles.
Sub Agrupar9()
Dim oDoc As Object
Dim oHojaActiva As Object
Dim oRango As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oHojaActiva = oDoc.getCurrentController.getActiveSheet()
oHojaActiva.clearOutline()
With oRango
.Sheet = oHojaActiva.getRangeAddress.Sheet
.StartColumn = 0
.EndColumn = 1
End With
'Agrupamos las columnas 1-2
oHojaActiva.group( oRango, 0 )
With oRango
.StartColumn = 0
.EndColumn = 3
End With
'Agrupamos las columnas 1-4
oHojaActiva.group( oRango, 0 )
With oRango
.StartColumn = 0
.EndColumn = 5
End With
'Agrupamos las columnas 1-6
oHojaActiva.group( oRango, 0 )
End Sub
Ahora veamos como agregar al mismo nivel.
Sub Agrupar10()
Dim oDoc As Object
Dim oHojaActiva As Object
Dim oRango As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oHojaActiva = oDoc.getCurrentController.getActiveSheet()
oHojaActiva.clearOutline()
With oRango
.Sheet = oHojaActiva.getRangeAddress.Sheet
.StartRow = 0
.EndRow = 3
End With
'Agrupamos la fila 1 a 4
oHojaActiva.group( oRango, 1 )
With oRango
.StartRow = 5
.EndRow = 8
End With
'Agrupamos la fila 4 a 7
oHojaActiva.group( oRango, 1 )
With oRango
.StartRow = 10
.EndRow = 13
End With
'Agrupamos la fila 9 a 12
oHojaActiva.group( oRango, 1 )
End Sub
Y lo comprobamos:
Puedes crear un grupo y ocultarlo inmediatamente, como en.
Sub Agrupar11()
Dim oDoc As Object
Dim oHojaActiva As Object
Dim oRango As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oHojaActiva = oDoc.getCurrentController.getActiveSheet()
With oRango
.Sheet = oHojaActiva.getRangeAddress.Sheet
.StartColumn = 0
.EndColumn = 9
End With
oHojaActiva.group( oRango, 0 )
'Ocultamos el grupo contenido en el rango
oHojaActiva.hideDetail( oRango )
End Sub
A diferencia de mostrar un grupo (showDetail), cuando lo ocultas (hideDetail), el rango no necesariamente debe ser del mismo tamaño, con que apunte a cualquier celda dentro del rango a ocultar, funcionará. También tienes la posibilidad de mostrar cualquier nivel de agrupamiento, los demás niveles hacia arriba, si los hay, se cerraran.
Sub Agrupar12()
Dim oDoc As Object
Dim oHojaActiva As Object
Dim oRango As New com.sun.star.table.CellRangeAddress
oDoc = ThisComponent
oHojaActiva = oDoc.getCurrentController.getActiveSheet()
With oRango
.Sheet = oHojaActiva.getRangeAddress.Sheet
.StartRow = 0
.EndRow = 4
End With
oHojaActiva.group( oRango, 1 )
With oRango
.StartRow = 0
.EndRow = 9
End With
oHojaActiva.group( oRango, 1 )
With oRango
.StartRow = 0
.EndRow = 14
End With
oHojaActiva.group( oRango, 1 )
With oRango
.StartRow = 0
.EndRow = 19
End With
oHojaActiva.group( oRango, 1 )
'Mostramos el nivel 2
oHojaActiva.showLevel( 2, 1 )
End Sub
El método para mostrar un nivel especifico (showLevel), solo usa dos parámetros, el nivel a mostrar como primero y como segundo parámetro, si el nivel a mostrar esta en columnas (0) o en filas (1). Para terminar este tema, veamos un ejemplo practico bastante útil, para que veas su uso, procura llamarla, teniendo el cursor dentro de un rango de celdas que tenga al menos dos pantallas de datos en sentido vertical, es decir, de filas, si tienes más filas, es mucho mejor pues se paginara más.
Sub Agrupar13()
Dim oDoc As Object
Dim oHojaActiva As Object
Dim oSel As Object
Dim oCursor As Object
Dim iNumFilPag As Integer
Dim oRango As New com.sun.star.table.CellRangeAddress
Dim co1 As Long
oDoc = ThisComponent
oHojaActiva = oDoc.getCurrentController.getActiveSheet()
oSel = oDoc.getCurrentSelection()
'Obligamos a seleccionar solo una celda
If oSel.getImplementationName = "ScCellObj" Then
'Creamos un cursor a partir de la celda seleccionada
oCursor = oHojaActiva.createCursorByRange( oSel )
'Expandimos a la región actual
oCursor.collapseToCurrentRegion()
'Obtenemos el número de filas visibles por página
iNumFilPag = oDoc.getCurrentController.getVisibleRange.EndRow - oDoc.getCurrentController.getVisibleRange.StartRow - 1
'Establecemos el primer nivel de agrupamiento en el total de filas
With oRango
.Sheet = oHojaActiva.getRangeAddress.Sheet
.StartRow = 0
.EndRow = oCursor.getRangeAddress.EndRow
End With
oHojaActiva.group( oRango, 1 )
'Agregamos un segundo nivel de agrupamiento, página por página
For co1 = 0 To oCursor.getRangeAddress.EndRow Step iNumFilPag
oRango.StartRow = co1 + 1
oRango.EndRow = co1 + iNumFilPag - 1
oHojaActiva.group( oRango, 1 )
Next
'Para el ultimo rango si no es exacto
If co1 > oCursor.getRangeAddress.EndRow Then
oRango.StartRow = co1 - iNumFilPag + 1
oRango.EndRow = oCursor.getRangeAddress.EndRow
oHojaActiva.group( oRango, 1 )
End If
oHojaActiva.ShowLevel( 1, 1 )
Else
MsgBox "Selecciona solo una celda"
End If
End Sub
Y mira que bien queda.
Claro que el área no esta limitada a que tenga datos, puedes establecer tus criterios pero sobre todo tus necesidades particulares para agrupar.
Si tienes dudas acerca de lo aquí explicado, tienes algún problema con AOO, o quieres ampliar la información, no dudes en dirigirte al {{#switch: macros |
indice = Índice del Foro Oficial en español de Apache OpenOffice | writer = Foro Oficial en español de Apache OpenOffice para Writer | calc = Foro Oficial en español de Apache OpenOffice para Calc | impress = Foro Oficial en español de Apache OpenOffice para Impress | draw = Foro Oficial en español de Apache OpenOffice para Draw | base = Foro Oficial en español de Apache OpenOffice para Base | math = Foro Oficial en español de Apache OpenOffice para Math | macros = Foro Oficial en español de Apache OpenOffice para Macros y API UNO | extensiones = Foro Oficial en español de Apache OpenOffice para Extensiones | comunidad = Foro Oficial en español de Apache OpenOffice para la Comunidad | macros
}} |