src/Controller/ExcelQController.php line 35

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\DailyRepository;
  10. use PhpOffice\PhpSpreadsheet\Spreadsheet;
  11. use Symfony\Component\HttpFoundation\Request;
  12. use Symfony\Component\HttpFoundation\Response;
  13. use Symfony\Component\Routing\Annotation\Route;
  14. use Symfony\Bridge\Doctrine\Form\Type\EntityType;
  15. use Symfony\Component\Form\Extension\Core\Type\TextType;
  16. use Symfony\Component\Form\Extension\Core\Type\SubmitType;
  17. use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
  19. class ExcelQController extends AbstractController
  20. {
  21.     private $petitsOutils;
  22.     private $siteRepository;
  23.     private $dailyRepository;
  25.     public function __construct(PetitsOutils $petitsOutils, SiteRepository $siteRepository, DailyRepository $dailyRepository )
  26.     {
  27.         $this->petitsOutils = $petitsOutils;
  28.         $this->siteRepository = $siteRepository;
  29.         $this->dailyRepository = $dailyRepository;
  30.     }
  32.     /**
  33.      * @Route("/user/excel/qdates",name="excel-q-dates")
  34.      */
  35.     public function choixDates(Request $request): Response
  36.     {
  37.         $route = 'excel-q';
  38.         $defaultData = [];
  39.         $form = $this->createFormBuilder($defaultData)
  40.         ->add('debut', TextType::class,array(
  41.             'label' =>"début",
  42.             'attr' => array(
  43.                 'class' => 'js-datepicker',
  44.             )))
  45.             ->add('fin', TextType::class,array(
  46.                 'label' =>"fin",
  47.                 'attr' => array(
  48.                     'class' => 'js-datepicker',
  49.                 )
  50.             ))
  51.             ->add('site',EntityType::class,array(
  52.                 'class'=>Site::class,
  54.             ))
  55.             ->add('ok', SubmitType::class)
  56.             ->getForm();
  58.         $form->handleRequest($request);
  60.         if($form->isSubmitted() && $form->isValid()){
  61.             $data = $form->getData();
  62.             $debut = $this->petitsOutils->frenchDateToEnglish($data['debut']);
  63.             
  64.             $fin = $this->petitsOutils->frenchDateToEnglish($data['fin']);
  65.             return $this->redirectToRoute($route,array(
  66.                 'debut'=>$debut,
  67.                 'fin'=>$fin,
  68.                 'site'=>$data['site']->getId()
  69.             ));
  70.         }
  71.         return $this->renderForm('graph/dates.html.twig', [
  72.             'form' => $form
  73.         ]);
  74.     }
  75.     /**
  76.      * @Route("/user/excelq",name="excel-q")
  77.      */
  78.     public function excelq(Request $request)
  79.     {
  80.         $debut = $request->query->get('debut');
  81.         $fin = $request->query->get('fin');
  82.         $siteId = $request->query->get('site');
  84.         $debut = new DateTime($debut);
  85.         $fin = new DateTime($fin);
  86.         $site = $this->siteRepository->find($siteId);
  88.         $datas = $this->dailyRepository->findBetweenDatesForSite($debut,$fin,$site);
  89.         //dd($datas);
  90.         $spreadsheet = new Spreadsheet();
  92.         /* @var $sheet \PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet */
  93.         $sheet = $spreadsheet->getActiveSheet();
  94.         $sheet
  95.             ->setCellValue('A1', 'Station')
  96.             ->setCellValue('A2', 'Date')
  97.             ->setCellValue('B2', 'Pluie')
  98.             ->setCellValue('C2', 'T°')
  99.             ->setCellValue('D2', 'ETP')
  100.             ;
  101.         $ligne = 3;
  103.         foreach ($datas as $d) {
  104.             //dump($ep);exit;
  105.             
  106.             $sheet->setCellValue('B1', $site->getName());
  108.             $date = $d->getDate();
  109.             $date = $date->format('Y-m-d');
  110.             $excelDateValue = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($date);
  111.             
  112.             $sheet->setCellValue('A' . $ligne, $excelDateValue);
  113.             // Set the number format mask so that the excel timestamp  
  114.             // will be displayed as a human-readable date/time 
  115.             //voir /vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Style/NumberFormat.php
  116.             $spreadsheet->getActiveSheet()->getStyle('A' . $ligne)
  117.                 ->getNumberFormat()
  118.                 ->setFormatCode(
  119.                     \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DDMMYYYY
  120.                 ); 
  123.             $sheet->setCellValue('B' . $ligne, $d->getPrecipsum());
  124.             $sheet->setCellValue('C' . $ligne, $d->getT());
  125.             $sheet->setCellValue('D' . $ligne, $d->getETP());
  127.             $ligne++;
  128.         }
  129.           
  131.     // Create your Office 2007 Excel (XLSX Format)
  132.     //$writer = new Xlsx($spreadsheet);
  133.             
  134.     // redirect output to client browser
  135.     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  136.     
  137.         header('Content-Disposition: attachment;filename="quotidiennes.xlsx"');
  138.     
  139.     header('Cache-Control: max-age=0');
  141.     $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
  142.     $writer->save('php://output');
  143.     exit;// Create your Office 2007 Excel (XLSX Format)
  144.         
  145.     }
  146. }