All cells like strings PHP Excel

Question

I'm trying to read some data from excel file. Everything is good if i have ex.50.44% in my excel cell PHP excel format it like float 0.0531 i dont want to format any numbers and i want all cell to be casted as string. How can i accomplish this before making the data toArray();

Here my sample code

try {
    $objPHPExcel = PHPExcel_IOFactory::load($upload_url);
} catch(Exception $e) {
    die('Error loading file "'.pathinfo($upload_url,PATHINFO_BASENAME).'": '.$e->getMessage());
}

 $objPHPExcel->getDefaultStyle()
->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);


$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
var_dump($sheetData);
exit;

Any help will be appreacited.

score 0 · Answer 1 · answered Sep 01 '15 at 10:55

0

If you simply want all values as strings rather than the appropriate datatype, then retrieve your array as you're doing at the moment, then execute:

array_walk_recursive(
    $sheetData,
    function (&$value) {
        $value = (string) $value;
    }
);

answered Sep 01 '15 at 10:55

Mark Baker

209,507
32
346
385

The values are already formated and broken. 'O' => string '0.1096' (length=6) 'P' => string '0.2955' (length=6) 'Q' => string '0.3065' (length=6) 'R' => string '0.3198' (length=6) The original values is 50.44% and in the array is 0.531 – mstojanov Sep 01 '15 at 11:20
Remove `$objPHPExcel->getDefaultStyle() ->getNumberFormat() ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);` because this will break formatting. Otherwise, are you doing anything else that might be disabling formatting? (e.g. setting `loadDataOnly(true)` or similar? – Mark Baker Sep 01 '15 at 11:33
I want to disable formatting. I dont want PHP excel to format my cell values. In my cell in .xls file value i have 50.44% and PHP excel format to 0.05311. I also tried without $objPHPExcel->getDefaultStyle() ->getNumberFormat() ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); – mstojanov Sep 01 '15 at 11:38
The value stored in MS Excel is `0.05311`.... MS Excel uses a "number format mask" to display that value as `50.44%`.... PHPExcel does exactly the same, unless you change the mask..... so don't try to override that mask by setting your own format code.... if you disable formatting", or change the formatting mask, then you'll never get `50.44%` – Mark Baker Sep 01 '15 at 11:48
So how can i get the original value ? – mstojanov Sep 01 '15 at 12:33
The original value is `0.05044`. the formatted value is `50.44%`.... use `toArray()` with `true` as the third argument to get PHPExcel to return the formatted value, `false` if you want the original/raw value..... but don't change any formatting codes yourself – Mark Baker Sep 01 '15 at 13:15

All cells like strings PHP Excel

1 Answers1