Procedimientos. Datos.

Calc. Uso de funciones dentro de los script.

Sabemos que Calc cuenta con un número elevado de funciones que podemos utilizar en este servicio para trabajar con datos, numéricos o alfanuméricos. Podemos reemplazar estas funciones mediante código OOo Basic, pero también tenemos la posibilidad de incluirlas en nuestros script, aprovechando así toda su potencia y ahorrándonos trabajo (1). 

Encontré el código que explicaré en esta entrada buscando precisamente cómo aprovechar las funciones Calc  en el código OOo Basic y, por lo que parece, no fui el único que se planteó una pregunta parecida (2). Cierto que no me ayudó a resolver el problema que tenía planteado, pero me ha parecido una solución muy interesante y de mucha utilidad, así que la comparto (3).

La base fundamental de este procedimiento consiste en hacer referencia en los script a las funciones Calc, pero no es la denominación con que se nos presentan en el servicio en su Asistente de funciones, sino en inglés; detalle éste importante para que funciones el código (4).

Se plantean dos opciones básicas: la que incorpora la función Calc en el script y la que lo hace mediante una función OOo Basic. Esta diferencia conlleva mucho más, como podremos ver más adelante, pero como primer criterio de diferenciación es relevante para que nos vayamos poniendo en situación.

Estas dos formas de trabajo se ejemplifican en los siguientes script. En el primero llamamos a una función para que "escriba" su resultado en una celda y después accedemos al contenido de esa celda para aprovechar el resultado de la función.

Sub PruebaFuncionCalc1a

Dim oHoja As Object

Dim dResulta As Double

oHoja = ThisComponent.CurrentController.ActiveSheet

oHoja.getCellRangeByName("B12").Formula = "=SUM(A1:A12)"

dResulta = oHoja.getCellRangeByName("B12").Value

MsgBox dResulta

End Sub

Tras declarar dos variables (oHoja y dResulta) y acceder al objeto Hoja (oHoja = ThisComponent.CurrentController.ActiveSheet), implementamos la fórmula (=SUM(A1:A12)) en la celda B12. Para ello añadimos al identificador de la celda el identificador Formula (oHoja.getCellRangeByName("B12").Formula = "=SUM(A1:A12)")

Una vez que la fórmula actúa y entrega su resultado en la celda establecida, accedemos a dicho resultado asignándolo a la variable dResulta (dResulta = oHoja.getCellRangeByName("B12").Value) (5). En este caso, lo que hacemos con el valor obtenido es mostrarlo mediante MsgBox (MsgBox dResulta), pero podríamos utilizarlo de cualquier otra forma, dependiendo del algoritmo de nuestro script.

El segundo modo de trabajo implica el uso de una función (FuncionCalc()) que es llamada desde un script (PruebaFuncionCalc2). Empecemos explicando el script:

Sub PruebaFuncionCalc2

Dim oHoja As Object

Dim dSuma As Double

oHoja = ThisComponent.getSheets().getByName("Hoja1")

dSuma = FuncionCalc( "SUM", Array(oHoja.getCellRangeByName("A1:A12")) )

MsgBox dSuma

 

End Sub

Tras declarar las dos variables necesarias (oHoja y dSuma) y acceder al objeto Hoja (oHoja = ThisComponent.getSheets().getByName("Hoja1")) recurrimos a la función (FuncionCalc()), asignando el dato que devuelve ésta a la variable dSuma. Dado que se trata de una función, cuenta (en este caso) con dos parámetros, que son los que pasamos por valor en la formulación de la llamada a la función (FuncionCalc( "SUM", Array(oHoja.getCellRangeByName("A1:A12")) )):

• El primero es el nombre de la función Calc (en inglés) ("SUM")
• Y el segundo, mucho más complejo, es la matriz de datos formada por el contenido del conjunto de celdas A1:A12, esto es: los datos que requiere la función Calc (Array(oHoja.getCellRangeByName("A1:A12"))) (6)

Después utilizamos el resultado devuelto por la función para mostrarlo en pantalla mediante MsgBox (MsgBox dSuma) (7)

 Veamos a continuación el código de la función FuncionCalc():

Function FuncionCalc( Nombre As String, Datos() )

Dim oSFA As Object

oSFA = createUnoService( "com.sun.star.sheet.FunctionAccess" )

FuncionCalc = oSFA.callFunction( Nombre, Datos() )

End Function

Observa que la función tiene dos parámetros, uno de tipo texto y otro una matriz (Nombre As String, Datos()): son los mismos a los que damos valor desde el script (( "SUM", Array(oHoja.getCellRangeByName("A1:A12")))).

Además, la función cuenta con una variable de tipo objeto (oSFA) a la que se asigna el objeto UnoService de acceso a las funciones de Calc FunctionAccess. (createUnoService( "com.sun.star.sheet.FunctionAccess")) Esta asignación se basa en la estructura típica de acceso a las funciones nucleares de la suite LibreOffice, que, como sabes, está escrita en parte en Java, esto es: en un lenguaje orientado a objetos (POO)

Siguiendo la sintaxis de las funciones, asignamos al identificador de la actual el resultado de aplicar la función nombrada a la matriz de datos; como recordarás, ambos han sido pasados por valor desde el scriot (FuncionCalc = oSFA.callFunction(Nombre, Datos())). De este modo la función devuelve al script, el resultado de dicha función. (8)

