PHP PHPExcel::addExternalSheet方法代码示例

 protected function prepare(\PHPExcel_Worksheet $templateSheet)
 {
     $this->output = new \PHPExcel();
     $outputSheet = $this->output->getActiveSheet();
     $outputSheet->setTitle('Report');
     $this->templateSheet = $this->output->addExternalSheet($templateSheet);
     foreach ($this->templateSheet->getColumnDimensions() as $col => $columnDimension) {
         $outputSheet->getColumnDimension($col)->setWidth($columnDimension->getWidth());
     }
 }

 protected function setUp()
 {
     $logger = new \Monolog\Logger('rendertest');
     $logger->pushHandler(new \Monolog\Handler\StreamHandler('php://stdout', \Monolog\Logger::DEBUG));
     $inputFileType = \PHPExcel_IOFactory::identify(__DIR__ . '/../metadata/template/singlelevel.xlsx');
     $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
     $template = $objReader->load(__DIR__ . '/../metadata/template/singlelevel.xlsx');
     $this->output = new \PHPExcel();
     $outputSheet = $this->output->getActiveSheet();
     $templateSheet = $this->output->addExternalSheet($template->getSheetByName('TEMPLATE'));
     $namedRange = $this->output->getNamedRange('ROOT');
     $rangeData = $templateSheet->rangeToArray($namedRange->getRange(), null, false, true, true);
     print_R($rangeData);
     $namedRange = $this->output->getNamedRange('LEVEL1');
     $rangeData = $templateSheet->rangeToArray($namedRange->getRange(), null, false, true, true);
     print_R($rangeData);
     die;
     $reportSheet = new \PHPExcelReport\Report\ReportSheet($outputSheet);
     $this->sut = new \PHPExcelReport\Report\Writer\VerticalRangeWriter($templateSheet, $reportSheet, $logger);
 }

<?php

//	パスを通す
set_include_path('/_lib/Classes/');
//	インクルード
include_once dirname(__FILE__) . '/_lib/Classes/PHPExcel.php';
include_once dirname(__FILE__) . '/_lib/Classes/PHPExcel/IOFactory.php';
//	テンプレート読み込み
$reader = PHPExcel_IOFactory::createReader('Excel5');
$template = $reader->load(dirname(__FILE__) . '/template.xls');
print dirname(__FILE__) . '\\template.xls';
$templateSheet = clone $template->getActiveSheet();
$excel = new PHPExcel();
$needlessSheet = $excel->getActiveSheet();
$sheet = $excel->addExternalSheet($templateSheet);
$sheet->setTitle('テスト');
//	出力
//  必要ないシートを削除
$sheetIndex = $excel->GetIndex($needlessSheet);
$excel->removeSheetByIndex($sheetIndex);
$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
$outputFileName = '/2015_07.xls';
$writer->save(dirname(__FILE__) . $outputFileName);
print dirname(__FILE__);
print $outputFileName;

 public function export($competition, $exportFormsts, $all = false, $xlsx = false, $extra = false, $order = 'date')
 {
     $registrations = $this->getRegistrations($competition, $all, $order);
     $template = PHPExcel_IOFactory::load(Yii::getPathOfAlias('application.data.results') . '.xls');
     $export = new PHPExcel();
     $export->getProperties()->setCreator(Yii::app()->params->author)->setLastModifiedBy(Yii::app()->params->author)->setTitle($competition->wca_competition_id ?: $competition->name)->setSubject($competition->name);
     $export->removeSheetByIndex(0);
     //注册页
     $sheet = $template->getSheet(0);
     $sheet->setCellValue('A1', $competition->wca_competition_id ?: $competition->name);
     $events = $competition->getRegistrationEvents();
     $col = 'J';
     $cubecompsEvents = array('333' => '3x3', '444' => '4x4', '555' => '5x5', '666' => '6x6', '777' => '7x7', '222' => '2x2', '333bf' => '333bld', '333fm' => 'fmc', 'minx' => 'mega', 'pyram' => 'pyra', '444bf' => '444bld', '555bf' => '555bld', '333mbf' => '333mlt');
     foreach ($events as $event => $data) {
         $sheet->setCellValue($col . 2, "=SUM({$col}4:{$col}" . (count($registrations) + 4) . ')');
         $sheet->setCellValue($col . 3, isset($cubecompsEvents[$event]) ? $cubecompsEvents[$event] : $event);
         $sheet->getColumnDimension($col)->setWidth(5.5);
         $col++;
     }
     foreach ($registrations as $key => $registration) {
         $user = $registration->user;
         $row = $key + 4;
         $sheet->setCellValue('A' . $row, $registration->number)->setCellValue('B' . $row, $user->getCompetitionName())->setCellValue('C' . $row, $user->country->name)->setCellValue('D' . $row, $user->wcaid)->setCellValue('E' . $row, $user->getWcaGender())->setCellValue('F' . $row, PHPExcel_Shared_Date::FormattedPHPToExcel(date('Y', $user->birthday), date('m', $user->birthday), date('d', $user->birthday)));
         $col = 'J';
         foreach ($events as $event => $data) {
             if (in_array("{$event}", $registration->events)) {
                 $sheet->setCellValue($col . $row, 1);
             }
             $col++;
         }
         if ($extra) {
             $col++;
             $fee = $registration->getTotalFee();
             if ($registration->isPaid()) {
                 $fee .= Yii::t('common', ' (paid)');
             }
             $sheet->setCellValue($col . $row, $fee);
             $col++;
             $sheet->setCellValue($col . $row, $user->mobile);
             $col++;
             $sheet->setCellValue($col . $row, $user->email);
             $col++;
             $sheet->setCellValue($col . $row, $registration->comments);
         }
         if (!$registration->isAccepted()) {
             $sheet->getStyle("A{$row}:D{$row}")->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFF00'))));
         }
     }
     $export->addExternalSheet($sheet);
     //各个项目
     foreach ($exportFormsts as $event => $rounds) {
         $count = count($rounds);
         foreach ($rounds as $round => $format) {
             if ($round == $count - 1) {
                 $round = 'f';
             } else {
                 $round++;
             }
             $sheet = $template->getSheetByName($format);
             if ($sheet === null) {
                 continue;
             }
             $sheet = clone $sheet;
             $sheet->setTitle("{$event}-{$round}");
             $template->addSheet($sheet);
             $sheet->setCellValue('A1', Events::getFullEventName($event) . ' - ' . Rounds::getFullRoundName($round));
             if ($round == 1 || $count == 1) {
                 $row = 5;
                 foreach ($registrations as $registration) {
                     if (!in_array("{$event}", $registration->events)) {
                         continue;
                     }
                     $user = $registration->user;
                     $sheet->setCellValue('B' . $row, $user->getCompetitionName())->setCellValue('C' . $row, $user->country->name)->setCellValue('D' . $row, $user->wcaid);
                     if ($row > 5) {
                         $formula = $sheet->getCell('A' . ($row - 1))->getValue();
                         $formula = strtr($formula, array('-4' => '_temp_', $row - 1 => $row, $row - 2 => $row - 1, $row => $row + 1));
                         $formula = str_replace('_temp_', '-4', $formula);
                         $sheet->setCellValue('A' . $row, $formula);
                     }
                     if (!$registration->isAccepted()) {
                         $sheet->getStyle("A{$row}:D{$row}")->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFF00'))));
                     }
                     $row++;
                 }
             }
             $export->addExternalSheet($sheet);
         }
     }
     $this->exportToExcel($export, 'php://output', $competition->name, $xlsx);
 }