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