Page 1 of 1

reading the exact time from excel using php

Posted: Wed Jul 23, 2014 12:51 pm
by mannielucero
I made a php script that parses xls file. It works well but the only problem is that

when php read the cell it only reads 7/21/2014 0:43

also when i go to the xls and click the cell it shows the right format which is

7/21/2014 12:21:05 AM
why does xls hides it?

how can i convert that?

when i use $date_time = date('Y-m-d H:i:s', strtotime($date));

to format it took away the seconds it only returns 2014-07-21 00:43:00

please help me, is there a way i can convert it back to hh:mm:ss format (having the seconds).

This is what the output i need:

2014-07-21 00:43:05
Thank you.

Re: reading the exact time from excel using php

Posted: Wed Jul 23, 2014 3:38 pm
by requinix
What's your code?

Re: reading the exact time from excel using php

Posted: Wed Jul 23, 2014 10:37 pm
by mannielucero
this is my code, i just want to know if there is a way that php can get the right output because its reading in wrong format. thank you.

Code: Select all

<?php
	
	/* PROCESS EXCEL PARSER */
	/* PARSE IF UPLOAD SUCCESSFUL */
	
	set_include_path(get_include_path() . PATH_SEPARATOR . '../../../Classes/');

	include 'inc/phpexcel/PHPExcel/IOFactory.php';

	$inputFileName = 'file.xls';

	try{

		/**  Identify the type of $inputFileName  **/
		$inputFileType = PHPExcel_IOFactory::identify($inputFileName);

		/**  Create a new Reader of the type that has been identified  **/
		$objReader = PHPExcel_IOFactory::createReader($inputFileType);

		/**  Load $inputFileName to a PHPExcel Object  **/
		$objPHPExcel = $objReader->load($inputFileName);

	} catch(Exception $e) {
		
		die();
	}
			
	$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

	$columns = array(	'ID', 'Date/Time', 'File', 'Sender', 'Operator', 'Cost', 'Total', 'Message', 'Notification' );
	$indexes = array(	'ID' => '', 'Date/Time' => '', 'File' => '', 'Sender' => '', 'Operator' => '',
						'Cost' => '', 'Total' => '', 'Message' => '', 'Notification' => '');

	/* FIND AND SAVE INDICES */

	foreach( $sheetData[1] as $key => $val ){
		if( in_array( $val, $columns ) ){
			$indexes[$val] = $key;
		}
	}
				
	foreach($sheetData as $cell_key => $cell_val) {

		if( $ctr > 0 ) {
		
			if( in_array( $cell_val[$indexes['ID']], array('101-100', '101-101', '101-102') )  ) {
	
				$direction =  $cell_val[$indexes['ID']];
				$date =  $cell_val[$indexes['Date/Time']];
				$date_time = date('Y-m-d H:i:s', strtotime($date));
				$duration =  $cell_val[$indexes['File']];
				$dialed =  $cell_val[$indexes['Sender']];
				$caller =  $cell_val[$indexes['Operator']];
				$rate =  $cell_val[$indexes['Cost']];
				$charge =  $cell_val[$indexes['Total']];
				$custom =  $cell_val[$indexes['Message']];
				$result =  $cell_val[$indexes['Notification']];
				
			}
		}
		$ctr++;
	}

	unlink($inputFileName);
?>

Re: reading the exact time from excel using php

Posted: Sat Jul 26, 2014 9:15 pm
by sheila
I happen to be working with that library so I tested your code.

I created a simple xls file with two rows. I copied the test date into three columns of the second row and added seconds to them.
I applied different formats to each and copied the format code into the first column.
I then added var_dump($sheetData) to the script and when I ran the script I got this.

[text]
array(2) {
[1]=>
array(3) {
["A"]=>
string(20) "MM/DD/YY HH:MM AM/PM"
["B"]=>
string(19) "MM/DD/YYYY HH:MM:SS"
["C"]=>
string(1) "@"
}
[2]=>
array(3) {
["A"]=>
string(17) "07/21/14 12:21 AM"
["B"]=>
string(19) "07/21/2014 00:21:05"
["C"]=>
string(20) "07/21/14 12:21:05 AM"
}
}
[/text]

Parsing the xls file works just fine. Whether or not you get seconds from the data seems to depend on the cell's format code.
Also in the snippet of code you posted there is a problem with the $ctr variable be undefined.