在PHPExcel中复制样式和数据 [英] copy style and data in PHPExcel
问题描述
我想将所有数据和某个范围的样式复制到其他单元格.例如，我想从A4:I15复制，然后粘贴，正是我想要从A16复制内容和样式.我该怎么做?.这就是我要复制的内容:
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');
我需要复制样式和数据
推荐答案
更新: @pavelDD 指出，自版本1.xx的某些导入已被重命名.我将不接受他对原始代码的编辑，而是共享新版本.除了重命名，我还修复了烦人的索引错误，并添加了一个可从原始工作表中复制样式的助手.不幸的是，我懒得像上次那样将其从类转换为简单的函数.但是根据以前的版本，如果需要，您应该可以自己完成此操作.
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);
}
}
}
?>
我已经接受了Prashant的回答，并对其进行了扩展以便于使用，并添加了一些其他功能.首先是一个很好的资源.但这对我来说缺少一些关键功能: 取一个范围(例如A10:B15)而不只是索引 选择目标单元格而不是一行. 设置目标列的宽度
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
请注意，我使用的是PHPExcel的后继版本PHPSpreadsheet. 如果您使用的是旧版本，则只需更新Cell类的路径.
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);
}
}
}
?>
示例用法:
copyRange($sheet, 'A4:B8', 'E1');
copyRange($sheet, 'A4:B8', 'A10');
copyRange($sheet, 'A4:B8', 'C17');
注意:我还没有尝试过使用单个单元格.但从理论上讲，它应该与A1:A1
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.
这篇关于在PHPExcel中复制样式和数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
