Crear informes con phpSpreadsheet

Veces vista: 4.189

Reflexión sobre los informes en las aplicaciones informáticas (PhpSpreadsheet)

En la vida, normalmente trasladamos en nuestro quehacer diario lo que anteriormente hemos hecho en situaciones anteriores.

Cuando las aplicaciones informáticas se iniciaron, los usuarios obtenían los datos de las mismas en informes impresos (en papel) y de ahí, estudiaban y explotaban los mismos.

Hace ya muchos años (al menos desde 1995) que los usuarios disponen de hojas de cálculo que les permiten hacer estudios de sus datos y presentar los mismos a su equipo o a sus responsables. Al principio copiaban los datos de los informes (papel) y más adelante, los informáticos éramos capaces de generales ficheros csv, dbf, etc. que les permitían explotar más rápido los datos.

Hemos evolucionado mucho, pero todavía nos queda bastante. Creo que seguimos (casi todos) con la idea de que los sistemas deben producir los informes en PDF (que por similitud a los informes en papel nos parece más adecuado).

Llevo bastantes años intentando explicar que para los informes debemos ELIMINAR la salida PDF y lo que debemos es producir los informes en Excel (o producto similar) por los siguientes motivos:

  • El usuario es capaz de pensar en Excel, hacednos la descripción de lo que necesita en Excel y validarnos rápidamente  la solución si está en Excel. Excel lo conoce cualquier usuario que trabaja en una oficina/empresa.
  • El usuario es capaz de modificar el informe si está en Excel, por lo que nos va a solicitar muchos menos cambios que los que habitualmente se solicitan si la salida es PDF o papel.
  • El usuario puede, si está en Excel, utilizar los datos para hacer nuevos informes o estudiar nuevas formas de presentar sus datos, sin requerir del informático para nada.
  • Excel es una herramienta muy productiva, capaz de hacer estudios y presentaciones de datos (gráficos) que muchos productos muy caros no son capaces de obtener.

Muchos de los productos con éxito en el mercado, en parte, su éxito es debido a que son capaces de exportar los datos del sistema a Excel (por ejemplo los productos de la compañía SAP).

Si nos fijamos en la situación de PHPRunner y de su historia, vemos que la parte de los informes está floja, con poco desarrollo, pero siempre ha tenido una solución sencilla de exportar los datos a Excel, aunque muy básica, por lo que en aplicaciones grandes, la parcela de los informes se nos quedaba un poco “en el aire”.

En el ejemplo que pongo a vuestra disposición en este artículo, he ampliado la funcionalidad de un desarrollo PHPRunner con 1 solución de creación de informes en Excel:

  • PHPoffice/phpspreadsheet.  Esta solución nos permite crear ficheros Excel (desde plantillas) con características de informe, pudiéndose añadir gráficos, etc. Es open source y totalmente hecha en PHP.

En esta web podrás encontrar otras alternativas, como:

Explicación  del conjunto de datos que vamos a utilizar en el ejemplo:

Para el ejercicio he utilizado un conjunto de información de países, provincias de España y municipios de cada una de las provincias. Son datos con poco significado y públicos. Los he utilizado por:

  • Tiene dependencia entre ellos.
  • Tienen un cierto volumen, para estudiar tiempos de respuesta de la solución ( 8.122 Municipios).

En el código que facilito hay muchas líneas comentadas. Estas líneas suelen ser posibilidades o alternativas a la solución del ejemplo y he creído que os pueden ayudar en vuestra búsqueda de la solución que requerís.

 

Las consultas son simples, lo único que tienen es un nuevo botón que lanza la ejecución de informe (Excel). Hay 5 ejemplos con características diferentes, para que obtengáis una más amplia visión de la solución.

Podéis probar el ejemplo en https://fhumanes.com/reports/.

Los informes que se obtienen son de este tipo, en donde tienen su cabecera, fecha de informe y paginación.

En este caso, se ha incluido un gráfico que se actualiza con los nuevos datos.

Otro de los informes (Municipios), produce un listado de 161 páginas y 192 (en formato de ruptura en cabecera) y desde mi punto de vista, lo hace bastante rápido.

Método utilizado en la construcción de los informes

Para aquellos que estudiéis las plantillas, indicaros que el método utilizado es:

  • Creo dos hojas (“Hoja1” y “Hoja2”). En Hoja1 voy a presentar los datos para el informe y en Hoja2 la voy a utilizar para pegar los datos que obtengo de la base de datos.
  • En Hoja1, tengo los campos con la fórmula de “INDICE” que me permite referenciar los datos de la Hoja2 (No duplico para que no ocupe más espacio que el justo necesario).
  • En Hoja1 duplico las líneas según los registros, para que copie el formato en todas ellas.
  • En Hoja1 actualizo la fórmula para que en cada caso referencie la fila que corresponde. Esto lo hago en un ARRAY, porque las operaciones masivas son más rápidas que las individuales.

Los códigos de cada informe están en “Custom File” de PHPRunner, debajo del directorio “MyCode”. Os muestro algunos de ellos:

Informe de Paises
<?php
@ini_set("display_errors","1");
@ini_set("display_startup_errors","1");
require_once("../../include/dbcommon.php");

require_once __DIR__ . '/../phpspreadsheet_dompdf_1.29.0/autoload.php'; 

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

// to use mitoteam/jpgraph
Settings::setChartRenderer(\PhpOffice\PhpSpreadsheet\Chart\Renderer\MtJpGraphRenderer::class);

//  Template processor instance creation

