reading the exact time from excel using php

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mannielucero
Forum Newbie
Posts: 2
Joined: Wed Jul 23, 2014 12:39 pm

reading the exact time from excel using php

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: reading the exact time from excel using php

Post by requinix »

What's your code?
mannielucero
Forum Newbie
Posts: 2
Joined: Wed Jul 23, 2014 12:39 pm

Re: reading the exact time from excel using php

Post 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);
?>
sheila
Forum Commoner
Posts: 98
Joined: Mon Sep 05, 2005 9:52 pm
Location: Texas

Re: reading the exact time from excel using php

Post 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.
Post Reply