/** * 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; }
private function formatDataSetLabels($groupID, $datasetLabels, $labelCount, $rotation = '') { $datasetLabelFormatCode = $this->chart->getPlotArea()->getPlotGroupByIndex($groupID)->getPlotCategoryByIndex(0)->getFormatCode(); if (!is_null($datasetLabelFormatCode)) { // Retrieve any label formatting code $datasetLabelFormatCode = stripslashes($datasetLabelFormatCode); } $testCurrentIndex = 0; foreach ($datasetLabels as $i => $datasetLabel) { if (is_array($datasetLabel)) { if ($rotation == 'bar') { $datasetLabels[$i] = implode(" ", $datasetLabel); } else { $datasetLabel = array_reverse($datasetLabel); $datasetLabels[$i] = implode("\n", $datasetLabel); } } else { // Format labels according to any formatting code if (!is_null($datasetLabelFormatCode)) { $datasetLabels[$i] = PHPExcel_Style_NumberFormat::toFormattedString($datasetLabel, $datasetLabelFormatCode); } } ++$testCurrentIndex; } return $datasetLabels; }
public static function convertCellData(\PHPExcel_Cell $cell) { $ret = array(); $datatype = $cell->getDataType(); if ($datatype === \excel2sql\Type::EXCEL_NUMERIC) { $format = $cell->getStyle()->getNumberFormat()->getFormatCode(); if (array_key_exists($format, \excel2sql\Type::$numeric_convert_map)) { $format = \excel2sql\Type::$numeric_convert_map[$format]; } $ret['type'] = \excel2sql\Type::$numeric_to_sql_map[$format]; $ret['value'] = \PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), $format); if ($format === \excel2sql\Type::EXCEL_DATE || $format === \excel2sql\Type::EXCEL_DATETIME || $format === \excel2sql\Type::EXCEL_TIME) { $ret['value'] = "'{$ret['value']}'"; } } else { $ret['type'] = \excel2sql\Type::$excel_to_sql_map[$datatype]; if ($ret['value'] = $cell->getFormattedValue()) { if ($datatype === \excel2sql\Type::EXCEL_STRING || $datatype === \excel2sql\Type::EXCEL_STRING2) { $ret['value'] = "\"" . preg_replace("[\"]", "''", $ret['value']) . "\""; } } else { $ret['value'] = "null"; } } return $ret; }
/** * Get hash code * * @return string Hash code */ public function getHashCode() { $hashConditionals = ''; foreach ($this->_conditionalStyles as $conditional) { $hashConditionals .= $conditional->getHashCode(); } return md5($this->_fill->getHashCode() . $this->_font->getHashCode() . $this->_borders->getHashCode() . $this->_alignment->getHashCode() . $this->_numberFormat->getHashCode() . $hashConditionals . $this->_protection->getHashCode() . __CLASS__); }
/** * Get hash code * * @return string Hash code */ public function getHashCode() { $hashConditionals = ''; foreach ($this->conditionalStyles as $conditional) { $hashConditionals .= $conditional->getHashCode(); } return md5($this->fill->getHashCode() . $this->font->getHashCode() . $this->borders->getHashCode() . $this->alignment->getHashCode() . $this->numberFormat->getHashCode() . $hashConditionals . $this->protection->getHashCode() . ($this->quotePrefix ? 't' : 'f') . __CLASS__); }
function getImportExcelData($data, $fields) { global $total_records, $cCharset, $columnIndex; foreach ($data->getWorksheetIterator() as $worksheet) { $highestRow = $worksheet->getHighestRow(); for ($row = 2; $row <= $highestRow; ++$row) { for ($col = 0; $col < $columnIndex; ++$col) { $cell = $worksheet->getCellByColumnAndRow($col, $row); if (PHPExcel_Shared_Date::isDateTime($cell)) { $date_format = $cell->getParent()->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode(); $value = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $date_format); if (is_a($value, 'PHPExcel_RichText')) { $value = $value->getPlainText(); } if ($value) { $time = array(); if (strtotime($value)) { $value = strtotime($value); } else { $d_format = ""; for ($i = 0; $i < strlen($date_format); $i++) { $letter = substr(strtolower($date_format), $i, 1); if ($letter == "d" || $letter == "m" || $letter == "y") { if (strpos($d_format, $letter) === false) { $d_format .= $letter; } } } $value = strtotime(localdatetime2db($value, $d_format)); } // $value = PHPExcel_Shared_Date::ExcelToPHP($value); $time = localtime($value, true); $val = $time["tm_year"] + 1900 . "-" . ($time["tm_mon"] + 1) . "-" . $time["tm_mday"] . " " . $time["tm_hour"] . ":" . $time["tm_min"] . ":" . $time["tm_sec"]; } else { $val = NULL; } } else { $error_handler = set_error_handler("empty_error_handler"); $val = PHPExcel_Shared_String::ConvertEncoding($cell->getValue(), $cCharset, 'UTF-8'); if (is_a($val, 'PHPExcel_RichText')) { $val = $val->getPlainText(); } if ($error_handler) { set_error_handler($error_handler); } } $arr[$fields[$col]] = $val; } $ret = InsertRecord($arr, $row - 2); $total_records++; } break; } }
/** * PROCESA EL ARCHIVO EXCEL Y LO METE EN LA BASE DE DATOS */ public function ProcesaArchivo() { $archivo = $_FILES['archivo']; //COGE EL ARCHIVO $objPHPExcel = PHPExcel_IOFactory::load($archivo['tmp_name']); //NOMBRE DEL ARCHIVO TEMPORAL EN EL SISTEMA $objPHPExcel->setActiveSheetIndex(0); //COGE EL PANEL 1 DEL EXEL $numRows = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow(); //CUENTA LAS FILAS QUE TIENE EL EXCEL $datos = array(); //ARRAY PARA GUARDAR LOS DATOS $tipo = ""; //TIPO PARA VER SI ES CATEGORÍA O PRODUCTO $idCat = 0; //ID DE CATEGORÍA PARA EL PRODUCTO for ($i = 1; $i <= $numRows; $i++) { //POR CADA FILA $celdaB = $objPHPExcel->getActiveSheet()->getCell('B' . $i)->getCalculatedValue(); //CALCULO LA CELDA A$i $celdaA = $objPHPExcel->getActiveSheet()->getCell('A' . $i)->getCalculatedValue(); //CALCULO LA CELDA B$i if ($celdaB == "") { //SI LA CELDA B ESTÁ VACÍA ES QUE ES UN TIPO $tipo = $celdaA; //SE LE ASIGNA EL TIPO A LA VARIABLE } else { //SI TIENE CONTENIDO if ($celdaA == 'fec_ini' || $celdaA == 'fec_fin') { //SI ES UNA FECHA, LA CONVIERTO A FORMATO YYYY/MM/DD Y LO METO EN EL ARRAY $datos[$tipo][$celdaA] = PHPExcel_Style_NumberFormat::toFormattedString($celdaB, 'YYYY/MM/DD'); } else { // SI NO LO METO EN EL ARRAY SIN MÁS $datos[$tipo][$celdaA] = $celdaB; } } if ($celdaA == "se_muestra" && $tipo == 'CATEGORIA') { //SI LA ÚLTIMA CELDA ES se_muestra Y EL TIPO ES LA CATEGORÍA $idCat = $this->xml->mas_categoria($datos['CATEGORIA']); //AÑADE LA CATEGORÍA A LA BASE DE DATOS Y DEVUELVE LA ID } if ($celdaA == "se_muestra" && $tipo == 'PRODUCTOS') { //SI LA ÚLTIMA CELDA ES se_muestra Y EL TIPO ES PRODUCTOS $datos['PRODUCTOS']['Categoria_idCat'] = $idCat; //AÑADE LA ID DE CATEGORÍA AL ARRAY $this->xml->mas_productos($datos['PRODUCTOS']); //METE EL PRODUCTO EN LA BASE DE DATOS } } redirect('/Welcome/index', 'location', 301); //MUESTRA INICIO }
public function insertData($data) { // EVENT_INFO_ID serial NOT NULL, // STATUS CHAR(1) DEFAULT '0' NOT NULL, // EVENT_DATE TIMESTAMP, // MEDICAL_INSTITION_NO CHAR(3), // PARTICIPANT_NO NUMERIC(2,0) NOT NULL, // BARCODE NUMERIC(9,0) NOT NULL, // MEDICAL_INSTITION VARCHAR(100), // DEPARTMENT VARCHAR(100), // POST VARCHAR(100), // NAME VARCHAR(100), // TEL_NO1 VARCHAR(20), // TEL_NO2 VARCHAR(20), // FAX VARCHAR(20), // MAIL_ADDRESS VARCHAR(256), // POSTAL_CODE VARCHAR(10), // ADDRESS VARCHAR(100), // REMARKS VARCHAR(100) $columns = array('event_info_id', 'event_date', 'medical_instition_no', 'medical_instition', 'participant_no', 'department', 'post', 'name', 'tel_no1', 'tel_no2', 'mail_address', 'postal_code', 'address', 'remarks'); foreach ($data as $k1 => $v1) { // 先頭行はヘッダのため除く if ($k1 < 4) { continue; } $newData = null; $this->create(); if (!isset($v1[0]) || $v1[0] == '') { break; } foreach ($v1 as $k2 => $v2) { if ($k2 >= 14) { continue; } var_dump($v2); if ('event_info_id' === $columns[$k2]) { $newData[$columns[$k2]] = intval($v2); } else { if ('event_date' === $columns[$k2]) { $display_date = PHPExcel_Style_NumberFormat::toFormattedString($v2, PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2); // $newData[$columns[$k2]] = date('Y-M-D', $display_date); $newData[$columns[$k2]] = $display_date; } else { // $newData[$columns[$k2]] = mb_convert_encoding($v2, "UTF-8", "SJIS"); $newData[$columns[$k2]] = $v2; } } } $this->save($newData); } }
/** * Create a new PHPExcel_Style * * @param boolean $isSupervisor */ public function __construct($isSupervisor = false) { // Supervisor? $this->_isSupervisor = $isSupervisor; // Initialise values $this->_conditionalStyles = array(); $this->_font = new PHPExcel_Style_Font($isSupervisor); $this->_fill = new PHPExcel_Style_Fill($isSupervisor); $this->_borders = new PHPExcel_Style_Borders($isSupervisor); $this->_alignment = new PHPExcel_Style_Alignment($isSupervisor); $this->_numberFormat = new PHPExcel_Style_NumberFormat($isSupervisor); $this->_protection = new PHPExcel_Style_Protection($isSupervisor); // bind parent if we are a supervisor if ($isSupervisor) { $this->_font->bindParent($this); $this->_fill->bindParent($this); $this->_borders->bindParent($this); $this->_alignment->bindParent($this); $this->_numberFormat->bindParent($this); $this->_protection->bindParent($this); } }
function getSonucExcel($post, $get, $files) { $_db = JFactory::getOracleDBO(); $user =& JFactory::getUser(); $user_id = $user->getOracleUserId(); /////// EKLENEN FILENAME BIZDE KAYITLIMI DEGILMI ////////////////////7 // $filename = $files['upload']['name']; // // $paktetBul = explode('_', $filename); // $paket_Id = explode('.',$paktetBul[2]); $paket_Id = $post['paketId']; // if(!file_exists(EK_FOLDER.'sinav_bildirimleri/'.$filename) || $paktetBul[0] != $user_id || $paktetBul[1] != $get['sinav']){ // $return['hataMesaji'][1] = 'Sonuç bildirimi gönderdiğiniz dosya sistemimizde yer almamaktadır. Lütfen sistemden indirdiğiniz dosyanın adını değiştirmeden yollayınız.'; // return $return; // } ////////////////////////////////////////////////////////////////////777 if ($files['upload']['size'] > 0) { if ($files['upload']['type'] == 'application/vnd.ms-excel') { // $objPHPExcel = PHPExcel_IOFactory::load($files['upload']['tmp_name']); $uzanti = "xls"; $excel = new Spreadsheet_Excel_Reader(); $excel->setOutputEncoding('windows-1254'); $excel->read($files['upload']['tmp_name']); foreach ($excel->sheets[0]['cells'][1] as $key => $val) { $cols[$key] = $val; } $keySql = array('1' => 'TC_KIMLIK', 'UYRUK', 'ADI', 'SOYADI', 'DOGUM_TARIHI', 'CINSIYETI', 'EGITIMI', 'CALISMA_DURUMU', 'EMAIL', 'TELEFON', 'IBAN', 'YETERLILIK_KODU', 'BIRIM_KODU', 'SINAV_TURU_KODU', 'SINAV_TARIHI', 'SINAV_SAATI', 'SINAV_YERI_ID', 'DEGERLENDIRICI_TC_KIMLIK', 'PUAN', 'BASARI_DURUMU'); $bossatir = 0; $x = 4; for ($i = 4; $i <= count($excel->sheets[0]['cells']); $i++) { for ($y = 1; $y <= 20; $y++) { if (!isset($excel->sheets[0]['cells'][$i][$y])) { $excel->sheets[0]['cells'][$i][$y] = ""; } } foreach ($excel->sheets[0]['cells'][$i] as $key => $val) { $data[$x][$keySql[$key]] = mb_convert_encoding($val, "UTF-8", "windows-1254"); if ($cols[$key] == "dogumtarihi" && $data[$x][$keySql[$key]] != "") { $timestamp = trim($data[$x][$keySql[$key]]); $mysqlDate = date('d/m/Y', strtotime('1899-12-31+' . ($timestamp - 1) . ' days')); if (strpos($timestamp, ".") || strpos($timestamp, "/")) { $data[$x][$keySql[$key]] = str_replace('.', '/', $timestamp); } else { $data[$x][$keySql[$key]] = $mysqlDate; } } else { if ($cols[$key] == "sinavtarihi" && $data[$x][$keySql[$key]] != "") { $timestamp = trim($data[$x][$keySql[$key]]); $mysqlDate = date('d/m/Y', strtotime('1899-12-31+' . ($timestamp - 1) . ' days')); if (strpos($timestamp, ".") || strpos($timestamp, "/")) { $data[$x][$keySql[$key]] = str_replace('.', '/', $timestamp); } else { $data[$x][$keySql[$key]] = $mysqlDate; } } else { if ($cols[$key] == "tckn" && $data[$x][$keySql[$key]] == '') { $bossatir++; break; } else { if ($data[$x][$keySql[$key]] != '') { $data[$x][$keySql[$key]] = trim($data[$x][$keySql[$key]]); } } } } } $x++; if ($bossatir > 0) { break; } } } else { if ($files['upload']['type'] == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' || $files['upload']['type'] == "application/octet-stream") { $objReader = PHPExcel_IOFactory::createReader('Excel2007'); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($files['upload']['tmp_name']); $uzanti = "xlsx"; $objWorksheet = $objPHPExcel->getActiveSheet(); $highestRow = $objWorksheet->getHighestRow(); $highestColumn = $objWorksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $data = array(); for ($col = 0; $col <= $highestColumnIndex - 1; ++$col) { $key[] = $objWorksheet->getCellByColumnAndRow($col, 1)->getValue(); } $keySql = array('0' => 'TC_KIMLIK', 'UYRUK', 'ADI', 'SOYADI', 'DOGUM_TARIHI', 'CINSIYETI', 'EGITIMI', 'CALISMA_DURUMU', 'YETERLILIK_KODU', 'BIRIM_KODU', 'SINAV_TURU_KODU', 'SINAV_TARIHI', 'SINAV_SAATI', 'SINAV_YERI_ID', 'DEGERLENDIRICI_TC_KIMLIK', 'PUAN', 'BASARI_DURUMU'); $bossatir = 0; for ($row = 4; $row <= $highestRow; ++$row) { for ($col = 0; $col <= $highestColumnIndex - 1; ++$col) { if ($key[$col] == "dogumtarihi") { if ($objWorksheet->getCellByColumnAndRow($col, $row)->getValue() != '') { $timestamp = trim($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()); $mysqlDate = date('d/m/Y', strtotime('1899-12-31+' . ($timestamp - 1) . ' days')); if (strpos($timestamp, ".") || strpos($timestamp, "/")) { $data[$row][$keySql[$col]] = str_replace('.', '/', $timestamp); } else { $data[$row][$keySql[$col]] = $mysqlDate; } } } else { if ($key[$col] == "sinavtarihi") { if ($objWorksheet->getCellByColumnAndRow($col, $row)->getValue() != '') { $timestamp = trim($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()); $mysqlDate = date('d/m/Y', strtotime('1899-12-31+' . ($timestamp - 1) . ' days')); if (strpos($timestamp, ".") || strpos($timestamp, "/")) { $data[$row][$keySql[$col]] = str_replace('.', '/', $timestamp); } else { $data[$row][$keySql[$col]] = $mysqlDate; } } } else { if ($key[$col] == "sinavsaati") { if ($objWorksheet->getCellByColumnAndRow($col, $row)->getValue() != '') { $mysqlDate = PHPExcel_Style_NumberFormat::toFormattedString(trim($objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue()), 'hh:mm'); $data[$row][$keySql[$col]] = $mysqlDate; } } else { if ($key[$col] == "tckn" && $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() == '') { $bossatir++; break; } else { if ($objWorksheet->getCellByColumnAndRow($col, $row)->getValue() != '') { $data[$row][$keySql[$col]] = trim($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()); } } } } } } if ($bossatir > 0) { break; } } } else { print 'dosya formatı yanlış'; exit; } } $sql = "select yeterlilik_id from m_belgelendirme_sinav where sinav_id=" . $get["sinav"]; $yeterlilik_id = $_db->prep_exec($sql, array()); $yeterlilik_id = $yeterlilik_id[0]["YETERLILIK_ID"]; $sinavYerleri = $this->sinavYeriKontrol($user_id, $yeterlilik_id); $degerlendiriciler = $this->sinavDegerlendiriciKontrol($user_id, $yeterlilik_id); $keyArray = array(); $dataTest = array(); $dataTest = $data; $tcArray = array(); foreach ($dataTest as $key => $cow) { $keyArray[] = $key; unset($dataTest[$key]['PUAN']); unset($dataTest[$key]['BASARI_DURUMU']); unset($dataTest[$key]['SINAV_TARIHI']); unset($dataTest[$key]['SINAV_SAATI']); unset($dataTest[$key]['YETERLILIK_KODU']); unset($dataTest[$key]['UYRUK']); unset($dataTest[$key]['DEGERLENDIRICI_TC_KIMLIK']); settype($dataTest[$key]['TC_KIMLIK'], "string"); $dataTest[$key]['SINAV_YERI_ID'] = (int) $dataTest[$key]['SINAV_YERI_ID']; $dataTest[$key]['BIRIM_KODU'] = ucwords(trim(str_replace(' ', '', $dataTest[$key]['BIRIM_KODU']))); $dataTest[$key]['SINAV_TURU_KODU'] = ucwords(trim(str_replace(' ', '', $dataTest[$key]['SINAV_TURU_KODU']))); if (isset($dataTest[$key]['IBAN'])) { //ALT+255 BOŞLUĞU YAKALAR $dataTest[$key]['IBAN'] = str_replace(array(' ', chr(0xc2) . chr(0xa0)), array('', ''), $dataTest[$key]['IBAN']); } if (isset($dataTest[$key]['TELEFON'])) { $dataTest[$key]['TELEFON'] = str_replace(array(' ', '(', ')', '-', '+90'), array('', '', '', '', ''), $dataTest[$key]['TELEFON']); if (substr($dataTest[$key]['TELEFON'], 0, 1) == "0") { $dataTest[$key]['TELEFON'] = substr($dataTest[$key]['TELEFON'], 1); } } if (!in_array($dataTest[$key]['TC_KIMLIK'], $tcArray)) { $tcArray[] = $dataTest[$key]['TC_KIMLIK']; $dataTest[$key]['ADI'] = FormFactory::ucWordsTR($dataTest[$key]['ADI']); $dataTest[$key]['SOYADI'] = FormFactory::toUpperCase($dataTest[$key]['SOYADI']); $cinsiyet = FormFactory::toUpperCase($dataTest[$key]['CINSIYETI']); if ($cinsiyet == 'ERKEK') { $dataTest[$key]['CINSIYETI'] = 1; $data[$key]['CINSIYETI'] = 1; } else { if ($cinsiyet == 'KADIN') { $dataTest[$key]['CINSIYETI'] = 2; $data[$key]['CINSIYETI'] = 2; } else { if ($cinsiyet == 'BELİRTİLMEMİŞ') { $dataTest[$key]['CINSIYETI'] = 3; $data[$key]['CINSIYETI'] = 3; } } } $egitimm = FormFactory::toUpperCase(trim(str_replace(' ', '', $dataTest[$key]['EGITIMI']))); if ($egitimm == 'OKURYAZARDEĞİL') { $dataTest[$key]['EGITIMI'] = 1; $data[$key]['EGITIMI'] = 1; } else { if ($egitimm == 'OKURYAZAR') { $dataTest[$key]['EGITIMI'] = 2; $data[$key]['EGITIMI'] = 2; } else { if ($egitimm == 'İLKOKUL') { $dataTest[$key]['EGITIMI'] = 3; $data[$key]['EGITIMI'] = 3; } else { if ($egitimm == 'ORTAOKUL') { $dataTest[$key]['EGITIMI'] = 4; $data[$key]['EGITIMI'] = 4; } else { if ($egitimm == 'MESLEKLİSESİ') { $dataTest[$key]['EGITIMI'] = 5; $data[$key]['EGITIMI'] = 5; } else { if ($egitimm == 'GENELLİSE') { $dataTest[$key]['EGITIMI'] = 6; $data[$key]['EGITIMI'] = 6; } else { if ($egitimm == 'MESLEKYÜKSEKOKULU') { $dataTest[$key]['EGITIMI'] = 7; $data[$key]['EGITIMI'] = 7; } else { if ($egitimm == 'LİSANS') { $dataTest[$key]['EGITIMI'] = 8; $data[$key]['EGITIMI'] = 8; } else { if ($egitimm == 'YÜKSEKLİSANS') { $dataTest[$key]['EGITIMI'] = 9; $data[$key]['EGITIMI'] = 9; } else { if ($egitimm == 'DOKTORA') { $dataTest[$key]['EGITIMI'] = 10; $data[$key]['EGITIMI'] = 10; } } } } } } } } } } $calismaDurum = FormFactory::toUpperCase(trim(str_replace(' ', '', $dataTest[$key]['CALISMA_DURUMU']))); if ($calismaDurum == 'ÇALIŞIYOR') { $dataTest[$key]['CALISMA_DURUMU'] = 1; $data[$key]['CALISMA_DURUMU'] = 1; } else { if ($calismaDurum == 'ÇALIŞMIYOR') { $dataTest[$key]['CALISMA_DURUMU'] = 2; $data[$key]['CALISMA_DURUMU'] = 2; } else { if ($calismaDurum == 'STAJYAPIYOR') { $dataTest[$key]['CALISMA_DURUMU'] = 3; $data[$key]['CALISMA_DURUMU'] = 3; } } } } else { unset($dataTest[$key]['ADI']); unset($dataTest[$key]['SOYADI']); unset($dataTest[$key]['DOGUM_TARIHI']); unset($dataTest[$key]['CINSIYETI']); unset($dataTest[$key]['EGITIMI']); unset($dataTest[$key]['CALISMA_DURUMU']); } } //array_splice($data[4], 5) ilk 5 degerden sonrasını siliyor. 5'degerden sonrasını sana döndürüyor. $sql = "select * from m_yeterlilik where yeterlilik_id=" . $yeterlilik_id; $yeterlilik = $_db->prep_exec($sql, array()); $yeni_mi = $yeterlilik[0]["YENI_MI"]; //Eski excel ve yeni excel format uyuşmazlıkları için eklendi $sinavTarihi = $this->getSinavTarihi($get['sinav']); $sql_add = ""; if (strtotime(str_replace('/', '-', $sinavTarihi)) > strtotime(str_replace('/', '-', '20-07-2015'))) { $sql_add = ",M_BELGELENDIRME_OGRENCI.TELEFON,M_BELGELENDIRME_OGRENCI.EMAIL,M_BELGELENDIRME_OGRENCI.IBAN"; } if ($yeni_mi == 1) { $sql = "SELECT DISTINCT M_BELGELENDIRME_OGRENCI.TC_KIMLIK, M_BELGELENDIRME_OGRENCI.ADI, M_BELGELENDIRME_OGRENCI.SOYADI, M_BELGELENDIRME_OGRENCI.DOGUM_TARIHI, \n M_BELGELENDIRME_OGRENCI.CINSIYETI, M_BELGELENDIRME_OGRENCI.EGITIMI,\n M_BELGELENDIRME_OGRENCI.CALISMA_DURUMU,\n \t\t\t\t\t\tM_BIRIM.BIRIM_KODU, M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_TURU_KODU,\n M_BELGELENDIRME_ADAY_BILDIRIM.BILDIRIM_ID,M_BELGELENDIRME_ADAY_BILDIRIM.BIRIM_ID,M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_TURU_KODU,\n M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_YERI_ID, M_BELGELENDIRME_ADAY_BILDIRIM.DEGERLENDIRICI_TC_KIMLIK" . $sql_add . "\n FROM M_BELGELENDIRME_OGRENCI\n JOIN M_BELGELENDIRME_ADAY_BILDIRIM ON M_BELGELENDIRME_OGRENCI.TC_KIMLIK = M_BELGELENDIRME_ADAY_BILDIRIM.TC_KIMLIK\n JOIN M_BIRIM ON M_BELGELENDIRME_ADAY_BILDIRIM.BIRIM_ID = M_BIRIM.BIRIM_ID\n WHERE M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_ID = ? AND PAKET_ID=?\n ORDER BY M_BELGELENDIRME_ADAY_BILDIRIM.BILDIRIM_ID"; } else { $sql = "SELECT DISTINCT M_BELGELENDIRME_OGRENCI.TC_KIMLIK, M_BELGELENDIRME_OGRENCI.ADI, M_BELGELENDIRME_OGRENCI.SOYADI, M_BELGELENDIRME_OGRENCI.DOGUM_TARIHI, \n M_BELGELENDIRME_OGRENCI.CINSIYETI, M_BELGELENDIRME_OGRENCI.EGITIMI,\n M_BELGELENDIRME_OGRENCI.CALISMA_DURUMU,\n \t\t\t\t\t\tM_YETERLILIK_ALT_BIRIM.YETERLILIK_ALT_BIRIM_NO AS BIRIM_KODU, M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_TURU_KODU,\n M_BELGELENDIRME_ADAY_BILDIRIM.BILDIRIM_ID,M_BELGELENDIRME_ADAY_BILDIRIM.BIRIM_ID,M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_TURU_KODU,\n M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_YERI_ID, M_BELGELENDIRME_ADAY_BILDIRIM.DEGERLENDIRICI_TC_KIMLIK" . $sql_add . "\n FROM M_BELGELENDIRME_OGRENCI\n JOIN M_BELGELENDIRME_ADAY_BILDIRIM ON M_BELGELENDIRME_OGRENCI.TC_KIMLIK = M_BELGELENDIRME_ADAY_BILDIRIM.TC_KIMLIK\n JOIN M_YETERLILIK_ALT_BIRIM ON M_BELGELENDIRME_ADAY_BILDIRIM.BIRIM_ID = M_YETERLILIK_ALT_BIRIM.YETERLILIK_ALT_BIRIM_ID\n WHERE M_BELGELENDIRME_ADAY_BILDIRIM.SINAV_ID = ? AND PAKET_ID=?\n ORDER BY M_BELGELENDIRME_ADAY_BILDIRIM.BILDIRIM_ID"; } $kayitliaday = $_db->prep_exec($sql, array($get['sinav'], $paket_Id)); $adayBirims = array(); $adaylar = array(); $tcArray = array(); foreach ($kayitliaday as $key => $row) { $adayBirims[$keyArray[$key]][] = $kayitliaday[$key]['BIRIM_ID']; $adayBirims[$keyArray[$key]][] = $kayitliaday[$key]['SINAV_TURU_KODU']; unset($kayitliaday[$key]['BIRIM_ID']); unset($kayitliaday[$key]['BILDIRIM_ID']); unset($kayitliaday[$key]['DEGERLENDIRICI_TC_KIMLIK']); if ($yeni_mi == 1) { $kayitliaday[$key]['BIRIM_KODU'] = trim($kayitliaday[$key]['BIRIM_KODU']); } else { $kayitliaday[$key]['BIRIM_KODU'] = trim($yeterlilik[0]['YETERLILIK_KODU']) . '/' . trim($kayitliaday[$key]['BIRIM_KODU']); } $kayitliaday[$key]['BIRIM_KODU'] = str_replace(" ", "", $kayitliaday[$key]['BIRIM_KODU']); $kayitliaday[$key]['SINAV_YERI_ID'] = (int) $kayitliaday[$key]['SINAV_YERI_ID']; if (!in_array($kayitliaday[$key]['TC_KIMLIK'], $tcArray)) { $tcArray[] = $kayitliaday[$key]['TC_KIMLIK']; $kayitliaday[$key]['CINSIYETI'] = (int) $kayitliaday[$key]['CINSIYETI']; $kayitliaday[$key]['EGITIMI'] = (int) $kayitliaday[$key]['EGITIMI']; $kayitliaday[$key]['CALISMA_DURUMU'] = (int) $kayitliaday[$key]['CALISMA_DURUMU']; } else { unset($kayitliaday[$key]['ADI']); unset($kayitliaday[$key]['SOYADI']); unset($kayitliaday[$key]['DOGUM_TARIHI']); unset($kayitliaday[$key]['CINSIYETI']); unset($kayitliaday[$key]['EGITIMI']); unset($kayitliaday[$key]['CALISMA_DURUMU']); } $adaylar[$keyArray[$key]] = $kayitliaday[$key]; } $hataArray = array(); for ($i = 4; $i < count($dataTest) + 4; $i++) { if ($adaylar[$i] != $dataTest[$i]) { $hatalikolonlar = ""; for ($j = 0; $j < count($keySql); $j++) { if ($adaylar[$i][$keySql[$j]] != $dataTest[$i][$keySql[$j]]) { if ($keySql[$j] == "EMAIL" and $dataTest[$i][$keySql[$j]] == "" or $keySql[$j] == "TELEFON" and $dataTest[$i][$keySql[$j]] == "" or $keySql[$j] == "IBAN" and $dataTest[$i][$keySql[$j]] == "") { } else { $hatalikolonlar .= $keySql[$j] . " "; } } } if ($hatalikolonlar != "") { $return["adayBilgisi"][] = $i . " (" . $hatalikolonlar . ") "; } } } if (count($return["adayBilgisi"]) == 0) { $sinavTarihi = $this->getSinavTarihi($get['sinav']); $sinavYerleri = $this->sinavYeriKontrol($user_id, $yeterlilik_id); $degerlendiriciler = $this->sinavDegerlendiriciKontrol($user_id, $yeterlilik_id); foreach ($data as $key => $satir) { ////////////// EXCEL'DEN GELEN SINAV TARİHİ KONTROLÜ /////////////// if (strtotime($sinavTarihi) > strtotime(ereg_replace("/", "-", $satir["SINAV_TARIHI"]))) { $return["hataMesaji"][4] = "Aşağıdaki satırlarda verilen Sınav Tarihleri, Sınav Programınızda belirttiğiniz Başlangıç Tarihinden (" . $sinavTarihi . ") öncedir. Düzeltip dosyanızı yeniden yükleyiniz."; $return["sinavTarihi"][] = array($key, $satir["SINAV_TARIHI"]); } ////////////// EXCEL'DEN GELEN SINAV TARİHİ 360 GÜN KONTROLÜ /////////////// if (strtotime('+360 days', strtotime($sinavTarihi)) < strtotime(ereg_replace("/", "-", $satir["SINAV_TARIHI"]))) { $return["hataMesaji"][2] = "Aşağıdaki satırlarda verilen Sınav Tarihleri, Sınav Programınızda belirttiğiniz Başlangıç Tarihinden (" . $sinavTarihi . ") 1 yıl içinde olmalıdır. Düzeltip dosyanızı yeniden yükleyiniz."; $return["sinavTarihi360"][] = array($key, $satir["SINAV_TARIHI"]); } ////////////// EXCEL'DEN GELEN SINAV YERİ KONTROLÜ /////////////// // if (!in_array($satir["SINAV_YERI_ID"], $sinavYerleri)){ // $return["hataMesaji"][5]="Aşağıdaki satırlarda Verdiğiniz Sınav Yeri ID'leri, <a href='index.php?option=com_belgelendirme&view=belgelendirme_islemleri&layout=belgelendirme_sinav_yeri' style='color:red;'>Sınav Yerleriniz</a> arasında yoktur. Düzeltip dosyanızı yeniden yükleyiniz."; // $return["sinavYeri"][]=array($key,$satir["SINAV_YERI_ID"]); // } ////////////// EXCEL'DEN GELEN DEĞERLENDİRİCİLERİN KONTROLÜ /////////////// $degTcKimliks = explode(",", $satir["DEGERLENDIRICI_TC_KIMLIK"]); foreach ($degTcKimliks as $degTcKimlik) { if (!in_array(trim($degTcKimlik), $degerlendiriciler)) { $return["hataMesaji"][6] = "Aşağıdaki satırlarda verdiğiniz Değerlendirici/Gözetmen'ler, <a href='index.php?option=com_belgelendirme&view=belgelendirme_islemleri&layout=belgelendirme_degerlendirici' style='color:red;'>Değerlendiriciler</a> arasında yoktur. Düzeltip dosyanızı yeniden yükleyiniz."; $return["degerlendirici"][] = array($key, $degTcKimlik); } } ////////////// EXCEL'DEN GELEN BASARI VE SONUC KONTROLÜ /////////////// if ($satir["BASARI_DURUMU"] == '' || empty($satir["BASARI_DURUMU"]) || !isset($satir["BASARI_DURUMU"])) { $return["hataMesaji"][7] = "Aşağıdaki satırlarda Sonuç bildirilmemiştir. Düzeltip dosyanızı yeniden yükleyiniz."; $return["sonuc"][] = $key; } else { $sonuc = 0; if (FormFactory::toUpperCase($satir["BASARI_DURUMU"]) == 'BAŞARILI') { if ($satir['PUAN'] == '' || empty($satir["PUAN"]) || !isset($satir["PUAN"])) { $return["hataMesaji"][8] = "Aşağıdaki satırlarda Sonuç bildirilmesine rağmen Puan bildirilmemiştir. Düzeltip dosyanızı yeniden yükleyiniz."; $return["puan"][] = $key; } else { $adaySonuc[$key] = 1; } } else { if (FormFactory::toUpperCase($satir["BASARI_DURUMU"]) == 'BAŞARISIZ') { if ($satir['PUAN'] == '' || empty($satir["PUAN"]) || !isset($satir["PUAN"])) { $satir['PUAN'] = 0; $adaySonuc[$key] = 2; } else { $adaySonuc[$key] = 2; } } else { if (FormFactory::toUpperCase($satir["BASARI_DURUMU"]) == 'GİRMEDİ') { $adaySonuc[$key] = 3; } } } } ///////////// EXCEL'DEN GELEN VERIYI KAYDETME //////////////// $sql = "UPDATE M_BELGELENDIRME_ADAY_BILDIRIM SET PUAN=?, BASARI_DURUMU = ?, DEGERLENDIRICI_TC_KIMLIK = ? WHERE TC_KIMLIK = ? AND SINAV_ID=? AND BIRIM_ID=? AND SINAV_TURU_KODU=? AND PAKET_ID=?"; $param = array($satir['PUAN'], $adaySonuc[$key], $satir['DEGERLENDIRICI_TC_KIMLIK'], $satir['TC_KIMLIK'], $get['sinav'], $adayBirims[$key][0], $adayBirims[$key][1], $paket_Id); $durum = $_db->prep_exec_insert($sql, $param); } if (count($return) == 0) { $sql = "DELETE FROM M_BELGELENDIRME_SINAV_DOSYA WHERE SINAV_ID=? AND PAKET_ID=?"; $_db->prep_exec_insert($sql, array($get['sinav'], $paket_Id)); $paket_id_yeni = $_db->getNextVal('SEQ_SINAV_DOSYA'); $sql = "INSERT INTO M_BELGELENDIRME_SINAV_DOSYA (PAKET_ID,SINAV_ID,TIP,UZANTI) VALUES(?,?,2,?)"; $_db->prep_exec_insert($sql, array($paket_id_yeni, $get['sinav'], $uzanti)); $sql = "UPDATE M_BELGELENDIRME_ADAY_BILDIRIM SET PAKET_ID = ? WHERE SINAV_ID=? AND PAKET_ID=?"; $_db->prep_exec_insert($sql, array($paket_id_yeni, $get['sinav'], $paket_Id)); move_uploaded_file($files['upload']['tmp_name'], EK_FOLDER . "sinav_bildirimleri/" . $user_id . "_" . $get["sinav"] . "_" . $paket_id_yeni . "." . $uzanti); } else { $sql = "UPDATE M_BELGELENDIRME_ADAY_BILDIRIM SET PUAN = null, BASARI_DURUMU = null WHERE PAKET_ID = ?"; $_db->prep_exec_insert($sql, array($paket_Id)); return $return; } } else { $return["hataMesaji"][3] = "Yolladığınız dosyadaki veriler ile indirdiğiniz dosyadaki veriler uyuşmamaktadır. Kontrol ederek tekrar yükleyiniz."; return $return; } } }
/** * Get Number Format * * @return PHPExcel_Style_NumberFormat */ public function getNumberFormat() { if (isset($this->_numberFormat)) { return $this->_numberFormat; } $property = new PHPExcel_Style_NumberFormat(); $property->propertyPrepareBind($this, "_numberFormat"); return $property; }
/** * Lee el archivo excel cargado y lo importa en la base de datos */ public function ProcesaArchivo() { $archivo = $_FILES['archivo']; $objPHPExcel = PHPExcel_IOFactory::load($archivo['tmp_name']); foreach ($objPHPExcel->getWorksheetIterator() as $hojaEstilo) { $num_filas = $hojaEstilo->getHighestRow(); $cell = $hojaEstilo->getCellByColumnAndRow(0, 3); $categoria['cod_categoria'] = $cell->getValue(); $cell = $hojaEstilo->getCellByColumnAndRow(1, 3); $categoria['nombre_cat'] = $cell->getValue(); $cell = $hojaEstilo->getCellByColumnAndRow(2, 3); $categoria['descripcion'] = $cell->getValue(); $cell = $hojaEstilo->getCellByColumnAndRow(3, 3); $categoria['mostrar'] = $cell->getValue(); $categoria_id = $this->Mdl_xml->addCategoria($categoria); //Guardamos su id para poder insertar las camisetas en esa categoría //CREA e INSERTA CAMISETA DESDE EXCEL for ($row = 7; $row <= $num_filas; ++$row) { for ($col = 0; $col <= 11; ++$col) { switch ($col) { case 0: $cell = $hojaEstilo->getCellByColumnAndRow($col, $row); $camiseta['cod_camiseta'] = $cell->getValue(); break; case 1: $cell = $hojaEstilo->getCellByColumnAndRow($col, $row); $camiseta['nombre_cam'] = $cell->getValue(); break; case 2: $cell = $hojaEstilo->getCellByColumnAndRow($col, $row); $camiseta['precio'] = $cell->getValue(); break; case 3: $cell = $hojaEstilo->getCellByColumnAndRow($col, $row); $camiseta['descuento'] = $cell->getValue(); break; case 4: $cell = $hojaEstilo->getCellByColumnAndRow($col, $row); $camiseta['imagen'] = $cell->getValue(); break; case 5: $cell = $hojaEstilo->getCellByColumnAndRow($col, $row); $camiseta['iva'] = $cell->getValue(); break; case 6: $cell = $hojaEstilo->getCellByColumnAndRow($col, $row); $camiseta['descripcion'] = $cell->getValue(); break; case 7: $cell = $hojaEstilo->getCellByColumnAndRow($col, $row); $camiseta['seleccionada'] = $cell->getValue(); break; case 8: $cell = $hojaEstilo->getCellByColumnAndRow($col, $row); $camiseta['mostrar'] = $cell->getValue(); break; case 9: $cell = $hojaEstilo->getCellByColumnAndRow($col, $row); $val = $cell->getValue(); $camiseta['fecha_inicio'] = PHPExcel_Style_NumberFormat::toFormattedString($val, 'YYYY-MM-DD'); break; case 10: $cell = $hojaEstilo->getCellByColumnAndRow($col, $row); $val = $cell->getValue(); $camiseta['fecha_fin'] = PHPExcel_Style_NumberFormat::toFormattedString($val, 'YYYY-MM-DD'); break; case 11: $cell = $hojaEstilo->getCellByColumnAndRow($col, $row); $camiseta['stock'] = $cell->getValue(); break; } } $camiseta['idCategoria'] = $categoria_id; //Guardamos el id de su categoría $this->Mdl_xml->AddCamiseta($camiseta); // Inserta camiseta } } $cuerpo = $this->load->view('View_importacionExcelCorrecta', '', true); $this->load->view('View_plantilla', array('cuerpo' => $cuerpo, 'titulo' => 'Importación en Excel', 'homeactive' => 'active')); }
/** * 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; }
public function actionUploadexcel($fkperson, $idbp24, $name) { $uploadfile = "uploads/" . $name . "" . ".xlsx"; try { $inputfiletype = \PHPExcel_IOFactory::identify($uploadfile); $objreader = \PHPExcel_IOFactory::createReader($inputfiletype); $objPHPExcel = $objreader->load($uploadfile); } catch (Exception $e) { die('Error'); } $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); for ($row = 1; $row <= $highestRow; $row++) { $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE); if ($row == 1) { continue; } $model = new \app\models\Monitoringbp24data(); $time = $rowData[0][0]; $model->time = \PHPExcel_Style_NumberFormat::toFormattedString($time, 'hh:mm:ss'); $model->SBPbr = $rowData[0][1]; $model->dia = $rowData[0][2]; $model->pulse = $rowData[0][3]; $model->SBPao = $rowData[0][4]; $model->AIXao = $rowData[0][5]; $model->AIXbr = $rowData[0][6]; $model->PWVao = $rowData[0][7]; $model->PWVsd = $rowData[0][8]; $model->date_creation = date("Y-m-d"); $model->fk_person = $fkperson; $model->fk_id24h = $idbp24; $model->altered = 1; $model->save(FALSE); } if (file_exists($uploadfile)) { unlink($uploadfile); } Yii::$app->session->setFlash('success', 'Records saved and excel uploaded successfully'); return $this->redirect(["bp24h/update/{$idbp24}"]); }
/** * Write NumberFormat * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel_Style_NumberFormat $pNumberFormat Number Format * @param int $pId Number Format identifier * @throws Exception */ private function _writeNumFmt(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Style_NumberFormat $pNumberFormat = null, $pId = 0) { // Translate formatcode $formatCode = $pNumberFormat->getFormatCode(); // numFmt $objWriter->startElement('numFmt'); $objWriter->writeAttribute('numFmtId', $pId + 164); $objWriter->writeAttribute('formatCode', $formatCode); $objWriter->endElement(); }
function excel_date($xl_date) { return $displayDate = PHPExcel_Style_NumberFormat::toFormattedString($xl_date, 'YYYY-MM-DD hh:mm:ss'); }
/** * Get cell value with formatting * * @return string */ public function getFormattedValue() { return (string) PHPExcel_Style_NumberFormat::toFormattedString($this->getCalculatedValue(), $this->getStyle()->getNumberFormat()->getFormatCode()); }
/** * Get built-in format code * * @param int $pIndex * @return string */ public static function builtInFormatCode($pIndex) { // Clean parameter $pIndex = intval($pIndex); // Built-in format codes if (is_null(self::$_builtInFormats)) { self::$_builtInFormats = array(); // General self::$_builtInFormats[0] = 'General'; self::$_builtInFormats[1] = '0'; self::$_builtInFormats[2] = '0.00'; self::$_builtInFormats[3] = '#,##0'; self::$_builtInFormats[4] = '#,##0.00'; self::$_builtInFormats[9] = '0%'; self::$_builtInFormats[10] = '0.00%'; self::$_builtInFormats[11] = '0.00E+00'; self::$_builtInFormats[12] = '# ?/?'; self::$_builtInFormats[13] = '# ??/??'; self::$_builtInFormats[14] = 'mm-dd-yy'; self::$_builtInFormats[15] = 'd-mmm-yy'; self::$_builtInFormats[16] = 'd-mmm'; self::$_builtInFormats[17] = 'mmm-yy'; self::$_builtInFormats[18] = 'h:mm AM/PM'; self::$_builtInFormats[19] = 'h:mm:ss AM/PM'; self::$_builtInFormats[20] = 'h:mm'; self::$_builtInFormats[21] = 'h:mm:ss'; self::$_builtInFormats[22] = 'm/d/yy h:mm'; self::$_builtInFormats[37] = '#,##0 ;(#,##0)'; self::$_builtInFormats[38] = '#,##0 ;[Red](#,##0)'; self::$_builtInFormats[39] = '#,##0.00;(#,##0.00)'; self::$_builtInFormats[40] = '#,##0.00;[Red](#,##0.00)'; self::$_builtInFormats[45] = 'mm:ss'; self::$_builtInFormats[46] = '[h]:mm:ss'; self::$_builtInFormats[47] = 'mmss.0'; self::$_builtInFormats[48] = '##0.0E+0'; self::$_builtInFormats[49] = '@'; // CHT self::$_builtInFormats[27] = '[$-404]e/m/d'; self::$_builtInFormats[30] = 'm/d/yy'; self::$_builtInFormats[36] = '[$-404]e/m/d'; self::$_builtInFormats[50] = '[$-404]e/m/d'; self::$_builtInFormats[57] = '[$-404]e/m/d'; // THA self::$_builtInFormats[59] = 't0'; self::$_builtInFormats[60] = 't0.00'; self::$_builtInFormats[61] = 't#,##0'; self::$_builtInFormats[62] = 't#,##0.00'; self::$_builtInFormats[67] = 't0%'; self::$_builtInFormats[68] = 't0.00%'; self::$_builtInFormats[69] = 't# ?/?'; self::$_builtInFormats[70] = 't# ??/??'; } if (array_key_exists($pIndex, self::$_builtInFormats)) { return self::$_builtInFormats[$pIndex]; } return ''; }
/** * Generate row * * @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet * @param array $pValues Array containing cells in a row * @param int $pRow Row number * @return string * @throws Exception */ private function _generateRow(PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0) { if (is_array($pValues)) { // Construct HTML $html = ''; // Sheet index $sheetIndex = $pSheet->getParent()->getIndex($pSheet); // Write row start if (!$this->_useInlineCss) { $html .= ' <tr class="row' . $pRow . '">' . "\r\n"; } else { $style = isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) ? $this->_assembleCSS($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) : ''; $html .= ' <tr style="' . $style . '">' . "\r\n"; } // Write cells $colNum = 0; foreach ($pValues as $cell) { if (!$this->_useInlineCss) { $cssClass = ''; $cssClass = 'column' . $colNum; } else { $cssClass = array(); if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum])) { $this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum]; } } $colSpan = 1; $rowSpan = 1; $writeCell = true; // Write cell // initialize $cellData = ''; // PHPExcel_Cell if ($cell instanceof PHPExcel_Cell) { // Value if ($cell->getValue() instanceof PHPExcel_RichText) { // Loop trough rich text elements $elements = $cell->getValue()->getRichTextElements(); foreach ($elements as $element) { // Rich text start? if ($element instanceof PHPExcel_RichText_Run) { $cellData .= '<span style="' . $this->_assembleCSS($this->_createCSSStyleFont($element->getFont())) . '">'; if ($element->getFont()->getSuperScript()) { $cellData .= '<sup>'; } else { if ($element->getFont()->getSubScript()) { $cellData .= '<sub>'; } } } // Convert UTF8 data to PCDATA $cellText = $element->getText(); $cellData .= htmlspecialchars($cellText); if ($element instanceof PHPExcel_RichText_Run) { if ($element->getFont()->getSuperScript()) { $cellData .= '</sup>'; } else { if ($element->getFont()->getSubScript()) { $cellData .= '</sub>'; } } $cellData .= '</span>'; } } } else { if ($this->_preCalculateFormulas) { $cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode()); } else { $cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode()); } // Convert UTF8 data to PCDATA $cellData = htmlspecialchars($cellData); } // replace leading spaces on each line with $cellData = $this->_convertNbsp($cellData); // convert newline "\n" to '<br>' $cellData = str_replace("\n", '<br/>', $cellData); // Check value if ($cellData == '') { $cellData = ' '; } // Extend CSS class? if (!$this->_useInlineCss) { $cssClass .= ' style' . $cell->getXfIndex(); $cssClass .= ' ' . $cell->getDataType(); } else { if (isset($this->_cssStyles['td.style' . $cell->getXfIndex()])) { $cssClass = array_merge($cssClass, $this->_cssStyles['td.style' . $cell->getXfIndex()]); } // General horizontal alignment: Actual horizontal alignment depends on dataType $sharedStyle = $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex()); if ($sharedStyle->getAlignment()->getHorizontal() == PHPExcel_Style_Alignment::HORIZONTAL_GENERAL && isset($this->_cssStyles['.' . $cell->getDataType()]['text-align'])) { $cssClass['text-align'] = $this->_cssStyles['.' . $cell->getDataType()]['text-align']; } } } else { $cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', PHPExcel_Cell_DataType::TYPE_NULL, $pSheet); } // Hyperlink? if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) { $cellData = '<a href="' . htmlspecialchars($cell->getHyperlink()->getUrl()) . '" title="' . htmlspecialchars($cell->getHyperlink()->getTooltip()) . '">' . $cellData . '</a>'; } // Column/rowspan foreach ($pSheet->getMergeCells() as $cells) { if ($cell->isInRange($cells)) { list($first, ) = PHPExcel_Cell::splitRange($cells); if ($first[0] == $cell->getCoordinate()) { list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells); } else { $writeCell = false; } break; } } // Write if ($writeCell) { // Column start $html .= ' <td'; if (!$this->_useInlineCss) { $html .= ' class="' . $cssClass . '"'; } else { //** Necessary redundant code for the sake of PHPExcel_Writer_PDF ** // We must explicitly write the width of the <td> element because TCPDF // does not recognize e.g. <col style="width:42pt"> $width = 0; $columnIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1; for ($i = $columnIndex; $i < $columnIndex + $colSpan; ++$i) { if (isset($this->_columnWidths[$sheetIndex][$i])) { $width += $this->_columnWidths[$sheetIndex][$i]; } } $cssClass['width'] = $width . 'pt'; // We must also explicitly write the height of the <td> element because TCPDF // does not recognize e.g. <tr style="height:50pt"> if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height'])) { $height = $this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height']; $cssClass['height'] = $height; } //** end of redundant code ** $html .= ' style="' . $this->_assembleCSS($cssClass) . '"'; } if ($colSpan > 1) { $html .= ' colspan="' . $colSpan . '"'; } if ($rowSpan > 1) { $html .= ' rowspan="' . $rowSpan . '"'; } $html .= '>'; // Image? $html .= $this->_writeImageTagInCell($pSheet, $cell->getCoordinate()); // Cell data $html .= $cellData; // Column end $html .= '</td>' . "\r\n"; } // Next column ++$colNum; } // Write row end $html .= ' </tr>' . "\r\n"; // Return return $html; } else { throw new Exception("Invalid parameters passed."); } }
public function format_date($date) { return PHPExcel_Style_NumberFormat::toFormattedString($date, 'YYYY-MM-DD'); }
/** * TEXTFORMAT * * @param mixed $value Value to check * @return boolean */ public static function TEXTFORMAT($value, $format) { $value = self::flattenSingleValue($value); $format = self::flattenSingleValue($format); return (string) PHPExcel_Style_NumberFormat::toFormattedString($value, $format); }
/** * Get cell value with formatting * * @return string */ public function getFormattedValue() { return (string) PHPExcel_Style_NumberFormat::toFormattedString($this->getCalculatedValue(), $this->_parent->getParent()->getCellXfByIndex($this->getXfIndex())->getNumberFormat()->getFormatCode()); }
/** * Fill built-in format codes */ private static function fillBuiltInFormatCodes() { // Built-in format codes if (is_null(self::$_builtInFormats)) { self::$_builtInFormats = array(); // General self::$_builtInFormats[0] = PHPExcel_Style_NumberFormat::FORMAT_GENERAL; self::$_builtInFormats[1] = '0'; self::$_builtInFormats[2] = '0.00'; self::$_builtInFormats[3] = '#,##0'; self::$_builtInFormats[4] = '#,##0.00'; self::$_builtInFormats[9] = '0%'; self::$_builtInFormats[10] = '0.00%'; self::$_builtInFormats[11] = '0.00E+00'; self::$_builtInFormats[12] = '# ?/?'; self::$_builtInFormats[13] = '# ??/??'; self::$_builtInFormats[14] = 'mm-dd-yy'; self::$_builtInFormats[15] = 'd-mmm-yy'; self::$_builtInFormats[16] = 'd-mmm'; self::$_builtInFormats[17] = 'mmm-yy'; self::$_builtInFormats[18] = 'h:mm AM/PM'; self::$_builtInFormats[19] = 'h:mm:ss AM/PM'; self::$_builtInFormats[20] = 'h:mm'; self::$_builtInFormats[21] = 'h:mm:ss'; self::$_builtInFormats[22] = 'm/d/yy h:mm'; self::$_builtInFormats[37] = '#,##0 ;(#,##0)'; self::$_builtInFormats[38] = '#,##0 ;[Red](#,##0)'; self::$_builtInFormats[39] = '#,##0.00;(#,##0.00)'; self::$_builtInFormats[40] = '#,##0.00;[Red](#,##0.00)'; self::$_builtInFormats[44] = '_("$"* #,##0.00_);_("$"* \\(#,##0.00\\);_("$"* "-"??_);_(@_)'; self::$_builtInFormats[45] = 'mm:ss'; self::$_builtInFormats[46] = '[h]:mm:ss'; self::$_builtInFormats[47] = 'mmss.0'; self::$_builtInFormats[48] = '##0.0E+0'; self::$_builtInFormats[49] = '@'; // CHT self::$_builtInFormats[27] = '[$-404]e/m/d'; self::$_builtInFormats[30] = 'm/d/yy'; self::$_builtInFormats[36] = '[$-404]e/m/d'; self::$_builtInFormats[50] = '[$-404]e/m/d'; self::$_builtInFormats[57] = '[$-404]e/m/d'; // THA self::$_builtInFormats[59] = 't0'; self::$_builtInFormats[60] = 't0.00'; self::$_builtInFormats[61] = 't#,##0'; self::$_builtInFormats[62] = 't#,##0.00'; self::$_builtInFormats[67] = 't0%'; self::$_builtInFormats[68] = 't0.00%'; self::$_builtInFormats[69] = 't# ?/?'; self::$_builtInFormats[70] = 't# ??/??'; // Flip array (for faster lookups) self::$_flippedBuiltInFormats = array_flip(self::$_builtInFormats); } }
/** * TEXTFORMAT * * @param mixed $value Value to check * @return boolean */ public static function TEXTFORMAT($value, $format) { $value = self::flattenSingleValue($value); $format = self::flattenSingleValue($format); if (is_string($value) && !is_numeric($value) && PHPExcel_Shared_Date::isDateTimeFormatCode($format)) { $value = self::DATEVALUE($value); } return (string) PHPExcel_Style_NumberFormat::toFormattedString($value, $format); }
/** * Write row to HTML file * * @param mixed $pFileHandle PHP filehandle * @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet * @param array $pValues Array containing cells in a row * @param int $pRow Row number * @throws Exception */ private function _writeRow($pFileHandle = null, PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0) { if (!is_null($pFileHandle) && is_array($pValues)) { // Write row start fwrite($pFileHandle, ' <tr class="row' . $pRow . '">' . "\r\n"); // Write cells $colNum = 0; foreach ($pValues as $cell) { $cellData = ' '; $cssClass = 'column' . $colNum; $colSpan = 1; $rowSpan = 1; $writeCell = true; // Write cell // PHPExcel_Cell if ($cell instanceof PHPExcel_Cell) { // Value if ($cell->getValue() instanceof PHPExcel_RichText) { // Loop trough rich text elements $elements = $cell->getValue()->getRichTextElements(); foreach ($elements as $element) { // Rich text start? if ($element instanceof PHPExcel_RichText_Run) { $cellData .= '<span style="' . str_replace("\r\n", '', $this->_createCSSStyleFont($element->getFont())) . '">'; } $cellData .= $element->getText(); if ($element instanceof PHPExcel_RichText_Run) { $cellData .= '</span>'; } } } else { if ($this->_preCalculateFormulas) { $cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode()); } else { $cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode()); } } // Check value if ($cellData == '') { $cellData = ' '; } // Extend CSS class? if (array_key_exists($cell->getCoordinate(), $pSheet->getStyles())) { $cssClass .= ' style' . $pSheet->getStyle($cell->getCoordinate())->getHashCode(); } } else { $cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', null, null); } // Hyperlink? if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) { $cellData = '<a href="' . $cell->getHyperlink()->getUrl() . '" title="' . $cell->getHyperlink()->getTooltip() . '">' . $cellData . '</a>'; } // Column/rowspan foreach ($pSheet->getMergeCells() as $cells) { if ($cell->isInRange($cells)) { list($first, ) = PHPExcel_Cell::splitRange($cells); if ($first == $cell->getCoordinate()) { list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells); } else { $writeCell = false; } break; } } // Write if ($writeCell) { // Column start fwrite($pFileHandle, ' <td'); fwrite($pFileHandle, ' class="' . $cssClass . '"'); if ($colSpan > 1) { fwrite($pFileHandle, ' colspan="' . $colSpan . '"'); } if ($rowSpan > 1) { fwrite($pFileHandle, ' rowspan="' . $rowSpan . '"'); } fwrite($pFileHandle, '>'); // Image? $this->_writeImageInCell($pFileHandle, $pSheet, $cell->getCoordinate()); // Cell data fwrite($pFileHandle, $cellData); // Column end fwrite($pFileHandle, '</td>' . "\r\n"); } // Next column $colNum++; } // Write row end fwrite($pFileHandle, ' </tr>' . "\r\n"); } else { throw new Exception("Invalid parameters passed."); } }
/** * Create array from a range of cells * * @param string $pRange Range of cells (i.e. "A1:B10"), or just one cell (i.e. "A1") * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist * @param boolean $calculateFormulas Should formulas be calculated? * @param boolean $formatData Should formatting be applied to cell values? * @param boolean $returnCellRef False - Return a simple array of rows and columns indexed by number counting from zero * True - Return rows and columns indexed by their actual row and column IDs * @return array */ public function rangeToArray($pRange = 'A1', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) { // Returnvalue $returnValue = array(); // Identify the range that we need to extract from the worksheet list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($pRange); $minCol = PHPExcel_Cell::stringFromColumnIndex($rangeStart[0] - 1); $minRow = $rangeStart[1]; $maxCol = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0] - 1); $maxRow = $rangeEnd[1]; $maxCol++; // Loop through rows $r = -1; for ($row = $minRow; $row <= $maxRow; ++$row) { $rRef = $returnCellRef ? $row : ++$r; $c = -1; // Loop through columns in the current row for ($col = $minCol; $col != $maxCol; ++$col) { $cRef = $returnCellRef ? $col : ++$c; // Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen // so we test and retrieve directly against _cellCollection if ($this->_cellCollection->isDataSet($col . $row)) { // Cell exists $cell = $this->_cellCollection->getCacheData($col . $row); if ($cell->getValue() !== null) { if ($cell->getValue() instanceof PHPExcel_RichText) { $returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText(); } else { if ($calculateFormulas) { $returnValue[$rRef][$cRef] = $cell->getCalculatedValue(); } else { $returnValue[$rRef][$cRef] = $cell->getValue(); } } if ($formatData) { $style = $this->_parent->getCellXfByIndex($cell->getXfIndex()); $returnValue[$rRef][$cRef] = PHPExcel_Style_NumberFormat::toFormattedString($returnValue[$rRef][$cRef], $style && $style->getNumberFormat() ? $style->getNumberFormat()->getFormatCode() : PHPExcel_Style_NumberFormat::FORMAT_GENERAL); } } else { // Cell holds a NULL $returnValue[$rRef][$cRef] = $nullValue; } } else { // Cell doesn't exist $returnValue[$rRef][$cRef] = $nullValue; } } } // Return return $returnValue; }
/** * Is a given number format a date/time? * * @param PHPExcel_Style_NumberFormat $pFormat * @return boolean */ public static function isDateTimeFormat(PHPExcel_Style_NumberFormat $pFormat) { return self::isDateTimeFormatCode($pFormat->getFormatCode()); }
/** * Generate row * * @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet * @param array $pValues Array containing cells in a row * @param int $pRow Row number (0-based) * @return string * @throws PHPExcel_Writer_Exception */ private function _generateRow(PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0) { if (is_array($pValues)) { // Construct HTML $html = ''; // Sheet index $sheetIndex = $pSheet->getParent()->getIndex($pSheet); // DomPDF and breaks if ($this->_isPdf && count($pSheet->getBreaks()) > 0) { $breaks = $pSheet->getBreaks(); // check if a break is needed before this row if (isset($breaks['A' . $pRow])) { // close table: </table> $html .= $this->_generateTableFooter(); // insert page break $html .= '<div style="page-break-before:always" />'; // open table again: <table> + <col> etc. $html .= $this->_generateTableHeader($pSheet); } } // Write row start if (!$this->_useInlineCss) { $html .= ' <tr class="row' . $pRow . '">' . PHP_EOL; } else { $style = isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) ? $this->_assembleCSS($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) : ''; $html .= ' <tr style="' . $style . '">' . PHP_EOL; } // Write cells $colNum = 0; foreach ($pValues as $cell) { $coordinate = PHPExcel_Cell::stringFromColumnIndex($colNum) . ($pRow + 1); if (!$this->_useInlineCss) { $cssClass = ''; $cssClass = 'column' . $colNum; } else { $cssClass = array(); if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum])) { $this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum]; } } $colSpan = 1; $rowSpan = 1; // initialize $cellData = ' '; // PHPExcel_Cell if ($cell instanceof PHPExcel_Cell) { $cellData = ''; if (is_null($cell->getParent())) { $cell->attach($pSheet); } // Value if ($cell->getValue() instanceof PHPExcel_RichText) { // Loop through rich text elements $elements = $cell->getValue()->getRichTextElements(); foreach ($elements as $element) { // Rich text start? if ($element instanceof PHPExcel_RichText_Run) { $cellData .= '<span style="' . $this->_assembleCSS($this->_createCSSStyleFont($element->getFont())) . '">'; if ($element->getFont()->getSuperScript()) { $cellData .= '<sup>'; } else { if ($element->getFont()->getSubScript()) { $cellData .= '<sub>'; } } } // Convert UTF8 data to PCDATA $cellText = $element->getText(); $cellData .= htmlspecialchars($cellText); if ($element instanceof PHPExcel_RichText_Run) { if ($element->getFont()->getSuperScript()) { $cellData .= '</sup>'; } else { if ($element->getFont()->getSubScript()) { $cellData .= '</sub>'; } } $cellData .= '</span>'; } } } else { if ($this->_preCalculateFormulas) { $cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode(), array($this, 'formatColor')); } else { $cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode(), array($this, 'formatColor')); } $cellData = htmlspecialchars($cellData); if ($pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont()->getSuperScript()) { $cellData = '<sup>' . $cellData . '</sup>'; } elseif ($pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont()->getSubScript()) { $cellData = '<sub>' . $cellData . '</sub>'; } } // Converts the cell content so that spaces occuring at beginning of each new line are replaced by // Example: " Hello\n to the world" is converted to " Hello\n to the world" $cellData = preg_replace("/(?m)(?:^|\\G) /", ' ', $cellData); // convert newline "\n" to '<br>' $cellData = nl2br($cellData); // Extend CSS class? if (!$this->_useInlineCss) { $cssClass .= ' style' . $cell->getXfIndex(); $cssClass .= ' ' . $cell->getDataType(); } else { if (isset($this->_cssStyles['td.style' . $cell->getXfIndex()])) { $cssClass = array_merge($cssClass, $this->_cssStyles['td.style' . $cell->getXfIndex()]); } // General horizontal alignment: Actual horizontal alignment depends on dataType $sharedStyle = $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex()); if ($sharedStyle->getAlignment()->getHorizontal() == PHPExcel_Style_Alignment::HORIZONTAL_GENERAL && isset($this->_cssStyles['.' . $cell->getDataType()]['text-align'])) { $cssClass['text-align'] = $this->_cssStyles['.' . $cell->getDataType()]['text-align']; } } } // Hyperlink? if ($pSheet->hyperlinkExists($coordinate) && !$pSheet->getHyperlink($coordinate)->isInternal()) { $cellData = '<a href="' . htmlspecialchars($pSheet->getHyperlink($coordinate)->getUrl()) . '" title="' . htmlspecialchars($pSheet->getHyperlink($coordinate)->getTooltip()) . '">' . $cellData . '</a>'; } // Should the cell be written or is it swallowed by a rowspan or colspan? $writeCell = !(isset($this->_isSpannedCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum]) && $this->_isSpannedCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum]); // Colspan and Rowspan $colspan = 1; $rowspan = 1; if (isset($this->_isBaseCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum])) { $spans = $this->_isBaseCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum]; $rowSpan = $spans['rowspan']; $colSpan = $spans['colspan']; // Also apply style from last cell in merge to fix borders - // relies on !important for non-none border declarations in _createCSSStyleBorder $endCellCoord = PHPExcel_Cell::stringFromColumnIndex($colNum + $colSpan - 1) . ($pRow + $rowSpan); $cssClass .= ' style' . $pSheet->getCell($endCellCoord)->getXfIndex(); } // Write if ($writeCell) { // Column start $html .= ' <td'; if (!$this->_useInlineCss) { $html .= ' class="' . $cssClass . '"'; } else { //** Necessary redundant code for the sake of PHPExcel_Writer_PDF ** // We must explicitly write the width of the <td> element because TCPDF // does not recognize e.g. <col style="width:42pt"> $width = 0; $i = $colNum - 1; $e = $colNum + $colSpan - 1; while ($i++ < $e) { if (isset($this->_columnWidths[$sheetIndex][$i])) { $width += $this->_columnWidths[$sheetIndex][$i]; } } $cssClass['width'] = $width . 'pt'; // We must also explicitly write the height of the <td> element because TCPDF // does not recognize e.g. <tr style="height:50pt"> if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height'])) { $height = $this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height']; $cssClass['height'] = $height; } //** end of redundant code ** $html .= ' style="' . $this->_assembleCSS($cssClass) . '"'; } if ($colSpan > 1) { $html .= ' colspan="' . $colSpan . '"'; } if ($rowSpan > 1) { $html .= ' rowspan="' . $rowSpan . '"'; } $html .= '>'; // Image? $html .= $this->_writeImageInCell($pSheet, $coordinate); // Chart? if ($this->_includeCharts) { $html .= $this->_writeChartInCell($pSheet, $coordinate); } // Cell data $html .= $cellData; // Column end $html .= '</td>' . PHP_EOL; } // Next column ++$colNum; } // Write row end $html .= ' </tr>' . PHP_EOL; // Return return $html; } else { throw new PHPExcel_Writer_Exception("Invalid parameters passed."); } }
/** * Create array from worksheet * * @param mixed $nullValue Value treated as "null" * @param boolean $calculateFormulas Should formulas be calculated? * @return array */ public function toArray($nullValue = null, $calculateFormulas = true) { // Returnvalue $returnValue = array(); // Garbage collect... $this->garbageCollect(); // Get worksheet dimension $dimension = explode(':', $this->calculateWorksheetDimension()); $dimension[0] = PHPExcel_Cell::coordinateFromString($dimension[0]); $dimension[0][0] = PHPExcel_Cell::columnIndexFromString($dimension[0][0]) - 1; $dimension[1] = PHPExcel_Cell::coordinateFromString($dimension[1]); $dimension[1][0] = PHPExcel_Cell::columnIndexFromString($dimension[1][0]) - 1; // Loop trough cells for ($row = $dimension[0][1]; $row <= $dimension[1][1]; $row++) { for ($column = $dimension[0][0]; $column <= $dimension[1][0]; $column++) { // Cell exists? if ($this->cellExistsByColumnAndRow($column, $row)) { $cell = $this->getCellByColumnAndRow($column, $row); if ($cell->getValue() instanceof PHPExcel_RichText) { $returnValue[$row][$column] = $cell->getValue()->getPlainText(); } else { if ($calculateFormulas) { $returnValue[$row][$column] = $cell->getCalculatedValue(); } else { $returnValue[$row][$column] = $cell->getValue(); } } $style = $this->getDefaultStyle(); if (isset($this->_styles[$cell->getCoordinate()])) { $style = $this->getStyleByColumnAndRow($column, $row); } $returnValue[$row][$column] = PHPExcel_Style_NumberFormat::toFormattedString($returnValue[$row][$column], $style->getNumberFormat()->getFormatCode()); } else { $returnValue[$row][$column] = $nullValue; } } } // Return return $returnValue; }
/** * Reads a general type of BIFF record. Does nothing except for moving stream pointer forward to next record. */ private function _readDefault() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); // $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; } /** * The NOTE record specifies a comment associated with a particular cell. In Excel 95 (BIFF7) and earlier versions, * this record stores a note (cell note). This feature was significantly enhanced in Excel 97. */ private function _readNote() { // echo '<b>Read Cell Annotation</b><br>'; $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; if ($this->_readDataOnly) { return; } $cellAddress = $this->_readBIFF8CellAddress(substr($recordData, 0, 4)); if ($this->_version == self::XLS_BIFF8) { $noteObjID = self::_GetInt2d($recordData, 6); $noteAuthor = self::_readUnicodeStringLong(substr($recordData, 8)); $noteAuthor = $noteAuthor['value']; // echo 'Note Address=',$cellAddress,'<br>'; // echo 'Note Object ID=',$noteObjID,'<br>'; // echo 'Note Author=',$noteAuthor,'<hr />'; // $this->_cellNotes[$noteObjID] = array('cellRef' => $cellAddress, 'objectID' => $noteObjID, 'author' => $noteAuthor); } else { $extension = false; if ($cellAddress == '$B$65536') { // If the address row is -1 and the column is 0, (which translates as $B$65536) then this is a continuation // note from the previous cell annotation. We're not yet handling this, so annotations longer than the // max 2048 bytes will probably throw a wobbly. $row = self::_GetInt2d($recordData, 0); $extension = true; $cellAddress = array_pop(array_keys($this->_phpSheet->getComments())); } // echo 'Note Address=',$cellAddress,'<br>'; $cellAddress = str_replace('$', '', $cellAddress); $noteLength = self::_GetInt2d($recordData, 4); $noteText = trim(substr($recordData, 6)); // echo 'Note Length=',$noteLength,'<br>'; // echo 'Note Text=',$noteText,'<br>'; if ($extension) { // Concatenate this extension with the currently set comment for the cell $comment = $this->_phpSheet->getComment($cellAddress); $commentText = $comment->getText()->getPlainText(); $comment->setText($this->_parseRichText($commentText . $noteText)); } else { // Set comment for the cell $this->_phpSheet->getComment($cellAddress)->setText($this->_parseRichText($noteText)); } } } /** * The TEXT Object record contains the text associated with a cell annotation. */ private function _readTextObject() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; if ($this->_readDataOnly) { return; } // recordData consists of an array of subrecords looking like this: // grbit: 2 bytes; Option Flags // rot: 2 bytes; rotation // cchText: 2 bytes; length of the text (in the first continue record) // cbRuns: 2 bytes; length of the formatting (in the second continue record) // followed by the continuation records containing the actual text and formatting $grbitOpts = self::_GetInt2d($recordData, 0); $rot = self::_GetInt2d($recordData, 2); $cchText = self::_GetInt2d($recordData, 10); $cbRuns = self::_GetInt2d($recordData, 12); $text = $this->_getSplicedRecordData(); $this->_textObjects[$this->textObjRef] = array('text' => substr($text["recordData"], $text["spliceOffsets"][0] + 1, $cchText), 'format' => substr($text["recordData"], $text["spliceOffsets"][1], $cbRuns), 'alignment' => $grbitOpts, 'rotation' => $rot); // echo '<b>_readTextObject()</b><br>'; // var_dump($this->_textObjects[$this->textObjRef]); // echo '<br>'; } /** * Read BOF */ private function _readBof() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; // offset: 2; size: 2; type of the following data $substreamType = self::_GetInt2d($recordData, 2); switch ($substreamType) { case self::XLS_WorkbookGlobals: $version = self::_GetInt2d($recordData, 0); if ($version != self::XLS_BIFF8 && $version != self::XLS_BIFF7) { throw new Exception('Cannot read this Excel file. Version is too old.'); } $this->_version = $version; break; case self::XLS_Worksheet: // do not use this version information for anything // it is unreliable (OpenOffice doc, 5.8), use only version information from the global stream break; default: // substream, e.g. chart // just skip the entire substream do { $code = self::_GetInt2d($this->_data, $this->_pos); $this->_readDefault(); } while ($code != self::XLS_Type_EOF && $this->_pos < $this->_dataSize); break; } } /** * FILEPASS * * This record is part of the File Protection Block. It * contains information about the read/write password of the * file. All record contents following this record will be * encrypted. * * -- "OpenOffice.org's Documentation of the Microsoft * Excel File Format" */ private function _readFilepass() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); // $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; throw new Exception('Cannot read encrypted file'); } /** * CODEPAGE * * This record stores the text encoding used to write byte * strings, stored as MS Windows code page identifier. * * -- "OpenOffice.org's Documentation of the Microsoft * Excel File Format" */ private function _readCodepage() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; // offset: 0; size: 2; code page identifier $codepage = self::_GetInt2d($recordData, 0); $this->_codepage = PHPExcel_Shared_CodePage::NumberToName($codepage); } /** * DATEMODE * * This record specifies the base date for displaying date * values. All dates are stored as count of days past this * base date. In BIFF2-BIFF4 this record is part of the * Calculation Settings Block. In BIFF5-BIFF8 it is * stored in the Workbook Globals Substream. * * -- "OpenOffice.org's Documentation of the Microsoft * Excel File Format" */ private function _readDateMode() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; // offset: 0; size: 2; 0 = base 1900, 1 = base 1904 PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900); if (ord($recordData[0]) == 1) { PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904); } } /** * Read a FONT record */ private function _readFont() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; if (!$this->_readDataOnly) { $objFont = new PHPExcel_Style_Font(); // offset: 0; size: 2; height of the font (in twips = 1/20 of a point) $size = self::_GetInt2d($recordData, 0); $objFont->setSize($size / 20); // offset: 2; size: 2; option flags // bit: 0; mask 0x0001; bold (redundant in BIFF5-BIFF8) // bit: 1; mask 0x0002; italic $isItalic = (0x2 & self::_GetInt2d($recordData, 2)) >> 1; if ($isItalic) { $objFont->setItalic(true); } // bit: 2; mask 0x0004; underlined (redundant in BIFF5-BIFF8) // bit: 3; mask 0x0008; strike $isStrike = (0x8 & self::_GetInt2d($recordData, 2)) >> 3; if ($isStrike) { $objFont->setStrikethrough(true); } // offset: 4; size: 2; colour index $colorIndex = self::_GetInt2d($recordData, 4); $objFont->colorIndex = $colorIndex; // offset: 6; size: 2; font weight $weight = self::_GetInt2d($recordData, 6); switch ($weight) { case 0x2bc: $objFont->setBold(true); break; } // offset: 8; size: 2; escapement type $escapement = self::_GetInt2d($recordData, 8); switch ($escapement) { case 0x1: $objFont->setSuperScript(true); break; case 0x2: $objFont->setSubScript(true); break; } // offset: 10; size: 1; underline type $underlineType = ord($recordData[10]); switch ($underlineType) { case 0x0: break; // no underline // no underline case 0x1: