cell formats in phpexcel

Question

i'm trying to read some excel files with phpexcel, which works ok so far. phpexcel is a bit too clever though, and removes leading zeros from my cells. i guess i need to tell it to treat the cell as a text string, not as general or number. but i've failed. i even found threads on stackoverflow about this but the suggested solutions simply wouldn't work.

below is a snippet of the stuff i'm working on.

foreach(@$objWorksheet->getRowIterator() as $row){
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);
    foreach($cellIterator as $cell){
        #$objWorksheet->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode('@');
        $cells[] = $cell->getValue();
    }
}

any idea? i don't want to limit myself to only read numeric content, as i don't have any control over what the users will upload. treating everything as strings would be perfect.

/peder

Mark Baker · Accepted Answer · 2010-11-21T18:02:37.423

The getValue() method does exactly what it should do, without trying to be clever. It returns the actual value stored in the cell. If that cell contains an integer, it returns an integer; if the cell contains a float, it returns a float; if it contains a string, it returns a string. Being clever is returning that value as a formatted string (with leading zeroes if appropriate), then you need to use a rather different method, and apply the cell formatting to the returned value.

foreach($cellIterator as $cell){ 
   $cells[] = PHPExcel_Style_NumberFormat::toFormattedString( $cell->getValue(),
              $objPHPExcel->getCellXfByIndex( $cell->getXfIndex() )->getNumberFormat()->getFormatCode()
   );
}

or if the cell contains a formula:

foreach($cellIterator as $cell){ 
   $cells[] = PHPExcel_Style_NumberFormat::toFormattedString( $cell->getCalculatedValue(),
              $objPHPExcel->getCellXfByIndex( $cell->getXfIndex() )->getNumberFormat()->getFormatCode()
   );
}

And please don't use @ to try and suppress errors. PHPExcel throws exceptions, and you really should want to trap these.

However, for what you're doing, you might consider the worksheet's toArray() method, that will return an array of all the cell values in the worksheet.

thanks, all good stuff. but - applied this and tried to parse a csv file, but it seems like all my cells are formatted as "general". file looks like this: http://dl.dropbox.com/u/252696/csv.csv is this a limitation of the csv format, and if so - wouldn't it be better if phpexcel just returned the contents of the cells? — fjallstrom, Nov 21 '10 at 18:42
If PHPExcel simply returned the "strings" it would have lost all its ability to process data from Excel worksheets. If you're just trying to read a CSV file into an array, your best option is PHP's built-in csv handling functions, like fgetcsv() — Mark Baker, Nov 21 '10 at 19:55
If you want PHPExcel to treat input from the CSV file as strings rather than numbers, then you can write a simple "read filter" that ensures all input from the file is treated as strings. — Mark Baker, Nov 21 '10 at 19:57

score 1 · Answer 2 · edited Aug 15 '12 at 12:11

1

none of provided solutions worked for me (CSV import with PHPExcel v1.7.5) i solved by set value binder like this:

PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_StringValueBinder());

before PHPExcel_IOFactory::createReader('CSV')

edited Aug 15 '12 at 12:11

Alfred

21,058
61
167
249

answered Aug 13 '12 at 11:45

jaro

11
1

score 1 · Answer 3 · edited Sep 22 '11 at 17:35

There is something easier than those Iterators. For doing foreach You can also use toArray method, in example:

$active_sheet = $objPHPExcel -> getActiveSheet();
foreach($active_sheet -> toArray() as $row_n => $row){
 foreach($row as $cell_n => $cell){
  // operations here
 }
}

Worked fine for me and also seems to be faster then Iterators, just as Mark Baker sad.

In processing CVS I see one main problem: user have to export data form Excel and this process can be confusing for most of them. That's why i searched for solution for import straight from Excel.

In usage of those iterators is something strange for me and I really didn't get into it much. While accessing data by using iterator it returns few (form 2 to 4 in my case) serialized objects and getting data out of them was nightmare.

cell formats in phpexcel

3 Answers3