Guía 2 – Export Excel - Fernando Humanes

Veces vista: 1.078

En esta ocasión vamos a ver un método para obtener hojas Excel más avanzadas que las exportaciones que nos ofrece PHPRunner.

Objetivo

El ejemplo, muy sencillo, pero que entiendo puede ofrecer las bases para otras necesidades más complejas, se genera al requisito de producir una hoja Excel de datos de “Maestro” y “Detalle”, de un conjunto de datos.

Datos

El modelo de datos del ejemplo es:

Como vemos, tenernos las “Órdenes” de venta y dependiente de estas, tenemos el “Detalle de las Órdenes” con sus artículos y precios.

Resultado

Lo que deseamos obtener, en este caso, es un resultado en Excel de este tipo:

DEMO: https://fhumanes.com/novel2/

Solución

Como en otros ejemplos que tengo en mi portal, la solución la orienté para utilizar la librería PHP de PHPoffice/phpspreadsheet, que es la evolución de PHPExcel, esta es la librería PHP que trae PHPRunner para la creación de las exportaciones a Excel.

Para simplificar la confección, utilizo una plantilla de fichero Excel que tiene 3 hojas, las mismas que se ven en la solución.

La información se distribuye de la siguiente forma:

Sheet1. Es donde se va a obtener los datos y presentación que requiere el usuario.
Sheet2. Es donde el programa PHP va a almacenar el conjunto de datos recuperado de la base de datos.
Sheet3. Donde vamos adefinir la presentación de los datos. Esto nos simplifica la programación y además cualquier usuario de Excel podría modificar la plantilla, y por lo tanto, la presentación del resultado sin que haya que cambiar nada en la programación.

La plantilla tiene este aspecto:

(1) Utilizamos 2 “Nombres” (definición de rangos de celdas) para:

Datos01.- Definir la tabla del conjunto de datos que recuperamos de la base de datos.
NumRow.- Para que todas las fórmulas que utilicemos en la 3ªhoja (presentación y cálculos), nos va a indicar un índice sobre la tabla de los datos.

(2) Representa el nombre “NumRow”. Se puede poner en cualquier parte del cuaderno.

(3) Representa el nombre “Datos01” y debe iniciarse en el punto en que está. El número de campos a recuperar puede crecer todo lo que se necesite, pero debemos tenerlo en cuenta en la codificación PHP que después veremos.

(1) Es la presentación de los datos de la tabla “Maestra”.

(2) Es la representación de los datos de la tabla “Detalle”.

(3) En todas las celdas que utilicemos los datos recuperados utilizamos esta fórmula (que en la imagen se ve en español). Como vemos hace referencia al nombre del rango de la tabla de los datos recuperados, después indica el número de registro de la tabla y por último el número de columna de la tabla.

Una vez que tenemos la plantilla, ya sólo nos queda escribir el código PHP que se debe ejecutar, bien mediante un botón o como en el caso del ejemplo, una opción del menú que realiza el informe.

Para facilitar la codificación he creado 2 funciones:

// Function for copy row of sheet of Excel
function copy_row_excel(int $num_page_orig, int $num_page_dest, int $num_row_orig, int $num_row_dest, int $count_row, int $limit_colum )

Esta función la utilizaremos para copiar las celdas desde “Sheet3” a “Sheet1”. Se copian contenidos y formatos o estilos.

// Assign value to reference of formulas
function reference_data(int $num_data, int $num_row, int $count_row, int $limit_colum )

Esta función sustituye en la formulas el literal “NumRow”, por la fila de la tabla de datos que corresponde.

Muestro el código completo de la creación del informe

<?php

// Required by PHPRunner (security)
@ini_set("display_errors","1");
@ini_set("display_startup_errors","1");
require_once("../../include/dbcommon.php");

// Load the excel sheet management library classes
require_once __DIR__ . '/../../../ComponentCode/PhpSpreadsheet_1.10/autoload.php'; 

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\NamedRange;

//  Template processor instance creation

//  $reader = IOFactory::createReader('Xlsx');
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();

