public function payQuickerReport()
 {
     $market = array(1 => 'US', 2 => 'CA', 3 => 'AU', 4 => 'NZ', 5 => 'GB');
     //        error_reporting(E_ALL);
     $data = $this->RoyaltiesEarned->getRoyaltiesReport(3, ['method' => 'all']);
     //        echo '<pre>';
     //        print_r($data);
     //        echo '</pre>';
     //        die;
     //        for ($i = 0; $i <= $queryCount; $i = $i + $this->payQuickerReportLimit) {
     //
     //        }
     $objPHPExcel = new PHPExcel();
     // Set properties
     $objPHPExcel->getProperties()->setCreator("");
     $objPHPExcel->getProperties()->setLastModifiedBy("");
     $objPHPExcel->getProperties()->setTitle("");
     $objPHPExcel->getProperties()->setSubject("");
     $objPHPExcel->getProperties()->setDescription("");
     $objPHPExcel->setActiveSheetIndex(0);
     $objPHPExcel->getActiveSheet()->setTitle('Instructions');
     // Add some data
     $objPHPExcel->createSheet(1);
     $objPHPExcel->setActiveSheetIndex(1);
     $objPHPExcel->getActiveSheet()->setTitle('Instant Payments');
     $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'INSTANT PAYMENTS');
     $objPHPExcel->getActiveSheet()->SetCellValue('A2', "RECIPIENT'S EMAIL ADDRESS\n * Required!");
     $objPHPExcel->getActiveSheet()->SetCellValue('B2', "PAYMENT AMOUNT\n * Required");
     $objPHPExcel->getActiveSheet()->SetCellValue('C2', "COUNTRY CODE\n * Required");
     $objPHPExcel->getActiveSheet()->SetCellValue('D2', "STATE CODE\n * Required");
     $objPHPExcel->getActiveSheet()->SetCellValue('E2', "COMMENT\n Optional");
     $objPHPExcel->getActiveSheet()->SetCellValue('F2', "SECURITY ID\n Optional");
     $objPHPExcel->getActiveSheet()->SetCellValue('G2', "SECURITY ID HINT\n Optional");
     $objPHPExcel->getActiveSheet()->SetCellValue('H2', "ACCOUNTING ID\n Optional");
     $objPHPExcel->getActiveSheet()->SetCellValue('I2', "EXPIRATION DATE\n Optional");
     $objPHPExcel->getActiveSheet()->SetCellValue('J2', "UDF1\n Optional");
     $objPHPExcel->getActiveSheet()->SetCellValue('K2', "UDF2\n Optional");
     $objPHPExcel->getActiveSheet()->SetCellValue('L2', "UDF3\n Optional");
     $objPHPExcel->getActiveSheet()->SetCellValue('M2', "AUTO ISSUE DEBIT CARDS\n Optional");
     $objPHPExcel->getActiveSheet()->SetCellValue('N2', "EXCLUDE FROM 1099 RECONCILIATION\n Optional");
     // Add data
     for ($i = 0; $i < count($data); $i++) {
         $cell = $i + 3;
         $objPHPExcel->getActiveSheet()->setCellValue('A' . $cell, $data[$i]['Email']['email'])->setCellValue('B' . $cell, $data[$i]['RoyaltiesEarned']['amount'])->setCellValue('C' . $cell, $market[$data[$i]['RoyaltiesEarned']['market_id']])->setCellValue('D' . $cell, $data[$i]['State']['abbrev'])->setCellValue('D' . $cell, $data[$i]['State']['abbrev'])->setCellValue('E' . $cell, $data[$i]['User']['first_name'])->setCellValue('M' . $cell, 'YES');
     }
     $objPHPExcel->createSheet(2);
     $objPHPExcel->setActiveSheetIndex(2);
     $objPHPExcel->getActiveSheet()->setTitle('Version');
     $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Version');
     $objPHPExcel->getActiveSheet()->SetCellValue('A2', '816eda7a-db7a-4c83-adeb-206bdfad2bb0');
     // Save Excel 2007 file
     $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
     // We'll be outputting an excel file
     //        header('Content-type: application/vnd.ms-excel');
     // It will be called file.xls
     //        header('Content-Disposition: attachment; filename="file.xls"');
     // Write file to the browser
     //        $objWriter->save('php://output');
     $objWriter->save(str_replace('.php', '.xlsx', __FILE__));
 }
