How to move from phpexcel to phpspreadsheet

Question

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

@qdequippe Thank you for your comment. Please check it because it was added. — scottie320, Mar 18 '21 at 10:23
Rector is an interesting tool but there is no way it can automatically update your code from phpexcel to phpspreadsheet. The two packages are fundamentally different. You could always try contacting rector's author via github. He is an interesting fellow. Might even add the rector tag to your question. He monitors these sorts of questions. But you pretty much need to learn how to use phpspreadsheet and refactor your code accordingly. — Cerad, Mar 18 '21 at 13:47
Cool, did you try to update your config according to this article https://getrector.org/blog/2020/04/16/how-to-migrate-from-phpexcel-to-phpspreadsheet-with-rector-in-30-minutes — qdequippe, Mar 18 '21 at 14:38
@Cerad Thanks for your comment. Just in case, I added the tag of reector. After all it seems that you need to change the code yourself. Is there any reference material? I couldn't find it myself. — scottie320, Mar 19 '21 at 02:06
@qdequippe I overlooked it, so I tried running it, but init didn't work and I'm throwing an error. Do you have any idea? I was able to confirm that the package was included by ```composer show --installed```. — scottie320, Mar 19 '21 at 02:17
Did you also read the [article](https://getrector.org/blog/2020/04/16/how-to-migrate-from-phpexcel-to-phpspreadsheet-with-rector-in-30-minutes) which is linked in this official docs? — adampweb, Mar 28 '21 at 15:35
FYI: text/vnd.ms-excel does not exist. use application/vnd.ms-excel — Pascal, Mar 17 '23 at 08:11

score 2 · Answer 1 · answered Mar 19 '21 at 16:59

2

Option 1

Add to composer.json

{
    "scripts" : {
        "rector" : "rector process src --set phpexcel-to-phpspreadsheet"
    }
}

Adn run composer run rector

Option 2

Run in root project directory

./vendor/bin/rector process src --set phpexcel-to-phpspreadsheet
↑

answered Mar 19 '21 at 16:59

Isaac Limón

1,940
18
15

1

Thank you for your answer. I tried it, but I got the error ```[ERROR] Set" phpexcel-to-phpspreadsheet "was not found.``` in ```composer run rector```. Is this a rector version issue? – scottie320 Mar 21 '21 at 02:29

score 0 · Accepted Answer · answered Mar 29 '21 at 01:47

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;
     }

How to move from phpexcel to phpspreadsheet

2 Answers2