Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
[PHPExcel] 範囲コピー(セルの結合(マージ)にも対応)
<?php
/**
* セルの書式を指定位置にコピーする
* @param $from_cells コピー元のセル範囲
* @param $to_cell コピー先の左上のセル
* @param $copycount コピーを繰り返す回数(下へコピーするのみ)
*/
public function copy_cells_format($from_cells, $to_cell, $copycount=1) {
// ----------------------------------------
// コピー元のセルの範囲を数値で取得
// ----------------------------------------
$from_area = PHPExcel_Cell::rangeBoundaries($from_cells); // ex) array( array(2, "2"),array(53, "3"))
$from_start_col = (int)$from_area[0][0]; // 開始列数値 ex) B --- 2
$from_start_row = (int)$from_area[0][1]; // 開始行数値
$from_end_col = (int)$from_area[1][0]; // 終了列数値 ex) BA --- 53
$from_end_row = (int)$from_area[1][1]; // 終了行数値
// ----------------------------------------
// 範囲の行数、列数を取得
// ----------------------------------------
$area_dmsn = PHPExcel_Cell::rangeDimension($from_cells); // ex) array( 53, 1 )
$area_cols = $area_dmsn[0];
$area_rows = $area_dmsn[1];
// ----------------------------------------
// セルをマージ
// ----------------------------------------
// array(21) { ["AH6:AK6"]=> string(7) "AH6:AK6" ["AL6:AO6"]=> string(7) "AL6:AO6" ["AP6:AS6"]=> string(7) "AP6:AS6" ["AT6:AV6"]=> string(7) "AT6:AV6" ["AD6:AF6"]=> string(7) "AD6:AF6" ["B6:C6"]=> string(5) "B6:C6" ["D6:Q6"]=> string(5) "D6:Q6" ["R6:U6"]=> string(5) "R6:U6" ["V6:Y6"]=> string(5) "V6:Y6" ["Z6:AC6"]=> string(6) "Z6:AC6" ["B2:AW3"]=> string(6) "B2:AW3" ["B5:C5"]=> string(5) "B5:C5" ["R5:U5"]=> string(5) "R5:U5" ["V5:Y5"]=> string(5) "V5:Y5" ["Z5:AC5"]=> string(6) "Z5:AC5" ["AD5:AG5"]=> string(7) "AD5:AG5" ["AH5:AK5"]=> string(7) "AH5:AK5" ["AL5:AO5"]=> string(7) "AL5:AO5" ["AP5:AS5"]=> string(7) "AP5:AS5" ["AT5:AW5"]=> string(7) "AT5:AW5" ["D5:Q5"]=> string(5) "D5:Q5" }
$mg = $this->current_sheet->getMergeCells();
$mg_cells = array();
foreach ($mg as $mg_cell) {
$area = PHPExcel_Cell::rangeBoundaries($mg_cell);
if( $from_area[0][0] <= $area[0][0] && $from_area[1][0] >= $area[1][0]
&& $from_area[0][1] <= $area[0][1] && $from_area[1][1] >= $area[1][1]) {
$mg_cells[] = $mg_cell;
}
}
$to = PHPExcel_Cell::rangeBoundaries($to_cell); // ex) array( array(2, "2"),array(53, "3"))
$to_start_col = (int)$to[0][0]; // 開始列数値 ex) B --- 2
$to_start_row = (int)$to[0][1]; // 開始行数値
// コピー回数分繰り返す
for( $cp_c = 0; $cp_c < $copycount; $cp_c++ ) {
// コピー元
$from_col = $from_start_col;
$from_row = $from_start_row;
// コピー先
$row_offset = $cp_c * $area_rows; // 位置の調整
$start_col = $to_start_col;
$start_row = $to_start_row + $row_offset;
// 移動距離をチェック
$pos_col = $start_col - $from_col;
$pos_row = $start_row - $from_row;
// セル結合
foreach ($mg_cells as $mg_cell) {
$mg_area = PHPExcel_Cell::rangeBoundaries($mg_cell); // ex) array( array(2, "2"),array(53, "3"))
// 位置を調整
$mg_area[0][0] += $pos_col;
$mg_area[1][0] += $pos_col;
$mg_area[0][1] += $pos_row;
$mg_area[1][1] += $pos_row;
$mg_start = PHPExcel_Cell::stringFromColumnIndex($mg_area[0][0] - 1) . ($mg_area[0][1]);
$mg_end = PHPExcel_Cell::stringFromColumnIndex($mg_area[1][0] - 1) . ($mg_area[1][1]);
$mg_range = $mg_start . ":" . $mg_end;
$this->current_sheet->mergeCells( $mg_range );
}
// セルの書式設定
for( $i = 0; $i < $area_rows; $i++ ) {
for( $j = 0; $j <= $area_cols; $j++ ) {
// 値とスタイルを取得
$original = PHPExcel_Cell::stringFromColumnIndex($from_col + $j - 1) . ($from_row + $i);
$value = $this->current_sheet->getCell($original)->getValue();
$style = $this->current_sheet->getStyle($original);
// 値とスタイルを設定
$target = PHPExcel_Cell::stringFromColumnIndex($start_col + $j - 1) . ($start_row + $i);
$this->current_sheet->setCellValue($target, $value);
$this->current_sheet->duplicateStyle($style , $target );
}
}
}
}
@onocom

This comment has been minimized.

Copy link
Owner Author

commented Oct 16, 2015

どなたかご利用になる時には
$this->current_sheet
を適宜変更ください。

@touiminao

This comment has been minimized.

Copy link

commented Feb 8, 2018

$this->current_sheet どういう風に宣言しましたか?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.