Dado que sólo he sabido manejar adecuadamente el primer modo de trabajo (ver nota 8), me limitaré a exponer cómo transformarlo para trabajar con otras funciones. Llamo a estos script PruebaFuncionCalc1b y PruebaFuncionCalc1c respectivamente. En el primero empleo una función Calc que actúa sobre un dato unitario (la función RAIZ()) y en el segundo trabajo con una función que trabaja con datos alfanuméricos, posicionados en una colección del celdas (no una matriz) (la función CONCATENAR()) (9).

Empecemos por el primer script y la función RAIZ()

Sub PruebaFuncionCalc1b

Dim oHoja As Object

Dim sResulta As Double

oHoja = ThisComponent.CurrentController.ActiveSheet

oHoja.getCellRangeByName("B15").Formula = "=SQRT(A15)"

dResulta = oHoja.getCellRangeByName("B15").Value

MsgBox dResulta

End Sub

 Como ves hay poco que añadir a lo visto en la explicación del script de base (PruebaFuncionCalc1a): únicamente sustituimos la expresión de la función anterior (SUM()) por la actual (SQRT()) (en inglés), con lo que no tienes mayor dificultad que recurrir al Asistente para funciones de Calc y traducir el nombre de la función al inglés. Los cambios de parámetros van incluidos en el procedimiento: para SUM() era necesaria una matriz de datos ("=SUM(A1:A12)") y para SQRT() únicamente una referencia a celda ("=SQRT(A15)")

El script PruebaFuncionCalc1c muestra la validez universal del procedimiento de sustitución que te acabo de explicar:

Sub PruebaFuncionCalc1c

Dim oHoja As Object

Dim sResulta As String

oHoja = ThisComponent.CurrentController.ActiveSheet

oHoja.getCellRangeByName("B20").Formula = "=CONCATENATE(A20;A21;A22)"

dResulta = oHoja.getCellRangeByName("B20").String

MsgBox dResulta

End Sub

De nuevo, es suficiente con escribir la sintaxis de la función Calc en la posición adecuada dentro del script para obtener el resultado deseado. 

Pero aun hay más: podemos combinar varias funciones dentro de la misma expresión, del mismo modo que es posible construir una combinación de funciones Calc. El script PruebaFuncionCalc1d es una prueba de ello.

Sub PruebaFuncionCalc1d

Dim oHoja As Object

Dim dResulta As Double

oHoja = ThisComponent.CurrentController.ActiveSheet

oHoja.getCellRangeByName("B13").Formula = "=SQRT(SUM(A1:A12))"

dResulta = oHoja.getCellRangeByName("B13").Value

MsgBox dResulta

End Sub

Aquí combinamos las funciones SQRT() y SUM() para obtener una función combinada en la que sobre la suma resultante de la matriz de datos A1:A12 solicitamos el cálculo de su raíz cuadrada ("=SQRT(SUM(A1:A12))"). El resultado se escribe en la celda B13. Como puedes comprobar, la sintaxis de la instrucción únicamente varía en la forma en que se llaman a ambas funciones, que es la misma que escribimos sobre la Línea de entrada de fórmulas de Calc.

Por tanto resulta evidente la ventaja de este modo de implementar funciones Calc en los script OOo Basic: es suficiente con escribir la fórmula según su sintaxis Calc, sustituyendo el nombre de la función por su equivalente en inglés. Es además importante establecer correctamente la referencia a los datos con los que trabajarán dichas funciones.

Documentos.

Pongo a tu disposición dos documento. El primero [FormulCalc.ods] es el documento Calc en el que puedes encontrar los ejemplos explicados en la entrada. El segundo [WIKI de OpenOffice] el enlace a la web donde encontrarás el listado de funciones Calc en inglés y en español.

NOTAS

(1) En algunas ocasiones esto puede ser así, pero en otras es preferible generar procedimiento propios, basados en OOo Basic, ya que son mucho más potentes y flexibles. No obstante, no es de despreciar lo que el uso de las funciones Calc nos puede ayudar a resolver problemas (especialmente de cálculos complejos) para los que no dispongamos de solución en un momento concreto. Toda ayuda es bienvenida.

(2) El material procede en primera instancia de la Web OpenOffice (en español), aunque los autores reconocen el mérito a Mauricio Baeza, del que son deudores. Y yo de ambos.

(3) A lo que aportan los autores anteriores añado de mi parte mi explicación del funcionamiento del código. Esta explicación no está disponible al menos en la primera de las referencias.

(4) Una contribución que añado para facilitarte el trabajo: el listado de funciones Calc en inglés y en español, cortesía de la WIKI de OpenOffice.

(5) Utilizamos .Value por tratarse de un resultado numérico. En caso de emplear una fórmula que devuelva un contenido alfanumérico emplearíamos .String. 

(6) Si tienes curiosidad, puedes comprobarlo escribiendo esa misma función en la Línea de entrada (de fórmulas) de Calc.

(7) Observa que, a diferencia del modo anterior, ahora no escribimos el resultado de la función en ninguna celda; no como paso previo para acceder a su resultado. Podríamos hacerlo, pero sería ya una decisión a posteriori, equivalente a mostrarlo por pantalla, que es la solución por la que optamos en este caso. 

(8) En principio podríamos sustituir la función SUM() por cualquiera otra que utilice como datos una matriz similar a la que emplea ésta, pero no resulta tan sencillo trabajar con funciones que actúan sobre otro tipo de estructuras. Al menos yo no he sabido hacerlo. Supongo que habrá forma de hacerlo y cuando lo averigüe lo expondré. 

(9) Te sugiero que, antes de seguir, refresques tu memoria haciendo uso de ambas funciones desde Calc. Así te resultarán familiares y comprenderás mejor los script en los que se incluyen.