[Bugs] Cell Format with PDF and error_reporting(E_ALL);

Topics: Developer Forum, Project Management Forum, User Forum
Apr 27, 2010 at 2:25 PM
Edited Apr 27, 2010 at 2:32 PM

Hi guys, im posting here quite often, sorry for that, just trying help you and myself.

Now im having one problem with cell format when i create the file using PDF_Writer, with XLS its OK but PDF isnt, so im gonna post some information:

PHPExcel Version: 1.7.2

Database: MS SQL Server 2005

PHP IDE: Komodo Edit 5.2

Well ill insert some prints so you guys can check out the problem:

Image with THE problem: http://i26.photobucket.com/albums/c110/nydiow/Riversoft/format_problem.png

XLS: http://i26.photobucket.com/albums/c110/nydiow/xls.png

How should be: http://i26.photobucket.com/albums/c110/nydiow/Riversoft/format.png

I got those images with de same code, the unique difference between than is that i forced the code fill the page horizontally (plus one year for example), so every time that i dont do that, i got this formating problem, and im quite sure that is a bug, and more, the error_reporting(E_ALL); doesnt work so i MUST comment that to keep my code working (dont know why, i got a lot of errors such as Notice: Undefined index: TOTAL in C:\Inetpub\wwwroot\STCPWEB_ADMIN-4_0_0-4057\www\list_est_xls_pdf.php on line 295)

Code:

<?php
/**
 * PHPExcel
 *
 * Copyright (C) 2006 - 2010 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 * 
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2010 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    1.7.2, 2010-01-11
 * @File Encoding MUST be UTF-8
 */

require ( "./cgi-bin/stcpwebsup.php" );
require ( "./cgi-bin/stcpwebses.php" );

$de=$_GET['de'];
$ate=$_GET['ate'];
$idUser=$_GET['idUser'];
$idSit=$_GET['idSit'];
$idArq=$_GET['idArq'];

$Datade = date("d/m/Y G:i:s", strtotime($de));
$Dataate = date("d/m/Y G:i:s", strtotime($ate));

$year = date("Y", strtotime($ate));
$year = $year - 2000;

$s = count( $descEstat );
//echo "<br>Contando array: ".$s."<br><br>";

$tit = $descEstat[$idSit] ." - " . GetNomeServidorODETTE();
    
//OBTEM RELATORIO
$arLinhas = GetRelatEstatisticaPDF( $de,$ate,$idUser,$idSit,$idArq );

$s = count( $arLinhas );
//echo "<br>Contando no Array GetRelatEstatistica: " . $s."<br>";

/** Error reporting */
//error_reporting(E_ALL);

/** PHPExcel */
require_once './Classes/PHPExcel.php';

/** PHPExcel_IOFactory */
require_once './Classes/PHPExcel/IOFactory.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setCreator("Riversoft")
							 ->setLastModifiedBy("Riversoft")
							 ->setTitle("Relatório - STCP OFTP Web Admin")
							 ->setSubject("Relatório - STCP OFTP Web Admin")
							 ->setDescription("Relatório - STCP OFTP Web Admin - Riversoft")
							 ->setKeywords("Relatório")
							 ->setCategory("Relatório");
                                                         
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);



$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('B1', html_entity_decode(STCPWEB_MSG_5770,ENT_QUOTES,'UTF-8'));
	    
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Relatório');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Riversoft logo');
$objDrawing->setDescription('Riversoft logo');
$objDrawing->setPath('./imagens/riverlogo.jpg');
$objDrawing->setHeight(20);
$objDrawing->setCoordinates('A1');
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A3', html_entity_decode(STCPWEB_MSG_0066,ENT_QUOTES,'UTF-8')." ". $Datade);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B3', html_entity_decode(STCPWEB_MSG_0067,ENT_QUOTES,'UTF-8')." ". $Dataate);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A6', html_entity_decode($tit,ENT_QUOTES,'UTF-8'));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B4', html_entity_decode(STCPWEB_MSG_0258,ENT_QUOTES,'UTF-8').": ". $idArq);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A4', html_entity_decode(STCPWEB_MSG_0064,ENT_QUOTES,'UTF-8').": ". $idUser);

/*$objPHPExcel->getActiveSheet()->getStyle('7')->getFont()->setSize(8);
$objPHPExcel->getActiveSheet()->getStyle('8')->getFont()->setSize(8);*/

