not pick cell formats from excel sheet some cell show as 17 not 17.00

Topics: Developer Forum, Project Management Forum, User Forum
Jan 6, 2014 at 10:07 PM
not getting all cell formats the excel is created but some cell show as 17 not 17.00

here is the code

$sql2 =" SELECT expense_data_info_id, expense_data_info_member_id, expense_data_info_date, expense_data_info_name,
expense_data_info_cust_notes, expense_data_info_business_note
FROM expense_data_info
where
expense_data_info_member_id ='".$member_id."' 
AND expense_data_info_date = '".$enddate."'";
    $rslt2 = mysql_query($sql2 )  or die(" 407 expense_data_info Failed\n<br>".$sql2."<br>\n".mysql_errno().":".mysql_error()."\n");

    while ($row2 = mysql_fetch_array($rslt2))
        {   
            $expense_data_info_cust_notes =  $row2['expense_data_info_cust_notes'] ;
            $expense_data_info_business_note =  $row2['expense_data_info_business_note'] ;
    }
      mysql_free_result($rslt2);


$path = "/httpdocs/backoffice/documents/".$name."/";
$fromfile= catPaths('/home/vhosts/greatgrow.com/httpdocs/backoffice/documents/folder0011/Expense_Report.xlsx');
$excel2 = PHPExcel_IOFactory::load($fromfile);
PHPExcel_Shared_String::setDecimalSeparator('.');
PHPExcel_Shared_String::setThousandsSeparator(',');
// print( "path is $path <br>fromfile is $fromfile <br>");
 $sql ="SELECT expense_data_id , expense_data_name , expense_data_member_id , expense_data_date , expense_data_amount , expense_data_number , expense_data_cell , expense_data_row
FROM expense_data
where expense_data_member_id ='". $member_id."'
    AND expense_data_date = '".$enddate."' 
ORDER BY expense_data_row , expense_data_cell ";
// print("$sql <br>");
$rslt2 = mysql_query($sql ) or die(" 429 expense_data Query Failed\n<br>".$sql."<br>\n".mysql_errno().":".mysql_error()."\n");
    while ($row2 = mysql_fetch_array($rslt2))
        {    
        $expense_data_amount    = $row2['expense_data_amount'] ;
        $expense_data_name  = $row2['expense_data_name'] ;
        $expense_data_number    = $row2['expense_data_number'] ;
        $cell = $row2['expense_data_cell'].$row2['expense_data_row'] ;

        $amount = 0;

        switch($expense_data_name )
                                {
        case 'MilesDriven[1]':
        $amount =  $row2['expense_data_number'] ;
        break; 
        case 'MilesDriven[2]':
        $amount =  $row2['expense_data_number'] ;
        break;
        case 'MilesDriven[3]':
        $amount =  $row2['expense_data_number'] ;
        break; 
        case 'MilesDriven[4]':
        $amount =  $row2['expense_data_number'] ;
        break;
        case 'MilesDriven[5]':
        $amount =  $row2['expense_data_number'] ;
        break; 
        case 'MilesDriven[6]':
        $amount =  $row2['expense_data_number'] ;
        break;
        case 'MilesDriven[7]':
        $amount =  $row2['expense_data_number'] ;
        break;
        case 'MilesDriven[8]':
        $amount =  $row2['expense_data_number'] ;
        break; 
        default:
        $amount = '$' . number_format($row2['expense_data_amount'], 2);
         $amount =  $row2['expense_data_amount'];
        }
        //Print( " 277 expense_data_name is $expense_data_name  amount is $amount<br>");
        $findme = 'TOTAL';
        $ptotal = strpos($expense_data_name,$findme );
        if ($ptotal === false) {$pos1 =0;} else {    $pos1 =1;}
        $findme = 'total';
        $ptotal = strpos($expense_data_name,$findme );
        if ($ptotal === false) { $pos2 =0;} else { $pos2 =1;}

        $pos = $pos1 +$pos2 ;
        if (($pos  == 0) and ($amount == '0.00'))
            {
                if ($amount == '0.00') {$amount = ''; }
                if ($amount == 0) {$amount = ''; }
            }

//print("485 cell is $cell  amount is $amount<br>");

     if ($sict == '')
        {   
            $sict = 'y';
            $excel2->setActiveSheetIndex(0) 
             ->setCellValue('C4', $name_full)
             ->setCellValue('h6', $datemdy)
              ->setCellValue('c6', $datemdy);



$i = $dstart;
//echo  $dstart .' '.$dend; 
$ix = 1;
$s = 2;
while($i<=$dend )
{
$datet = cal_from_jd($i, CAL_GREGORIAN);
$datemdya  = $datet[year].'-'.$datet[month].'-'.$datet[day]; 
$pdate = $datet[abbrevdayname] . '  '.$datet[date] ; 
$cella =$cellarray[$s];
$cella .=8;
//echo $pdate. ' ' .$cella . ' '.$s . '<br>';  
$excel2->setActiveSheetIndex(0) 
->setCellValue($cella, $pdate);


 $i++;  $ix++; $s++;
}
}

            //echo $cell .' '.$amount .'<br>' ;

            if($cell == 'J36') 
            { 
            $sql5 = "UPDATE expense_detail SET expense_detail_amount='".$amount."' 
            WHERE  expense_detail_member_id ='". $member_id."' and expense_detail_date ='".$enddate."' ";
                     $rslt5 = mysql_query($sql5)    
    or die(" 525 expense_detail update Query failed\n<br>".$sql5."<br>\n".mysql_errno().":".mysql_error()."\n");
    //mysql_free_result($rslt5);


            }

            if (($cell != 'B46') and  ($cell != 'B40'))
            {
              $excel2->setActiveSheetIndex(0) 
              ->setCellValue($cell, $amount);
            }

            if  ($cell == 'B40')
            {
            // print("530 cell is $cell  expense_data_info_cust_notes is $expense_data_info_cust_notes<br>");
              $excel2->setActiveSheetIndex(0) 
              ->setCellValue($cell, $expense_data_info_cust_notes );
            }

            if  ($cell == 'B46')
            {
            //  print("537 cell is $cell  expense_data_info_business_note is $expense_data_info_business_note<br>");
              $excel2->setActiveSheetIndex(0) 
              ->setCellValue($cell, $expense_data_info_business_note );
            }
        }

      mysql_free_result($rslt2);

     // die;
