Fill in a matrice in PHPEXCEL with data from database

Vote count:

0

I created in PHP a calendar to show to my boss my holidays.

The calendar works great.

I have to export the data of the calendar into an Excel sheet in this format :

I would like to know how can I put a cross in this table at March 09 if I take a holiday on march 9.

I tried this but my way seems to be not the best way :

include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);

$ii = 1;
for($col = 'A'; $col !== 'AF'; $col++)
{
     $objPHPExcel->getActiveSheet()->SetCellValue($col.'1', $ii);
     $ii++;
 }

$annee_en_cours = date('Y');
$sql = "SELECT id, nom, prenom FROM utilisateurs";
$fiches = $bdd->prepare($sql);
$users = $fiches->execute();
$user_number = 0;

$res = $fiches->fetchAll(PDO::FETCH_OBJ);
$ligne = 0;
foreach($res as $user)
{
  for($month = 1; $month <=12; $month++)
  {
     $ligne++;
     $num_jours = cal_days_in_month(CAL_GREGORIAN, $month, $annee_en_cours);

     $col = 'A';

     for($i=1; $i<= $num_jours; $i++)
     {
        $sql = "SELECT jour, commentaire
                FROM reservations
                WHERE id_user = ".$user->id;
        if($month < 10)
        {
            $sql.= " AND SUBSTRING(jour,10) = '".$annee_en_cours."_0".$month;
            //$sql.= " AND SUBSTRING(jour,9,11) = 0".$i;
        }
        else
        {
            $sql.= " AND SUBSTRING(jour,1,10) = '".$annee_en_cours."_".$month;
            //$sql.= " AND SUBSTRING(jour,9,11) = ".$i;
        }

        if($i < 10)
        {
            $sql.= "_0".$i."'";       
        }
        else
        {
           $sql.= "_".$i."'";
        }
        $sql.= " ORDER BY jour";
        $req = $bdd->prepare($sql);
        $execution = $req->execute();
        $res = $req->fetchAll(PDO::FETCH_OBJ);

        if (count($res) > 0)
        {



          $objPHPExcel->getActiveSheet()->SetCellValue($col.$ligne, $res[0]->jour);
        }
        echo $col.$ligne.'</br />';
        $col++;
     }

  }
  $ligne = 1;
  $user_number++;
  $objPHPExcel->createSheet($user_number);
  $objPHPExcel->setActiveSheetIndex($user_number);
  $objPHPExcel->getActiveSheet()->setTitle($user->nom);
  $ii = 1;
  for($col = 'A'; $col !== 'AF'; $col++)
  {
       $objPHPExcel->getActiveSheet()->SetCellValue($col.'1', $ii);
       $ii++;
   }
}

I begin in the first cell and I browse all the cell, if I find a holiday, I put a cross.

This is not the best way, have you a idea plese ?

asked 1 min ago

user3553866

12

Fill in a matrice in PHPEXCEL with data from database