How to read excel one cell date(DD/MM/YYYY) value and convert date format into (M/D/YYYY) using PHPExcel library

Question

I'm converting excel file data into csv file using php PHPExcel library. currently i'm getting all data from excel into csv. but i need to change date format in one particular cell [ex. B2] in excel its date(DD/MM/YYYY) i want to change it as M/D/YYYY and same it to csv.

Note: I'm using phpexcel library to read excel fi

i tried the following code:

<?php
$excel_lib = '/var/www/mp/cronfiles/data/Classes/PHPExcel/IOFactory.php';
include($excel_lib);
$filearray = array('file1.xlsx','file2.csv','file3.csv');
$file2 = fopen(dirname(__FILE__).'/'.$filearray[0],"r");
if (!$file2) {
                echo "File not exisit";
            }
$inputFileType1 = PHPExcel_IOFactory::identify($filearray[0]);
$objReader1 = PHPExcel_IOFactory::createReader($inputFileType1);
$objPHPExcel1 = $objReader1->load($filearray[0]);
$sheet1 = $objPHPExcel1->getSheet(0);

      $highestRow1 = $sheet1->getHighestRow();

      $highestColumn1 = $sheet1->getHighestColumn();

      $rowData1 = array();

  for ($row1 = 1; $row1 <= $highestRow1; $row1++) { 
    $rowData1[] = $sheet1->rangeToArray('A' . $row1 . ':' . $highestColumn1 . $row1, null, true, true);
  }

  foreach($rowData1 AS $k => $v){
      foreach ($v AS $fk => $fv){
          $csv[] = $fv;
          }
      }

$excel_date = 'As of '.$csv[1][1]; //its B2 cell in excel value ex.[echo $excel_date; output 22/6/2019]
$changed_date = date_format('M/d/Y', $excel_date);
echo $change_date;

I'm getting the following error:

Warning: date_format() expects parameter 1 to be DateTimeInterface, string given in var/www/html/mp/cronfiles/data/myfile.php**

My question: 1. How to get excel cell value in date format so i can convert it.

Note: In excel the B2 cell value that i'm trying to get is in date format only. but its changing depend on my excel default language.

Please let me know if you need more information. I hope i'll get fix here.

Thanks in advance.

By Agan

Answer 1

You are received date format in excel date format. First you have to convert to php datetime format then convert to desire output format.

Use this code :

$excel_date = $csv[1][1];  //22/6/2019;   
$changed_date = DateTime::createFromFormat('d/m/Y', $excel_date)->format('D/M/Y');
echo $changed_date;

for your requirement to decide which format is excel date is given use below code :

function convert_date_format($old_date = '')     {  
 $old_date = trim($old_date);        
    if (preg_match('/^(0[1-9]|1[0-2]|[1-9])\/(0[1-9]|[1-2][0-9]|3[0-1])\/[0-9]{4}$/', $old_date)) // MySQL-compatible YYYY-MM-DD format  
     {   return 'm/d/Y';     }   
     elseif (preg_match('/^(0[1-9]|[1-2][0-9]|3[0-1])\/(0[1-9]|1[0-2]|[1-9])\/[0-9]{4}$/', $old_date)) // DD-MM-YYYY format  
     {   return 'd/m/Y';
     }    
 }

$excel_format = convert_date_format($excel_date);
$changed_date = DateTime::createFromFormat($excel_format, $excel_date)->format('D/M/Y');
echo $changed_date;