//  Options for reading excel files
// $reader->setReadDataOnly(true);
$reader->setIncludeCharts(true);
// $reader->setReadFilter();
// $reader->setReadEmptyCells(true);

$spreadsheet = $reader->load(__DIR__ . '/template1.xlsx'); //  read template

$spreadsheet->setActiveSheetIndex(0); // Activate the first sheet

// Capture data from the BD
$sql="
SELECT
o.id id,
o.customer_name,
o.phone,
o.email,
o.address,
countod,
od.id idod,
od.product_name,
od.price
FROM `order` o
join order_details od on (o.id = od.order_id)
join ( select order_id, count(*) countod from order_details group by order_id ) T1 on (o.id = T1.order_id)
order by o.id, od.id
";
$result = $conn->query($sql);
$data = $result->fetch_all(MYSQLI_NUM); // faster - All record
$dataRows = count($data);
// head
$DateList=now();
$spreadsheet->getActiveSheet()->setCellValue('F2'
        . '', Date::PHPToExcel($DateList)); //  Fill in a cell with the current date in excel format

$spreadsheet->setActiveSheetIndex(1); // Activate the second sheet
$spreadsheet->getActiveSheet()->fromArray($data, null, 'A2'); //  Load ALL records in the active sheet

// Define named ranges
$spreadsheet->addNamedRange(new NamedRange('Datos01', $spreadsheet->getActiveSheet(), 'A2:'.'I'.($dataRows-1+2)));

$master = NULL;
$num_row = 3; // Firt line of Report

for ($x = 0; $x < $dataRows; $x++) { // All Record
    if ($master <> $data[$x][0]) { // Change of Master
        $master = $data[$x][0]; // New Master
        // Copy template of page 3 to page 1
        copy_row_excel(2, 0, 3, $num_row, 7, 6 );
        $spreadsheet->setActiveSheetIndex(0); // Activate the first sheet
        // $spreadsheet->getActiveSheet()->insertNewRowBefore($num_row+6, ($data[$x][5])); // Copy presentation formats
        reference_data($x, $num_row+2, 1, 6); // Head of Order
        $num_row = $num_row + 5;
    }
 copy_row_excel(2, 0, 8, $num_row, 1, 6 );
 reference_data($x, $num_row, 1, 6); // for each record
 $num_row = $num_row + 1;
}

$spreadsheet->addNamedRange(new NamedRange('DatosPrint001', $spreadsheet->getActiveSheet(), 'A4:'.'F'.($num_row))); //  Define print range

// Show/hide gridlines when printing
$spreadsheet->getActiveSheet()->setShowGridlines(false); // Disable grid in print

// Config print file Excel
// $spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_PORTRAIT);
$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4);
// Page Setup: Scaling options
$spreadsheet->getActiveSheet()->getPageSetup()->setFitToPage(false);
$spreadsheet->getActiveSheet()->getPageSetup()->setScale(90);
// $spreadsheet->getActiveSheet()->getPageSetup()->setFitToWidth(0); 
// $spreadsheet->getActiveSheet()->getPageSetup()->setFitToHeight(0);

// Page margins
// $spreadsheet->getActiveSheet()->getPageMargins()->setTop(1);
// $spreadsheet->getActiveSheet()->getPageMargins()->setRight(0.6);
// $spreadsheet->getActiveSheet()->getPageMargins()->setLeft(0.6);
// $spreadsheet->getActiveSheet()->getPageMargins()->setBottom(1);

// Center a page horizontally/vertically
$spreadsheet->getActiveSheet()->getPageSetup()->setHorizontalCentered(false);
$spreadsheet->getActiveSheet()->getPageSetup()->setVerticalCentered(false);

// Setting the print header and footer of a worksheet
// $spreadsheet->getActiveSheet()->getHeaderFooter()->setOddHeader('&C&HPlease treat this document as confidential!');
$spreadsheet->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $spreadsheet->getProperties()->getTitle() . '&RPage &P of &N');

// Setting rows/columns to repeat at top/left
$spreadsheet->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 2);

// Specify printing area
$spreadsheet->getActiveSheet()->getPageSetup()->setPrintArea('B1:'.'F'.($num_row));

