phpexcel csv export 2 decimel place format

[bowen73]

Help Please

 

im trying to export from mysql whereas the code adds a calculation (*100/70) to the cost and exports that (along with other info from the DB) to the csv (hopefully that makes sense)  but its exporting 4 or sometimes 5 decimel places ive had a look but nothing ive seen i can make work without getting an error,

<?php
require ("../connect.php"); 
//$sql ="select * from products";

$sql = mysqli_query($connect,"Select * FROM products");
while ($row = mysqli_fetch_array($sql))

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

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

        // Set document properties
        $objPHPExcel->getProperties()->setCreator("Mark Bowen")
                                                   ->setLastModifiedBy("Mark Bowen")
                                                   ->setTitle("Product Export")
                                                   ->setSubject("Product Export")
                                                   ->setDescription("Product export from database to import into Website")
                                                   ->setKeywords("office 2007 openxml php")
                                                   ->setCategory("CSV result file");
       
        // Add some data
        $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A1',"sku") 
						->setCellValue('B1',"name")
						->setCellValue('C1',"short_description")
						->setCellValue('D1',"description")
						->setCellValue('E1',"msrp")
						->setCellValue('F1',"price")
						->setCellValue('G1',"cost")
						->setCellValue('H1',"in_in_stock")
						->setCellValue('I1',"qty")
						->setCellValue('J1',"visibility")
						->setCellValue('K1',"status")
						->setCellValue('L1',"websites")
						->setCellValue('M1',"type")
						->setCellValue('N1',"image")
						->setCellValue('O1',"image_label")
						->setCellValue('P1',"small_image")
						->setCellValue('Q1',"small_image_label")
						->setCellValue('R1',"thumbnail")
						->setCellValue('S1',"thumbnail_label")
						->setCellValue('T1',"categories")
						->setCellValue('U1',"tax_class")
						->setCellValue('V1',"weight")
						->setCellValue('W1',"meta_title")
						->setCellValue('X1',"meta_description")
						->setCellValue('Y1',"meta_key")
						->setCellValue('Z1',"manufacturer")
						->setCellValue('AA1',"url_key");
			
			

	
				
        $i = 2;
        foreach($sql as $row){
		 $objPHPExcel->setActiveSheetIndex(0)
						->setCellValue('A'.$i,$row['prod_sku'])
						->setCellValue('B'.$i,$row['prod_name'])
				//		->setCellValue('C'.$i,$row['short desc'])
						->setCellValue('D'.$i,$row['prod_long'])
						->setCellValue('E'.$i,$row['prod_rrp'])
						->setCellValue('F'.$i,$row['prod_sell_override']>0 ? $row['prod_sell_override'] : $row['prod_trade']*100/70)
						->setCellValue('G'.$i,$row['prod_trade'])
			//			->setCellValue('H'.$i,$row['is in stock'])
			//			->setCellValue('I'.$i,$row['qty'])
			//			->setCellValue('J'.$i,$row['visibility'])
						->setCellValue('K'.$i,$row['prod_status'])
			//			->setCellValue('L'.$i,$row['websites'])
			//			->setCellValue('M'.$i,$row['type'])
			//			->setCellValue('N'.$i,$row['image'])
			//			->setCellValue('O'.$i,$row['image label'])
			//			->setCellValue('P'.$i,$row['small image'])
				//		->setCellValue('Q'.$i,$row['small image label'])
				//		->setCellValue('R'.$i,$row['thumbnail'])
			//			->setCellValue('S'.$i,$row['thumbnail label'])
			//			->setCellValue('T'.$i,$row['categories'])
			//			->setCellValue('U'.$i,$row['tax_class'])
						->setCellValue('V'.$i,$row['prod_weight'])
		//				->setCellValue('W'.$i,$row['meta_title'])
		//				->setCellValue('X'.$i,$row['meta_description'])
		//				->setCellValue('Y'.$i,$row['meta_key'])
		//				->setCellValue('Z'.$i,$row['manufacturer'])
		//				->setCellValue('AA'.$i,$row['url_key']);
          ;    $i++;
        }

        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle('export');


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

        // Redirect output to a client’s web browser (Excel5)
//      header('Content-Type: application/vnd.ms-excel');
//      header('Content-Disposition: attachment;filename="export_DB_products.csv"');
//      header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
        $objWriter->save('../export/export_db_products.csv');

?>

[Barand]

Have you tried number_format()?

[bowen73]

Have you tried number_format()?

 

Ive just looked at that, im still learning php so its trying to apply the examples without breaking stuff.

i tried various ways to include it in the setCellValue but not got anywhere :-( 

 

Ive tfound ones for phpexcel too

objPHPExcel->getActiveSheet()->getStyle("A1")->getNumberFormat()->setFormatCode('#,##0');

but dont know how to implement it into my code without throwing out an error

Edited October 28, 2016 by bowen73

[Barand]

->setCellValue('F'.$i,$row['prod_sell_override']>0 ? $row['prod_sell_override'] : number_format($row['prod_trade']*100/70), 2)

?

[bowen73]

ive got this far....  if i remove the ',2'  it outputs the numbers without decimels.... but if i put the ,2 back in i get an error

->setCellValue('F'.$i,$row['prod_sell_override']>0 ? $row['prod_sell_override'] : number_format($row['prod_trade']*100/70),2)

Error:  Fatal error: Uncaught Error: Call to undefined method PHPExcel_Cell::setCellValue() in C:\xampp\htdocs\pmc\dev\csv.php:66 Stack trace: #0 {main} thrown in C:\xampp\htdocs\pmc\dev\csv.php on line 66

[bowen73]

->setCellValue('F'.$i,$row['prod_sell_override']>0 ? $row['prod_sell_override'] : number_format($row['prod_trade']*100/70), 2)

?

 

i got to that part after you suggested number_format :-)  

[bowen73]

I got there with some logical thinking,   just moved the ,2 one ) in.  Thanks for your help @barrand

->setCellValue('F'.$i,$row['prod_sell_override']>0 ? $row['prod_sell_override'] : number_format($row['prod_trade']*100/70,2))