$objPHPExcel->getActiveSheet()->getStyle('A3:B4')->getFont()->setSize(8);
    
$j=1;
$letra = 'B';
    

    $cont = 7;
    
    $objPHPExcel->getActiveSheet()->mergeCells('B1:F1');
    $objPHPExcel->getActiveSheet()->mergeCells('B4:H4'); //File name
    $objPHPExcel->getActiveSheet()->mergeCells('B3:C3');
    
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A7', html_entity_decode(STCPWEB_MSG_0052,ENT_QUOTES,'UTF-8').": "); 
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A8', html_entity_decode(STCPWEB_MSG_0053,ENT_QUOTES,'UTF-8').": "); 
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A9', html_entity_decode(STCPWEB_MSG_0054,ENT_QUOTES,'UTF-8').": ");
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A10', html_entity_decode(STCPWEB_MSG_0059,ENT_QUOTES,'UTF-8').": ");
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A11', html_entity_decode(STCPWEB_MSG_0055,ENT_QUOTES,'UTF-8').": ");
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A12', html_entity_decode(STCPWEB_MSG_0056,ENT_QUOTES,'UTF-8').": ");
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A13', html_entity_decode(STCPWEB_MSG_0057,ENT_QUOTES,'UTF-8').": "); 
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A14', html_entity_decode(STCPWEB_MSG_0058,ENT_QUOTES,'UTF-8').": ");
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A15', html_entity_decode(STCPWEB_MSG_0060,ENT_QUOTES,'UTF-8').": ");
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A16', html_entity_decode(STCPWEB_MSG_0061,ENT_QUOTES,'UTF-8').": ");
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A17', html_entity_decode(STCPWEB_MSG_0062,ENT_QUOTES,'UTF-8').": ");
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A18', html_entity_decode(STCPWEB_MSG_0063,ENT_QUOTES,'UTF-8').": "); 
		
    $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setWidth(33);
    
    $objPHPExcel->getActiveSheet()->getStyle('A6:B18')->getFont()->setSize(8);
    $objPHPExcel->getActiveSheet()->getStyle('A6:A18')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle('A7:A18')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
    $objPHPExcel->getActiveSheet()->getStyle('A7:A18')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
	
    $objPHPExcel->getActiveSheet()->getStyle('A7:A18')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $objPHPExcel->getActiveSheet()->getStyle('A7:A18')->getFill()->getStartColor()->setARGB('FF808080');
    
    
    for ( $i = 0; $i <= $year; $i++ )
    {
		
	    //Years logic (print 6 years horizontally and jump lines and restore column)
        if ($i==6 || $i==12 || $i==18 || $i==24 || $i==30 || $i==36 || $i==42 || $i==48)
        {
            $letra = 'B';
            $j=$j-6;
            $cont=$cont+14;
            
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, ($cont-1),html_entity_decode($tit,ENT_QUOTES,'UTF-8'));
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, ($cont),html_entity_decode(STCPWEB_MSG_0052,ENT_QUOTES,'UTF-8').": ");
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, ($cont+1),html_entity_decode(STCPWEB_MSG_0053,ENT_QUOTES,'UTF-8').": ");
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, ($cont+2),html_entity_decode(STCPWEB_MSG_0054,ENT_QUOTES,'UTF-8').": ");
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, ($cont+3),html_entity_decode(STCPWEB_MSG_0059,ENT_QUOTES,'UTF-8').": ");
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, ($cont+4),html_entity_decode(STCPWEB_MSG_0055,ENT_QUOTES,'UTF-8').": ");
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, ($cont+5),html_entity_decode(STCPWEB_MSG_0056,ENT_QUOTES,'UTF-8').": ");
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, ($cont+6),html_entity_decode(STCPWEB_MSG_0057,ENT_QUOTES,'UTF-8').": ");
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, ($cont+7),html_entity_decode(STCPWEB_MSG_0058,ENT_QUOTES,'UTF-8').": ");
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, ($cont+8),html_entity_decode(STCPWEB_MSG_0060,ENT_QUOTES,'UTF-8').": ");
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, ($cont+9),html_entity_decode(STCPWEB_MSG_0061,ENT_QUOTES,'UTF-8').": ");
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, ($cont+10),html_entity_decode(STCPWEB_MSG_0062,ENT_QUOTES,'UTF-8').": ");
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, ($cont+11),html_entity_decode(STCPWEB_MSG_0063,ENT_QUOTES,'UTF-8').": ");
                                    
            $objPHPExcel->getActiveSheet()->getStyle('A'.($cont-1).':A'.($cont+11))->getFont()->setSize(8);
            $objPHPExcel->getActiveSheet()->getStyle('A'.($cont-1).':A'.($cont+11))->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('A'.($cont).':A'.($cont+11))->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
            $objPHPExcel->getActiveSheet()->getStyle('A'.($cont).':A'.($cont+11))->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
                        
            $objPHPExcel->getActiveSheet()->getStyle('A'.($cont).':A'.($cont+11))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $objPHPExcel->getActiveSheet()->getStyle('A'.($cont).':A'.($cont+11))->getFill()->getStartColor()->setARGB('FF808080');
        }
        
            // Seta array temporario
	    for ( $n = 0; $n < 8; $n++ )
		$arTmp[$n] = $arLinhas[$n][$i];
            
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j, ($cont-1), html_entity_decode(STCPWEB_MSG_0372,ENT_QUOTES,'UTF-8')." " . ($i+2000));
            $objPHPExcel->getActiveSheet()->getStyle($letra.($cont-1))->getFont()->setBold(true);
	    	    
            $objPHPExcel->getActiveSheet()->getStyle($letra.($cont-1))->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
            $objPHPExcel->getActiveSheet()->getStyle($letra.($cont-1))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $objPHPExcel->getActiveSheet()->getStyle($letra.($cont-1))->getFill()->getStartColor()->setARGB('FF808080');
            $objPHPExcel->getActiveSheet()->getStyle($letra.($cont-1))->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
            
            $objPHPExcel->getActiveSheet()->getStyle($letra)->getFont()->setSize(8);
            $objPHPExcel->getActiveSheet()->getStyle($letra.($cont-1))->getFont()->setSize(8);
        
	    // Mostra Lista das estatisticas
        $Total    = ( $arTmp[1]['TOTAL'] );
        if(!$Total)
            $Total = 0;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j, $cont," ".$Total);
	$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($j)->setWidth(13);
        
        
        $Total  = ( $arTmp[1]['QTD'] );
        if(!$Total)
            $Total = 0;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j, ($cont+1)," ".$Total);
                
        $Total   = ( $arTmp[1]['MEDIA'] );
        if(!$Total)
            $Total = 0;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j, ($cont+2)," ".$Total);
                    
        $Total  = ( $arTmp[0]['TOTAL'] );
        if(!$Total)
            $Total = 0;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j, ($cont+3)," ".$Total);
                
        $Total    = ( $arTmp[0]['QTD'] );
        if(!$Total)
            $Total = 0;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j, ($cont+4)," ".$Total);
                    
        $Total  = ( $arTmp[0]['MEDIA'] );
        if(!$Total)
            $Total = 0;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j, ($cont+5)," ".$Total);
                   
        $Total  = ( $arTmp[2]['QTD'] );
        if(!$Total)
            $Total = 0;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j, ($cont+6)," ".$Total);
                   
            $Total    = ( $arTmp[3]['QTD'] );
        if(!$Total)
            $Total = 0;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j, ($cont+7)," ".$Total);
                   
        $Total    = ( $arTmp[4]['QTD'] );
        if(!$Total)
	    $Total = 0;
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j, ($cont+8)," ".$Total);
                   
        $Total    = ( $arTmp[5]['QTD'] );
        if(!$Total)
            $Total = 0;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j, ($cont+9)," ".$Total);
                   
        $Total    = ( $arTmp[6]['QTD'] );
        if(!$Total)
            $Total = 0;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j, ($cont+10)," ".$Total);
                   
        $Total    = ( $arTmp[7]['QTD'] );
        if(!$Total)
            $Total = 0;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($j, ($cont+11)," ".$Total);
		
	$j++;
	$letra++;
	
    }



    
// Redirect output to a client’s web browser (Excel5)
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-type: application/pdf");
header("Pragma: public");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");		
header("Content-Disposition: attachment;filename=relatorio_est.xls");
header("Content-Transfer-Encoding: binary ");

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->save('php://output'); 

Thanks,

DioW