//  $reader = IOFactory::createReader('Xlsx');
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
// $reader->setReadDataOnly(true);
$reader->setIncludeCharts(true);
// $reader->setReadFilter();
// $reader->setReadEmptyCells(true);

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

// Recuperar datos Cabecera

// Variables on different parts of document
$spreadsheet->setActiveSheetIndex(0);

$DateList=now();
$spreadsheet->getActiveSheet()->setCellValue('D2', Date::PHPToExcel($DateList));

$data = array();			// All Records
$rs = DB::Query("SELECT idrp_pais, CodigoPais, NombrePais, IndicadorBaja FROM rp_pais");
while( $row = $rs->fetchNumeric() )
{
    $data[] = $row;
}
$dataRows = count($data);

$spreadsheet->setActiveSheetIndex(1);
$spreadsheet->getActiveSheet()->fromArray($data, null, 'A2'); //  Fill all rows in a single function

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

$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->insertNewRowBefore(5, ($dataRows-1)); // Copy presentation formats
$spreadsheet->getActiveSheet()->removeRow(($dataRows-1+5), 1); // Delete to last

$spreadsheet->addNamedRange(new NamedRange('DatosPrint001', $spreadsheet->getActiveSheet(), '$A$3:'.'$D$'.($dataRows+2)));

// Reference the data
$formulaA4 = $spreadsheet->getActiveSheet()->getCell('A4')->getValue();
$formulaB4 = $spreadsheet->getActiveSheet()->getCell('B4')->getValue();
$formulaC4 = $spreadsheet->getActiveSheet()->getCell('C4')->getValue();
$formulaD4 = $spreadsheet->getActiveSheet()->getCell('D4')->getValue();

$dataArray = [];
for ($x = 4; $x <= $dataRows+3; $x++) {
    $dataArray[] = [str_replace("NumRow", $x-3, $formulaA4),
                  str_replace("NumRow", $x-3, $formulaB4),
                  str_replace("NumRow", $x-3, $formulaC4),
                  str_replace("NumRow", $x-3, $formulaD4)];
} 
$spreadsheet->getActiveSheet()->fromArray($dataArray, null, 'A4'); // Copy formulas

/*
// Create file PDF

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

// IOFactory::registerWriter('Pdf', \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf::class);
$className = \PhpOffice\PhpSpreadsheet\Writer\Pdf\Dompdf::class;
// $helper->log("Write to PDF format using {$className}");
IOFactory::registerWriter('Pdf', $className);

// Redirect output to a client’s web browser (PDF)
header('Content-Type: application/pdf');
header('Content-Disposition: attachment;filename="paises.pdf"');
header('Cache-Control: max-age=0');

$writer = IOFactory::createWriter($spreadsheet, 'Pdf');
$writer->save('php://output');
*/

// 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="paises.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->setPreCalculateFormulas(false); //Disable formula validation
$writer->save($temp_file);

// ------------------ Operation with file result -------------------------------------------
$documento = file_get_contents($temp_file);
unlink($temp_file);  // delete file tmp


echo $documento;

// Alternative method, but also create a temporary file
// $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
// $writer->save('php://output');
?>
Informe por Municipios
<?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__ . '/../phpspreadsheet_dompdf_1.29.0/autoload.php'; 

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

// to use mitoteam/jpgraph
Settings::setChartRenderer(\PhpOffice\PhpSpreadsheet\Chart\Renderer\MtJpGraphRenderer::class);

//  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__ . '/template.xlsx'); //  read template

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

$DateList=now();
$spreadsheet->getActiveSheet()->setCellValue('E2', Date::PHPToExcel($DateList)); //  Fill in a cell with the current date in excel format

$data = array();			// All Records
$rs = DB::Query(
"SELECT m.idrp_municipio, concat(m.CodigoProvincia,' - ',p.NombreProvincia) Provincia, m.CodigoMunicipio, m.NombreMunicipio
FROM rp_municipio m
join rp_provincia p on (m.CodigoProvincia = p.CodigoProvincia) ");
while( $row = $rs->fetchNumeric() )
{
    $data[] = $row;
}
$dataRows = count($data);

$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(), '=$A$2:$D$'.($dataRows-1+2)));

$spreadsheet->setActiveSheetIndex(0); // Activate the first sheet
$spreadsheet->getActiveSheet()->insertNewRowBefore(5, ($dataRows-1)); // Copy presentation formats
$spreadsheet->getActiveSheet()->removeRow(($dataRows-1+5), 1); // Delete last row

$spreadsheet->addNamedRange(new NamedRange('DatosPrint001', $spreadsheet->getActiveSheet(), '=$A$3:$E$'.($dataRows+3))); //  Define print range

// Presentation Field Formulas
$formulaA4 = $spreadsheet->getActiveSheet()->getCell('A4')->getValue();
$formulaB4 = $spreadsheet->getActiveSheet()->getCell('B4')->getValue();
$formulaC4 = $spreadsheet->getActiveSheet()->getCell('C4')->getValue();
$formulaD4 = $spreadsheet->getActiveSheet()->getCell('D4')->getValue();
$formulaE4 = $spreadsheet->getActiveSheet()->getCell('E4')->getValue();

// Build the formulas with definitive references
$dataArray = [];
for ($x = 4; $x <= $dataRows+3; $x++) {
    $dataArray[] = [
                  str_replace("NumRow", $x-3, $formulaA4),
                  str_replace("NumRow", $x-3, $formulaB4),
                  str_replace("NumRow", $x-3, $formulaC4),
                  str_replace("NumRow", $x-3, $formulaD4),
                  str_replace("NumRow", $x-3, $formulaE4)];
} 
$spreadsheet->getActiveSheet()->fromArray($dataArray, null, 'A4'); // Copy formulas in all fils

