How to determine if an excel column is formatted as a date?

May 25, 2010 at 9:37 PM

I currently have a few different excel spreadsheets I'm trying to input into a MySQL DB. I'm trying to write a function that checks to see if the column is formatted as a date or not, and if so process it as a date into the db. Anybody know how to get formatting type from an excel column?

Coordinator
May 26, 2010 at 8:32 AM
if (PHPExcel_Shared_Date::isDateTime($objPHPExcel->getActiveSheet()->getCell('B1')))

May 27, 2010 at 1:10 AM
Edited May 27, 2010 at 1:17 AM
Using PHP 5.3 and PHPExcel 1.7.3, I get a failure for code:
<?php
require "PHPExcel.php";

$file = "xls/date.xls";

//Open the file
$objReader = PHPExcel_IOFactory::createReaderForFile($file);
$objReader->setReadDataOnly(true);
$PHPExcelObject = $objReader->load($file);
$PHPExcelObject->setActiveSheetIndex(0);
if (PHPExcel_Shared_Date::isDateTime($PHPExcelObject->getActiveSheet()->getCell('B1')))
	echo "Works";
else 
	echo "Fail";

Output is:
Fail

The XLS file has a single cell in it at B1 with date 5/21/2010 that got autoformatted to "Date" format
Coordinator
May 27, 2010 at 8:34 AM

$objReader->setReadDataOnly(true);
is your problem

 

If the workbook is read with data only, then no formatting information is read by PHPExcel; and the only way we can determine whether a cell contains a number or a date is by checking the formatting.

May 27, 2010 at 10:34 AM

Hello,

I used the below snippet to retrieve items from an xlsx file.

 <?php
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load("test.xlsx");
$objWorksheet = $objPHPExcel->getActiveSheet();

$highestRow = $objWorksheet->getHighestRow(); // e.g. 10
$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'

$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5

echo '<table>' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
  echo '<tr>' . "\n";

  for ($col = 0; $col <= $highestColumnIndex; ++$col) {
    echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '</td>' . "\n";
  }

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

 Question:

How can I check if the cell using the above iterations script ( $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() ) is a date/time value?

The above posts uses the A1 column references as for my files, date column varies. How can I check by using the current getValue?

 

Thanks


May 27, 2010 at 9:01 PM

Thanks, Mark. That fixed it.

Coordinator
May 27, 2010 at 10:58 PM
Edited May 27, 2010 at 11:12 PM

One of these days I'll modify the setReadDataOnly(true) method to read the number formatting code for dates so they can always be detected.

 

And you can convert the Excel date to a PHP date/timestamp or a dateTime object using the shared date methods:

$phpTimestamp = PHPExcel_Shared_Date::ExcelToPHP($excelDateValue);
$phpDateTimeObject = PHPExcel_Shared_Date::ExcelToPHPObject($excelDateValue);
Jun 3, 2010 at 10:36 PM

I need help !!!!

I am using "PHPExcel.php" to read my file, when I get the value of a date cell, the value comes incorrectly. The value in the spreadsheet is "12/01/2010", when I do a get the value returned is 40513

 

any ideas

Jun 3, 2010 at 10:42 PM

Are you using setReadDataOnly(true) ?

 

If you are, then formatting data isn't applied and you get the integer value (days since 1/1/1900) instead.  Comment out setReadDataOnly(true) or change it to setReadDataOnly(false) and you'll get it formatted as defined in the Excel sheet.

Jun 4, 2010 at 2:51 AM
didn't work for me.I have no idea what I am doing wring. This is what I have for code.
<?php

require_once 'C:/php-5.2.13/Excel/v1.1/Classes/PHPExcel.php';
require_once 'C:/php-5.2.13/Excel/excel_reader2.php';


class ReadFile2 extends PHPExcel{
    function read1(){
       
        $objPHPExcel = PHPExcel_IOFactory::load("CompanyTab.xls");
        return $objPHPExcel;
    }
}

$y = new ReadFile2();
       
$yy = $y->read1();

$val = $yy->setActiveSheetIndexByName('Sheet1')->getCellByColumnAndRow(2,2)->getValue();

print $val;


I get 40513




Actually,

On Thu, Jun 3, 2010 at 5:42 PM, mkommar <notifications@codeplex.com> wrote:

From: mkommar

Are you using setReadDataOnly(true) ?

 

