Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PHPExcel - copy range of columns

Tags:

php

phpexcel

I have Excel file where I need to expend space by duplicating excat column range.

enter image description here

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..

like image 574
John Martin Avatar asked Jan 18 '19 14:01

John Martin


1 Answers

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);
like image 66
Nigel Ren Avatar answered Nov 03 '22 07:11

Nigel Ren