Error generating excel with PHPExcel

0

I have a generic method that is responsible for generating the excel I need, what I send to the method is the resource of a query, the method is responsible for tracing the result to generate the excel with culumnas and corresponding rows.

As I mentioned several excel files of different queries, and today I am generating one more, the problem is that when I generate the excel at the beginning of the excel I attach a blank space or rather a blank line \ n, and this causes that the file with the office package is not visualized

So start the excel before this line there is a blank line,

PK    FušJG’D²X  ð     [Content_Types].xml­”MNÃ0…÷œ"ò%nY „švAa  •(0ö¤±êØ–gúw{&i‰@ÕnbEö{ßøyìÑdÛ¸l

In hexadecimal code, already identify the character

0d0a

I already researched about the character and what I found was what I mentioned in a bank line \ n or a new line that in PHP is PHP_EOL

var_dump(urlencode(PHP_EOL)); ?> 

Result: '%0D%0A'

And that's my code:

 <?php
   $parametro1 =  isset($_GET['a'])?$_REQUEST['a']:date('Y-m');
$parametro2 = isset($_REQUEST['b'])?$_REQUEST['b']:0;
$parametro3 = isset($_REQUEST['c'])?$_REQUEST['c']:0;
$parametro4 = isset($_REQUEST['d'])?$_REQUEST['d']:0;
$parametro5 = isset($_REQUEST['e'])?$_REQUEST['e']:0;
$parametro6 = $_REQUEST['f'];

$parametro7=$_GET['param'];

session_start();

require 'Datos.class.php';
require_once '../../../php/lib/PHPExcel/generarExcel.php';

$datos=Datos::getInstance();

$resultado = $datos->QueryExcel($parametro1,$parametro2,$parametro3,$parametro4,$parametro5,$parametro6);

GeneraExcel($resultado, 'TITULOEXCEL', $_SESSION['nombre'],'NombredelArchivo');

GeneraExcel is a method that is responsible for traversing the resource, it is already implemented and works correctly for all the excel that I generate except for this new one.

And the line that is added to my file automatically and I do not add it in my code at any time, I do not know where it comes from.

Maybe it's something very simple that I'm letting go through there.

Annex the method of GeneraExcel

<?php
/**
 * [$ruta description]Gnerar Excel
 * @var null
 */
function GeneraExcel($result, $tituloReporte, $usuario, $nombreArchivo)
{
    if (mysql_num_rows($result) > 0)
    {
        date_default_timezone_set('America/Mexico_City');
        if (PHP_SAPI == 'cli')
            die('Este archivo solo se puede ver desde un navegador web');
        /** Se agrega la libreria PHPExcel */
        require_once 'PHPExcel.php';
        // Se crea el objeto PHPExcel
        $objPHPExcel = new PHPExcel();
        // Se asignan las propiedades del libro
        $objPHPExcel->getProperties()->setCreator($usuario)//Autor
        ->setLastModifiedBy($usuario)//Ultimo usuario que lo modificó
        ->setTitle("Reporte excel")
            ->setSubject("Reporte excel")
            ->setDescription("Reporte excel")
            ->setKeywords("Reporte excel")
            ->setCategory("Reporte excel");

        // Obtenemos los nombres de las columnas y la cantidad de columnas.
        $titulosColumnas = array();
        $columnas = mysql_num_fields($result);
        for ( $i = 0; $i < $columnas; $i++ ) 
        {
            array_push($titulosColumnas,mysql_field_name( $result, $i ));
        }

        $letraFinal = GetLetras($columnas-1);

        $objPHPExcel->setActiveSheetIndex(0)
            ->mergeCells('A1:'.$letraFinal.'1');

        // Se agregan los titulos del reporte
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', $tituloReporte);

        for ( $i = 0; $i < $columnas; $i++ )
        {
            $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue(GetLetras($i).'3', $titulosColumnas[$i]);
        }

        $r=4;
        while ($fila = mysql_fetch_array($result)){
            for ( $i = 0; $i < $columnas; $i++ )
            {
                $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue(GetLetras($i).$r,$fila[$titulosColumnas[$i]]);
            }
            $r++;
        }

        $estiloTituloReporte = array(
            'font' => array(
                'name' => 'Verdana',
                'bold' => true,
                'italic' => false,
                'strike' => false,
                'size' => 16,
                'color' => array(
                    'rgb' => 'FFFFFF'
                )
            ),
            'fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array('argb' => 'FF220835')
            ),
            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_NONE
                )
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                'rotation' => 0,
                'wrap' => TRUE
            )
        );

        $estiloTituloColumnas = array(
            'font' => array(
                'name' => 'Arial',
                'bold' => true,
                'color' => array(
                    'rgb' => 'FFFFFF'
                )
            ),
            'fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
                'rotation' => 90,
                'startcolor' => array(
                    'rgb' => '00004d'
                ),
                'endcolor' => array(
                    'argb' => '00004d'
                )
            ),
            'borders' => array(
                'top' => array(
                    'style' => PHPExcel_Style_Border::BORDER_MEDIUM,
                    'color' => array(
                        'rgb' => '000000'
                    )
                )
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                'wrap' => TRUE
            ));

        $estiloInformacion = new PHPExcel_Style();
        $estiloInformacion->applyFromArray(
            array(
                'font' => array(
                    'name' => 'Arial',
                    'color' => array(
                        'rgb' => '000000'
                    )
                ),
                'fill' => array(
                    'type' => PHPExcel_Style_Fill::FILL_SOLID,
                    'color' => array('rgb' => 'ffffff')
                ),
                'borders' => array(
                    'left' => array(
                        'style' => PHPExcel_Style_Border::BORDER_THIN,
                        'color' => array(
                            'rgb' => '000000'
                        )
                    )
                )
            ));

        $objPHPExcel->getActiveSheet()->getStyle('A1:'.$letraFinal.'1')->applyFromArray($estiloTituloReporte);
        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$letraFinal.'3')->applyFromArray($estiloTituloColumnas);
        //$objPHPExcel->getActiveSheet()->setSharedStyle($estiloInformacion, "A4:".$letraFinal . ($columnas - 1));

        for ($i = 'A'; $i <= $letraFinal; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)
                ->getColumnDimension($i)->setAutoSize(TRUE);
        }

        // Se asigna el nombre a la hoja
        $objPHPExcel->getActiveSheet()->setTitle('REGISTROS');

        // Se activa la hoja para que sea la que se muestre cuando el archivo se abre
        $objPHPExcel->setActiveSheetIndex(0);
        // Inmovilizar paneles
        //$objPHPExcel->getActiveSheet(0)->freezePane('A4');
        $objPHPExcel->getActiveSheet(0)->freezePaneByColumnAndRow(0, 4);

        // Se manda el archivo al navegador web, con el nombre que se indica (Excel2007)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$nombreArchivo.'.xlsx"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    } else {
        print_r('No hay resultados para mostrar');
    }
}