// Create file XLSX

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Xlsx)
// header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
// header('Content-Disposition: attachment;filename="municipios.xlsx"');
// header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
// header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

// Old system based on a temporary file
// -------------------- v foot to save the new Excel document  ------------------
$temp_file = tempnam(sys_get_temp_dir(), 'Excel');
// Save EXCEL
// $writer = new Xlsx($spreadsheet);
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setPreCalculateFormulas(false); // Disable formula validation - to improve response time.
$writer->save($temp_file);

// ------------------ Operation with file result -------------------------------------------
$documento = file_get_contents($temp_file);
unlink($temp_file);  // delete file tmp
header("Content-Disposition: attachment; filename= List_order.xlsx");
header('Content-Type: application/excel');
echo $documento;

// Alternative method, but also creates a temporary
// $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
// $writer->save('php://output');

// ----------------------------------- Functions ------------------------
// Function for copy row of sheet of Excel
function copy_row_excel(int $num_page_orig, int $num_page_dest, int $num_row_orig, int $num_row_dest, int $count_row, int $limit_colum ) {
    global $spreadsheet;
    $values = array();
    $styles = array();

    for ($a= 0; $a < $count_row; $a++){
        $spreadsheet->setActiveSheetIndex($num_page_orig); // Activate the  sheet
        for ($b= 0; $b < $limit_colum; $b++){
        // Copy de Values
            $ref=chr((ord('A')+$b)).($num_row_orig+$a);  // Origen
            $values[$a][$b] =  $spreadsheet->getActiveSheet()->getCell($ref)->getValue(); // Value
            $style =  $spreadsheet->getActiveSheet()->getStyle($ref); // Style
            $style =  serialize($style); // Style
            $styles[$a][$b] = $style; // Style
        } 
    }
    for ($a= 0; $a < $count_row; $a++){
        $spreadsheet->setActiveSheetIndex($num_page_dest); // Activate the  sheet
        for ($b= 0; $b < $limit_colum; $b++){
        // Copy de Values
            $ref=chr((ord('A')+$b)).($num_row_dest+$a);  // Origen
            $spreadsheet->getActiveSheet()->setCellValue($ref,$values[$a][$b]); // Value
            $style = unserialize($styles[$a][$b]); // Style
            $spreadsheet->getActiveSheet()->duplicateStyle($style,$ref); // Style
        } 
    }
    return true;
}
// Assign value to reference of formulas
function reference_data(int $num_data, int $num_row, int $count_row, int $limit_colum ) {
    global $spreadsheet;
    global $data;
    $values = 0;
    $spreadsheet->setActiveSheetIndex(0); // Activate the  sheet

    for ($a= 0; $a < $count_row; $a++){
        for ($b= 0; $b < $limit_colum; $b++){
        // Copy de Values
            $ref=chr((ord('A')+$b)).($num_row+$a); 
            $values =  $spreadsheet->getActiveSheet()->getCell($ref)->getValue(); // Value
            $values = str_replace("NumRow", $num_data+1 , $values);
            switch ($b) {
            case 2:
                // $spreadsheet->getActiveSheet()->setCellValue($ref1,'=IF(U'.(6+$b).'<>U'.(7+$b).',1,B'.(6+$b).'+1)'); // Valor
                // break;
            default:
                $spreadsheet->getActiveSheet()->setCellValue($ref,$values); // Valor
            }
        } 
    }
    return true;
}
?>

Adjunto, en el pie del artículo, todos los ficheros que requerís para desplegar el ejemplo en vuestros equipos. Aunque el ejemplo está en versión 10.4 de PHPrunner, esta codificación valdría para cualquier versión, siempre que el PHP sea versión 7.X

Para cualquier duda o aclaración, por favor, escribidme un email a [email protected]

Adjuntos

Archivo	Tamaño de archivo	Descargas
Backup de Base de Datos	1 KB	566
Proyecto PHPRunner 10.4	44 KB	617

Objetivo

Datos

Resultado

Solución

Adjuntos

Blog personal para facilitar soporte gratuito a usuarios de React y PHPRunner