Example #2
1
	/**
	 * 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);

							$style = (object) array(
								"numFmt" => $numFmt,
								"font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
								"fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
								"border" => $xmlStyles->borders->border[intval($xf["borderId"])],
								"alignment" => $xf->alignment,
								"protection" => $xf->protection,
							);
							$styles[] = $style;

							// add style to cellXf collection
							$objStyle = new PHPExcel_Style;
							$this->_readStyle($objStyle, $style);
							$excel->addCellXf($objStyle);
						}

						foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
							$numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
							if ($numFmts && $xf["numFmtId"]) {
								$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
								if (isset($tmpNumFmt["formatCode"])) {
									$numFmt = (string) $tmpNumFmt["formatCode"];
								} else if ((int)$xf["numFmtId"] < 165) {
									$numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
								}
							}

							$cellStyle = (object) array(
								"numFmt" => $numFmt,
								"font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
								"fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
								"border" => $xmlStyles->borders->border[intval($xf["borderId"])],
								"alignment" => $xf->alignment,
								"protection" => $xf->protection,
							);
							$cellStyles[] = $cellStyle;

							// add style to cellStyleXf collection
							$objStyle = new PHPExcel_Style;
							$this->_readStyle($objStyle, $cellStyle);
							$excel->addCellStyleXf($objStyle);
						}
					}

					$dxfs = array();
					if (!$this->_readDataOnly && $xmlStyles) {
						if ($xmlStyles->dxfs) {
							foreach ($xmlStyles->dxfs->dxf as $dxf) {
								$style = new PHPExcel_Style;
								$this->_readStyle($style, $dxf);
								$dxfs[] = $style;
							}
						}

						if ($xmlStyles->cellStyles)
						{
							foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
								if (intval($cellStyle['builtinId']) == 0) {
									if (isset($cellStyles[intval($cellStyle['xfId'])])) {
										// Set default style
										$style = new PHPExcel_Style;
										$this->_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]);

										// normal style, currently not using it for anything
									}
								}
							}
						}
					}

					$xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));  //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");

					// Set base date
					if ($xmlWorkbook->workbookPr) {
						PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
						if (isset($xmlWorkbook->workbookPr['date1904'])) {
							$date1904 = (string)$xmlWorkbook->workbookPr['date1904'];
							if ($date1904 == "true" || $date1904 == "1") {
								PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
							}
						}
					}

					$sheetId = 0; // keep track of new sheet id in final workbook
					$oldSheetId = -1; // keep track of old sheet id in final workbook
					$countSkippedSheets = 0; // keep track of number of skipped sheets
					$mapSheetId = array(); // mapping of sheet ids from old to new

					if ($xmlWorkbook->sheets)
					{
						foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
							++$oldSheetId;

							// Check if sheet should be skipped
							if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) {
								++$countSkippedSheets;
								$mapSheetId[$oldSheetId] = null;
								continue;
							}

							// Map old sheet id in original workbook to new sheet id.
							// They will differ if loadSheetsOnly() is being used
							$mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets;

							// Load sheet
							$docSheet = $excel->createSheet();
							$docSheet->setTitle((string) $eleSheet["name"]);
							$fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
							$xmlSheet = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$fileWorksheet"));  //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");

							$sharedFormulas = array();

							if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') {
								$docSheet->setSheetState( (string) $eleSheet["state"] );
							}

							if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
							    if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) {
								    $docSheet->getSheetView()->setZoomScale( intval($xmlSheet->sheetViews->sheetView['zoomScale']) );
								}

							    if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) {
								    $docSheet->getSheetView()->setZoomScaleNormal( intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal']) );
								}

								if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
									$docSheet->setShowGridLines((string)$xmlSheet->sheetViews->sheetView['showGridLines'] ? true : false);
								}

								if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) {
									$docSheet->setShowRowColHeaders((string)$xmlSheet->sheetViews->sheetView['showRowColHeaders'] ? true : false);
								}

								if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) {
									$docSheet->setRightToLeft((string)$xmlSheet->sheetViews->sheetView['rightToLeft'] ? true : false);
								}

								if (isset($xmlSheet->sheetViews->sheetView->pane)) {
								    if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) {
								        $docSheet->freezePane( (string)$xmlSheet->sheetViews->sheetView->pane['topLeftCell'] );
								    } else {
								        $xSplit = 0;
								        $ySplit = 0;

								        if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) {
								            $xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']);
								        }

								    	if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) {
								            $ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']);
								        }

								        $docSheet->freezePaneByColumnAndRow($xSplit, $ySplit);
								    }
								}

								if (isset($xmlSheet->sheetViews->sheetView->selection)) {
									if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) {
										$sqref = (string)$xmlSheet->sheetViews->sheetView->selection['sqref'];
										$sqref = explode(' ', $sqref);
										$sqref = $sqref[0];
										$docSheet->setSelectedCells($sqref);
									}
								}

							}

							if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) {
								if (isset($xmlSheet->sheetPr->tabColor['rgb'])) {
									$docSheet->getTabColor()->setARGB( (string)$xmlSheet->sheetPr->tabColor['rgb'] );
								}
							}

							if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
								if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && $xmlSheet->sheetPr->outlinePr['summaryRight'] == false) {
									$docSheet->setShowSummaryRight(false);
								} else {
									$docSheet->setShowSummaryRight(true);
								}

								if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && $xmlSheet->sheetPr->outlinePr['summaryBelow'] == false) {
									$docSheet->setShowSummaryBelow(false);
								} else {
									$docSheet->setShowSummaryBelow(true);
								}
							}

							if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) {
								if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && $xmlSheet->sheetPr->pageSetUpPr['fitToPage'] == false) {
									$docSheet->getPageSetup()->setFitToPage(false);
								} else {
									$docSheet->getPageSetup()->setFitToPage(true);
								}
							}

							if (isset($xmlSheet->sheetFormatPr)) {
								if (isset($xmlSheet->sheetFormatPr['customHeight']) && ((string)$xmlSheet->sheetFormatPr['customHeight'] == '1' || strtolower((string)$xmlSheet->sheetFormatPr['customHeight']) == 'true') && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
									$docSheet->getDefaultRowDimension()->setRowHeight( (float)$xmlSheet->sheetFormatPr['defaultRowHeight'] );
								}
								if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
									$docSheet->getDefaultColumnDimension()->setWidth( (float)$xmlSheet->sheetFormatPr['defaultColWidth'] );
								}
							}

							if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
								foreach ($xmlSheet->cols->col as $col) {
									for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
										if ($col["style"] && !$this->_readDataOnly) {
											$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"]));
										}
										if ($col["bestFit"]) {
											//$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(true);
										}
										if ($col["hidden"]) {
											$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setVisible(false);
										}
										if ($col["collapsed"]) {
											$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setCollapsed(true);
										}
										if ($col["outlineLevel"] > 0) {
											$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
										}
										$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));

										if (intval($col["max"]) == 16384) {
											break;
										}
									}
								}
							}

							if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
								if ($xmlSheet->printOptions['gridLinesSet'] == 'true' && $xmlSheet->printOptions['gridLinesSet'] == '1') {
									$docSheet->setShowGridlines(true);
								}

								if ($xmlSheet->printOptions['gridLines'] == 'true' || $xmlSheet->printOptions['gridLines'] == '1') {
									$docSheet->setPrintGridlines(true);
								}

								if ($xmlSheet->printOptions['horizontalCentered']) {
									$docSheet->getPageSetup()->setHorizontalCentered(true);
								}
								if ($xmlSheet->printOptions['verticalCentered']) {
									$docSheet->getPageSetup()->setVerticalCentered(true);
								}
							}

							if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
								foreach ($xmlSheet->sheetData->row as $row) {
									if ($row["ht"] && !$this->_readDataOnly) {
										$docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
									}
									if ($row["hidden"] && !$this->_readDataOnly) {
										$docSheet->getRowDimension(intval($row["r"]))->setVisible(false);
									}
									if ($row["collapsed"]) {
										$docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true);
									}
									if ($row["outlineLevel"] > 0) {
										$docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
									}
									if ($row["s"] && !$this->_readDataOnly) {
										$docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"]));
									}

									foreach ($row->c as $c) {
										$r 					= (string) $c["r"];
										$cellDataType 		= (string) $c["t"];
										$value				= null;
										$calculatedValue 	= null;

										// Read cell?
										if (!is_null($this->getReadFilter())) {
											$coordinates = PHPExcel_Cell::coordinateFromString($r);

											if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
												continue;
											}
										}

	//									echo '<b>Reading cell '.$coordinates[0].$coordinates[1].'</b><br />';
	//									print_r($c);
	//									echo '<br />';
	//									echo 'Cell Data Type is '.$cellDataType.': ';
	//
										// Read cell!
										switch ($cellDataType) {
											case "s":
	//											echo 'String<br />';
												if ((string)$c->v != '') {
													$value = $sharedStrings[intval($c->v)];

													if ($value instanceof PHPExcel_RichText) {
														$value = clone $value;
													}
												} else {
													$value = '';
												}

												break;
											case "b":
	//											echo 'Boolean<br />';
												if (!isset($c->f)) {
													$value = $this->_castToBool($c);
												} else {
													// Formula
													$this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToBool');
	//												echo '$calculatedValue = '.$calculatedValue.'<br />';
												}
												break;
											case "inlineStr":
	//											echo 'Inline String<br />';
												$value = $this->_parseRichText($c->is);

												break;
											case "e":
	//											echo 'Error<br />';
												if (!isset($c->f)) {
													$value = $this->_castToError($c);
												} else {
													// Formula
													$this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToError');
	//												echo '$calculatedValue = '.$calculatedValue.'<br />';
												}

												break;

											default:
	//											echo 'Default<br />';
												if (!isset($c->f)) {
	//												echo 'Not a Formula<br />';
													$value = $this->_castToString($c);
												} else {
	//												echo 'Treat as Formula<br />';
													// Formula
													$this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToString');
	//												echo '$calculatedValue = '.$calculatedValue.'<br />';
												}

												break;
										}
	//									echo 'Value is '.$value.'<br />';

										// Check for numeric values
										if (is_numeric($value) && $cellDataType != 's') {
											if ($value == (int)$value) $value = (int)$value;
											elseif ($value == (float)$value) $value = (float)$value;
											elseif ($value == (double)$value) $value = (double)$value;
										}

										// Rich text?
										if ($value instanceof PHPExcel_RichText && $this->_readDataOnly) {
											$value = $value->getPlainText();
										}

										$cell = $docSheet->getCell($r);
										// Assign value
										if ($cellDataType != '') {
											$cell->setValueExplicit($value, $cellDataType);
										} else {
											$cell->setValue($value);
										}
										if (!is_null($calculatedValue)) {
											$cell->setCalculatedValue($calculatedValue);
										}

										// Style information?
										if ($c["s"] && !$this->_readDataOnly) {
											// no style index means 0, it seems
											$cell->setXfIndex(isset($styles[intval($c["s"])]) ?
												intval($c["s"]) : 0);
										}
									}
								}
							}

							$conditionals = array();
							if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) {
								foreach ($xmlSheet->conditionalFormatting as $conditional) {
									foreach ($conditional->cfRule as $cfRule) {
										if (
											(
												(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_NONE ||
												(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CELLIS ||
												(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT ||
												(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_EXPRESSION
											) && isset($dxfs[intval($cfRule["dxfId"])])
										) {
											$conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule;
										}
									}
								}

								foreach ($conditionals as $ref => $cfRules) {
									ksort($cfRules);
									$conditionalStyles = array();
									foreach ($cfRules as $cfRule) {
										$objConditional = new PHPExcel_Style_Conditional();
										$objConditional->setConditionType((string)$cfRule["type"]);
										$objConditional->setOperatorType((string)$cfRule["operator"]);

										if ((string)$cfRule["text"] != '') {
											$objConditional->setText((string)$cfRule["text"]);
										}

										if (count($cfRule->formula) > 1) {
											foreach ($cfRule->formula as $formula) {
												$objConditional->addCondition((string)$formula);
											}
										} else {
											$objConditional->addCondition((string)$cfRule->formula);
										}
										$objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]);
										$conditionalStyles[] = $objConditional;
									}

									// Extract all cell references in $ref
									$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($ref);
									foreach ($aReferences as $reference) {
										$docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles);
									}
								}
							}

							$aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells");
							if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
								foreach ($aKeys as $key) {
									$method = "set" . ucfirst($key);
									$docSheet->getProtection()->$method($xmlSheet->sheetProtection[$key] == "true");
								}
							}

							if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
								$docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], true);
								if ($xmlSheet->protectedRanges->protectedRange) {
									foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
										$docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
									}
								}
							}

							if ($xmlSheet && $xmlSheet->autoFilter && !$this->_readDataOnly) {
								$docSheet->setAutoFilter((string) $xmlSheet->autoFilter["ref"]);
							}

							if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
								foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
									$docSheet->mergeCells((string) $mergeCell["ref"]);
								}
							}

							if ($xmlSheet && $xmlSheet->pageMargins && !$this->_readDataOnly) {
								$docPageMargins = $docSheet->getPageMargins();
								$docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"]));
								$docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"]));
								$docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"]));
								$docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"]));
								$docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"]));
								$docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"]));
							}

							if ($xmlSheet && $xmlSheet->pageSetup && !$this->_readDataOnly) {
								$docPageSetup = $docSheet->getPageSetup();

								if (isset($xmlSheet->pageSetup["orientation"])) {
									$docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
								}
								if (isset($xmlSheet->pageSetup["paperSize"])) {
									$docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"]));
								}
								if (isset($xmlSheet->pageSetup["scale"])) {
									$docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), false);
								}
								if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) {
									$docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), false);
								}
								if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) {
									$docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), false);
								}
								if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) &&
									((string)$xmlSheet->pageSetup["useFirstPageNumber"] == 'true' || (string)$xmlSheet->pageSetup["useFirstPageNumber"] == '1')) {
									$docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"]));
								}
							}

							if ($xmlSheet && $xmlSheet->headerFooter && !$this->_readDataOnly) {
								$docHeaderFooter = $docSheet->getHeaderFooter();

								if (isset($xmlSheet->headerFooter["differentOddEven"]) &&
									((string)$xmlSheet->headerFooter["differentOddEven"] == 'true' || (string)$xmlSheet->headerFooter["differentOddEven"] == '1')) {
									$docHeaderFooter->setDifferentOddEven(true);
								} else {
									$docHeaderFooter->setDifferentOddEven(false);
								}
								if (isset($xmlSheet->headerFooter["differentFirst"]) &&
									((string)$xmlSheet->headerFooter["differentFirst"] == 'true' || (string)$xmlSheet->headerFooter["differentFirst"] == '1')) {
									$docHeaderFooter->setDifferentFirst(true);
								} else {
									$docHeaderFooter->setDifferentFirst(false);
								}
								if (isset($xmlSheet->headerFooter["scaleWithDoc"]) &&
									((string)$xmlSheet->headerFooter["scaleWithDoc"] == 'false' || (string)$xmlSheet->headerFooter["scaleWithDoc"] == '0')) {
									$docHeaderFooter->setScaleWithDocument(false);
								} else {
									$docHeaderFooter->setScaleWithDocument(true);
								}
								if (isset($xmlSheet->headerFooter["alignWithMargins"]) &&
									((string)$xmlSheet->headerFooter["alignWithMargins"] == 'false' || (string)$xmlSheet->headerFooter["alignWithMargins"] == '0')) {
									$docHeaderFooter->setAlignWithMargins(false);
								} else {
									$docHeaderFooter->setAlignWithMargins(true);
								}

								$docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
								$docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
								$docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
								$docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
								$docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
								$docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
							}

							if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->_readDataOnly) {
								foreach ($xmlSheet->rowBreaks->brk as $brk) {
									if ($brk["man"]) {
										$docSheet->setBreak("A$brk[id]", PHPExcel_Worksheet::BREAK_ROW);
									}
								}
							}
							if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
								foreach ($xmlSheet->colBreaks->brk as $brk) {
									if ($brk["man"]) {
										$docSheet->setBreak(PHPExcel_Cell::stringFromColumnIndex($brk["id"]) . "1", PHPExcel_Worksheet::BREAK_COLUMN);
									}
								}
							}

							if ($xmlSheet && $xmlSheet->dataValidations && !$this->_readDataOnly) {
								foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
								    // Uppercase coordinate
							    	$range = strtoupper($dataValidation["sqref"]);
									$rangeSet = explode(' ',$range);
									foreach($rangeSet as $range) {
										$stRange = $docSheet->shrinkRangeToFit($range);

										// Extract all cell references in $range
										$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($stRange);
										foreach ($aReferences as $reference) {
											// Create validation
											$docValidation = $docSheet->getCell($reference)->getDataValidation();
											$docValidation->setType((string) $dataValidation["type"]);
											$docValidation->setErrorStyle((string) $dataValidation["errorStyle"]);
											$docValidation->setOperator((string) $dataValidation["operator"]);
											$docValidation->setAllowBlank($dataValidation["allowBlank"] != 0);
											$docValidation->setShowDropDown($dataValidation["showDropDown"] == 0);
											$docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0);
											$docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0);
											$docValidation->setErrorTitle((string) $dataValidation["errorTitle"]);
											$docValidation->setError((string) $dataValidation["error"]);
											$docValidation->setPromptTitle((string) $dataValidation["promptTitle"]);
											$docValidation->setPrompt((string) $dataValidation["prompt"]);
											$docValidation->setFormula1((string) $dataValidation->formula1);
											$docValidation->setFormula2((string) $dataValidation->formula2);
										}
									}
								}
							}

							// Add hyperlinks
							$hyperlinks = array();
							if (!$this->_readDataOnly) {
								// Locate hyperlink relations
								if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
									$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip,  dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
									foreach ($relsWorksheet->Relationship as $ele) {
										if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
											$hyperlinks[(string)$ele["Id"]] = (string)$ele["Target"];
										}
									}
								}

								// Loop through hyperlinks
								if ($xmlSheet && $xmlSheet->hyperlinks) {
									foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
										// Link url
										$linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');

										foreach (PHPExcel_Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) {
											$cell = $docSheet->getCell( $cellReference );
											if (isset($linkRel['id'])) {
												$cell->getHyperlink()->setUrl( $hyperlinks[ (string)$linkRel['id'] ] );
											}
											if (isset($hyperlink['location'])) {
												$cell->getHyperlink()->setUrl( 'sheet://' . (string)$hyperlink['location'] );
											}

											// Tooltip
											if (isset($hyperlink['tooltip'])) {
												$cell->getHyperlink()->setTooltip( (string)$hyperlink['tooltip'] );
											}
										}
									}
								}
							}

							// Add comments
							$comments = array();
							$vmlComments = array();
							if (!$this->_readDataOnly) {
								// Locate comment relations
								if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
									$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip,  dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
									foreach ($relsWorksheet->Relationship as $ele) {
									    if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") {
											$comments[(string)$ele["Id"]] = (string)$ele["Target"];
										}
									    if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
											$vmlComments[(string)$ele["Id"]] = (string)$ele["Target"];
										}
									}
								}

								// Loop through comments
								foreach ($comments as $relName => $relPath) {
									// Load comments file
									$relPath = PHPExcel_Shared_File::realpath(dirname("$dir/$fileWorksheet") . "/" . $relPath);
									$commentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath) );

									// Utility variables
									$authors = array();

									// Loop through authors
									foreach ($commentsFile->authors->author as $author) {
										$authors[] = (string)$author;
									}

									// Loop through contents
									foreach ($commentsFile->commentList->comment as $comment) {
										$docSheet->getComment( (string)$comment['ref'] )->setAuthor( $authors[(string)$comment['authorId']] );
										$docSheet->getComment( (string)$comment['ref'] )->setText( $this->_parseRichText($comment->text) );
									}
								}

								// Loop through VML comments
							    foreach ($vmlComments as $relName => $relPath) {
									// Load VML comments file
									$relPath = PHPExcel_Shared_File::realpath(dirname("$dir/$fileWorksheet") . "/" . $relPath);
									$vmlCommentsFile = simplexml_load_string( $this->_getFromZipArchive($zip, $relPath) );
									$vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');

									$shapes = $vmlCommentsFile->xpath('//v:shape');
									foreach ($shapes as $shape) {
										$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');

										if (isset($shape['style'])) {
	    									$style        = (string)$shape['style'];
	    									$fillColor    = strtoupper( substr( (string)$shape['fillcolor'], 1 ) );
	    									$column       = null;
	    									$row          = null;

	    									$clientData   = $shape->xpath('.//x:ClientData');
	    									if (is_array($clientData) && count($clientData) > 0) {
	        									$clientData   = $clientData[0];

	        									if ( isset($clientData['ObjectType']) && (string)$clientData['ObjectType'] == 'Note' ) {
	        									    $temp = $clientData->xpath('.//x:Row');
	        									    if (is_array($temp)) $row = $temp[0];

	        									    $temp = $clientData->xpath('.//x:Column');
	        									    if (is_array($temp)) $column = $temp[0];
	        									}
	    									}

	    									if (!is_null($column) && !is_null($row)) {
	    									    // Set comment properties
	    									    $comment = $docSheet->getCommentByColumnAndRow($column, $row + 1);
	    									    $comment->getFillColor()->setRGB( $fillColor );

	    									    // Parse style
	    									    $styleArray = explode(';', str_replace(' ', '', $style));
	    									    foreach ($styleArray as $stylePair) {
	    									        $stylePair = explode(':', $stylePair);

	    									        if ($stylePair[0] == 'margin-left')     $comment->setMarginLeft($stylePair[1]);
	    									        if ($stylePair[0] == 'margin-top')      $comment->setMarginTop($stylePair[1]);
	    									        if ($stylePair[0] == 'width')           $comment->setWidth($stylePair[1]);
	    									        if ($stylePair[0] == 'height')          $comment->setHeight($stylePair[1]);
	    									        if ($stylePair[0] == 'visibility')      $comment->setVisible( $stylePair[1] == 'visible' );

	    									    }
	    									}
										}
									}
								}

								// Header/footer images
								if ($xmlSheet && $xmlSheet->legacyDrawingHF && !$this->_readDataOnly) {
									if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
										$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip,  dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
										$vmlRelationship = '';

										foreach ($relsWorksheet->Relationship as $ele) {
											if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
												$vmlRelationship = self::dir_add("$dir/$fileWorksheet", $ele["Target"]);
											}
										}

										if ($vmlRelationship != '') {
											// Fetch linked images
											$relsVML = simplexml_load_string($this->_getFromZipArchive($zip,  dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels' )); //~ http://schemas.openxmlformats.org/package/2006/relationships");
											$drawings = array();
											foreach ($relsVML->Relationship as $ele) {
												if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
													$drawings[(string) $ele["Id"]] = self::dir_add($vmlRelationship, $ele["Target"]);
												}
											}

											// Fetch VML document
											$vmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $vmlRelationship));
											$vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');

											$hfImages = array();

											$shapes = $vmlDrawing->xpath('//v:shape');
											foreach ($shapes as $shape) {
												$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
												$imageData = $shape->xpath('//v:imagedata');
												$imageData = $imageData[0];

												$imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office');
												$style = self::toCSSArray( (string)$shape['style'] );

												$hfImages[ (string)$shape['id'] ] = new PHPExcel_Worksheet_HeaderFooterDrawing();
												if (isset($imageData['title'])) {
													$hfImages[ (string)$shape['id'] ]->setName( (string)$imageData['title'] );
												}

												$hfImages[ (string)$shape['id'] ]->setPath("zip://$pFilename#" . $drawings[(string)$imageData['relid']], false);
												$hfImages[ (string)$shape['id'] ]->setResizeProportional(false);
												$hfImages[ (string)$shape['id'] ]->setWidth($style['width']);
												$hfImages[ (string)$shape['id'] ]->setHeight($style['height']);
												$hfImages[ (string)$shape['id'] ]->setOffsetX($style['margin-left']);
												$hfImages[ (string)$shape['id'] ]->setOffsetY($style['margin-top']);
												$hfImages[ (string)$shape['id'] ]->setResizeProportional(true);
											}

											$docSheet->getHeaderFooter()->setImages($hfImages);
										}
									}
								}

							}

	// ----: Make sure drawings and graph are loaded differently!
							if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
								$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip,  dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
								$drawings = array();
								foreach ($relsWorksheet->Relationship as $ele) {
									if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") {
										$drawings[(string) $ele["Id"]] = self::dir_add("$dir/$fileWorksheet", $ele["Target"]);
									}
								}
								if ($xmlSheet->drawing && !$this->_readDataOnly) {
									foreach ($xmlSheet->drawing as $drawing) {
										$fileDrawing = $drawings[(string) self::array_item($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
										$relsDrawing = simplexml_load_string($this->_getFromZipArchive($zip,  dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
										$images = array();

										if ($relsDrawing && $relsDrawing->Relationship) {
											foreach ($relsDrawing->Relationship as $ele) {
												if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
													$images[(string) $ele["Id"]] = self::dir_add($fileDrawing, $ele["Target"]);
												}
											}
										}
										$xmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $fileDrawing))->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");

										if ($xmlDrawing->oneCellAnchor) {
											foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) {
												if ($oneCellAnchor->pic->blipFill) {
													$blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
													$xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
													$outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
													$objDrawing = new PHPExcel_Worksheet_Drawing;
													$objDrawing->setName((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
													$objDrawing->setDescription((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
													$objDrawing->setPath("zip://$pFilename#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
													$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1));
													$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff));
													$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff));
													$objDrawing->setResizeProportional(false);
													$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx")));
													$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy")));
													if ($xfrm) {
														$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
													}
													if ($outerShdw) {
														$shadow = $objDrawing->getShadow();
														$shadow->setVisible(true);
														$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
														$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
														$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
														$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
														$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
														$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
													}
													$objDrawing->setWorksheet($docSheet);
												}
											}
										}
										if ($xmlDrawing->twoCellAnchor) {
											foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) {
												if ($twoCellAnchor->pic->blipFill) {
													$blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
													$xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
													$outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
													$objDrawing = new PHPExcel_Worksheet_Drawing;
													$objDrawing->setName((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
													$objDrawing->setDescription((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
													$objDrawing->setPath("zip://$pFilename#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
													$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
													$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff));
													$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
													$objDrawing->setResizeProportional(false);

													$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx")));
													$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy")));

													if ($xfrm) {
														$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
													}
													if ($outerShdw) {
														$shadow = $objDrawing->getShadow();
														$shadow->setVisible(true);
														$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
														$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
														$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
														$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
														$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
														$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
													}
													$objDrawing->setWorksheet($docSheet);
												}
											}
										}

									}
								}
							}

							// Loop through definedNames
							if ($xmlWorkbook->definedNames) {
								foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
									// Extract range
									$extractedRange = (string)$definedName;
									$extractedRange = preg_replace('/\'(\w+)\'\!/', '', $extractedRange);
									$extractedRange = str_replace('$', '', $extractedRange);

									// Valid range?
									if (stripos((string)$definedName, '#REF!') !== false || $extractedRange == '') {
										continue;
									}

									// Some definedNames are only applicable if we are on the same sheet...
									if ((string)$definedName['localSheetId'] != '' && (string)$definedName['localSheetId'] == $sheetId) {
										// Switch on type
										switch ((string)$definedName['name']) {

											case '_xlnm._FilterDatabase':
												$docSheet->setAutoFilter($extractedRange);
												break;

											case '_xlnm.Print_Titles':
												// Split $extractedRange
												$extractedRange = explode(',', $extractedRange);

												// Set print titles
												foreach ($extractedRange as $range) {
													$matches = array();

													// check for repeating columns, e g. 'A:A' or 'A:D'
													if (preg_match('/^([A-Z]+)\:([A-Z]+)$/', $range, $matches)) {
														$docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($matches[1], $matches[2]));
													}
													// check for repeating rows, e.g. '1:1' or '1:5'
													elseif (preg_match('/^(\d+)\:(\d+)$/', $range, $matches)) {
														$docSheet->getPageSetup()->setRowsToRepeatAtTop(array($matches[1], $matches[2]));
													}
												}
												break;

											case '_xlnm.Print_Area':
												$range = explode('!', $extractedRange);
												$extractedRange = isset($range[1]) ? $range[1] : $range[0];

												$docSheet->getPageSetup()->setPrintArea($extractedRange);
												break;

											default:
												break;
										}
									}
								}
							}

							// Next sheet id
							++$sheetId;
						}

						// Loop through definedNames
						if ($xmlWorkbook->definedNames) {
							foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
								// Extract range
								$extractedRange = (string)$definedName;
								$extractedRange = preg_replace('/\'(\w+)\'\!/', '', $extractedRange);
								$extractedRange = str_replace('$', '', $extractedRange);

								// Valid range?
								if (stripos((string)$definedName, '#REF!') !== false || $extractedRange == '') {
									continue;
								}

								// Some definedNames are only applicable if we are on the same sheet...
								if ((string)$definedName['localSheetId'] != '') {
									// Local defined name
									// Switch on type
									switch ((string)$definedName['name']) {

										case '_xlnm._FilterDatabase':
										case '_xlnm.Print_Titles':
										case '_xlnm.Print_Area':
											break;

										default:
											$range = explode('!', (string)$definedName);
											if (count($range) == 2) {
												$range[0] = str_replace("''", "'", $range[0]);
												$range[0] = str_replace("'", "", $range[0]);
												if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) {
													$extractedRange = str_replace('$', '', $range[1]);
													$scope = $docSheet->getParent()->getSheet((string)$definedName['localSheetId']);

													$excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $worksheet, $extractedRange, true, $scope) );
												}
											}
											break;
									}
								} else if (!isset($definedName['localSheetId'])) {
									// "Global" definedNames
									$locatedSheet = null;
									$extractedSheetName = '';
									if (strpos( (string)$definedName, '!' ) !== false) {
										// Extract sheet name
										$extractedSheetName = PHPExcel_Worksheet::extractSheetTitle( (string)$definedName, true );
										$extractedSheetName = $extractedSheetName[0];

										// Locate sheet
										$locatedSheet = $excel->getSheetByName($extractedSheetName);

										// Modify range
										$range = explode('!', $extractedRange);
										$extractedRange = isset($range[1]) ? $range[1] : $range[0];
									}

									if (!is_null($locatedSheet)) {
										$excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $locatedSheet, $extractedRange, false) );
									}
								}
							}
						}
					}

					if (!$this->_readDataOnly) {
						// active sheet index
						$activeTab = intval($xmlWorkbook->bookViews->workbookView["activeTab"]); // refers to old sheet index

						// keep active sheet index if sheet is still loaded, else first sheet is set as the active
						if (isset($mapSheetId[$activeTab]) && $mapSheetId[$activeTab] !== null) {
							$excel->setActiveSheetIndex($mapSheetId[$activeTab]);
						} else {
							if ($excel->getSheetCount() == 0)
							{
								$excel->createSheet();
							}
							$excel->setActiveSheetIndex(0);
						}
					}
				break;
			}

		}

		return $excel;
	}
 /**
  * create new worksheet
  * 
  * @access protected
  */
 protected function create_worksheet()
 {
     $newsheet = $this->_spreadsheet->createSheet();
     $new_worksheet = new Worksheet($this->_spreadsheet, $newsheet);
     $this->_worksheets[$this->_spreadsheet->getIndex($newsheet)] = $new_worksheet;
     return $new_worksheet;
 }
 /**
  * Add sheet
  *
  * @param string $name
  * @return $this for method chaining
  */
 public function addSheet($name)
 {
     $index = $this->_xls->getSheetCount();
     $this->_xls->createSheet($index)->setTitle($name);
     $this->setActiveSheet($index);
     return $this;
 }
 public function buildSpreadSheet($worksheetName = null, $index = 0)
 {
     if ($index != 0) {
         $this->writer->createSheet($index);
     }
     $this->writer->setActiveSheetIndex($index);
     if (!is_null($worksheetName)) {
         $this->worksheetName = $this->shortsWorksheetName($worksheetName);
     }
     $this->putColumtTitlesInSpreadsheet();
     $this->putLineValuesInSpreadsheet();
     $this->writer->setActiveSheetIndex(0);
     $excelWriter = PHPExcel_IOFactory::createWriter($this->writer, 'Excel5');
     $excelWriter->save($this->spreadPath);
 }
 /**
  * @param integer|callable|string $sheetID
  * @param null                    $callback
  *
  * @return $this
  * @throws \PHPExcel_Exception
  */
 public function sheet($sheetID, $callback = null)
 {
     // Default
     $isCallable = false;
     // Init a new PHPExcel instance without any worksheets
     if (!$this->excel instanceof PHPExcel) {
         $this->original = $this->excel;
         $this->initClonedExcelObject($this->excel);
         // Clone all connected sheets
         foreach ($this->original->getAllSheets() as $sheet) {
             $this->excel->createSheet()->cloneParent($sheet);
         }
     }
     // Copy the callback when needed
     if (is_callable($sheetID)) {
         $callback = $sheetID;
         $isCallable = true;
     } elseif (is_callable($callback)) {
         $isCallable = true;
     }
     // Clone the loaded excel instance
     $this->sheet = $this->getSheetByIdOrName($sheetID);
     // Do the callback
     if ($isCallable) {
         call_user_func($callback, $this->sheet);
     }
     // Return the sheet
     return $this->sheet;
 }
 /**
  * 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);
     }
 }
 function arrayToExcel($objPHPExcel = null, $rows, $writeArrayKeysAsHeader = false, $rowStartWrite = 1, $setActiveSheetTo = 0, $sheetName = null)
 {
     include_once 'sites/all/libraries/PHPExcel/PHPExcel/Writer/Excel2007.php';
     // Create an object/instance for the output spreadsheet
     if (is_null($objPHPExcel)) {
         $objPHPExcel = new PHPExcel();
     }
     myExcel_setActiveRow($rowStartWrite);
     // Create new sheet is needed
     $sheetOk = true;
     do {
         try {
             $objPHPExcel->setActiveSheetIndex($setActiveSheetTo);
             $sheetOk = true;
         } catch (Exception $e) {
             $sheetOk = false;
             $objPHPExcel->createSheet();
         }
     } while ($sheetOk === false);
     if (is_null($sheetName)) {
         $objPHPExcel->getActiveSheet()->setTitle("Sheet {$setActiveSheetTo}");
     } else {
         $objPHPExcel->getActiveSheet()->setTitle($sheetName);
     }
     // Set basic properties to output spreadsheet
     $objPHPExcel->getProperties()->setCreator("Business USA");
     $objPHPExcel->getProperties()->setLastModifiedBy("Business USA");
     $objPHPExcel->getProperties()->setTitle("Business USA");
     $objPHPExcel->getProperties()->setSubject("Business USA");
     $objPHPExcel->getProperties()->setDescription("Business USA");
     // Debug
     if (strpos(request_uri(), '-DEBUG-NOEXCELWRITE-REPORTWRITE-') !== false) {
         ob_end_clean();
     }
     // Write headders
     if ($writeArrayKeysAsHeader === true) {
         $headders = array();
         foreach ($rows[0] as $key => $cell) {
             $headders[] = $key;
         }
         myExcel_WriteValuesToActiveRow($objPHPExcel, $headders, true);
         // Set the next row as "active" so the next time myExcel_WriteValuesToActiveRow() is called it will write to the next
         myExcel_setActiveRow(myExcel_getActiveRow() + 1);
     }
     // Write rows
     foreach ($rows as $row) {
         // Add a row into the spreadsheet
         myExcel_WriteValuesToActiveRow($objPHPExcel, $row);
         // Set the next row as "active" so the next time myExcel_WriteValuesToActiveRow() is called it will write to the next
         myExcel_setActiveRow(myExcel_getActiveRow() + 1);
     }
     // Debug
     if (strpos(request_uri(), '-DEBUG-NOEXCELWRITE-REPORTWRITE-') !== false) {
         flush();
         exit;
     }
     myExcel_decideColumnWidths($objPHPExcel, $rows);
     return $objPHPExcel;
 }
 /**
  * Creates a worksheet with the given name
  *
  * @param type  $name
  * @param array $data
  *
  * @return array \PHPExcelWorksheet
  */
 protected function createWorksheet($name, array $data)
 {
     $worksheet = $this->xls->createSheet();
     $worksheet->setTitle($name);
     $this->rowIndexes[$name] = $this->options['data_row'];
     $this->labels[$name] = [];
     return $worksheet;
 }
Example #10
0
 /**
  * Sets up the fixture, for example, opens a network connection.
  * This method is called before a test is executed.
  *
  * @access protected
  */
 protected function setUp()
 {
     if (!class_exists('\\PHPExcel')) {
         $this->markTestSkipped('PHPExcel not available');
     }
     $phpExcel = new \PHPExcel();
     $sheet = $phpExcel->createSheet();
     $this->object = new \Aimeos\MW\Container\Content\PHPExcel($sheet, 'test', array());
 }
