Primeros pasos para programar en Excel
Después de ver una tabla de traducción a de fórmulas de Excel de un compañero, me he animado a escribir esta entrada.
Todos sabemos que Excel es una excelente herramienta de trabajo. Puede ser una inmejorable herramienta de reporting, sobre todo cuando los informes son del tipo 'que pasaría si....'
Pues bien, desde nuestra aplicación siempre podemos generar nuestra propia Excel y, además podemos decirle a Excel que haga los cálculos por nosotros. Desde unas simples agrupaciones con subtotales hasta complejas fórmulas.
Para los que no habéis trabajado nunca con Excel (desde el punto de vista del programador), os diré que lo más sencillo es grabarse una macro con los datos, fórmulas y acciones que queramos. Después se puede tomar la macro grabada y retocarla ligeramente.
Pero... si os fijais, en la macro, las fórmulas que genera Excel están en inglés, mientras que las que introducimos nostros, como usuarios, están en castellano (o en el idioma de nuestra madre). Cuando ya tenemos algo de práctica, es bastante pesado grabarse una macro solo para averiguar el nombre de la función. De ahí lo interesante de la tabla de más arriba.
Cuando nos referimos desde código a una celda lo podemos hacer de varias maneras:
Nota para los programadores de C#. Este lenguaje no admite, de momento, propiedades con parámetros (la verdad es que es un poco enrevesado lo de una propiedad con parámetros). Para ello podéis utilizar los métodos get. El anterior código quedaría así en C#: miRango.get_Address(true, false, ,XlReferenceStyle.xlA1, Missing.Value, Missing.Value); Hay que tener en cuenta que C# tampoco admite parámetros opcionales.
Cuando escribimos algo en una celda, podemos realmente hacer 2 cosas:
En realidad el segundo camino no funciona en .NET. Al usar la propiedad Formula del objeto Range, en VBA funciona correctamente pero desde .NET, Excel está esperando el nombre de la función según la configuración regional del usuario. Lo que debemos hacer es crearnos una función como la que sigue que establezca por Reflexión la propiedad Fórmula. Usando IDispatch::Invoke podemos especificar el locale a usar en la llamada: el 1033 (en-US) que es el que espera Excel cuando se le invoca mediante automatización.
Hay una tercera opción, que es utilizar la propiedad WorksheetFunction. Esta propiedad del objeto Application enumera las funciones estandar de Excel. Pero lo que hace al llamar a la correspondiente función es devolver el valor que normalmente escribiría en la celda. Es decir, nos permite utilizar las funciones de Excel dentro de nuestros algoritmos; pero no escribir la fórmula en la celda, sino su resultado.
Hay que hace notar la sintaxis de la fórmula. Tenemos que empezar por el signo igual y las referencias a los rangos los tenemos que poner en formato A1 o formato R1C1, pero no podemos utilizar índices para filas y columnas, que tan bien nos vendría. Pero como ya conocemos la propiedad Address... pues no hay problema en generar la referencia de las celdas en el formato que le gusta a Excel ¿verdad?
Con esto ya podemos crear hojas más o menos complejas (en cuanto a datos y fórmulas). Pero también será necesario dar algún formato. El tema de los formatos puede llevarnos más de una entrada. De momento os diré que la mejor forma de saber que propiedades tenemos que aplicar es grabar una macro, dar el formato y ver qué ha escrito Excel por nosotros. Para los formatos de número y fecha, tenemos el mismo dilema que con las formulas: tenemos las propiedades NumberFormat y NumberFormatLocal.
Otro día más.
Todos sabemos que Excel es una excelente herramienta de trabajo. Puede ser una inmejorable herramienta de reporting, sobre todo cuando los informes son del tipo 'que pasaría si....'
Pues bien, desde nuestra aplicación siempre podemos generar nuestra propia Excel y, además podemos decirle a Excel que haga los cálculos por nosotros. Desde unas simples agrupaciones con subtotales hasta complejas fórmulas.
Para los que no habéis trabajado nunca con Excel (desde el punto de vista del programador), os diré que lo más sencillo es grabarse una macro con los datos, fórmulas y acciones que queramos. Después se puede tomar la macro grabada y retocarla ligeramente.
Pero... si os fijais, en la macro, las fórmulas que genera Excel están en inglés, mientras que las que introducimos nostros, como usuarios, están en castellano (o en el idioma de nuestra madre). Cuando ya tenemos algo de práctica, es bastante pesado grabarse una macro solo para averiguar el nombre de la función. De ahí lo interesante de la tabla de más arriba.
Cuando nos referimos desde código a una celda lo podemos hacer de varias maneras:
- Según la forma tradicional de Excel, por ejemplo hoja.Cells("A3:A5")
- Por número de fila, columna. Esta forma es más apropiada, sobre todo si tenemos que iterar o calcular nº de filas o columnas que estamos escribiendo. set miRango = hoja.Range(hoja.Cells(3,1), hoja.Cells(5,1)) es el equivalente al formato anterior.
Nota para los programadores de C#. Este lenguaje no admite, de momento, propiedades con parámetros (la verdad es que es un poco enrevesado lo de una propiedad con parámetros). Para ello podéis utilizar los métodos get. El anterior código quedaría así en C#: miRango.get_Address(true, false, ,XlReferenceStyle.xlA1, Missing.Value, Missing.Value); Hay que tener en cuenta que C# tampoco admite parámetros opcionales.
Cuando escribimos algo en una celda, podemos realmente hacer 2 cosas:
- Escribir un valor. Esto es sencillo. Por ejemplo hoja.Cells(3, 1).Value = 10, escribe 10 en la celda A3
- Escribir una fórmula. Aquí podemos optar por 2 caminos:
- Utilizar la propiedad FormulaLocal: hoja.cells(1,1).FormulaLocal = "=SUMA(A2:A20)"
- Utilizar la propiedad Formula: hoja.cells(1,1).Formula = "=SUM(A2:A20)"
En realidad el segundo camino no funciona en .NET. Al usar la propiedad Formula del objeto Range, en VBA funciona correctamente pero desde .NET, Excel está esperando el nombre de la función según la configuración regional del usuario. Lo que debemos hacer es crearnos una función como la que sigue que establezca por Reflexión la propiedad Fórmula. Usando IDispatch::Invoke podemos especificar el locale a usar en la llamada: el 1033 (en-US) que es el que espera Excel cuando se le invoca mediante automatización.
public void SetFormula(Range range, string formula) {
range.GetType().InvokeMember("Formula",
BindingFlags.SetProperty | BindingFlags.Public | BindingFlags.Instance,
null,
range,
new object[] { formula },
new System.Globalization.CultureInfo(0x409));
}
range.GetType().InvokeMember("Formula",
BindingFlags.SetProperty | BindingFlags.Public | BindingFlags.Instance,
null,
range,
new object[] { formula },
new System.Globalization.CultureInfo(0x409));
}
Hay una tercera opción, que es utilizar la propiedad WorksheetFunction. Esta propiedad del objeto Application enumera las funciones estandar de Excel. Pero lo que hace al llamar a la correspondiente función es devolver el valor que normalmente escribiría en la celda. Es decir, nos permite utilizar las funciones de Excel dentro de nuestros algoritmos; pero no escribir la fórmula en la celda, sino su resultado.
Hay que hace notar la sintaxis de la fórmula. Tenemos que empezar por el signo igual y las referencias a los rangos los tenemos que poner en formato A1 o formato R1C1, pero no podemos utilizar índices para filas y columnas, que tan bien nos vendría. Pero como ya conocemos la propiedad Address... pues no hay problema en generar la referencia de las celdas en el formato que le gusta a Excel ¿verdad?
Con esto ya podemos crear hojas más o menos complejas (en cuanto a datos y fórmulas). Pero también será necesario dar algún formato. El tema de los formatos puede llevarnos más de una entrada. De momento os diré que la mejor forma de saber que propiedades tenemos que aplicar es grabar una macro, dar el formato y ver qué ha escrito Excel por nosotros. Para los formatos de número y fecha, tenemos el mismo dilema que con las formulas: tenemos las propiedades NumberFormat y NumberFormatLocal.
Otro día más.
Comentarios