CODEWITHSUNDEEP
phpphpexcel
Maggi
Maggi

Reputation: 173

Copy Cut and Paste the set of rows from one place to other in a excel sheet using PHPExcel

I am trying to cut and copy a range of rows from my excel sheet (about 2346 rows) and paste it in down at the bottom of the sheet. I would like to cut and copy 6-80 rows,331-354 rows and then paste them in bottom,
I would like to know a good way to perform this operation using PHPexcel methods. Please give me suggestions.

Thank you

Upvotes: 6

Views: 11027

Answers (1)

mikeybeck
mikeybeck

Reputation: 582

Okay I had nearly the same problem (I just wanted to copy & paste values and styling, without cutting anything). The solution doesn't seem to quite be solved here, or anywhere that I could find, sooo this is what I did:

//Copy & paste values of range of cells
$cellValues = $objPHPExcel->getActiveSheet()->rangeToArray('C25:AN26');
$objPHPExcel->getActiveSheet()->fromArray($cellValues, null, 'C89');

(I got the same Call to undefined method PHPExcel_Worksheet::range‌​ToArray() error as Maggi above, using Mark's given code, but for some reason breaking it up into 2 lines works);

and then this silly bit of code to copy & paste the styling of the same range:

$i = 2;
while ($i < 55) {
    $j = num2char($i);
    $k = num2char($i+1);
    $objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle($j.'26:'.$k.'26'), $j.'90:'.$k.'90');
    $i++;
}

function num2char($num) {
    $numeric = $num % 26;
    $letter = chr(65 + $numeric);
    $num2 = intval($num / 26);
    if ($num2 > 0) {
        return num2char($num2 - 1) . $letter;
    } else {
        return $letter;
    }
}

(num2char function taken directly from here)

And then if you need to cut the original cells, use the removeRow() function.

Upvotes: 4

Related Questions