PHP PHPExcel::removeSheetByIndex方法代码示例

	/**
	 * Loads PHPExcel from file
	 *
	 * @param 	string 		$pFilename
	 * @throws 	Exception
	 */
	public function load($pFilename)
	{
		// Check if file exists
		if (!file_exists($pFilename)) {
			throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
		}

		// Initialisations
		$excel = new PHPExcel;
		$excel->removeSheetByIndex(0);
		if (!$this->_readDataOnly) {
			$excel->removeCellStyleXfByIndex(0); // remove the default style
			$excel->removeCellXfByIndex(0); // remove the default style
		}
		$zip = new ZipArchive;
		$zip->open($pFilename);

		$rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
		foreach ($rels->Relationship as $rel) {
			switch ($rel["Type"]) {
				case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
					$xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
					if ($xmlCore) {
						$xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
						$xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
						$xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
						$docProps = $excel->getProperties();
						$docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator")));
						$docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy")));
						$docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created")))); //! respect xsi:type
						$docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified")))); //! respect xsi:type
						$docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title")));
						$docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description")));
						$docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject")));
						$docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords")));
						$docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category")));
					}
				break;

				case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
					$dir = dirname($rel["Target"]);
					$relsWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/_rels/" . basename($rel["Target"]) . ".rels"));  //~ http://schemas.openxmlformats.org/package/2006/relationships");
					$relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");

					$sharedStrings = array();
					$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']"));
					$xmlStrings = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]"));  //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
					if (isset($xmlStrings) && isset($xmlStrings->si)) {
						foreach ($xmlStrings->si as $val) {
							if (isset($val->t)) {
								$sharedStrings[] = PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $val->t );
							} elseif (isset($val->r)) {
								$sharedStrings[] = $this->_parseRichText($val);
							}
						}
					}

					$worksheets = array();
					foreach ($relsWorkbook->Relationship as $ele) {
						if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
							$worksheets[(string) $ele["Id"]] = $ele["Target"];
						}
					}

					$styles 	= array();
					$cellStyles = array();
					$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']"));
					$xmlStyles = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
					$numFmts = null;
					if ($xmlStyles && $xmlStyles->numFmts[0]) {
						$numFmts = $xmlStyles->numFmts[0];
					}
					if (isset($numFmts) && !is_null($numFmts)) {
						$numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
					}
					if (!$this->_readDataOnly && $xmlStyles) {
						foreach ($xmlStyles->cellXfs->xf as $xf) {
							$numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;

							if ($xf["numFmtId"]) {
								if (isset($numFmts)) {
									$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));

									if (isset($tmpNumFmt["formatCode"])) {
										$numFmt = (string) $tmpNumFmt["formatCode"];
									}
								}

								if ((int)$xf["numFmtId"] < 164) {
									$numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
								}
							}
							//$numFmt = str_replace('mm', 'i', $numFmt);
							//$numFmt = str_replace('h', 'H', $numFmt);