// Break page by row
$spreadsheet->setActiveSheetIndex(0);

$Ruptura=$data[0][1];
for ($x = 0; $x <= $dataRows; $x++) {
    if ($Ruptura <> $data[$x][1] ) {
        $Ruptura=$data[$x][1];
        $spreadsheet->getActiveSheet()->setBreak('A'.($x+3), \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW); // Rupture for printing. Page break
    }
}

// Break page by column
//$spreadsheet->getActiveSheet()->setBreak('C10', \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_COLUMN);

// 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(85);
// $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() . '&RPágina &P de &N');

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

// Specify printing area
$spreadsheet->getActiveSheet()->getPageSetup()->setPrintArea('A1:'.'E'.($dataRows+3));

// 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= Municipios.xlsx");
header('Content-Type: application/excel');
echo $documento;

// Alternative method, but also creates a temporary
// $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
// $writer->save('php://output');
?>
Informe por Provincias con gráficos
<?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__ . '/../phpspreadsheet_dompdf_1.29.0/autoload.php'; 

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Settings;
use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\Legend;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Title;

// to use mitoteam/jpgraph
Settings::setChartRenderer(\PhpOffice\PhpSpreadsheet\Chart\Renderer\MtJpGraphRenderer::class);

use PhpOffice\PhpSpreadsheet\Helper\Sample;
$helper = new Sample();

// setlocale(LC_ALL, 'en_US');
// $locale = 'en_US'; //  'es_ES'
// $validLocale = \PhpOffice\PhpSpreadsheet\Settings::setLocale($locale); // For read Formulas

//  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__ . '/template.xlsx'); //  read template

/* Test read Charts
foreach ($spreadsheet->getWorksheetIterator() as $worksheet) {
        $sheetName = $worksheet->getTitle();
        $chartNames = $worksheet->getChartNames();
}
*/

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

$DateList=now();
$spreadsheet->getActiveSheet()->setCellValue('D2', Date::PHPToExcel($DateList)); //  Fill in a cell with the current date in excel format

// Capture data from the BD
$data = array();			// All Records
$rs = DB::Query(
"SELECT p.`idrp_provincia`, p.`CodigoProvincia`, p.`NombreProvincia`, count(m.CodigoProvincia) NumMunicipio
FROM rp_provincia p
join rp_municipio m on (p.CodigoProvincia = m.CodigoProvincia)
group by 1,2,3
order by 4 desc");
while( $row = $rs->fetchNumeric() )
{
    $data[] = $row;
}
$dataRows = count($data);

$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(), '=$A$2:$D'.($dataRows-1+2)));

$spreadsheet->setActiveSheetIndex(0); // Activate the first sheet
$spreadsheet->getActiveSheet()->insertNewRowBefore(24, ($dataRows-1)); // Copy presentation formats
// $spreadsheet->getActiveSheet()->removeRow(($dataRows-1+24), 1); // Delete last row
for ($x = 1; $x <= 5; $x++) {$spreadsheet->getActiveSheet()->setCellValueByColumnAndRow($x,($dataRows-1+24),'');} // Clear last row

$spreadsheet->addNamedRange(new NamedRange('DatosPrint001', $spreadsheet->getActiveSheet(), '$A$3:'.'$D$'.($dataRows+22))); //  Define print range

// Presentation Field Formulas
$formulaA4 = $spreadsheet->getActiveSheet()->getCell('A23')->getValue();
$formulaB4 = $spreadsheet->getActiveSheet()->getCell('B23')->getValue();
$formulaC4 = $spreadsheet->getActiveSheet()->getCell('C23')->getValue();
$formulaD4 = $spreadsheet->getActiveSheet()->getCell('D23')->getValue();


// Build the formulas with definitive references
$dataArray = [];

for ($x = 23; $x <= $dataRows+22; $x++) {
    $dataArray[] = [
      str_replace("NumRow", $x-22, $formulaA4),
                  str_replace("NumRow", $x-22, $formulaB4),
                  str_replace("NumRow", $x-22, $formulaC4),
                  str_replace("NumRow", $x-22, $formulaD4)];
} 
$spreadsheet->getActiveSheet()->fromArray($dataArray, null, 'A23'); // Copy formulas in all fils

// Break page by row
$x = $dataRows-1 ;  // of 0 - all record
$y = 23;            // displacement
$w = 28;            // firts page break
$z = 66;            // second page break
$a = 0;             // Loop records
$c = 0;             // Loop rows
$spreadsheet->setActiveSheetIndex(0);

for (;;) { 
    for ($b = 0; $b <= $z ; $b++ ) {
        if ($a == $w || $b == $z ){ // Break Page
                $spreadsheet->getActiveSheet()->setBreak('A'.($c+$y-1), \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW);
                $spreadsheet->getActiveSheet()->insertNewRowBefore(($c+$y),1); // Insert row
                for ($d= 0; $d <= 4; $d++){ // Copy Head
                     $ref1=chr((ord('A')+$d)).($c+$y); // Destino
                     $ref2=chr((ord('A')+$d)).($y-1);  // Origen
                     $spreadsheet->getActiveSheet()->setCellValue($ref1,$spreadsheet->getActiveSheet()->getCell($ref2)); // Valores
                     $spreadsheet->getActiveSheet()->duplicateStyle($spreadsheet->getActiveSheet()->getStyle($ref2),$ref1); // Estilo
                }
                $a++;
                $c++;               
                break;
        }
        $a++;
        $c++;
        if ($a > $x) { break;} // End of records
    }
    if ($a > $x) { break;} // End of records
}

