CODEWITHSUNDEEP
phpphpexcel
JuanFernandoz
JuanFernandoz

Reputation: 799

PHPExcel output numbers are wrong

I have this code: 

$query = "SELECT ciudadh,corporacionh,r1,proxima,ultima FROM usuariox"; 
$headings = array('#', 'Estas son sus fichas hasta la fecha','fecha de ingreso'); 
if ($result = mysql_query($query) or die(mysql_error())) { 
    // Create a new PHPExcel object 
    $objPHPExcel = new PHPExcel(); 
    $objPHPExcel->getActiveSheet()->setTitle('Asuntos Pendientes'); 

    $rowNumber = 2; 
     $rowNumber2 = 2; 
    $col = 'A'; 
    foreach($headings as $heading) { 
       $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$heading); 
       $col++; 
         } 
    // Loop through the result set 
    $rowNumber = 3; 
    $newId = 1;
    while ($row = mysql_fetch_row($result)) { 
       $col = 'B'; 
        $objPHPExcel->getActiveSheet()->setCellValue("A$rowNumber", $newId++);
       foreach($row as $cell) { 
          $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell); 
          $col++; 
       } 
       $rowNumber++; 
         $rowNumber2++; 
    } 

$styleArray = array(
       'borders' => array(
             'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('argb' => 'FFB0c4dE'),
             ),

       ),
);


$styleArray2 = array(
             'fill' => array(
                    'type' => PHPExcel_Style_Fill::FILL_SOLID,
                    'color' => array('argb' => 'FFB0c4dE'),
             ),



);

$objPHPExcel->getActiveSheet()->getStyle('A2:F2')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->applyFromArray($styleArray2);
$objPHPExcel->getActiveSheet()->getSheetView()->setZoomScale(80);
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/headerexcel2.png');
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$objDrawing->setCoordinates('A1');
$objDrawing->setOffsetX(30);

    // Freeze pane so that the heading line won't scroll 

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(30);
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(50);
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(15);
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&C&16');
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(14); 
$objPHPExcel->getActiveSheet()->freezePane('A2'); 

    // Save as an Excel BIFF (xls) file 



   $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 

   header('Content-Type: application/vnd.ms-excel'); 
   header('Content-Disposition: attachment;filename="asuntos-pendientes.xls"'); 
   header('Cache-Control: max-age=0'); 
    $objWriter->save('php://output'); 


   exit(); 


} 
echo 'a problem has occurred... no data retrieved from the database';

the code works but "r1" field (mysql) have numbers like this:

05001400300120110010000

but if a look the excel file instead the number Im getting this :

enter image description here

Upvotes: 0

Views: 1734

Answers (2)

Mark Baker
Mark Baker

Reputation: 212522

Write values like 05001400300120110010000 using setCellValueExplicit() which will (by default) treat it as a string value, and hence store the value with the leading zero and (because it is far larger than an Excel or a PHP integer value) it won't be cast to float and displayed in scientific format

Upvotes: 3

Adam Szabo
Adam Szabo

Reputation: 11412

Excel can't represent numbers this long if the cell type is set to General, it not a problem of PHPExcel. You have to set the cell type to Number. You can try:

$PHPExcelObject->getActiveSheet()->getStyle($cell)->getNumberFormat()->setFormatCode('0');

Upvotes: 0

Related Questions