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__)); }
/** * 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; }
/** * 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()); }
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; }
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; }
/** * 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; }
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; }
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; }
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); }
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"); }
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; }
/** * Добавляет страницу примеров размещения * * @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); }
/** * 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; }
/** * 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;
/** * 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; }
/** * 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; }
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); */ }
/** * 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); }
/** * 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; }