This topic is locked

Reflection on reports in computer applications

12/7/2019 12:31:11 PM
PHPRunner Tips and Tricks
fhumanes author

In life, we usually move in our daily work, what we have done in previous situations.
When the computer applications started, users obtained their data in printed reports (on paper) and from there, studied and exploited them.
Many years ago (at least since 1995) that users have spreadsheets that allow them to study their data and present them to their team or their managers. At first they copied the data from the reports (paper) and later, the computer scientists were capable of general files csv, dbf, etc. that allowed them to exploit the data faster.
We have evolved a lot, but we still have enough. I think that we continue (almost all) with the idea that systems should produce PDF reports (which, similar to paper reports, seems more appropriate).
I have been trying for many years to explain that for the reports we must DELETE the PDF output and what we must do is produce the reports in Excel (or similar product) for the following reasons:

  • The user is able to think in Excel, give us the description of what he needs in Excel and quickly validate the solution if it is in Excel. Excel is known to any user who works in an office / company.
  • The user is able to modify the report if it is in Excel, so it will ask us for much less changes than those usually requested if the output is PDF or paper.
  • The user can, if in Excel, use the data to make new reports or study new ways of presenting their data, without requiring the computer at all.
  • Excel is a very productive tool, capable of making studies and presentations of data (graphics) that many very expensive products are not able to obtain.


Many of the products successfully in the market in part, their success is because they are able to export the system data to Excel (for example the products of the SAP company).
If we look at the situation of PHPRunner and its history, we see that the part of the reports is weak, with little development, but it has always had a simple solution to export the data to Excel, although very basic, so in applications large, the plot of reports we were a little "in the air."
In the example I put at your disposal in this article, I have expanded the functionality of a PHPRunner development with 2 reporting solutions:

  • PHPoffice/phpspreadsheet. This solution allows us to create Excel files (from templates) with report features, being able to add graphics, etc. It is open source and completely made in PHP.
  • Koolreport A report framework solution, open source, entirely made in PHP.


Koolreport I put it because a user of PHPRunner requested a proposal of how it could be integrated with PHPRunner and as I had the example, I have left it, in case more people think that this type of solution is what they need.
My solution proposal is PHPoffice/phpspreadsheet because it allows me to create Excel notebooks, with the data and presentation that I understand are necessary, in a simple, fast and very practical way.


For the exercise I used a set of information from countries, provinces of Spain and municipalities in each of the provinces. They are data with little meaning and public. I have used them for:

  • It has dependence on them.
  • They have a certain volume, to study response times of the solution.


In the code I provide there are many commented lines. These lines are usually possibilities or alternatives to the solution of the example and I have believed that they can help you in your search for the solution you require.
Also, as in other articles I have commented, the code libraries that I include in the PHPrunner developments, I usually leave them in a directory outside the project, because that makes it easier for me to develop the PHPRunner developments and also these libraries can be used by multiple projects In addition, it facilitates the maintenance or updating of them. Normally these libraries I put under a directory that I name as "ComponentCode".


The queries are simple, all they have is a new button that launches the report execution (Excel). There are 3 examples with different characteristics, so that you get a broader view of the solution.
You can try the example at https://fhumanes.com/reports/.
The reports obtained are of this type, where they have their header, report date and pagination.


In this case, a graphic has been included. In the case of the graphics, I have a problem that I cannot identify at the moment, it could be that my Windows is in Spanish or my version of Microsoft Excel 2007. I am studying it because even the product demos give me this error ( that Excel can recover it).
Another of the reports (Municipalities) produces a list of 161 pages and from my point of view, it does so quite quickly.
Method used in the construction of Template/Reports
For those of you who study the templates, indicate that the method used is:

  • I create two sheets ("Sheet1" and "Sheet2"). On Sheet1 I will present the data for the report and on Sheet2 I will use it to paste the data I obtain from the database.
  • On Sheet1, I have the fields with the formula of “INDEX” that allows me to reference the data on Sheet2 (I do not duplicate it so that it does not take up more space than is just necessary).
  • In Sheet1 I duplicate the lines according to the records, so that I copy the format in all of them.
  • In Sheet1 I update the formula so that in each case it references the corresponding row. I do this in an ARRAY, because massive operations are faster than individual ones.


The codes of each report are in "Custom File" of PHPRunner, under the "MyCode" directory. I show you some of them:
Report of Country



<?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 of 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 of Provincias





<?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 (;<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=26949&image=4&table=forumtopics' class='bbc_emoticon' alt=';)' /> {

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');

?>


As always, for any questions or what you need, I will gladly assist you through my email [email="fernandohumanes@gmail.com"]fernandohumanes@gmail.com[/email].
In my blog is the complete project to download it to your computer.
https://fhumanes.com/blog/impresos/crear-informes-con-phpspreadsheet/