I would like to copy all the data and the style of a certain range to other cells. for example I want to copy from A4: I15 and then paste exactly want I copied the contents and styles from A16. How can I do it?. this is what I want to copy:
I know only copy the data but not the style and do it with this code:
$cellValues = $objPHPExcel->getActiveSheet()->rangeToArray('A4:I15');
$objPHPExcel->getActiveSheet()->fromArray($cellValues, null, 'A16');
I need copy the styes.. and the data
function copyRows(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($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);
}
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);
}
}
}
$objPHPExcel = PHPExcel_IOFactory::load("x.xlsx");
$sheet = $objPHPExcel->getActiveSheet();
copyRows($sheet, 1, 50, 48, 11);
copyRows($sheet, 2, 7 + 5, 5, 5);
copyRows($sheet, 2, 7 + 10, 5, 5);
copyRows($sheet, 2, 7 + 15, 5, 5);
Update: @pavelDD pointed out that since version 1.x.x some of the imports has been renamed. Instead of accepting his edits to the original code I will share new version. Besides the renaming I've also fixed the annoying index bug and added a helper that copies styles from the original sheet. Unfortunately I'm too lazy to convert it from a class to a simple function like I did last time. But based on the previous version you should be able to do that by yourself if needed.
<?php
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
class Utils {
public static function copyRows( Worksheet $sheet, $srcRange, $dstCell, Worksheet $destSheet = null) {
if( !isset($destSheet)) {
$destSheet = $sheet;
}
if( !preg_match('/^([A-Z]+)(\d+):([A-Z]+)(\d+)$/', $srcRange, $srcRangeMatch) ) {
// Invalid src range
return;
}
if( !preg_match('/^([A-Z]+)(\d+)$/', $dstCell, $destCellMatch) ) {
// Invalid dest cell
return;
}
$srcColumnStart = $srcRangeMatch[1];
$srcRowStart = $srcRangeMatch[2];
$srcColumnEnd = $srcRangeMatch[3];
$srcRowEnd = $srcRangeMatch[4];
$destColumnStart = $destCellMatch[1];
$destRowStart = $destCellMatch[2];
$srcColumnStart = Coordinate::columnIndexFromString($srcColumnStart);
$srcColumnEnd = Coordinate::columnIndexFromString($srcColumnEnd);
$destColumnStart = Coordinate::columnIndexFromString($destColumnStart);
$rowCount = 0;
for ($row = $srcRowStart; $row <= $srcRowEnd; $row++) {
$colCount = 0;
for ($col = $srcColumnStart; $col <= $srcColumnEnd; $col++) {
$cell = $sheet->getCellByColumnAndRow($col, $row);
$style = $sheet->getStyleByColumnAndRow($col, $row);
$dstCell = Coordinate::stringFromColumnIndex($destColumnStart + $colCount) . (string)($destRowStart + $rowCount);
$destSheet->setCellValue($dstCell, $cell->getValue());
$destSheet->duplicateStyle($style, $dstCell);
// Set width of column, but only once per column
if ($rowCount === 0) {
$w = $sheet->getColumnDimensionByColumn($col)->getWidth();
$destSheet->getColumnDimensionByColumn ($destColumnStart + $colCount)->setAutoSize(false);
$destSheet->getColumnDimensionByColumn ($destColumnStart + $colCount)->setWidth($w);
}
$colCount++;
}
$h = $sheet->getRowDimension($row)->getRowHeight();
$destSheet->getRowDimension($destRowStart + $rowCount)->setRowHeight($h);
$rowCount++;
}
foreach ($sheet->getMergeCells() as $mergeCell) {
$mc = explode(":", $mergeCell);
$mergeColSrcStart = Coordinate::columnIndexFromString(preg_replace("/[0-9]*/", "", $mc[0]));
$mergeColSrcEnd = Coordinate::columnIndexFromString(preg_replace("/[0-9]*/", "", $mc[1]));
$mergeRowSrcStart = ((int)preg_replace("/[A-Z]*/", "", $mc[0]));
$mergeRowSrcEnd = ((int)preg_replace("/[A-Z]*/", "", $mc[1]));
$relativeColStart = $mergeColSrcStart - $srcColumnStart;
$relativeColEnd = $mergeColSrcEnd - $srcColumnStart;
$relativeRowStart = $mergeRowSrcStart - $srcRowStart;
$relativeRowEnd = $mergeRowSrcEnd - $srcRowStart;
if (0 <= $mergeRowSrcStart && $mergeRowSrcStart >= $srcRowStart && $mergeRowSrcEnd <= $srcRowEnd) {
$targetColStart = Coordinate::stringFromColumnIndex($destColumnStart + $relativeColStart);
$targetColEnd = Coordinate::stringFromColumnIndex($destColumnStart + $relativeColEnd);
$targetRowStart = $destRowStart + $relativeRowStart;
$targetRowEnd = $destRowStart + $relativeRowEnd;
$merge = (string)$targetColStart . (string)($targetRowStart) . ":" . (string)$targetColEnd . (string)($targetRowEnd);
//Merge target cells
$destSheet->mergeCells($merge);
}
}
}
public static function copyStyleXFCollection(Spreadsheet $sourceSheet, Spreadsheet $destSheet) {
$collection = $sourceSheet->getCellXfCollection();
foreach ($collection as $key => $item) {
$destSheet->addCellXf($item);
}
}
}
?>
I've taken Prashant's answer and extended it for easier use and added some additional functionality. It was a great source to begin with. But it was missing some key features for me: Taking a range (e.g. A10:B15) instead of just indexes Taking a destination cell rather then just a row. Setting width of the destination columns
Please note that I'm using PHPSpreadsheet, the successor of PHPExcel. If you are using the older version just need to update the path for the Cell class.
<?php
/**
* Copy range in PHPSpreadsheet/PHPExcel including styles
**/
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
function copyRange( Worksheet $sheet, $srcRange, $dstCell) {
// Validate source range. Examples: A2:A3, A2:AB2, A27:B100
if( !preg_match('/^([A-Z]+)(\d+):([A-Z]+)(\d+)$/', $srcRange, $srcRangeMatch) ) {
// Wrong source range
return;
}
// Validate destination cell. Examples: A2, AB3, A27
if( !preg_match('/^([A-Z]+)(\d+)$/', $dstCell, $destCellMatch) ) {
// Wrong destination cell
return;
}
$srcColumnStart = $srcRangeMatch[1];
$srcRowStart = $srcRangeMatch[2];
$srcColumnEnd = $srcRangeMatch[3];
$srcRowEnd = $srcRangeMatch[4];
$destColumnStart = $destCellMatch[1];
$destRowStart = $destCellMatch[2];
// For looping purposes we need to convert the indexes instead
// Note: We need to subtract 1 since column are 0-based and not 1-based like this method acts.
$srcColumnStart = Cell::columnIndexFromString($srcColumnStart) - 1;
$srcColumnEnd = Cell::columnIndexFromString($srcColumnEnd) - 1;
$destColumnStart = Cell::columnIndexFromString($destColumnStart) - 1;
$rowCount = 0;
for ($row = $srcRowStart; $row <= $srcRowEnd; $row++) {
$colCount = 0;
for ($col = $srcColumnStart; $col <= $srcColumnEnd; $col++) {
$cell = $sheet->getCellByColumnAndRow($col, $row);
$style = $sheet->getStyleByColumnAndRow($col, $row);
$dstCell = Cell::stringFromColumnIndex($destColumnStart + $colCount) . (string)($destRowStart + $rowCount);
$sheet->setCellValue($dstCell, $cell->getValue());
$sheet->duplicateStyle($style, $dstCell);
// Set width of column, but only once per row
if ($rowCount === 0) {
$w = $sheet->getColumnDimensionByColumn($col)->getWidth();
$sheet->getColumnDimensionByColumn ($destColumnStart + $colCount)->setAutoSize(false);
$sheet->getColumnDimensionByColumn ($destColumnStart + $colCount)->setWidth($w);
}
$colCount++;
}
$h = $sheet->getRowDimension($row)->getRowHeight();
$sheet->getRowDimension($destRowStart + $rowCount)->setRowHeight($h);
$rowCount++;
}
foreach ($sheet->getMergeCells() as $mergeCell) {
$mc = explode(":", $mergeCell);
$mergeColSrcStart = Cell::columnIndexFromString(preg_replace("/[0-9]*/", "", $mc[0])) - 1;
$mergeColSrcEnd = Cell::columnIndexFromString(preg_replace("/[0-9]*/", "", $mc[1])) - 1;
$mergeRowSrcStart = ((int)preg_replace("/[A-Z]*/", "", $mc[0]));
$mergeRowSrcEnd = ((int)preg_replace("/[A-Z]*/", "", $mc[1]));
$relativeColStart = $mergeColSrcStart - $srcColumnStart;
$relativeColEnd = $mergeColSrcEnd - $srcColumnStart;
$relativeRowStart = $mergeRowSrcStart - $srcRowStart;
$relativeRowEnd = $mergeRowSrcEnd - $srcRowStart;
if (0 <= $mergeRowSrcStart && $mergeRowSrcStart >= $srcRowStart && $mergeRowSrcEnd <= $srcRowEnd) {
$targetColStart = Cell::stringFromColumnIndex($destColumnStart + $relativeColStart);
$targetColEnd = Cell::stringFromColumnIndex($destColumnStart + $relativeColEnd);
$targetRowStart = $destRowStart + $relativeRowStart;
$targetRowEnd = $destRowStart + $relativeRowEnd;
$merge = (string)$targetColStart . (string)($targetRowStart) . ":" . (string)$targetColEnd . (string)($targetRowEnd);
//Merge target cells
$sheet->mergeCells($merge);
}
}
}
?>
Example usage:
copyRange($sheet, 'A4:B8', 'E1');
copyRange($sheet, 'A4:B8', 'A10');
copyRange($sheet, 'A4:B8', 'C17');
Note: I haven't tried it with a single cell. But in theory it should work with A1:A1
Please let me if I have made any mistake, or feel free to edit the answer.
I found a solution without additional code:
<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\BaseReader;
/** @var BaseReader $reader */
$reader = IOFactory::createReaderForFile('old_file.xlsx');
$reader->setReadDataOnly(false);
$spreadsheet = $reader->load('old_file.xlsx');
$worksheet = $spreadsheet->getActiveSheet();
/** Iterate needed cells in $worksheet and change their values **/
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('new_file.xlsx');
The important part is to set option $reader->setReadDataOnly(false) (which is set by default) because if option is set to true then only data without styles are read.
