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];
}
?>