number to date and time issues

Topics: Developer Forum, User Forum
Jul 19, 2011 at 10:23 PM

hi ,

i am a beginner in using php , and i am using your library php excel to read a data file .the biggest issue i am facing is that when ever i read date and time from cell it converts to numbers like 40725.999791667 should be 2011-07-01 23:59 , 

i am facing two other issues if you could will be great help !

set_time_limit (6000);
require_once('classes/phpexcel.php');
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("2.xlsx");
$objWorksheet = $objPHPExcel->setActiveSheetIndex('0') ;
$i=0;$dum=false;$sum=0;
foreach ($objWorksheet->getRowIterator() as $row) 
{
  $cellIterator = $row->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(false); 
   if($dum) //to ignore first cell 
 {                                                  .
   foreach ($cellIterator as $cell) 
   {    if($i==2||$i==3||$i==4||$i==9)
        {
	          if($i==2)
		      {
			  $value[$i]=$cell->getValue();
			  $num1=$value[$i]; // get the starting date and time
			  }
		      if($i==3)
		      {
			  $value[$i]=$cell->getValue();
			  $num2=$value[$i]; // get the ending date and time
			  }
		      if($i==4)
		      { 
			  $value[$i]=$cell->getValue(); 
			  $asd=preg_split('#(?=\d)(?<=[a-z])#i',$value[$i] ); //convert strings as asd12321 to asd , 12321
			  $value[$i]=$asd[1]; // to read only digit
			  }
			  if($i==9)
		      {
			  $value[$i]=$cell->getValue(); // read a string
			  $value[$i+1]=$num2-$num1; //to take diff in minutes between the two 
			  }
		     
		}$i++;
		
    }$i=0;
	
	$con =  mysql_connect("localhost","root","");
    if (!$con)
    {$value[0]=$value[2];
     die('Could not connect: ' . mysql_error());
    }mysql_select_db("mobilink", $con);
    $sql="INSERT INTO my query ....";
    if (!mysql_query($sql,$con))
    {
    die('Error: ' . mysql_error());
    }
	$sum++;
     

     mysql_close($con);


 
   }
 $dum=true;
  
}
as you can see its a mess ! i tried using your option to retrive data from selected columbs but i cant ! i have to loop through 26 columbs at a time to fetch only 4 values. if you can only show me how to change this code into more efficent code . i would be so much thank full to you.

Jul 19, 2011 at 10:32 PM

i have php version 5.3.5 and i have 1.7.6 phpexcel and as you can guess i do full fill the requirments of phpexcel waiting for the response

Jul 20, 2011 at 1:40 PM

Boss,
Microsoft uses reference date & time as 1 Jan, 1900 , 00:00:00 AM.

Time is taken as a ratio of time with 24:00:00 like, 06:00:00 am is .25 beacuse 06:00:00/24:00:00 = .25, thus time will always be less than 1.

Date is taken as no. of days passed since Jan 1, 1990.

Ex- June 2, 2011 will be stored as 40696 as it comes after 40696 days from Jan 1, 1900.

 

Here is a code snippet which i used to separate the same:

 

First if condition is for time, else is for date:

if(PHPExcel_Shared_Date::isDateTime($cell))
{
//print($value."<br>");
	if($value != "null")
	{
		if($value<1000)
		{
		if($time_flag!=3)
		{
			$H=$value*24;
			$M=($H-floor($H))*60;
			$S=($M-floor($M))*60;
			$value=floor($H).":".floor($M).":".floor($S);
			$time_flag++;				
		}
	}
	else
	{
                $value*=24*60*60;	//CONVERT MICROSOFT EXCEL TIME FORMAT(I.E. TIME:24.00.00 <= 1.00) TO total seconds
		$time_flag=1;
	}
	}
	else
	{	
	// echo($value.'---');
	$value=floor($value);
	// echo($value.'<br>');
	$date = new DateTime('1899-12-30');	//REFERENCE DATE AS TAKEN BY MICROSOFT EXCEL TO REPRESENT DATE
	$date->add(new DateInterval('P'.$value.'D'));
	$value=$date->format('d-M-Y');
	}
}
Jul 20, 2011 at 1:45 PM

There exists Excel date/time conversion in PHPExcel:

$date = PHPExcel_Style_NumberFormat::toFormattedString($cell, "M/D/YYYY");

Jul 20, 2011 at 7:40 PM

thanks to daredev and 

ScottMSanders for all your help . i have solved this date issue now can you help with columns selection issue !