{"id":1308,"date":"2020-09-12T19:45:08","date_gmt":"2020-09-12T18:45:08","guid":{"rendered":"https:\/\/fhumanes.es\/blog\/?page_id=1308"},"modified":"2021-08-28T18:29:40","modified_gmt":"2021-08-28T17:29:40","slug":"guia-2-para-noveles-en-phprunner","status":"publish","type":"page","link":"https:\/\/fhumanes.es\/blog\/guias-desarrollo\/guia-2-para-noveles-en-phprunner\/","title":{"rendered":"Gu\u00eda 2 \u2013 Export Excel"},"content":{"rendered":"<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" class=\"alignleft wp-image-1284\" src=\"https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/07\/guia1_008.png\" alt=\"\" width=\"150\" height=\"150\" srcset=\"https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/07\/guia1_008.png 200w, https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/07\/guia1_008-150x150.png 150w\" sizes=\"auto, (max-width: 150px) 100vw, 150px\" \/><\/figure>\n\n\n<p class=\"wp-block-paragraph\">En esta ocasi\u00f3n vamos a ver un m\u00e9todo para obtener hojas Excel m\u00e1s avanzadas que las exportaciones que nos ofrece PHPRunner.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><h4><strong>Objetivo<\/strong><\/h4><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">El ejemplo, muy sencillo, pero que entiendo puede ofrecer las bases para otras necesidades m\u00e1s complejas, se genera al requisito de producir una hoja Excel de datos de \u201cMaestro\u201d y \u201cDetalle\u201d, de un conjunto de datos.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><h4><strong>Datos<\/strong><\/h4><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">El modelo de datos del ejemplo es:<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"516\" height=\"351\" class=\"wp-image-1311 aligncenter\" src=\"https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_001.png\" alt=\"\" srcset=\"https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_001.png 516w, https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_001-300x204.png 300w\" sizes=\"auto, (max-width: 516px) 100vw, 516px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">Como vemos, tenernos las \u201c\u00d3rdenes\u201d de venta y dependiente de estas, tenemos el \u201cDetalle de las \u00d3rdenes\u201d con sus art\u00edculos y precios.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><h4><strong>Resultado<\/strong><\/h4><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Lo que deseamos obtener, en este caso, es un resultado en Excel de este tipo:<\/p>\n\n\n\n<div class=\"wp-block-image is-style-default\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_002.png\" alt=\"\" class=\"wp-image-1310\" width=\"687\" height=\"575\" srcset=\"https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_002.png 916w, https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_002-300x251.png 300w, https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_002-768x643.png 768w\" sizes=\"auto, (max-width: 687px) 100vw, 687px\" \/><\/figure><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>DEMO: <\/strong><a href=\"https:\/\/fhumanes.com\/novel2\/\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/fhumanes.com\/novel2\/<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><h4><strong>Soluci\u00f3n<\/strong><\/h4><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Como en otros ejemplos que tengo en mi portal, la soluci\u00f3n la orient\u00e9 para utilizar la librer\u00eda PHP de <strong>PHPoffice\/phpspreadsheet<\/strong>, que es la evoluci\u00f3n de PHPExcel, esta es la librer\u00eda PHP que trae PHPRunner para la creaci\u00f3n de las exportaciones a Excel.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Para simplificar la confecci\u00f3n, utilizo una plantilla de fichero Excel que tiene 3 hojas, las mismas que se ven en la soluci\u00f3n.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">La informaci\u00f3n se distribuye de la siguiente forma:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>Sheet1<\/strong>. Es donde se va a obtener los datos y presentaci\u00f3n que requiere el usuario.<\/li><li><strong>Sheet2<\/strong>. Es donde el programa PHP &nbsp;va a almacenar el conjunto de datos recuperado de la base de datos.<\/li><li><strong>Sheet3<\/strong>. <strong>&nbsp;<\/strong>Donde vamos adefinir la presentaci\u00f3n de los datos. Esto nos simplifica la programaci\u00f3n y adem\u00e1s cualquier usuario de Excel podr\u00eda modificar la plantilla, y por lo tanto, la presentaci\u00f3n del resultado sin que haya que cambiar nada en la programaci\u00f3n.<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">La plantilla tiene este aspecto:<\/p>\n\n\n\n<div class=\"wp-block-image is-style-default\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_003.png\" alt=\"\" class=\"wp-image-1309\" width=\"767\" height=\"322\" srcset=\"https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_003.png 1023w, https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_003-300x126.png 300w, https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_003-768x322.png 768w\" sizes=\"auto, (max-width: 767px) 100vw, 767px\" \/><\/figure><\/div>\n\n\n\n<div class=\"wp-block-image is-style-default\"><figure class=\"aligncenter size-large is-resized\"><a href=\"https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_004.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_004-1024x340.png\" alt=\"\" class=\"wp-image-1313\" width=\"768\" height=\"255\" srcset=\"https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_004-1024x340.png 1024w, https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_004-300x100.png 300w, https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_004-768x255.png 768w, https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_004-1536x510.png 1536w, https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_004.png 1714w\" sizes=\"auto, (max-width: 768px) 100vw, 768px\" \/><\/a><\/figure><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>(1)<\/strong> Utilizamos 2 \u201cNombres\u201d (definici\u00f3n de rangos de celdas) para:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>Datos01<\/strong>.- Definir la tabla del conjunto de datos que recuperamos de la base de datos.<\/li><li><strong>NumRow<\/strong>.- Para que todas las f\u00f3rmulas que utilicemos en la 3\u00aahoja (presentaci\u00f3n y c\u00e1lculos), nos va a indicar un \u00edndice sobre la tabla de los datos.<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>(2)<\/strong> Representa el nombre \u201c<strong>NumRow<\/strong>\u201d. Se puede poner en cualquier parte del cuaderno.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>(3)<\/strong> Representa el nombre \u201c<strong>Datos01<\/strong>\u201d y debe iniciarse en el punto en que est\u00e1. El n\u00famero de campos a recuperar puede crecer todo lo que se necesite, pero debemos tenerlo en cuenta en la codificaci\u00f3n PHP que despu\u00e9s veremos.<\/p>\n\n\n\n<div class=\"wp-block-image is-style-default\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_005-1024x527.png\" alt=\"\" class=\"wp-image-1312\" width=\"768\" height=\"395\" srcset=\"https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_005-1024x527.png 1024w, https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_005-300x154.png 300w, https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_005-768x395.png 768w, https:\/\/fhumanes.es\/blog\/wp-content\/uploads\/2020\/09\/novel2_005.png 1052w\" sizes=\"auto, (max-width: 768px) 100vw, 768px\" \/><\/figure><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>(1)<\/strong> Es la presentaci\u00f3n de los datos de la tabla \u201cMaestra\u201d.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>(2)<\/strong> Es la representaci\u00f3n de los datos de la tabla \u201cDetalle\u201d.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>(3)<\/strong> En todas las celdas que utilicemos los datos recuperados utilizamos esta f\u00f3rmula (que en la imagen se ve en espa\u00f1ol). Como vemos hace referencia al nombre del rango de la tabla de los datos recuperados, despu\u00e9s indica el n\u00famero de registro de la tabla y por \u00faltimo el n\u00famero de columna de la tabla.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Una vez que tenemos la plantilla, ya s\u00f3lo nos queda escribir el c\u00f3digo PHP que se debe ejecutar, bien mediante un bot\u00f3n o como en el caso del ejemplo, una opci\u00f3n del men\u00fa que realiza el informe.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><p>Para facilitar la codificaci\u00f3n he creado 2 funciones:<\/p><pre>\/\/ Function for copy row of sheet of Excel<br>function <span style=\"color: #993300;\">copy_row_excel<\/span>(int $num_page_orig, int $num_page_dest, int $num_row_orig, int $num_row_dest, int $count_row, int $limit_colum )<\/pre><div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container\"><div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container\"><p>Esta funci\u00f3n la utilizaremos para copiar las celdas desde \u201cSheet3\u201d a \u201cSheet1\u201d. Se copian contenidos y formatos o estilos.<\/p><pre>\/\/ Assign value to reference of formulas<br>function <span style=\"color: #993300;\">reference_data<\/span>(int $num_data, int $num_row, int $count_row, int $limit_colum )<\/pre><p>Esta funci\u00f3n sustituye en la formulas el literal \u201cNumRow\u201d, por la fila de la tabla de datos que corresponde.<\/p><\/div><\/div><\/div><\/div><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container\"><div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container\"><p>Muestro el c\u00f3digo completo de la creaci\u00f3n del informe<\/p><\/div><\/div><\/div><\/div><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"php\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">&lt;?php\n\n\/\/ Required by PHPRunner (security)\n@ini_set(\"display_errors\",\"1\");\n@ini_set(\"display_startup_errors\",\"1\");\nrequire_once(\"..\/..\/include\/dbcommon.php\");\n\n\/\/ Load the excel sheet management library classes\nrequire_once __DIR__ . '\/..\/..\/..\/ComponentCode\/PhpSpreadsheet_1.10\/autoload.php'; \n\nuse PhpOffice\\PhpSpreadsheet\\IOFactory;\nuse PhpOffice\\PhpSpreadsheet\\Shared\\Date;\nuse PhpOffice\\PhpSpreadsheet\\Spreadsheet;\nuse PhpOffice\\PhpSpreadsheet\\Writer\\Xlsx;\nuse PhpOffice\\PhpSpreadsheet\\NamedRange;\n\n\/\/  Template processor instance creation\n\n\/\/  $reader = IOFactory::createReader('Xlsx');\n$reader = new \\PhpOffice\\PhpSpreadsheet\\Reader\\Xlsx();\n\n\/\/  Options for reading excel files\n\/\/ $reader->setReadDataOnly(true);\n$reader->setIncludeCharts(true);\n\/\/ $reader->setReadFilter();\n\/\/ $reader->setReadEmptyCells(true);\n\n$spreadsheet = $reader->load(__DIR__ . '\/template1.xlsx'); \/\/  read template\n\n$spreadsheet->setActiveSheetIndex(0); \/\/ Activate the first sheet\n\n\/\/ Capture data from the BD\n$sql=\"\nSELECT\no.id id,\no.customer_name,\no.phone,\no.email,\no.address,\ncountod,\nod.id idod,\nod.product_name,\nod.price\nFROM `order` o\njoin order_details od on (o.id = od.order_id)\njoin ( select order_id, count(*) countod from order_details group by order_id ) T1 on (o.id = T1.order_id)\norder by o.id, od.id\n\";\n$result = $conn->query($sql);\n$data = $result->fetch_all(MYSQLI_NUM); \/\/ faster - All record\n$dataRows = count($data);\n\/\/ head\n$DateList=now();\n$spreadsheet->getActiveSheet()->setCellValue('F2'\n        . '', Date::PHPToExcel($DateList)); \/\/  Fill in a cell with the current date in excel format\n\n$spreadsheet->setActiveSheetIndex(1); \/\/ Activate the second sheet\n$spreadsheet->getActiveSheet()->fromArray($data, null, 'A2'); \/\/  Load ALL records in the active sheet\n\n\/\/ Define named ranges\n$spreadsheet->addNamedRange(new NamedRange('Datos01', $spreadsheet->getActiveSheet(), 'A2:'.'I'.($dataRows-1+2)));\n\n$master = NULL;\n$num_row = 3; \/\/ Firt line of Report\n\nfor ($x = 0; $x &lt; $dataRows; $x++) { \/\/ All Record\n    if ($master &lt;> $data[$x][0]) { \/\/ Change of Master\n        $master = $data[$x][0]; \/\/ New Master\n        \/\/ Copy template of page 3 to page 1\n        copy_row_excel(2, 0, 3, $num_row, 7, 6 );\n        $spreadsheet->setActiveSheetIndex(0); \/\/ Activate the first sheet\n        \/\/ $spreadsheet->getActiveSheet()->insertNewRowBefore($num_row+6, ($data[$x][5])); \/\/ Copy presentation formats\n        reference_data($x, $num_row+2, 1, 6); \/\/ Head of Order\n        $num_row = $num_row + 5;\n    }\n copy_row_excel(2, 0, 8, $num_row, 1, 6 );\n reference_data($x, $num_row, 1, 6); \/\/ for each record\n $num_row = $num_row + 1;\n}\n\n$spreadsheet->addNamedRange(new NamedRange('DatosPrint001', $spreadsheet->getActiveSheet(), 'A4:'.'F'.($num_row))); \/\/  Define print range\n\n\/\/ Show\/hide gridlines when printing\n$spreadsheet->getActiveSheet()->setShowGridlines(false); \/\/ Disable grid in print\n\n\/\/ Config print file Excel\n\/\/ $spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(\\PhpOffice\\PhpSpreadsheet\\Worksheet\\PageSetup::ORIENTATION_LANDSCAPE);\n$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(\\PhpOffice\\PhpSpreadsheet\\Worksheet\\PageSetup::ORIENTATION_PORTRAIT);\n$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(\\PhpOffice\\PhpSpreadsheet\\Worksheet\\PageSetup::PAPERSIZE_A4);\n\/\/ Page Setup: Scaling options\n$spreadsheet->getActiveSheet()->getPageSetup()->setFitToPage(false);\n$spreadsheet->getActiveSheet()->getPageSetup()->setScale(90);\n\/\/ $spreadsheet->getActiveSheet()->getPageSetup()->setFitToWidth(0); \n\/\/ $spreadsheet->getActiveSheet()->getPageSetup()->setFitToHeight(0);\n\n\/\/ Page margins\n\/\/ $spreadsheet->getActiveSheet()->getPageMargins()->setTop(1);\n\/\/ $spreadsheet->getActiveSheet()->getPageMargins()->setRight(0.6);\n\/\/ $spreadsheet->getActiveSheet()->getPageMargins()->setLeft(0.6);\n\/\/ $spreadsheet->getActiveSheet()->getPageMargins()->setBottom(1);\n\n\/\/ Center a page horizontally\/vertically\n$spreadsheet->getActiveSheet()->getPageSetup()->setHorizontalCentered(false);\n$spreadsheet->getActiveSheet()->getPageSetup()->setVerticalCentered(false);\n\n\/\/ Setting the print header and footer of a worksheet\n\/\/ $spreadsheet->getActiveSheet()->getHeaderFooter()->setOddHeader('&amp;C&amp;HPlease treat this document as confidential!');\n$spreadsheet->getActiveSheet()->getHeaderFooter()->setOddFooter('&amp;L&amp;B' . $spreadsheet->getProperties()->getTitle() . '&amp;RPage &amp;P of &amp;N');\n\n\/\/ Setting rows\/columns to repeat at top\/left\n$spreadsheet->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 2);\n\n\/\/ Specify printing area\n$spreadsheet->getActiveSheet()->getPageSetup()->setPrintArea('B1:'.'F'.($num_row));\n\n\/\/ Create file XLSX\n\n\/\/ Set active sheet index to the first sheet, so Excel opens this as the first sheet\n$spreadsheet->setActiveSheetIndex(0);\n\n\/\/ Redirect output to a client\u2019s web browser (Xlsx)\n\/\/ header('Content-Type: application\/vnd.openxmlformats-officedocument.spreadsheetml.sheet');\n\/\/ header('Content-Disposition: attachment;filename=\"municipios.xlsx\"');\n\/\/ header('Cache-Control: max-age=0');\n\/\/ If you're serving to IE 9, then the following may be needed\n\/\/ header('Cache-Control: max-age=1');\n\n\/\/ If you're serving to IE over SSL, then the following may be needed\nheader('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); \/\/ Date in the past\nheader('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); \/\/ always modified\nheader('Cache-Control: cache, must-revalidate'); \/\/ HTTP\/1.1\nheader('Pragma: public'); \/\/ HTTP\/1.0\n\n\/\/ Old system based on a temporary file\n\/\/ -------------------- v foot to save the new Excel document  ------------------\n$temp_file = tempnam(sys_get_temp_dir(), 'Excel');\n\/\/ Save EXCEL\n\/\/ $writer = new Xlsx($spreadsheet);\n$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');\n$writer->setPreCalculateFormulas(false); \/\/ Disable formula validation - to improve response time.\n$writer->save($temp_file);\n\n\/\/ ------------------ Operation with file result -------------------------------------------\n$documento = file_get_contents($temp_file);\nunlink($temp_file);  \/\/ delete file tmp\nheader(\"Content-Disposition: attachment; filename= List_order.xlsx\");\nheader('Content-Type: application\/excel');\necho $documento;\n\n\/\/ Alternative method, but also creates a temporary\n\/\/ $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');\n\/\/ $writer->save('php:\/\/output');\n\n\/\/ ----------------------------------- Functions ------------------------\n\/\/ Function for copy row of sheet of Excel\nfunction copy_row_excel(int $num_page_orig, int $num_page_dest, int $num_row_orig, int $num_row_dest, int $count_row, int $limit_colum ) {\n    global $spreadsheet;\n    $values = array();\n    $styles = array();\n\n    for ($a= 0; $a &lt; $count_row; $a++){\n        $spreadsheet->setActiveSheetIndex($num_page_orig); \/\/ Activate the  sheet\n        for ($b= 0; $b &lt; $limit_colum; $b++){\n        \/\/ Copy de Values\n            $ref=chr((ord('A')+$b)).($num_row_orig+$a);  \/\/ Origen\n            $values[$a][$b] =  $spreadsheet->getActiveSheet()->getCell($ref)->getValue(); \/\/ Value\n            $style =  $spreadsheet->getActiveSheet()->getStyle($ref); \/\/ Style\n            $style =  serialize($style); \/\/ Style\n            $styles[$a][$b] = $style; \/\/ Style\n        } \n    }\n    for ($a= 0; $a &lt; $count_row; $a++){\n        $spreadsheet->setActiveSheetIndex($num_page_dest); \/\/ Activate the  sheet\n        for ($b= 0; $b &lt; $limit_colum; $b++){\n        \/\/ Copy de Values\n            $ref=chr((ord('A')+$b)).($num_row_dest+$a);  \/\/ Origen\n            $spreadsheet->getActiveSheet()->setCellValue($ref,$values[$a][$b]); \/\/ Value\n            $style = unserialize($styles[$a][$b]); \/\/ Style\n            $spreadsheet->getActiveSheet()->duplicateStyle($style,$ref); \/\/ Style\n        } \n    }\n    return true;\n}\n\/\/ Assign value to reference of formulas\nfunction reference_data(int $num_data, int $num_row, int $count_row, int $limit_colum ) {\n    global $spreadsheet;\n    global $data;\n    $values = 0;\n    $spreadsheet->setActiveSheetIndex(0); \/\/ Activate the  sheet\n\n    for ($a= 0; $a &lt; $count_row; $a++){\n        for ($b= 0; $b &lt; $limit_colum; $b++){\n        \/\/ Copy de Values\n            $ref=chr((ord('A')+$b)).($num_row+$a); \n            $values =  $spreadsheet->getActiveSheet()->getCell($ref)->getValue(); \/\/ Value\n            $values = str_replace(\"NumRow\", $num_data+1 , $values);\n            switch ($b) {\n            case 2:\n                \/\/ $spreadsheet->getActiveSheet()->setCellValue($ref1,'=IF(U'.(6+$b).'&lt;>U'.(7+$b).',1,B'.(6+$b).'+1)'); \/\/ Valor\n                \/\/ break;\n            default:\n                $spreadsheet->getActiveSheet()->setCellValue($ref,$values); \/\/ Valor\n            }\n        } \n    }\n    return true;\n}\n?><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><div class=\"wp-block-group\">Adjunto, en el pie del art\u00edculo, todos los ficheros que requer\u00eds para desplegar el ejemplo en vuestros equipos. Aunque el ejemplo est\u00e1 en versi\u00f3n 10.4 de PHPrunner, esta codificaci\u00f3n valdr\u00eda para cualquier versi\u00f3n, siempre que el PHP sea versi\u00f3n 7.X<\/div><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><p>Para cualquier duda o aclaraci\u00f3n, por favor, escribidme un email a <a href=\"mailto:fernandohumanes@gmail.com\">fernandohumanes@gmail.com<\/a><\/p> <\/p>\n","protected":false},"excerpt":{"rendered":"<p>En esta ocasi\u00f3n vamos a ver un m\u00e9todo para obtener hojas Excel m\u00e1s avanzadas que las exportaciones que nos ofrece PHPRunner. Objetivo El ejemplo, muy sencillo, pero que entiendo puede ofrecer las bases para otras necesidades m\u00e1s complejas, se genera al requisito de producir una hoja Excel de datos de \u201cMaestro\u201d y \u201cDetalle\u201d, de un &hellip; <a href=\"https:\/\/fhumanes.es\/blog\/guias-desarrollo\/guia-2-para-noveles-en-phprunner\/\" class=\"more-link\">Seguir leyendo <span class=\"screen-reader-text\">Gu\u00eda 2 \u2013 Export Excel<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1254,"menu_order":11,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1308","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/fhumanes.es\/blog\/wp-json\/wp\/v2\/pages\/1308","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fhumanes.es\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/fhumanes.es\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/fhumanes.es\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fhumanes.es\/blog\/wp-json\/wp\/v2\/comments?post=1308"}],"version-history":[{"count":13,"href":"https:\/\/fhumanes.es\/blog\/wp-json\/wp\/v2\/pages\/1308\/revisions"}],"predecessor-version":[{"id":2085,"href":"https:\/\/fhumanes.es\/blog\/wp-json\/wp\/v2\/pages\/1308\/revisions\/2085"}],"up":[{"embeddable":true,"href":"https:\/\/fhumanes.es\/blog\/wp-json\/wp\/v2\/pages\/1254"}],"wp:attachment":[{"href":"https:\/\/fhumanes.es\/blog\/wp-json\/wp\/v2\/media?parent=1308"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}