Crear un reporte en Excel con PHP y MySQL

Stay hungry, stay foolish

Steve Jobs

En este post les trigo un ejemplo para crear un reporte en Excel con PHP y una base de datos MySQL, la librería que se va a utilizar es PHPExcel.

PHPExcel

PHPExcel es una librería escrita en PHP  integrada por un conjunto de clases que permiten escribir y leer diferentes formatos de archivos de hojas de cálculo  como .xls, . xlsx, .csv,  .ods, PDF, HTML, … Este proyecto se basa en el estándar OpenXML Microsoft y PHP.

Pagina Oficial y descarga

¿En qué consiste el ejemplo?

El ejemplo va a consistir en extraer de la base de datos una lista de alumnos ordenados por carrera, los cuales serán presentados en una hoja de cálculo de Excel y se les aplicará formato a las celdas(Fuente, tamaño, alineación, bordes, relleno, etc). Bueno ahora que sabemos que es lo que vamos a hacer podemos comenzar.

Base de datos


CREATE TABLE IF NOT EXISTS `repexcel`.`alumno` (
 `numcontrol` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
 `paterno` VARCHAR(15) NOT NULL ,
 `materno` VARCHAR(55) NOT NULL ,
 `nombre` VARCHAR(15) NOT NULL ,
 `sexo` ENUM('F','M') NOT NULL ,
 `fechanac` DATE NOT NULL ,
 `idcarrera` INT UNSIGNED NOT NULL ,
 PRIMARY KEY (`numcontrol`) ,
 INDEX `fk_alumno_carrera_idx` (`idcarrera` ASC) ,
 CONSTRAINT `fk_alumno_carrera`
 FOREIGN KEY (`idcarrera` )
 REFERENCES `repexcel`.`carrera` (`idcarrera` )
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `repexcel`.`carrera` (
 `idcarrera` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
 `carrera` VARCHAR(50) NOT NULL ,
 PRIMARY KEY (`idcarrera`) )
ENGINE = InnoDB

 

Archivo HTML

Con lo que voy a comenzar es creando un archivo HTML en el cual solo voy a colocar un título y un hipervínculo para descargar el reporte.


<!DOCTYPE html>
<html>
   <head>
      <meta charset="utf-8" />
      <title>Reporte de excel con PHP y MySQL</title>
   </head>
   <body>
      <div>
        <header>
           <h1>REPORTE DE EXCEL CON PHP Y MYSQL</h1>
        </header>
        <div>
           <a href="reporteexcel.php"> Haz clic para descargar el reporte</a>
        </div>
      </div>
   </body>
</html>

Archivo PHP

Ahora toca el turno de crear el archivo PHP que se va a encargar de generar el reporte, voy a ir explicando el contenido de este archivo poco a poco.

Lo primero es crear la conexión a la base de datos y verificar si se pudo conectar al servidor de lo contrario el programa terminaría hasta aquí y mandaría el error.


$conexion = new mysqli('localhost','usuario','password','basedatos',3306);
if (mysqli_connect_errno()) {
   printf("La conexión con el servidor de base de datos falló: %s\n", mysqli_connect_error());
   exit();
}

Una vez que se realizó la conexión al servidor de base de datos procedemos a ejecutar la consulta de selección para obtener los datos del reporte.


$consulta = "SELECT concat(paterno,' ', materno, ' ' , nombre) AS alumno, fechanac, sexo, carrera FROM alumno ";
$consulta .= " INNER JOIN carrera ON alumno.idcarrera = carrera.idcarrera ORDER BY carrera, nombre";

$resultado = $conexion->query($consulta);

Lo siguiente es verificar que la consulta obtuvo los registros a mostrar y lo hacemos con la siguiente condición, si el número de registros es mayor que 0 quiere decir que si se obtuvieron datos por lo tanto procedemos a crear el reporte.

if($resultado->num_rows > 0 ){

Lo primero que hacemos es definir la zona horaria, debido a que vamos a trabajar con datos de tipo fecha y luego manda un error si no tenemos asignada una zona horaria, en este caso voy a asignar la de la ciudad de México, puedes consultar los datos de la zona horaria desde aquí: Listado de zonas horarias.

date_default_timezone_set('America/Mexico_City');

La siguiente línea determina si se está accediendo al archivo vía HTTP o CLI(command line interface), el archivo solo se va a mostrar si se accede desde un navegador web(HTTP).


if (PHP_SAPI == 'cli')
    die('Este archivo solo se puede ver desde un navegador web');

Ahora si comenzamos a armar el reporte de Excel


/** Se agrega la libreria PHPExcel */
 require_once 'lib/PHPExcel/PHPExcel.php';

// Se crea el objeto PHPExcel
 $objPHPExcel = new PHPExcel();

Comenzamos agregando las propiedades del archivo de Excel, estas las podemos ver una vez que se haya guardado el archivo haciendo “Clic derecho > Propiedades > Detalles”


// Se asignan las propiedades del libro
$objPHPExcel->getProperties()->setCreator("Codedrinks") // Nombre del autor
    ->setLastModifiedBy("Codedrinks") //Ultimo usuario que lo modificó
    ->setTitle("Reporte Excel con PHP y MySQL") // Titulo
    ->setSubject("Reporte Excel con PHP y MySQL") //Asunto
    ->setDescription("Reporte de alumnos") //Descripción
    ->setKeywords("reporte alumnos carreras") //Etiquetas
    ->setCategory("Reporte excel"); //Categorias

Para los títulos del reporte voy a crear dos variables, de esta forma es un poco más fácil realizar algunos cambios si es que el reporte fuera muy extenso.

$tituloReporte = "Relación de alumnos por carrera";
$titulosColumnas = array('NOMBRE', 'FECHA DE NACIMIENTO', 'SEXO', 'CARRERA');

El reporte como ya se habrán dado cuenta va a tener solo 4 columnas: Nombre, Fecha de nacimiento, sexo y carrera. Por lo tanto solo vamos a ocupar hasta la columna D.

// Se combinan las celdas A1 hasta D1, para colocar ahí el titulo del reporte
$objPHPExcel->setActiveSheetIndex(0)
    ->mergeCells('A1:D1');

// Se agregan los titulos del reporte
$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1',$tituloReporte) // Titulo del reporte
    ->setCellValue('A3',  $titulosColumnas[0])  //Titulo de las columnas
    ->setCellValue('B3',  $titulosColumnas[1])
    ->setCellValue('C3',  $titulosColumnas[2])
    ->setCellValue('D3',  $titulosColumnas[3]);

Como pueden apreciar para asignar contenido a una celda se selecciona primero la hoja con setActiveSheetIndex(Indice de hoja) y después con setCellValue(celda, valor) asignamos el contenido a la celda deseada.

Ahora procedemos a rellenar las columnas con la información de los alumnos, para ello vamos a recorrer el resultado utilizando un while, las celdas se van a rellenar de forma parecida al paso anterior solo que aquí para indicar el número de fila se va a utilizar una variable que se va a estar incrementando cada vez que se rellene la información de un alumno.


//Se agregan los datos de los alumnos

 $i = 4; //Numero de fila donde se va a comenzar a rellenar
 while ($fila = $resultado->fetch_array()) {
     $objPHPExcel->setActiveSheetIndex(0)
         ->setCellValue('A'.$i, $fila['alumno'])
         ->setCellValue('B'.$i, $fila['fechanac'])
         ->setCellValue('C'.$i, $fila['sexo'])
         ->setCellValue('D'.$i, $fila['carrera']);
     $i++;
 }

Hasta este punto ya se tiene el archivo con los datos ahora procedemos a aplicar el formato a las celdas. Para ello vamos a crear 3 variables, la primera va a contener el estilo del título del reporte, la segunda el estilo del título de las columnas y la tercera el estilo de la información de los alumnos.

$estiloTituloReporte = array(
    'font' => array(
      	'name'      => 'Verdana',
        'bold'      => true,
        'italic'    => false,
        'strike'    => false,
      	'size' =>16,
      	'color'     => array(
      	    'rgb' => 'FFFFFF'
        )
    ),
    'fill' => array(
    	'type'	=> PHPExcel_Style_Fill::FILL_SOLID,
    	'color'	=> array(
            'argb' => 'FF220835')
	),
    'borders' => array(
      	'allborders' => array(
       	    'style' => PHPExcel_Style_Border::BORDER_NONE
        )
    ),
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
        'rotation' => 0,
        'wrap' => TRUE
    )
);

$estiloTituloColumnas = array(
    'font' => array(
        'name'  => 'Arial',
        'bold'  => true,
        'color' => array(
            'rgb' => 'FFFFFF'
        )
    ),
    'fill' => array(
        'type'	     => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
	'rotation'   => 90,
        'startcolor' => array(
            'rgb' => 'c47cf2'
        ),
        'endcolor' => array(
            'argb' => 'FF431a5d'
        )
    ),
    'borders' => array(
        'top' => array(
            'style' => PHPExcel_Style_Border::BORDER_MEDIUM ,
            'color' => array(
                'rgb' => '143860'
            )
        ),
        'bottom' => array(
            'style' => PHPExcel_Style_Border::BORDER_MEDIUM ,
            'color' => array(
                'rgb' => '143860'
            )
        )
    ),
    'alignment' =>  array(
        'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER,
        'wrap'      => TRUE
    )
);

$estiloInformacion = new PHPExcel_Style();
$estiloInformacion->applyFromArray( array(
    'font' => array(
      	'name'  => 'Arial',
       	'color' => array(
       	    'rgb' => '000000'
        )
    ),
    'fill' => array(
	'type'	=> PHPExcel_Style_Fill::FILL_SOLID,
	'color'	=> array(
            'argb' => 'FFd9b7f4')
	),
    'borders' => array(
        'left' => array(
       	    'style' => PHPExcel_Style_Border::BORDER_THIN ,
	    'color' => array(
    	        'rgb' => '3a2a47'
            )
        )
    )
));

La forma más rápida de dar formato a las celdas es a través de arreglos en los cuales se define todo el conjunto de formato que deseamos aplicar a las celdas. Veamos cómo se aplican.

$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->applyFromArray($estiloTituloReporte);
$objPHPExcel->getActiveSheet()->getStyle('A3:D3')->applyFromArray($estiloTituloColumnas);
$objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A4:D".($i-1));

Como pueden ver el formato se aplicó de 2 formas diferentes una con getStyle(celda)->applyFromArray($arreglo) y la otra con setSharedStyle($estilo, celdas). ¿Cuál es la diferencia entre una y otra?, esto lo podemos apreciar cuando se aplican bordes: con la primer opción por ejemplo si se indica que se van a aplicar bordes superiores a las celdas desde B5 hasta F15, solo estaría aplicando el borde superior a la fila 5 debido a que toma el rango como si fuera una sola celda. En cambio con la segunda opción los bordes superiores se aplicarían a todas las filas desde la 5 hasta la 15 digamos que el formato se aplica a cada celda del rango indicado.

Ahora procedemos a asignar el ancho de las columnas de forma automática en base al contenido de cada una de ellas y lo hacemos con un ciclo de la siguiente forma.

for($i = 'A'; $i <= 'D'; $i++){
    $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension($i)->setAutoSize(TRUE);
}

Bien, ahora solo agregamos algunos detalles mas

// Se asigna el nombre a la hoja
$objPHPExcel->getActiveSheet()->setTitle('Alumnos');

// Se activa la hoja para que sea la que se muestre cuando el archivo se abre
$objPHPExcel->setActiveSheetIndex(0);

// Inmovilizar paneles
//$objPHPExcel->getActiveSheet(0)->freezePane('A4');
$objPHPExcel->getActiveSheet(0)->freezePaneByColumnAndRow(0,4);

Ya para terminar vamos a enviar el archivo para que el usuario lo descargue.

// Se manda el archivo al navegador web, con el nombre que se indica, en formato 2007
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Reportedealumnos.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
else{
    print_r('No hay resultados para mostrar');
}

OK, con eso se termina este ejercicio, más abajo está el link para que descarguen el ejemplo, para más información sobre esta librería puedes revisar su documentación o dejar algún comentario.

Si el contenido te fue de utilidad dale me gusta a nuestra página de Facebook.

download