phpsymfonyrector

How to move from phpexcel to phpspreadsheet


I used to use phpexcel, but I want to move to phpspreadsheet.
I tried to hit the command based on the following site, but it doesn't work.
Did I make a mistake?
I'm also using a container named phpexcel in my code, is this still available?

https://phpspreadsheet.readthedocs.io/en/latest/topics/migration-from-PHPExcel/

Command

$composer require phpoffice/phpspreadsheet
$composer require rector/rector --dev
$vendor/bin/rector process src --set phpexcel-to-phpspreadsheet
  bash: vendor/bin/rector: No such file or directory
$vendor/rector/rector/bin/rector process src --set phpexcel-to-phpspreadsheet
 [ERROR] Set "phpexcel-to-phpspreadsheet" was not found.

//Add command
$composer require rector/rector "0.7.*"
$composer require rector/rector-prefixed --dev
$vendor/rector/rector/bin/rector init

php using phpexcel in Controller

    /**
     * @Route("/summary/{_format}", defaults={"_format"="html"}, requirements={"_format"="html|xls"})
     * @Method("GET")
     *
     */
    public function summaryAction(Request $request, $_format)
    {
        $perPage = 100;
        // Create a search form
        $searchForm = $this->createForm(ShopMetricsSearchType::class, null, array(
            'action' => $this->generateUrl('app_hq_analytics_summary'),
        ));

        // Get search criteria
        $params = $this->getSearchParameter($searchForm, $request);

        $pagination = null;
        $no = 0;
        if ($request->getRequestFormat() == 'html') {
            // At the time of html output
            // Create page nation
            $count = 0;
            if($params['brand']){
                $count = $this->get('admin.shopService')->countShopBySearchParams(
                    array('shopDispFlg' => 1, 'brand' => $params['brand'])
                );
            }else{
                $count = $this->get('admin.brandService')->countBrandBySearchParams(
                    array('brandDispFlg' => 1)
                );
            }
            $page = $request->query->getInt('page', 1);
            $num = $request->query->getInt('num',$perPage);
            $pagination = new Pagination($count, $page, $num, 10);
            // Calculation of No.
            $no = ($perPage*$page) - $perPage;
        } elseif ($request->getRequestFormat() == 'xls') {
            // xls at the time of output
            $phpExcelObject = $this->get('admin.analyticsService')->getSummaryExcel(
                $params
                ,$this->get('phpexcel')->createPHPExcelObject()
                ,$this->get('kernel')->getRootDir()."/../src/AppBundle/Resources/views/Hq/Analytics/summary.xls"
            );
            // create the writer
            $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel5');
            // create the response
            $response = $this->get('phpexcel')->createStreamedResponse($writer);
            // adding headers
            $dispositionHeader = $response->headers->makeDisposition(
                ResponseHeaderBag::DISPOSITION_ATTACHMENT,
                'summary.xls'
            );
            $response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
            $response->headers->set('Pragma', 'public');
            $response->headers->set('Cache-Control', 'maxage=1');
            $response->headers->set('Content-Disposition', $dispositionHeader);
            return $response;
        }

        // Get access status by shop
        $summaryMetrics = $this->get('admin.analyticsService')->getSummaryMetrics(
            $params,
            $pagination ? $pagination->getItemsPerPage() : null,
            $pagination ? $pagination->getSelectedPageOffset() : null
        );

        // Screen display
        return $this->render('@AppBundle/Hq/Analytics/summary.' . $_format . '.twig', [
            'searchForm' => $searchForm->createView(),
            'summaryMetrics' => $summaryMetrics,
            'pagination' => $pagination,
            'no' => $no
        ]);

    }

