converting Text to Numeric form

Topics: Developer Forum, Project Management Forum, User Forum
Nov 7, 2014 at 10:05 PM
I have made this sheet using PHPExcel
i filled it using an array .

it has values in percent form and it treated as text form by default .
how can i convert my all the columns in to numeric form for so i can apply some math formulas on it .
   $excel_out_Questions = $this->outputQuestions($Sets);

   $objPHPExcel->getActiveSheet()->fromArray($excel_out_Questions, "" , 'A5');

excel sheet after filling

i have tried this but it doesn't work ... :/
  $objPHPExcel->getActiveSheet()->getStyle('B434:B444')->getNumberFormat()->setFormatCode (PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);

$objPHPExcel->getActiveSheet()->setCellValue( 'B445','=(SUM(B443:B444)-SUM(B434:B440))*100)');

Please suggest.

Thanks in advance
Nov 7, 2014 at 10:40 PM
Unless you use setCellValueExplicit(), then PHPExcel uses a value binder to identify the datatype that you are setting in the cell.

By default, this is PHPExcel_Cell_DefaultValueBinder, which performs a very simplistic check on whether the PHP datatype is Null or Boolean, or a numeric value or a string. A value such as 37.7% is a PHP string, so will be treated as a string.

There is also an advanced value binder (PHPExcel_Cell_AdvancedValueBinder) that can perform much more sophisticated checks, and is capable of identifying strings containing basic date formats and converting them to an Excel serialized datetime, and setting a number format mask accordingly. It can also detect currency values, fractions, and (most importantly) percentages.

If you pass a string value like 37.7% through the advanced value binder, it will divide the numeric part by 100, and set the number format mask to a percentage mask, in exactly the same way as the MS Excel GUI.

To enable the advanced value binder, call
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
before instantiating your PHPExcel object.

You can find an example of advanced binder usage in /Examples/29advancedvaluebinder.php
Marked as answer by gupta34 on 11/10/2014 at 8:09 AM