unable to fetch data from cell with the percentage format data

Topics: Developer Forum, User Forum
Jul 20, 2012 at 11:49 AM
Edited Jul 20, 2012 at 2:46 PM

i have cell with the percentage format data like '100%'.

trying to get data using reader.

all cells with format data different from percentage have their values and i can get them using getCalculatedValue(), getValue etc.

but all this methods can't give me value with percent.

PHPExcel_Style_NumberFormat::toFormattedString and other methods don't help. they just return empty string.

so and method GetDataType returns null.

what i did wrong and how i can get cell values like 5% or 5,0% anything?

i use PHPExcel 1.7.7, Windows7, framework yii, php 5.3.13

I will be grateful for any help

 

 


Coordinator
Jul 21, 2012 at 12:11 PM

What happens when you use the getFormattedValue() method?

What is the actual value in the cell (as returned by getCalculatedValue()?

What is the formatting mask for the cell?

What are your locale settings?

Jul 23, 2012 at 7:41 AM

getFormattedValue method return an empty string.

getFormattedValue method return an empty string.

The actual value in the cell '19,10%'

formatting mask, if I understand you correctly - percentage

What exactly do you mean asking about locale settings?

 

All this methods return an empty string:

echo $cell->getCalculatedValue();
echo $cell->getValue();
echo $cell->getFormattedValue();
echo PHPExcel_Style_NumberFormat::toFormattedString( $cell->getValue(),
                                                  $objPHPExcel->getCellXfByIndex( $cell->getXfIndex() )->getNumberFormat()->getFormatCode());

even after this i have nothing:
$work_sheet->getStyleByColumnAndRow($col,$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);                                   

Coordinator
Jul 23, 2012 at 9:10 AM

Can you please provide an example file that demonstrates this problem? It's definitely abnormal behaviour

Jul 23, 2012 at 10:41 AM

file here

this file has 3 columns.

$cell = $work_sheet->getCellByColumnAndRow($col, $row);// $col = 2; $row = 7; (or B7)

// an empty string

echo var_export($cell->getCalculatedValue(), true);


// an empty string

echo var_export($cell->getValue(), true);

// an empty string
echo var_export($cell->getFormattedValue(), true);

// an empty string
echo PHPExcel_Style_NumberFormat::toFormattedString( $cell->getValue(),
                           $objPHPExcel->getCellXfByIndex( $cell->getXfIndex() )->getNumberFormat()->getFormatCode());

 

Thanks a lot for help                                   

                                          

                                 

Coordinator
Jul 23, 2012 at 12:59 PM
Edited Jul 23, 2012 at 1:00 PM

Thanks for the link to a demonstration file that allowed me to test and identify this problem quickly.

The issue was on my part when developing the original OOCalc Reader, I'd omited testing for the percentage and currency dataTypes that exist in ods files.

This has now been fixed in the develop branch on github - https://github.com/PHPOffice/PHPExcel - and will be included in the 1.7.8 release

Note that OOCalc is basically a data reader, and has very limited support for styles, but date, time, percentage and currency datatypes are now all styled with simple style information, allowing them to be identified

 

Jul 23, 2012 at 2:08 PM
Edited Jul 23, 2012 at 2:18 PM

Thanks for your work!!!!=)

Percentage data is diplayed correctly.

But methods $work_sheet->getHighestRow(); and PHPExcel_Cell::columnIndexFromString($work_sheet->getHighestColumn()); now return wrong values.

52 row and 1024 columns for that file.

 

And now all cells pass next validation:

if(PHPExcel_Shared_Date::isDateTime($cell))

even if they have string value

 

check this please.

Coordinator
Jul 23, 2012 at 10:32 PM

Three different bugs fixed in the develop branch on github, two of which effect you:

isDateTime() was recognising the e in "General" as epoch, so treating it as a date. This was a core PHPExcel bugfix.

OOCalc reader modified to parse the "number-rows-repeated" attribute as well as "number-columns-repeated" attribute. Libre Office in particular uses these attributes extensively. However, I also modified the reader to process only cells that contain a "value-type" attribute, so blank cells are not loaded (unless they are part of a merged group).

The file you provided actually contains two empty merged cell groups (AP3:AQ3 and AR3:AS3).

If you read the file with setReadDataOnly(FALSE) - which is the default - then it will identify these merge groups, and your worksheet range will be A1:AS7. If you use setReadDataOnly(TRUE) then the merge groups will be ignored, and the worksheet range will be A1:C7.

 

Jul 24, 2012 at 7:32 AM
Edited Jul 24, 2012 at 7:35 AM

Fatal error: Undefined class constant 'GENERAL' in Z:\home\yii\www\protected\extensions\PHPExcel\Shared\Date.php on line 268

 

downloaded new files and i got this error while parsing test.ods file 

this validation throw it:

 if (PHPExcel_Shared_Date::isDateTime($cell))

Coordinator
Jul 24, 2012 at 7:42 AM

Fixed

Jul 24, 2012 at 9:58 AM

thanks you very much!=)