I have Excel file where I need to expend space by duplicating excat column range.
I upload a part of that file that can be seen in a picture. I need to copy row from A16 to H16 and set it right below (A17 to H17) so it would automatically move other columns below.
This is one of examples I hvae tried. None of them work.
$appPath = $this->container->getParameter('kernel.root_dir');
$webPath = realpath($appPath . '/../web/uploads/statements/the-file.xlsx');
$phpExcelObject = $this->get('phpexcel')->createPHPExcelObject($webPath);
$phpExcelObject->getActiveSheet()->insertNewRowBefore(16,16);
$phpExcelObject->getActiveSheet()->duplicateStyle($phpExcelObject->getActiveSheet()->getStyle('A16'), 'A16:H16');
$writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel2007');
$response = $this->get('phpexcel')->createStreamedResponse($writer);
$dispositionHeader = $response->headers->makeDisposition(
ResponseHeaderBag::DISPOSITION_ATTACHMENT,
'the-file.xlsx'
);
$response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
$response->headers->set('Cache-Control', 'maxage=1');
$response->headers->set('Content-Disposition', $dispositionHeader);
return $response;
Api call works, to be exact, it donwloads that file with updated edits but the part with copying rows doesn't work..
Looking at the parameters and the example on PhpExcel - How insert the same row after row N? I would assume your parameters are incorrect, I think insertNewRowBefore()
has parameters insert point and number of rows, so this should be 16 and 1 in your case. Also the duplicateStyle()
call should be with the rows you want to update and not the range of cells - I've put 'A15', but this may need to be tweaked.
$phpExcelObject->getActiveSheet()->insertNewRowBefore(16,1);
$phpExcelObject->getActiveSheet()->duplicateStyle($phpExcelObject->getActiveSheet()->getStyle('A16'), 'A15');
Update:
If you want to copy the data as well, I've rewritten it to cut down the repetitive bits, but start with the row you want to copy. This then inserts a row before this and then creates a range which covers all of the row using getHighestColumn()
to get the end of the row. It then copies this range for you.
$copyFrom= "4";
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->insertNewRowBefore($copyFrom,1);
$activeSheet->duplicateStyle($activeSheet->getStyle('A'.($copyFrom+1)), 'A'.$copyFrom);
$lastColumn = $objPHPExcel->getActiveSheet()->getHighestColumn();
$rangeFrom = 'A'.($copyFrom+1).':'.$lastColumn.($copyFrom+1);
$activeSheet->fromArray($activeSheet->rangeToArray($rangeFrom), null, 'A'.$copyFrom);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With