$objWriter = PHPExcel_IOFactory::createWriter($excel2, 'Excel5');
$objWriter->save($path.$enddate.'.xls');
$wfile =$enddate.'.xls';


$q1c = "SELECT COUNT(*) as Num FROM expense_detail
WHERE expense_detail_date = '".$enddate."' AND expense_detail_member_id ='".$member_id."' ";
$COUNT = mysql_result(mysql_query($q1c),0);

//print("$COUNT <br>"); die;
if ($COUNT == 0)
{
$q1 = "INSERT INTO expense_detail (expense_detail_id,expense_detail_member_id,expense_detail_date,expense_detail_file_name)
VALUES ( '', '$member_id','$enddate','$wfile' )";
// print(" $q1 <BR>"); die;
        $rslt1 = mysql_query($q1)     or die(" expense_detail add Query failed\n<br>"
        .$q1."<br>\n".mysql_errno().":".mysql_error()."\n");

        mysql_free_result($rslt1);
}



}
Coordinator
Jan 7, 2014 at 1:25 PM
Don't use PHP's number_Format() to format your cells.... you're creating an Excel file not HTML markup, use the Excel number format masks

e.g.
$objPHPExcel->getActiveSheet()->getStyle('A1:C3')
    ->getNumberFormat()
    ->setFormatCode('#,##0.00');
Jan 8, 2014 at 12:28 AM
I tried
{
              $objPHPExcel->getActiveSheet()->getStyle($cell)
                ->getNumberFormat()
                ->setFormatCode('#,##0.00');
              $excel2->setActiveSheetIndex(0) 
              ->setCellValue($cell, $amount);

did not work
Jan 8, 2014 at 12:31 AM
Edited Jan 8, 2014 at 12:32 AM
I also tried
$objPHPExcel->getActiveSheet()->getStyle($cell)
                ->getNumberFormat()
                ->setFormatCode('#,##0.00')
              ->setCellValue($cell, $amount);
did not work
Coordinator
Jan 8, 2014 at 12:41 AM
If your PHPExcel object is $excel2, then it's

$excel2->getActiveSheet() 
    ->getStyle($cell)
    ->getNumberFormat()
    ->setFormatCode('#,##0.00');
Jan 8, 2014 at 4:15 PM
Edited Jan 8, 2014 at 4:17 PM
I now get this error

cell is C10
Fatal error: Call to undefined method PHPExcel_Style_NumberFormat::setCellValue() in /home/vhosts/greatgrow.com/httpdocs/backoffice/expense_report_post.php on line 554

the code is
print("cell is $cell <br>");
            if (($cell != 'B46') and  ($cell != 'B40'))
            {
                 $excel2->getActiveSheet()->getStyle($cell)                
                ->getNumberFormat()
                ->setFormatCode('#,##0.00')
              ->setCellValue($cell, $amount); 
            }
if you want to see the excel sheet let me know how to send it to you
Jan 8, 2014 at 4:50 PM
setFormatCode return PHPExcel_Style_NumberFormat, not a PHPExcel_Worksheet.

if yo want to use fluent interface :
$excel2->getActiveSheet()->setCellValue($cell, $amount)->getStyle($cell)->getNumberFormat()->setFormatCode('#,##0.00');
// or
$excel2->getActiveSheet()->setCellValue($cell, $amount, true)->getStyle()->getNumberFormat()->setFormatCode('#,##0.00');
setCellValue return the PHPExcel_Worksheet (third parameter to false or omitted) and you can chain to call getStyle($cell) (the worksheet's method), or return PHPExcel_Cell (third parameter to true) and you can call getStyle() (without coordinate : it's the style of the cell)
Jan 9, 2014 at 12:59 AM

Yes it worked

Thank you

$excel2->getActiveSheet()
->setCellValue($cell, $amount, true)
->getStyle()->getNumberFormat()
->setFormatCode('#,##0.00');