How to read any cell as text

Topics: Developer Forum
May 3, 2013 at 2:22 AM
Hello, fairly new PHPExcel user here, but pretty experienced with PHP. I really like PHPExcel, and think it will be incredibly useful for my application. Thanks to all who support it.

I think this is a pretty simple question, but I have spent a bunch of hours Googling and even reading the documentation ;) with no success.

I get spreadsheets from all over the world, each formatted in its own way. In other words, I have no idea whether a given cell will be a string, date, numeric, etc. What I need to do is read the cell contents as displayed in the spreadsheet, regardless of what the underlying Excel data type is. Then I store the contents in a database.

Here's a recent example. Let's say cell A1 looks like "24:00:00" when you open it in Excel. But if you look at the formula, it's value is "1/1/1900 12:00:00 AM". I need to retrieve and store the contents of A1 as "24:00:00".

But no matter what I've tried, I keep getting "1" as the cell content. I realize that this is the correct value for 1/1/1990, the MS date system. I cannot explicitly reformat the contents of A1 to get "24:00:00" because the next spreadsheet from the next client will have something totally different in A1.

I've tried using toArray and getFormattedValue. I am NOT using setReadDataOnly. I'm having PHPExcel choose the reader type (love this feature!). I was intrigued by the setValueExplicit function, but I need a "getValueExplicit".

To reiterate, the above example of "24:00:00" is just an example, I've run into multiple situations where the value I've retrieved does not look like what is displayed in the spreadsheet.

The issue is not with my database (SQL Server) because I haven't gotten that far yet. I've done var_dump (with toArray) and also written to a csv with the PHPExcel csv writer, and comparing the results to what is displayed in the spreadsheet.

Again, thanks for the wonderful tool, and any suggestions you can offer.

Dave
Coordinator
May 3, 2013 at 7:37 AM
getValue() will return 1 for your example, but getFormattedValue() or getCalculatedValue() should certainly work converting the time/datestamp to a formatted time and/or date string automatically; but without knowing exactly what's in the cell and what number format masking it uses I can't really expand any further. You could also consider getDataType() which returns the type set by get getValueExplicit() but which is PHPExcel_Cell_DataType::TYPE_NUMERIC for both numbers and for dates.
May 3, 2013 at 6:01 PM
Mark,

Thank you for your quick reply! Most of what I've learned about PHPExcel to date are from your answers to newbies like me in various forums like this!

I tried both getFormattedValue() and getCalculatedValue() for my "24:00:00" example, and both return "1". If I open the spreadsheet and look at the cell format, it is custom: "[h]:mm:ss". In fact, I'm pretty sure that every case in which I'm unable to retrieve a cell's contents as they appear in the spreadsheet, the cell has had a custom format.

Another example is a cell which has what looks like a straightforward date, "1/15/2013". But its Excel format was also custom, not one of the usual Excel date formats. No matter how I read it, the contents are converted to "1-15-2013" (i.e. slashes changed to dashes). Believe it or not, this simple substitution causes grief for my application (don't ask me why, very complicated ;). If I change the cell format to the standard Excel "mm/dd/yy" format, then I can read it with slashes.

So my problem seems to revolve around custom cell formats, primarily with date and time. I could upload an example spreadsheet, but not sure how.

For the record, here is my code:

$XLFileType = PHPExcel_IOFactory::identify('tiny.xls');
$objReader = PHPExcel_IOFactory::createReader($XLFileType);
$objReader->setLoadSheetsOnly($sheetname);
$objPHPExcel = $objReader->load($XLFileName);
$objWorksheet = $objPHPExcel->setActiveSheetIndexByName('sheet1');
echo 'cell A1 is ' . $objPHPExcel->getActiveSheet()->getCell('A1')->getFormattedValue();

Regards,
Dave
May 3, 2013 at 6:04 PM
minor corrections to posted code above:

$XLFileType = PHPExcel_IOFactory::identify('tiny.xls');
$objReader = PHPExcel_IOFactory::createReader($XLFileType);
$objReader->setLoadSheetsOnly('sheet1');
$objPHPExcel = $objReader->load('tiny.xls');
$objWorksheet = $objPHPExcel->setActiveSheetIndexByName('sheet1');
echo 'cell A1 is ' . $objPHPExcel->getActiveSheet()->getCell('A1')->getFormattedValue();