Generar hojas de cálculo con fórmulas mediante Apache POI.
0. Índice de contenidos.
- 1. Introducción.
- 2. Entorno.
- 3. Diseñando la hoja de cálculo.
- 4. La clase Piloto.
- 5. Generando la hoja de cálculo.
- 6. Ejecutando el ejemplo.
- 7. Referencias.
- 8. Conclusiones.
1. Introducción
Las hojas de cálculo son una poderosa herramienta de la que nos proveen las suites de ofimática. Con ellas podemos realizar infinidad de tareas como llevar nuestras cuentas domésticas, gestionar tareas o explotar métricas. Un aspecto muy interesante de las hojas de cálculo son las fórmulas, que nos permiten tratar datos que tengamos almacenados en ellas.
En este tutorial vamos a ver cómo generar hojas de cálculo con fórmulas con ayuda de la librería Apache POI.
2. Entorno.
El tutorial está escrito usando el siguiente entorno:
- Hardware: Portátil MacBook Pro 15′ (2.2 Ghz Intel Core I7, 4GB DDR3).
- Sistema Operativo: Mac OS Snow Leopard 10.6.7
- Entorno de desarrollo: Eclipse 3.7 Indigo.
- Apache POI 3.7.
- Libre Office 3.4.3
3. Diseñando la hoja de cálculo.
En nuestro ejemplo vamos a generar una hoja de cálculo que almacene los tiempos que tardan los pilotos de carreras en dar las vueltas de entrenamiento. Además la hoja de cálculo deberá ser capaz de tratar esos tiempos para generar información adicional. Los requisitos serán los siguientes:
- En la primera fila de la hoja de cálculo debe aparecer una descripción de las columnas.
- En la primera columna debe aparecer el nombre del piloto.
- Cada piloto dará 5 vueltas de entrenamiento por lo que las 5 columnas siguientes deben corresponder a los tiempos en segundos que el piloto ha tardado en dar cada una de las vueltas.
- La siguiente columna será el total en segundos que el piloto tardó en dar las 5 vueltas. Se calculará mediante una fórmula.
- La siguiente columna será el promedio de tiempo que tardó el piloto teniendo en cuenta las 5 vueltas. Se calculará mediante una fórmula.
- La siguiente columna será el mejor tiempo de las 5 vueltas. Se calculará mediante una fórmula.
4. La clase Piloto.
Para almacenar el nombre del piloto y el tiempo de sus vueltas crearemos una clase a la que llamaremos Piloto y de la que hará uso la clase que genere nuestra hoja de cálculo.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
import java.util.ArrayList; import java.util.List; public class Piloto { public static final int NUMERO_VUELTAS_ENTRENAMIENTO = 5; private final String nombre; private final List<Double> tiemposVueltas; public Piloto(String nombre) { this.nombre = nombre; this.tiemposVueltas = new ArrayList<Double>(); } public String getNombre() { return nombre; } public List<Double> getTiemposVueltas() { return tiemposVueltas; } } |
De momento fácil, ¿no?.
5. Generando la hoja de cálculo.
Una vez tenemos la clase que nos proporcionará el nombre del piloto y sus tiempos en dar las vueltas, vamos con la gracia de todo esto: la clase que nos creará la hoja de cálculo.
La clase generará una hoja de cálculo (recordemos que un documento puede tener más de una hoja de cálculo) llamada «Tiempos entrenamientos» y generará un fichero llamado tiempos-entrenamientos.xls
Para las fórmulas utilizaremos las siguientes funciones: SUM (sumará el total de tiempos de un piloto), AVERAGE (calculará la media de tiempos de un piloto) y MIN (calculará el mejor tiempo en dar una vuelta).
Además, vamos a añadir un estilo propio a las celdas de la primera fila (la que contiene los nombres de las columnas) y otro estilo a las celdas con fórmula para que resalten un poco más. Finalmente auto-ajustaremos el ancho de las columnas para que se adapten al contenido.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 |
import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; public class GeneradorHojaCalculo { // documento con las hojas de calculo private final Workbook libro; // la hoja de calculo private final Sheet hojaTiemposPilotos; // estilo de las celdas del encabezado (con el nombre de las columnas) private final CellStyle estiloTitulo; // estilo de las celdas con fórmula private final CellStyle estiloCeldaConFormula; public GeneradorHojaCalculo() { this.libro = new HSSFWorkbook(); this.hojaTiemposPilotos = this.libro.createSheet("Tiempos entrenamientos"); this.estiloTitulo = getEstiloTitulo(); this.estiloCeldaConFormula = getEstiloCeldaConFormula(); anadeFilaEncabezado(); } // crea una fila con los datos del piloto: nombre, tiempos, total, media y mejor tiempo public void anadeTiemposPiloto(Piloto piloto) { final Row filaPiloto = getNuevaFila(); filaPiloto.createCell(0).setCellValue(piloto.getNombre()); for (int i = 1; i <= Piloto.NUMERO_VUELTAS_ENTRENAMIENTO; i++) { final Cell celda = filaPiloto.createCell(i); celda.setCellValue(piloto.getTiemposVueltas().get(i - 1)); celda.setCellType(Cell.CELL_TYPE_NUMERIC); } generaFormulaSumaTiempos(filaPiloto); generaFormulaMediaTiempos(filaPiloto); generaFormulaMejorTiempo(filaPiloto); } // crea la celda con la fórmula de suma de tiempos correspondiente a una fila private void generaFormulaSumaTiempos(Row filaPiloto) { final int numeroFila = filaPiloto.getRowNum() + 1; final String formula = "SUM" + generaRangoFormulaEnFila(numeroFila); anadeFormulaYEstiloACelda(filaPiloto.createCell(Piloto.NUMERO_VUELTAS_ENTRENAMIENTO + 1), formula); } // crea la celda con la fórmula de media de tiempos correspondiente a una fila private void generaFormulaMediaTiempos(Row filaPiloto) { final int numeroFila = filaPiloto.getRowNum() + 1; final String formula = "AVERAGE" + generaRangoFormulaEnFila(numeroFila); anadeFormulaYEstiloACelda(filaPiloto.createCell(Piloto.NUMERO_VUELTAS_ENTRENAMIENTO + 2), formula); } // crea la celda con la fórmula de que calcula el mejor tiempo a una fila private void generaFormulaMejorTiempo(Row filaPiloto) { final int numeroFila = filaPiloto.getRowNum() + 1; final String formula = "MIN" + generaRangoFormulaEnFila(numeroFila); anadeFormulaYEstiloACelda(filaPiloto.createCell(Piloto.NUMERO_VUELTAS_ENTRENAMIENTO + 3), formula); } // devuelve el rango de columnas sobre las que actuará la formula. Ej: (B2:F2) private static String generaRangoFormulaEnFila(int numeroFila) { // la columna donde se situa el primer tiempo será la B (codigo ASCII 66) ya que en la A está el nombre del piloto) final byte columnaB = 66; final char primeraColumna = (char)columnaB; final char ultimaColumna = (char)columnaB + Piloto.NUMERO_VUELTAS_ENTRENAMIENTO - 1; return "(" + primeraColumna + numeroFila + ":" + ultimaColumna + numeroFila + ")"; } // añade la fórmula a una celda y añade el estilo de las celdas con fórmula private void anadeFormulaYEstiloACelda(Cell celda, String formula) { celda.setCellFormula(formula); celda.setCellStyle(estiloCeldaConFormula); } // genera el documento public OutputStream generaDocumento() throws IOException { ajustaColumnas(); final OutputStream outputStream = new FileOutputStream("tiempos-entrenamientos.xls"); libro.write(outputStream); outputStream.close(); return outputStream; } // crea la fila y celdas del encabezado con el nombre de las columnas private void anadeFilaEncabezado() { final Row filaEncabezado = getNuevaFila(); int numeroCelda = 0; creaCeldaEncabezado(filaEncabezado, numeroCelda++, "Piloto"); for (int i = 1; i <= Piloto.NUMERO_VUELTAS_ENTRENAMIENTO; i++) { creaCeldaEncabezado(filaEncabezado, numeroCelda++, "Vuelta " + i); } creaCeldaEncabezado(filaEncabezado, numeroCelda++, "Tiempo total"); creaCeldaEncabezado(filaEncabezado, numeroCelda++, "Promedio"); creaCeldaEncabezado(filaEncabezado, numeroCelda++, "Mejor tiempo"); } // crea una celda de encabezado (las del título) y añade el estilo private void creaCeldaEncabezado(Row filaEncabezado, int numeroCelda, String valor) { final Cell celdaEncabezado = filaEncabezado.createCell(numeroCelda); celdaEncabezado.setCellValue(valor); celdaEncabezado.setCellStyle(estiloTitulo); } // ajusta el ancho de las columnas en función de su contenido private void ajustaColumnas() { final short numeroColumnas = hojaTiemposPilotos.getRow(0).getLastCellNum(); for (int i = 0; i < numeroColumnas; i++) { hojaTiemposPilotos.autoSizeColumn(i); } } // devuelve el estilo que tendrán las celdas del título (negrita y color de fondo azul) private CellStyle getEstiloTitulo() { final CellStyle cellStyle = libro.createCellStyle(); final Font cellFont = libro.createFont(); cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(cellFont); cellStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); return cellStyle; } // devuelve el estilo que tendrán las celdas con fórmula (color de fondo gris claro) private CellStyle getEstiloCeldaConFormula() { final CellStyle cellStyle = libro.createCellStyle(); cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); return cellStyle; } // crea una nueva fila a continuación de la anterior private Row getNuevaFila() { return hojaTiemposPilotos.createRow(hojaTiemposPilotos.getPhysicalNumberOfRows()); } } |
Obsérvese que los métodos getEstiloTitulo y getEstiloCeldaConFormula crean los estilos de las celdas con los nombres de las columnas y las que contienen una fórmula.
El método encargado de añadir una fórmula a una celda es setCellFormula de la clase Cell, al que habrá que invocar por cada celda a la que se quiera adjuntar una fórmula. La fórmula encargada de sumar los tiempos del primer piloto será SUM(B2:F2) que indica que se va a sumar el contenido del rango de las columnas B a F de la fila 2 (fila donde están los tiempos del primer piloto). Para el segundo piloto, que está en la fila 3, la fórmula sería SUM(B3:F3). Lo mismo se hace con las fórmulas de media (AVERAGE) y mejor tiempo (MIN).
6. Ejecutando el ejemplo.
Pues bien, vamos a ver si esto funciona o no… Para ello vamos a dar de alta los datos de dos pilotos y ver si nos genera correctamente el documento.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
public static void main (String args[]) { final GeneradorHojaCalculo generadorHojaCalculo = new GeneradorHojaCalculo(); final Piloto piloto1 = new Piloto("Fernando Alonso"); piloto1.getTiemposVueltas().add(131.78); piloto1.getTiemposVueltas().add(129.95); piloto1.getTiemposVueltas().add(128.16); piloto1.getTiemposVueltas().add(125.91); piloto1.getTiemposVueltas().add(130.44); final Piloto piloto2 = new Piloto("Jaime Alguersuari"); piloto2.getTiemposVueltas().add(133.16); piloto2.getTiemposVueltas().add(132.32); piloto2.getTiemposVueltas().add(129.86); piloto2.getTiemposVueltas().add(128.02); piloto2.getTiemposVueltas().add(132.45); generadorHojaCalculo.anadeTiemposPiloto(piloto1); generadorHojaCalculo.anadeTiemposPiloto(piloto2); try { generadorHojaCalculo.generaDocumento(); } catch (IOException e) { e.printStackTrace(); } } |
El proceso no da ningún error y nos genera un documento como se muestra a continuación.
Si nos posicionamos sobre alguna de las celdas con fórmula podremos ver en la línea de entrada que se han generado correctamente. Si, en la propia hoja de cálculo, cambiamos alguno de los tiempos de los pilotos observaremos que se recalcula el tiempo total, la media y el mejor tiempo.
7. Referencias.
8. Conclusiones.
En este tutorial hemos visto que no tiene mucho misterio generar hojas de cálculo con fórmulas gracias a la ayuda de Apache POI. Además, por hacer un poco más completo el tutorial hemos añadido estilos a las celdas y ajustado en función de su contenido.
En nuestras aplicaciones puede ser muy interesante generar hojas de cálculo a modo de informes o para exportar algunos datos.
Espero que este tutorial os haya sido de ayuda. Un saludo.
Miguel Arlandy
Facil y práctico. Gracias.
Muy bueno,
Una opción buena es utilizar un fichero Excel hecho, listo con el formato, estructura, objetos de gráfica preparados, y con POI manipulemos las fórmulas y celdas.
A ver si te animas y haces uno de PowerPoint.
Aun así después de leer este, no quepo en mí de gozo. 😀
Muy buen ejemplo… Pero quisiera hacer una solicitud a las brillantes personas de adictos al trabajo… Sería posible publicar un tutorial de como guardar un documento Word (ejemplo: Curriculum) dentro un campo Blob de una tabla en MySQL??? Sería excelente, y la verdad, necesitaria un ejemplo de esos… Gracias y felicidades 😉
Amigo, me gustaría saber cómo reconozco al leer un fichero xls q una celda es realmente un bloque de celdas
Gracias de antemano
Muchas gracias por el aporte, la verdad es que me ha servido muy gratamente, tanto el como ponerle una formula a una celda como el conseguir el rango de celdas a operar.
Muchas gracias, está genial, muy útil, sólo una cuestión, ¿es posible conseguir copiar fórmulas de una fila a otra y que las coordenadas de las celdas que no están bloqueadas con «$» se incrementen en función de la fila a la que se copian?
Muchas gracias,
Raúl