// Break page by column
//$spreadsheet->getActiveSheet()->setBreak('C10', \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_COLUMN);

// ---- Area of printing ------------------

// 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(100);
// $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() . '&RPágina &P de &N');

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

// Specify printing area
$spreadsheet->getActiveSheet()->getPageSetup()->setPrintArea('A1:'.'D'.($dataRows+23));


// --------------------------------------------------------------------------------------------
// 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->setIncludeCharts(true); // For Charts
$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= Municipios.xlsx");
header('Content-Type: application/excel');
echo $documento;
*/
// Alternative method, but also creates a temporary
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setIncludeCharts(true); // For Charts
$writer->setPreCalculateFormulas(false); // Disable formula validation - to improve response time.
header("Content-Disposition: attachment; filename= Municipios.xlsx");
header("Content-Transfer-Encoding: binary");
header('Content-Type: application/excel');
$writer->save('php://output');
?>
Informe por Municipios, ruptura con cabecera rápido
<?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__ . '/../phpspreadsheet_dompdf_1.29.0/autoload.php'; 

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

// to use mitoteam/jpgraph
Settings::setChartRenderer(\PhpOffice\PhpSpreadsheet\Chart\Renderer\MtJpGraphRenderer::class);


//  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__ . '/template.xlsx'); //  read template

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

$DateList=now();
$spreadsheet->getActiveSheet()->setCellValue('E2', Date::PHPToExcel($DateList)); //  Fill in a cell with the current date in excel format

// Capture data from the BD
$data = array();			// All Records
$rs = DB::Query(
"SELECT m.idrp_municipio, m.CodigoProvincia Provincia, m.CodigoMunicipio, m.NombreMunicipio
FROM rp_municipio m
-- limit 500   
");
while( $row = $rs->fetchNumeric() )
{
    $data[] = $row;
}
$dataRows = count($data);

Calculation::getInstance($spreadsheet)->disableCalculationCache();

$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(), '$A$2:'.'$D$'.($dataRows-1+2)));

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

// Presentation Field Formulas
$formulaA4 = $spreadsheet->getActiveSheet()->getCell('A6')->getValue();
$formulaB4 = $spreadsheet->getActiveSheet()->getCell('B6')->getValue();
$formulaC4 = $spreadsheet->getActiveSheet()->getCell('C6')->getValue();
$formulaD4 = $spreadsheet->getActiveSheet()->getCell('D6')->getValue();

// Build the formulas with definitive references

$dataFormula = [];
for ($x = 0; $x <= $dataRows; $x++) {
    $dataFormula[] = [
      str_replace("NumRow", $x+1, $formulaA4),
      str_replace("NumRow", $x+1, $formulaB4),
      str_replace("NumRow", $x+1, $formulaC4),
      str_replace("NumRow", $x+1, $formulaD4)
                        ];

} 

// Break page by row
$spreadsheet->setActiveSheetIndex(0);

$Ruptura=$data[0][1];
DatosCabecera($Ruptura); // Recupera datos de cabecera
// Primera Cabecera
$spreadsheet->setActiveSheetIndex(2);
$cabecera_001 = $spreadsheet->getActiveSheet()->getCell('C3')->getValue();
$cabecera_002 = $spreadsheet->getActiveSheet()->getCell('D3')->getValue();
$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->setCellValue('C3', $cabecera_001);
$spreadsheet->getActiveSheet()->setCellValue('D3', $cabecera_002);

$z = 0; // Cantidad de Cabeceras
$w = 5+1; // Líneaas de Cabecera
$l = 0; // Control de línea de la página
$lT = 48; // Líneas en una página
for ($x = 0; $x < $dataRows; $x++) {

    if ($Ruptura <> $data[$x][1] ) { // Cambio datosd de Cabecera.       
            
        $Ruptura=$data[$x][1];
        if ($Ruptura <> Null){
            custom_error(1011,"Inicio de abrir 'Cabecera Nueva' "); // To debug
            DatosCabecera($Ruptura); // Recupera datos de cabecera
            $fila= (($x+5)+($z*$w))+1;
            $spreadsheet->getActiveSheet()->setBreak('A'.$fila, \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW); // Rupture for printing. Page break
            PonCabecera(($x+5)+($z*$w)+2);
            $z = $z+1;
            $l = -1; // Incializamos líneas
            custom_error(1012,"Final de abrir 'Cabecera Nueva' "); // To debug
        }
    }
    if ($l === $lT){ // Ha llegado a líneas máximas de página
        custom_error(1001,"Inicio de abrir 'Cabecera' "); // To debug
        $fila= (($x+5)+($z*$w))+1;
        $spreadsheet->getActiveSheet()->setBreak('A'.$fila, \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW); // Rupture for printing. Page break

        PonCabecera(($x+5)+($z*$w)+2);
        $z = $z+1;
        $l = -1; // Incializamos líneas
        custom_error(1002,"Final de abrir 'Cabecera' "); // To debug
    }
    $nrow = (($x+5)+($z*$w)+1);

    copyStyleLine($nrow, 5);  // Copy Style of line master
    $spreadsheet->getActiveSheet()->setCellValue('A'.$nrow,  $dataFormula[$x][0]);
    $spreadsheet->getActiveSheet()->setCellValue('B'.$nrow,  $dataFormula[$x][1]);
    $spreadsheet->getActiveSheet()->setCellValue('C'.$nrow,  $dataFormula[$x][2]);
    $spreadsheet->getActiveSheet()->setCellValue('D'.$nrow,  $dataFormula[$x][3]);
    $l = $l+1;   
}

