
I am new to PHPExcel using it to display a spreadsheet as a web page where visitors can enter values and calculate the results.
Everything seems to be working OK except for calculations including cells formatted as percentages.
For example, if I enter '4' in a cell formatted as a percentage, the real value that should be used in a calculation is 0.04 but 4 is being used instead.
What do I need to do for PHPExcel to handle percentage formatted cells properly?
The spreadsheet is at: http://tinyurl.com/22udfl3
The web page is here: http://tinyurl.com/2f6qlwg
I have searched the discussions and the web and have not been able to find an answer so hopefully someone here can help me!
Thanks,
Neil.


Coordinator
Sep 1, 2010 at 1:08 PM

Within Excel, when you enter 4 in a cell (A2), then format it as a percentage, the cell still contains the value 4 rather than 400%. If you use that cell value in a calculation =A1*A2 where A1 contains 10, your answer will be 40. If you have a cell
that is formatted as a percentage, and enter a 4 in that cell, Excel automatically divides the number that you have entered by 4, so your cell will contain 0.04 instead of the 4 that you entered.
This behaviour can be emulated in PHPExcel if you use a value binder. I've not tested this, but something like:
class PHPExcel_Cell_AdvancedValueBinder extends PHPExcel_Cell_DefaultValueBinder implements PHPExcel_Cell_IValueBinder
{
public function bindValue(PHPExcel_Cell $cell, $value = null)
{
// Find out data type
$dataType = parent::dataTypeForValue($value);
if ($dataType == PHPExcel_Cell_DataType::TYPE_NUMERIC) {
if ($cell>getParent()>getStyle( $cell>getCoordinate() )>getNumberFormat() == PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE ) {
$cell>setValueExplicit( $value / 100, PHPExcel_Cell_DataType::TYPE_NUMERIC);
return true;
}
}
// Not bound yet? Use parent...
return parent::bindValue($cell, $value);
}
}
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
$objPHPExcel = new PHPExcel();
Note that using the AdvancedValueBinder provided in the distribution would allow you to set a cell value to a string '4%', and would automatically convert this to 0.04 and apply percentage formatting to that cell.



Hi Mark!
Thanks for the prompt and useful response!
I was aware of the value binder from the documentation included in the PHPExcel download but there's no detailed example as you have shown!
I'll give it a try again,
Thanks,
Neil.

