Date imported as input mask, not as value

Topics: Developer Forum, Project Management Forum, User Forum
Oct 10, 2012 at 3:14 PM
Edited Oct 10, 2012 at 3:14 PM

I have an excel, with an adapted inputmask for a date: dd.mm.yyyy

When I'm trying to import it, or even just echo it with php, it doesn't show the value, but the input mask! In my database, there is a column with all values dd.mm.yyy, and not the date that is put in Excel...

Help?

This is what I allready tried: 

1) 

$data->sheets[0]['cells'][$x][$j]->setValueExplicit('25', PHPExcel_Cell_DataType::TYPE_NUMERIC);

2) 

$objPHPExcel->getActiveSheet()->getCell($x.$j)->setValueExplicit('25', PHPExcel_Cell_DataType::TYPE_NUMERIC);

3)

date('Y-m-d',strtotime($data->sheets[0]['cells'][$x][$j]))
Coordinator
Oct 11, 2012 at 9:01 AM

I'm trying to understand what you want here.... what do you expect when you store 25 in a cell and format it as a date?

 

EXcel stores "dates" as a float serial number (number of days since 1st Jan 1900, or 1st Jan 1904 depending on Windows or Mac calendar) so when you store a date in a cell you need to convert it to that float value. There's a couple of helper functions in the PHPExcel_Shared_Date class that can be used for this conversion: PHPToExcel() and FormattedPHPToExcel() to convert PHP/unix timestamps, DateTime objects or strings to an excel timestamp, and ExcelToPHP() and ExcelToPHPObject() for the reverse.

You then need to set the cell's number format mask to an appropriate mask for a date, such as the masks defined in the PHPExcel_Style_NumberFormat class, or your adapted mask of dd.mm.yyyy.

Oct 11, 2012 at 9:25 AM
Edited Oct 11, 2012 at 9:26 AM

The 25 was just to test how that function worked. But it just inputted dd.mm.yyyy.

The problem is, that in Excel, the date is shown, but in PHP, dd.mm.yyyy is shown. I want to add an excel file to a mysql-table, with the correct dates. I think you're seeing it the opposite way.

Now, in my MYSQL, there is dd.mm.yyyy (I formatted the mysql-table to text, just as a test, so I can exactly see what is added.)

If you want me to add (a part of) the excel file, feel free to ask!

Coordinator
Oct 11, 2012 at 9:58 AM
Edited Oct 11, 2012 at 10:02 AM

Please don't use textual representation for dates, either in PHPExcel, in PHP, or in a database... if you're working with proper Unix/PHP datetimestamps or with DateTime objects, then you have the tools to format however you wish.

I'm having difficulty understanding whether  you're trying to read a date from an Excel file and store it in a database; or the reverse.

 

Reading a date from Excel:

$excelDateValue = $objPHPExcel->getActiveSheet()->getCell('A1')->getValue();
$phpDateValue = PHPExcel_Shared_Date::ExcelToPHP($excelDateValue);
echo date('Y-m-d', $phpDateValue);

or

$excelDateValue = $objPHPExcel->getActiveSheet()->getCell('A1')->getValue();
$phpDateTimeObject = PHPExcel_Shared_Date::ExcelToPHPObject($excelDateValue);
echo phpDateTimeObject->format('Y-m-d');

Writing a date to Excel

$phpDateValue = gmmktime(0,0,0,10,11,2012);
$excelDateValue = PHPExcel_Shared_Date::PHPToExcel($phpDateValue);
$objPHPExcel->getActiveSheet()->setCellValue('A1', $excelDateValue);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);

or

 

$phpDateTimeObject = new DateTime('2012-11-10');
$excelDateValue = PHPExcel_Shared_Date::PHPToExcel($phpDateTimeObject);
$objPHPExcel->getActiveSheet()->setCellValue('A1', $excelDateValue);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);

 

 

 

Oct 17, 2012 at 12:47 PM
Edited Oct 17, 2012 at 12:48 PM

I'm trying to read data from Excel, and store it in a database.

It doesn't work what you proposed. He hangs on the one line of code:

$phpDateTimeObject = PHPExcel_Shared_Date::ExcelToPHPObject($excelDateValue);

Or 

$phpDateValue = PHPExcel_Shared_Date::ExcelToPHP($excelDateValue);

What can I do now? This is my code:

include('excel_reader2.php');
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('UTF-8');
$data->read($file);

$sql= "INSERT INTO table (xx,xxx,xxx,...) VALUES ";

for ($x = 2; $x <= count($data->sheets[0]["cells"]); $x++) 
{
	$sql .= "(";
	for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++)
	{
		if($j==4 || $j==16 || $j==17 || $j==21 || $j==22) 
		{//cell with date
			$excelDateValue = $data->sheets[0]['cells'][$x][$j];
			$phpDateTimeObject = PHPExcel_Shared_Date::ExcelToPHP($excelDateValue);
			$sql .= date('Y-m-d', $phpDateTimeObject);
		}
		else
		{
			$sql .= "'" . mysql_escape_string($data->sheets[0]['cells'][$x][$j]) . "',";
		}
	}
	$sql = substr($sql, 0, -1) . "),";
}	
$sql =  substr($sql, 0, -1);

 

When I just try to echo the query ($sql), at the end of this code, nothing happens...

Coordinator
Oct 17, 2012 at 1:44 PM

You realise that Spreadsheet_Excel_Reader !== PHPExcel ?

Oct 17, 2012 at 1:52 PM

Ow jeah, sorry... Didn't notice that one.

So I'm on the wrong forum all this time?