CODEWITHSUNDEEP
phpexcel
Mervyn
Mervyn

Reputation: 891

Copy an entire column using phpexcel

Am trying to copy an entire column or copy the values to another column.my script can determine the column that needs to be copied over and then highestrow value. any suggestions.

Upvotes: 0

Views: 1587

Answers (1)

Nidust
Nidust

Reputation: 619

I were having the same issue, and after several days of searching, all I've got so far is this topic [copy style and data in PHPExcel ]. The code worked perfectly and clearly to understand, but just as you, I need to copy from column to column, not row to row. Then, I figured out copy a column basically is just "self-copy a cell to another cell index in a row". So here's the code, tested and it's worked for me. Hope this can help.

/**
 * Copy excel column to column
 * @param $sheet:current active sheet
 * @param $srcRow: source row
 * @param $dstRow: destination row
 * @param $height: rows number want to copy
 * @param $width: column number want to copy
 **/
private function selfCopyRow(\PHPExcel_Worksheet $sheet, $srcRow, $dstRow, $height, $width)
{
    for ($row = 0; $row < $height; $row++) {
        for ($col = 0; $col < $width; $col++) {
            $cell = $sheet->getCellByColumnAndRow($col, $srcRow + $row);
            $style = $sheet->getStyleByColumnAndRow($col, $srcRow + $row);
            $dstCell = \PHPExcel_Cell::stringFromColumnIndex(($width + $col)) . (string)($dstRow + $row);
            $sheet->setCellValue($dstCell, $cell->getValue());
            $sheet->duplicateStyle($style, $dstCell);
        }

        $h = $sheet->getRowDimension($srcRow + $row)->getRowHeight();
        $sheet->getRowDimension($dstRow + $row)->setRowHeight($h);
    }

    // EN : Copy format
    foreach ($sheet->getMergeCells() as $mergeCell) {
        $mc = explode(":", $mergeCell);
        $col_s = preg_replace("/[0-9]*/", "", $mc[0]);
        $col_e = preg_replace("/[0-9]*/", "", $mc[1]);
        $row_s = ((int)preg_replace("/[A-Z]*/", "", $mc[0])) - $srcRow;
        $row_e = ((int)preg_replace("/[A-Z]*/", "", $mc[1])) - $srcRow;

        if (0 <= $row_s && $row_s < $height) {
            $merge = $col_s . (string)($dstRow + $row_s) . ":" . $col_e . (string)($dstRow + $row_e);
            $sheet->mergeCells($merge);
        }
    }

}

Upvotes: 3

Related Questions