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

10,058

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.

Share:
10,058
Maggi
Author by

Maggi

Updated on June 16, 2022

Comments

  • Maggi
    Maggi over 1 year

    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

    • Mark Baker
      Mark Baker almost 9 years
      Just the data? Or do you need any styling as well?
    • Maggi
      Maggi almost 9 years
      if style is not possible to get it's fine i can set it later in the code but atleast if can the data, that will helps a lot
    • Mark Baker
      Mark Baker almost 9 years
      A combination of rangeToArray() and fromArray() should allow you to easily copy/paste the data you need; then removeRow() to remove the old rows that you copied from
    • Maggi
      Maggi almost 9 years
      i have 5 columns as well so what agrument will work in it ? $objPHPExcel->getActiveSheet()->rangeToArray('A6:E6'); is this right ??do i need to apply a loop to get it from rows 6-80 or is there ant method to do it in other way ?
    • Maggi
      Maggi almost 9 years
      $excel_out [] = $objPHPExcel->getActiveSheet()->rangeToArray('A6:D6'); $objPHPExcel->getActiveSheet()->fromArray($excel_out, "" , 'A2346'); $objPHPExcel->getActiveSheet()->removeRow(6);
    • Mark Baker
      Mark Baker almost 9 years
      $objPHPExcel->getActiveSheet()->rangeToArray('A6:E80');
    • Mark Baker
      Mark Baker almost 9 years
      But you should be able to simplify the whole copy/paste block as $objPHPExcel->getActiveSheet()->fromArray($objPHPExcel->getA‌​ctiveSheet()->rangeT‌​oArray('A6:E80'), null, 'A2346');
    • Mark Baker
      Mark Baker almost 9 years
      And removeRow() accepts a second argument specifying the number of rows to remove: $objPHPExcel->getActiveSheet()->removeRow(6, 80-6+1);
    • Maggi
      Maggi almost 9 years
      thank you for such a perfect answers, but for some reason it gives me this error : Call to undefined method PHPExcel_Worksheet::range‌​ToArray() excel_ap_export.php on line 101
    • Maggi
      Maggi almost 9 years
      i used this in my file : use \PHPExcel_Worksheet; but still it's giving that fatal error i stated above. what could be the possible mistake i am doing here i am not sure
    • Mark Baker
      Mark Baker almost 9 years
      I'm not certain, unless there's a case-sensitivity error somewhere: you can double-check the rangeToArray() method in the source code
    • Maggi
      Maggi almost 9 years
  • Geoffrey
    Geoffrey over 8 years
    Your num2char function could be simplified to just 'A' + $num provided it doesn't go past 'Z'
  • John
    John almost 7 years
    This whole problem could be simplified to serving a static Excel file, as long as the data in the file never needs to change. HTH. ;)
  • Abdul Rehman
    Abdul Rehman over 2 years