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); }