CODEWITHSUNDEEP
phpexcelphpexcel
user5330699
user5330699

Reputation:

Read Date Format in PHP EXCEL

I have read excel file using PHP excel .

But that excel file contain some date (time format) PHP excel return wrong values for that case

My code is in below

enter code hereinclude 'Classes/PHPExcel/IOFactory.php';

$inputFileName = 'index.xlsx';

//  Read your Excel workbook
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
} catch (Exception $e) {
    die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME)
        . '": ' . $e->getMessage());
}

//  Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();

//  Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++) {
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
        NULL, TRUE, FALSE);

    foreach ($rowData[0] as $k => $v)
        echo "Row: " . $row . "- Col: " . ($k + 1) . " = " . $v . "<br />";
}

any one can help for this.. Have some specific function for this case in PHPExcel..

My input excel file is in below

2013-12-12, 2013-12-13

My output is in below

41621, 41631

Have some method to covert date format output data?

Upvotes: 18

Views: 66445

Answers (5)

dasun
dasun

Reputation: 41

I noticed that you used the PHPExcel library, and within that library, there is a function call PHPExcel_Shared_Date::ExcelToPHP() to convert the date values. This function can be utilized to extract the desired result.

include 'Classes/PHPExcel/IOFactory.php';

$inputFileName = 'index.xlsx';

// Read your Excel workbook
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
} catch (Exception $e) {
    die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME)
        . '": ' . $e->getMessage());
}

// Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();

// Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++) {
    // Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);

    foreach ($rowData[0] as $k => $v) {
        if (PHPExcel_Shared_Date::isDateTime($sheet->getCellByColumnAndRow($k, $row))) {
            $dateValue = $sheet->getCellByColumnAndRow($k, $row)->getValue();
            $timestamp = PHPExcel_Shared_Date::ExcelToPHP($dateValue);
            $formattedDate = date('Y-m-d', $timestamp);
            echo "Row: " . $row . "- Col: " . ($k + 1) . " = " . $formattedDate . "<br />";
        } else {
            echo "Row: " . $row . "- Col: " . ($k + 1) . " = " . $v . "<br />";
        }
    }
}

I added a check using PHPExcel_Shared_Date::isDateTime() to determine if a cell contains a date value. If it does, I retrieve the raw date value using $sheet->getCellByColumnAndRow($k, $row)->getValue(). Then, I convert the Excel date value to a PHP timestamp using PHPExcel_Shared_Date::ExcelToPHP(). Finally, I format the timestamp to the desired format using date().

This will output the dates in the format 'Y-m-d'.

Upvotes: 0

Max Cuttins
Max Cuttins

Reputation: 613

To retrieve the date you have to convert from Excel Timestamp to Linux Timestamp. What you need to know is:

  • Excel express dates in days while Linux do it in seconds.
  • Excel start to count from year 1900 while Linux do it from year 1970.
  • All timestamps are in GM (greenwitch meantime), so you should not convert it with gmdate(); but instead use just date();

So, in order to convert from Excel to Linux you have to:

  • remove 70 years of days: 25569
  • multiply for seconds in a day: 86400

This is the code:

function fromExcelToLinux($excel_time) {
    return ($excel_time-25569)*86400;
}

$linux_time = fromExcelToLinux(30637); 
echo date("Y-m-d",$linux_time);
//should print 1983-11-17

That's all the folks.

Upvotes: 9

Madhan
Madhan

Reputation: 107

If you are using excel_reader for excel import, You can use Code like this. 

include('excel_reader/xlsxreader/simplexlsx.class.php');
$xlsx_class = new SimpleXLSX();
$j['IST_Time'] = '43010';
echo date('Y-m-d',$xlsx_class->unixstamp($j['IST_Time'])); // Result will be 2017-10-02

And For Time 

$j['IST_Time'] = '0.416666666667'; 
echo date('h:i A',$xlsx_class->unixstamp($j['IST_Time'])); //10:00 AM

or view simplexlsx.class.php

Upvotes: 0

user4272288
user4272288

Reputation:

An other way is using gmdate:

$excel_date = 43010; //here is that value 41621 or 41631
$unix_date = ($excel_date - 25569) * 86400;
$excel_date = 25569 + ($unix_date / 86400);
$unix_date = ($excel_date - 25569) * 86400;
echo gmdate("Y-m-d", $unix_date);

//result is 2017-10-02

Upvotes: 22

murrometz
murrometz

Reputation: 904

When you read xls file with PHPExcel_Reader_Excel5 lib, the data in file is 39984, but Excel formats it using a date formatting mask as '2009-06-20'?

Excel holds dates as a count of the number of days since 1st January 1900 (on Windows 1900 calendar). PHPExcel stores its dates in the same way, but doesn't automatically format them for you.

You can format them yourself using PHPExcel_Style_NumberFormat::toFormattedString(39984,PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY)
or any of the other format masks in
PHPExcel_Style_NumberFormat
, or convert it to a PHP date using
PHPExcel_Shared_Date::ExcelToPHP(39984) and then use PHP's date()
function to format it as you wish

Example:

$val = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($cell->getValue()));

Upvotes: 40

Related Questions