src/Controller/ExcelController.php line 38

Open in your IDE?
  1. <?php
  3. namespace App\Controller;
  5. use DateTime;
  6. use App\Entity\Site;
  7. use App\Service\PetitsOutils;
  8. use App\Repository\SiteRepository;
  9. use App\Repository\MeteoRepository;
  10. use App\Repository\PrevisMeteoRepository;
  11. use PhpOffice\PhpSpreadsheet\Spreadsheet;
  12. use Symfony\Component\HttpFoundation\Request;
  13. use Symfony\Component\HttpFoundation\Response;
  14. use Symfony\Component\Routing\Annotation\Route;
  15. use Symfony\Bridge\Doctrine\Form\Type\EntityType;
  16. use Symfony\Component\Form\Extension\Core\Type\TextType;
  17. use Symfony\Component\Form\Extension\Core\Type\SubmitType;
  18. use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
  20. class ExcelController extends AbstractController
  21. {
  22.     private $petitsOutils;
  23.     private $siteRepository;
  24.     private $meteoRepository;
  25.     private $previsMeteoRepository;
  27.     public function __construct(PetitsOutils $petitsOutils, SiteRepository $siteRepository, MeteoRepository $meteoRepository, PrevisMeteoRepository $previsMeteoRepository )
  28.     {
  29.         $this->petitsOutils = $petitsOutils;
  30.         $this->siteRepository = $siteRepository;
  31.         $this->meteoRepository = $meteoRepository;
  32.         $this->previsMeteoRepository = $previsMeteoRepository;
  33.     }
  35.     /**
  36.      * @Route("/user/excel/dates",name="excel-dates")
  37.      */
  38.     public function choixDates(Request $request): Response
  39.     {
  40.         $route = 'excel';
  41.         $defaultData = [];
  42.         $form = $this->createFormBuilder($defaultData)
  43.         ->add('debut', TextType::class,array(
  44.             'label' =>"début",
  45.             'attr' => array(
  46.                 'class' => 'js-datepicker',
  47.             )))
  48.             ->add('fin', TextType::class,array(
  49.                 'label' =>"fin",
  50.                 'attr' => array(
  51.                     'class' => 'js-datepicker',
  52.                 )
  53.             ))
  54.             ->add('site',EntityType::class,array(
  55.                 'class'=>Site::class,
  57.             ))
  58.             ->add('ok', SubmitType::class)
  59.             ->getForm();
  61.         $form->handleRequest($request);
  63.         if($form->isSubmitted() && $form->isValid()){
  64.             $data = $form->getData();
  65.             $debut = $this->petitsOutils->frenchDateToEnglish($data['debut']);
  66.             
  67.             $fin = $this->petitsOutils->frenchDateToEnglish($data['fin']);
  68.             return $this->redirectToRoute($route,array(
  69.                 'debut'=>$debut,
  70.                 'fin'=>$fin,
  71.                 'site'=>$data['site']->getId()
  72.             ));
  73.         }
  74.         return $this->renderForm('graph/dates.html.twig', [
  75.             'form' => $form
  76.         ]);
  77.     }
  78.     /**
  79.      * @Route("/user/excel",name="excel")
  80.      */
  81.     public function excel(Request $request)
  82.     {
  83.         $debut = $request->query->get('debut');
  84.         $fin = $request->query->get('fin');
  85.         $siteId = $request->query->get('site');
  87.         $debut = new DateTime($debut);
  88.         $fin = new DateTime($fin);
  89.         $site = $this->siteRepository->find($siteId);
  91.         $pastdatas = $this->meteoRepository->findBetweenDatesForSite($debut,$fin,$site);
  92.         $previsdatas = $this->previsMeteoRepository->findBetweenDatesForSite($debut,$fin,$site);
  94.         $datas = array_merge($pastdatas,$previsdatas);
  95.         usort($datas, function($a, $b)
  96.         {
  97.             return strcmp($a->getDate()->format('Y-m-d H'), $b->getDate()->format('Y-m-d H'));
  98.         });
  99.        // dd($datas);
  100.         $spreadsheet = new Spreadsheet();
  102.         /* @var $sheet \PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet */
  103.         $sheet = $spreadsheet->getActiveSheet();
  104.         $sheet
  105.             ->setCellValue('A1', 'Station')
  106.             ->setCellValue('A2', 'Date')
  107.             ->setCellValue('B2', 'Pluie')
  108.             ->setCellValue('C2', 'Humidité')
  109.             ->setCellValue('D2', 'T°')
  110.             ;
  111.         $ligne = 3;
  113.         foreach ($datas as $d) {
  114.             //dump($ep);exit;
  115.             
  116.             $sheet->setCellValue('B1', $site->getName());
  118.             $date = $d->getDate();
  119.             $date = $date->format('Y-m-d H:i');
  120.             $excelDateValue = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($date);
  121.             
  122.             $sheet->setCellValue('A' . $ligne, $excelDateValue);
  123.             // Set the number format mask so that the excel timestamp  
  124.             // will be displayed as a human-readable date/time 
  125.             //voir /vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Style/NumberFormat.php
  126.             $spreadsheet->getActiveSheet()->getStyle('A' . $ligne)
  127.                 ->getNumberFormat()
  128.                 ->setFormatCode(
  129.                     \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME
  130.                 ); //'d/m/yy h:mm'
  133.             $sheet->setCellValue('B' . $ligne, $d->getRR());
  134.             $sheet->setCellValue('C' . $ligne, $d->getU());
  135.             $sheet->setCellValue('D' . $ligne, $d->getT());
  137.             $ligne++;
  138.         }
  139.           
  141.     // Create your Office 2007 Excel (XLSX Format)
  142.     //$writer = new Xlsx($spreadsheet);
  143.             
  144.     // redirect output to client browser
  145.     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  146.     
  147.         header('Content-Disposition: attachment;filename="horaires.xlsx"');
  148.     
  149.     header('Cache-Control: max-age=0');
  151.     $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
  152.     $writer->save('php://output');
  153.     exit;// Create your Office 2007 Excel (XLSX Format)
  154.         
  155.     }
  156. }