// 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(85);
// $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() . '&RPágina &P de &N');

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

// Specify printing area
$spreadsheet->getActiveSheet()->getPageSetup()->setPrintArea('A1:'.'E'.($dataRows+5+($z*$w)));

// 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= Municipios.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

// Accede a Base de datos para recuperar los datos de cabecera
function DatosCabecera($Ruptura){
global $spreadsheet;
custom_error(101,"Inicio Function 'Datos Cabecera' ".$Ruptura); // To debug
$resql = DB::Query("SELECT CodigoProvincia, NombreProvincia FROM rp_provincia  WHERE idrp_provincia = $Ruptura");
$row = $resql->fetchAssoc();
$spreadsheet->setActiveSheetIndex(2); // Activate the first sheet
$spreadsheet->getActiveSheet()->setCellValue('D3', $row['CodigoProvincia'].' - '.$row['NombreProvincia']); //  Fill in a cell with Datos de ruptura
$spreadsheet->setActiveSheetIndex(0); // Activate the first sheet
custom_error(102,"Final Function 'Datos Cabecera' ".$Ruptura); // To debug
return(true);
}



// Pon Cabecera en página
function PonCabecera($Row){
global $spreadsheet;

custom_error(111,"Inicio Function 'Pon Cabecera' ".$Row); // To debug

$dataArray = $spreadsheet->getActiveSheet() // Copia
    ->rangeToArray(
        'A1:E5',     // The worksheet range that we want to retrieve
        NULL,        // Value that should be returned for empty cells
        TRUE,        // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell)
        TRUE,        // Should values be formatted (the equivalent of getFormattedValue() for each cell)
        TRUE         // Should the array be indexed by cell row and cell column
    );
$spreadsheet->getActiveSheet() //Pega
    ->fromArray(
        $dataArray,  // The data to set
        NULL,        // Array values with this value will not be set
        'A'.$Row     // Top left coordinate of the worksheet range where
                     //    we want to set these values (default is A1)
    );

// Copia de Styles
for ($a= 0; $a <= 4; $a++){         // Columnas
    for ($b= 0; $b <= 5; $b++){     // Filas 
        $ref1=chr((ord('A')+$a)).($Row-1+$b); // Destino
        $ref2=chr((ord('A')+$a)).(0+$b);  // Origen
        $spreadsheet->getActiveSheet()->duplicateStyle($spreadsheet->getActiveSheet()->getStyle($ref2),$ref1); // Estilo}
    }                    
}

$spreadsheet->setActiveSheetIndex(2); // Activate template

$dataArray = $spreadsheet->getActiveSheet() // Copia
    ->rangeToArray(
        'A3:E3',     // The worksheet range that we want to retrieve
        NULL,        // Value that should be returned for empty cells
        TRUE,        // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell)
        TRUE,        // Should values be formatted (the equivalent of getFormattedValue() for each cell)
        TRUE         // Should the array be indexed by cell row and cell column
    );

$spreadsheet->setActiveSheetIndex(0); // Activate the first sheet
$fila = $Row+2;
$spreadsheet->getActiveSheet() //Pega
    ->fromArray(
        $dataArray,  // The data to set
        NULL,        // Array values with this value will not be set
        'A'.$fila     // Top left coordinate of the worksheet range where
                     //    we want to set these values (default is A1)
    );

custom_error(112,"Final Function 'Pon Cabecera' "); // To debug
return(true);
}

// Copia de Styles
function copyStyleLine($line, $rows = 1){
    global $spreadsheet;
    $spreadsheet->setActiveSheetIndex(0); // Activate the first sheet
    $lineOrigen = 6;
    if ( $line == $lineOrigen ) { return(true); }
    
    for ($a= 0; $a <= $rows ; $a++){         // Columnas    
        $ref1=chr((ord('A')+$a)).($line); // Destino
        $ref2=chr((ord('A')+$a)).($lineOrigen );  // Origen
        $spreadsheet->getActiveSheet()->duplicateStyle($spreadsheet->getActiveSheet()->getStyle($ref2),$ref1); // Estilo}                   
    }
    
    return(true);
}
?>
Informe por Municipios, ruptura con cabecera lento
<?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__ . '/../phpspreadsheet_dompdf_1.29.0/autoload.php'; 

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

// to use mitoteam/jpgraph
Settings::setChartRenderer(\PhpOffice\PhpSpreadsheet\Chart\Renderer\MtJpGraphRenderer::class);

//  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__ . '/template.xlsx'); //  read template

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

$DateList=now();
$spreadsheet->getActiveSheet()->setCellValue('E2', Date::PHPToExcel($DateList)); //  Fill in a cell with the current date in excel format

// Capture data from the BD
$data = array();			// All Records
$rs = DB::Query(
"SELECT m.idrp_municipio, m.CodigoProvincia Provincia, m.CodigoMunicipio, m.NombreMunicipio
FROM rp_municipio m
limit 4000   
");
while( $row = $rs->fetchNumeric() )
{
    $data[] = $row;
}
$dataRows = count($data);

$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(), '$A$2:'.'$D$'.($dataRows-1+2)));

$spreadsheet->setActiveSheetIndex(0); // Activate the first sheet
$spreadsheet->getActiveSheet()->insertNewRowBefore(7, ($dataRows-1)); // Copy presentation formats
$spreadsheet->getActiveSheet()->removeRow(($dataRows-1+7), 1); // Delete last row

