/** * 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[] = 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 = 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 = 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 Style(); $this->_readStyle($objStyle, $style); $excel->addCellXf($objStyle); } foreach ($xmlStyles->cellStyleXfs->xf as $xf) { $numFmt = 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 = 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 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 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 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) { Shared_Date::setExcelCalendar(Shared_Date::CALENDAR_WINDOWS_1900); if (isset($xmlWorkbook->workbookPr['date1904'])) { $date1904 = (string) $xmlWorkbook->workbookPr['date1904']; if ($date1904 == "true" || $date1904 == "1") { Shared_Date::setExcelCalendar(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((double) $xmlSheet->sheetFormatPr['defaultRowHeight']); } if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) { $docSheet->getDefaultColumnDimension()->setWidth((double) $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(Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"])); } if ($col["bestFit"]) { //$docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setAutoSize(true); } if ($col["hidden"]) { $docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setVisible(false); } if ($col["collapsed"]) { $docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setCollapsed(true); } if ($col["outlineLevel"] > 0) { $docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"])); } $docSheet->getColumnDimension(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 = 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 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 == (double) $value) { $value = (double) $value; } elseif ($value == (double) $value) { $value = (double) $value; } } // Rich text? if ($value instanceof 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"] == Style_Conditional::CONDITION_NONE || (string) $cfRule["type"] == Style_Conditional::CONDITION_CELLIS || (string) $cfRule["type"] == Style_Conditional::CONDITION_CONTAINSTEXT || (string) $cfRule["type"] == 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 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 = 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']}", Worksheet::BREAK_ROW); } } } if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) { foreach ($xmlSheet->colBreaks->brk as $brk) { if ($brk["man"]) { $docSheet->setBreak(Cell::stringFromColumnIndex($brk["id"]) . "1", 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 = 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 (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 = 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 = 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 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); } } } } // TODO: 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 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(Cell::stringFromColumnIndex($oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1)); $objDrawing->setOffsetX(Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff)); $objDrawing->setOffsetY(Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff)); $objDrawing->setResizeProportional(false); $objDrawing->setWidth(Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx"))); $objDrawing->setHeight(Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy"))); if ($xfrm) { $objDrawing->setRotation(Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot"))); } if ($outerShdw) { $shadow = $objDrawing->getShadow(); $shadow->setVisible(true); $shadow->setBlurRadius(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad"))); $shadow->setDistance(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist"))); $shadow->setDirection(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 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(Cell::stringFromColumnIndex($twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1)); $objDrawing->setOffsetX(Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff)); $objDrawing->setOffsetY(Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff)); $objDrawing->setResizeProportional(false); $objDrawing->setWidth(Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx"))); $objDrawing->setHeight(Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy"))); if ($xfrm) { $objDrawing->setRotation(Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot"))); } if ($outerShdw) { $shadow = $objDrawing->getShadow(); $shadow->setVisible(true); $shadow->setBlurRadius(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad"))); $shadow->setDistance(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist"))); $shadow->setDirection(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])); } 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 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 = 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 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; }
/** * Calculate an (approximate) OpenXML column width, based on font size and text contained * * @param int $fontSize Font size (in pixels or points) * @param bool $fontSizeInPixels Is the font size specified in pixels (true) or in points (false) ? * @param string $cellText Text to calculate width * @param int $rotation Rotation angle * @return int Column width */ public static function calculateColumnWidth(Style_Font $font, $cellText = '', $rotation = 0, Style_Font $defaultFont = null) { // If it is rich text, use plain text if ($cellText instanceof RichText) { $cellText = $cellText->getPlainText(); } // Special case if there are one or more newline characters ("\n") if (strpos($cellText, "\n") !== false) { $lineTexts = explode("\n", $cellText); $lineWitdhs = array(); foreach ($lineTexts as $lineText) { $lineWidths[] = self::calculateColumnWidth($font, $lineText, $rotation = 0, $defaultFont); } return max($lineWidths); // width of longest line in cell } // Try to get the exact text width in pixels try { // If autosize method is set to 'approx', use approximation if (self::$autoSizeMethod == self::AUTOSIZE_METHOD_APPROX) { throw new Exception('AutoSize method is set to approx'); } // Width of text in pixels excl. padding $columnWidth = self::getTextWidthPixelsExact($cellText, $font, $rotation); // Excel adds some padding, use 1.07 of the width of an 'n' glyph $columnWidth += ceil(self::getTextWidthPixelsExact('0', $font, 0) * 1.07); // pixels incl. padding } catch (Exception $e) { // Width of text in pixels excl. padding, approximation $columnWidth = self::getTextWidthPixelsApprox($cellText, $font, $rotation); // Excel adds some padding, just use approx width of 'n' glyph $columnWidth += self::getTextWidthPixelsApprox('n', $font, 0); } // Convert from pixel width to column width $columnWidth = Shared_Drawing::pixelsToCellDimension($columnWidth, $defaultFont); // Return return round($columnWidth, 6); }
/** * Write drawings to XML format * * @param Shared_XMLWriter $objWriter XML Writer * @param Worksheet_BaseDrawing $pDrawing * @param int $pRelationId * @throws Exception */ public function _writeDrawing(Shared_XMLWriter $objWriter = null, Worksheet_BaseDrawing $pDrawing = null, $pRelationId = -1) { if ($pRelationId >= 0) { // xdr:oneCellAnchor $objWriter->startElement('xdr:oneCellAnchor'); // Image location $aCoordinates = Cell::coordinateFromString($pDrawing->getCoordinates()); $aCoordinates[0] = Cell::columnIndexFromString($aCoordinates[0]); // xdr:from $objWriter->startElement('xdr:from'); $objWriter->writeElement('xdr:col', $aCoordinates[0] - 1); $objWriter->writeElement('xdr:colOff', Shared_Drawing::pixelsToEMU($pDrawing->getOffsetX())); $objWriter->writeElement('xdr:row', $aCoordinates[1] - 1); $objWriter->writeElement('xdr:rowOff', Shared_Drawing::pixelsToEMU($pDrawing->getOffsetY())); $objWriter->endElement(); // xdr:ext $objWriter->startElement('xdr:ext'); $objWriter->writeAttribute('cx', Shared_Drawing::pixelsToEMU($pDrawing->getWidth())); $objWriter->writeAttribute('cy', Shared_Drawing::pixelsToEMU($pDrawing->getHeight())); $objWriter->endElement(); // xdr:pic $objWriter->startElement('xdr:pic'); // xdr:nvPicPr $objWriter->startElement('xdr:nvPicPr'); // xdr:cNvPr $objWriter->startElement('xdr:cNvPr'); $objWriter->writeAttribute('id', $pRelationId); $objWriter->writeAttribute('name', $pDrawing->getName()); $objWriter->writeAttribute('descr', $pDrawing->getDescription()); $objWriter->endElement(); // xdr:cNvPicPr $objWriter->startElement('xdr:cNvPicPr'); // a:picLocks $objWriter->startElement('a:picLocks'); $objWriter->writeAttribute('noChangeAspect', '1'); $objWriter->endElement(); $objWriter->endElement(); $objWriter->endElement(); // xdr:blipFill $objWriter->startElement('xdr:blipFill'); // a:blip $objWriter->startElement('a:blip'); $objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'); $objWriter->writeAttribute('r:embed', 'rId' . $pRelationId); $objWriter->endElement(); // a:stretch $objWriter->startElement('a:stretch'); $objWriter->writeElement('a:fillRect', null); $objWriter->endElement(); $objWriter->endElement(); // xdr:spPr $objWriter->startElement('xdr:spPr'); // a:xfrm $objWriter->startElement('a:xfrm'); $objWriter->writeAttribute('rot', Shared_Drawing::degreesToAngle($pDrawing->getRotation())); $objWriter->endElement(); // a:prstGeom $objWriter->startElement('a:prstGeom'); $objWriter->writeAttribute('prst', 'rect'); // a:avLst $objWriter->writeElement('a:avLst', null); $objWriter->endElement(); // // a:solidFill // $objWriter->startElement('a:solidFill'); // // a:srgbClr // $objWriter->startElement('a:srgbClr'); // $objWriter->writeAttribute('val', 'FFFFFF'); ///* SHADE // // a:shade // $objWriter->startElement('a:shade'); // $objWriter->writeAttribute('val', '85000'); // $objWriter->endElement(); //*/ // $objWriter->endElement(); // $objWriter->endElement(); /* // a:ln $objWriter->startElement('a:ln'); $objWriter->writeAttribute('w', '88900'); $objWriter->writeAttribute('cap', 'sq'); // a:solidFill $objWriter->startElement('a:solidFill'); // a:srgbClr $objWriter->startElement('a:srgbClr'); $objWriter->writeAttribute('val', 'FFFFFF'); $objWriter->endElement(); $objWriter->endElement(); // a:miter $objWriter->startElement('a:miter'); $objWriter->writeAttribute('lim', '800000'); $objWriter->endElement(); $objWriter->endElement(); */ if ($pDrawing->getShadow()->getVisible()) { // a:effectLst $objWriter->startElement('a:effectLst'); // a:outerShdw $objWriter->startElement('a:outerShdw'); $objWriter->writeAttribute('blurRad', Shared_Drawing::pixelsToEMU($pDrawing->getShadow()->getBlurRadius())); $objWriter->writeAttribute('dist', Shared_Drawing::pixelsToEMU($pDrawing->getShadow()->getDistance())); $objWriter->writeAttribute('dir', Shared_Drawing::degreesToAngle($pDrawing->getShadow()->getDirection())); $objWriter->writeAttribute('algn', $pDrawing->getShadow()->getAlignment()); $objWriter->writeAttribute('rotWithShape', '0'); // a:srgbClr $objWriter->startElement('a:srgbClr'); $objWriter->writeAttribute('val', $pDrawing->getShadow()->getColor()->getRGB()); // a:alpha $objWriter->startElement('a:alpha'); $objWriter->writeAttribute('val', $pDrawing->getShadow()->getAlpha() * 1000); $objWriter->endElement(); $objWriter->endElement(); $objWriter->endElement(); $objWriter->endElement(); } /* // a:scene3d $objWriter->startElement('a:scene3d'); // a:camera $objWriter->startElement('a:camera'); $objWriter->writeAttribute('prst', 'orthographicFront'); $objWriter->endElement(); // a:lightRig $objWriter->startElement('a:lightRig'); $objWriter->writeAttribute('rig', 'twoPt'); $objWriter->writeAttribute('dir', 't'); // a:rot $objWriter->startElement('a:rot'); $objWriter->writeAttribute('lat', '0'); $objWriter->writeAttribute('lon', '0'); $objWriter->writeAttribute('rev', '0'); $objWriter->endElement(); $objWriter->endElement(); $objWriter->endElement(); */ /* // a:sp3d $objWriter->startElement('a:sp3d'); // a:bevelT $objWriter->startElement('a:bevelT'); $objWriter->writeAttribute('w', '25400'); $objWriter->writeAttribute('h', '19050'); $objWriter->endElement(); // a:contourClr $objWriter->startElement('a:contourClr'); // a:srgbClr $objWriter->startElement('a:srgbClr'); $objWriter->writeAttribute('val', 'FFFFFF'); $objWriter->endElement(); $objWriter->endElement(); $objWriter->endElement(); */ $objWriter->endElement(); $objWriter->endElement(); // xdr:clientData $objWriter->writeElement('xdr:clientData', null); $objWriter->endElement(); } else { throw new Exception("Invalid parameters passed."); } }
/** * Convert the height of a cell from user's units to pixels. By interpolation * the relationship is: y = 4/3x. If the height hasn't been set by the user we * use the default value. If the row is hidden we use a value of zero. * * @param Worksheet $sheet The sheet * @param integer $row The row index (1-based) * @return integer The width in pixels */ public static function sizeRow($sheet, $row = 1) { // default font of the workbook $font = $sheet->getParent()->getDefaultStyle()->getFont(); $rowDimensions = $sheet->getRowDimensions(); // first find the true row height in pixels (uncollapsed and unhidden) if (isset($rowDimensions[$row]) and $rowDimensions[$row]->getRowHeight() != -1) { // then we have a row dimension $rowDimension = $rowDimensions[$row]; $rowHeight = $rowDimension->getRowHeight(); $pixelRowHeight = (int) ceil(4 * $rowHeight / 3); // here we assume Arial 10 } else { if ($sheet->getDefaultRowDimension()->getRowHeight() != -1) { // then we have a default row dimension with explicit height $defaultRowDimension = $sheet->getDefaultRowDimension(); $rowHeight = $defaultRowDimension->getRowHeight(); $pixelRowHeight = Shared_Drawing::pointsToPixels($rowHeight); } else { // we don't even have any default row dimension. Height depends on default font $pointRowHeight = Shared_Font::getDefaultRowHeightByFont($font); $pixelRowHeight = Shared_Font::fontSizeToPixels($pointRowHeight); } } // now find the effective row height in pixels if (isset($rowDimensions[$row]) and !$rowDimensions[$row]->getVisible()) { $effectivePixelRowHeight = 0; } else { $effectivePixelRowHeight = $pixelRowHeight; } return $effectivePixelRowHeight; }
/** * Build CSS styles * * @param boolean $generateSurroundingHTML Generate surrounding HTML style? (html { }) * @return array * @throws Exception */ public function buildCSS($generateSurroundingHTML = true) { // PHPExcel object known? if (is_null($this->_phpExcel)) { throw new Exception('Internal PHPExcel object not set to an instance of an object.'); } // Cached? if (!is_null($this->_cssStyles)) { return $this->_cssStyles; } // Ensure that spans have been calculated if (!$this->_spansAreCalculated) { $this->_calculateSpans(); } // Construct CSS $css = array(); // Start styles if ($generateSurroundingHTML) { // html { } $css['html']['font-family'] = 'Calibri, Arial, Helvetica, sans-serif'; $css['html']['font-size'] = '11pt'; $css['html']['background-color'] = 'white'; } // table { } $css['table']['border-collapse'] = 'collapse'; $css['table']['page-break-after'] = 'always'; // .gridlines td { } $css['.gridlines td']['border'] = '1px dotted black'; // .b {} $css['.b']['text-align'] = 'center'; // BOOL // .e {} $css['.e']['text-align'] = 'center'; // ERROR // .f {} $css['.f']['text-align'] = 'right'; // FORMULA // .inlineStr {} $css['.inlineStr']['text-align'] = 'left'; // INLINE // .n {} $css['.n']['text-align'] = 'right'; // NUMERIC // .s {} $css['.s']['text-align'] = 'left'; // STRING // Calculate cell style hashes foreach ($this->_phpExcel->getCellXfCollection() as $index => $style) { $css['td.style' . $index] = $this->_createCSSStyle($style); } // Fetch sheets $sheets = array(); if (is_null($this->_sheetIndex)) { $sheets = $this->_phpExcel->getAllSheets(); } else { $sheets[] = $this->_phpExcel->getSheet($this->_sheetIndex); } // Build styles per sheet foreach ($sheets as $sheet) { // Calculate hash code $sheetIndex = $sheet->getParent()->getIndex($sheet); // Build styles // Calculate column widths $sheet->calculateColumnWidths(); // col elements, initialize $highestColumnIndex = Cell::columnIndexFromString($sheet->getHighestColumn()) - 1; for ($column = 0; $column <= $highestColumnIndex; ++$column) { $this->_columnWidths[$sheetIndex][$column] = 42; // approximation $css['table.sheet' . $sheetIndex . ' col.col' . $column]['width'] = '42pt'; } // col elements, loop through columnDimensions and set width foreach ($sheet->getColumnDimensions() as $columnDimension) { if (($width = Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth(), $this->_defaultFont)) >= 0) { $width = Shared_Drawing::pixelsToPoints($width); $column = Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1; $this->_columnWidths[$sheetIndex][$column] = $width; $css['table.sheet' . $sheetIndex . ' col.col' . $column]['width'] = $width . 'pt'; if ($columnDimension->getVisible() === false) { $css['table.sheet' . $sheetIndex . ' col.col' . $column]['visibility'] = 'collapse'; $css['table.sheet' . $sheetIndex . ' col.col' . $column]['*display'] = 'none'; // target IE6+7 } } } // Default row height $rowDimension = $sheet->getDefaultRowDimension(); // table.sheetN tr { } $css['table.sheet' . $sheetIndex . ' tr'] = array(); if ($rowDimension->getRowHeight() == -1) { $pt_height = Shared_Font::getDefaultRowHeightByFont($this->_phpExcel->getDefaultStyle()->getFont()); } else { $pt_height = $rowDimension->getRowHeight(); } $css['table.sheet' . $sheetIndex . ' tr']['height'] = $pt_height . 'pt'; if ($rowDimension->getVisible() === false) { $css['table.sheet' . $sheetIndex . ' tr']['display'] = 'none'; $css['table.sheet' . $sheetIndex . ' tr']['visibility'] = 'hidden'; } // Calculate row heights foreach ($sheet->getRowDimensions() as $rowDimension) { $row = $rowDimension->getRowIndex() - 1; // table.sheetN tr.rowYYYYYY { } $css['table.sheet' . $sheetIndex . ' tr.row' . $row] = array(); if ($rowDimension->getRowHeight() == -1) { $pt_height = Shared_Font::getDefaultRowHeightByFont($this->_phpExcel->getDefaultStyle()->getFont()); } else { $pt_height = $rowDimension->getRowHeight(); } $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['height'] = $pt_height . 'pt'; if ($rowDimension->getVisible() === false) { $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['display'] = 'none'; $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['visibility'] = 'hidden'; } } } // Cache if (is_null($this->_cssStyles)) { $this->_cssStyles = $css; } // Return return $css; }
/** * Writes the MSODRAWINGGROUP record if needed. Possibly split using CONTINUE records. */ private function _writeMsoDrawingGroup() { // any drawings in this workbook? $found = false; foreach ($this->_phpExcel->getAllSheets() as $sheet) { if (count($sheet->getDrawingCollection()) > 0) { $found = true; } } // if there are drawings, then we need to write MSODRAWINGGROUP record if ($found) { // create intermediate Escher object $escher = new Shared_Escher(); // dggContainer $dggContainer = new Shared_Escher_DggContainer(); $escher->setDggContainer($dggContainer); // this loop is for determining maximum shape identifier of all drawing $spIdMax = 0; $totalCountShapes = 0; $countDrawings = 0; foreach ($this->_phpExcel->getAllsheets() as $sheet) { $sheetCountShapes = 0; // count number of shapes (minus group shape), in sheet if (count($sheet->getDrawingCollection()) > 0) { ++$countDrawings; foreach ($sheet->getDrawingCollection() as $drawing) { ++$sheetCountShapes; ++$totalCountShapes; $spId = $sheetCountShapes | $this->_phpExcel->getIndex($sheet) + 1 << 10; $spIdMax = max($spId, $spIdMax); } } } $dggContainer->setSpIdMax($spIdMax + 1); $dggContainer->setCDgSaved($countDrawings); $dggContainer->setCSpSaved($totalCountShapes + $countDrawings); // total number of shapes incl. one group shapes per drawing // bstoreContainer $bstoreContainer = new Shared_Escher_DggContainer_BstoreContainer(); $dggContainer->setBstoreContainer($bstoreContainer); // the BSE's (all the images) foreach ($this->_phpExcel->getAllsheets() as $sheet) { foreach ($sheet->getDrawingCollection() as $drawing) { if ($drawing instanceof Worksheet_Drawing) { $filename = $drawing->getPath(); list($imagesx, $imagesy, $imageFormat) = getimagesize($filename); switch ($imageFormat) { case 1: // GIF, not supported by BIFF8, we convert to PNG $blipType = Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG; $imageResource = imagecreatefromgif($filename); ob_start(); imagepng($imageResource); $blipData = ob_get_contents(); ob_end_clean(); break; case 2: // JPEG $blipType = Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG; $blipData = file_get_contents($filename); break; case 3: // PNG $blipType = Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG; $blipData = file_get_contents($filename); break; case 6: // Windows DIB (BMP), we convert to PNG $blipType = Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG; $imageResource = Shared_Drawing::imagecreatefrombmp($filename); ob_start(); imagepng($imageResource); $blipData = ob_get_contents(); ob_end_clean(); break; default: continue 2; } $blip = new Shared_Escher_DggContainer_BstoreContainer_BSE_Blip(); $blip->setData($blipData); $BSE = new Shared_Escher_DggContainer_BstoreContainer_BSE(); $BSE->setBlipType($blipType); $BSE->setBlip($blip); $bstoreContainer->addBSE($BSE); } else { if ($drawing instanceof Worksheet_MemoryDrawing) { switch ($drawing->getRenderingFunction()) { case Worksheet_MemoryDrawing::RENDERING_JPEG: $blipType = Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG; $renderingFunction = 'imagejpeg'; break; case Worksheet_MemoryDrawing::RENDERING_GIF: case Worksheet_MemoryDrawing::RENDERING_PNG: case Worksheet_MemoryDrawing::RENDERING_DEFAULT: $blipType = Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG; $renderingFunction = 'imagepng'; break; } ob_start(); call_user_func($renderingFunction, $drawing->getImageResource()); $blipData = ob_get_contents(); ob_end_clean(); $blip = new Shared_Escher_DggContainer_BstoreContainer_BSE_Blip(); $blip->setData($blipData); $BSE = new Shared_Escher_DggContainer_BstoreContainer_BSE(); $BSE->setBlipType($blipType); $BSE->setBlip($blip); $bstoreContainer->addBSE($BSE); } } } } // write the Escher stream from the intermediate Escher object $writer = new Writer_Excel5_Escher($escher); $data = $writer->close(); $record = 0xeb; $length = strlen($data); $header = pack("vv", $record, $length); return $this->writeData($header . $data); } }