//.........这里部分代码省略.........

 public function testSimple()
 {
     $data = ['ROOT' => ['LEVEL1' => [['LEVEL1_CAPTION' => '1'], ['LEVEL1_CAPTION' => '2']]]];
     $namedRange = $this->output->getNamedRange('ROOT');
     $this->sut->write($data, $namedRange);
     $this->output->setActiveSheetIndexByName('TEMPLATE');
     $this->output->removeSheetByIndex($this->output->getActiveSheetIndex());
     $excelWriter = \PHPExcel_IOFactory::createWriter($this->output, 'Excel2007');
     $excelWriter->save(__DIR__ . '/../metadata/output/verticalrangewritertest.xlsx');
 }

 /**
  * Constructs one Moodle Workbook.
  *
  * @param string $filename The name of the file
  * @param string $type file format type 'Excel5' or 'Excel2007'
  */
 public function __construct($filename, $type = 'Excel2007')
 {
     global $CFG;
     require_once "{$CFG->libdir}/phpexcel/PHPExcel.php";
     $this->objPHPExcel = new PHPExcel();
     $this->objPHPExcel->removeSheetByIndex(0);
     $this->filename = $filename;
     if (strtolower($type) === 'excel5') {
         $this->type = 'Excel5';
     } else {
         $this->type = 'Excel2007';
     }
 }

 /**
  * Excel with one sheet
  * @param string|null $filePath
  * @param bool $withoutSheets
  * @throws PhpExcelException
  */
 public function __construct($filePath = null, $withoutSheets = true)
 {
     try {
         $this->excel = $filePath ? PhpOffice_PHPExcel_IOFactory::load($filePath) : new PhpOffice_PHPExcel();
     } catch (PhpOffice_PHPExcel_Reader_Exception $ex) {
         throw new PhpExcelException('Unable to create excel object.', 0, $e);
     }
     if ($filePath) {
         $this->excelType = PhpOffice_PHPExcel_IOFactory::identify($filePath);
     }
     if (!$filePath && $withoutSheets) {
         $this->excel->removeSheetByIndex();
     }
 }

 /**
  * Writes cells to the spreadsheet
  *  array(
  *	   1 => array('A1', 'B1', 'C1', 'D1', 'E1'),
  *	   2 => array('A2', 'B2', 'C2', 'D2', 'E2'),
  *	   3 => array('A3', 'B3', 'C3', 'D3', 'E3'),
  *  );
  * 
  * @param array of array( [row] => array([col]=>[value]) ) ie $arr[row][col] => value
  * @return void
  */
 public function set_data(array $data, $multi_sheet = FALSE)
 {
     // Single sheet ones can just dump everything to the current sheet
     if (!$multi_sheet) {
         $sheet = $this->_spreadsheet->getActiveSheet();
         $this->set_sheet_data($data, $sheet);
     } else {
         foreach ($data as $sheetname => $sheetData) {
             $sheet = $this->_spreadsheet->createSheet();
             $sheet->setTitle($sheetname);
             $this->set_sheet_data($sheetData, $sheet);
         }
         // Now remove the auto-created blank sheet at start of XLS
         $this->_spreadsheet->removeSheetByIndex(0);
     }
 }

 /**
  * @param null|array $properties
  *
  * @throws \PHPExcel_Exception
  */
 public function start(array $properties = null)
 {
     $this->object = new \PHPExcel();
     $this->object->removeSheetByIndex(0);
     $this->attributes['properties'] = $properties ?: [];
     if ($properties !== null) {
         $this->setProperties($properties, $this->mappings);
     }
 }

 /**
  * Re-bind parent
  *
  * @param PHPExcel $parent
  */
 public function rebindParent(PHPExcel $parent)
 {
     $namedRanges = $this->_parent->getNamedRanges();
     foreach ($namedRanges as $namedRange) {
         $parent->addNamedRange($namedRange);
     }
     $this->_parent->removeSheetByIndex($this->_parent->getindex($this));
     $this->_parent = $parent;
 }

 /**
  * Re-bind parent
  *
  * @param PHPExcel $parent
  * @return PHPExcel_Worksheet
  */
 public function rebindParent(PHPExcel $parent)
 {
     if ($this->parent !== null) {
         $namedRanges = $this->parent->getNamedRanges();
         foreach ($namedRanges as $namedRange) {
             $parent->addNamedRange($namedRange);
         }
         $this->parent->removeSheetByIndex($this->parent->getIndex($this));
     }
     $this->parent = $parent;
     return $this;
 }

 /**
 * Retrieve data and split to sheets.
 * Example:
 * <pre><code>
 * <?php
    $function = function($offset, $limit)
    {
        $result = ...
        return array(
            $result['data'],
            $result['total'],
        );
    };
    $xls = new XLSReport();
    $xls->splitToSheets($function, $titles);
 * ?>
 * </code></pre>
 * @param function $function
 * @param array $titles
 * @param PHPExcel_Chart $charts
 */
 public function splitToSheets(&$function, &$titles = array(), $charts = null)
 {
     $offset = 0;
     $limit = $this->limitLength;
     do {
         list($data, $total) = $function($offset, $limit);
         if (!empty($data)) {
             $this->newSheet($data, $titles, $charts);
         }
         $offset += $limit;
     } while ($offset < $total);
     $this->objPHPExcel->removeSheetByIndex($this->objPHPExcel->getSheetCount() - 1);
 }

 /**
  * (non-PHPdoc)
  * @see \scipper\Datatransfer\TransferService::generateEmptyDocument()
  */
 public function generateDocument(Map $map)
 {
     if (!class_exists("PHPExcel")) {
         throw new GenerationException("dependency 'PHPExcel' not found");
     }
     $excel = new \PHPExcel();
     $excel->removeSheetByIndex(0);
     $excel->getProperties()->setCreator($map->getCreator());
     $excel->getProperties()->setTitle($map->getTitle());
     $protectedStyle = new \PHPExcel_Style();
     $protectedStyle->applyFromArray(array("fill" => array("type" => \PHPExcel_Style_Fill::FILL_SOLID, "color" => array("argb" => "55CCCCCC")), "borders" => array("bottom" => array("style" => \PHPExcel_Style_Border::BORDER_THIN), "right" => array("style" => \PHPExcel_Style_Border::BORDER_MEDIUM))));
     $i = 0;
     foreach ($map->getSheets() as $sheet) {
         $active = $excel->addSheet(new \PHPExcel_Worksheet(NULL, $sheet->getTitle()), $i);
         $active->getProtection()->setSheet(true);
         $active->getStyle("A1:Z30")->getProtection()->setLocked(\PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
         foreach ($sheet->getCells() as $cell) {
             //Convert content to list format ist necessary
             if ($cell->getType() == "select") {
                 $dataValidation = $active->getCell($cell->getCoord())->getDataValidation();
                 $dataValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST);
                 $dataValidation->setAllowBlank(false);
                 $dataValidation->setShowInputMessage(true);
                 $dataValidation->setShowDropDown(true);
                 $dataValidation->setFormula1($cell->getContent());
             } else {
                 $active->setCellValue($cell->getCoord(), $cell->getValue());
             }
             //Add protection is necessary
             if ($cell->isProtected()) {
                 $active->protectCells($cell->getCoord(), "123");
                 $active->setSharedStyle($protectedStyle, $cell->getCoord());
                 // 				} elseif(!$cell->isProtected() && $active->getProtection()->isProtectionEnabled()) {
                 // 					$active->unprotectCells($cell->getCoord());
             }
             $active->getColumnDimension($cell->getX())->setAutoSize(true);
             if (!$cell->isVisible()) {
                 $active->getColumnDimension($cell->getX())->setVisible(false);
             }
         }
         $i++;
     }
     $excel->setActiveSheetIndex(0);
     $writer = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
     $filename = $this->documentRoot . $excel->getProperties()->getTitle() . ".xlsx";
     $writer->save($filename);
     return $filename;
 }

 /**
  * Loads PHPExcel from file
  *
  * @param 	string 		$pFilename
  * @throws 	PHPExcel_Reader_Exception
  */
 public function load($pFilename)
 {
     // Check if file exists
     if (!file_exists($pFilename)) {
         throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
     }
     // Initialisations
     $excel = new PHPExcel();
     $excel->removeSheetByIndex(0);
     if (!$this->_readDataOnly) {
         $excel->removeCellStyleXfByIndex(0);
         // remove the default style
         $excel->removeCellXfByIndex(0);
         // remove the default style
     }
     $zip = new ZipArchive();
     $zip->open($pFilename);
     //	Read the theme first, because we need the colour scheme when reading the styles
     $wbRels = simplexml_load_string($this->_getFromZipArchive($zip, "xl/_rels/workbook.xml.rels"));
     //~ http://schemas.openxmlformats.org/package/2006/relationships");
     foreach ($wbRels->Relationship as $rel) {
         switch ($rel["Type"]) {
             case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme":
                 $themeOrderArray = array('lt1', 'dk1', 'lt2', 'dk2');
                 $themeOrderAdditional = count($themeOrderArray);
                 $xmlTheme = simplexml_load_string($this->_getFromZipArchive($zip, "xl/{$rel['Target']}"));
                 if (is_object($xmlTheme)) {
                     $xmlThemeName = $xmlTheme->attributes();
                     $xmlTheme = $xmlTheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
                     $themeName = (string) $xmlThemeName['name'];
                     $colourScheme = $xmlTheme->themeElements->clrScheme->attributes();
                     $colourSchemeName = (string) $colourScheme['name'];
                     $colourScheme = $xmlTheme->themeElements->clrScheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
                     $themeColours = array();
                     foreach ($colourScheme as $k => $xmlColour) {
                         $themePos = array_search($k, $themeOrderArray);
                         if ($themePos === false) {
                             $themePos = $themeOrderAdditional++;
                         }
                         if (isset($xmlColour->sysClr)) {
                             $xmlColourData = $xmlColour->sysClr->attributes();
                             $themeColours[$themePos] = $xmlColourData['lastClr'];
                         } elseif (isset($xmlColour->srgbClr)) {
                             $xmlColourData = $xmlColour->srgbClr->attributes();
                             $themeColours[$themePos] = $xmlColourData['val'];
                         }
                     }
                     self::$_theme = new PHPExcel_Reader_Excel2007_Theme($themeName, $colourSchemeName, $themeColours);
                 }
                 break;
         }
     }
     $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels"));
     //~ http://schemas.openxmlformats.org/package/2006/relationships");
     foreach ($rels->Relationship as $rel) {
         switch ($rel["Type"]) {
             case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
                 $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
                 if (is_object($xmlCore)) {
                     $xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
                     $xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
                     $xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
                     $docProps = $excel->getProperties();
                     $docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator")));
                     $docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy")));
                     $docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created"))));
                     //! respect xsi:type
                     $docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified"))));
                     //! respect xsi:type
                     $docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title")));
                     $docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description")));
                     $docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject")));
                     $docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords")));
                     $docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category")));
                 }
                 break;
             case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties":
                 $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
                 if (is_object($xmlCore)) {
                     $docProps = $excel->getProperties();
                     if (isset($xmlCore->Company)) {
                         $docProps->setCompany((string) $xmlCore->Company);
                     }
                     if (isset($xmlCore->Manager)) {
                         $docProps->setManager((string) $xmlCore->Manager);
                     }
                 }
                 break;
             case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties":
                 $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
                 if (is_object($xmlCore)) {
                     $docProps = $excel->getProperties();
                     foreach ($xmlCore as $xmlProperty) {
                         $cellDataOfficeAttributes = $xmlProperty->attributes();
//.........这里部分代码省略.........

 /**
  * @author caochunhui@dachuwang.com
  * @description 用数组和地址直接生成excel文件
  * 每一个数组占一个sheet
  */
 private function _convert_array_to_excel($arr = array(), $sheet_titles = array(), $out_name = '', $barcode_arr = array())
 {
     //下面的代码是抄的。
     //set cache
     $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
     PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
     //open excel file
     $write_objPHPExcel = new PHPExcel();
     $write_objPHPExcel->getDefaultStyle()->getFont()->setName('simsun')->setSize(10);
     //下面要循环了
     $sheet_cnt = 0;
     foreach ($arr as $item) {
         //用订单id.csv来命名每一个sheet
         $out_sheet = new PHPExcel_Worksheet($write_objPHPExcel, $sheet_titles[$sheet_cnt]);
         //$out_sheet->setTitle($item);
         //row index start from 1
         $row_index = 0;
         foreach ($item as $row) {
             $row_index++;
             //$cellIterator = $row->getCellIterator();
             //$cellIterator->setIterateOnlyExistingCells(false);
             //column index start from 0
             $column_index = -1;
             foreach ($row as $cell) {
                 $column_index++;
                 //var_dump($cell);
                 $out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell, PHPExcel_Cell_DataType::TYPE_STRING);
             }
         }
         //如果条码数组不为空，那么说明需要在sheet里插入条码
         if (!empty($barcode_arr) && isset($barcode_arr[$sheet_cnt])) {
             $barcode_download_res = $this->_download_barcode($barcode_arr[$sheet_cnt]);
             if ($barcode_download_res['code'] == 200) {
                 //no pic you say a jb
                 $pic_path = $barcode_download_res['file'];
                 $objDrawing = new PHPExcel_Worksheet_Drawing();
                 $objDrawing->setName('barcode');
                 $objDrawing->setDescription('');
                 $objDrawing->setPath($pic_path);
                 $objDrawing->setHeight(50);
                 $objDrawing->setCoordinates('D26');
                 //$objDrawing->setOffsetX(10);
                 //$objDrawing->getShadow()->setVisible(true);
                 //$objDrawing->getShadow()->setDirection(36);
                 $objDrawing->setWorksheet($out_sheet);
                 //no pic you say a jb
             }
         }
         $write_objPHPExcel->addSheet($out_sheet);
         $sheet_cnt++;
     }
     $write_objPHPExcel->removeSheetByIndex(0);
     //删除第一个空sheet
     //上面要循环了
     //上面的代码是抄的
     //write excel file
     $objWriter = new PHPExcel_Writer_Excel2007($write_objPHPExcel);
     $dir_name = dirname($out_name);
     if (!is_dir($dir_name)) {
         $res = mkdir($dir_name, 0777, TRUE);
     }
     $objWriter->save($out_name);
 }

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

    $activeSheet->setCellValue('A' . $stt, 'Người báo làm thêm giờ');
    $activeSheet->mergeCells('A' . $stt . ':B' . $stt);
    $activeSheet->setCellValue('C' . $stt, 'Trưởng phòng');
    $activeSheet->mergeCells('C' . $stt . ':G' . $stt);
    $activeSheet->getStyle('A' . $stt . ':' . 'G' . $stt)->applyFromArray($indam)->applyFromArray($canhgiua);
    $activeSheet->getColumnDimension('A')->setWidth(15);
    $activeSheet->getColumnDimension('B')->setWidth(50);
    $activeSheet->getColumnDimension('C')->setWidth(8);
    $activeSheet->getColumnDimension('D')->setWidth(8);
    $activeSheet->getColumnDimension('E')->setWidth(8);
    $activeSheet->getColumnDimension('F')->setWidth(8);
    $activeSheet->getColumnDimension('G')->setWidth(15);
    $objWorkSheet->setTitle("T{$key}");
    $sheet++;
}
$objPHPExcel->removeSheetByIndex(0);
/**
 * start export
*/
ob_end_clean();
$excelFileName = 'Làm thêm giờ - ' . $tennv;
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $excelFileName . '.xlsx"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header('Expires: Mon, 20 Jan 2015 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1

 /**
  * Loads PHPExcel from file
  *
  * @param 	string 		$pFilename
  * @throws 	Exception
  */
 public function load($pFilename)
 {
     // Initialisations
     $this->_phpExcel = new PHPExcel();
     $this->_phpExcel->removeSheetByIndex(0);
     // remove 1st sheet
     if (!$this->_readDataOnly) {
         $this->_phpExcel->removeCellStyleXfByIndex(0);
         // remove the default style
         $this->_phpExcel->removeCellXfByIndex(0);
         // remove the default style
     }
     // Use ParseXL for the hard work.
     $this->_ole = new PHPExcel_Shared_OLERead();
     // get excel data
     $res = $this->_ole->read($pFilename);
     $this->_data = $this->_ole->getWorkBook();
     // total byte size of Excel data (workbook global substream + sheet substreams)
     $this->_dataSize = strlen($this->_data);
     // initialize
     $this->_pos = 0;
     $this->_codepage = 'CP1252';
     $this->_formats = array();
     $this->_objFonts = array();
     $this->_palette = array();
     $this->_sheets = array();
     $this->_externalBooks = array();
     $this->_ref = array();
     $this->_definedname = array();
     $this->_sst = array();
     $this->_drawingGroupData = '';
     $this->_xfIndex = '';
     $this->_mapCellXfIndex = array();
     $this->_mapCellStyleXfIndex = array();
     // Parse Workbook Global Substream
     while ($this->_pos < $this->_dataSize) {
         $code = $this->_GetInt2d($this->_data, $this->_pos);
         switch ($code) {
             case self::XLS_Type_BOF:
                 $pos = $this->_pos;
                 $length = $this->_GetInt2d($this->_data, $pos + 2);
                 $recordData = substr($this->_data, $pos + 4, $length);
                 // offset: 0; size: 2; BIFF version
                 $this->_version = $this->_GetInt2d($this->_data, $pos + 4);
                 if ($this->_version != self::XLS_BIFF8 && $this->_version != self::XLS_BIFF7) {
                     return false;
                 }
                 // offset: 2; size: 2; type of stream
                 $substreamType = $this->_GetInt2d($this->_data, $pos + 6);
                 if ($substreamType != self::XLS_WorkbookGlobals) {
                     return false;
                 }
                 $this->_pos += 4 + $length;
                 break;
             case self::XLS_Type_FILEPASS:
                 $this->_readFilepass();
                 break;
             case self::XLS_Type_CODEPAGE:
                 $this->_readCodepage();
                 break;
             case self::XLS_Type_DATEMODE:
                 $this->_readDateMode();
                 break;
             case self::XLS_Type_FONT:
                 $this->_readFont();
                 break;
             case self::XLS_Type_FORMAT:
                 $this->_readFormat();
                 break;
             case self::XLS_Type_XF:
                 $this->_readXf();
                 break;
             case self::XLS_Type_STYLE:
                 $this->_readStyle();
                 break;
             case self::XLS_Type_PALETTE:
                 $this->_readPalette();
                 break;
             case self::XLS_Type_SHEET:
                 $this->_readSheet();
                 break;
             case self::XLS_Type_EXTERNALBOOK:
                 $this->_readExternalBook();
                 break;
             case self::XLS_Type_EXTERNSHEET:
                 $this->_readExternSheet();
                 break;
             case self::XLS_Type_DEFINEDNAME:
                 $this->_readDefinedName();
                 break;
             case self::XLS_Type_MSODRAWINGGROUP:
                 $this->_readMsoDrawingGroup();
                 break;
             case self::XLS_Type_SST:
//.........这里部分代码省略.........