Cannot display date, time and numbers correctly using PHPExcel

Question

This is my first stackoverflow question. I am trying to use PHPExcel to create an HTML table from an .xlsx file. You can find my project in: http://rahulr92.x10.mx/excel/index.php Log in with the username "admin" and you will find the option "View table". This page displays a table from a previously downloaded .xlsx file. I am new to PHPExcel and used some standard code that I found on the Internet. Here he is:

<?php require_once '/Classes/PHPExcel.php'; $objReader = PHPExcel_IOFactory::createReader('Excel2007'); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load("..\excel.xlsx"); $objWorksheet = $objPHPExcel->getActiveSheet(); $highestRow = $objWorksheet->getHighestRow(); $highestColumn = $objWorksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $user=strtolower($_GET['user']); $r_count=0; echo '<table border="1">' . "\n"; for ($row = 5; $row <= $highestRow; ++$row) { if ($row <7 || $user=="admin" || strstr(strtolower($objWorksheet->getCellByColumnAndRow(8, $row)->getValue()),$user )) { if($row>7)$r_count++; for ($col = 0; $col <= $highestColumnIndex; ++$col) { if(PHPExcel_Shared_Date::isDateTime($objWorksheet->getCellByColumnAndRow($col,$row))) echo '<td>' . date("d MY",PHPExcel_Shared_Date::ExcelToPHP($objWorksheet->getCellByColumnAndRow($col, $row))) . '</td>' ; else echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '</td>' . "\n"; } echo '</tr>' . "\n"; } } echo '</table>' . "\n"; echo "No. of Entries: ".$r_count; ?> 

Sorry for the sub-optimal code. When I run the php file, I get a table and all the rows in it are displayed correctly, but the datetime and number fields are filled with seemingly random nonsense. Please take a look at this from the link above. I did some research and found out about this function isDateTime (). But it does not work or is probably used incorrectly. I have a limited time for this project, so it would be great if someone could point me in the right direction. It is unfortunate if the answer was very obvious. Thanks a lot in advance. Rahul

Answer 1

Do not set $objReader->setReadDataOnly(true); if you want to define date / time values ​​... Excel stores dates / times as floating point numbers, and the only way to distinguish a number from date / time is to use format masking: isDateTime() uses this format masking to determine if whether the value is a number or date / time ... $objReader->setReadDataOnly(true) tells the file reader to read only data (numbers) and ignore format masks, so it only reads raw data.

However, I'm not sure I understand exactly what you mean by “random ravings” ... numbers should still appear as numbers ... can you give an example.

EDIT

Note that cell methods such as getFormattedValue() return numbers as they are masked in the getFormattedValue() , so date / time values ​​will be formatted as dates / time, numbers will be displayed with the appropriate number of decimal places, thousands of separators or currency or percentages (if they had the appropriate formatting in the Excel file), so you do not need to test isDateTime() .... until $objReader->setReadDataOnly(true) has been set for reading.