$spreadsheet->addNamedRange(new NamedRange('DatosPrint001', $spreadsheet->getActiveSheet(), '$A$6:'.'$E$'.($dataRows+3))); //  Define print range

// Presentation Field Formulas
$formulaA4 = $spreadsheet->getActiveSheet()->getCell('A6')->getValue();
$formulaB4 = $spreadsheet->getActiveSheet()->getCell('B6')->getValue();
$formulaC4 = $spreadsheet->getActiveSheet()->getCell('C6')->getValue();
$formulaD4 = $spreadsheet->getActiveSheet()->getCell('D6')->getValue();

// Build the formulas with definitive references
$dataArray = [];
for ($x = 2; $x <= $dataRows+1; $x++) {
    $dataArray[] = [
      str_replace("NumRow", $x-1, $formulaA4),
      str_replace("NumRow", $x-1, $formulaB4),
      str_replace("NumRow", $x-1, $formulaC4),
      str_replace("NumRow", $x-1, $formulaD4)];

} 
$spreadsheet->getActiveSheet()->fromArray($dataArray, null, 'A6'); // Copy formulas in all fils

// Break page by row
$spreadsheet->setActiveSheetIndex(0);

$Ruptura=$data[0][1];
DatosCabecera($Ruptura); // Recupera datos de cabecera
// Primera Cabecera
$spreadsheet->setActiveSheetIndex(2);
$cabecera_001 = $spreadsheet->getActiveSheet()->getCell('C3')->getValue();
$cabecera_002 = $spreadsheet->getActiveSheet()->getCell('D3')->getValue();
$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->setCellValue('C3', $cabecera_001);
$spreadsheet->getActiveSheet()->setCellValue('D3', $cabecera_002);

$z = 0; // Cantidad de Cabeceras
$w = 5+1; // Líneaas de Cabecera
$l = 0; // Control de línea de la página
$lT = 48; // Líneas en una página
for ($x = 0; $x <= $dataRows; $x++) {

        if ($Ruptura <> $data[$x][1] ) { // Cambio datosd de Cabecera.       
            
            $Ruptura=$data[$x][1];
            if ($Ruptura <> Null){
                 custom_error(1011,"Inicio de abrir 'Cabecera Nueva' "); // To debug
                DatosCabecera($Ruptura); // Recupera datos de cabecera
                $fila= (($x+5)+($z*$w))+1;
                $spreadsheet->getActiveSheet()->insertNewRowBefore((($x+5)+($z*$w)+1), $w); // Insert Líneas de Cabecera
                $spreadsheet->getActiveSheet()->setBreak('A'.$fila, \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW); // Rupture for printing. Page break
                PonCabecera(($x+5)+($z*$w)+2);
                $z = $z+1;
                $l = -1; // Incializamos líneas
                 custom_error(1012,"Final de abrir 'Cabecera Nueva' "); // To debug
            }
         }
        if ($l === $lT){ // Ha llegado a líneas máximas de página
             custom_error(1001,"Inicio de abrir 'Cabecera' "); // To debug
            $fila= (($x+5)+($z*$w))+1;
            $spreadsheet->getActiveSheet()->insertNewRowBefore((($x+5)+($z*$w)+1), $w); // Insert Líneas de Cabecera
            $spreadsheet->getActiveSheet()->setBreak('A'.$fila, \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW); // Rupture for printing. Page break
            
            PonCabecera(($x+5)+($z*$w)+2);
            $z = $z+1;
            $l = -1; // Incializamos líneas
             custom_error(1002,"Final de abrir 'Cabecera' "); // To debug
        }
        $l = $l+1;   
}

// Break page by column
//$spreadsheet->getActiveSheet()->setBreak('C10', \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_COLUMN);

// 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(85);
// $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() . '&RPágina &P de &N');

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

// Specify printing area
$spreadsheet->getActiveSheet()->getPageSetup()->setPrintArea('A1:'.'E'.($dataRows+5+($z*$w)));

// 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= Municipios.xlsx");
header('Content-Type: application/excel');
echo $documento;

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



// Accede a Base de datos para recuperar los datos de caecera
function DatosCabecera($Ruptura){
global $spreadsheet;
custom_error(101,"Inicio Function 'Datos Cabecera' "); // To debug
$resql = DB::Query("SELECT CodigoProvincia, NombreProvincia FROM rp_provincia  WHERE idrp_provincia = $Ruptura");
$row = $resql->fetchAssoc();
$spreadsheet->setActiveSheetIndex(2); // Activate the first sheet
$spreadsheet->getActiveSheet()->setCellValue('D3', $row['CodigoProvincia'].' - '.$row['NombreProvincia']); //  Fill in a cell with Datos de ruptura
$spreadsheet->setActiveSheetIndex(0); // Activate the first sheet
custom_error(102,"Final Function 'Datos Cabecera' "); // To debug
return(true);
}

