Single quote + =SUM()

Topics: Developer Forum, Project Management Forum, User Forum
Feb 9, 2009 at 1:59 PM
Hello,

sorry but I have a little trouble with English.

I have a concern I would like to move from MySQL timestamp format [H]: mm: ss, but I have a concern with a single quote.
example, $ sheet-> setCellValueByColumnAndRow (1, $ cell, $ hours [$ i] -> eft) will give result as'32: 15:00 in this case the single quote I can not make a = SUM ().

Can you help me remove the single quote?

I already try setCellValueByColumnAndRowExplicit (1, $ cell, $ hours [$ i] -> eft, PHPExcel_Cell_DataType: TYPE_NUMERIC), but without success.
Thank you for your help.

MaitrePylos
Developer
Feb 9, 2009 at 3:16 PM
In Excel, times are stored internally as numbers. Example:

14:59:10 = 0.624421296296296

The rule is that 1 day = 24 hours corresponds to 1.

$sheet->getCell('A1')->setValue(0.624421296296296);
$sheet->getStyle('A1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);

It may be a bit cumbersome to convert '14:59:10' to 0.624421296296296. But you can do it like this:

In MySQL, we have

SELECT TIME_TO_SEC( '14:59:10' )
-> 53950

Then, convert from seconds to Excel time like this:

53950 / (24 * 3600) = 0.624421296296296

Your MySQL query may look something like this:

$sql = "SELECT TIME_TO_SEC(`time`) as `sec` FROM `mytable` WHERE `id` = '5'";

Strangely, I think you are the first one asking about how to insert times. In the future there may be a more simple way to do this. Until then, the above method should do the trick.

Coordinator
Feb 9, 2009 at 7:44 PM
There is also the Excel TIME() or TIMEVALUE() functions

$sheet->getCell('A1')->setValue("=TIME(14,59,10)");
$sheet->getStyle('A1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
OR
$sheet->getCell('A1')->setValue("=TIMEVALUE('14:59:10')");
$sheet->getStyle('A1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
Feb 10, 2009 at 8:18 AM
Hello,

Thank you for your responses.

Koyama:

In MySQL, if I do this, I have errors:

SUM (TIME_TO_SEC (heures_nbr) / (24 * 3600))

Result: 5:30:03 or 634:00:03 This is not the seconds are too much against this by

$sheet->setCellValueByColumnAndRow (1, $cell, $hours[$i]->eft / (24 * 3600));

It's OK:)

markbaker:

$sheet->setCellValueByColumnAndRow (1, $cell, "TIMEVALUE = (". $hours[$i]->eft .")");

Gives this:

Fatal error: Uncaught exception 'Exception' with message 'Incorrect number of arguments in function TIMEVALUE ()' in / var/www/pontaury/library/Excel/PHPExcel/Writer/Excel5/Parser.php: 1450 Stack trace: # 0


For NumberFormat I do this:

$ sheet-> duplicateStyleArray (array (
'NumberFormat' => array (
'code'=>'[ H]: MM: SS')), 'B25: F47');

A big thank you to both of you for this help.

The issue is resolved for me.

MaitrePylos
Coordinator
Feb 10, 2009 at 8:54 AM
The parameter passed to the TIMEVALUE() function should be a quoted string, so try wrapping your $hours[$i]->eft in quotes

$sheet->setCellValueByColumnAndRow (1, $cell, "TIMEVALUE = ('". $hours[$i]->eft ."')");
Feb 10, 2009 at 1:21 PM
Flawless:)

to be careful slight error in the code.

$ sheet-> setCellValueByColumnAndRow (1, $ cell, "=TIMEVALUE( '". $ hours [$ i] -> eft ."')");

Both ways work and gets the desired result.

I have just a small concern, using one of your codes, I can not send the file to $writer->save ( 'php: / / output');

I am compelled to record, but this is not very serious.

Again thank you for help.

MaitrePylos