Unable to correctly display the contents of the cell containg date

Topics: Developer Forum
Dec 1, 2010 at 9:45 PM

Hi i am trying to parse an xls, using the phpexcel .

The problem with the below code is that it prints the date in General format. The condition "if (PHPExcel_Shared_Date::isDateTime($cell))" always fails. I tried this code by creating new xls sheet containing only date field. At that time also it displayed the date in general format instead of Date format.

The following is my code.

                                 require_once 'Classes/PHPExcel.php';

                                $objReader = PHPExcel_IOFactory::createReaderForFile($fullFilePath);
                                $objReader->setReadDataOnly(true);

                                $objPHPExcel = $objReader->load($fullFilePath);
                                $objWorksheet = $objPHPExcel->getActiveSheet();

                                echo '<table border="1">' . "\n";
                                foreach ($objWorksheet->getRowIterator() as $row) {
                                        echo '<tr>' . "\n";

                                        $cellIterator = $row->getCellIterator();
                                        $cellIterator->setIterateOnlyExistingCells(false);
                                        foreach ($cellIterator as $cell) {
                                                if (PHPExcel_Shared_Date::isDateTime($cell)) {
                                                        echo '<td > ' . date("d M Y",PHPExcel_Shared_Date::ExcelToPHP($cell->getValue())) . '</td>' ;
                                                }else{
                                                        echo '<td >' . $cell->getValue() . '</td>' ;
                                                }
                                        }

                                        echo '</tr>' . "\n";
                                }
                                echo '</table>' . "\n";

 

My php version is 5.2.3

and phpexcel version is  1.7.4.

Microsoft Excel version i am using is 2007

Operating system is Linux (redhat)

Coordinator
Dec 1, 2010 at 10:12 PM

Your problem is

$objReader->setReadDataOnly(true);

This tells the Reader to read only the raw cell data, with none of the formatting; and Excel differentiates between numbers and dates based on the number format mask... PHPExcel works the same way.

I know that this seems illogical, and I've begun to modify the Reader code so that date format masks will be loaded even with ReadDataOnly set to true, but that won't be ready for the next release.

Dec 1, 2010 at 10:43 PM

Thank you Very much.