// Pon Cabecera en página
function PonCabecera($Row){
global $spreadsheet;
custom_error(111,"Inicio Function 'Pon Cabecera' "); // To debug
$dataArray = $spreadsheet->getActiveSheet() // Copia
    ->rangeToArray(
        'A1:E5',     // The worksheet range that we want to retrieve
        NULL,        // Value that should be returned for empty cells
        TRUE,        // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell)
        TRUE,        // Should values be formatted (the equivalent of getFormattedValue() for each cell)
        TRUE         // Should the array be indexed by cell row and cell column
    );
$spreadsheet->getActiveSheet() //Pega
    ->fromArray(
        $dataArray,  // The data to set
        NULL,        // Array values with this value will not be set
        'A'.$Row     // Top left coordinate of the worksheet range where
                     //    we want to set these values (default is A1)
    );

// Copia de Styles
for ($a= 0; $a <= 4; $a++){         // Columnas
    for ($b= 0; $b <= 5; $b++){     // Filas 
        $ref1=chr((ord('A')+$a)).($Row-1+$b); // Destino
        $ref2=chr((ord('A')+$a)).(0+$b);  // Origen
        // $spreadsheet->getActiveSheet()->setCellValue($ref1,$spreadsheet->getActiveSheet()->getCell($ref2)); // Valores
        $spreadsheet->getActiveSheet()->duplicateStyle($spreadsheet->getActiveSheet()->getStyle($ref2),$ref1); // Estilo}
    }                    
}

$spreadsheet->setActiveSheetIndex(2); // Activate template
$dataArray = $spreadsheet->getActiveSheet() // Copia
    ->rangeToArray(
        'A3:E3',     // The worksheet range that we want to retrieve
        NULL,        // Value that should be returned for empty cells
        TRUE,        // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell)
        TRUE,        // Should values be formatted (the equivalent of getFormattedValue() for each cell)
        TRUE         // Should the array be indexed by cell row and cell column
    );

$spreadsheet->setActiveSheetIndex(0); // Activate the first sheet
$fila = $Row+2;
$spreadsheet->getActiveSheet() //Pega
    ->fromArray(
        $dataArray,  // The data to set
        NULL,        // Array values with this value will not be set
        'A'.$fila     // Top left coordinate of the worksheet range where
                     //    we want to set these values (default is A1)
    );

custom_error(112,"Final Function 'Pon Cabecera' "); // To debug
return(true);
}
?>
Informe por Provincia en formato Ficha
<?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__ . '/../phpspreadsheet_dompdf_1.29.0/autoload.php'; 

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

// to use mitoteam/jpgraph
Settings::setChartRenderer(\PhpOffice\PhpSpreadsheet\Chart\Renderer\MtJpGraphRenderer::class);

//  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__ . '/template.xlsx'); //  read template

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

$DateList=now();
$spreadsheet->getActiveSheet()->setCellValue('E2', Date::PHPToExcel($DateList)); //  Fill in a cell with the current date in excel format

// Capture data from the BD
$data = array();			// All Records
$rs = DB::Query(
"SELECT p.`idrp_provincia`, p.`CodigoProvincia`, p.`NombreProvincia`, count(m.CodigoProvincia) NumMunicipio
FROM rp_provincia p
join rp_municipio m on (p.CodigoProvincia = m.CodigoProvincia)
group by 1,2,3
order by 4 desc");
while( $row = $rs->fetchNumeric() )
{
    $data[] = $row;
}
$dataRows = count($data);

$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(), '$A$2:'.'$D$'.($dataRows-1+2)));

$spreadsheet->setActiveSheetIndex(0); // Activate the first sheet
/*
$spreadsheet->getActiveSheet()->insertNewRowBefore(7, ($dataRows-1)); // Copy presentation formats
$spreadsheet->getActiveSheet()->removeRow(($dataRows-1+7), 1); // Delete last row
*/

// Sheet of Templates Card
$spreadsheet->setActiveSheetIndex(2); // Activate the tree sheet
// Presentation Field Formulas
$formulas = array();
$formulas = ['codigo' => $spreadsheet->getActiveSheet()->getCell('D2')->getValue(),
             'provincia' => $spreadsheet->getActiveSheet()->getCell('D3')->getValue(),
             'municipios' => $spreadsheet->getActiveSheet()->getCell('D4')->getValue()];

$cab = 3; // Filas de cabecera
$z = 0; // Cantidad de Card
$w = 5+1; // Líneas de Card    

for ($x = 1 ; $x <= $dataRows; $x++) {

    custom_error(1011,"Inicio 'Card Nueva' "); // To debug
    DatosCard($x); // Recupera datos de Card
    
    // $spreadsheet->getActiveSheet()->insertNewRowBefore((($cab)+($z*$w)+1), $w); // Insert Líneas de Card
    PonCard(($cab)+($z*$w)+1);
    $z = $z+1;
    $fila= (($cab)+($z*$w));    
    if ($x < $dataRows ) {  // Last Card, not Break page
        $spreadsheet->setActiveSheetIndex(0); // Activate the first sheet
        $spreadsheet->getActiveSheet()->setBreak('A'.$fila, \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW); // Rupture for printing. Page break
    }


    custom_error(1012,"Final 'Card Nueva' "); // To debug 
}
$spreadsheet->setActiveSheetIndex(0); // Activate the first sheet

// Break page by column
//$spreadsheet->getActiveSheet()->setBreak('C10', \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_COLUMN);

// 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(85);
// $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() . '&RPágina &P de &N');

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

// Specify printing area
$spreadsheet->addNamedRange(new NamedRange('DatosPrint001', $spreadsheet->getActiveSheet(), '$A$3:'.'$E$'.(($cab)+($z*$w)+1))); //  Define print range
$spreadsheet->getActiveSheet()->getPageSetup()->setPrintArea('A1:'.'E'.($cab+($z*$w)));

// 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= Municipios2.xlsx");
header('Content-Type: application/excel');
echo $documento;

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