If you are, then formatting data isn't applied and you get the integer value (days since 1/1/1900) instead.  Comment out setReadDataOnly(true) or change it to setReadDataOnly(false) and you'll get it formatted as defined in the Excel sheet.

Read the full discussion online.

To add a post to this discussion, reply to this email (PHPExcel@discussions.codeplex.com)

To start a new discussion for this project, email PHPExcel@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com


Coordinator
Jun 4, 2010 at 8:36 AM

This is correct behaviour.

The value in the cell is 40513. Open the file in Excel, select cellB2, and format as a number to confirm.
The only thing in Excel that differentiates a number from a date is the format mask.

$cellValue = $yy->setActiveSheetIndexByName('Sheet1')->getCellByColumnAndRow(2,2)->getValue();
echo $cellValue;
if (PHPExcel_Shared_Date::isDateTime($yy->setActiveSheetIndexByName('Sheet1')->getCellByColumnAndRow(2,2))) {
   echo " is a date -> ";
   $dateValue = PHPExcel_Shared_Date::ExcelToPHP($cellValue);
   echo date('d-M-Y',$dateValue);
} else {
   echo " is not a date";
}
Jan 19, 2012 at 2:39 PM
mkommar wrote:

Are you using setReadDataOnly(true) ?

 

If you are, then formatting data isn't applied and you get the integer value (days since 1/1/1900) instead.  Comment out setReadDataOnly(true) or change it to setReadDataOnly(false) and you'll get it formatted as defined in the Excel sheet.

 

The solution for your problem is...

    $totalSheets = $objPHPExcel->getSheetCount();

    function xls2Date($date)
    {
        $date = round($date);
        return date('d/m/Y',(((++$date > 25568) ? $date : 25569) * 86400) - ((70 * 365 + 19) * 86400));
    }

    echo '<table border=1>' . "\n";
    for ($sheet = 0; $sheet < $totalSheets; $sheet++)
    {
        $objPHPExcel->setActiveSheetIndex($sheet);
        $objWorksheet = $objPHPExcel->getActiveSheet();
        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        for ($row = 1; $row <= $highestRow; ++$row)
        {
            echo '<tr>' . "\n";
            for ($col = 0; $col <= $highestColumnIndex; ++$col)
            {
                $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                $format = $objWorksheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($col).$row)->getNumberFormat()->getFormatCode();
                switch ($format)
                {
                    /*** DATE ***/
                    case 'yyyy-mm-dd':
                    case 'yy-mm-dd':
                    case 'dd/mm/yy':
                    case 'd/m/y':
                    case 'd-m-y':
                    case 'd-m':
                    case 'm-y':
                    case 'mm-dd-yy':
                    case 'd-mmm-yy':
                    case 'd-mmm':
                    case 'mmm-yy':
                    case 'yyyy-mm-dd;@':
                    case 'yy-mm-dd;@':
                    case 'dd/mm/yy;@':
                    case 'd/m/y;@':
                    case 'd-m-y;@':
                    case 'd-m;@':
                    case 'm-y;@':
                    case 'mm-dd-yy;@':
                    case 'd-mmm-yy;@':
                    case 'd-mmm;@':
                    case 'mmm-yy;@':
                    case 'd/m/y h:mm':
                    case 'm/d/yy h:mm':
                    case 'd/m/yy\ h:mm;@':
                    case 'm/d/yy\ h:mm;@':
                    case 'yy/mm/dd;@':
                    case 'd/m;@':
                    case 'd/m/yy;@':
                    case 'dd/mm/yy;@':
                    case '[$-416]d\-mmm;@':
                    case '[$-416]d\-mmm\-yy;@':
                    case '[$-416]dd\-mmm\-yy;@':
                    case '[$-416]mmm\-yy;@':
                    case '[$-416]mmmm\-yy;@':
                    case '[$-416]d\ \ mmmm\,\ yyyy;@':
                    case '[$-409]d/m/yy\ h:mm\ AM/PM;@':
                    case '[$-F800]dddd\,\ mmmm\ dd\,\ yyyy':
                        $value = xls2Date($value); break;
                }
                echo '<td>' . $value .'</td>' . "\n";
            }
            echo '</tr>' . "\n";
        }
    }
    echo '</table>' . "\n";



Jan 19, 2012 at 2:40 PM
zsousa wrote:

I need help !!!!