Example #11
0
 private static function init($data, $headerTitle = [], $headerRGBColor = 'FCFCFC', $borderColor = 'CCC')
 {
     $phpExcel = new \PHPExcel();
     $phpExcel->getProperties()->setCreator('Affiliates Team')->setTitle('Excel Data');
     $phpExcelSheet = $phpExcel->createSheet(0);
     $phpExcelSheet->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
     $phpExcelSheet->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A3);
     if (!empty($headerTitle)) {
         $tmpData = [];
         $columnKeys = array_keys($headerTitle);
         foreach ($data as $dataTmpRow) {
             $tmpRow = [];
             foreach ($columnKeys as $columnKey) {
                 $tmpRow[$columnKey] = isset($dataTmpRow[$columnKey]) ? $dataTmpRow[$columnKey] : '';
             }
             $tmpData[] = $tmpRow;
         }
         $data = $tmpData;
     }
     $rowLength = count($data);
     if ($rowLength) {
         $keys = array_keys($data[0]);
         if (!empty($headerTitle)) {
             foreach ($keys as &$keysRow) {
                 $keysRow = isset($headerTitle[$keysRow]) ? $headerTitle[$keysRow] : $keysRow;
             }
         }
         $columnLength = count($keys);
         $fromCode = \PHPExcel_Cell::stringFromColumnIndex(0);
         $toCode = \PHPExcel_Cell::stringFromColumnIndex($columnLength - 1);
         for ($i = 0; $i < $columnLength; $i++) {
             $phpExcelSheet->setCellValueByColumnAndRow($i, 1, $keys[$i]);
             $phpExcelSheet->getColumnDimension(chr(65 + $i))->setAutoSize(true);
         }
         // [merging], develop later
         // $phpExcelSheet->mergeCells('A1:A2');
         // $phpExcelSheet->getStyle('A1:A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
         $phpExcelSheet->fromArray($data, ' ', 'A3');
         for ($i = 0; $i < $rowLength; ++$i) {
             $rowIndex = $i + 1;
             if ($rowIndex % 3 == 0) {
                 if ($rowIndex / 3 % 2) {
                     self::colorRow($phpExcelSheet, $fromCode, $toCode, $i + 3, 'F9F9F9');
                 } else {
                     self::colorRow($phpExcelSheet, $fromCode, $toCode, $i + 3, 'F5FMD');
                 }
             } else {
                 self::colorRow($phpExcelSheet, $fromCode, $toCode, $i + 3, 'FFFFFF');
             }
         }
         self::colorRow($phpExcelSheet, $fromCode, $toCode, 1, $headerRGBColor);
         $headerStyle = ['font' => ['bold' => true], 'alignment' => ['horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER]];
         $phpExcelSheet->getStyle($fromCode . '1:' . $toCode . '1')->applyFromArray($headerStyle);
         self::setBorderStyle($phpExcelSheet, $fromCode, $toCode, $rowLength + 2, $borderColor);
     }
     return $phpExcel;
 }
Example #12
0
 function crear_hoja($nombre = null)
 {
     $hoja = $this->excel->createSheet();
     if (isset($nombre)) {
         $hoja->setTitle(utf8_encode(strval($nombre)));
     }
     $this->excel->setActiveSheetIndex($this->excel->getSheetCount() - 1);
     $this->cursor = $this->cursor_base;
 }
 public function excelExport($project_id)
 {
     PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
     $objPHPExcel = new PHPExcel();
     $objPHPExcel->createSheet(0);
     $objPHPExcel->setActiveSheetIndex(0);
     //设置第一个内置表
     $objActSheet = $objPHPExcel->getActiveSheet();
     // 获取当前活动的表
     $objActSheet->setTitle('项目总体数据表');
     //获取项目下的所有成员id
     $examinee = $this->modelsManager->createBuilder()->columns(array('number', 'id', 'state', 'name'))->from('Examinee')->where('Examinee.type = 0 AND Examinee.project_id =  ' . $project_id)->getQuery()->execute()->toArray();
     //异常处理
     if (empty($examinee)) {
         throw new Exception('项目的被试人数为0,无法进行项目数据表生成');
     }
     $members_not_finished = array();
     foreach ($examinee as $value) {
         if ($value['state'] < 4) {
             $members_not_finished[$value['number']] = $value['name'];
         }
     }
     if (!empty($members_not_finished)) {
         $list = '项目中部分成员未完成测评过程,如下:<br/>';
         foreach ($members_not_finished as $key => $value) {
             $list .= $key . ':' . $value . '<br/>';
         }
         throw new Exception(print_r($list, true));
     }
     $i = 0;
     $result = new ProjectData();
     $start_column = 'D';
     $last = 'D';
     $last_data = null;
     foreach ($examinee as $examinee_info) {
         $data = array();
         $data = $result->getindividualComprehensive($examinee_info['id']);
         if ($i === 0) {
             $this->makeTable($data, $objActSheet);
         }
         $last = $start_column;
         $last_data = $data;
         $this->joinTable($data, $objActSheet, $start_column++, $examinee_info['number']);
         $i++;
     }
     // 计算平均值
     $this->joinAvg($objActSheet, $last_data, 'D', $last);
     //根据项目第一人成绩统计打表
     //循环写入每个人的成绩
     $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
     $file_name = './tmp/' . $project_id . '_project_data.xls';
     $objWriter->save($file_name);
     return $file_name;
 }
Example #14
0
 /**
  * Create excel from document
  *
  * @return \PHPExcel
  */
 protected function createExcel()
 {
     $this->phpexcel = new \PHPExcel();
     $tableNumber = 0;
     // Loop over all tables in document
     foreach ($this->document->getTables() as $table) {
         // Handle worksheets
         if ($tableNumber > 0) {
             $this->phpexcel->createSheet();
         }
         $excelWorksheet = $this->phpexcel->setActiveSheetIndex($tableNumber);
         if ($sheetTitle = $table->getAttribute('_excel-name')) {
             $excelWorksheet->setTitle($sheetTitle);
         }
         // Loop over all rows
         $rowNumber = 1;
         foreach ($table->getRows() as $row) {
             $excelWorksheet->getStyle($rowNumber . ':' . $rowNumber)->applyFromArray($this->getRowStylesArray($row));
             $this->setDimensions($excelWorksheet, $excelWorksheet->getRowIterator($rowNumber)->current(), $row);
             // Loop over all cells in row
             $cellNumber = 0;
             foreach ($row->getCells() as $cell) {
                 $excelCellIndex = \PHPExcel_Cell::stringFromColumnIndex($cellNumber) . $rowNumber;
                 // Set value
                 if ($explicitCellType = $cell->getAttribute('_excel-explicit') || ($explicitCellType = $row->getAttribute('_excel-explicit'))) {
                     $excelWorksheet->setCellValueExplicit($excelCellIndex, $this->changeValueEncoding($cell->getValue()), $this->convertStaticPhpExcelConstantsFromStringsToConstants($explicitCellType), true);
                 } else {
                     $excelWorksheet->setCellValue($excelCellIndex, $this->changeValueEncoding($cell->getValue()), true);
                 }
                 // Merge cells
                 $colspan = $cell->getAttribute('colspan');
                 $rowspan = $cell->getAttribute('rowspan');
                 if ($colspan || $rowspan) {
                     if ($colspan) {
                         $colspan = $colspan - 1;
                     }
                     if ($rowspan) {
                         $rowspan = $rowspan - 1;
                     }
                     $mergeCellsTargetCellIndex = \PHPExcel_Cell::stringFromColumnIndex($cellNumber + $colspan) . ($rowNumber + $rowspan);
                     $excelWorksheet->mergeCells($excelCellIndex . ':' . $mergeCellsTargetCellIndex);
                 }
                 // Set styles
                 $excelWorksheet->getStyle($excelCellIndex)->applyFromArray($this->getCellStylesArray($cell));
                 $this->setDimensions($excelWorksheet, $excelWorksheet->getCell($excelCellIndex), $cell);
                 $cellNumber++;
             }
             $rowNumber++;
         }
         $tableNumber++;
     }
     return $this->phpexcel;
 }
Example #15
0
 public function excelExport($project_id)
 {
     PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
     $objPHPExcel = new PHPExcel();
     $objPHPExcel->createSheet(0);
     $objPHPExcel->setActiveSheetIndex(0);
     //设置第一个内置表
     $objActSheet = $objPHPExcel->getActiveSheet();
     // 获取当前活动的表
     $objActSheet->setTitle('综合');
     $this->checkoutFirst($objActSheet, $project_id);
     //个人信息
     //data
     //数据
     $data = new ProjectComData();
     $data->project_check($project_id);
     $inquery_data = $data->getInqueryAnsComDetail($project_id);
     $objPHPExcel->createSheet(1);
     //添加一个表
     $objPHPExcel->setActiveSheetIndex(1);
     //设置第2个表为活动表,提供操作句柄
     $objActSheet = $objPHPExcel->getActiveSheet();
     // 获取当前活动的表
     $objActSheet->setTitle('人数统计-单项统计');
     $this->checkoutSecond($objActSheet, $project_id, $inquery_data);
     $objPHPExcel->createSheet(2);
     //添加一个表
     $objPHPExcel->setActiveSheetIndex(2);
     //设置第2个表为活动表,提供操作句柄
     $objActSheet = $objPHPExcel->getActiveSheet();
     // 获取当前活动的表
     $objActSheet->setTitle('人数统计-交叉项统计');
     $this->checkoutThird($objActSheet, $project_id, $inquery_data);
     $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
     $file_name = './tmp/' . $project_id . '_inqueryans_data.xls';
     $objWriter->save($file_name);
     return $file_name;
 }
Example #16
0
 public static function getExcelRepresantation(&$report)
 {
     // Create new PHPExcel object
     $objPHPExcel = new PHPExcel();
     // Set document properties
     $objPHPExcel->getProperties()->setCreator("PHP-Reports")->setLastModifiedBy("PHP-Reports")->setTitle("")->setSubject("")->setDescription("");
     foreach ($report->options['DataSets'] as $i => $dataset) {
         $objPHPExcel->createSheet($i);
         self::addSheet($objPHPExcel, $dataset, $i);
     }
     // Set the active sheet to the first one
     $objPHPExcel->setActiveSheetIndex(0);
     return $objPHPExcel;
 }
Example #17
0
 public function output()
 {
     $excel = new \PHPExcel();
     $sheetCount = 0;
     foreach ($this->sheets as $sheetName => $sheet) {
         if (0 !== $sheetCount) {
             $excel->createSheet($sheetCount);
         }
         $excel->setActiveSheetIndex($sheetCount);
         $this->makeSheet($excel, $sheetName, $sheet);
         $sheetCount++;
     }
     $excel->setActiveSheetIndex(0);
     $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
     $objWriter->save($this->targetFile);
 }
Example #18
0
 public function ExcelMethod($alldata, $size, $title, $ExcelTitle)
 {
     $objPHPExcel = new PHPExcel();
     for ($i = 1; $i <= $size; $i++) {
         // 新建的objPHPExcel对象就已经新建的sheet对象
         if ($i > 1) {
             //创建新的内置表
             $objPHPExcel->createSheet();
         }
         //把新创建的sheet设定为当前活动sheet
         $objPHPExcel->setActiveSheetIndex($i - 1);
         //获取当前活动sheet
         $objSheet = $objPHPExcel->getActiveSheet();
         //set width
         $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
         // p($objSheet);die();
         //给当前活动sheet起个名称
         $objSheet->setTitle($title);
         //位置居中
         $objSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
         //设置excel文件默认水平垂直方向居中
         //字体的设置
         //$objSheet->getDefaultStyle()->getFont()->setSize(14)->setName("微软雅黑");//设置默认字体大小和格式
         //填充数据 [固定的数据]
         $objSheet->setCellValue("A1", "序号")->setCellValue("B1", "座位")->setCellValue("C1", "学号")->setCellValue("D1", "姓名");
         $j = 2;
         foreach ($alldata as $key => $value) {
             $objSheet->setCellValue("A" . $j, $j - 1)->setCellValue("B" . $j, $value['num'])->setCellValue("C" . $j, $value['idname'])->setCellValue("D" . $j, trim($value['username']));
             $j++;
         }
     }
     // 困扰我一直的bug在这里
     // 由于乱码的问题导致打不开Excel文件
     ob_end_clean();
     $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel2007');
     //在controller里面的方法的互相调用
     $this->browser_export('Excel2007', $ExcelTitle . '.xlsx');
     //输出到浏览器
     $objWriter->save("php://output");
 }
Example #19
0
 public function outportExcel()
 {
     //查出了全部的数据
     $alldata = $this->register->getAllData();
     $objPHPExcel = new PHPExcel();
     //创建了3个工作簿
     for ($i = 1; $i <= 3; $i++) {
         // 新建的objPHPExcel对象就已经新建的sheet对象
         if ($i > 1) {
             //创建新的内置表
             $objPHPExcel->createSheet();
         }
         //把新创建的sheet设定为当前活动sheet
         $objPHPExcel->setActiveSheetIndex($i - 1);
         //获取当前活动sheet
         $objSheet = $objPHPExcel->getActiveSheet();
         // p($objSheet);die();
         //给当前活动sheet起个名称
         $objSheet->setTitle('第' . $i . '个工作簿');
         //位置居中
         $objSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
         //设置excel文件默认水平垂直方向居中
         //字体的设置
         //$objSheet->getDefaultStyle()->getFont()->setSize(14)->setName("微软雅黑");//设置默认字体大小和格式
         //填充数据 [固定的数据]
         $objSheet->setCellValue("A1", "姓名")->setCellValue("B1", "校区")->setCellValue("C1", "学院")->setCellValue("D1", "专业")->setCellValue("E1", "年级")->setCellValue("F1", "班级")->setCellValue("G1", "分组");
         $j = 2;
         foreach ($alldata as $key => $value) {
             //插入具体的数据
             $objSheet->setCellValue("A" . $j, $value['user_name'])->setCellValue("B" . $j, $value['school_zone'])->setCellValue("C" . $j, $value['belong'])->setCellValue("D" . $j, $value['specialty'])->setCellValue("E" . $j, $value['ngrade'])->setCellValue("F" . $j, $value['class'])->setCellValue("G" . $j, $value['group']);
             $j++;
         }
     }
     $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel2007');
     //在controller里面的方法的互相调用
     $this->browser_export('Excel2007', '学生信息表.xlsx');
     //输出到浏览器
     $objWriter->save("php://output");
 }
 /**
  * @param integer|callable|string $sheetID
  * @param null $callback
  * @return $this
  * @throws \PHPExcel_Exception
  */
 public function sheet($sheetID, $callback = null)
 {
     // Default
     $isCallable = false;
     // Copy the callback when needed
     if (is_callable($sheetID)) {
         $callback = $sheetID;
         $isCallable = true;
     }
     // Clone the loaded excel instance
     $clone = clone $this->excel;
     $sheet = $this->getSheetByIdOrName($sheetID, $isCallable);
     // Init a new PHPExcel instance without any worksheets
     $this->initClonedExcelObject($clone);
     // Create a new cloned sheet
     $this->sheet = $this->excel->createSheet()->cloneParent($sheet);
     // Do the callback
     if ($isCallable) {
         $return = call_user_func($callback, $this->sheet);
     }
     // Return the sheet
     return $this->sheet;
 }
 /**
  * Create a new sheet
  * @param  string        $title
  * @param  callback|null $callback
  * @return  LaravelExcelWriter
  */
 public function sheet($title, $callback = null)
 {
     // Clone the active sheet
     $this->sheet = $this->excel->createSheet(null, $title);
     // If a parser was set, inject it
     if ($this->parser) {
         $this->sheet->setParser($this->parser);
     }
     // Set the sheet title
     $this->sheet->setTitle($title);
     // Set the default page setup
     $this->sheet->setDefaultPageSetup();
     // Do the callback
     if ($callback instanceof Closure) {
         call_user_func($callback, $this->sheet);
     }
     // Autosize columns when no user didn't change anything about column sizing
     if (!$this->sheet->hasFixedSizeColumns()) {
         $this->sheet->setAutosize(Config::get('excel.export.autosize', false));
     }
     // Parse the sheet
     $this->sheet->parsed();
     return $this;
 }
Example #22
0
 /**
  * Добавляет страницу примеров размещения
  *
  * @param PHPExcel $objPHPExcel
  */
 private function addPlacementExamples(PHPExcel $objPHPExcel)
 {
     $objPHPExcel->createSheet(1);
     $activeSheet = $objPHPExcel->setActiveSheetIndex(1);
     $this->addLogo($activeSheet, 'C');
     $this->setHeader($activeSheet, $this->getHeaders(), 'C');
     $activeSheet->setTitle('Примеры размещений');
     $activeSheet->setCellValue('A14', 'Дата создания')->setCellValue('B14', 'Изображение')->setCellValue('C14', 'Заголовок');
     $row = 15;
     $linkColor = new PHPExcel_Style_Color('FF538ed5');
     foreach ($this->campaign->news as $news) {
         foreach ($news->teasers as $teaser) {
             if ($teaser->is_external) {
                 continue;
             }
             $activeSheet->setCellValue('A' . ($row + 2), Yii::app()->dateFormatter->formatDateTime($teaser->create_date, 'short', null));
             $img = $this->getTeaserImage($teaser);
             $img->setWorksheet($activeSheet);
             $img->setCoordinates('B' . $row);
             $activeSheet->mergeCells('B' . $row . ':B' . ($row + 4));
             $activeSheet->getStyle('B' . $row . ':B' . ($row + 4))->applyFromArray(array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => '000000')))));
             $activeSheet->setCellValue('C' . ($row + 1), $teaser->title)->setCellValue('C' . ($row + 2), $teaser->getEncryptedAbsoluteUrl())->setCellValue('C' . ($row + 3), 'Сегмент: ' . implode(' | ', $teaser->getTagNames()))->setCellValue('C' . ($row + 4), $news->name);
             $activeSheet->getStyle('C' . ($row + 1))->getFont()->setBold(true);
             $activeSheet->getStyle('C' . ($row + 2))->getFont()->setColor($linkColor);
             $activeSheet->getCell('C' . ($row + 2))->getHyperlink()->setUrl($teaser->getEncryptedAbsoluteUrl());
             $activeSheet->getStyle('C' . ($row + 4))->applyFromArray(array('font' => array('size' => 8), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)));
             $activeSheet->getStyle('A' . $row . ':C' . ($row + 4))->applyFromArray(array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('rgb' => '7f7f7f')))));
             $row += 6;
         }
     }
     $this->formatTableHeader($activeSheet, 'A', '14', 'C', '14');
     $activeSheet->getStyle('A15:C' . ($row - 2))->applyFromArray(array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('rgb' => '7f7f7f')))));
     $activeSheet->getColumnDimension('A')->setWidth(10.29 * 1.05);
     $activeSheet->getColumnDimension('B')->setWidth(15.71 * 1.05);
     $activeSheet->getColumnDimension('C')->setWidth(71 * 1.05);
 }