Service

    public function getSummaryExcel(array $params,$phpExcelObject,$file)
    {
        $summaryMetrics = $this->getSummaryMetrics(
            $params
        );
        $phpExcelObject = \PHPExcel_IOFactory::load($file);
        $phpExcelObject->setActiveSheetIndex(0);
        $colInitPos = 0;
        $startRow = 4;
        $col = $colInitPos;
        $rowsCount = count($summaryMetrics);
        $colsCount = 24;
        $totalRow=$rowsCount+$startRow;
        // First, prepare as many rows as you need
        $sheet = $phpExcelObject->getActiveSheet();
        $sheet->insertNewRowBefore($startRow+1, $rowsCount -1 );
        $formulaDef = array(
            );
        for($col=0;$col<$colsCount;$col++){
            for($row=$startRow;$row<$totalRow;$row++){
                if(isset($formulaDef[$col])){
                    $value = str_replace('[ROW]', $row, $formulaDef[$col]);
                    $sheet->setCellValueByColumnAndRow($col,$row, $value);
                }
            }
        }
        $row = $startRow;
        foreach($summaryMetrics as $metrics){
            $sheet
            ->setCellValueByColumnAndRow(0, $row, $metrics['rank']);
            $row++;
            $col = $colInitPos;
        }
        $sheet->setCellValueByColumnAndRow(0, 2, $term);
        $sheet->setTitle('Aggregate report '.str_replace('/','',$term));
        $phpExcelObject->setActiveSheetIndex(0);
        return $phpExcelObject;
    }

Rector

 #!/usr/bin/env php
  <?php
  
  declare(strict_types=1);
  
  use Psr\Container\ContainerInterface;
  use Rector\Console\Application;
  use Rector\Console\Style\SymfonyStyleFactory;
  use Symplify\PackageBuilder\Reflection\PrivatesCaller;
  
  @ini_set('memory_limit', '-1'); // @ intentionally: continue anyway
  
  // Performance boost
  gc_disable();
  
  // Require Composer autoload.php
  require_once __DIR__ . '/bootstrap.php';
  
  try {
      /** @var ContainerInterface $container */
      $container = require_once __DIR__ . '/container.php';
  } catch (Throwable $throwable) {
     $symfonyStyle = (new SymfonyStyleFactory(new PrivatesCaller()))->create();
      $symfonyStyle->error($throwable->getMessage());
      exit(1);
  }
  
  $application = $container-rector/rector-prefixed>get(Application::class);
  exit($application->run());

Version
symfony v4.4.19
php v7.3.24
phpoffice/phpspreadsheet 1.17.1
rector/rector v0.7.2
rector/rector-prefixed v0.9.31


Solution

  • I implemented the following code and the download function of xls worked.

    https://github.com/yectep/phpspreadsheet-bundle

    composer require yectep/phpspreadsheet-bundle
    

    Model

             } elseif ($request->getRequestFormat() == 'xls') {
    
                 $phpSpreadSheetObject = $this->get('admin.analyticsService')->getSummaryExcel(
                     $params
                     ,$this->get('phpoffice.spreadsheet')->createSpreadsheet()
                     ,$this->get('kernel')->getRootDir()."/../src/AppBundle/Resources/views/Hq/Analytics/summary.xls"
                 );
     
                 // create the response
                 $response = $this->get('phpoffice.spreadsheet')->createStreamedResponse($phpSpreadSheetObject, 'Xls');
                 // adding headers
                 $dispositionHeader = $response->headers->makeDisposition(
                     ResponseHeaderBag::DISPOSITION_ATTACHMENT,
                     'summary.xls'
                 );
    
                 return $response;
     
    

    Service

    use PhpOffice\PhpSpreadsheet\IOFactory;
    
         public function getSummaryExcel(array $params,$phpSpreadSheetObject,$file)
         {
             $phpSpreadSheetObject = IOFactory::load($file);
             $phpSpreadSheetObject->setActiveSheetIndex(0);
             $sheet = $phpSpreadSheetObject->getActiveSheet();
    
             $phpSpreadSheetObject->setActiveSheetIndex(0);
             return $phpSpreadSheetObject;
         }