I am using "PHPExcel.php" to read my file, when I get the value of a date cell, the value comes incorrectly. The value in the spreadsheet is "12/01/2010", when I do a get the value returned is 40513

 

any ideas

 

 

The solution for your problem is...

    $totalSheets = $objPHPExcel->getSheetCount();

    function xls2Date($date)
    {
        $date = round($date);
        return date('d/m/Y',(((++$date > 25568) ? $date : 25569) * 86400) - ((70 * 365 + 19) * 86400));
    }

    echo '<table border=1>' . "\n";
    for ($sheet = 0; $sheet < $totalSheets; $sheet++)
    {
        $objPHPExcel->setActiveSheetIndex($sheet);
        $objWorksheet = $objPHPExcel->getActiveSheet();
        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        for ($row = 1; $row <= $highestRow; ++$row)
        {
            echo '<tr>' . "\n";
            for ($col = 0; $col <= $highestColumnIndex; ++$col)
            {
                $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                $format = $objWorksheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($col).$row)->getNumberFormat()->getFormatCode();
                switch ($format)
                {
                    /*** DATE ***/
                    case 'yyyy-mm-dd':
                    case 'yy-mm-dd':
                    case 'dd/mm/yy':
                    case 'd/m/y':
                    case 'd-m-y':
                    case 'd-m':
                    case 'm-y':
                    case 'mm-dd-yy':
                    case 'd-mmm-yy':
                    case 'd-mmm':
                    case 'mmm-yy':
                    case 'yyyy-mm-dd;@':
                    case 'yy-mm-dd;@':
                    case 'dd/mm/yy;@':
                    case 'd/m/y;@':
                    case 'd-m-y;@':
                    case 'd-m;@':
                    case 'm-y;@':
                    case 'mm-dd-yy;@':
                    case 'd-mmm-yy;@':
                    case 'd-mmm;@':
                    case 'mmm-yy;@':
                    case 'd/m/y h:mm':
                    case 'm/d/yy h:mm':
                    case 'd/m/yy\ h:mm;@':
                    case 'm/d/yy\ h:mm;@':
                    case 'yy/mm/dd;@':
                    case 'd/m;@':
                    case 'd/m/yy;@':
                    case 'dd/mm/yy;@':
                    case '[$-416]d\-mmm;@':
                    case '[$-416]d\-mmm\-yy;@':
                    case '[$-416]dd\-mmm\-yy;@':
                    case '[$-416]mmm\-yy;@':
                    case '[$-416]mmmm\-yy;@':
                    case '[$-416]d\ \ mmmm\,\ yyyy;@':
                    case '[$-409]d/m/yy\ h:mm\ AM/PM;@':
                    case '[$-F800]dddd\,\ mmmm\ dd\,\ yyyy':
                        $value = xls2Date($value); break;
                }
                echo '<td>' . $value .'</td>' . "\n";
            }
            echo '</tr>' . "\n";
        }
    }
    echo '</table>' . "\n";





Coordinator
Jan 19, 2012 at 9:28 PM
Edited Jan 19, 2012 at 9:31 PM

Why not simply use

$dateValue = PHPExcel_Shared_Date::ExcelToPHP($cellValue);

 and

PHPExcel_Shared_Date::isDateTime();


as described in the previous answers?

What does your method do that the built-in methods don't?

 

Jan 20, 2012 at 4:32 PM

 damn you're right! :X

I lost so much time to describe that code, i didn't knew the built-in methods

Thanks

Coordinator
Jan 20, 2012 at 5:08 PM

Adventage of the built-in methods is that they can handle both dates and times, and non-standard date/time format masks; additional date complexities such as the Windows 1900/Mac 1904 calendars, and work with either PHP date/time values or with PHP dateTime objects.

Jan 20, 2012 at 5:46 PM

Very nice!
Are there another built-in conversion methods for currency, number or percentage?

Thanks in advance

Mar 24, 2013 at 7:59 PM
PHPExcel_Shared_Date::ExcelToPHP is provided
    $onevalue=$cell->getCalculatedValue();

    $onevalue=date('Y-m-d',PHPExcel_Shared_Date::ExcelToPHP($onevalue));
Sep 5, 2014 at 8:40 AM
hello..!!
i m using PHPExcel_shared_date::ExcelToPHP but problem is that when i m import the excel file without date field it automatically fill that perticular cell which is empty in date column as today's date..
so please help me out..!!


Thanks in advance!!