// Rellena los datos de las fichas
function DatosCard($x){
global $spreadsheet;
global $formulas;
custom_error(101,"Inicio Function 'Datos Card' "); // To debug

$data_arr = array();
$data_arr = [   str_replace("NumRow", $x, $formulas['codigo']),
    str_replace("NumRow", $x, $formulas['provincia']),
    str_replace("NumRow", $x, $formulas['municipios'])];

$spreadsheet->setActiveSheetIndex(2); // Activate the tree sheet
$spreadsheet->getActiveSheet()->setCellValue('D2', $data_arr[0]); //  Put formulas of data
$spreadsheet->getActiveSheet()->setCellValue('D3', $data_arr[1]); 
$spreadsheet->getActiveSheet()->setCellValue('D4', $data_arr[2]); 
$spreadsheet->setActiveSheetIndex(0); // Activate the first sheet
custom_error(102,"Final Function 'Datos Card' "); // To debug
return(true);
}

// Pon la ficha/card en página
function PonCard($Row){
global $spreadsheet;
custom_error(111,"Inicio Function 'Pon card' "); // To debug

$spreadsheet->setActiveSheetIndex(2); // Activate the tree sheet
$dataArray = array();
// Copy
$dataArray = $spreadsheet->getActiveSheet()->rangeToArray( 
        'A1:E5',     // The worksheet range that we want to retrieve
        NULL,        // Value that should be returned for empty cells
        false,        // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell)
        false,        // Should values be formatted (the equivalent of getFormattedValue() for each cell)
        false         // Should the array be indexed by cell row and cell column
    );
$spreadsheet->setActiveSheetIndex(0); // Activate the firt sheet
//Pega
$spreadsheet->getActiveSheet()->fromArray(
        $dataArray,  // The data to set
        NULL,        // Array values with this value will not be set
        'A'.$Row     // Top left coordinate of the worksheet range where
                     //    we want to set these values (default is A1)
    );
// Copia de Styles

for ($a= 0; $a <= 4; $a++){         // Columnas
    for ($b= 0; $b <= 5; $b++){     // Filas 
        $ref1=chr((ord('A')+$a)).($Row-1+$b); // Destino
        $ref2=chr((ord('A')+$a)).(0+$b);  // Origen
        $spreadsheet->setActiveSheetIndex(0)->duplicateStyle($spreadsheet->setActiveSheetIndex(2)->getStyle($ref2),$ref1);
    }                    
}

custom_error(112,"Final Function 'Pon Card' "); // To debug
return(true);
}

Como siempre, para cualquier duda o lo que necesitéis, gustosamente os atenderé a través de mi email [email protected].

Actualización: 13 de diciembre de 2023

He actualizado este ejemplo para actualizar la librería de confección de las hojas Excel (esta nueva versión requiere PHP 8.X), para independizarla del gestor de base de datos y para mejorar considerablemente en rendimiento para informes de muchos registros.

El informe de Municipios con ruptura y cabecera por Provincia lo he realizado siguiendo el método propuesto de insertar líneas para «arrastrar» los estilos de las filas, el que he llamado «lento» y el llamado «rápido» he utilizado el método de copiado de estilos, sin hacer inserciones de líneas.

Cuando inserta líneas, además de copiar los estilos revisa toda la hoja para recalcular las fórmulas y de ahí la tardanza que tiene una progresión exponencial del tiempo requerido según se va aumentando el número de registros.

Para 8.122 registros, la mejora es de al menos de 10 a 1.

Actualización: 17 de febrero de 2024

El formato de ficha (un registro una hoja) no lo tenía el ejemplo que había hecho y Rubén (un desarrollador de PHPRunner) me solicitó este tipo de Report, ya que en su empresa (como en casi todas) los empleados de la oficina diseñan y manejan con mucha destreza los informes en Excel.

He añadido (botón naranja) un nuevo informe en la opción de Provincias con este tipo de informe y he puesto en código en esta web y he actualizado el proyecto.

Ampliación: 24 de julio de 2024

En esta actualización se ha intentado mejorar la seguridad y la información que se facilita al usuario para indicar que el informe se está elaborando.

El botón de 3 estado queda codificado:

  • Client (before):
Swal.fire({
    // icon: 'info',
   title: 'El Informe se ha inciado y se está ejecutando',
   text: '',
   imageUrl: "MyCode/images/calculated.gif",
   imageHeight: 200,
   imageAlt: "Estamos trabajando",
   timer: 60000,
   timerProgressBar: true,
   toast: true,
   showConfirmButton: false,
   position:  'center', // "top-start",
   footer: ''
  })
  • Server:
$_SESSION['report_fase'] = 1;
$_SESSION['report_file'] = '';

include "MyCode/Report001/report.php";

$_SESSION['report_fase'] = 2;
$result['report_file'] = $_SESSION['report_file']
  • Cient (after):
window.open("MyCode/Report001/report.php","_blank" ); 

Swal.fire({
    // icon: 'info',
   title: 'El Informe se ha descargado',
   text: '',
   imageUrl: "MyCode/images/download.gif",
   imageHeight: 200,
    imageAlt: "Estamos trabajando",
   timer: 4000,
   timerProgressBar: true,
   toast: true,
   showConfirmButton: false,
   position:  'center', // "top-start",
   footer: ''
  })

Con este sistema el proceso de elaboración del informe se fracciona en 2 fases (la obtención y la descarga). A nivel de seguridad, no se puede ejecutar el informe aunque se conozca la URL del mismo y al usuario se le facilita el gráfico indicando que se está elaborando el informe

 

Adjuntos

Archivo Tamaño de archivo Descargas
zip backup de la base de datos 83 KB 1093
zip PHPRunner 10.7, Actualización del 17/02/2024 9 MB 1119
zip PHPRunner 10.91 - Actualización 24/07/2024 11 MB 159

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