Cell formats in phpexcel

Question

I am trying to read some excel files with phpexcel, which is working fine so far. phpexcel is a little smarter, but removes leading zeros from my cells. I think I need to say that it treats the cell as a text string, and not as a general or a digit. but i failed. I even found threads in stackoverflow about this, but the suggested solutions just didn't work.

below is a snippet of the material 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 just reading numerical content, since I have no control over what users will download. handling everything as strings would be perfect.

/ Peder

Answer 1

The getValue () method does exactly what it should do without trying to be smart. It returns the actual value stored in the cell. If this 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 smart returns this value as a formatted string (with zero leading zeros, if necessary), then you need to use a completely different method and apply 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 the formula:

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

And please do not use @ to try to suppress errors. PHPExcel throws exceptions, and you really should want to catch them.

However, for what you are doing, you can consider the toArray () method of the worksheet, which will return an array of all the cell values ​​in the worksheet.

Answer 2

There is something lighter than those Iterators. You can also use the toArray method to execute foreach, for example:

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

It worked great for me, and also seems to be faster than Iterators, just like Mark Baker.

When processing CVS, I see one main problem: the user needs to export the Excel data form, and this process can be confusing for most of them. This is why I was looking for a solution to import directly from Excel.

There is something strange for me in using these iterators, and I really didn’t really go into it much. When accessing data using an iterator, it returns several (form 2 to 4 in my case), serializing objects and getting data from them was a nightmare.

Answer 3

none of the solutions provided worked for me (import CSV with PHPExcel v1.7.5) I decided to set the binder value as follows:

 PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_StringValueBinder()); 

before PHPExcel_IOFactory::createReader('CSV')