Example #23
0
 /**
  * Loads PHPExcel from file into PHPExcel instance
  *
  * @param 	string 		$pFilename
  * @param	PHPExcel	$objPHPExcel
  * @return 	PHPExcel
  * @throws 	Exception
  */
 public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
 {
     $fromFormats = array('\\-', '\\ ');
     $toFormats = array('-', ' ');
     $underlineStyles = array(PHPExcel_Style_Font::UNDERLINE_NONE, PHPExcel_Style_Font::UNDERLINE_DOUBLE, PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING, PHPExcel_Style_Font::UNDERLINE_SINGLE, PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING);
     $verticalAlignmentStyles = array(PHPExcel_Style_Alignment::VERTICAL_BOTTOM, PHPExcel_Style_Alignment::VERTICAL_TOP, PHPExcel_Style_Alignment::VERTICAL_CENTER, PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
     $horizontalAlignmentStyles = array(PHPExcel_Style_Alignment::HORIZONTAL_GENERAL, PHPExcel_Style_Alignment::HORIZONTAL_LEFT, PHPExcel_Style_Alignment::HORIZONTAL_RIGHT, PHPExcel_Style_Alignment::HORIZONTAL_CENTER, PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS, PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
     // Check if file exists
     if (!file_exists($pFilename)) {
         throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
     }
     $xml = simplexml_load_file($pFilename);
     $namespaces = $xml->getNamespaces(true);
     //		echo '<pre>';
     //		print_r($namespaces);
     //		echo '</pre><hr />';
     //
     //		echo '<pre>';
     //		print_r($xml);
     //		echo '</pre><hr />';
     //
     $docProps = $objPHPExcel->getProperties();
     foreach ($xml->DocumentProperties[0] as $propertyName => $propertyValue) {
         switch ($propertyName) {
             case 'Title':
                 $docProps->setTitle($propertyValue);
                 break;
             case 'Subject':
                 $docProps->setSubject($propertyValue);
                 break;
             case 'Author':
                 $docProps->setCreator($propertyValue);
                 break;
             case 'Created':
                 $creationDate = strtotime($propertyValue);
                 $docProps->setCreated($creationDate);
                 break;
             case 'LastAuthor':
                 $docProps->setLastModifiedBy($propertyValue);
                 break;
             case 'Company':
                 $docProps->setCompany($propertyValue);
                 break;
             case 'Category':
                 $docProps->setCategory($propertyValue);
                 break;
             case 'Keywords':
                 $docProps->setKeywords($propertyValue);
                 break;
             case 'Description':
                 $docProps->setDescription($propertyValue);
                 break;
         }
     }
     foreach ($xml->Styles[0] as $style) {
         $style_ss = $style->attributes($namespaces['ss']);
         $styleID = (string) $style_ss['ID'];
         //			echo 'Style ID = '.$styleID.'<br />';
         if ($styleID == 'Default') {
             $this->_styles['Default'] = array();
         } else {
             $this->_styles[$styleID] = $this->_styles['Default'];
         }
         foreach ($style as $styleType => $styleData) {
             $styleAttributes = $styleData->attributes($namespaces['ss']);
             //				echo $styleType.'<br />';
             switch ($styleType) {
                 case 'Alignment':
                     foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
                         //								echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
                         $styleAttributeValue = (string) $styleAttributeValue;
                         switch ($styleAttributeKey) {
                             case 'Vertical':
                                 if (self::identifyFixedStyleValue($verticalAlignmentStyles, $styleAttributeValue)) {
                                     $this->_styles[$styleID]['alignment']['vertical'] = $styleAttributeValue;
                                 }
                                 break;
                             case 'Horizontal':
                                 if (self::identifyFixedStyleValue($horizontalAlignmentStyles, $styleAttributeValue)) {
                                     $this->_styles[$styleID]['alignment']['horizontal'] = $styleAttributeValue;
                                 }
                                 break;
                             case 'WrapText':
                                 $this->_styles[$styleID]['alignment']['wrap'] = true;
                                 break;
                         }
                     }
                     break;
                 case 'Borders':
                     foreach ($styleData->Border as $borderStyle) {
                         $borderAttributes = $borderStyle->attributes($namespaces['ss']);
                         $thisBorder = array();
                         foreach ($borderAttributes as $borderStyleKey => $borderStyleValue) {
                             //									echo $borderStyleKey.' = '.$borderStyleValue.'<br />';
                             switch ($borderStyleKey) {
                                 case 'LineStyle':
                                     $thisBorder['style'] = PHPExcel_Style_Border::BORDER_MEDIUM;
                                     //												$thisBorder['style'] = $borderStyleValue;
                                     break;
                                 case 'Weight':
                                     //												$thisBorder['style'] = $borderStyleValue;
                                     break;
                                 case 'Position':
                                     $borderPosition = strtolower($borderStyleValue);
                                     break;
                                 case 'Color':
                                     $borderColour = substr($borderStyleValue, 1);
                                     $thisBorder['color']['rgb'] = $borderColour;
                                     break;
                             }
                         }
                         if (count($thisBorder) > 0) {
                             if ($borderPosition == 'left' || $borderPosition == 'right' || $borderPosition == 'top' || $borderPosition == 'bottom') {
                                 $this->_styles[$styleID]['borders'][$borderPosition] = $thisBorder;
                             }
                         }
                     }
                     break;
                 case 'Font':
                     foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
                         //								echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
                         $styleAttributeValue = (string) $styleAttributeValue;
                         switch ($styleAttributeKey) {
                             case 'FontName':
                                 $this->_styles[$styleID]['font']['name'] = $styleAttributeValue;
                                 break;
                             case 'Size':
                                 $this->_styles[$styleID]['font']['size'] = $styleAttributeValue;
                                 break;
                             case 'Color':
                                 $this->_styles[$styleID]['font']['color']['rgb'] = substr($styleAttributeValue, 1);
                                 break;
                             case 'Bold':
                                 $this->_styles[$styleID]['font']['bold'] = true;
                                 break;
                             case 'Italic':
                                 $this->_styles[$styleID]['font']['italic'] = true;
                                 break;
                             case 'Underline':
                                 if (self::identifyFixedStyleValue($underlineStyles, $styleAttributeValue)) {
                                     $this->_styles[$styleID]['font']['underline'] = $styleAttributeValue;
                                 }
                                 break;
                         }
                     }
                     break;
                 case 'Interior':
                     foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
                         //								echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
                         switch ($styleAttributeKey) {
                             case 'Color':
                                 $this->_styles[$styleID]['fill']['color']['rgb'] = substr($styleAttributeValue, 1);
                                 break;
                         }
                     }
                     break;
                 case 'NumberFormat':
                     foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
                         //								echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
                         $styleAttributeValue = str_replace($fromFormats, $toFormats, $styleAttributeValue);
                         switch ($styleAttributeValue) {
                             case 'Short Date':
                                 $styleAttributeValue = 'dd/mm/yyyy';
                                 break;
                         }
                         if ($styleAttributeValue > '') {
                             $this->_styles[$styleID]['numberformat']['code'] = $styleAttributeValue;
                         }
                     }
                     break;
                 case 'Protection':
                     foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
                         //								echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
                     }
                     break;
             }
         }
         //			print_r($this->_styles[$styleID]);
         //			echo '<hr />';
     }
     //		echo '<hr />';
     $worksheetID = 0;
     foreach ($xml->Worksheet as $worksheet) {
         $worksheet_ss = $worksheet->attributes($namespaces['ss']);
         if (isset($this->_loadSheetsOnly) && isset($worksheet_ss['Name']) && !in_array($worksheet_ss['Name'], $this->_loadSheetsOnly)) {
             continue;
         }
         // Create new Worksheet
         $objPHPExcel->createSheet();
         $objPHPExcel->setActiveSheetIndex($worksheetID);
         if (isset($worksheet_ss['Name'])) {
             $worksheetName = (string) $worksheet_ss['Name'];
             $objPHPExcel->getActiveSheet()->setTitle($worksheetName);
         }
         $columnID = 'A';
         foreach ($worksheet->Table->Column as $columnData) {
             $columnData_ss = $columnData->attributes($namespaces['ss']);
             if (isset($columnData_ss['Index'])) {
                 $columnID = PHPExcel_Cell::stringFromColumnIndex($columnData_ss['Index'] - 1);
             }
             if (isset($columnData_ss['Width'])) {
                 $columnWidth = $columnData_ss['Width'];
                 //					echo '<b>Setting column width for '.$columnID.' to '.$columnWidth.'</b><br />';
                 $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4);
             }
             ++$columnID;
         }
         $rowID = 1;
         foreach ($worksheet->Table->Row as $rowData) {
             $row_ss = $rowData->attributes($namespaces['ss']);
             if (isset($row_ss['Index'])) {
                 $rowID = (int) $row_ss['Index'];
             }
             //				echo '<b>Row '.$rowID.'</b><br />';
             if (isset($row_ss['StyleID'])) {
                 $rowStyle = $row_ss['StyleID'];
             }
             if (isset($row_ss['Height'])) {
                 $rowHeight = $row_ss['Height'];
                 //					echo '<b>Setting row height to '.$rowHeight.'</b><br />';
                 $objPHPExcel->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight);
             }
             $columnID = 'A';
             foreach ($rowData->Cell as $cell) {
                 $cell_ss = $cell->attributes($namespaces['ss']);
                 if (isset($cell_ss['Index'])) {
                     $columnID = PHPExcel_Cell::stringFromColumnIndex($cell_ss['Index'] - 1);
                 }
                 $cellRange = $columnID . $rowID;
                 if (isset($cell_ss['MergeAcross']) || isset($cell_ss['MergeDown'])) {
                     $columnTo = $columnID;
                     if (isset($cell_ss['MergeAcross'])) {
                         $columnTo = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cell_ss['MergeAcross'] - 1);
                     }
                     $rowTo = $rowID;
                     if (isset($cell_ss['MergeDown'])) {
                         $rowTo = $rowTo + $cell_ss['MergeDown'];
                     }
                     $cellRange .= ':' . $columnTo . $rowTo;
                     $objPHPExcel->getActiveSheet()->mergeCells($cellRange);
                 }
                 $hasCalculatedValue = false;
                 $cellDataFormula = '';
                 if (isset($cell_ss['Formula'])) {
                     $cellDataFormula = $cell_ss['Formula'];
                     $hasCalculatedValue = true;
                 }
                 if (isset($cell->Data)) {
                     $cellValue = $cellData = $cell->Data;
                     $type = PHPExcel_Cell_DataType::TYPE_NULL;
                     $cellData_ss = $cellData->attributes($namespaces['ss']);
                     if (isset($cellData_ss['Type'])) {
                         $cellDataType = $cellData_ss['Type'];
                         switch ($cellDataType) {
                             /*
                             const TYPE_STRING		= 's';
                             const TYPE_FORMULA		= 'f';
                             const TYPE_NUMERIC		= 'n';
                             const TYPE_BOOL			= 'b';
                             							    const TYPE_NULL			= 's';
                             							    const TYPE_INLINE		= 'inlineStr';
                             const TYPE_ERROR		= 'e';
                             */
                             case 'String':
                                 $type = PHPExcel_Cell_DataType::TYPE_STRING;
                                 break;
                             case 'Number':
                                 $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
                                 $cellValue = (double) $cellValue;
                                 if (floor($cellValue) == $cellValue) {
                                     $cellValue = (int) $cellValue;
                                 }
                                 break;
                             case 'Boolean':
                                 $type = PHPExcel_Cell_DataType::TYPE_BOOL;
                                 $cellValue = $cellValue != 0;
                                 break;
                             case 'DateTime':
                                 $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
                                 $cellValue = PHPExcel_Shared_Date::PHPToExcel(strtotime($cellValue));
                                 break;
                             case 'Error':
                                 $type = PHPExcel_Cell_DataType::TYPE_ERROR;
                                 break;
                         }
                     }
                     if ($hasCalculatedValue) {
                         $type = PHPExcel_Cell_DataType::TYPE_FORMULA;
                         $columnNumber = PHPExcel_Cell::columnIndexFromString($columnID);
                         //	Convert R1C1 style references to A1 style references (but only when not quoted)
                         $temp = explode('"', $cellDataFormula);
                         foreach ($temp as $key => &$value) {
                             //	Only replace in alternate array entries (i.e. non-quoted blocks)
                             if ($key % 2 == 0) {
                                 preg_match_all('/(R(\\[?-?\\d*\\]?))(C(\\[?-?\\d*\\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
                                 //	Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
                                 //		through the formula from left to right. Reversing means that we work right to left.through
                                 //		the formula
                                 $cellReferences = array_reverse($cellReferences);
                                 //	Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
                                 //		then modify the formula to use that new reference
                                 foreach ($cellReferences as $cellReference) {
                                     $rowReference = $cellReference[2][0];
                                     //	Empty R reference is the current row
                                     if ($rowReference == '') {
                                         $rowReference = $rowID;
                                     }
                                     //	Bracketed R references are relative to the current row
                                     if ($rowReference[0] == '[') {
                                         $rowReference = $rowID + trim($rowReference, '[]');
                                     }
                                     $columnReference = $cellReference[4][0];
                                     //	Empty C reference is the current column
                                     if ($columnReference == '') {
                                         $columnReference = $columnNumber;
                                     }
                                     //	Bracketed C references are relative to the current column
                                     if ($columnReference[0] == '[') {
                                         $columnReference = $columnNumber + trim($columnReference, '[]');
                                     }
                                     $A1CellReference = PHPExcel_Cell::stringFromColumnIndex($columnReference - 1) . $rowReference;
                                     $value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
                                 }
                             }
                         }
                         unset($value);
                         //	Then rebuild the formula string
                         $cellDataFormula = implode('"', $temp);
                     }
                     //						echo 'Cell '.$columnID.$rowID.' is a '.$type.' with a value of '.(($hasCalculatedValue) ? $cellDataFormula : $cellValue).'<br />';
                     //
                     $objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit($hasCalculatedValue ? $cellDataFormula : $cellValue, $type);
                     if ($hasCalculatedValue) {
                         //							echo 'Forumla result is '.$cellValue.'<br />';
                         $objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue);
                     }
                 }
                 if (isset($cell_ss['StyleID'])) {
                     $style = (string) $cell_ss['StyleID'];
                     //						echo 'Cell style for '.$columnID.$rowID.' is '.$style.'<br />';
                     if (isset($this->_styles[$style]) && count($this->_styles[$style]) > 0) {
                         //							echo 'Cell '.$columnID.$rowID.'<br />';
                         //							print_r($this->_styles[$style]);
                         //							echo '<br />';
                         if (!$objPHPExcel->getActiveSheet()->cellExists($columnID . $rowID)) {
                             $objPHPExcel->getActiveSheet()->setCellValue($columnID . $rowID, NULL);
                         }
                         $objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->_styles[$style]);
                     }
                 }
                 ++$columnID;
             }
             ++$rowID;
         }
         ++$worksheetID;
     }
     // Return
     return $objPHPExcel;
 }
