setValue TYPE_NUMERIC

Topics: User Forum
Jan 22, 2010 at 1:32 PM

I have grades being written and as usual they have a % sign displayed, however I am getting the green arrow telling me the value is text and not numeric.  Can I use setValue($IRAT_Grade.'%', TYPE_NUMERIC);

 

Do I need to use setValueExplicit instead?

 

$objPHPExcel->getActiveSheet()->getCell('G'.$datarow)->setValue($IRAT_Raw);
$objPHPExcel->getActiveSheet()->getCell('H'.$datarow)->setValue($IRAT_Grade.'%');
$objPHPExcel->getActiveSheet()->getCell('I'.$datarow)->setValue($GRAT_Raw);
$objPHPExcel->getActiveSheet()->getCell('J'.$datarow)->setValue($GRAT_Grade.'%');
$objPHPExcel->getActiveSheet()->getCell('K'.$datarow)->setValue($AppEx_Raw);
$objPHPExcel->getActiveSheet()->getCell('L'.$datarow)->setValue($AppEx_Grade.'%');

 

Jan 22, 2010 at 1:48 PM

I assume there is no percentage datatype?

Developer
Jan 22, 2010 at 1:51 PM

Do like this

$worksheet->getCell('A1')->setValue(.21);
$worksheet->getStyle('A1')->getNumberFormat()->setFormatCode('0.00%');

If you don't want decimals, the number format looks like this:

$worksheet->getCell('A2')->setValue(.21);
$worksheet->getStyle('A2')->getNumberFormat()->setFormatCode('0%');

You are free to use setValueExplicit():

$worksheet->getCell('A2')->setValueExplicit(.21, PHPExcel_Cell_DataType::TYPE_NUMERIC);
$worksheet->getStyle('A2')->getNumberFormat()->setFormatCode('0%');

 

Alternatively, you can enable the AdvancedValueBinder.php at the beginning of your script.

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );

$worksheet->getCell('A3')->setValue('21%');

The intention with AdvancedValueBinder.php is that it behaves like Microsoft Office Excel, automatically setting number formats for you, recognizing dates, times, percentages etc.

Jan 22, 2010 at 3:02 PM

Using your first example:

 

$worksheet->getCell('A1')->setValue(.21);
$worksheet->getStyle('A1')->getNumberFormat()->setFormatCode('0.00%');

I'm getting values that display 10000% or 8750%

The datatype in my MySQL table are decimal (5,2) and look like 100 or 87.50

 

Developer
Jan 22, 2010 at 3:07 PM
SFDonovan wrote:

Using your first example:

 

$worksheet->getCell('A1')->setValue(.21);
$worksheet->getStyle('A1')->getNumberFormat()->setFormatCode('0.00%');

I'm getting values that display 10000% or 8750%

The datatype in my MySQL table are decimal (5,2) and look like 100 or 87.50

 

Ok, then you need to divide by 100. Example:

$value = 87.50;

$worksheet->getCell('A1')->setValue($value / 100);
$worksheet->getStyle('A1')->getNumberFormat()->setFormatCode('0.00%');

The above should look like 87.50% when you open the file in MS Office Excel.

Jan 22, 2010 at 3:17 PM

Easy enough but my concern is why is my data being changed?  Why am I now forced to divide by 100?  Why did 100 turn into 1000, and 87.50 into 8750?

Developer
Jan 22, 2010 at 3:30 PM

When you type in a value such as 87.50% in MS Office Excel, the underlying value is actually 0.875, not 87.50.

You can verify this in MS Office Excel. Type 87.50% in cell A1



Change number format to 'General' to reveal the true, underlying value.



As you can see, it is 0.875 and not 87.5. Therefore, you need to use $cell->setValue(0.875)

Jan 22, 2010 at 3:46 PM

Thanks.

 

I have another question and will post a separate thread.