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