For loop with zero in front of a number (calendar script)

Topics: User Forum
Apr 13, 2009 at 10:32 PM
Edited Apr 13, 2009 at 10:42 PM
Hello,

I am pretty new at working with PHPExcel but already love it.
Haven't had any problems with it since the start. till now..

I'm trying to create a calendar display with phpexcel..
but for some reason it won't allow me to put a 0 in front of all numbers below 10..
this because i need to match the time formats with data in my database

for example:

a sceduled meeting at 9:45.
this is written down like 0945 in my database..
The loop though, gives output like this: 945, this way it doesn't find a match at all..

it does the same with round hours. like 11:00, it displays it like 110..

below the loop i am talking about..

for($i='8'; $i<='23'; $i++)
{
if($i < 10 && $i > 8)
{
$i = "0".$i;
}
for($j='0'; $j<='45'; $j=$j+'15')
{
if($j < 10 )
{
$j = "0".$j;
}

mysql_data_seek($vertrekSql, 0); 
while($r = mysql_fetch_array($vertrekSql)) 
{
# Vertrek
if($r['tijd'] == $i.$j) // (matching time  with database data)
{
//adding it to the sheet
  }
$i.$j // should display like 0945 but displays like 945  || should display like 1000 but displays like 100
}
}
}

Thanks in advance for all the replies.

Martijn
Developer
Apr 13, 2009 at 11:56 PM
Edited Apr 14, 2009 at 12:27 AM
For now, do like in this example:

$time = '0945';
$hh = substr($time, 0, 2);
$mm = substr($time, 2, 2);
$days = $hh / 24 + $mm / 1440;

$objWorksheet->getCell('A1')->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC);
$objWorksheet->getStyle('A1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);

I will ask maartenba if we can have a simple way of doing this using AdvancedValueBinder.

Edit:
Feature request posted:
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=9747

Apr 14, 2009 at 3:15 PM
so..

$i.$j is the same as $time?
or
$r['tijd'] is the same $time

(tijd is the dutch word for time)
Developer
Apr 15, 2009 at 6:39 AM
I was under the impression you needed to write an Excel file using PHPExcel, but I may have misunderstood. Can you explain whether you are reading or writing an Excel file?

In the loop, I don't see any statements related to PHPExcel?

Apr 17, 2009 at 3:15 PM
Edited Apr 17, 2009 at 3:20 PM
Hello,
Sorry I'll try to be a bit more clear about the case.

to answer your question. i want to create an excel file with phpexcel :)

I'm making a for loop with hours and quarters. the hours start in this case at 8 and end at 23.
The quarters in my loop are starting with 00 and end with 45.
the quarters loop is placed incide the hours loop. this way i can make a match with data in the database. So that if there is a appointment at $i.$j (for example 1315) it will return it.
the part below 
"
if($i < 10 && $i > 8)
{
$i = "0".$i;
}
should place a 0 in front of the hours that don't contain 2 characters. (8 and 9 o'clock.)
but, in this case... it doesn't do it. (i beleive it has to do with the phpexcel class since i am using this on several other places where it does work)

so. $i stand for hours and $j stands for quarters.

hopefully you do understand the question and problem :)

$rij = "7";
for($i='8'; $i<='23'; $i++)
{
if($i < 10 && $i > 8)
{
$i = "0".$i;
}
for($j='0'; $j<='45'; $j=$j+'15')
{
$objPHPExcel->getActiveSheet()->setCellValue('A'.$rij, $i.$j); // add the time to the excel sheet. should display something like 0945 or 1000. current situation is 945 and 100.
mysql_data_seek($vertrekSql, 0);
while($r = mysql_fetch_array($vertrekSql))
{
# Vertrek
if($r['tijd'] == $i.$j)
{
$naam = ($r['aanhef'].($r['tussenvoegsel'] ? " ".$r['tussenvoegsel']."" : '')." ".$r['achternaam']); // stands for eg. "mr. van helsing" ;) 
$objPHPExcel->getActiveSheet()->setCellValue('C'.$rij, $naam); // stands for the name eg. "max"
$rij++;
}
}
$rij++;

}
}
Developer
Apr 18, 2009 at 3:14 AM
I show you two ways. The first method is more correct than the second because times are inserted as numeric values while the second method just uses plain text. Notice the difference by looking at the formular bar in the screen shots.

1. Time inserted the Excel way ("Correct" method)

$rij = 1;
for ($i = 8; $i <= 23; $i++) {
    for($j = 0; $j <= 45; $j += 15) {
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $rij, $i / 24 + $j / 1440);
        $objPHPExcel->getActiveSheet()->getStyle('A' . $rij)->getNumberFormat()->setFormatCode('hhmm');
        $rij++;
    }
}


2. Time inserted as text

$rij = 1;
for ($i = 8; $i <= 23; $i++) {
    for($j = 0; $j <= 45; $j += 15) {
        $objPHPExcel->getActiveSheet()->setCellValueExplicit(
            'A' . $rij,
            sprintf('%02s', $i) . sprintf('%02s', $j),
            PHPExcel_Cell_DataType::TYPE_STRING
        );
        $rij++;
    }
}


Apr 20, 2009 at 7:17 PM
thank you, that did the trick, for so far :)
please leave this thread open for more questions..