Blog personal

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 2 soluciones de creación de informes:

  • 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.
  • Koolreport. Una solución de framework de report, open source, totalmente hecha en PHP.

Koolreport lo he puesto porque un usuario de PHPRunner solicitó una propuesta de cómo se podía integrar con PHPRunner y como tenía el ejemplo, lo he dejado, por si más personas piensa que ese tipo de solución es lo que necesitan.

Mi propuesta de solución es PHPoffice/phpspreadsheet porque me permite crear cuadernos Excel, con los datos y presentación que entiendo son necesarias, de forma sencilla, rápida y muy práctica.

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.

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.

También, como en otros artículos os he comentado, las librerías de código que incluyo en los desarrollos de PHPrunner las suelo dejar en un directorio fuera del proyecto, porque así se me hace más sencillo los desarrollos de PHPRunner y además dichas librerías pueden ser utilizadas por múltiples proyectos. Además, me facilita el mantenimiento o actualización de las mismas. Normalmente estas librerías las pongo bajo un directorio que nombro como “ComponentCode”.

Las consultas son simples, lo único que tienen es un nuevo botón que lanza la ejecución de informe (Excel). Hay 3 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 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:

Report de Paises
 
<?php
@ini_set("display_errors","1");
@ini_set("display_startup_errors","1");
require_once("../../include/dbcommon.php");
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();
// $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));
//  This is a way to pass the data to Excel
// read lines the Pais
// -      $sql="SELECT idrp_pais, CodigoPais, NombrePais, IndicadorBaja FROM rp_pais";
// -      $rsSql=db_query($sql,$conn);
// -      $baseRow = 5;
// -      $r=0;
// -      while ($data2 = db_fetch_array($rsSql)){
// -          $row = $baseRow + $r;
// -          $spreadsheet->getActiveSheet()->insertNewRowBefore($row, 1);
// -             $spreadsheet->getActiveSheet()->fromArray($data2, null, 'A'.$row);  
/*      $spreadsheet->getActiveSheet()->setCellValue('A' . $row, $data2['idrp_pais'])
        ->setCellValue('B' . $row, $data2['CodigoPais'])
        ->setCellValue('C' . $row, $data2['NombrePais'])
        ->setCellValue('D' . $row, $data2['IndicadorBaja']); 
*/
// -                  $r= $r+1;
// -            }
// -      $spreadsheet->getActiveSheet()->removeRow($baseRow - 1, 1);
// Another way to capture BD records
$result = $conn->query("SELECT idrp_pais, CodigoPais, NombrePais, IndicadorBaja FROM rp_pais");
$data = $result->fetch_all(MYSQLI_NUM); // faster All record
$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(), 'A2:'.'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(), 'A3:'.'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
// Save PDF
// $writer = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf($spreadsheet);
//$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= paises.pdf");
//header('Content-Type: application/pdf');
//echo $documento;
// 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');
?>
Report de 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__ . '/../../../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__ . '/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
$result = $conn->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) "
);
$data = $result->fetch_all(MYSQLI_NUM); // faster - All record
$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(), 'A2:'.'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(), 'A3:'.'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');
?>
Report 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__ . '/../../../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;
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;
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
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
$result = $conn->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"
);
$data = $result->fetch_all(MYSQLI_NUM); // faster - All record
$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(), 'A2:'.'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(), 'A3:'.'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+22));
// --------------------------------------------------------------------------------------------
// 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->save('php://output');
?>

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

 

Adjuntos

Archivo Tamaño de archivo Descargas
zip Librería del framework de koolreport 1 MB 93
zip Librería de PHPOffice/PhpSpreadsheet 18 MB 100
zip Proyecto PHPrunner 10.2 107 KB 87
zip backup de la base de datos 83 KB 90