Example #24
0
    /**
	 * Panes are frozen? (in sheet currently being read). See WINDOW2 record.
	 *
	 * @var boolean
	 */
    private $_frozen;
    /**
	 * Fit printout to number of pages? (in sheet currently being read). See SHEETPR record.
	 *
	 * @var boolean
	 */
    private $_isFitToPages;
    /**
	 * Objects. One OBJ record contributes with one entry.
	 *
	 * @var array
	 */
    private $_objs;
    /**
	 * Text Objects. One TXO record corresponds with one entry.
	 *
	 * @var array
	 */
    private $_textObjects;
    /**
	 * Cell Annotations (BIFF8)
	 *
	 * @var array
	 */
    private $_cellNotes;
    /**
	 * The combined MSODRAWINGGROUP data
	 *
	 * @var string
	 */
    private $_drawingGroupData;
    /**
	 * The combined MSODRAWING data (per sheet)
	 *
	 * @var string
	 */
    private $_drawingData;
    /**
	 * Keep track of XF index
	 *
	 * @var int
	 */
    private $_xfIndex;
    /**
	 * Mapping of XF index (that is a cell XF) to final index in cellXf collection
	 *
	 * @var array
	 */
    private $_mapCellXfIndex;
    /**
	 * Mapping of XF index (that is a style XF) to final index in cellStyleXf collection
	 *
	 * @var array
	 */
    private $_mapCellStyleXfIndex;
    /**
	 * The shared formulas in a sheet. One SHAREDFMLA record contributes with one value.
	 *
	 * @var array
	 */
    private $_sharedFormulas;
    /**
	 * The shared formula parts in a sheet. One FORMULA record contributes with one value if it
	 * refers to a shared formula.
	 *
	 * @var array
	 */
    private $_sharedFormulaParts;
    /**
	 *	Read data only?
	 *		If this is true, then the Reader will only read data values for cells, it will not read any formatting information.
	 *		If false (the default) it will read data and formatting.
	 *
	 *	@return	boolean
	 */
    public function getReadDataOnly()
    {
        return $this->_readDataOnly;
    }
    /**
	 *	Set read data only
	 *		Set to true, to advise the Reader only to read data values for cells, and to ignore any formatting information.
	 *		Set to false (the default) to advise the Reader to read both data and formatting for cells.
	 *
	 *	@param	boolean	$pValue
	 *
	 *	@return	PHPExcel_Reader_Excel5
	 */
    public function setReadDataOnly($pValue = false)
    {
        $this->_readDataOnly = $pValue;
        return $this;
    }
    /**
	 *	Get which sheets to load
	 *		Returns either an array of worksheet names (the list of worksheets that should be loaded), or a null
	 *			indicating that all worksheets in the workbook should be loaded.
	 *
	 *	@return mixed
	 */
    public function getLoadSheetsOnly()
    {
        return $this->_loadSheetsOnly;
    }
    /**
	 *	Set which sheets to load
	 *
	 *	@param mixed $value
	 *		This should be either an array of worksheet names to be loaded, or a string containing a single worksheet name.
	 *		If NULL, then it tells the Reader to read all worksheets in the workbook
	 *
	 *	@return PHPExcel_Reader_Excel5
	 */
    public function setLoadSheetsOnly($value = null)
    {
        $this->_loadSheetsOnly = is_array($value) ? $value : array($value);
        return $this;
    }
    /**
	 *	Set all sheets to load
	 *		Tells the Reader to load all worksheets from the workbook.
	 *
	 *	@return	PHPExcel_Reader_Excel5
	 */
    public function setLoadAllSheets()
    {
        $this->_loadSheetsOnly = null;
        return $this;
    }
    /**
	 * Read filter
	 *
	 * @return PHPExcel_Reader_IReadFilter
	 */
    public function getReadFilter()
    {
        return $this->_readFilter;
    }
    /**
	 * Set read filter
	 *
	 * @param PHPExcel_Reader_IReadFilter $pValue
	 * @return PHPExcel_Reader_Excel5
	 */
    public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue)
    {
        $this->_readFilter = $pValue;
        return $this;
    }
    /**
	 * Create a new PHPExcel_Reader_Excel5 instance
	 */
    public function __construct()
    {
        $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
    }
    /**
	 * Can the current PHPExcel_Reader_IReader read the file?
	 *
	 * @param 	string 		$pFileName
	 * @return 	boolean
	 */
    public function canRead($pFilename)
    {
        // Check if file exists
        if (!file_exists($pFilename)) {
            throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
        }
        try {
            // Use ParseXL for the hard work.
            $ole = new PHPExcel_Shared_OLERead();
            // get excel data
            $res = $ole->read($pFilename);
            return true;
        } catch (Exception $e) {
            return false;
        }
    }
    /**
	 * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object
	 *
	 * @param 	string 		$pFilename
	 * @throws 	Exception
	 */
    public function listWorksheetNames($pFilename)
    {
        // Check if file exists
        if (!file_exists($pFilename)) {
            throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
        }
        $worksheetNames = array();
        // Read the OLE file
        $this->_loadOLE($pFilename);
        // total byte size of Excel data (workbook global substream + sheet substreams)
        $this->_dataSize = strlen($this->_data);
        $this->_pos = 0;
        $this->_sheets = array();
        // Parse Workbook Global Substream
        while ($this->_pos < $this->_dataSize) {
            $code = self::_GetInt2d($this->_data, $this->_pos);
            switch ($code) {
                case self::XLS_Type_BOF:
                    $this->_readBof();
                    break;
                case self::XLS_Type_SHEET:
                    $this->_readSheet();
                    break;
                case self::XLS_Type_EOF:
                    $this->_readDefault();
                    break 2;
                default:
                    $this->_readDefault();
                    break;
            }
        }
        foreach ($this->_sheets as $sheet) {
            if ($sheet['sheetType'] != 0x0) {
                // 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
                continue;
            }
            $worksheetNames[] = $sheet['name'];
        }
        return $worksheetNames;
    }
    /**
	 * Loads PHPExcel from file
	 *
	 * @param 	string 		$pFilename
	 * @return 	PHPExcel
	 * @throws 	Exception
	 */
    public function load($pFilename)
    {
        // Read the OLE file
        $this->_loadOLE($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
        }
        // Read the summary information stream (containing meta data)
        $this->_readSummaryInformation();
        // Read the Additional document summary information stream (containing application-specific meta data)
        $this->_readDocumentSummaryInformation();
        // 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 = self::_GetInt2d($this->_data, $this->_pos);
            switch ($code) {
                case self::XLS_Type_BOF:
                    $this->_readBof();
                    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_XFEXT:
                    $this->_readXfExt();
                    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_EXTERNNAME:
                    $this->_readExternName();
                    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:
                    $this->_readSst();
                    break;
                case self::XLS_Type_EOF:
                    $this->_readDefault();
                    break 2;
                default:
                    $this->_readDefault();
                    break;
            }
        }
        // Resolve indexed colors for font, fill, and border colors
        // Cannot be resolved already in XF record, because PALETTE record comes afterwards
        if (!$this->_readDataOnly) {
            foreach ($this->_objFonts as $objFont) {
                if (isset($objFont->colorIndex)) {
                    $color = self::_readColor($objFont->colorIndex, $this->_palette, $this->_version);
                    $objFont->getColor()->setRGB($color['rgb']);
                }
            }
            foreach ($this->_phpExcel->getCellXfCollection() as $objStyle) {
                // fill start and end color
                $fill = $objStyle->getFill();
                if (isset($fill->startcolorIndex)) {
                    $startColor = self::_readColor($fill->startcolorIndex, $this->_palette, $this->_version);
                    $fill->getStartColor()->setRGB($startColor['rgb']);
                }
                if (isset($fill->endcolorIndex)) {
                    $endColor = self::_readColor($fill->endcolorIndex, $this->_palette, $this->_version);
                    $fill->getEndColor()->setRGB($endColor['rgb']);
                }
                // border colors
                $top = $objStyle->getBorders()->getTop();
                $right = $objStyle->getBorders()->getRight();
                $bottom = $objStyle->getBorders()->getBottom();
                $left = $objStyle->getBorders()->getLeft();
                $diagonal = $objStyle->getBorders()->getDiagonal();
                if (isset($top->colorIndex)) {
                    $borderTopColor = self::_readColor($top->colorIndex, $this->_palette, $this->_version);
                    $top->getColor()->setRGB($borderTopColor['rgb']);
                }
                if (isset($right->colorIndex)) {
                    $borderRightColor = self::_readColor($right->colorIndex, $this->_palette, $this->_version);
                    $right->getColor()->setRGB($borderRightColor['rgb']);
                }
                if (isset($bottom->colorIndex)) {
                    $borderBottomColor = self::_readColor($bottom->colorIndex, $this->_palette, $this->_version);
                    $bottom->getColor()->setRGB($borderBottomColor['rgb']);
                }
                if (isset($left->colorIndex)) {
                    $borderLeftColor = self::_readColor($left->colorIndex, $this->_palette, $this->_version);
                    $left->getColor()->setRGB($borderLeftColor['rgb']);
                }
                if (isset($diagonal->colorIndex)) {
                    $borderDiagonalColor = self::_readColor($diagonal->colorIndex, $this->_palette, $this->_version);
                    $diagonal->getColor()->setRGB($borderDiagonalColor['rgb']);
                }
            }
        }
        // treat MSODRAWINGGROUP records, workbook-level Escher
        if (!$this->_readDataOnly && $this->_drawingGroupData) {
            $escherWorkbook = new PHPExcel_Shared_Escher();
            $reader = new PHPExcel_Reader_Excel5_Escher($escherWorkbook);
            $escherWorkbook = $reader->load($this->_drawingGroupData);
            // debug Escher stream
            //$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
            //$debug->load($this->_drawingGroupData);
        }
        // Parse the individual sheets
        foreach ($this->_sheets as $sheet) {
            if ($sheet['sheetType'] != 0x0) {
                // 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
                continue;
            }
            // check if sheet should be skipped
            if (isset($this->_loadSheetsOnly) && !in_array($sheet['name'], $this->_loadSheetsOnly)) {
                continue;
            }
            // add sheet to PHPExcel object
            $this->_phpSheet = $this->_phpExcel->createSheet();
            $this->_phpSheet->setTitle($sheet['name']);
            $this->_phpSheet->setSheetState($sheet['sheetState']);
            $this->_pos = $sheet['offset'];
            // Initialize isFitToPages. May change after reading SHEETPR record.
            $this->_isFitToPages = false;
            // Initialize drawingData
            $this->_drawingData = '';
            // Initialize objs
            $this->_objs = array();
            // Initialize shared formula parts
            $this->_sharedFormulaParts = array();
            // Initialize shared formulas
            $this->_sharedFormulas = array();
            // Initialize text objs
            $this->_textObjects = array();
            // Initialize cell annotations
            $this->_cellNotes = array();
            $this->textObjRef = -1;
            while ($this->_pos <= $this->_dataSize - 4) {
                $code = self::_GetInt2d($this->_data, $this->_pos);
                switch ($code) {
                    case self::XLS_Type_BOF:
                        $this->_readBof();
                        break;
                    case self::XLS_Type_PRINTGRIDLINES:
                        $this->_readPrintGridlines();
                        break;
                    case self::XLS_Type_DEFAULTROWHEIGHT:
                        $this->_readDefaultRowHeight();
                        break;
                    case self::XLS_Type_SHEETPR:
                        $this->_readSheetPr();
                        break;
                    case self::XLS_Type_HORIZONTALPAGEBREAKS:
                        $this->_readHorizontalPageBreaks();
                        break;
                    case self::XLS_Type_VERTICALPAGEBREAKS:
                        $this->_readVerticalPageBreaks();
                        break;
                    case self::XLS_Type_HEADER:
                        $this->_readHeader();
                        break;
                    case self::XLS_Type_FOOTER:
                        $this->_readFooter();
                        break;
                    case self::XLS_Type_HCENTER:
                        $this->_readHcenter();
                        break;
                    case self::XLS_Type_VCENTER:
                        $this->_readVcenter();
                        break;
                    case self::XLS_Type_LEFTMARGIN:
                        $this->_readLeftMargin();
                        break;
                    case self::XLS_Type_RIGHTMARGIN:
                        $this->_readRightMargin();
                        break;
                    case self::XLS_Type_TOPMARGIN:
                        $this->_readTopMargin();
                        break;
                    case self::XLS_Type_BOTTOMMARGIN:
                        $this->_readBottomMargin();
                        break;
                    case self::XLS_Type_PAGESETUP:
                        $this->_readPageSetup();
                        break;
                    case self::XLS_Type_PROTECT:
                        $this->_readProtect();
                        break;
                    case self::XLS_Type_SCENPROTECT:
                        $this->_readScenProtect();
                        break;
                    case self::XLS_Type_OBJECTPROTECT:
                        $this->_readObjectProtect();
                        break;
                    case self::XLS_Type_PASSWORD:
                        $this->_readPassword();
                        break;
                    case self::XLS_Type_DEFCOLWIDTH:
                        $this->_readDefColWidth();
                        break;
                    case self::XLS_Type_COLINFO:
                        $this->_readColInfo();
                        break;
                    case self::XLS_Type_DIMENSION:
                        $this->_readDefault();
                        break;
                    case self::XLS_Type_ROW:
                        $this->_readRow();
                        break;
                    case self::XLS_Type_DBCELL:
                        $this->_readDefault();
                        break;
                    case self::XLS_Type_RK:
                        $this->_readRk();
                        break;
                    case self::XLS_Type_LABELSST:
                        $this->_readLabelSst();
                        break;
                    case self::XLS_Type_MULRK:
                        $this->_readMulRk();
                        break;
                    case self::XLS_Type_NUMBER:
                        $this->_readNumber();
                        break;
                    case self::XLS_Type_FORMULA:
                        $this->_readFormula();
                        break;
                    case self::XLS_Type_SHAREDFMLA:
                        $this->_readSharedFmla();
                        break;
                    case self::XLS_Type_BOOLERR:
                        $this->_readBoolErr();
                        break;
                    case self::XLS_Type_MULBLANK:
                        $this->_readMulBlank();
                        break;
                    case self::XLS_Type_LABEL:
                        $this->_readLabel();
                        break;
                    case self::XLS_Type_BLANK:
                        $this->_readBlank();
                        break;
                    case self::XLS_Type_MSODRAWING:
                        $this->_readMsoDrawing();
                        break;
                    case self::XLS_Type_OBJ:
                        $this->_readObj();
                        break;
                    case self::XLS_Type_WINDOW2:
                        $this->_readWindow2();
                        break;
                    case self::XLS_Type_SCL:
                        $this->_readScl();
                        break;
                    case self::XLS_Type_PANE:
                        $this->_readPane();
                        break;
                    case self::XLS_Type_SELECTION:
                        $this->_readSelection();
                        break;
                    case self::XLS_Type_MERGEDCELLS:
                        $this->_readMergedCells();
                        break;
Example #25
0
 /**
  * Loads PHPExcel from file into PHPExcel instance
  *
  * @param 	string 		$pFilename
  * @param	PHPExcel	$objPHPExcel
  * @return 	PHPExcel
  * @throws 	Exception
  */
 public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
 {
     // Check if file exists
     if (!file_exists($pFilename)) {
         throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
     }
     // Create new PHPExcel
     while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) {
         $objPHPExcel->createSheet();
     }
     $objPHPExcel->setActiveSheetIndex($this->_sheetIndex);
     $fromFormats = array('\\-', '\\ ');
     $toFormats = array('-', ' ');
     // Open file
     $fileHandle = fopen($pFilename, 'r');
     if ($fileHandle === false) {
         throw new Exception("Could not open file {$pFilename} for reading.");
     }
     // Loop through file
     $rowData = array();
     $column = $row = '';
     // loop through one row (line) at a time in the file
     while (($rowData = fgets($fileHandle)) !== FALSE) {
         // convert SYLK encoded $rowData to UTF-8
         $rowData = Shared_String::SYLKtoUTF8($rowData);
         // explode each row at semicolons while taking into account that literal semicolon (;)
         // is escaped like this (;;)
         $rowData = explode("\t", str_replace('¤', ';', str_replace(';', "\t", str_replace(';;', '¤', rtrim($rowData)))));
         $dataType = array_shift($rowData);
         //	Read shared styles
         if ($dataType == 'P') {
             $formatArray = array();
             foreach ($rowData as $rowDatum) {
                 switch ($rowDatum[0]) {
                     case 'P':
                         $formatArray['numberformat']['code'] = str_replace($fromFormats, $toFormats, substr($rowDatum, 1));
                         break;
                     case 'E':
                     case 'F':
                         $formatArray['font']['name'] = substr($rowDatum, 1);
                         break;
                     case 'L':
                         $formatArray['font']['size'] = substr($rowDatum, 1);
                         break;
                     case 'S':
                         $styleSettings = substr($rowDatum, 1);
                         for ($i = 0; $i < strlen($styleSettings); ++$i) {
                             switch ($styleSettings[$i]) {
                                 case 'I':
                                     $formatArray['font']['italic'] = true;
                                     break;
                                 case 'D':
                                     $formatArray['font']['bold'] = true;
                                     break;
                                 case 'T':
                                     $formatArray['borders']['top']['style'] = Style_Border::BORDER_THIN;
                                     break;
                                 case 'B':
                                     $formatArray['borders']['bottom']['style'] = Style_Border::BORDER_THIN;
                                     break;
                                 case 'L':
                                     $formatArray['borders']['left']['style'] = Style_Border::BORDER_THIN;
                                     break;
                                 case 'R':
                                     $formatArray['borders']['right']['style'] = Style_Border::BORDER_THIN;
                                     break;
                             }
                         }
                         break;
                 }
             }
             $this->_formats['P' . $this->_format++] = $formatArray;
             //	Read cell value data
         } elseif ($dataType == 'C') {
             $hasCalculatedValue = false;
             $cellData = $cellDataFormula = '';
             foreach ($rowData as $rowDatum) {
                 switch ($rowDatum[0]) {
                     case 'C':
                     case 'X':
                         $column = substr($rowDatum, 1);
                         break;
                     case 'R':
                     case 'Y':
                         $row = substr($rowDatum, 1);
                         break;
                     case 'K':
                         $cellData = substr($rowDatum, 1);
                         break;
                     case 'E':
                         $cellDataFormula = '=' . substr($rowDatum, 1);
                         //	Convert R1C1 style references to A1 style references (but only when not quoted)
                         $temp = explode('"', $cellDataFormula);
                         foreach ($temp as $key => &$value) {
                             //	Only count/replace in alternate array entries
                             if ($key % 2 == 0) {
                                 preg_match_all('/(R(\\[?-?\\d*\\]?))(C(\\[?-?\\d*\\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
                                 //	Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
                                 //		through the formula from left to right. Reversing means that we work right to left.through
                                 //		the formula
                                 $cellReferences = array_reverse($cellReferences);
                                 //	Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
                                 //		then modify the formula to use that new reference
                                 foreach ($cellReferences as $cellReference) {
                                     $rowReference = $cellReference[2][0];
                                     //	Empty R reference is the current row
                                     if ($rowReference == '') {
                                         $rowReference = $row;
                                     }
                                     //	Bracketed R references are relative to the current row
                                     if ($rowReference[0] == '[') {
                                         $rowReference = $row + trim($rowReference, '[]');
                                     }
                                     $columnReference = $cellReference[4][0];
                                     //	Empty C reference is the current column
                                     if ($columnReference == '') {
                                         $columnReference = $column;
                                     }
                                     //	Bracketed C references are relative to the current column
                                     if ($columnReference[0] == '[') {
                                         $columnReference = $column + trim($columnReference, '[]');
                                     }
                                     $A1CellReference = Cell::stringFromColumnIndex($columnReference - 1) . $rowReference;
                                     $value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
                                 }
                             }
                         }
                         unset($value);
                         //	Then rebuild the formula string
                         $cellDataFormula = implode('"', $temp);
                         $hasCalculatedValue = true;
                         break;
                 }
             }
             $columnLetter = Cell::stringFromColumnIndex($column - 1);
             $cellData = Calculation::_unwrapResult($cellData);
             // Set cell value
             $objPHPExcel->getActiveSheet()->getCell($columnLetter . $row)->setValue($hasCalculatedValue ? $cellDataFormula : $cellData);
             if ($hasCalculatedValue) {
                 $cellData = Calculation::_unwrapResult($cellData);
                 $objPHPExcel->getActiveSheet()->getCell($columnLetter . $row)->setCalculatedValue($cellData);
             }
             //	Read cell formatting
         } elseif ($dataType == 'F') {
             $formatStyle = $columnWidth = $styleSettings = '';
             $styleData = array();
             foreach ($rowData as $rowDatum) {
                 switch ($rowDatum[0]) {
                     case 'C':
                     case 'X':
                         $column = substr($rowDatum, 1);
                         break;
                     case 'R':
                     case 'Y':
                         $row = substr($rowDatum, 1);
                         break;
                     case 'P':
                         $formatStyle = $rowDatum;
                         break;
                     case 'W':
                         list($startCol, $endCol, $columnWidth) = explode(' ', substr($rowDatum, 1));
                         break;
                     case 'S':
                         $styleSettings = substr($rowDatum, 1);
                         for ($i = 0; $i < strlen($styleSettings); ++$i) {
                             switch ($styleSettings[$i]) {
                                 case 'I':
                                     $styleData['font']['italic'] = true;
                                     break;
                                 case 'D':
                                     $styleData['font']['bold'] = true;
                                     break;
                                 case 'T':
                                     $styleData['borders']['top']['style'] = Style_Border::BORDER_THIN;
                                     break;
                                 case 'B':
                                     $styleData['borders']['bottom']['style'] = Style_Border::BORDER_THIN;
                                     break;
                                 case 'L':
                                     $styleData['borders']['left']['style'] = Style_Border::BORDER_THIN;
                                     break;
                                 case 'R':
                                     $styleData['borders']['right']['style'] = Style_Border::BORDER_THIN;
                                     break;
                             }
                         }
                         break;
                 }
             }
             if ($formatStyle > '' && $column > '' && $row > '') {
                 $columnLetter = Cell::stringFromColumnIndex($column - 1);
                 $objPHPExcel->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($this->_formats[$formatStyle]);
             }
             if (count($styleData) > 0 && $column > '' && $row > '') {
                 $columnLetter = Cell::stringFromColumnIndex($column - 1);
                 $objPHPExcel->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($styleData);
             }
             if ($columnWidth > '') {
                 if ($startCol == $endCol) {
                     $startCol = Cell::stringFromColumnIndex($startCol - 1);
                     $objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
                 } else {
                     $startCol = Cell::stringFromColumnIndex($startCol - 1);
                     $endCol = Cell::stringFromColumnIndex($endCol - 1);
                     $objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
                     do {
                         $objPHPExcel->getActiveSheet()->getColumnDimension(++$startCol)->setWidth($columnWidth);
                     } while ($startCol != $endCol);
                 }
             }
         } else {
             foreach ($rowData as $rowDatum) {
                 switch ($rowDatum[0]) {
                     case 'C':
                     case 'X':
                         $column = substr($rowDatum, 1);
                         break;
                     case 'R':
                     case 'Y':
                         $row = substr($rowDatum, 1);
                         break;
                 }
             }
         }
     }
     // Close file
     fclose($fileHandle);
     // Return
     return $objPHPExcel;
 }
Example #26
0
 /**
  * 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();
                         if (isset($cellDataOfficeAttributes['name'])) {
                             $propertyName = (string) $cellDataOfficeAttributes['name'];
                             $cellDataOfficeChildren = $xmlProperty->children('http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes');
                             $attributeType = $cellDataOfficeChildren->getName();
                             $attributeValue = (string) $cellDataOfficeChildren->{$attributeType};
                             $attributeValue = PHPExcel_DocumentProperties::convertProperty($attributeValue, $attributeType);
                             $attributeType = PHPExcel_DocumentProperties::convertPropertyType($attributeType);
                             $docProps->setCustomProperty($propertyName, $attributeValue, $attributeType);
                         }
                     }
                 }
                 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) && $numFmts !== NULL) {
                     $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);
                         $style = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection);
                         $styles[] = $style;
                         // add style to cellXf collection
                         $objStyle = new PHPExcel_Style();
                         self::_readStyle($objStyle, $style);
                         $excel->addCellXf($objStyle);
                     }
                     foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
                         $numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
                         if ($numFmts && $xf["numFmtId"]) {
                             $tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]"));
                             if (isset($tmpNumFmt["formatCode"])) {
                                 $numFmt = (string) $tmpNumFmt["formatCode"];
                             } else {
                                 if ((int) $xf["numFmtId"] < 165) {
                                     $numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int) $xf["numFmtId"]);
                                 }
                             }
                         }
                         $cellStyle = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection);
                         $cellStyles[] = $cellStyle;
                         // add style to cellStyleXf collection
                         $objStyle = new PHPExcel_Style();
                         self::_readStyle($objStyle, $cellStyle);
                         $excel->addCellStyleXf($objStyle);
                     }
                 }
                 $dxfs = array();
                 if (!$this->_readDataOnly && $xmlStyles) {
                     //	Conditional Styles
                     if ($xmlStyles->dxfs) {
                         foreach ($xmlStyles->dxfs->dxf as $dxf) {
                             $style = new PHPExcel_Style(FALSE, TRUE);
                             self::_readStyle($style, $dxf);
                             $dxfs[] = $style;
                         }
                     }
                     //	Cell Styles
                     if ($xmlStyles->cellStyles) {
                         foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
                             if (intval($cellStyle['builtinId']) == 0) {
                                 if (isset($cellStyles[intval($cellStyle['xfId'])])) {
                                     // Set default style
                                     $style = new PHPExcel_Style();
                                     self::_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]);
                                     // normal style, currently not using it for anything
                                 }
                             }
                         }
                     }
                 }
                 $xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
                 //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                 // Set base date
                 if ($xmlWorkbook->workbookPr) {
                     PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
                     if (isset($xmlWorkbook->workbookPr['date1904'])) {
                         if (self::boolean((string) $xmlWorkbook->workbookPr['date1904'])) {
                             PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
                         }
                     }
                 }
                 $sheetId = 0;
                 // keep track of new sheet id in final workbook
                 $oldSheetId = -1;
                 // keep track of old sheet id in final workbook
                 $countSkippedSheets = 0;
                 // keep track of number of skipped sheets
                 $mapSheetId = array();
                 // mapping of sheet ids from old to new
                 $charts = $chartDetails = array();
                 if ($xmlWorkbook->sheets) {
                     foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
                         ++$oldSheetId;
                         // Check if sheet should be skipped
                         if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) {
                             ++$countSkippedSheets;
                             $mapSheetId[$oldSheetId] = null;
                             continue;
                         }
                         // Map old sheet id in original workbook to new sheet id.
                         // They will differ if loadSheetsOnly() is being used
                         $mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets;
                         // Load sheet
                         $docSheet = $excel->createSheet();
                         //	Use false for $updateFormulaCellReferences to prevent adjustment of worksheet
                         //		references in formula cells... during the load, all formulae should be correct,
                         //		and we're simply bringing the worksheet name in line with the formula, not the
                         //		reverse
                         $docSheet->setTitle((string) $eleSheet["name"], false);
                         $fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
                         $xmlSheet = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/{$fileWorksheet}"));
                         //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                         $sharedFormulas = array();
                         if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') {
                             $docSheet->setSheetState((string) $eleSheet["state"]);
                         }
                         if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
                             if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) {
                                 $docSheet->getSheetView()->setZoomScale(intval($xmlSheet->sheetViews->sheetView['zoomScale']));
                             }
                             if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) {
                                 $docSheet->getSheetView()->setZoomScaleNormal(intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal']));
                             }
                             if (isset($xmlSheet->sheetViews->sheetView['view'])) {
                                 $docSheet->getSheetView()->setView((string) $xmlSheet->sheetViews->sheetView['view']);
                             }
                             if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
                                 $docSheet->setShowGridLines(self::boolean((string) $xmlSheet->sheetViews->sheetView['showGridLines']));
                             }
                             if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) {
                                 $docSheet->setShowRowColHeaders(self::boolean((string) $xmlSheet->sheetViews->sheetView['showRowColHeaders']));
                             }
                             if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) {
                                 $docSheet->setRightToLeft(self::boolean((string) $xmlSheet->sheetViews->sheetView['rightToLeft']));
                             }
                             if (isset($xmlSheet->sheetViews->sheetView->pane)) {
                                 if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) {
                                     $docSheet->freezePane((string) $xmlSheet->sheetViews->sheetView->pane['topLeftCell']);
                                 } else {
                                     $xSplit = 0;
                                     $ySplit = 0;
                                     if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) {
                                         $xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']);
                                     }
                                     if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) {
                                         $ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']);
                                     }
                                     $docSheet->freezePaneByColumnAndRow($xSplit, $ySplit);
                                 }
                             }
                             if (isset($xmlSheet->sheetViews->sheetView->selection)) {
                                 if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) {
                                     $sqref = (string) $xmlSheet->sheetViews->sheetView->selection['sqref'];
                                     $sqref = explode(' ', $sqref);
                                     $sqref = $sqref[0];
                                     $docSheet->setSelectedCells($sqref);
                                 }
                             }
                         }
                         if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) {
                             if (isset($xmlSheet->sheetPr->tabColor['rgb'])) {
                                 $docSheet->getTabColor()->setARGB((string) $xmlSheet->sheetPr->tabColor['rgb']);
                             }
                         }
                         if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
                             if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && !self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryRight'])) {
                                 $docSheet->setShowSummaryRight(FALSE);
                             } else {
                                 $docSheet->setShowSummaryRight(TRUE);
                             }
                             if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && !self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryBelow'])) {
                                 $docSheet->setShowSummaryBelow(FALSE);
                             } else {
                                 $docSheet->setShowSummaryBelow(TRUE);
                             }
                         }
                         if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) {
                             if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && !self::boolean((string) $xmlSheet->sheetPr->pageSetUpPr['fitToPage'])) {
                                 $docSheet->getPageSetup()->setFitToPage(FALSE);
                             } else {
                                 $docSheet->getPageSetup()->setFitToPage(TRUE);
                             }
                         }
                         if (isset($xmlSheet->sheetFormatPr)) {
                             if (isset($xmlSheet->sheetFormatPr['customHeight']) && self::boolean((string) $xmlSheet->sheetFormatPr['customHeight']) && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
                                 $docSheet->getDefaultRowDimension()->setRowHeight((double) $xmlSheet->sheetFormatPr['defaultRowHeight']);
                             }
                             if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
                                 $docSheet->getDefaultColumnDimension()->setWidth((double) $xmlSheet->sheetFormatPr['defaultColWidth']);
                             }
                             if (isset($xmlSheet->sheetFormatPr['zeroHeight']) && (string) $xmlSheet->sheetFormatPr['zeroHeight'] == '1') {
                                 $docSheet->getDefaultRowDimension()->setzeroHeight(true);
                             }
                         }
                         if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
                             foreach ($xmlSheet->cols->col as $col) {
                                 for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
                                     if ($col["style"] && !$this->_readDataOnly) {
                                         $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"]));
                                     }
                                     if (self::boolean($col["bestFit"])) {
                                         //$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(TRUE);
                                     }
                                     if (self::boolean($col["hidden"])) {
                                         $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setVisible(FALSE);
                                     }
                                     if (self::boolean($col["collapsed"])) {
                                         $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setCollapsed(TRUE);
                                     }
                                     if ($col["outlineLevel"] > 0) {
                                         $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
                                     }
                                     $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));
                                     if (intval($col["max"]) == 16384) {
                                         break;
                                     }
                                 }
                             }
                         }
                         if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
                             if (self::boolean((string) $xmlSheet->printOptions['gridLinesSet'])) {
                                 $docSheet->setShowGridlines(TRUE);
                             }
                             if (self::boolean((string) $xmlSheet->printOptions['gridLines'])) {
                                 $docSheet->setPrintGridlines(TRUE);
                             }
                             if (self::boolean((string) $xmlSheet->printOptions['horizontalCentered'])) {
                                 $docSheet->getPageSetup()->setHorizontalCentered(TRUE);
                             }
                             if (self::boolean((string) $xmlSheet->printOptions['verticalCentered'])) {
                                 $docSheet->getPageSetup()->setVerticalCentered(TRUE);
                             }
                         }
                         if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
                             foreach ($xmlSheet->sheetData->row as $row) {
                                 if ($row["ht"] && !$this->_readDataOnly) {
                                     $docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
                                 }
                                 if (self::boolean($row["hidden"]) && !$this->_readDataOnly) {
                                     $docSheet->getRowDimension(intval($row["r"]))->setVisible(FALSE);
                                 }
                                 if (self::boolean($row["collapsed"])) {
                                     $docSheet->getRowDimension(intval($row["r"]))->setCollapsed(TRUE);
                                 }
                                 if ($row["outlineLevel"] > 0) {
                                     $docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
                                 }
                                 if ($row["s"] && !$this->_readDataOnly) {
                                     $docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"]));
                                 }
                                 foreach ($row->c as $c) {
                                     $r = (string) $c["r"];
                                     $cellDataType = (string) $c["t"];
                                     $value = null;
                                     $calculatedValue = null;
                                     // Read cell?
                                     if ($this->getReadFilter() !== NULL) {
                                         $coordinates = PHPExcel_Cell::coordinateFromString($r);
                                         if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
                                             continue;
                                         }
                                     }
                                     //									echo '<b>Reading cell '.$coordinates[0].$coordinates[1].'</b><br />';
                                     //									print_r($c);
                                     //									echo '<br />';
                                     //									echo 'Cell Data Type is '.$cellDataType.': ';
                                     //
                                     // Read cell!
                                     switch ($cellDataType) {
                                         case "s":
                                             //											echo 'String<br />';
                                             if ((string) $c->v != '') {
                                                 $value = $sharedStrings[intval($c->v)];
                                                 if ($value instanceof PHPExcel_RichText) {
                                                     $value = clone $value;
                                                 }
                                             } else {
                                                 $value = '';
                                             }
                                             break;
                                         case "b":
                                             //											echo 'Boolean<br />';
                                             if (!isset($c->f)) {
                                                 $value = self::_castToBool($c);
                                             } else {
                                                 // Formula
                                                 $this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToBool');
                                                 if (isset($c->f['t'])) {
                                                     $att = array();
                                                     $att = $c->f;
                                                     $docSheet->getCell($r)->setFormulaAttributes($att);
                                                 }
                                                 //												echo '$calculatedValue = '.$calculatedValue.'<br />';
                                             }
                                             break;
                                         case "inlineStr":
                                             //											echo 'Inline String<br />';
                                             $value = $this->_parseRichText($c->is);
                                             break;
                                         case "e":
                                             //											echo 'Error<br />';
                                             if (!isset($c->f)) {
                                                 $value = self::_castToError($c);
                                             } else {
                                                 // Formula
                                                 $this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToError');
                                                 //												echo '$calculatedValue = '.$calculatedValue.'<br />';
                                             }
                                             break;
                                         default:
                                             //											echo 'Default<br />';
                                             if (!isset($c->f)) {
                                                 //												echo 'Not a Formula<br />';
                                                 $value = self::_castToString($c);
                                             } else {
                                                 //												echo 'Treat as Formula<br />';
                                                 // Formula
                                                 $this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToString');
                                                 //												echo '$calculatedValue = '.$calculatedValue.'<br />';
                                             }
                                             break;
                                     }
                                     //									echo 'Value is '.$value.'<br />';
                                     // Check for numeric values
                                     if (is_numeric($value) && $cellDataType != 's') {
                                         if ($value == (int) $value) {
                                             $value = (int) $value;
                                         } elseif ($value == (double) $value) {
                                             $value = (double) $value;
                                         } elseif ($value == (double) $value) {
                                             $value = (double) $value;
                                         }
                                     }
                                     // Rich text?
                                     if ($value instanceof PHPExcel_RichText && $this->_readDataOnly) {
                                         $value = $value->getPlainText();
                                     }
                                     $cell = $docSheet->getCell($r);
                                     // Assign value
                                     if ($cellDataType != '') {
                                         $cell->setValueExplicit($value, $cellDataType);
                                     } else {
                                         $cell->setValue($value);
                                     }
                                     if ($calculatedValue !== NULL) {
                                         $cell->setCalculatedValue($calculatedValue);
                                     }
                                     // Style information?
                                     if ($c["s"] && !$this->_readDataOnly) {
                                         // no style index means 0, it seems
                                         $cell->setXfIndex(isset($styles[intval($c["s"])]) ? intval($c["s"]) : 0);
                                     }
                                 }
                             }
                         }
                         $conditionals = array();
                         if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) {
                             foreach ($xmlSheet->conditionalFormatting as $conditional) {
                                 foreach ($conditional->cfRule as $cfRule) {
                                     if (((string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_NONE || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CELLIS || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_EXPRESSION) && isset($dxfs[intval($cfRule["dxfId"])])) {
                                         $conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule;
                                     }
                                 }
                             }
                             foreach ($conditionals as $ref => $cfRules) {
                                 ksort($cfRules);
                                 $conditionalStyles = array();
                                 foreach ($cfRules as $cfRule) {
                                     $objConditional = new PHPExcel_Style_Conditional();
                                     $objConditional->setConditionType((string) $cfRule["type"]);
                                     $objConditional->setOperatorType((string) $cfRule["operator"]);
                                     if ((string) $cfRule["text"] != '') {
                                         $objConditional->setText((string) $cfRule["text"]);
                                     }
                                     if (count($cfRule->formula) > 1) {
                                         foreach ($cfRule->formula as $formula) {
                                             $objConditional->addCondition((string) $formula);
                                         }
                                     } else {
                                         $objConditional->addCondition((string) $cfRule->formula);
                                     }
                                     $objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]);
                                     $conditionalStyles[] = $objConditional;
                                 }
                                 // Extract all cell references in $ref
                                 $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($ref);
                                 foreach ($aReferences as $reference) {
                                     $docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles);
                                 }
                             }
                         }
                         $aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells");
                         if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
                             foreach ($aKeys as $key) {
                                 $method = "set" . ucfirst($key);
                                 $docSheet->getProtection()->{$method}(self::boolean((string) $xmlSheet->sheetProtection[$key]));
                             }
                         }
                         if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
                             $docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], TRUE);
                             if ($xmlSheet->protectedRanges->protectedRange) {
                                 foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
                                     $docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
                                 }
                             }
                         }
                         if ($xmlSheet && $xmlSheet->autoFilter && !$this->_readDataOnly) {
                             $autoFilter = $docSheet->getAutoFilter();
                             $autoFilter->setRange((string) $xmlSheet->autoFilter["ref"]);
                             foreach ($xmlSheet->autoFilter->filterColumn as $filterColumn) {
                                 $column = $autoFilter->getColumnByOffset((int) $filterColumn["colId"]);
                                 //	Check for standard filters
                                 if ($filterColumn->filters) {
                                     $column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER);
                                     $filters = $filterColumn->filters;
                                     if (isset($filters["blank"]) && $filters["blank"] == 1) {
                                         $column->createRule()->setRule(NULL, '')->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER);
                                     }
                                     //	Standard filters are always an OR join, so no join rule needs to be set
                                     //	Entries can be either filter elements
                                     foreach ($filters->filter as $filterRule) {
                                         $column->createRule()->setRule(NULL, (string) $filterRule["val"])->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER);
                                     }
                                     //	Or Date Group elements
                                     foreach ($filters->dateGroupItem as $dateGroupItem) {
                                         $column->createRule()->setRule(NULL, array('year' => (string) $dateGroupItem["year"], 'month' => (string) $dateGroupItem["month"], 'day' => (string) $dateGroupItem["day"], 'hour' => (string) $dateGroupItem["hour"], 'minute' => (string) $dateGroupItem["minute"], 'second' => (string) $dateGroupItem["second"]), (string) $dateGroupItem["dateTimeGrouping"])->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP);
                                     }
                                 }
                                 //	Check for custom filters
                                 if ($filterColumn->customFilters) {
                                     $column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER);
                                     $customFilters = $filterColumn->customFilters;
                                     //	Custom filters can an AND or an OR join;
                                     //		and there should only ever be one or two entries
                                     if (isset($customFilters["and"]) && $customFilters["and"] == 1) {
                                         $column->setJoin(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND);
                                     }
                                     foreach ($customFilters->customFilter as $filterRule) {
                                         $column->createRule()->setRule((string) $filterRule["operator"], (string) $filterRule["val"])->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER);
                                     }
                                 }
                                 //	Check for dynamic filters
                                 if ($filterColumn->dynamicFilter) {
                                     $column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER);
                                     //	We should only ever have one dynamic filter
                                     foreach ($filterColumn->dynamicFilter as $filterRule) {
                                         $column->createRule()->setRule(NULL, (string) $filterRule["val"], (string) $filterRule["type"])->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER);
                                         if (isset($filterRule["val"])) {
                                             $column->setAttribute('val', (string) $filterRule["val"]);
                                         }
                                         if (isset($filterRule["maxVal"])) {
                                             $column->setAttribute('maxVal', (string) $filterRule["maxVal"]);
                                         }
                                     }
                                 }
                                 //	Check for dynamic filters
                                 if ($filterColumn->top10) {
                                     $column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER);
                                     //	We should only ever have one top10 filter
                                     foreach ($filterColumn->top10 as $filterRule) {
                                         $column->createRule()->setRule(isset($filterRule["percent"]) && $filterRule["percent"] == 1 ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE, (string) $filterRule["val"], isset($filterRule["top"]) && $filterRule["top"] == 1 ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM)->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_TOPTENFILTER);
                                     }
                                 }
                             }
                         }
                         if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
                             foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
                                 $mergeRef = (string) $mergeCell["ref"];
                                 if (strpos($mergeRef, ':') !== FALSE) {
                                     $docSheet->mergeCells((string) $mergeCell["ref"]);
                                 }
                             }
                         }
                         if ($xmlSheet && $xmlSheet->pageMargins && !$this->_readDataOnly) {
                             $docPageMargins = $docSheet->getPageMargins();
                             $docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"]));
                             $docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"]));
                             $docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"]));
                             $docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"]));
                             $docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"]));
                             $docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"]));
                         }
                         if ($xmlSheet && $xmlSheet->pageSetup && !$this->_readDataOnly) {
                             $docPageSetup = $docSheet->getPageSetup();
                             if (isset($xmlSheet->pageSetup["orientation"])) {
                                 $docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
                             }
                             if (isset($xmlSheet->pageSetup["paperSize"])) {
                                 $docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"]));
                             }
                             if (isset($xmlSheet->pageSetup["scale"])) {
                                 $docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), FALSE);
                             }
                             if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) {
                                 $docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), FALSE);
                             }
                             if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) {
                                 $docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), FALSE);
                             }
                             if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) && self::boolean((string) $xmlSheet->pageSetup["useFirstPageNumber"])) {
                                 $docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"]));
                             }
                         }
                         if ($xmlSheet && $xmlSheet->headerFooter && !$this->_readDataOnly) {
                             $docHeaderFooter = $docSheet->getHeaderFooter();
                             if (isset($xmlSheet->headerFooter["differentOddEven"]) && self::boolean((string) $xmlSheet->headerFooter["differentOddEven"])) {
                                 $docHeaderFooter->setDifferentOddEven(TRUE);
                             } else {
                                 $docHeaderFooter->setDifferentOddEven(FALSE);
                             }
                             if (isset($xmlSheet->headerFooter["differentFirst"]) && self::boolean((string) $xmlSheet->headerFooter["differentFirst"])) {
                                 $docHeaderFooter->setDifferentFirst(TRUE);
                             } else {
                                 $docHeaderFooter->setDifferentFirst(FALSE);
                             }
                             if (isset($xmlSheet->headerFooter["scaleWithDoc"]) && !self::boolean((string) $xmlSheet->headerFooter["scaleWithDoc"])) {
                                 $docHeaderFooter->setScaleWithDocument(FALSE);
                             } else {
                                 $docHeaderFooter->setScaleWithDocument(TRUE);
                             }
                             if (isset($xmlSheet->headerFooter["alignWithMargins"]) && !self::boolean((string) $xmlSheet->headerFooter["alignWithMargins"])) {
                                 $docHeaderFooter->setAlignWithMargins(FALSE);
                             } else {
                                 $docHeaderFooter->setAlignWithMargins(TRUE);
                             }
                             $docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
                             $docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
                             $docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
                             $docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
                             $docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
                             $docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
                         }
                         if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->_readDataOnly) {
                             foreach ($xmlSheet->rowBreaks->brk as $brk) {
                                 if ($brk["man"]) {
                                     $docSheet->setBreak("A{$brk['id']}", PHPExcel_Worksheet::BREAK_ROW);
                                 }
                             }
                         }
                         if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
                             foreach ($xmlSheet->colBreaks->brk as $brk) {
                                 if ($brk["man"]) {
                                     $docSheet->setBreak(PHPExcel_Cell::stringFromColumnIndex((string) $brk["id"]) . "1", PHPExcel_Worksheet::BREAK_COLUMN);
                                 }
                             }
                         }
                         if ($xmlSheet && $xmlSheet->dataValidations && !$this->_readDataOnly) {
                             foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
                                 // Uppercase coordinate
                                 $range = strtoupper($dataValidation["sqref"]);
                                 $rangeSet = explode(' ', $range);
                                 foreach ($rangeSet as $range) {
                                     $stRange = $docSheet->shrinkRangeToFit($range);
                                     // Extract all cell references in $range
                                     $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($stRange);
                                     foreach ($aReferences as $reference) {
                                         // Create validation
                                         $docValidation = $docSheet->getCell($reference)->getDataValidation();
                                         $docValidation->setType((string) $dataValidation["type"]);
                                         $docValidation->setErrorStyle((string) $dataValidation["errorStyle"]);
                                         $docValidation->setOperator((string) $dataValidation["operator"]);
                                         $docValidation->setAllowBlank($dataValidation["allowBlank"] != 0);
                                         $docValidation->setShowDropDown($dataValidation["showDropDown"] == 0);
                                         $docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0);
                                         $docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0);
                                         $docValidation->setErrorTitle((string) $dataValidation["errorTitle"]);
                                         $docValidation->setError((string) $dataValidation["error"]);
                                         $docValidation->setPromptTitle((string) $dataValidation["promptTitle"]);
                                         $docValidation->setPrompt((string) $dataValidation["prompt"]);
                                         $docValidation->setFormula1((string) $dataValidation->formula1);
                                         $docValidation->setFormula2((string) $dataValidation->formula2);
                                     }
                                 }
                             }
                         }
                         // Add hyperlinks
                         $hyperlinks = array();
                         if (!$this->_readDataOnly) {
                             // Locate hyperlink relations
                             if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
                                 $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
                                 //~ http://schemas.openxmlformats.org/package/2006/relationships");
                                 foreach ($relsWorksheet->Relationship as $ele) {
                                     if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
                                         $hyperlinks[(string) $ele["Id"]] = (string) $ele["Target"];
                                     }
                                 }
                             }
                             // Loop through hyperlinks
                             if ($xmlSheet && $xmlSheet->hyperlinks) {
                                 foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
                                     // Link url
                                     $linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
                                     foreach (PHPExcel_Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) {
                                         $cell = $docSheet->getCell($cellReference);
                                         if (isset($linkRel['id'])) {
                                             $hyperlinkUrl = $hyperlinks[(string) $linkRel['id']];
                                             if (isset($hyperlink['location'])) {
                                                 $hyperlinkUrl .= '#' . (string) $hyperlink['location'];
                                             }
                                             $cell->getHyperlink()->setUrl($hyperlinkUrl);
                                         } elseif (isset($hyperlink['location'])) {
                                             $cell->getHyperlink()->setUrl('sheet://' . (string) $hyperlink['location']);
                                         }
                                         // Tooltip
                                         if (isset($hyperlink['tooltip'])) {
                                             $cell->getHyperlink()->setTooltip((string) $hyperlink['tooltip']);
                                         }
                                     }
                                 }
                             }
                         }
                         // Add comments
                         $comments = array();
                         $vmlComments = array();
                         if (!$this->_readDataOnly) {
                             // Locate comment relations
                             if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
                                 $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
                                 //~ http://schemas.openxmlformats.org/package/2006/relationships");
                                 foreach ($relsWorksheet->Relationship as $ele) {
                                     if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") {
                                         $comments[(string) $ele["Id"]] = (string) $ele["Target"];
                                     }
                                     if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
                                         $vmlComments[(string) $ele["Id"]] = (string) $ele["Target"];
                                     }
                                 }
                             }
                             // Loop through comments
                             foreach ($comments as $relName => $relPath) {
                                 // Load comments file
                                 $relPath = PHPExcel_Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath);
                                 $commentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath));
                                 // Utility variables
                                 $authors = array();
                                 // Loop through authors
                                 foreach ($commentsFile->authors->author as $author) {
                                     $authors[] = (string) $author;
                                 }
                                 // Loop through contents
                                 foreach ($commentsFile->commentList->comment as $comment) {
                                     $docSheet->getComment((string) $comment['ref'])->setAuthor($authors[(string) $comment['authorId']]);
                                     $docSheet->getComment((string) $comment['ref'])->setText($this->_parseRichText($comment->text));
                                 }
                             }
                             // Loop through VML comments
                             foreach ($vmlComments as $relName => $relPath) {
                                 // Load VML comments file
                                 $relPath = PHPExcel_Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath);
                                 $vmlCommentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath));
                                 $vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
                                 $shapes = $vmlCommentsFile->xpath('//v:shape');
                                 foreach ($shapes as $shape) {
                                     $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
                                     if (isset($shape['style'])) {
                                         $style = (string) $shape['style'];
                                         $fillColor = strtoupper(substr((string) $shape['fillcolor'], 1));
                                         $column = null;
                                         $row = null;
                                         $clientData = $shape->xpath('.//x:ClientData');
                                         if (is_array($clientData) && !empty($clientData)) {
                                             $clientData = $clientData[0];
                                             if (isset($clientData['ObjectType']) && (string) $clientData['ObjectType'] == 'Note') {
                                                 $temp = $clientData->xpath('.//x:Row');
                                                 if (is_array($temp)) {
                                                     $row = $temp[0];
                                                 }
                                                 $temp = $clientData->xpath('.//x:Column');
                                                 if (is_array($temp)) {
                                                     $column = $temp[0];
                                                 }
                                             }
                                         }
                                         if ($column !== NULL && $row !== NULL) {
                                             // Set comment properties
                                             $comment = $docSheet->getCommentByColumnAndRow((string) $column, $row + 1);
                                             $comment->getFillColor()->setRGB($fillColor);
                                             // Parse style
                                             $styleArray = explode(';', str_replace(' ', '', $style));
                                             foreach ($styleArray as $stylePair) {
                                                 $stylePair = explode(':', $stylePair);
                                                 if ($stylePair[0] == 'margin-left') {
                                                     $comment->setMarginLeft($stylePair[1]);
                                                 }
                                                 if ($stylePair[0] == 'margin-top') {
                                                     $comment->setMarginTop($stylePair[1]);
                                                 }
                                                 if ($stylePair[0] == 'width') {
                                                     $comment->setWidth($stylePair[1]);
                                                 }
                                                 if ($stylePair[0] == 'height') {
                                                     $comment->setHeight($stylePair[1]);
                                                 }
                                                 if ($stylePair[0] == 'visibility') {
                                                     $comment->setVisible($stylePair[1] == 'visible');
                                                 }
                                             }
                                         }
                                     }
                                 }
                             }
                             // Header/footer images
                             if ($xmlSheet && $xmlSheet->legacyDrawingHF && !$this->_readDataOnly) {
                                 if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
                                     $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
                                     //~ http://schemas.openxmlformats.org/package/2006/relationships");
                                     $vmlRelationship = '';
                                     foreach ($relsWorksheet->Relationship as $ele) {
                                         if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
                                             $vmlRelationship = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]);
                                         }
                                     }
                                     if ($vmlRelationship != '') {
                                         // Fetch linked images
                                         $relsVML = simplexml_load_string($this->_getFromZipArchive($zip, dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels'));
                                         //~ http://schemas.openxmlformats.org/package/2006/relationships");
                                         $drawings = array();
                                         foreach ($relsVML->Relationship as $ele) {
                                             if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
                                                 $drawings[(string) $ele["Id"]] = self::dir_add($vmlRelationship, $ele["Target"]);
                                             }
                                         }
                                         // Fetch VML document
                                         $vmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $vmlRelationship));
                                         $vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
                                         $hfImages = array();
                                         $shapes = $vmlDrawing->xpath('//v:shape');
                                         foreach ($shapes as $shape) {
                                             $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
                                             $imageData = $shape->xpath('//v:imagedata');
                                             $imageData = $imageData[0];
                                             $imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office');
                                             $style = self::toCSSArray((string) $shape['style']);
                                             $hfImages[(string) $shape['id']] = new PHPExcel_Worksheet_HeaderFooterDrawing();
                                             if (isset($imageData['title'])) {
                                                 $hfImages[(string) $shape['id']]->setName((string) $imageData['title']);
                                             }
                                             $hfImages[(string) $shape['id']]->setPath("zip://" . PHPExcel_Shared_File::realpath($pFilename) . "#" . $drawings[(string) $imageData['relid']], false);
                                             $hfImages[(string) $shape['id']]->setResizeProportional(false);
                                             $hfImages[(string) $shape['id']]->setWidth($style['width']);
                                             $hfImages[(string) $shape['id']]->setHeight($style['height']);
                                             $hfImages[(string) $shape['id']]->setOffsetX($style['margin-left']);
                                             $hfImages[(string) $shape['id']]->setOffsetY($style['margin-top']);
                                             $hfImages[(string) $shape['id']]->setResizeProportional(true);
                                         }
                                         $docSheet->getHeaderFooter()->setImages($hfImages);
                                     }
                                 }
                             }
                         }
                         // TODO: Autoshapes from twoCellAnchors!
                         if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
                             $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
                             //~ http://schemas.openxmlformats.org/package/2006/relationships");
                             $drawings = array();
                             foreach ($relsWorksheet->Relationship as $ele) {
                                 if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") {
                                     $drawings[(string) $ele["Id"]] = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]);
                                 }
                             }
                             if ($xmlSheet->drawing && !$this->_readDataOnly) {
                                 foreach ($xmlSheet->drawing as $drawing) {
                                     $fileDrawing = $drawings[(string) self::array_item($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
                                     $relsDrawing = simplexml_load_string($this->_getFromZipArchive($zip, dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels"));
                                     //~ http://schemas.openxmlformats.org/package/2006/relationships");
                                     $images = array();
                                     if ($relsDrawing && $relsDrawing->Relationship) {
                                         foreach ($relsDrawing->Relationship as $ele) {
                                             if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
                                                 $images[(string) $ele["Id"]] = self::dir_add($fileDrawing, $ele["Target"]);
                                             } elseif ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart") {
                                                 if ($this->_includeCharts) {
                                                     $charts[self::dir_add($fileDrawing, $ele["Target"])] = array('id' => (string) $ele["Id"], 'sheet' => $docSheet->getTitle());
                                                 }
                                             }
                                         }
                                     }
                                     $xmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $fileDrawing))->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
                                     if ($xmlDrawing->oneCellAnchor) {
                                         foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) {
                                             if ($oneCellAnchor->pic->blipFill) {
                                                 $blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
                                                 $xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
                                                 $outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
                                                 $objDrawing = new PHPExcel_Worksheet_Drawing();
                                                 $objDrawing->setName((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
                                                 $objDrawing->setDescription((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
                                                 $objDrawing->setPath("zip://" . PHPExcel_Shared_File::realpath($pFilename) . "#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
                                                 $objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1));
                                                 $objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff));
                                                 $objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff));
                                                 $objDrawing->setResizeProportional(false);
                                                 $objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx")));
                                                 $objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy")));
                                                 if ($xfrm) {
                                                     $objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
                                                 }
                                                 if ($outerShdw) {
                                                     $shadow = $objDrawing->getShadow();
                                                     $shadow->setVisible(true);
                                                     $shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
                                                     $shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
                                                     $shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
                                                     $shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
                                                     $shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
                                                     $shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
                                                 }
                                                 $objDrawing->setWorksheet($docSheet);
                                             } else {
                                                 //	? Can charts be positioned with a oneCellAnchor ?
                                                 $coordinates = PHPExcel_Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1);
                                                 $offsetX = PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff);
                                                 $offsetY = PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff);
                                                 $width = PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx"));
                                                 $height = PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy"));
                                             }
                                         }
                                     }
                                     if ($xmlDrawing->twoCellAnchor) {
                                         foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) {
                                             if ($twoCellAnchor->pic->blipFill) {
                                                 $blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
                                                 $xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
                                                 $outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
                                                 $objDrawing = new PHPExcel_Worksheet_Drawing();
                                                 $objDrawing->setName((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
                                                 $objDrawing->setDescription((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
                                                 $objDrawing->setPath("zip://" . PHPExcel_Shared_File::realpath($pFilename) . "#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
                                                 $objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
                                                 $objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff));
                                                 $objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
                                                 $objDrawing->setResizeProportional(false);
                                                 $objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx")));
                                                 $objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy")));
                                                 if ($xfrm) {
                                                     $objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
                                                 }
                                                 if ($outerShdw) {
                                                     $shadow = $objDrawing->getShadow();
                                                     $shadow->setVisible(true);
                                                     $shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
                                                     $shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
                                                     $shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
                                                     $shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
                                                     $shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
                                                     $shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
                                                 }
                                                 $objDrawing->setWorksheet($docSheet);
                                             } elseif ($this->_includeCharts && $twoCellAnchor->graphicFrame) {
                                                 $fromCoordinate = PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1);
                                                 $fromOffsetX = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff);
                                                 $fromOffsetY = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff);
                                                 $toCoordinate = PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->to->col) . ($twoCellAnchor->to->row + 1);
                                                 $toOffsetX = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->to->colOff);
                                                 $toOffsetY = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->to->rowOff);
                                                 $graphic = $twoCellAnchor->graphicFrame->children("http://schemas.openxmlformats.org/drawingml/2006/main")->graphic;
                                                 $chartRef = $graphic->graphicData->children("http://schemas.openxmlformats.org/drawingml/2006/chart")->chart;
                                                 $thisChart = (string) $chartRef->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                                                 $chartDetails[$docSheet->getTitle() . '!' . $thisChart] = array('fromCoordinate' => $fromCoordinate, 'fromOffsetX' => $fromOffsetX, 'fromOffsetY' => $fromOffsetY, 'toCoordinate' => $toCoordinate, 'toOffsetX' => $toOffsetX, 'toOffsetY' => $toOffsetY, 'worksheetTitle' => $docSheet->getTitle());
                                             }
                                         }
                                     }
                                 }
                             }
                         }
                         // Loop through definedNames
                         if ($xmlWorkbook->definedNames) {
                             foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
                                 // Extract range
                                 $extractedRange = (string) $definedName;
                                 $extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange);
                                 if (($spos = strpos($extractedRange, '!')) !== false) {
                                     $extractedRange = substr($extractedRange, 0, $spos) . str_replace('$', '', substr($extractedRange, $spos));
                                 } else {
                                     $extractedRange = str_replace('$', '', $extractedRange);
                                 }
                                 // Valid range?
                                 if (stripos((string) $definedName, '#REF!') !== FALSE || $extractedRange == '') {
                                     continue;
                                 }
                                 // Some definedNames are only applicable if we are on the same sheet...
                                 if ((string) $definedName['localSheetId'] != '' && (string) $definedName['localSheetId'] == $sheetId) {
                                     // Switch on type
                                     switch ((string) $definedName['name']) {
                                         case '_xlnm._FilterDatabase':
                                             if ((string) $definedName['hidden'] !== '1') {
                                                 $docSheet->getAutoFilter()->setRange($extractedRange);
                                             }
                                             break;
                                         case '_xlnm.Print_Titles':
                                             // Split $extractedRange
                                             $extractedRange = explode(',', $extractedRange);
                                             // Set print titles
                                             foreach ($extractedRange as $range) {
                                                 $matches = array();
                                                 // check for repeating columns, e g. 'A:A' or 'A:D'
                                                 if (preg_match('/^([A-Z]+)\\:([A-Z]+)$/', $range, $matches)) {
                                                     $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($matches[1], $matches[2]));
                                                 } elseif (preg_match('/^(\\d+)\\:(\\d+)$/', $range, $matches)) {
                                                     $docSheet->getPageSetup()->setRowsToRepeatAtTop(array($matches[1], $matches[2]));
                                                 }
                                             }
                                             break;
                                         case '_xlnm.Print_Area':
                                             $rangeSets = explode(',', $extractedRange);
                                             // FIXME: what if sheetname contains comma?
                                             $newRangeSets = array();
                                             foreach ($rangeSets as $rangeSet) {
                                                 $range = explode('!', $rangeSet);
                                                 // FIXME: what if sheetname contains exclamation mark?
                                                 $rangeSet = isset($range[1]) ? $range[1] : $range[0];
                                                 if (strpos($rangeSet, ':') === FALSE) {
                                                     $rangeSet = $rangeSet . ':' . $rangeSet;
                                                 }
                                                 $newRangeSets[] = str_replace('$', '', $rangeSet);
                                             }
                                             $docSheet->getPageSetup()->setPrintArea(implode(',', $newRangeSets));
                                             break;
                                         default:
                                             break;
                                     }
                                 }
                             }
                         }
                         // Next sheet id
                         ++$sheetId;
                     }
                     // Loop through definedNames
                     if ($xmlWorkbook->definedNames) {
                         foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
                             // Extract range
                             $extractedRange = (string) $definedName;
                             $extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange);
                             if (($spos = strpos($extractedRange, '!')) !== false) {
                                 $extractedRange = substr($extractedRange, 0, $spos) . str_replace('$', '', substr($extractedRange, $spos));
                             } else {
                                 $extractedRange = str_replace('$', '', $extractedRange);
                             }
                             // Valid range?
                             if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') {
                                 continue;
                             }
                             // Some definedNames are only applicable if we are on the same sheet...
                             if ((string) $definedName['localSheetId'] != '') {
                                 // Local defined name
                                 // Switch on type
                                 switch ((string) $definedName['name']) {
                                     case '_xlnm._FilterDatabase':
                                     case '_xlnm.Print_Titles':
                                     case '_xlnm.Print_Area':
                                         break;
                                     default:
                                         if ($mapSheetId[(int) $definedName['localSheetId']] !== null) {
                                             $range = explode('!', (string) $definedName);
                                             if (count($range) == 2) {
                                                 $range[0] = str_replace("''", "'", $range[0]);
                                                 $range[0] = str_replace("'", "", $range[0]);
                                                 if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) {
                                                     $extractedRange = str_replace('$', '', $range[1]);
                                                     $scope = $docSheet->getParent()->getSheet($mapSheetId[(int) $definedName['localSheetId']]);
                                                     $excel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $worksheet, $extractedRange, true, $scope));
                                                 }
                                             }
                                         }
                                         break;
                                 }
                             } else {
                                 if (!isset($definedName['localSheetId'])) {
                                     // "Global" definedNames
                                     $locatedSheet = null;
                                     $extractedSheetName = '';
                                     if (strpos((string) $definedName, '!') !== false) {
                                         // Extract sheet name
                                         $extractedSheetName = PHPExcel_Worksheet::extractSheetTitle((string) $definedName, true);
                                         $extractedSheetName = $extractedSheetName[0];
                                         // Locate sheet
                                         $locatedSheet = $excel->getSheetByName($extractedSheetName);
                                         // Modify range
                                         $range = explode('!', $extractedRange);
                                         $extractedRange = isset($range[1]) ? $range[1] : $range[0];
                                     }
                                     if ($locatedSheet !== NULL) {
                                         $excel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $locatedSheet, $extractedRange, false));
                                     }
                                 }
                             }
                         }
                     }
                 }
                 if (!$this->_readDataOnly || !empty($this->_loadSheetsOnly)) {
                     // active sheet index
                     $activeTab = intval($xmlWorkbook->bookViews->workbookView["activeTab"]);
                     // refers to old sheet index
                     // keep active sheet index if sheet is still loaded, else first sheet is set as the active
                     if (isset($mapSheetId[$activeTab]) && $mapSheetId[$activeTab] !== null) {
                         $excel->setActiveSheetIndex($mapSheetId[$activeTab]);
                     } else {
                         if ($excel->getSheetCount() == 0) {
                             $excel->createSheet();
                         }
                         $excel->setActiveSheetIndex(0);
                     }
                 }
                 break;
         }
     }
     if (!$this->_readDataOnly) {
         $contentTypes = simplexml_load_string($this->_getFromZipArchive($zip, "[Content_Types].xml"));
         foreach ($contentTypes->Override as $contentType) {
             switch ($contentType["ContentType"]) {
                 case "application/vnd.openxmlformats-officedocument.drawingml.chart+xml":
                     if ($this->_includeCharts) {
                         $chartEntryRef = ltrim($contentType['PartName'], '/');
                         $chartElements = simplexml_load_string($this->_getFromZipArchive($zip, $chartEntryRef));
                         $objChart = PHPExcel_Reader_Excel2007_Chart::readChart($chartElements, basename($chartEntryRef, '.xml'));
                         //							echo 'Chart ',$chartEntryRef,'<br />';
                         //							var_dump($charts[$chartEntryRef]);
                         //
                         if (isset($charts[$chartEntryRef])) {
                             $chartPositionRef = $charts[$chartEntryRef]['sheet'] . '!' . $charts[$chartEntryRef]['id'];
                             //								echo 'Position Ref ',$chartPositionRef,'<br />';
                             if (isset($chartDetails[$chartPositionRef])) {
                                 //									var_dump($chartDetails[$chartPositionRef]);
                                 $excel->getSheetByName($charts[$chartEntryRef]['sheet'])->addChart($objChart);
                                 $objChart->setWorksheet($excel->getSheetByName($charts[$chartEntryRef]['sheet']));
                                 $objChart->setTopLeftPosition($chartDetails[$chartPositionRef]['fromCoordinate'], $chartDetails[$chartPositionRef]['fromOffsetX'], $chartDetails[$chartPositionRef]['fromOffsetY']);
                                 $objChart->setBottomRightPosition($chartDetails[$chartPositionRef]['toCoordinate'], $chartDetails[$chartPositionRef]['toOffsetX'], $chartDetails[$chartPositionRef]['toOffsetY']);
                             }
                         }
                     }
             }
         }
     }
     $zip->close();
     return $excel;
 }
Example #27
0
  function backup() {
//   $sql = "show tables;";
//   $tables = $this->db->query_flat($sql);
   $tables = array('objects', 'customers', 'users');
   
//   ajax_echo_r ($tables);
   
   $timestamp = date("Y-m-d-H-i-s");
   
   $dir = "data/backups";
   mkdirr(getrootdirsrv().$dir);
   
   $filename        = getrootdirsrv().$dir."/izum-rel-".$timestamp.".xls";
   $filename_client = getrootdir()   .$dir."/izum-rel-".$timestamp.".xls";
   
   $output = new PHPExcel;                               // Create new PHPExcel object
   $output->getProperties()->setCreator("Izum by Creative Force")
          ->setLastModifiedBy("Izum by Creative Force")
          ->setTitle("Izum Backup File - ".date("Y-m-d H:i:s"))
          ->setSubject("Office 2007 XLS Test Document")
          ->setDescription("Test document for Office 2007 XLS, generated using PHP classes.")
          ->setKeywords("Creative Force Izum backup")
          ->setCategory("Test result file");
   
   $i = 0;
   $n = 1;
   $si = 0;
   
   $srcrow = array();
   
   foreach ($tables as $tableName) {
//    $tableName  = 'accounts';
//    ajax_echo_r ($tableName);
    if (!in_array($tableName, array('visits', 'events'))) {
     $sql = "SELECT * FROM `".$tableName."` ORDER BY `DateAdded`;";
     $ret = $this->db->query_first($sql);
     $col=0;
     
     if ($si) {
      // Add new sheet
      $output->createSheet($si)->setTitle($tableName); //Setting index when creating
     } else {
      $output->setActiveSheetIndex($si)->setTitle($tableName);
     }
     
     $colnames = getColNames();
     
     foreach (array_keys((array)$ret) as $itm) {
  //   for ($col=0; $col<$colCount; $col++) {
  //    echo $itm;
      if ($colnames[$itm]) {
       $itm = $colnames[$itm];
      } else {
//       echo $itm."-";
      }
      
      $output->setActiveSheetIndex($si)->setCellValueByColumnAndRow($col, 1, $itm);
  //    $srcrow[] = $xls->getCellByColumnAndRow($col,2)->getValue();
      $col++;
     }
     
//     $colnames = getColNames();
     
     $i=2;
     
     $sql = "SELECT * FROM `".$tableName."`;";
     $ret = $this->db->query($sql);
     
     $subtables   = array('districts' , 'customersubtypes' , 'methodsofpayment' , 'sources' , 'users' , 'directions' , 'housetypes' , 'markets' , 'mediators' , 'overlappingtypes' , 'layouttypes' , 'toilettypes' , 'conditions' , 'finishings' , 'floorsurfaces' , 'stovetypes' , 'doorstypes' , 'wallssurfaces' , 'wallsmaterials' , 'bathroomequipments' , 'windowstypes' , 'rightssources' , 'rightstransmissions' );
     $subcolumns  = array('DistrictID', 'CustomerSubtypeID', 'MethodOfPaymentID', 'SourceID', 'UserID', 'DirectionID', 'HouseTypeID', 'MarketID', 'MediatorID', 'OverlappingTypeID', 'LayoutTypeID', 'ToiletTypeID', 'ConditionID', 'FinishingID', 'FloorSurfaceID', 'StoveTypeID', 'DoorsTypeID', 'WallsSurfaceID', 'WallsMaterialID', 'BathroomEquipmentID', 'WindowsTypeID', 'RightsSourceID', 'RightsTransmissionID');
     
     $ret = $this->fillSubtables($ret, $subtables, $subcolumns);
     
//     ajax_echo_r ($ret);
//     return false;
     
     foreach ($ret as $itm) {
      $col=0;
      
      $thisrecord = $this->cache[(string)$id];
      
      foreach ($itm as $c) {
       $output->setActiveSheetIndex($si)->setCellValueByColumnAndRow($col, $i, $c);
       $col++;
      }
      
      $i++;
     }
    }
    $si++;
   }
   
   $output->setActiveSheetIndex(0);
   
   $objWriter = PHPExcel_IOFactory::createWriter($output, 'Excel5');
   $objWriter->save($filename);
   
   return ($filename_client);
   
   
   
   /*
   $tableName  = 'accounts';
   $sql = "SELECT * FROM `".$tableName."`;";
   $ret = $this->db->query_first($sql);
   ajax_echo_r ($ret);
   
   $buf = "INSERT INTO `` ";
   foreach (array_keys((array)$ret as $itm) {
    
    
   }
   
//   file_put_contents ($backupFile, implode())."\n", FILE_APPEND);
   
   $sql = "SELECT * FROM `".$tableName."`;";
   $r = $this->db->query($sql);
   ajax_echo_r ($r);
   */
   
  }
Example #28
0
 /**
  * Loads PHPExcel from file into PHPExcel instance
  *
  * @param 	string 		$pFilename
  * @param	PHPExcel	$objPHPExcel
  * @return 	PHPExcel
  * @throws 	Exception
  */
 public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
 {
     // Check if file exists
     if (!file_exists($pFilename)) {
         throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
     }
     // Create new PHPExcel
     while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) {
         $objPHPExcel->createSheet();
     }
     $objPHPExcel->setActiveSheetIndex($this->_sheetIndex);
     //	Create a new DOM object
     $dom = new domDocument();
     //	Load the HTML file into the DOM object
     $loaded = $dom->loadHTMLFile($pFilename);
     if ($loaded === false) {
         throw new Exception('Failed to load ', $pFilename, ' as a DOM Document');
     }
     //	Discard white space
     $dom->preserveWhiteSpace = false;
     $row = 0;
     $column = 'A';
     $content = '';
     $this->_processDomElement($dom, $objPHPExcel->getActiveSheet(), $row, $column, $content);
     echo '<hr />';
     var_dump($this->_dataArray);
     // Return
     return $objPHPExcel;
 }
 /**
  * Set data to active sheet and create new one.
  * 
  * @param array $data
  * @param array $title
  * @param PHPExcel_Chart $charts
  */
 public function newSheet($data, $title = array(), $charts = null)
 {
     $this->setData($data, $title);
     $this->objPHPExcel->createSheet();
     $this->objPHPExcel->setActiveSheetIndex($this->objPHPExcel->getSheetCount() - 1);
 }
Example #30
0
 /**
  * Loads PHPExcel from file into PHPExcel instance
  *
  * @param 	string 		$pFilename
  * @param	PHPExcel	$objPHPExcel
  * @return 	PHPExcel
  * @throws 	PHPExcel_Reader_Exception
  */
 public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
 {
     // Check if file exists
     if (!file_exists($pFilename)) {
         throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
     }
     $timezoneObj = new DateTimeZone('Europe/London');
     $GMT = new DateTimeZone('UTC');
     $gFileData = $this->_gzfileGetContents($pFilename);
     //		echo '<pre>';
     //		echo htmlentities($gFileData,ENT_QUOTES,'UTF-8');
     //		echo '</pre><hr />';
     //
     $xml = simplexml_load_string($gFileData);
     $namespacesMeta = $xml->getNamespaces(true);
     //		var_dump($namespacesMeta);
     //
     $gnmXML = $xml->children($namespacesMeta['gnm']);
     $docProps = $objPHPExcel->getProperties();
     //	Document Properties are held differently, depending on the version of Gnumeric
     if (isset($namespacesMeta['office'])) {
         $officeXML = $xml->children($namespacesMeta['office']);
         $officeDocXML = $officeXML->{'document-meta'};
         $officeDocMetaXML = $officeDocXML->meta;
         foreach ($officeDocMetaXML as $officePropertyData) {
             $officePropertyDC = array();
             if (isset($namespacesMeta['dc'])) {
                 $officePropertyDC = $officePropertyData->children($namespacesMeta['dc']);
             }
             foreach ($officePropertyDC as $propertyName => $propertyValue) {
                 $propertyValue = (string) $propertyValue;
                 switch ($propertyName) {
                     case 'title':
                         $docProps->setTitle(trim($propertyValue));
                         break;
                     case 'subject':
                         $docProps->setSubject(trim($propertyValue));
                         break;
                     case 'creator':
                         $docProps->setCreator(trim($propertyValue));
                         $docProps->setLastModifiedBy(trim($propertyValue));
                         break;
                     case 'date':
                         $creationDate = strtotime(trim($propertyValue));
                         $docProps->setCreated($creationDate);
                         $docProps->setModified($creationDate);
                         break;
                     case 'description':
                         $docProps->setDescription(trim($propertyValue));
                         break;
                 }
             }
             $officePropertyMeta = array();
             if (isset($namespacesMeta['meta'])) {
                 $officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']);
             }
             foreach ($officePropertyMeta as $propertyName => $propertyValue) {
                 $attributes = $propertyValue->attributes($namespacesMeta['meta']);
                 $propertyValue = (string) $propertyValue;
                 switch ($propertyName) {
                     case 'keyword':
                         $docProps->setKeywords(trim($propertyValue));
                         break;
                     case 'initial-creator':
                         $docProps->setCreator(trim($propertyValue));
                         $docProps->setLastModifiedBy(trim($propertyValue));
                         break;
                     case 'creation-date':
                         $creationDate = strtotime(trim($propertyValue));
                         $docProps->setCreated($creationDate);
                         $docProps->setModified($creationDate);
                         break;
                     case 'user-defined':
                         list(, $attrName) = explode(':', $attributes['name']);
                         switch ($attrName) {
                             case 'publisher':
                                 $docProps->setCompany(trim($propertyValue));
                                 break;
                             case 'category':
                                 $docProps->setCategory(trim($propertyValue));
                                 break;
                             case 'manager':
                                 $docProps->setManager(trim($propertyValue));
                                 break;
                         }
                         break;
                 }
             }
         }
     } elseif (isset($gnmXML->Summary)) {
         foreach ($gnmXML->Summary->Item as $summaryItem) {
             $propertyName = $summaryItem->name;
             $propertyValue = $summaryItem->{'val-string'};
             switch ($propertyName) {
                 case 'title':
                     $docProps->setTitle(trim($propertyValue));
                     break;
                 case 'comments':
                     $docProps->setDescription(trim($propertyValue));
                     break;
                 case 'keywords':
                     $docProps->setKeywords(trim($propertyValue));
                     break;
                 case 'category':
                     $docProps->setCategory(trim($propertyValue));
                     break;
                 case 'manager':
                     $docProps->setManager(trim($propertyValue));
                     break;
                 case 'author':
                     $docProps->setCreator(trim($propertyValue));
                     $docProps->setLastModifiedBy(trim($propertyValue));
                     break;
                 case 'company':
                     $docProps->setCompany(trim($propertyValue));
                     break;
             }
         }
     }
     $worksheetID = 0;
     foreach ($gnmXML->Sheets->Sheet as $sheet) {
         $worksheetName = (string) $sheet->Name;
         //			echo '<b>Worksheet: ',$worksheetName,'</b><br />';
         if (isset($this->_loadSheetsOnly) && !in_array($worksheetName, $this->_loadSheetsOnly)) {
             continue;
         }
         $maxRow = $maxCol = 0;
         // Create new Worksheet
         $objPHPExcel->createSheet();
         $objPHPExcel->setActiveSheetIndex($worksheetID);
         //	Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in formula
         //		cells... during the load, all formulae should be correct, and we're simply bringing the worksheet
         //		name in line with the formula, not the reverse
         $objPHPExcel->getActiveSheet()->setTitle($worksheetName, false);
         if (!$this->_readDataOnly && isset($sheet->PrintInformation)) {
             if (isset($sheet->PrintInformation->Margins)) {
                 foreach ($sheet->PrintInformation->Margins->children('gnm', TRUE) as $key => $margin) {
                     $marginAttributes = $margin->attributes();
                     $marginSize = 72 / 100;
                     //	Default
                     switch ($marginAttributes['PrefUnit']) {
                         case 'mm':
                             $marginSize = intval($marginAttributes['Points']) / 100;
                             break;
                     }
                     switch ($key) {
                         case 'top':
                             $objPHPExcel->getActiveSheet()->getPageMargins()->setTop($marginSize);
                             break;
                         case 'bottom':
                             $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom($marginSize);
                             break;
                         case 'left':
                             $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft($marginSize);
                             break;
                         case 'right':
                             $objPHPExcel->getActiveSheet()->getPageMargins()->setRight($marginSize);
                             break;
                         case 'header':
                             $objPHPExcel->getActiveSheet()->getPageMargins()->setHeader($marginSize);
                             break;
                         case 'footer':
                             $objPHPExcel->getActiveSheet()->getPageMargins()->setFooter($marginSize);
                             break;
                     }
                 }
             }
         }
         foreach ($sheet->Cells->Cell as $cell) {
             $cellAttributes = $cell->attributes();
             $row = (int) $cellAttributes->Row + 1;
             $column = (int) $cellAttributes->Col;
             if ($row > $maxRow) {
                 $maxRow = $row;
             }
             if ($column > $maxCol) {
                 $maxCol = $column;
             }
             $column = PHPExcel_Cell::stringFromColumnIndex($column);
             // Read cell?
             if ($this->getReadFilter() !== NULL) {
                 if (!$this->getReadFilter()->readCell($column, $row, $worksheetName)) {
                     continue;
                 }
             }
             $ValueType = $cellAttributes->ValueType;
             $ExprID = (string) $cellAttributes->ExprID;
             //				echo 'Cell ',$column,$row,'<br />';
             //				echo 'Type is ',$ValueType,'<br />';
             //				echo 'Value is ',$cell,'<br />';
             $type = PHPExcel_Cell_DataType::TYPE_FORMULA;
             if ($ExprID > '') {
                 if ((string) $cell > '') {
                     $this->_expressions[$ExprID] = array('column' => $cellAttributes->Col, 'row' => $cellAttributes->Row, 'formula' => (string) $cell);
                     //						echo 'NEW EXPRESSION ',$ExprID,'<br />';
                 } else {
                     $expression = $this->_expressions[$ExprID];
                     $cell = $this->_referenceHelper->updateFormulaReferences($expression['formula'], 'A1', $cellAttributes->Col - $expression['column'], $cellAttributes->Row - $expression['row'], $worksheetName);
                     //						echo 'SHARED EXPRESSION ',$ExprID,'<br />';
                     //						echo 'New Value is ',$cell,'<br />';
                 }
                 $type = PHPExcel_Cell_DataType::TYPE_FORMULA;
             } else {
                 switch ($ValueType) {
                     case '10':
                         //	NULL
                         $type = PHPExcel_Cell_DataType::TYPE_NULL;
                         break;
                     case '20':
                         //	Boolean
                         $type = PHPExcel_Cell_DataType::TYPE_BOOL;
                         $cell = $cell == 'TRUE' ? True : False;
                         break;
                     case '30':
                         //	Integer
                         $cell = intval($cell);
                     case '40':
                         //	Float
                         $type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
                         break;
                     case '50':
                         //	Error
                         $type = PHPExcel_Cell_DataType::TYPE_ERROR;
                         break;
                     case '60':
                         //	String
                         $type = PHPExcel_Cell_DataType::TYPE_STRING;
                         break;
                     case '70':
                         //	Cell Range
                     //	Cell Range
                     case '80':
                         //	Array
                 }
             }
             $objPHPExcel->getActiveSheet()->getCell($column . $row)->setValueExplicit($cell, $type);
         }
         if (!$this->_readDataOnly && isset($sheet->Objects)) {
             foreach ($sheet->Objects->children('gnm', TRUE) as $key => $comment) {
                 $commentAttributes = $comment->attributes();
                 //	Only comment objects are handled at the moment
                 if ($commentAttributes->Text) {
                     $objPHPExcel->getActiveSheet()->getComment((string) $commentAttributes->ObjectBound)->setAuthor((string) $commentAttributes->Author)->setText($this->_parseRichText((string) $commentAttributes->Text));
                 }
             }
         }
         //			echo '$maxCol=',$maxCol,'; $maxRow=',$maxRow,'<br />';
         //
         foreach ($sheet->Styles->StyleRegion as $styleRegion) {
             $styleAttributes = $styleRegion->attributes();
             if ($styleAttributes['startRow'] <= $maxRow && $styleAttributes['startCol'] <= $maxCol) {
                 $startColumn = PHPExcel_Cell::stringFromColumnIndex((int) $styleAttributes['startCol']);
                 $startRow = $styleAttributes['startRow'] + 1;
                 $endColumn = $styleAttributes['endCol'] > $maxCol ? $maxCol : (int) $styleAttributes['endCol'];
                 $endColumn = PHPExcel_Cell::stringFromColumnIndex($endColumn);
                 $endRow = $styleAttributes['endRow'] > $maxRow ? $maxRow : $styleAttributes['endRow'];
                 $endRow += 1;
                 $cellRange = $startColumn . $startRow . ':' . $endColumn . $endRow;
                 //					echo $cellRange,'<br />';
                 $styleAttributes = $styleRegion->Style->attributes();
                 //					var_dump($styleAttributes);
                 //					echo '<br />';
                 //	We still set the number format mask for date/time values, even if _readDataOnly is true
                 if (!$this->_readDataOnly || PHPExcel_Shared_Date::isDateTimeFormatCode((string) $styleAttributes['Format'])) {
                     $styleArray = array();
                     $styleArray['numberformat']['code'] = (string) $styleAttributes['Format'];
                     //	If _readDataOnly is false, we set all formatting information
                     if (!$this->_readDataOnly) {
                         switch ($styleAttributes['HAlign']) {
                             case '1':
                                 $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_GENERAL;
                                 break;
                             case '2':
                                 $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
                                 break;
                             case '4':
                                 $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
                                 break;
                             case '8':
                                 $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
                                 break;
                             case '16':
                             case '64':
                                 $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS;
                                 break;
                             case '32':
                                 $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY;
                                 break;
                         }
                         switch ($styleAttributes['VAlign']) {
                             case '1':
                                 $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_TOP;
                                 break;
                             case '2':
                                 $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_BOTTOM;
                                 break;
                             case '4':
                                 $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_CENTER;
                                 break;
                             case '8':
                                 $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_JUSTIFY;
                                 break;
                         }
                         $styleArray['alignment']['wrap'] = $styleAttributes['WrapText'] == '1' ? True : False;
                         $styleArray['alignment']['shrinkToFit'] = $styleAttributes['ShrinkToFit'] == '1' ? True : False;
                         $styleArray['alignment']['indent'] = intval($styleAttributes["Indent"]) > 0 ? $styleAttributes["indent"] : 0;
                         $RGB = self::_parseGnumericColour($styleAttributes["Fore"]);
                         $styleArray['font']['color']['rgb'] = $RGB;
                         $RGB = self::_parseGnumericColour($styleAttributes["Back"]);
                         $shade = $styleAttributes["Shade"];
                         if ($RGB != '000000' || $shade != '0') {
                             $styleArray['fill']['color']['rgb'] = $styleArray['fill']['startcolor']['rgb'] = $RGB;
                             $RGB2 = self::_parseGnumericColour($styleAttributes["PatternColor"]);
                             $styleArray['fill']['endcolor']['rgb'] = $RGB2;
                             switch ($shade) {
                                 case '1':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID;
                                     break;
                                 case '2':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR;
                                     break;
                                 case '3':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_GRADIENT_PATH;
                                     break;
                                 case '4':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN;
                                     break;
                                 case '5':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY;
                                     break;
                                 case '6':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID;
                                     break;
                                 case '7':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL;
                                     break;
                                 case '8':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS;
                                     break;
                                 case '9':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKUP;
                                     break;
                                 case '10':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL;
                                     break;
                                 case '11':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625;
                                     break;
                                 case '12':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_GRAY125;
                                     break;
                                 case '13':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN;
                                     break;
                                 case '14':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY;
                                     break;
                                 case '15':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID;
                                     break;
                                 case '16':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL;
                                     break;
                                 case '17':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS;
                                     break;
                                 case '18':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP;
                                     break;
                                 case '19':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL;
                                     break;
                                 case '20':
                                     $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY;
                                     break;
                             }
                         }
                         $fontAttributes = $styleRegion->Style->Font->attributes();
                         //							var_dump($fontAttributes);
                         //							echo '<br />';
                         $styleArray['font']['name'] = (string) $styleRegion->Style->Font;
                         $styleArray['font']['size'] = intval($fontAttributes['Unit']);
                         $styleArray['font']['bold'] = $fontAttributes['Bold'] == '1' ? True : False;
                         $styleArray['font']['italic'] = $fontAttributes['Italic'] == '1' ? True : False;
                         $styleArray['font']['strike'] = $fontAttributes['StrikeThrough'] == '1' ? True : False;
                         switch ($fontAttributes['Underline']) {
                             case '1':
                                 $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_SINGLE;
                                 break;
                             case '2':
                                 $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_DOUBLE;
                                 break;
                             case '3':
                                 $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING;
                                 break;
                             case '4':
                                 $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING;
                                 break;
                             default:
                                 $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_NONE;
                                 break;
                         }
                         switch ($fontAttributes['Script']) {
                             case '1':
                                 $styleArray['font']['superScript'] = True;
                                 break;
                             case '-1':
                                 $styleArray['font']['subScript'] = True;
                                 break;
                         }
                         if (isset($styleRegion->Style->StyleBorder)) {
                             if (isset($styleRegion->Style->StyleBorder->Top)) {
                                 $styleArray['borders']['top'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Top->attributes());
                             }
                             if (isset($styleRegion->Style->StyleBorder->Bottom)) {
                                 $styleArray['borders']['bottom'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Bottom->attributes());
                             }
                             if (isset($styleRegion->Style->StyleBorder->Left)) {
                                 $styleArray['borders']['left'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Left->attributes());
                             }
                             if (isset($styleRegion->Style->StyleBorder->Right)) {
                                 $styleArray['borders']['right'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Right->attributes());
                             }
                             if (isset($styleRegion->Style->StyleBorder->Diagonal) && isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'})) {
                                 $styleArray['borders']['diagonal'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes());
                                 $styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders::DIAGONAL_BOTH;
                             } elseif (isset($styleRegion->Style->StyleBorder->Diagonal)) {
                                 $styleArray['borders']['diagonal'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes());
                                 $styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders::DIAGONAL_UP;
                             } elseif (isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'})) {
                                 $styleArray['borders']['diagonal'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->{'Rev-Diagonal'}->attributes());
                                 $styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders::DIAGONAL_DOWN;
                             }
                         }
                         if (isset($styleRegion->Style->HyperLink)) {
                             //	TO DO
                             $hyperlink = $styleRegion->Style->HyperLink->attributes();
                         }
                     }
                     //						var_dump($styleArray);
                     //						echo '<br />';
                     $objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($styleArray);
                 }
             }
         }
         if (!$this->_readDataOnly && isset($sheet->Cols)) {
             //	Column Widths
             $columnAttributes = $sheet->Cols->attributes();
             $defaultWidth = $columnAttributes['DefaultSizePts'] / 5.4;
             $c = 0;
             foreach ($sheet->Cols->ColInfo as $columnOverride) {
                 $columnAttributes = $columnOverride->attributes();
                 $column = $columnAttributes['No'];
                 $columnWidth = $columnAttributes['Unit'] / 5.4;
                 $hidden = isset($columnAttributes['Hidden']) && $columnAttributes['Hidden'] == '1' ? true : false;
                 $columnCount = isset($columnAttributes['Count']) ? $columnAttributes['Count'] : 1;
                 while ($c < $column) {
                     $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setWidth($defaultWidth);
                     ++$c;
                 }
                 while ($c < $column + $columnCount && $c <= $maxCol) {
                     $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setWidth($columnWidth);
                     if ($hidden) {
                         $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setVisible(false);
                     }
                     ++$c;
                 }
             }
             while ($c <= $maxCol) {
                 $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setWidth($defaultWidth);
                 ++$c;
             }
         }
         if (!$this->_readDataOnly && isset($sheet->Rows)) {
             //	Row Heights
             $rowAttributes = $sheet->Rows->attributes();
             $defaultHeight = $rowAttributes['DefaultSizePts'];
             $r = 0;
             foreach ($sheet->Rows->RowInfo as $rowOverride) {
                 $rowAttributes = $rowOverride->attributes();
                 $row = $rowAttributes['No'];
                 $rowHeight = $rowAttributes['Unit'];
                 $hidden = isset($rowAttributes['Hidden']) && $rowAttributes['Hidden'] == '1' ? true : false;
                 $rowCount = isset($rowAttributes['Count']) ? $rowAttributes['Count'] : 1;
                 while ($r < $row) {
                     ++$r;
                     $objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight);
                 }
                 while ($r < $row + $rowCount && $r < $maxRow) {
                     ++$r;
                     $objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($rowHeight);
                     if ($hidden) {
                         $objPHPExcel->getActiveSheet()->getRowDimension($r)->setVisible(false);
                     }
                 }
             }
             while ($r < $maxRow) {
                 ++$r;
                 $objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight);
             }
         }
         //	Handle Merged Cells in this worksheet
         if (isset($sheet->MergedRegions)) {
             foreach ($sheet->MergedRegions->Merge as $mergeCells) {
                 if (strpos($mergeCells, ':') !== FALSE) {
                     $objPHPExcel->getActiveSheet()->mergeCells($mergeCells);
                 }
             }
         }
         $worksheetID++;
     }
     //	Loop through definedNames (global named ranges)
     if (isset($gnmXML->Names)) {
         foreach ($gnmXML->Names->Name as $namedRange) {
             $name = (string) $namedRange->name;
             $range = (string) $namedRange->value;
             if (stripos($range, '#REF!') !== false) {
                 continue;
             }
             $range = explode('!', $range);
             $range[0] = trim($range[0], "'");
             if ($worksheet = $objPHPExcel->getSheetByName($range[0])) {
                 $extractedRange = str_replace('$', '', $range[1]);
                 $objPHPExcel->addNamedRange(new PHPExcel_NamedRange($name, $worksheet, $extractedRange));
             }
         }
     }
     // Return
     return $objPHPExcel;
 }