function GetLetras($i)
{
    $letras = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
    return $letras[$i];
}
?>
    
asked by Desarrollador Android Jr. 26.04.2017 в 22:07
source

2 answers

2

I know if it will be your exact code, but you have two errors that could cause that problem:

  • Your PHP program starts with a blank space. This produces the sending of data before the excel (puts _<?php ).
  • The character you indicate is usually caused by the same previous problem generated by a carriage return before or after some opening or closing of PHP ( <?php or ?> ).

Since we do not have the complete code to find out where you are leaving that extra character (like the Datos.class.php file), I recommend that you thoroughly review each opening and closing of PHP to check that there is no additional character or line break (You can use a hexadecimal editor if you feel comfortable managing them).

A temporary trick using ob_start() and ob_end_clean () could be the following:

<?php
/* Introducimos en el buffer todo lo generado de manera involuntaria */
ob_start();
$parametro1 = isset($_GET['a'])?$_REQUEST['a']:date('Y-m');
$parametro2 = isset($_REQUEST['b'])?$_REQUEST['b']:0;
$parametro3 = isset($_REQUEST['c'])?$_REQUEST['c']:0;
$parametro4 = isset($_REQUEST['d'])?$_REQUEST['d']:0;
$parametro5 = isset($_REQUEST['e'])?$_REQUEST['e']:0;
$parametro6 = $_REQUEST['f'];

$parametro7=$_GET['param'];

session_start();

require 'Datos.class.php';
require_once '../../../php/lib/PHPExcel/generarExcel.php';

$datos=Datos::getInstance();

$resultado = $datos->QueryExcel($parametro1, $parametro2, $parametro3, $parametro4, $parametro5, $parametro6);

/* Guardamos la información que se ha generado para su depuración */
file_put_contents('archivo.log.txt', ob_get_contents());
/* Limpiamos cualquier salida que antes iba al navegador de manera accidental
  antes de enviar el excel */
ob_end_clean();
GeneraExcel($resultado, 'TITULOEXCEL', $_SESSION['nombre'], 'NombredelArchivo');

Recommendations:

Using ob_start() and ob_end_clean() are patches to hide a root problem that needs to be found and fixed.

As recommended PSR-2 (section # 2.2) the files that contain only PHP (or, in general, those that do not generate HTML) should omit the closing of PHP ?> to avoid "sneaking" (some editors automatically add an empty line to the end of the text files) blank spaces or unwanted car returns.

  

2.2. Files

     

All PHP files MUST use the Unix LF (linefeed) line ending.

     

All PHP files MUST end with a single blank line.

     

The closing ?> tag MUST be omitted from files containing only PHP.

The translation would be:

  

2.2. Files

     

All PHP files MUST use the Unix LF line end (linefeed / linefeed).

     

All PHP files MUST end in a single blank line.

     

The closing tag ?> MUST be omitted in files that contain only PHP.

Therefore, you should remove the PHP lock you have in the file containing the function GeneraExcel and in Datos.class.php and try again to see if that was the problem.

    
answered by 27.04.2017 / 12:25
source
0

The times that I have used PHPExcel and I have generated a screenshot like the one that you attach, the error was NOT PHPExcel, but from some previous point, that is, you are probably returning the php some error and "print" it in excel, which does not know how to interpret and pulls out weird things .. Try to make a print_r of the values you want to print

    
answered by 27.04.2017 в 12:15