Sum numbers with comma in PHPExcel

I’m trying to sum my column(Calculated Cash) using PHPExcel. The problem is the numbers are typed as string… I don’t want to remove the commas to make it user friendly.

Can you help me in finding sum on the column?

Here’s my code:

$sql = "SELECT a.DOB as `DATE` 
FORMAT(a.Amount,2) as CALCULATED from deposit a where STR_TO_DATE(a.DOB, 
'%m/%d/%Y') BETWEEN '20170701' AND '20170701'



$rowCount = 2;  
$rowTop = 3;
$rowMinus = $rowCount - 1;

$objPHPExcel->getActiveSheet()->setCellValue('D'.$rowCount, 
'=SUM(D'.$rowTop.':D'.$rowMinus.')');
 $objPHPExcel->getActiveSheet()-
>getStyle('D'.$rowCount)>applyFromArray($styleBold);
 $objPHPExcel->getActiveSheet()->getStyle('D3:D4')
->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);

Some tricks about php:

$a = "4,";
$b = "5 soldiers";
echo $a + $b; // returns 9

So php is a weak type language and checks if needed converts string to integer!

If you had something like this you can convert it’s numbers to a array of numbers:

$val = ", 2, ,4 ,5 , 6"; // a string of numbers
$parts = explode(",",$val); // an array of numbers

So $parts would be like:

array(6) { [0]=> string(0) "" [2]=> string(1) " 2" [2]=> string(1) " " [3]=> string(2) " 4" [4]=> string(2) " 5" [5]=> string(2) " 6" }

I found the answer I’m looking for 🙂

I’ve got rid of the FORMAT({columnname},2) when I’m calling my result in my database and added this code to my PHPexcel 🙂

Code:

 $objPHPExcel->getActiveSheet()->getStyle('H'.$rowCount)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);