/** * 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; }
function addImageFooter($objPHPExcel, $index) { $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); $objDrawing->setName("name"); $objDrawing->setDescription("Description"); $objDrawing->setPath('../img/LogoBSW.png'); $objDrawing->setCoordinates("A" . $index); $objDrawing->setOffsetX(10); $objDrawing->setWidth(20); $objDrawing->setHeight(20); }
private function _excel_add_image($sheet, $image, &$i) { if (!(string) $image) { return --$i ? '' : ''; } download_web_file($image->url('350x230p'), $filepath = FCPATH . implode(DIRECTORY_SEPARATOR, array_merge(Cfg::system('orm_uploader', 'uploader', 'temp_directory'), array((string) $image)))); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setPath($filepath); $j = $j = (ceil($i / 3) - 1) * 7 + 1; $objDrawing->setCoordinates(($i % 3 < 2 ? $i % 3 < 1 ? 'G' : 'A' : 'D') . $j); $objDrawing->setOffsetX(20); $objDrawing->setOffsetY(8); $objDrawing->setWidth(175); $objDrawing->setWorksheet($sheet); return $filepath; }
// Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); $sharedStyle1 = new PHPExcel_Style(); $sharedStyle2 = new PHPExcel_Style(); $sharedStyle1->applyFromArray(array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM), 'left' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)))); $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A12:I{$nox}"); // Set style for header row using alternative method $objPHPExcel->getActiveSheet()->getStyle('A12:I12')->applyFromArray(array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 90, 'startcolor' => array('argb' => 'FFA0A0A0'), 'endcolor' => array('argb' => 'FFFFFFFF')))); // Add a drawing to the worksheet $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('Logo'); $objDrawing->setDescription('Logo'); $objDrawing->setPath('images/logo_smk.png'); $objDrawing->setCoordinates('D2'); $objDrawing->setHeight(100); $objDrawing->setWidth(100); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); //untuk font dan size data $objPHPExcel->getActiveSheet()->getStyle('A12:I1000')->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getStyle('A12:I1000')->getFont()->setSize(9); // Mulai Merge cells Judul $objPHPExcel->getActiveSheet()->mergeCells('D2:I2'); $objPHPExcel->getActiveSheet()->setCellValue('D2', "DAFTAR DATA SISWA"); $objPHPExcel->getActiveSheet()->mergeCells('D3:I3'); $objPHPExcel->getActiveSheet()->setCellValue('D3', "SMK NEGERI 1 PAKONG"); $objPHPExcel->getActiveSheet()->getStyle('D2:I5')->getFont()->setName('Arial'); $objPHPExcel->getActiveSheet()->getStyle('D2:I5')->getFont()->setSize(14); $objPHPExcel->getActiveSheet()->getStyle('D2:I5')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('D3:I5')->getFont()->setSize(13); // untuk sub judul $objPHPExcel->getActiveSheet()->setCellValue('I7', "JUMLAH DATA : {$jumlah}");
/** * 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); $zip = new ZipArchive(); $zip->open($pFilename); $rels = simplexml_load_string($zip->getFromName("_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($zip->getFromName("{$rel['Target']}")); $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($zip->getFromName("{$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($zip->getFromName("{$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(); $xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']")); $xmlStyles = simplexml_load_string($zip->getFromName("{$dir}/{$xpath['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main"); $numFmts = $xmlStyles->numFmts[0]; if ($numFmts) { $numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main"); } if (!$this->_readDataOnly) { foreach ($xmlStyles->cellXfs->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"]); } } } //$numFmt = str_replace('mm', 'i', $numFmt); //$numFmt = str_replace('h', 'H', $numFmt); $styles[] = (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); } } $dxfs = array(); if (!$this->_readDataOnly) { foreach ($xmlStyles->dxfs->dxf as $dxf) { $style = new PHPExcel_Style(); $this->_readStyle($style, $dxf); $dxfs[] = $style; } } $xmlWorkbook = simplexml_load_string($zip->getFromName("{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main"); $sheetId = 0; foreach ($xmlWorkbook->sheets->sheet as $eleSheet) { $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($zip->getFromName("{$dir}/{$fileWorksheet}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main"); $sharedFormulas = array(); if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) { if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) { $docSheet->setShowGridLines($xmlSheet->sheetViews->sheetView['showGridLines'] ? true : false); } } 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->sheetFormatPr)) { if (isset($xmlSheet->sheetFormatPr['customHeight']) && ((string) $xmlSheet->sheetFormatPr['customHeight'] == '1' || strtolower((string) $xmlSheet->sheetFormatPr['customHeight']) == 'true') && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) { $docSheet->getDefaultRowDimension()->setRowHeight((double) $xmlSheet->sheetFormatPr['defaultRowHeight']); } if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) { $docSheet->getDefaultColumnDimension()->setWidth((double) $xmlSheet->sheetFormatPr['defaultColWidth']); } } if (isset($xmlSheet->cols) && !$this->_readDataOnly) { foreach ($xmlSheet->cols->col as $col) { for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); $i++) { if ($col["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); } } 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"])); } foreach ($row->c as $c) { $r = (string) $c["r"]; switch ($c["t"]) { case "s": if ((string) $c->v != '') { $value = $sharedStrings[intval($c->v)]; if ($value instanceof PHPExcel_RichText) { $value = clone $value; } } else { $value = ''; } break; case "b": $value = (string) $c->v; if ($value == '0') { $value = false; } else { if ($value == '1') { $value = true; } else { $value = (bool) $c->v; } } break; case "inlineStr": $value = $this->_parseRichText($c->is); $value->setParent($docSheet->getCell($r)); break; default: if (!isset($c->f)) { $value = (string) $c->v; } else { // Formula $value = "={$c->f}"; // Shared formula? if (isset($c->f['t']) && strtolower((string) $c->f['t']) == 'shared') { $instance = (string) $c->f['si']; if (!isset($sharedFormulas[(string) $c->f['si']])) { $sharedFormulas[$instance] = array('master' => $r, 'formula' => $value); } else { $master = PHPExcel_Cell::coordinateFromString($sharedFormulas[$instance]['master']); $current = PHPExcel_Cell::coordinateFromString($r); $difference = array(0, 0); $difference[0] = PHPExcel_Cell::columnIndexFromString($current[0]) - PHPExcel_Cell::columnIndexFromString($master[0]); $difference[1] = $current[1] - $master[1]; $helper = PHPExcel_ReferenceHelper::getInstance(); $x = $helper->updateFormulaReferences($sharedFormulas[$instance]['formula'], 'A1', $difference[0], $difference[1]); $value = $x; } } } break; } // Check for numeric values if (is_numeric($value)) { if ($value == (int) $value) { $value = (int) $value; } elseif ($value == (double) $value) { $value = (double) $value; } elseif ($value == (double) $value) { $value = (double) $value; } } $docSheet->setCellValue($r, $value); if ($c["s"] && !$this->_readDataOnly) { if (isset($styles[intval($c["s"])])) { $this->_readStyle($docSheet->getStyle($r), $styles[intval($c["s"])]); } if (PHPExcel_Shared_Date::isDateTimeFormat($docSheet->getStyle($r)->getNumberFormat())) { if (preg_match("/^([0-9.,-]+)\$/", $value)) { $docSheet->setCellValue($r, PHPExcel_Shared_Date::ExcelToPHP($value)); } } } } } $conditionals = array(); if (!$this->_readDataOnly) { 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) && 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"]); $objConditional->setCondition((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) { foreach ($aKeys as $key) { $method = "set" . ucfirst($key); $docSheet->getProtection()->{$method}($xmlSheet->sheetProtection[$key] == "true"); } } if (!$this->_readDataOnly) { $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->autoFilter && !$this->_readDataOnly) { $docSheet->setAutoFilter((string) $xmlSheet->autoFilter["ref"]); } if ($xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) { foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) { $docSheet->mergeCells((string) $mergeCell["ref"]); } } if (!$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 (!$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"])); } if (isset($xmlSheet->pageSetup["fitToHeight"])) { $docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"])); } if (isset($xmlSheet->pageSetup["fitToWidth"])) { $docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"])); } } if (!$this->_readDataOnly) { $docHeaderFooter = $docSheet->getHeaderFooter(); $docHeaderFooter->setDifferentOddEven($xmlSheet->headerFooter["differentOddEven"] == 'true'); $docHeaderFooter->setDifferentFirst($xmlSheet->headerFooter["differentFirst"] == 'true'); $docHeaderFooter->setScaleWithDocument($xmlSheet->headerFooter["scaleWithDoc"] == 'true'); $docHeaderFooter->setAlignWithMargins($xmlSheet->headerFooter["alignWithMargins"] == '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->rowBreaks->brk && !$this->_readDataOnly) { foreach ($xmlSheet->rowBreaks->brk as $brk) { if ($brk["man"]) { $docSheet->setBreak("A{$brk['id']}", PHPExcel_Worksheet::BREAK_ROW); } } } if ($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->dataValidations && !$this->_readDataOnly) { foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) { // Uppercase coordinate $range = strtoupper($dataValidation["sqref"]); // Extract all cell references in $range $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($range); 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($zip->getFromName(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 trough hyperlinks if ($xmlSheet->hyperlinks) { foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) { // Link url $linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships'); if (isset($linkRel['id'])) { $docSheet->getCell($hyperlink['ref'])->getHyperlink()->setUrl($hyperlinks[(string) $linkRel['id']]); } if (isset($hyperlink['location'])) { $docSheet->getCell($hyperlink['ref'])->getHyperlink()->setUrl('sheet://' . (string) $hyperlink['location']); } // Tooltip if (isset($hyperlink['tooltip'])) { $docSheet->getCell($hyperlink['ref'])->getHyperlink()->setTooltip((string) $hyperlink['tooltip']); } } } } // Add comments $comments = array(); if (!$this->_readDataOnly) { // Locate comment relations if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($zip->getFromName(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"]; } } } // Loop trough comments foreach ($comments as $relName => $relPath) { // Load comments file $relPath = PHPExcel_Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath); $commentsFile = simplexml_load_string($zip->getFromName($relPath)); // Utility variables $authors = array(); // Loop trough authors foreach ($commentsFile->authors->author as $author) { $authors[] = (string) $author; } // Loop trough 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)); } } // Header/footer images if ($xmlSheet->legacyDrawingHF && !$this->_readDataOnly) { if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($zip->getFromName(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($zip->getFromName(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($zip->getFromName($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); } } } } // TODO: Make sure drawings and graph are loaded differently! if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($zip->getFromName(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($zip->getFromName(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($zip->getFromName($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 trough definedNames if ($xmlWorkbook->definedNames) { foreach ($xmlWorkbook->definedNames->definedName as $definedName) { // Extract range $extractedRange = (string) $definedName; if (strpos($extractedRange, '!') !== false) { $extractedRange = substr($extractedRange, strpos($extractedRange, '!') + 1); } $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 ($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 if (isset($extractedRange[0])) { $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(explode(':', $extractedRange[0])); } if (isset($extractedRange[1])) { $docSheet->getPageSetup()->setRowsToRepeatAtTop(explode(':', $extractedRange[1])); } break; case '_xlnm.Print_Area': $docSheet->getPageSetup()->setPrintArea($extractedRange); break; default: $excel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $docSheet, $extractedRange, true)); break; } } else { // "Global" definedNames $locatedSheet = null; $extractedSheetName = ''; if (strpos((string) $definedName, '!') !== false) { // Extract sheet name $extractedSheetName = PHPExcel_Worksheet::extractSheetTitle((string) $definedName); // Locate sheet $locatedSheet = $excel->getSheetByName($extractedSheetName); } if (!is_null($locatedSheet)) { $excel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $locatedSheet, $extractedRange, false)); } } } } // Garbage collect... $docSheet->garbageCollect(); // Next sheet id $sheetId++; } if (!$this->_readDataOnly) { $excel->setActiveSheetIndex(intval($xmlWorkbook->bookView->workbookView["activeTab"])); } break; } } return $excel; }
$objPHPExcel->getActiveSheet()->getStyle("C5:C5")->getFont()->setBold(false)->setName('Verdana')->setSize(10); ////////////////////////// $objPHPExcel->setActiveSheetIndex(0)->setCellValue("G5", 'HASTA:'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('G5:G5'); $objPHPExcel->getActiveSheet()->getStyle("G5:G5")->getFont()->setBold(false)->setName('Verdana')->setSize(10); ////////////////////////// $objPHPExcel->setActiveSheetIndex(0)->setCellValue("H5", $_GET['fin']); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H5:H5'); $objPHPExcel->getActiveSheet()->getStyle("H5:H5")->getFont()->setBold(false)->setName('Verdana')->setSize(10); ////////////////////////// $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('PHPExcel logo'); $objDrawing->setDescription('PHPExcel logo'); $objDrawing->setPath('../images/logo_empresa.jpg'); // $objDrawing->setWidth(160); // sets the image $objDrawing->setHeight(60); $objDrawing->setCoordinates('L2'); // pins the top-left corner $objDrawing->setOffsetX(10); // pins the top left $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); ////////////////////////////////////////////////////////// $styleArray = array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM))); $objPHPExcel->getActiveSheet()->getStyle('B5:L5')->applyFromArray($styleArray); unset($styleArray); ////////////////////////////////////////////////////////// $total = 0; $sub = 0; $desc = 0;
/** * Loads PHPExcel from file * * @param string $pFilename * @throws PHPExcel_Reader_Exception */ public function load($pFilename) { // Check if file exists if (!file_exists($pFilename)) { throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist."); } // Initialisations $excel = new PHPExcel(); $excel->removeSheetByIndex(0); if (!$this->_readDataOnly) { $excel->removeCellStyleXfByIndex(0); // remove the default style $excel->removeCellXfByIndex(0); // remove the default style } $zip = new ZipArchive(); $zip->open($pFilename); // Read the theme first, because we need the colour scheme when reading the styles $wbRels = simplexml_load_string($this->_getFromZipArchive($zip, "xl/_rels/workbook.xml.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); foreach ($wbRels->Relationship as $rel) { switch ($rel["Type"]) { case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme": $themeOrderArray = array('lt1', 'dk1', 'lt2', 'dk2'); $themeOrderAdditional = count($themeOrderArray); $xmlTheme = simplexml_load_string($this->_getFromZipArchive($zip, "xl/{$rel['Target']}")); if (is_object($xmlTheme)) { $xmlThemeName = $xmlTheme->attributes(); $xmlTheme = $xmlTheme->children("http://schemas.openxmlformats.org/drawingml/2006/main"); $themeName = (string) $xmlThemeName['name']; $colourScheme = $xmlTheme->themeElements->clrScheme->attributes(); $colourSchemeName = (string) $colourScheme['name']; $colourScheme = $xmlTheme->themeElements->clrScheme->children("http://schemas.openxmlformats.org/drawingml/2006/main"); $themeColours = array(); foreach ($colourScheme as $k => $xmlColour) { $themePos = array_search($k, $themeOrderArray); if ($themePos === false) { $themePos = $themeOrderAdditional++; } if (isset($xmlColour->sysClr)) { $xmlColourData = $xmlColour->sysClr->attributes(); $themeColours[$themePos] = $xmlColourData['lastClr']; } elseif (isset($xmlColour->srgbClr)) { $xmlColourData = $xmlColour->srgbClr->attributes(); $themeColours[$themePos] = $xmlColourData['val']; } } self::$_theme = new PHPExcel_Reader_Excel2007_Theme($themeName, $colourSchemeName, $themeColours); } break; } } $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); foreach ($rels->Relationship as $rel) { switch ($rel["Type"]) { case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties": $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); if (is_object($xmlCore)) { $xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/"); $xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/"); $xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties"); $docProps = $excel->getProperties(); $docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator"))); $docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy"))); $docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created")))); //! respect xsi:type $docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified")))); //! respect xsi:type $docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title"))); $docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description"))); $docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject"))); $docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords"))); $docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category"))); } break; case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties": $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); if (is_object($xmlCore)) { $docProps = $excel->getProperties(); if (isset($xmlCore->Company)) { $docProps->setCompany((string) $xmlCore->Company); } if (isset($xmlCore->Manager)) { $docProps->setManager((string) $xmlCore->Manager); } } break; case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties": $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); if (is_object($xmlCore)) { $docProps = $excel->getProperties(); foreach ($xmlCore as $xmlProperty) { $cellDataOfficeAttributes = $xmlProperty->attributes(); if (isset($cellDataOfficeAttributes['name'])) { $propertyName = (string) $cellDataOfficeAttributes['name']; $cellDataOfficeChildren = $xmlProperty->children('http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes'); $attributeType = $cellDataOfficeChildren->getName(); $attributeValue = (string) $cellDataOfficeChildren->{$attributeType}; $attributeValue = PHPExcel_DocumentProperties::convertProperty($attributeValue, $attributeType); $attributeType = PHPExcel_DocumentProperties::convertPropertyType($attributeType); $docProps->setCustomProperty($propertyName, $attributeValue, $attributeType); } } } break; case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument": $dir = dirname($rel["Target"]); $relsWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/_rels/" . basename($rel["Target"]) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); $relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships"); $sharedStrings = array(); $xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']")); $xmlStrings = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/{$xpath['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main"); if (isset($xmlStrings) && isset($xmlStrings->si)) { foreach ($xmlStrings->si as $val) { if (isset($val->t)) { $sharedStrings[] = PHPExcel_Shared_String::ControlCharacterOOXML2PHP((string) $val->t); } elseif (isset($val->r)) { $sharedStrings[] = $this->_parseRichText($val); } } } $worksheets = array(); foreach ($relsWorkbook->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") { $worksheets[(string) $ele["Id"]] = $ele["Target"]; } } $styles = array(); $cellStyles = array(); $xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']")); $xmlStyles = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/{$xpath['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main"); $numFmts = null; if ($xmlStyles && $xmlStyles->numFmts[0]) { $numFmts = $xmlStyles->numFmts[0]; } if (isset($numFmts) && $numFmts !== NULL) { $numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main"); } if (!$this->_readDataOnly && $xmlStyles) { foreach ($xmlStyles->cellXfs->xf as $xf) { $numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL; if ($xf["numFmtId"]) { if (isset($numFmts)) { $tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]")); if (isset($tmpNumFmt["formatCode"])) { $numFmt = (string) $tmpNumFmt["formatCode"]; } } if ((int) $xf["numFmtId"] < 164) { $numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int) $xf["numFmtId"]); } } //$numFmt = str_replace('mm', 'i', $numFmt); //$numFmt = str_replace('h', 'H', $numFmt); $style = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection); $styles[] = $style; // add style to cellXf collection $objStyle = new PHPExcel_Style(); self::_readStyle($objStyle, $style); $excel->addCellXf($objStyle); } foreach ($xmlStyles->cellStyleXfs->xf as $xf) { $numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL; if ($numFmts && $xf["numFmtId"]) { $tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]")); if (isset($tmpNumFmt["formatCode"])) { $numFmt = (string) $tmpNumFmt["formatCode"]; } else { if ((int) $xf["numFmtId"] < 165) { $numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int) $xf["numFmtId"]); } } } $cellStyle = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection); $cellStyles[] = $cellStyle; // add style to cellStyleXf collection $objStyle = new PHPExcel_Style(); self::_readStyle($objStyle, $cellStyle); $excel->addCellStyleXf($objStyle); } } $dxfs = array(); if (!$this->_readDataOnly && $xmlStyles) { // Conditional Styles if ($xmlStyles->dxfs) { foreach ($xmlStyles->dxfs->dxf as $dxf) { $style = new PHPExcel_Style(FALSE, TRUE); self::_readStyle($style, $dxf); $dxfs[] = $style; } } // Cell Styles if ($xmlStyles->cellStyles) { foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) { if (intval($cellStyle['builtinId']) == 0) { if (isset($cellStyles[intval($cellStyle['xfId'])])) { // Set default style $style = new PHPExcel_Style(); self::_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]); // normal style, currently not using it for anything } } } } } $xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main"); // Set base date if ($xmlWorkbook->workbookPr) { PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900); if (isset($xmlWorkbook->workbookPr['date1904'])) { if (self::boolean((string) $xmlWorkbook->workbookPr['date1904'])) { PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904); } } } $sheetId = 0; // keep track of new sheet id in final workbook $oldSheetId = -1; // keep track of old sheet id in final workbook $countSkippedSheets = 0; // keep track of number of skipped sheets $mapSheetId = array(); // mapping of sheet ids from old to new $charts = $chartDetails = array(); if ($xmlWorkbook->sheets) { foreach ($xmlWorkbook->sheets->sheet as $eleSheet) { ++$oldSheetId; // Check if sheet should be skipped if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) { ++$countSkippedSheets; $mapSheetId[$oldSheetId] = null; continue; } // Map old sheet id in original workbook to new sheet id. // They will differ if loadSheetsOnly() is being used $mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets; // Load sheet $docSheet = $excel->createSheet(); // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet // references in formula cells... during the load, all formulae should be correct, // and we're simply bringing the worksheet name in line with the formula, not the // reverse $docSheet->setTitle((string) $eleSheet["name"], false); $fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")]; $xmlSheet = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/{$fileWorksheet}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main"); $sharedFormulas = array(); if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') { $docSheet->setSheetState((string) $eleSheet["state"]); } if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) { if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) { $docSheet->getSheetView()->setZoomScale(intval($xmlSheet->sheetViews->sheetView['zoomScale'])); } if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) { $docSheet->getSheetView()->setZoomScaleNormal(intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])); } if (isset($xmlSheet->sheetViews->sheetView['view'])) { $docSheet->getSheetView()->setView((string) $xmlSheet->sheetViews->sheetView['view']); } if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) { $docSheet->setShowGridLines(self::boolean((string) $xmlSheet->sheetViews->sheetView['showGridLines'])); } if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) { $docSheet->setShowRowColHeaders(self::boolean((string) $xmlSheet->sheetViews->sheetView['showRowColHeaders'])); } if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) { $docSheet->setRightToLeft(self::boolean((string) $xmlSheet->sheetViews->sheetView['rightToLeft'])); } if (isset($xmlSheet->sheetViews->sheetView->pane)) { if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) { $docSheet->freezePane((string) $xmlSheet->sheetViews->sheetView->pane['topLeftCell']); } else { $xSplit = 0; $ySplit = 0; if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) { $xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']); } if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) { $ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']); } $docSheet->freezePaneByColumnAndRow($xSplit, $ySplit); } } if (isset($xmlSheet->sheetViews->sheetView->selection)) { if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) { $sqref = (string) $xmlSheet->sheetViews->sheetView->selection['sqref']; $sqref = explode(' ', $sqref); $sqref = $sqref[0]; $docSheet->setSelectedCells($sqref); } } } if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) { if (isset($xmlSheet->sheetPr->tabColor['rgb'])) { $docSheet->getTabColor()->setARGB((string) $xmlSheet->sheetPr->tabColor['rgb']); } } if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) { if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && !self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryRight'])) { $docSheet->setShowSummaryRight(FALSE); } else { $docSheet->setShowSummaryRight(TRUE); } if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && !self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryBelow'])) { $docSheet->setShowSummaryBelow(FALSE); } else { $docSheet->setShowSummaryBelow(TRUE); } } if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) { if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && !self::boolean((string) $xmlSheet->sheetPr->pageSetUpPr['fitToPage'])) { $docSheet->getPageSetup()->setFitToPage(FALSE); } else { $docSheet->getPageSetup()->setFitToPage(TRUE); } } if (isset($xmlSheet->sheetFormatPr)) { if (isset($xmlSheet->sheetFormatPr['customHeight']) && self::boolean((string) $xmlSheet->sheetFormatPr['customHeight']) && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) { $docSheet->getDefaultRowDimension()->setRowHeight((double) $xmlSheet->sheetFormatPr['defaultRowHeight']); } if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) { $docSheet->getDefaultColumnDimension()->setWidth((double) $xmlSheet->sheetFormatPr['defaultColWidth']); } if (isset($xmlSheet->sheetFormatPr['zeroHeight']) && (string) $xmlSheet->sheetFormatPr['zeroHeight'] == '1') { $docSheet->getDefaultRowDimension()->setzeroHeight(true); } } if (isset($xmlSheet->cols) && !$this->_readDataOnly) { foreach ($xmlSheet->cols->col as $col) { for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) { if ($col["style"] && !$this->_readDataOnly) { $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"])); } if (self::boolean($col["bestFit"])) { //$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(TRUE); } if (self::boolean($col["hidden"])) { $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setVisible(FALSE); } if (self::boolean($col["collapsed"])) { $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setCollapsed(TRUE); } if ($col["outlineLevel"] > 0) { $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"])); } $docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"])); if (intval($col["max"]) == 16384) { break; } } } } if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) { if (self::boolean((string) $xmlSheet->printOptions['gridLinesSet'])) { $docSheet->setShowGridlines(TRUE); } if (self::boolean((string) $xmlSheet->printOptions['gridLines'])) { $docSheet->setPrintGridlines(TRUE); } if (self::boolean((string) $xmlSheet->printOptions['horizontalCentered'])) { $docSheet->getPageSetup()->setHorizontalCentered(TRUE); } if (self::boolean((string) $xmlSheet->printOptions['verticalCentered'])) { $docSheet->getPageSetup()->setVerticalCentered(TRUE); } } if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) { foreach ($xmlSheet->sheetData->row as $row) { if ($row["ht"] && !$this->_readDataOnly) { $docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"])); } if (self::boolean($row["hidden"]) && !$this->_readDataOnly) { $docSheet->getRowDimension(intval($row["r"]))->setVisible(FALSE); } if (self::boolean($row["collapsed"])) { $docSheet->getRowDimension(intval($row["r"]))->setCollapsed(TRUE); } if ($row["outlineLevel"] > 0) { $docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"])); } if ($row["s"] && !$this->_readDataOnly) { $docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"])); } foreach ($row->c as $c) { $r = (string) $c["r"]; $cellDataType = (string) $c["t"]; $value = null; $calculatedValue = null; // Read cell? if ($this->getReadFilter() !== NULL) { $coordinates = PHPExcel_Cell::coordinateFromString($r); if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) { continue; } } // echo '<b>Reading cell '.$coordinates[0].$coordinates[1].'</b><br />'; // print_r($c); // echo '<br />'; // echo 'Cell Data Type is '.$cellDataType.': '; // // Read cell! switch ($cellDataType) { case "s": // echo 'String<br />'; if ((string) $c->v != '') { $value = $sharedStrings[intval($c->v)]; if ($value instanceof PHPExcel_RichText) { $value = clone $value; } } else { $value = ''; } break; case "b": // echo 'Boolean<br />'; if (!isset($c->f)) { $value = self::_castToBool($c); } else { // Formula $this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToBool'); if (isset($c->f['t'])) { $att = array(); $att = $c->f; $docSheet->getCell($r)->setFormulaAttributes($att); } // echo '$calculatedValue = '.$calculatedValue.'<br />'; } break; case "inlineStr": // echo 'Inline String<br />'; $value = $this->_parseRichText($c->is); break; case "e": // echo 'Error<br />'; if (!isset($c->f)) { $value = self::_castToError($c); } else { // Formula $this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToError'); // echo '$calculatedValue = '.$calculatedValue.'<br />'; } break; default: // echo 'Default<br />'; if (!isset($c->f)) { // echo 'Not a Formula<br />'; $value = self::_castToString($c); } else { // echo 'Treat as Formula<br />'; // Formula $this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToString'); // echo '$calculatedValue = '.$calculatedValue.'<br />'; } break; } // echo 'Value is '.$value.'<br />'; // Check for numeric values if (is_numeric($value) && $cellDataType != 's') { if ($value == (int) $value) { $value = (int) $value; } elseif ($value == (double) $value) { $value = (double) $value; } elseif ($value == (double) $value) { $value = (double) $value; } } // Rich text? if ($value instanceof PHPExcel_RichText && $this->_readDataOnly) { $value = $value->getPlainText(); } $cell = $docSheet->getCell($r); // Assign value if ($cellDataType != '') { $cell->setValueExplicit($value, $cellDataType); } else { $cell->setValue($value); } if ($calculatedValue !== NULL) { $cell->setCalculatedValue($calculatedValue); } // Style information? if ($c["s"] && !$this->_readDataOnly) { // no style index means 0, it seems $cell->setXfIndex(isset($styles[intval($c["s"])]) ? intval($c["s"]) : 0); } } } } $conditionals = array(); if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) { foreach ($xmlSheet->conditionalFormatting as $conditional) { foreach ($conditional->cfRule as $cfRule) { if (((string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_NONE || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CELLIS || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_EXPRESSION) && isset($dxfs[intval($cfRule["dxfId"])])) { $conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule; } } } foreach ($conditionals as $ref => $cfRules) { ksort($cfRules); $conditionalStyles = array(); foreach ($cfRules as $cfRule) { $objConditional = new PHPExcel_Style_Conditional(); $objConditional->setConditionType((string) $cfRule["type"]); $objConditional->setOperatorType((string) $cfRule["operator"]); if ((string) $cfRule["text"] != '') { $objConditional->setText((string) $cfRule["text"]); } if (count($cfRule->formula) > 1) { foreach ($cfRule->formula as $formula) { $objConditional->addCondition((string) $formula); } } else { $objConditional->addCondition((string) $cfRule->formula); } $objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]); $conditionalStyles[] = $objConditional; } // Extract all cell references in $ref $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($ref); foreach ($aReferences as $reference) { $docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles); } } } $aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells"); if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) { foreach ($aKeys as $key) { $method = "set" . ucfirst($key); $docSheet->getProtection()->{$method}(self::boolean((string) $xmlSheet->sheetProtection[$key])); } } if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) { $docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], TRUE); if ($xmlSheet->protectedRanges->protectedRange) { foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) { $docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true); } } } if ($xmlSheet && $xmlSheet->autoFilter && !$this->_readDataOnly) { $autoFilter = $docSheet->getAutoFilter(); $autoFilter->setRange((string) $xmlSheet->autoFilter["ref"]); foreach ($xmlSheet->autoFilter->filterColumn as $filterColumn) { $column = $autoFilter->getColumnByOffset((int) $filterColumn["colId"]); // Check for standard filters if ($filterColumn->filters) { $column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER); $filters = $filterColumn->filters; if (isset($filters["blank"]) && $filters["blank"] == 1) { $column->createRule()->setRule(NULL, '')->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER); } // Standard filters are always an OR join, so no join rule needs to be set // Entries can be either filter elements foreach ($filters->filter as $filterRule) { $column->createRule()->setRule(NULL, (string) $filterRule["val"])->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER); } // Or Date Group elements foreach ($filters->dateGroupItem as $dateGroupItem) { $column->createRule()->setRule(NULL, array('year' => (string) $dateGroupItem["year"], 'month' => (string) $dateGroupItem["month"], 'day' => (string) $dateGroupItem["day"], 'hour' => (string) $dateGroupItem["hour"], 'minute' => (string) $dateGroupItem["minute"], 'second' => (string) $dateGroupItem["second"]), (string) $dateGroupItem["dateTimeGrouping"])->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP); } } // Check for custom filters if ($filterColumn->customFilters) { $column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER); $customFilters = $filterColumn->customFilters; // Custom filters can an AND or an OR join; // and there should only ever be one or two entries if (isset($customFilters["and"]) && $customFilters["and"] == 1) { $column->setJoin(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND); } foreach ($customFilters->customFilter as $filterRule) { $column->createRule()->setRule((string) $filterRule["operator"], (string) $filterRule["val"])->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER); } } // Check for dynamic filters if ($filterColumn->dynamicFilter) { $column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER); // We should only ever have one dynamic filter foreach ($filterColumn->dynamicFilter as $filterRule) { $column->createRule()->setRule(NULL, (string) $filterRule["val"], (string) $filterRule["type"])->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER); if (isset($filterRule["val"])) { $column->setAttribute('val', (string) $filterRule["val"]); } if (isset($filterRule["maxVal"])) { $column->setAttribute('maxVal', (string) $filterRule["maxVal"]); } } } // Check for dynamic filters if ($filterColumn->top10) { $column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER); // We should only ever have one top10 filter foreach ($filterColumn->top10 as $filterRule) { $column->createRule()->setRule(isset($filterRule["percent"]) && $filterRule["percent"] == 1 ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE, (string) $filterRule["val"], isset($filterRule["top"]) && $filterRule["top"] == 1 ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM)->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_TOPTENFILTER); } } } } if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) { foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) { $mergeRef = (string) $mergeCell["ref"]; if (strpos($mergeRef, ':') !== FALSE) { $docSheet->mergeCells((string) $mergeCell["ref"]); } } } if ($xmlSheet && $xmlSheet->pageMargins && !$this->_readDataOnly) { $docPageMargins = $docSheet->getPageMargins(); $docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"])); $docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"])); $docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"])); $docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"])); $docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"])); $docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"])); } if ($xmlSheet && $xmlSheet->pageSetup && !$this->_readDataOnly) { $docPageSetup = $docSheet->getPageSetup(); if (isset($xmlSheet->pageSetup["orientation"])) { $docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]); } if (isset($xmlSheet->pageSetup["paperSize"])) { $docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"])); } if (isset($xmlSheet->pageSetup["scale"])) { $docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), FALSE); } if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) { $docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), FALSE); } if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) { $docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), FALSE); } if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) && self::boolean((string) $xmlSheet->pageSetup["useFirstPageNumber"])) { $docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"])); } } if ($xmlSheet && $xmlSheet->headerFooter && !$this->_readDataOnly) { $docHeaderFooter = $docSheet->getHeaderFooter(); if (isset($xmlSheet->headerFooter["differentOddEven"]) && self::boolean((string) $xmlSheet->headerFooter["differentOddEven"])) { $docHeaderFooter->setDifferentOddEven(TRUE); } else { $docHeaderFooter->setDifferentOddEven(FALSE); } if (isset($xmlSheet->headerFooter["differentFirst"]) && self::boolean((string) $xmlSheet->headerFooter["differentFirst"])) { $docHeaderFooter->setDifferentFirst(TRUE); } else { $docHeaderFooter->setDifferentFirst(FALSE); } if (isset($xmlSheet->headerFooter["scaleWithDoc"]) && !self::boolean((string) $xmlSheet->headerFooter["scaleWithDoc"])) { $docHeaderFooter->setScaleWithDocument(FALSE); } else { $docHeaderFooter->setScaleWithDocument(TRUE); } if (isset($xmlSheet->headerFooter["alignWithMargins"]) && !self::boolean((string) $xmlSheet->headerFooter["alignWithMargins"])) { $docHeaderFooter->setAlignWithMargins(FALSE); } else { $docHeaderFooter->setAlignWithMargins(TRUE); } $docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader); $docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter); $docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader); $docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter); $docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader); $docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter); } if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->_readDataOnly) { foreach ($xmlSheet->rowBreaks->brk as $brk) { if ($brk["man"]) { $docSheet->setBreak("A{$brk['id']}", PHPExcel_Worksheet::BREAK_ROW); } } } if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) { foreach ($xmlSheet->colBreaks->brk as $brk) { if ($brk["man"]) { $docSheet->setBreak(PHPExcel_Cell::stringFromColumnIndex((string) $brk["id"]) . "1", PHPExcel_Worksheet::BREAK_COLUMN); } } } if ($xmlSheet && $xmlSheet->dataValidations && !$this->_readDataOnly) { foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) { // Uppercase coordinate $range = strtoupper($dataValidation["sqref"]); $rangeSet = explode(' ', $range); foreach ($rangeSet as $range) { $stRange = $docSheet->shrinkRangeToFit($range); // Extract all cell references in $range $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($stRange); foreach ($aReferences as $reference) { // Create validation $docValidation = $docSheet->getCell($reference)->getDataValidation(); $docValidation->setType((string) $dataValidation["type"]); $docValidation->setErrorStyle((string) $dataValidation["errorStyle"]); $docValidation->setOperator((string) $dataValidation["operator"]); $docValidation->setAllowBlank($dataValidation["allowBlank"] != 0); $docValidation->setShowDropDown($dataValidation["showDropDown"] == 0); $docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0); $docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0); $docValidation->setErrorTitle((string) $dataValidation["errorTitle"]); $docValidation->setError((string) $dataValidation["error"]); $docValidation->setPromptTitle((string) $dataValidation["promptTitle"]); $docValidation->setPrompt((string) $dataValidation["prompt"]); $docValidation->setFormula1((string) $dataValidation->formula1); $docValidation->setFormula2((string) $dataValidation->formula2); } } } } // Add hyperlinks $hyperlinks = array(); if (!$this->_readDataOnly) { // Locate hyperlink relations if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") { $hyperlinks[(string) $ele["Id"]] = (string) $ele["Target"]; } } } // Loop through hyperlinks if ($xmlSheet && $xmlSheet->hyperlinks) { foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) { // Link url $linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships'); foreach (PHPExcel_Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) { $cell = $docSheet->getCell($cellReference); if (isset($linkRel['id'])) { $hyperlinkUrl = $hyperlinks[(string) $linkRel['id']]; if (isset($hyperlink['location'])) { $hyperlinkUrl .= '#' . (string) $hyperlink['location']; } $cell->getHyperlink()->setUrl($hyperlinkUrl); } elseif (isset($hyperlink['location'])) { $cell->getHyperlink()->setUrl('sheet://' . (string) $hyperlink['location']); } // Tooltip if (isset($hyperlink['tooltip'])) { $cell->getHyperlink()->setTooltip((string) $hyperlink['tooltip']); } } } } } // Add comments $comments = array(); $vmlComments = array(); if (!$this->_readDataOnly) { // Locate comment relations if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") { $comments[(string) $ele["Id"]] = (string) $ele["Target"]; } if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") { $vmlComments[(string) $ele["Id"]] = (string) $ele["Target"]; } } } // Loop through comments foreach ($comments as $relName => $relPath) { // Load comments file $relPath = PHPExcel_Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath); $commentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath)); // Utility variables $authors = array(); // Loop through authors foreach ($commentsFile->authors->author as $author) { $authors[] = (string) $author; } // Loop through contents foreach ($commentsFile->commentList->comment as $comment) { $docSheet->getComment((string) $comment['ref'])->setAuthor($authors[(string) $comment['authorId']]); $docSheet->getComment((string) $comment['ref'])->setText($this->_parseRichText($comment->text)); } } // Loop through VML comments foreach ($vmlComments as $relName => $relPath) { // Load VML comments file $relPath = PHPExcel_Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath); $vmlCommentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath)); $vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); $shapes = $vmlCommentsFile->xpath('//v:shape'); foreach ($shapes as $shape) { $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); if (isset($shape['style'])) { $style = (string) $shape['style']; $fillColor = strtoupper(substr((string) $shape['fillcolor'], 1)); $column = null; $row = null; $clientData = $shape->xpath('.//x:ClientData'); if (is_array($clientData) && !empty($clientData)) { $clientData = $clientData[0]; if (isset($clientData['ObjectType']) && (string) $clientData['ObjectType'] == 'Note') { $temp = $clientData->xpath('.//x:Row'); if (is_array($temp)) { $row = $temp[0]; } $temp = $clientData->xpath('.//x:Column'); if (is_array($temp)) { $column = $temp[0]; } } } if ($column !== NULL && $row !== NULL) { // Set comment properties $comment = $docSheet->getCommentByColumnAndRow((string) $column, $row + 1); $comment->getFillColor()->setRGB($fillColor); // Parse style $styleArray = explode(';', str_replace(' ', '', $style)); foreach ($styleArray as $stylePair) { $stylePair = explode(':', $stylePair); if ($stylePair[0] == 'margin-left') { $comment->setMarginLeft($stylePair[1]); } if ($stylePair[0] == 'margin-top') { $comment->setMarginTop($stylePair[1]); } if ($stylePair[0] == 'width') { $comment->setWidth($stylePair[1]); } if ($stylePair[0] == 'height') { $comment->setHeight($stylePair[1]); } if ($stylePair[0] == 'visibility') { $comment->setVisible($stylePair[1] == 'visible'); } } } } } } // Header/footer images if ($xmlSheet && $xmlSheet->legacyDrawingHF && !$this->_readDataOnly) { if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); $vmlRelationship = ''; foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") { $vmlRelationship = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]); } } if ($vmlRelationship != '') { // Fetch linked images $relsVML = simplexml_load_string($this->_getFromZipArchive($zip, dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels')); //~ http://schemas.openxmlformats.org/package/2006/relationships"); $drawings = array(); foreach ($relsVML->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") { $drawings[(string) $ele["Id"]] = self::dir_add($vmlRelationship, $ele["Target"]); } } // Fetch VML document $vmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $vmlRelationship)); $vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); $hfImages = array(); $shapes = $vmlDrawing->xpath('//v:shape'); foreach ($shapes as $shape) { $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); $imageData = $shape->xpath('//v:imagedata'); $imageData = $imageData[0]; $imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office'); $style = self::toCSSArray((string) $shape['style']); $hfImages[(string) $shape['id']] = new PHPExcel_Worksheet_HeaderFooterDrawing(); if (isset($imageData['title'])) { $hfImages[(string) $shape['id']]->setName((string) $imageData['title']); } $hfImages[(string) $shape['id']]->setPath("zip://" . PHPExcel_Shared_File::realpath($pFilename) . "#" . $drawings[(string) $imageData['relid']], false); $hfImages[(string) $shape['id']]->setResizeProportional(false); $hfImages[(string) $shape['id']]->setWidth($style['width']); $hfImages[(string) $shape['id']]->setHeight($style['height']); $hfImages[(string) $shape['id']]->setOffsetX($style['margin-left']); $hfImages[(string) $shape['id']]->setOffsetY($style['margin-top']); $hfImages[(string) $shape['id']]->setResizeProportional(true); } $docSheet->getHeaderFooter()->setImages($hfImages); } } } } // TODO: Autoshapes from twoCellAnchors! if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); $drawings = array(); foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") { $drawings[(string) $ele["Id"]] = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]); } } if ($xmlSheet->drawing && !$this->_readDataOnly) { foreach ($xmlSheet->drawing as $drawing) { $fileDrawing = $drawings[(string) self::array_item($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")]; $relsDrawing = simplexml_load_string($this->_getFromZipArchive($zip, dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); $images = array(); if ($relsDrawing && $relsDrawing->Relationship) { foreach ($relsDrawing->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") { $images[(string) $ele["Id"]] = self::dir_add($fileDrawing, $ele["Target"]); } elseif ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart") { if ($this->_includeCharts) { $charts[self::dir_add($fileDrawing, $ele["Target"])] = array('id' => (string) $ele["Id"], 'sheet' => $docSheet->getTitle()); } } } } $xmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $fileDrawing))->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"); if ($xmlDrawing->oneCellAnchor) { foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) { if ($oneCellAnchor->pic->blipFill) { $blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip; $xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm; $outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw; $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name")); $objDrawing->setDescription((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr")); $objDrawing->setPath("zip://" . PHPExcel_Shared_File::realpath($pFilename) . "#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false); $objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1)); $objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff)); $objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff)); $objDrawing->setResizeProportional(false); $objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx"))); $objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy"))); if ($xfrm) { $objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot"))); } if ($outerShdw) { $shadow = $objDrawing->getShadow(); $shadow->setVisible(true); $shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad"))); $shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist"))); $shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir"))); $shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn")); $shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val")); $shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000); } $objDrawing->setWorksheet($docSheet); } else { // ? Can charts be positioned with a oneCellAnchor ? $coordinates = PHPExcel_Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1); $offsetX = PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff); $offsetY = PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff); $width = PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx")); $height = PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy")); } } } if ($xmlDrawing->twoCellAnchor) { foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) { if ($twoCellAnchor->pic->blipFill) { $blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip; $xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm; $outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw; $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name")); $objDrawing->setDescription((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr")); $objDrawing->setPath("zip://" . PHPExcel_Shared_File::realpath($pFilename) . "#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false); $objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1)); $objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff)); $objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff)); $objDrawing->setResizeProportional(false); $objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx"))); $objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy"))); if ($xfrm) { $objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot"))); } if ($outerShdw) { $shadow = $objDrawing->getShadow(); $shadow->setVisible(true); $shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad"))); $shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist"))); $shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir"))); $shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn")); $shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val")); $shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000); } $objDrawing->setWorksheet($docSheet); } elseif ($this->_includeCharts && $twoCellAnchor->graphicFrame) { $fromCoordinate = PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1); $fromOffsetX = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff); $fromOffsetY = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff); $toCoordinate = PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->to->col) . ($twoCellAnchor->to->row + 1); $toOffsetX = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->to->colOff); $toOffsetY = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->to->rowOff); $graphic = $twoCellAnchor->graphicFrame->children("http://schemas.openxmlformats.org/drawingml/2006/main")->graphic; $chartRef = $graphic->graphicData->children("http://schemas.openxmlformats.org/drawingml/2006/chart")->chart; $thisChart = (string) $chartRef->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"); $chartDetails[$docSheet->getTitle() . '!' . $thisChart] = array('fromCoordinate' => $fromCoordinate, 'fromOffsetX' => $fromOffsetX, 'fromOffsetY' => $fromOffsetY, 'toCoordinate' => $toCoordinate, 'toOffsetX' => $toOffsetX, 'toOffsetY' => $toOffsetY, 'worksheetTitle' => $docSheet->getTitle()); } } } } } } // Loop through definedNames if ($xmlWorkbook->definedNames) { foreach ($xmlWorkbook->definedNames->definedName as $definedName) { // Extract range $extractedRange = (string) $definedName; $extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange); if (($spos = strpos($extractedRange, '!')) !== false) { $extractedRange = substr($extractedRange, 0, $spos) . str_replace('$', '', substr($extractedRange, $spos)); } else { $extractedRange = str_replace('$', '', $extractedRange); } // Valid range? if (stripos((string) $definedName, '#REF!') !== FALSE || $extractedRange == '') { continue; } // Some definedNames are only applicable if we are on the same sheet... if ((string) $definedName['localSheetId'] != '' && (string) $definedName['localSheetId'] == $sheetId) { // Switch on type switch ((string) $definedName['name']) { case '_xlnm._FilterDatabase': if ((string) $definedName['hidden'] !== '1') { $docSheet->getAutoFilter()->setRange($extractedRange); } break; case '_xlnm.Print_Titles': // Split $extractedRange $extractedRange = explode(',', $extractedRange); // Set print titles foreach ($extractedRange as $range) { $matches = array(); // check for repeating columns, e g. 'A:A' or 'A:D' if (preg_match('/^([A-Z]+)\\:([A-Z]+)$/', $range, $matches)) { $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($matches[1], $matches[2])); } elseif (preg_match('/^(\\d+)\\:(\\d+)$/', $range, $matches)) { $docSheet->getPageSetup()->setRowsToRepeatAtTop(array($matches[1], $matches[2])); } } break; case '_xlnm.Print_Area': $rangeSets = explode(',', $extractedRange); // FIXME: what if sheetname contains comma? $newRangeSets = array(); foreach ($rangeSets as $rangeSet) { $range = explode('!', $rangeSet); // FIXME: what if sheetname contains exclamation mark? $rangeSet = isset($range[1]) ? $range[1] : $range[0]; if (strpos($rangeSet, ':') === FALSE) { $rangeSet = $rangeSet . ':' . $rangeSet; } $newRangeSets[] = str_replace('$', '', $rangeSet); } $docSheet->getPageSetup()->setPrintArea(implode(',', $newRangeSets)); break; default: break; } } } } // Next sheet id ++$sheetId; } // Loop through definedNames if ($xmlWorkbook->definedNames) { foreach ($xmlWorkbook->definedNames->definedName as $definedName) { // Extract range $extractedRange = (string) $definedName; $extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange); if (($spos = strpos($extractedRange, '!')) !== false) { $extractedRange = substr($extractedRange, 0, $spos) . str_replace('$', '', substr($extractedRange, $spos)); } else { $extractedRange = str_replace('$', '', $extractedRange); } // Valid range? if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') { continue; } // Some definedNames are only applicable if we are on the same sheet... if ((string) $definedName['localSheetId'] != '') { // Local defined name // Switch on type switch ((string) $definedName['name']) { case '_xlnm._FilterDatabase': case '_xlnm.Print_Titles': case '_xlnm.Print_Area': break; default: if ($mapSheetId[(int) $definedName['localSheetId']] !== null) { $range = explode('!', (string) $definedName); if (count($range) == 2) { $range[0] = str_replace("''", "'", $range[0]); $range[0] = str_replace("'", "", $range[0]); if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) { $extractedRange = str_replace('$', '', $range[1]); $scope = $docSheet->getParent()->getSheet($mapSheetId[(int) $definedName['localSheetId']]); $excel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $worksheet, $extractedRange, true, $scope)); } } } break; } } else { if (!isset($definedName['localSheetId'])) { // "Global" definedNames $locatedSheet = null; $extractedSheetName = ''; if (strpos((string) $definedName, '!') !== false) { // Extract sheet name $extractedSheetName = PHPExcel_Worksheet::extractSheetTitle((string) $definedName, true); $extractedSheetName = $extractedSheetName[0]; // Locate sheet $locatedSheet = $excel->getSheetByName($extractedSheetName); // Modify range $range = explode('!', $extractedRange); $extractedRange = isset($range[1]) ? $range[1] : $range[0]; } if ($locatedSheet !== NULL) { $excel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $locatedSheet, $extractedRange, false)); } } } } } } if (!$this->_readDataOnly || !empty($this->_loadSheetsOnly)) { // active sheet index $activeTab = intval($xmlWorkbook->bookViews->workbookView["activeTab"]); // refers to old sheet index // keep active sheet index if sheet is still loaded, else first sheet is set as the active if (isset($mapSheetId[$activeTab]) && $mapSheetId[$activeTab] !== null) { $excel->setActiveSheetIndex($mapSheetId[$activeTab]); } else { if ($excel->getSheetCount() == 0) { $excel->createSheet(); } $excel->setActiveSheetIndex(0); } } break; } } if (!$this->_readDataOnly) { $contentTypes = simplexml_load_string($this->_getFromZipArchive($zip, "[Content_Types].xml")); foreach ($contentTypes->Override as $contentType) { switch ($contentType["ContentType"]) { case "application/vnd.openxmlformats-officedocument.drawingml.chart+xml": if ($this->_includeCharts) { $chartEntryRef = ltrim($contentType['PartName'], '/'); $chartElements = simplexml_load_string($this->_getFromZipArchive($zip, $chartEntryRef)); $objChart = PHPExcel_Reader_Excel2007_Chart::readChart($chartElements, basename($chartEntryRef, '.xml')); // echo 'Chart ',$chartEntryRef,'<br />'; // var_dump($charts[$chartEntryRef]); // if (isset($charts[$chartEntryRef])) { $chartPositionRef = $charts[$chartEntryRef]['sheet'] . '!' . $charts[$chartEntryRef]['id']; // echo 'Position Ref ',$chartPositionRef,'<br />'; if (isset($chartDetails[$chartPositionRef])) { // var_dump($chartDetails[$chartPositionRef]); $excel->getSheetByName($charts[$chartEntryRef]['sheet'])->addChart($objChart); $objChart->setWorksheet($excel->getSheetByName($charts[$chartEntryRef]['sheet'])); $objChart->setTopLeftPosition($chartDetails[$chartPositionRef]['fromCoordinate'], $chartDetails[$chartPositionRef]['fromOffsetX'], $chartDetails[$chartPositionRef]['fromOffsetY']); $objChart->setBottomRightPosition($chartDetails[$chartPositionRef]['toCoordinate'], $chartDetails[$chartPositionRef]['toOffsetX'], $chartDetails[$chartPositionRef]['toOffsetY']); } } } } } } $zip->close(); return $excel; }
public function actionPollRating() { $orderIds = trim(Yii::app()->request->getParam('orderIds')); //获取数据 $data = PollRating::model()->getExportData($orderIds); //获取附件 $attachments = FileUpload::model()->getFiles($orderIds); $pics = array(); foreach ($attachments as $value) { $pics[$value['orderid']][] = $value['file_path']; } Yii::$enableIncludePath = false; //Yii::import('application.extension.PHPExcel', 1); $objPHPExcel = new PHPExcel(); // Add some data $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '处理日期')->setCellValue('B1', '产品类型')->setCellValue('C1', '开始时间')->setCellValue('D1', '结束时间')->setCellValue('E1', '处理时间')->setCellValue('F1', '账号')->setCellValue('G1', '订单号')->setCellValue('H1', '申请服务')->setCellValue('I1', '具体内容')->setCellValue('J1', '问题描述')->setCellValue('K1', '处理情况')->setCellValue('L1', '备注')->setCellValue('M1', '处理人员')->setCellValue('N1', '附件'); //最多支持5张附件图片 $picColumn = array('N', 'O', 'P', 'Q', 'R'); $i = 2; foreach ($data as $value) { $endTime = empty($value["updatetime"]) ? time() : strtotime($value["updatetime"]); $startTime = empty($value["intitime"]) ? 0 : strtotime($value["intitime"]); $subTime = $endTime - $startTime; $remark = ""; $res = unserialize(base64_decode($value['content'])); $remarkKey = mb_convert_encoding('备注_', 'GBK', 'UTF8'); if (isset($res[$remarkKey])) { $remark = mb_convert_encoding($res[$remarkKey], 'UTF8', 'GBK'); } $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $i, strstr($value["intitime"], ' ', true))->setCellValueExplicit('B' . $i, mb_convert_encoding($value['game_name'], 'UTF8', 'GBK'), PHPExcel_Cell_DataType::TYPE_STRING)->setCellValue('C' . $i, trim(strstr($value["intitime"], ' ')))->setCellValueExplicit('D' . $i, $value['updatetime'], PHPExcel_Cell_DataType::TYPE_STRING)->setCellValueExplicit('E' . $i, $subTime, PHPExcel_Cell_DataType::TYPE_STRING)->setCellValue('F' . $i, $value['consumeraccount'])->setCellValueExplicit('G' . $i, $value['ordernum'], PHPExcel_Cell_DataType::TYPE_STRING)->setCellValue('H' . $i, $value['typename'])->setCellValue('I' . $i, $value['typedetail'])->setCellValueExplicit('J' . $i, $remark, PHPExcel_Cell_DataType::TYPE_STRING)->setCellValue('K' . $i, mb_convert_encoding($value['status'], 'UTF8', 'GBK'))->setCellValue('L' . $i, mb_convert_encoding($value['remark'], 'UTF-8', 'GBK'))->setCellValue('M' . $i, mb_convert_encoding($value['username'], 'UTF8', 'GBK')); //图片 if (isset($pics[$value['orderid']])) { foreach ($pics[$value['orderid']] as $key => $pic) { $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setPath(_PATH_UPLOAD . DIRECTORY_SEPARATOR . $pic); $objDrawing->setCoordinates($picColumn[$key] . $i); $objDrawing->setName($pic); $objDrawing->setHeight(60); $objDrawing->setWidth(60); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); } } //设置行高 $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(30); $i++; } $filename = mb_convert_encoding('所有接待日志', 'GBK', 'UTF8') . date('YmdHis'); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="' . $filename . '.xls"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified header('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header('Pragma: public'); // HTTP/1.0 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; }
public function postAction() { $post = $this->getRequest()->getPost(); if ($post['file_type'] == 'csv') { $dir = $post['filetype'] . '_' . date('Y-m-d'); mkdir('image/' . $dir, 0777); $name = $dir . '.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename=csv/' . $name); header('Pragma: no-cache'); header("Expires: 0"); touch('/csv'); chmod('/csv', 0777); define('SAVE_FEED_LOCATION', 'csv/' . $name); set_time_limit(1800); try { $handle = fopen(SAVE_FEED_LOCATION, 'w'); $heading = array('STYLE NUMBER', 'STYLE NAME', 'LONG DESCRIPTION', 'GENDER', 'IMAGES PROVIDED', 'IMAGE FILENAME', 'COMPOSITION: STONES', 'COMPOSITION: SETTING', 'COMPOSITION: OTHER', 'SECONDARY COLOR', 'STONE CUT', 'CLARITY', 'COLOUR', 'SETTING TYPE', 'RING SIZE 6', 'RING SIZE 7', 'RING SIZE 7.5', 'RING SIZE 8', 'RING SIZE 8.5', 'RING SIZE 9', 'RING SIZE 9.5', 'RING SIZE 10', 'RING SIZE 10.5', 'RING SIZE 11', 'RING SIZE 11.5', 'RING SIZE 12', 'RING SIZE 12.5', 'RING SIZE 13', 'LENGTH', 'HEIGHT', 'WIDTH', 'SPECIAL CARE', 'MADE IN "COUNTRY"', 'WHOLESALE', 'RETAIL', 'SPECIAL PRICE', 'INVENTORY'); $feed_line = implode(",", $heading) . "\r\n"; fwrite($handle, $feed_line); $products = Mage::getModel('catalog/product')->getCollection(); $products->addAttributeToSelect('*'); $prodIds = $products->getAllIds(); $product = Mage::getModel('catalog/product'); $counter_test = 0; foreach ($post['id'] as $productId) { if (++$counter_test < 30000) { $product->load($productId); $product_data = array(); $product_data['style_number'] = str_replace('"', '""', $product->getSku()); $product_data['style_name'] = $product->getName(); $product_data['description'] = htmlspecialchars(iconv("UTF-8", "UTF-8//IGNORE", $product->getDescription())); $product_data['gender'] = $product->getResource()->getAttribute('filter_for')->getFrontend()->getValue($product); if ($product->getImage()) { if ($product->getImage() == 'no_selection') { $product_data['image_provided'] = 'No'; } else { $product_data['image_provided'] = 'Yes'; } } else { $product_data['image_provided'] = 'No'; } if ($product->getImage() == 'no_selection') { $product_data['image_link'] = ''; } else { $product_data['image_link'] = $product->getImage(); } $image_break = explode("/", $product->getImage()); $imagename = end($image_break); $file = Mage::getBaseUrl(Mage_Core_Model_Store::URL_TYPE_MEDIA) . 'catalog/product' . $product->getImage(); $newfile = 'image/' . $dir . '/' . $imagename; if (!copy($file, $newfile)) { echo "failed to copy {$file}...\n"; } $product_data['stone'] = $product->getAttributeText('product_stones'); $product_data['setting'] = ''; $product_data['other'] = ''; $product_data['s_color'] = ''; $product_data['stone_cut'] = ''; $product_data['clarity'] = ''; $product_data['color'] = $product->getAttributeText('product_color'); $product_data['setting_type'] = ''; $product_data['ring_size6'] = ''; $product_data['ring_size7'] = ''; $product_data['ring_size75'] = ''; $product_data['ring_size8'] = ''; $product_data['ring_size85'] = ''; $product_data['ring_size9'] = ''; $product_data['ring_size95'] = ''; $product_data['ring_size10'] = ''; $product_data['ring_size105'] = ''; $product_data['ring_size11'] = ''; $product_data['ring_size115'] = ''; $product_data['ring_size12'] = ''; $product_data['ring_size125'] = ''; $product_data['ring_size13'] = ''; $product_data['length'] = str_replace('"', '""', $product->getLengthDimensions()); $product_data['height'] = str_replace('"', '""', $product->getHeightDimensions()); $product_data['width'] = str_replace('"', '""', $product->getWidthDimensions()); $product_data['special_care'] = ''; $product_data['made_in'] = ''; $product->setCustomerGroupId(2); if (number_format($product->getPriceModel()->getFinalPrice(1, $product), 2) == number_format($product->getPrice(), 2)) { $product_data['wholesale'] = ''; } else { $product_data['wholesale'] = number_format($product->getPriceModel()->getFinalPrice(1, $product), 2); } $price_temp = round($product->getPrice(), 2); $product_data['price'] = round($product->getPrice(), 2); if ($product->getSpecialPrice() == '') { $product_data['special_price'] = ''; } else { $product_data['special_price'] = number_format($product->getSpecialPrice(), 2); } $product_data['qty'] = $product->getStockItem()->getQty(); foreach ($product_data as $k => $val) { $product_data[$k] = '"' . $val . '"'; } $feed_line = implode(",", $product_data) . "\r\n"; fwrite($handle, $feed_line); fflush($handle); } } fclose($handle); $dir12 = 'image/' . $dir; $archive = 'csv/' . $dir . '.zip'; $zip = new ZipArchive(); $zip->open($archive, ZipArchive::CREATE); $files = scandir($dir12); unset($files[0], $files[1]); foreach ($files as $file) { $zip->addFile($dir12 . '/' . $file); } $zip->close(); header('Content-Type: application/zip'); header('Content-disposition: attachment; filename=' . $archive); header('Content-Length: ' . filesize($archive)); $dirPath = 'image/' . $dir; if (!is_dir($dirPath)) { throw new InvalidArgumentException("{$dirPath} must be a directory"); } if (substr($dirPath, strlen($dirPath) - 1, 1) != '/') { $dirPath .= '/'; } $files = glob($dirPath . '*', GLOB_MARK); foreach ($files as $file) { if (is_dir($file)) { self::deleteDir($file); } else { unlink($file); } } rmdir($dirPath); } catch (Exception $e) { die($e->getMessage()); } } if ($post['file_type'] == 'xls') { error_reporting(E_ALL); /** Include PHPExcel */ require_once 'Classes/PHPExcel.php'; // Create new PHPExcel object //echo date('H:i:s') , " Create new PHPExcel object" , EOL; $objPHPExcel = new PHPExcel(); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel_IOFactory */ require_once 'Classes/PHPExcel/IOFactory.php'; $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); //********* CUSTOM CODE **************************** // Create a first sheet, representing sales data //echo date('H:i:s') , " Add some data" , EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->getCell('B1')->setValue("500 Sauve West\nMontreal, Quebec\nCanada\nH3L 1Z8"); $objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->getCell('C1')->setValue("info@italgemjewellers.com\nTel (514) 388-5777\nFax (514) 384-5777"); $objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setWrapText(true); // Merge cells //echo date('H:i:s') , " Merge cells" , EOL; $objPHPExcel->getActiveSheet()->mergeCells('F1:G1'); $objPHPExcel->getActiveSheet()->getCell('F1')->setValue("Quotation preparred for \nClientABCD"); $objPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->setCellValue('A3', 'STYLE CODE #'); $objPHPExcel->getActiveSheet()->setCellValue('B3', 'PICTURE'); $objPHPExcel->getActiveSheet()->setCellValue('C3', 'DESCRIPTION'); $objPHPExcel->getActiveSheet()->setCellValue('D3', 'RETAIL'); $objPHPExcel->getActiveSheet()->setCellValue('E3', 'WHOLE SALE PRICE'); $objPHPExcel->getActiveSheet()->setCellValue('F3', 'YOUR PRICE'); $objPHPExcel->getActiveSheet()->setCellValue('G3', 'Quanity '); // Set column widths //echo date('H:i:s') , " Set column widths" , EOL; $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(98); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(80); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20); // Set fonts //echo date('H:i:s') , " Set fonts" , EOL; $objPHPExcel->getActiveSheet()->getStyle("B1")->getFont()->setName('Times New Roman')->setSize(15)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle("C1")->getFont()->setName('Times New Roman')->setSize(15)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle("F1")->getFont()->setBold(true)->setName('Times New Roman')->setSize(18)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); // Set thin black border outline around column //echo date('H:i:s') , " Set thin black border outline around column" , EOL; $styleThinBlackBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000')))); $objPHPExcel->getActiveSheet()->getStyle('A1:D1')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('G1')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('B3')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('C3')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('D3')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('E3')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('F3')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('G3')->applyFromArray($styleThinBlackBorderOutline); // Set fills //echo date('H:i:s') , " Set fills" , EOL; $objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFill()->getStartColor()->setARGB('FFFFFF'); $objPHPExcel->getActiveSheet()->getStyle('A2:G2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A2:G2')->getFill()->getStartColor()->setARGB('FFFFFF'); // Set style for header row using alternative method //echo date('H:i:s') , " Set style for header row using alternative method" , EOL; $objPHPExcel->getActiveSheet()->getStyle('A3:G3')->applyFromArray(array('font' => array('bold' => true), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)))); // Add a drawing to the worksheet //echo date('H:i:s') , " Add a drawing to the worksheet" , EOL; $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('Logo'); $objDrawing->setDescription('Logo'); $objDrawing->setPath('./images/logo.png'); $objDrawing->setHeight(133); $objDrawing->setWidth(281); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); //$post['id'] = array("1","2","3","4","5"); $products = Mage::getModel('catalog/product')->getCollection(); $products->addAttributeToSelect('*'); $prodIds = $products->getAllIds(); $product = Mage::getModel('catalog/product'); $counter_test = 0; $i = 4; foreach ($post['id'] as $productId) { if (++$counter_test < 30000) { $product->load($productId); $style_number = str_replace('"', '', $product->getSku()); $image_link = $product->getImage(); $description = htmlspecialchars(iconv("UTF-8", "UTF-8//IGNORE", $product->getDescription())); $product->setCustomerGroupId(2); $wholesale = number_format($product->getPriceModel()->getFinalPrice(1, $product), 2); $price_temp = round($product->getPrice(), 2); $price = round($product->getPrice(), 2); $special_price = number_format($product->getSpecialPrice(), 2); $qty = $product->getStockItem()->getQty(); $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(190); //Infromation 1 $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $style_number); $objPHPExcel->getActiveSheet()->getStyle('A' . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A' . $i)->getAlignment()->setIndent(9); $objPHPExcel->getActiveSheet()->getStyle('A' . $i)->getFont()->setName('Arial')->setSize(10)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle('A' . $i)->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A' . $i . ':G' . $i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A' . $i . ':G' . $i)->getFill()->getStartColor()->setARGB('FFFFFF'); $objPHPExcel->getActiveSheet()->getStyle('A' . $i)->applyFromArray($styleThinBlackBorderOutline); //Extra Block $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('F' . $i)->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('G' . $i)->applyFromArray($styleThinBlackBorderOutline); //2 $objPHPExcel->getActiveSheet()->getStyle('B' . $i)->applyFromArray($styleThinBlackBorderOutline); $objDrawing = new PHPExcel_Worksheet_Drawing(); if (file_exists('./media/catalog/product' . $image_link)) { if ($product->getImage() == 'no_selection') { $objDrawing->setPath('./images/logo.png'); $objDrawing->setOffsetX(5); $objDrawing->setOffsetY(50); } else { $objDrawing->setPath('./media/catalog/product' . $image_link); $objDrawing->setOffsetX(5); $objDrawing->setOffsetY(5); } } else { $objDrawing->setPath('./images/logo.png'); $objDrawing->setOffsetX(5); $objDrawing->setOffsetY(50); } $objDrawing->setCoordinates('B' . $i); //$objDrawing->setHeight(190); //$objDrawing->setWidth(340); $objDrawing->setResizeProportional(true); $objDrawing->setWidthAndHeight(340, 210); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); //3 Cell $objPHPExcel->getActiveSheet()->getStyle('C' . $i)->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $description); $objPHPExcel->getActiveSheet()->getStyle('C' . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('C' . $i)->getFont()->setName('Arial')->setSize(10)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle('C' . $i)->getFont()->setBold(true); //4 Cell $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->applyFromArray($styleThinBlackBorderOutline); if ($price_temp == '0') { $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, ''); } else { $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->getNumberFormat()->applyFromArray(array('code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD)); $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $price_temp); } $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->getAlignment()->setIndent(9); $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->getFont()->setName('Arial')->setSize(10)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->getFont()->setBold(true); //5 Cell $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->applyFromArray($styleThinBlackBorderOutline); if ($wholesale == $price_temp) { $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, ''); } else { $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->getNumberFormat()->applyFromArray(array('code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD)); $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $wholesale); } $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->getAlignment()->setIndent(9); $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->getFont()->setName('Arial')->setSize(10)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->getFont()->setBold(true); //6 Cell $objPHPExcel->getActiveSheet()->getStyle('F' . $i)->applyFromArray($styleThinBlackBorderOutline); if ($special_price == '0') { $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, ''); } else { $objPHPExcel->getActiveSheet()->getStyle('F' . $i)->getNumberFormat()->applyFromArray(array('code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD)); $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $special_price); } $objPHPExcel->getActiveSheet()->getStyle('F' . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F' . $i)->getAlignment()->setIndent(9); $objPHPExcel->getActiveSheet()->getStyle('F' . $i)->getFont()->setName('Arial')->setSize(10)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle('F' . $i)->getFont()->setBold(true); //7 Cell $objPHPExcel->getActiveSheet()->getStyle('G' . $i)->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $qty); $objPHPExcel->getActiveSheet()->getStyle('G' . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('G' . $i)->getAlignment()->setIndent(9); $objPHPExcel->getActiveSheet()->getStyle('G' . $i)->getFont()->setName('Arial')->setSize(10)->getColor()->setRGB('FF0000'); $objPHPExcel->getActiveSheet()->getStyle('G' . $i)->getFont()->setBold(true); } $i++; } //echo "come"; //die(); $name = $post['filetype'] . '_' . date('Y-m-d') . '.xlsx'; $changeFilename = $name; $allvalues_array = explode('/', $changeFilename); $lastvalues = count($allvalues_array) - 1; $finalname_array = array(); $finalname = ''; for ($pl = 0; $pl < count($allvalues_array); $pl++) { if ($pl == $lastvalues) { $finalname_array[] = 'xls'; $finalname_array[] = $allvalues_array[$pl]; } else { $finalname_array[] = $allvalues_array[$pl]; } } $finalname = implode('/', $finalname_array); //********* CUSTOM CODE **************************** $objWriter->save($finalname); } try { if (empty($post)) { Mage::throwException($this->__('Invalid form data.')); } /* here's your form processing */ if ($post['file_type'] == 'csv') { $message = $this->__('Your CSV successfully exported.'); } if ($post['file_type'] == 'xls') { $message = $this->__('Your XLSX successfully exported.'); } Mage::getSingleton('adminhtml/session')->addSuccess($message); } catch (Exception $e) { Mage::getSingleton('adminhtml/session')->addError($e->getMessage()); } $this->_redirect('*/*'); }
function write($content, $char = "A1", $ispic = false, $align = "", $isnum = false) { //如果是图片 if ($ispic && is_file($content) && file_exists($content)) { $w_h = explode(":", $ispic); $pic_width = intval($w_h[0]) > 0 ? intval($w_h[0]) : 100; $pic_height = intval($w_h[1]) > 0 ? intval($w_h[1]) : 100; $XLS_D = new PHPExcel_Worksheet_Drawing(); //画图片 $XLS_D->setPath($content); $XLS_D->setOffsetX(6); $XLS_D->setOffsetY(3); $XLS_D->setHeight($pic_width); $XLS_D->setWidth($pic_height); $XLS_D->setCoordinates($char); $XLS_D->getShadow()->setVisible(true); $XLS_D->setWorksheet($this->phpexcel->getActiveSheet()); } else { //居中和居右设置 if ($align == "center") { $this->phpexcel->getActiveSheet()->getStyle($char)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); } elseif ($align == "right") { $this->phpexcel->getActiveSheet()->getStyle($char)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); } //垂直居中 $this->phpexcel->getActiveSheet()->getStyle($char)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $this->phpexcel->getActiveSheet()->getStyle($char)->getAlignment()->setWrapText(true); if (!$isnum) { $this->phpexcel->getActiveSheet()->setCellValueExplicit($char, $content, PHPExcel_Cell_DataType::TYPE_STRING); } else { $this->phpexcel->getActiveSheet()->setCellValue($char, $content); } } }
private function setDataIntoSheet($spaces, &$objWorksheet, &$imageNeedDelete = array()) { $alignCenter = array('alignment' => array('vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)); $first = 6; $i = 1; App::import('Vendor', 'PHPExcel', array('file' => 'PHPExcel/PHPExcel.php')); Configure::load('appconfig'); $typeSpace = Configure::read('TypeSpace'); $typeLease = Configure::read('TypeLease'); $objWorksheet->setCellValue('E3', date('m/d/Y')); foreach ($spaces as $value) { //fill alignment $row = $first + $i; $objWorksheet->setCellValue('A' . $row, $i); $objWorksheet->setCellValue('B' . $row, $value['Space']['address'] . ', ' . $value['City']['name'] . ', ' . $value['City']['State']['name']); $objWorksheet->setCellValue('C' . $row, $value['Space']['zip_code']); $objWorksheet->setCellValue('D' . $row, $value['Space']['floor']); $objWorksheet->setCellValue('E' . $row, $value['Space']['square_footage']); $objWorksheet->setCellValue('F' . $row, $value['Space']['availability']); $objWorksheet->setCellValue('G' . $row, $typeSpace[$value['Space']['space']]); $objWorksheet->setCellValue('H' . $row, $value['Space']['price_monthly']); $objWorksheet->setCellValue('I' . $row, $value['Space']['price_year']); $totalYear = 0; $totalMonth = 0; if (!empty($value['Space']['price_monthly'])) { $totalMonth = $value['Space']['price_monthly'] * $value['Space']['square_footage']; } if (!empty($value['Space']['price_year'])) { $totalYear = $value['Space']['price_year'] / 12 * $value['Space']['square_footage']; } $objWorksheet->setCellValue('J' . $row, round($totalMonth, 2)); $objWorksheet->setCellValue('K' . $row, round($totalYear, 2)); $objWorksheet->setCellValue('L' . $row, $typeLease[$value['Space']['type_lease']]); $objWorksheet->setCellValue('M' . $row, $value['Space']['rating']); $objWorksheet->setCellValue('N' . $row, $value['Space']['general_space_note']); $listNote = $value['SpaceNote']; $str = ''; foreach ($listNote as $item) { $str .= $item['SpaceNoteTemplate']['question']; if (isset($item['answer']) && $item['answer'] == 1) { $str .= " (True)\r\n"; } else { $str .= " (False)\r\n"; } } $objWorksheet->setCellValue('O' . $row, $str); $objWorksheet->getStyle('O' . $row)->getAlignment()->setWrapText(true); //Image if (!empty($value['Media'])) { $strVideo = ''; $isImage = false; $col = 'Q'; foreach ($value['Media'] as $item) { if ($item['type'] == MEDIA_IMAGE) { $objDrawing = new PHPExcel_Worksheet_Drawing(); $linkThumb = substr_replace($item['link'], '_thumb', strrpos($item['link'], '.'), 0); if (Utils::smart_resize_image($item['link'], $linkThumb, EXCEL_WIDTH_IMAGE, EXCEL_HEIGHT_IMAGE, false, 'file', false)) { $objDrawing->setPath($linkThumb); $objDrawing->setCoordinates($col . $row); $objDrawing->setHeight(EXCEL_HEIGHT_IMAGE); $objDrawing->setWidth(EXCEL_WIDTH_IMAGE); $objWorksheet->getColumnDimension($col)->setWidth(EXCEL_WIDTH_IMAGE / 6); $objDrawing->setWorksheet($objWorksheet); array_push($imageNeedDelete, $linkThumb); $col = Utils::increase($col); $isImage = true; } } else { $strVideo .= $item['link'] . "\r\n"; } } if (!empty($strVideo)) { $objWorksheet->setCellValue('P' . $row, $strVideo); } if ($isImage) { $objWorksheet->getRowDimension($row)->setRowHeight(EXCEL_HEIGHT_IMAGE - 30); $objWorksheet->duplicateStyleArray($alignCenter, 'A' . $row . ':P' . $row); } } $i++; } $this->setDefaultStyle($objWorksheet, $first, $first + $i - 1); }
private function _exportData2Excel($target = 'Excel2007') { require_once dirname(__FILE__) . "/../Util/PHPExcel/PHPExcel/IOFactory.php"; $session_id = $this->_request('session_id'); $filename = $this->_request('file'); $device_id = $this->_request('device') + 0; if (empty($session_id) || empty($filename) || empty($device_id)) { return; } session_id($session_id); session_write_close(); ignore_user_abort(true); set_time_limit(0); R('File/setfilepercent', array($filename, '正在读取模版文件...')); $objReader = PHPExcel_IOFactory::createReader('Excel2007'); $objPHPExcel = $objReader->load(EXCEL_TEMPLATES_PATH . "track_template.xlsx"); $activeSheet = $objPHPExcel->getActiveSheet(); $condition = $this->_parseCondition(); //先查一下Device信息,以便在没有定位数据的情况下仍然能填写表头数据 $Device = M('Device'); check_error($Device); R('File/setfilepercent', array($filename, '正在查询设备信息...')); $device = $Device->join('`department` on `department`.`id`=`device`.`department_id`')->field(array('`target_type`', '`target_name`', '`department`.`name`' => 'department_name'))->where("`device`.`id`={$device_id}")->find(); check_error($Device); if (empty($device)) { R('File/fileexit', array($filename, '设备id错误:无法查找到指定id的设备')); } R('File/setfilepercent', array($filename, '正在处理表头信息...')); //写表头信息: $activeSheet->setCellValue('A1', $device['target_type'] . '轨迹数据')->setCellValue('A2', '定位对象:' . $device['target_name'])->setCellValue('A3', '所属分组:' . $device['department_name'])->setCellValue('C2', '从:' . $_REQUEST['startTime']); if (!empty($_REQUEST['endTime'])) { $activeSheet->setCellValue('C3', '到:' . $_REQUEST['endTime']); } $Location = M('Location'); check_error($Location); R('File/setfilepercent', array($filename, '正在查询数据库...')); $locations = $Location->join('`device` on `device`.`id`=`location`.`device_id`')->join('`department` on `department`.`id`=`device`.`department_id`')->field(array('`location`.`id`', '`device`.`department_id`', '`department`.`name`' => 'department', '`location`.`device_id`', '`device`.`type`', '`device`.`label`', '`device`.`target_type`', '`device`.`target_id`', '`device`.`target_name`', '`location`.`time`', 'state', 'online', '`location`.`address`', 'baidu_lat', 'baidu_lng', 'speed', 'direction', 'mcc', 'mnc', 'lac', 'cellid', '`location`.`range`'))->where($condition)->order('`time` ASC')->select(); check_error($Location); if (empty($locations)) { $activeSheet->setCellValue('A6', '没有定位数据'); } else { $center = $this->_request('center'); $width = $this->_request('width') + 0; $height = $this->_request('height') + 0; $zoom = $this->_request('zoom') + 0; if (empty($center) || empty($width) || empty($height) || empty($zoom)) { $activeSheet->setCellValue('A6', '地图参数不正确,无法创建地图'); } else { $mapparams = array('center' => $center, 'width' => $width, 'height' => $height, 'zoom' => $zoom); } } $total = count($locations); R('File/setfilepercent', array($filename, '正在处理数据库查询结果...', $total, 0)); $lastTime = time(); $baseRow = 9; $startMarker = null; $endMarker = null; $paths = array(); foreach ($locations as $r => $dataRow) { $row = $baseRow + $r; if ($r) { $activeSheet->insertNewRowBefore($row, 1); } if (!empty($dataRow['baidu_lat']) && !empty($dataRow['baidu_lng'])) { $endMarker = $p = $dataRow['baidu_lng'] . "," . $dataRow['baidu_lat']; if (empty($startMarker)) { $startMarker = $p; } $paths[] = $p; } $activeSheet->setCellValue('A' . $row, $dataRow['time'])->setCellValue('B' . $row, $dataRow['address'])->setCellValue('C' . $row, $dataRow['speed'])->setCellValue('D' . $row, $dataRow['direction'])->setCellValue('E' . $row, $dataRow['state']); $activeSheet->getRowDimension($row)->setRowHeight(-1); if (time() - $lastTime) { //过了1秒 $lastTime = time(); R('File/setfilepercent', array($filename, '正在处理数据库查询结果...', $total, $r + 1)); } } if (!empty($mapparams)) { R('File/setfilepercent', array($filename, '正在准备地图...')); if (!empty($startMarker)) { $mapparams['markers'] = $startMarker; $mapparams['markerStyles'] = 'm,A'; } if (!empty($endMarker) && !empty($mapparams['markers'])) { $mapparams['markers'] .= '|' . $endMarker; $mapparams['markerStyles'] = '|m,B'; } if (!empty($paths)) { $mapparams['paths'] = implode(";", $paths); $mapparams['pathStyles'] = '0xff0000,3,1'; } $img = R('File/getbaidumapstaticimage', array($mapparams)); if ($img) { $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('map'); $objDrawing->setDescription('Map'); $objDrawing->setPath($img); $objDrawing->setCoordinates('A6'); $objDrawing->getShadow()->setVisible(true); $objDrawing->getShadow()->setDirection(45); //调整图片大小,使之适应实际大小 $widthPT = $activeSheet->getColumnDimension('A')->getWidth() + $activeSheet->getColumnDimension('B')->getWidth() + $activeSheet->getColumnDimension('C')->getWidth() + $activeSheet->getColumnDimension('D')->getWidth() + $activeSheet->getColumnDimension('E')->getWidth(); //根据经验调整$widthPT if ($target == 'PDF') { $widthPT *= 4.7499; } else { if ($target == 'Excel5') { $widthPT *= 5.251282; } else { $widthPT *= 6; } } //经验:导出成Excel2007 $widthPX = round($widthPT * 4 / 3); $scale = $objDrawing->getWidth() / $widthPX; $heightPX = round($objDrawing->getHeight() / $scale); $heightPT = $heightPX * 0.75; $objDrawing->setWidth($widthPX); $objDrawing->setHeight($heightPX); $activeSheet->getRowDimension('6')->setRowHeight($heightPT); $objDrawing->setWorksheet($activeSheet); R('File/setfilepercent', array($filename, '地图就绪...')); } else { R('File/setfilepercent', array($filename, '地图准备失败...')); } } return $objPHPExcel; }
protected function addHeader($from, $to) { $this->_row = 1; $structure = Doctrine::getTable('Structure')->createQuery('a')->fetchOne(); PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); $active_sheet = $this->getDocument()->getActiveSheet(); //Add the logo of the structure at the top left of the page: $logo = new PHPExcel_Worksheet_Drawing(); $logo->setName('Logo'); $logo->setDescription('Logo'); $logo->setPath('./uploads/images/logo.png'); $logo->setHeight(60); $logo->setWidth(129); $logo->setCoordinates('A' . $this->_row); $logo->setWorksheet($active_sheet); //Merge the cells at the top right of the page: $active_sheet->mergeCells('B' . $this->_row . ':D' . $this->_row); //and put inside the informations about the structure: $active_sheet->getCell('B' . $this->_row)->setValue($structure->getName() . "\n" . $structure->getAddress()->getStreet() . "\n" . $structure->getAddress()->getAddressCity()->getPostalCode() . " " . $structure->getAddress()->getAddressCity()->getName() . "\n" . "Tel: " . $structure->getTelephoneNumber() . "\n" . "Tel2: " . $structure->getAddress()->getTelephoneNumber() . "\n" . "e-mail: " . $structure->getEmail() . "\n" . "site: " . $structure->getWebsite() . "\n"); $active_sheet->getStyle('B' . $this->_row)->getFont()->setBold(true); $this->nextLine(); //Write the chosen period at the date of document generation: $active_sheet->getCell('A' . $this->_row)->setValue($this->_translate("Period") . ":\n" . $this->_translate("From") . " " . date($this->_translate('m/d/y'), strtotime($from)) . " " . $this->_translate("to") . " " . date($this->_translate('m/d/y'), strtotime($to))); $active_sheet->getCell('B' . $this->_row)->setValue($this->_translate("Generated") . ":\n" . date($this->_translate('m/d/y')) . " " . $this->_translate("at") . " " . date('H:i:s')); $active_sheet->getStyle('A' . $this->_row . ':B' . $this->_row)->getFont()->setSize(8); //General specifications of the page: //Footer: $active_sheet->getHeaderFooter()->setOddFooter('&L&D&RPage &P/&N'); $this->nextLine(2); }
/** * 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); $zip = new ZipArchive(); $zip->open($pFilename); $rels = simplexml_load_string($zip->getFromName("_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($zip->getFromName("{$rel['Target']}")); $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"))); break; case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument": $dir = dirname($rel["Target"]); $relsWorkbook = simplexml_load_string($zip->getFromName("{$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($zip->getFromName("{$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(); $xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']")); $xmlStyles = simplexml_load_string($zip->getFromName("{$dir}/{$xpath['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main"); $numFmts = $xmlStyles->numFmts[0]; if ($numFmts) { $numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main"); } if (!$this->_readDataOnly) { foreach ($xmlStyles->cellXfs->xf as $xf) { $numFmt = $numFmts && $xf["numFmtId"] ? self::array_item($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]")) : 0; //$numFmt = str_replace('mm', 'i', $numFmt); //$numFmt = str_replace('h', 'H', $numFmt); $styles[] = (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); } } $dxfs = array(); if (!$this->_readDataOnly) { foreach ($xmlStyles->dxfs->dxf as $dxf) { $style = new PHPExcel_Style(); $this->_readStyle($style, $dxf); $dxfs[] = $style; } } $xmlWorkbook = simplexml_load_string($zip->getFromName("{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main"); foreach ($xmlWorkbook->sheets->sheet as $eleSheet) { $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($zip->getFromName("{$dir}/{$fileWorksheet}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main"); 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->cols) && !$this->_readDataOnly) { foreach ($xmlSheet->cols->col as $col) { for ($i = intval($col["min"]) - 1; $i < intval($col["max"]) && $i < 256; $i++) { 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 (isset($xmlSheet->printOptions) && !$this->_readDataOnly) { if ($xmlSheet->printOptions['gridLines'] && $xmlSheet->printOptions['gridLinesSet']) { $docSheet->setShowGridlines(true); } } 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"])); } foreach ($row->c as $c) { $r = (string) $c["r"]; switch ($c["t"]) { case "s": $value = $sharedStrings[intval($c->v)]; if ($value instanceof PHPExcel_RichText) { $value = clone $value; } break; case "b": $value = (bool) $c->v; break; case "inlineStr": $value = $this->_parseRichText($c->is); $value->setParent($docSheet->getCell($r)); break; default: if (!isset($c->f)) { $value = (string) $c->v; } else { $value = "={$c->f}"; } break; } if ($value) { $docSheet->setCellValue($r, $value); } if ($c["s"] && !$this->_readDataOnly) { if (isset($styles[intval($c["s"])])) { $this->_readStyle($docSheet->getStyle($r), $styles[intval($c["s"])]); } if (PHPExcel_Shared_Date::isDateTimeFormat($docSheet->getStyle($r)->getNumberFormat())) { if (preg_match("/^([0-9.,-]+)\$/", $value)) { $docSheet->setCellValue($r, PHPExcel_Shared_Date::ExcelToPHP($value)); } } } } } $conditionals = array(); if (!$this->_readDataOnly) { 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) && 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"]); $objConditional->setCondition((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) { foreach ($aKeys as $key) { $method = "set" . ucfirst($key); $docSheet->getProtection()->{$method}($xmlSheet->sheetProtection[$key] == "true"); } } if (!$this->_readDataOnly) { $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->autoFilter && !$this->_readDataOnly) { $docSheet->setAutoFilter((string) $xmlSheet->autoFilter["ref"]); } if ($xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) { foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) { $docSheet->mergeCells((string) $mergeCell["ref"]); } } if (!$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 (!$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"])); } if (isset($xmlSheet->pageSetup["fitToHeight"])) { $docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"])); } if (isset($xmlSheet->pageSetup["fitToWidth"])) { $docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"])); } } if (!$this->_readDataOnly) { $docHeaderFooter = $docSheet->getHeaderFooter(); $docHeaderFooter->setDifferentOddEven($xmlSheet->headerFooter["differentOddEven"] == 'true'); $docHeaderFooter->setDifferentFirst($xmlSheet->headerFooter["differentFirst"] == 'true'); $docHeaderFooter->setScaleWithDocument($xmlSheet->headerFooter["scaleWithDoc"] == 'true'); $docHeaderFooter->setAlignWithMargins($xmlSheet->headerFooter["alignWithMargins"] == '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->rowBreaks->brk && !$this->_readDataOnly) { foreach ($xmlSheet->rowBreaks->brk as $brk) { if ($brk["man"]) { $docSheet->setBreak("A{$brk['id']}", PHPExcel_Worksheet::BREAK_ROW); } } } if ($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->dataValidations && !$this->_readDataOnly) { foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) { // Uppercase coordinate $range = strtoupper($dataValidation["sqref"]); // Extract all cell references in $range $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($range); 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($zip->getFromName(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 trough hyperlinks if ($xmlSheet->hyperlinks) { foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) { // Link url $linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships'); $docSheet->getCell($hyperlink['ref'])->getHyperlink()->setUrl($hyperlinks[(string) $linkRel['id']]); // Tooltip if (isset($hyperlink['tooltip'])) { $docSheet->getCell($hyperlink['ref'])->getHyperlink()->setTooltip((string) $hyperlink['tooltip']); } } } } // Add comments $comments = array(); if (!$this->_readDataOnly) { // Locate comment relations if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($zip->getFromName(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"]; } } } // Loop trough comments foreach ($comments as $relName => $relPath) { // Load comments file $relPath = PHPExcel_Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath); $commentsFile = simplexml_load_string($zip->getFromName($relPath)); // Utility variables $authors = array(); // Loop trough authors foreach ($commentsFile->authors->author as $author) { $authors[] = (string) $author; } // Loop trough 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)); } } } // TODO: Make sure drawings and graph are loaded differently! if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($zip->getFromName(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($zip->getFromName(dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); $images = array(); 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($zip->getFromName($fileDrawing))->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"); foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) { $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 (!$this->_readDataOnly) { $excel->setActiveSheetIndex(intval($xmlWorkbook->bookView->workbookView["activeTab"])); } break; } } return $excel; }
public function actionExcel() { /* Проверка на доступ пользователя к странице */ $permissions_report_task_search = \app\models\Permissions::find()->where('(SUBJECT_TYPE = :subject_type and SUBJECT_ID = :user_id and DEL_TRACT_ID = :del_tract and PERM_LEVEL != :perm_level and ACTION_ID = :action) or (SUBJECT_TYPE = :subject_type_dolg and SUBJECT_ID = :dolg_id and DEL_TRACT_ID = :del_tract and PERM_LEVEL != :perm_level and ACTION_ID = :action)', ['subject_type_dolg' => 1, 'dolg_id' => \Yii::$app->session->get('user.user_iddolg'), 'action' => 82, 'subject_type' => 2, 'user_id' => \Yii::$app->user->id, 'del_tract' => 0, 'perm_level' => 0])->one(); if ($permissions_report_task_search) { /* Проверяем получены ли идентификаторы заданий для формирования отчета */ if (Yii::$app->request->get('ids')) { $issues_ids = Yii::$app->request->get('ids'); $issues_ids = explode(',', $issues_ids); /* Делаем выборку необходимых заданий */ $model = \app\models\Tasks::find()->where(['ID' => $issues_ids])->all(); if ($model) { // Создаем объект класса PHPExcel $xls = new \PHPExcel(); // Устанавливаем индекс активного листа $xls->setActiveSheetIndex(0); // Получаем активный лист $sheet = $xls->getActiveSheet(); // Подписываем лист $sheet->setTitle('Отчет по отобранным заданиям'); $sheet->getStyle('A1')->getFont()->setBold(true); // Вставляем текст в ячейку A1 $sheet->setCellValue("A1", 'Отчет по отобранным заданиям'); $sheet->getStyle('A1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID); $sheet->getStyle('A1')->getFill()->getStartColor()->setRGB('EEEEEE'); // Объединяем ячейки $sheet->mergeCells('A1:I1'); // Выравнивание текста $sheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Формируем шапку $sheet->setCellValue("A2", 'Заказ ПЭО'); $sheet->setCellValue("B2", 'Номер заказа'); $sheet->setCellValue("C2", 'Проект/Тема'); $sheet->setCellValue("D2", 'Обозначение'); $sheet->setCellValue("E2", 'Наименование'); $sheet->setCellValue("F2", 'Срок выполнения'); $sheet->setCellValue("G2", 'Статус'); $sheet->setCellValue("H2", 'Ф.И.О. и Дата'); $sheet->setCellValue("I2", 'Форматов А4'); /* устанавливаем ширину колонок и стили*/ $sheet->getStyle('A2:I2')->getFont()->setBold(true); $sheet->getColumnDimension('A')->setAutoSize(true); $sheet->getColumnDimension('B')->setAutoSize(true); $sheet->getColumnDimension('C')->setAutoSize(true); $sheet->getColumnDimension('D')->setAutoSize(true); $sheet->getColumnDimension('E')->setAutoSize(true); $sheet->getColumnDimension('F')->setAutoSize(true); $sheet->getColumnDimension('G')->setWidth(20); $sheet->getColumnDimension('H')->setAutoSize(true); $sheet->getColumnDimension('I')->setAutoSize(true); $row_number = 3; foreach ($model as $task) { $sheet->setCellValue("A" . $row_number, $task->PEOORDERNUM); $sheet->setCellValue("B" . $row_number, $task->ORDERNUM); $sheet->setCellValue("C" . $row_number, ''); $sheet->setCellValue("D" . $row_number, $task->TASK_NUMBER); $sheet->setCellValue("E" . $row_number, 'Задание'); $sheet->setCellValue("F" . $row_number, \Yii::$app->formatter->asDate($task->DEADLINE, 'php:d-m-Y')); //вставляем информацию по статусам $task_states = \app\models\TaskStates::find()->where(['TASK_ID' => $task->ID])->orderBy('STATE_ID ASC')->all(); if ($task_states) { foreach ($task_states as $state) { $state_date = $state->getStateDate(); $logo = new \PHPExcel_Worksheet_Drawing(); $logo->setPath(Yii::getAlias('@webroot') . '/images/items_status/' . $state->getStateColour() . '.png'); $logo->setCoordinates("G" . $row_number); $logo->setOffsetX(5); $logo->setOffsetY(2); $logo->setResizeProportional(true); $logo->setWidth(16); $logo->setWorksheet($sheet); $sheet->setCellValue("G" . $row_number, ' ' . $state->getStateName()); $pers_tasks = \app\models\PersTasks::findOne($state->PERS_TASKS_ID); $query = new \yii\db\Query(); $query->select('*')->from('STIGIT.V_F_PERS')->where('TN = \'' . $pers_tasks->TN . '\''); $command = $query->createCommand(); $data = $command->queryOne(); $sheet->setCellValue("H" . $row_number, $data['FIO'] . ' ' . $state_date); $task_docs = \app\models\TaskDocs::find()->where(['PERS_TASKS_ID' => $state->PERS_TASKS_ID])->one(); if ($task_docs) { $quantity = $task_docs->FORMAT_QUANTITY; } else { $quantity = 0; } $sheet->setCellValue("I" . $row_number, $quantity); $row_number++; } } $row_number++; } //стили для рамки таблицы $styleArray = array('borders' => array('allborders' => array('style' => \PHPExcel_Style_Border::BORDER_THIN))); $total_rows = $row_number - 1; $sheet->getStyle('A1:I' . $total_rows)->applyFromArray($styleArray); //параметры страницы для печати - альбомная $xls->getActiveSheet()->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $xls->getActiveSheet()->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $xls->getActiveSheet()->getPageSetup()->setFitToPage(true); $xls->getActiveSheet()->getPageSetup()->setFitToWidth(1); $xls->getActiveSheet()->getPageSetup()->setFitToHeight(0); // Выводим HTTP-заголовки header("Expires: Mon, 1 Apr 1974 05:00:00 GMT"); header("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT"); header("Cache-Control: no-cache, must-revalidate"); header("Pragma: no-cache"); header("Content-type: application/vnd.ms-excel"); header("Content-Disposition: attachment; filename=report.xls"); //Выводим содержимое файла $objWriter = new \PHPExcel_Writer_Excel5($xls); $objWriter->save('php://output'); } else { /* Вызываем эксепшн в случае, если были переданы не верные параметры заданий */ throw new \yii\web\NotFoundHttpException('Что-то пошло не так. Пожалуйста, обратитесь к администратору системы.'); } } } else { /* Вызываем эксепшн в случае, если доступ к формированию отчета запрещен */ throw new \yii\web\ForbiddenHttpException('У Вас нет прав на редактирование "Формирование отчета"'); } }
public function down_order_excel($id) { //$this->load->library('PHPExcel'); //$this->load->library('PHPExcel/IOFactory'); require_once APPPATH . 'libraries/PHPExcel' . EXT; require_once APPPATH . 'libraries/PHPExcel/IOFactory' . EXT; require_once APPPATH . 'libraries/PHPExcel/Worksheet/Drawing' . EXT; $CI =& get_instance(); $order = $this->order_model->get_order($id); //var_dump($order);die(); if ($order) { $print_date = date('Y.m.d'); $user = fetch_user_name_by_id(fetch_user_id_by_login_name($order->input_user)); $country_cn_name = get_country_name_cn($order->country); $objPHPExcel = new PHPExcel(); $objPHPExcel->createSheet(); //创建sheet $objPHPExcel->setActiveSheetIndex(0); $objActSheet = $objPHPExcel->getActiveSheet(); $objPHPExcel->getProperties()->setCreator("mallerp")->setLastModifiedBy("mallerp")->setTitle("Ebay ShipOrder List")->setSubject("Ebay ShipOrder List")->setDescription("Mansea, Mallerp, zhaosenlin, 278203374, 7410992")->setKeywords("Mansea, Mallerp, zhaosenlin, 278203374, 7410992")->setCategory("mallerp"); $objActSheet->getColumnDimension('E')->setWidth(20); $objActSheet->getColumnDimension('A')->setWidth(0); /*excel表头*/ $objPHPExcel->getActiveSheet()->setCellValue('A1', ''); $objPHPExcel->getActiveSheet()->setCellValue('A2', ''); $objPHPExcel->getActiveSheet()->setCellValue('A3', ''); $objPHPExcel->getActiveSheet()->setCellValue('A4', ''); $objActSheet->getStyle('B1:B4')->getFont()->setSize(12); $objActSheet->getStyle('B1:B4')->getFont()->setBold(true); $objActSheet->getStyle('D1:D4')->getFont()->setBold(true); $objActSheet->getStyle('F1:F4')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->mergeCells('H1:M4'); $objPHPExcel->getActiveSheet()->setCellValue('B1', '业务员'); $objPHPExcel->getActiveSheet()->setCellValue('C1', $user); $objPHPExcel->getActiveSheet()->setCellValue('D1', 'ERP订单号'); $objPHPExcel->getActiveSheet()->setCellValue('E1', $order->item_no); $objPHPExcel->getActiveSheet()->setCellValue('F1', '日期'); $objPHPExcel->getActiveSheet()->setCellValue('G1', $print_date); $objPHPExcel->getActiveSheet()->setCellValue('H1', '备注:' . $order->note); $objPHPExcel->getActiveSheet()->setCellValue('B2', '客户名称'); $objPHPExcel->getActiveSheet()->setCellValue('C2', $order->name); $objPHPExcel->getActiveSheet()->setCellValue('D2', '国家'); $objPHPExcel->getActiveSheet()->setCellValue('E2', $country_cn_name); $objPHPExcel->getActiveSheet()->setCellValue('F2', '运输方式'); $objPHPExcel->getActiveSheet()->setCellValue('G2', $order->is_register); $objPHPExcel->getActiveSheet()->setCellValue('B3', '订单总额$'); $objPHPExcel->getActiveSheet()->setCellValue('C3', $order->gross); $objPHPExcel->getActiveSheet()->setCellValue('D3', '运费$'); $objPHPExcel->getActiveSheet()->setCellValue('E3', $order->shippingamt); $objPHPExcel->getActiveSheet()->setCellValue('F3', '付款方式'); $objPHPExcel->getActiveSheet()->setCellValue('G3', $order->payment_type); $objPHPExcel->getActiveSheet()->setCellValue('B4', '订单总额¥'); $objPHPExcel->getActiveSheet()->setCellValue('C4', $order->gross * $order->ex_rate); $objPHPExcel->getActiveSheet()->setCellValue('D4', '运费¥'); $objPHPExcel->getActiveSheet()->setCellValue('E4', $order->shippingamt * $order->ex_rate); $objPHPExcel->getActiveSheet()->setCellValue('F4', '实际运费'); $objPHPExcel->getActiveSheet()->setCellValue('G4', ''); $objPHPExcel->getActiveSheet()->setCellValue('H4', '交易号'); $objPHPExcel->getActiveSheet()->setCellValue('I4', $order->transaction_id); //$objPHPExcel->getActiveSheet()->mergeCells("F5":"G5":"H5"); $objActSheet->getStyle('B5:M5')->getFont()->setSize(12); $objActSheet->getStyle('B5:M5')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->mergeCells('F5:H5'); $objPHPExcel->getActiveSheet()->setCellValue('B5', '货架号'); $objPHPExcel->getActiveSheet()->setCellValue('C5', 'SKU'); $objPHPExcel->getActiveSheet()->setCellValue('D5', '数量'); $objPHPExcel->getActiveSheet()->setCellValue('E5', '图片'); $objPHPExcel->getActiveSheet()->setCellValue('F5', '中文名称'); $objPHPExcel->getActiveSheet()->setCellValue('I5', '单价$'); $objPHPExcel->getActiveSheet()->setCellValue('J5', '单价¥'); $objPHPExcel->getActiveSheet()->setCellValue('K5', '总价¥'); $objPHPExcel->getActiveSheet()->setCellValue('L5', '成本¥'); $objPHPExcel->getActiveSheet()->setCellValue('M5', '利润¥'); $skus = explode(',', $order->sku_str); $qties = explode(',', $order->qty_str); $item_prices = explode(',', $order->item_price_str); $i = 5; foreach ($skus as $key => $sku) { $i++; $sql1 = 'name_cn,shelf_code,sale_price,image_url,price'; $myproduct = $CI->product_model->fetch_product_by_sku($sku, $sql1); $image = ""; if ($myproduct->image_url != '' && $myproduct->image_url != NULL && $myproduct->image_url != 'none') { if (strpos($myproduct->image_url, 'http://') !== false) { $image = "/var/www/html/mallerp/static/images/404-error.png"; } else { $image = '/var/www/html/mallerp' . $myproduct->image_url; } } else { $image = "/var/www/html/mallerp/static/images/404-error.png"; } //die($image); $objActSheet->getRowDimension($i)->setRowHeight(100); $objActSheet->getStyle('B' . $i . ':M' . $i)->getFont()->setSize(12); $objPHPExcel->getActiveSheet()->mergeCells('F' . $i . ':H' . $i); $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $myproduct->shelf_code); $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $sku); $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $qties[$key]); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('avatar'); $objDrawing->setDescription('avatar'); $objDrawing->setPath($image); $objDrawing->setHeight(100); $objDrawing->setWidth(100); $objDrawing->setCoordinates('E' . $i); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); //$objPHPExcel->getActiveSheet()->setCellValue('E'.$i, '图片'); $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $myproduct->name_cn); $objPHPExcel->getActiveSheet()->setCellValue('I' . $i, isset($item_prices[$key]) ? $item_prices[$key] : 0); $objPHPExcel->getActiveSheet()->setCellValue('J' . $i, isset($item_prices[$key]) ? $item_prices[$key] : 0 * $order->ex_rate); $objPHPExcel->getActiveSheet()->setCellValue('K' . $i, isset($item_prices[$key]) ? $item_prices[$key] : 0 * $order->ex_rate * $qties[$key]); $objPHPExcel->getActiveSheet()->setCellValue('L' . $i, ''); $objPHPExcel->getActiveSheet()->setCellValue('M' . $i, ''); } } $filename = date("Y-m-d_H_i_s") . '.xls'; ob_end_clean(); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename=' . $filename); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); }
// 将背景设置为浅粉色 // 设置单元格格式(数字格式) $objPHPExcel->getActiveSheet()->getStyle('F1')->getNumberFormat()->setFormatCode('0.000'); // 给特定单元格中写入内容 $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello Baby'); $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Hello Baby'); $objPHPExcel->getActiveSheet()->setCellValue('H5', '444'); // 设置单元格样式(居中) $objPHPExcel->getActiveSheet()->getStyle('H5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // 给单元格中放入图片, 将数据图片放在J1单元格内 $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('Logo'); $objDrawing->setDescription('Logo'); $objDrawing->setPath("./images/logo.png"); // 图片路径,只能是相对路径 $objDrawing->setWidth(400); // 图片宽度 $objDrawing->setHeight(123); // 图片高度 $objDrawing->setCoordinates('J1'); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); // 设置A5单元格内容并增加超链接 $objPHPExcel->getActiveSheet()->setCellValue('A5', iconv('gbk', 'utf-8', '超链接keiyi.com')); $objPHPExcel->getActiveSheet()->getCell('A5')->getHyperlink()->setUrl('http://www.keiyi.com/'); $objWriter = PHPExcel_IOFactory::createWriter($m_objPHPExcel, 'Excel5'); $m_strOutputExcelFileName = date('Y-m-j_H_i_s') . ".xls"; // 输出EXCEL文件名 header("Pragma: public"); header("Expires: 0"); header("Cache-Control:must-revalidate, post-check=0, pre-check=0"); header("Content-Type:application/force-download");
function excel() { //load our new PHPExcel library $this->load->library('excel'); //activate worksheet number 1 $this->excel->setActiveSheetIndex(0); //name the worksheet $this->excel->getActiveSheet()->setTitle('Master Barang'); $this->excel->getActiveSheet()->mergeCells('A1:G6'); //$this->excel->getActiveSheet()->getStyle('C1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('Logo'); $objDrawing->setDescription('Logo'); $objDrawing->setPath('D:/logo-po.jpg'); $objDrawing->setCoordinates('C1'); $objDrawing->setOffsetX(150); $objDrawing->setHeight(108); $objDrawing->setWidth(637); $objDrawing->setWorksheet($this->excel->getActiveSheet()); //$this->excel->getActiveSheet()->setCellValue('C1', 'PT Gramaselindo'); $this->excel->getActiveSheet()->setCellValue('A7', 'No'); $this->excel->getActiveSheet()->setCellValue('B7', 'Kode'); $this->excel->getActiveSheet()->setCellValue('C7', 'Deskripsi'); $this->excel->getActiveSheet()->setCellValue('D7', 'Alias'); $this->excel->getActiveSheet()->setCellValue('E7', 'Jenis Barang'); $this->excel->getActiveSheet()->setCellValue('F7', 'Satuan Dasar'); $this->excel->getActiveSheet()->setCellValue('G7', 'Satuan Laporan'); for ($col = ord('A'); $col <= ord('F'); $col++) { //set column dimension $this->excel->getActiveSheet()->getColumnDimension(chr($col))->setAutoSize(false); //change the font size $this->excel->getActiveSheet()->getStyle(chr($col))->getFont()->setSize(12); } $this->excel->getActiveSheet()->getStyle(chr(ord('A')))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->excel->getActiveSheet()->getStyle(chr(ord('B')))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->excel->getActiveSheet()->getStyle(chr(ord('C')))->getAlignment()->setWrapText(true); $this->excel->getActiveSheet()->getStyle(chr(ord('D')))->getAlignment()->setWrapText(true); $this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(75); $this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(25); $this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(20); $this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(20); $this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(20); $rs = $this->barang->get_barang(); //print_//mz($rs); $exceldata = ""; foreach ($rs as $row) { $exceldata[] = $row; } //Fill data $this->excel->getActiveSheet()->fromArray($exceldata, null, 'A8'); $this->excel->getActiveSheet()->getStyle('A7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->excel->getActiveSheet()->getStyle('B7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->excel->getActiveSheet()->getStyle('C7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $filename = 'Master Barang.xls'; //save our workbook as this file name header('Content-Type: application/vnd.ms-excel'); //mime type header('Content-Disposition: attachment;filename="' . $filename . '"'); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type) //if you want to save it as .XLSX Excel 2007 format $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5'); //force user to download the Excel file without writing it to server's HD $objWriter->save('php://output'); }
/** * Вставить в указанную ячейку изображение */ protected function insert_image($img, $activeSheet, $coord) { $iDrowing = new PHPExcel_Worksheet_Drawing(); // Указываем путь к файлу изображения $iDrowing->setPath($this->path . $img['src']); // Устанавливаем ячейку $iDrowing->setCoordinates($coord); // Устанавливаем смещение X и Y if ($img['offsetX'] > 0) { $iDrowing->setOffsetX($img['offsetX']); } if ($img['offsetY'] > 0) { $iDrowing->setOffsetY($img['offsetY']); } // Устанавливаем размеры изображения width и height if ($img['width'] > 0) { $iDrowing->setWidth($img['width']); } if ($img['height'] > 0) { $iDrowing->setHeight($img['height']); } //помещаем на лист $iDrowing->setWorksheet($activeSheet); }
public function exportFlightTaskToXls($xls, $firstSheet, $row, $progressBar) { foreach ($firstSheet->getRowDimensions() as $rd) { $rd->setRowHeight(-1); } $xls->getProperties()->setCreator(Kwf_Config::getValue('application.name')); $xls->getProperties()->setLastModifiedBy(Kwf_Config::getValue('application.name')); $xls->getProperties()->setTitle("Полетное Задание"); $xls->getProperties()->setSubject("Полетное Задание"); $xls->getProperties()->setDescription("Полетное Задание на сегодня"); $xls->getProperties()->setKeywords(""); $xls->getProperties()->setCategory(""); $firstSheet->getPageSetup()->setFitToPage(true); $firstSheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $firstSheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $firstSheet->setTitle('Титульный лист'); $pageMargins = $firstSheet->getPageMargins(); $margin = 0.42; $pageMargins->setTop($margin * 2); $pageMargins->setBottom($margin); $pageMargins->setLeft($margin); $pageMargins->setRight($margin); $styleThinBlackBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000')))); $totalLeftColumns = 28; for ($i = 0; $i <= $totalLeftColumns - 1; $i++) { $firstSheet->getColumnDimension($this->_getColumnLetterByIndex($i))->setWidth('2.0pt'); } $tableColumn = $this->_getColumnLetterByIndex($totalLeftColumns - 1); $tableHeaderColumnt = $this->_getColumnLetterByIndex($totalLeftColumns); $firstSheet->getColumnDimension($this->_getColumnLetterByIndex($totalLeftColumns + 1))->setWidth('10pt'); $firstSheet->mergeCells($this->_getColumnLetterByIndex($totalLeftColumns + 1) . '1:' . $this->_getColumnLetterByIndex($totalLeftColumns + 1) . '39'); $firstSheet->getStyle('A1:' . $tableColumn . '13')->applyFromArray($styleThinBlackBorderOutline); $firstSheet->mergeCells('A1:' . $tableColumn . '13'); $firstSheet->getStyle($tableHeaderColumnt . '1:' . $tableHeaderColumnt . '13')->applyFromArray($styleThinBlackBorderOutline); $firstSheet->mergeCells($tableHeaderColumnt . '1:' . $tableHeaderColumnt . '13'); $firstSheet->setCellValue($tableHeaderColumnt . '1', 'Предполётный медосмотр'); $firstSheet->getStyle($tableHeaderColumnt . '1')->getAlignment()->setWrapText(true); $firstSheet->getStyle($tableHeaderColumnt . '1')->getFont()->setBold(true); $firstSheet->getStyle($tableHeaderColumnt . '1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $firstSheet->getStyle($tableHeaderColumnt . '1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $firstSheet->getStyle($tableHeaderColumnt . '1')->getAlignment()->setTextRotation(-90); $firstSheet->getStyle('A14:' . $tableColumn . '26')->applyFromArray($styleThinBlackBorderOutline); $firstSheet->mergeCells('A14:' . $tableColumn . '26'); $firstSheet->getStyle($tableHeaderColumnt . '14:' . $tableHeaderColumnt . '26')->applyFromArray($styleThinBlackBorderOutline); $firstSheet->mergeCells($tableHeaderColumnt . '14:' . $tableHeaderColumnt . '26'); $firstSheet->setCellValue($tableHeaderColumnt . '14', 'Спецконроль в аэропортах'); $firstSheet->getStyle($tableHeaderColumnt . '14')->getAlignment()->setWrapText(true); $firstSheet->getStyle($tableHeaderColumnt . '14')->getFont()->setBold(true); $firstSheet->getStyle($tableHeaderColumnt . '14')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $firstSheet->getStyle($tableHeaderColumnt . '14')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $firstSheet->getStyle($tableHeaderColumnt . '14')->getAlignment()->setTextRotation(-90); $firstSheet->getStyle('A27:' . $tableColumn . '39')->applyFromArray($styleThinBlackBorderOutline); $firstSheet->mergeCells('A27:B39'); $firstSheet->getStyle($tableHeaderColumnt . '27:' . $tableHeaderColumnt . '39')->applyFromArray($styleThinBlackBorderOutline); $firstSheet->mergeCells($tableHeaderColumnt . '27:' . $tableHeaderColumnt . '39'); $firstSheet->setCellValue($tableHeaderColumnt . '27', 'Результаты послеполётного разбора'); $firstSheet->getStyle($tableHeaderColumnt . '27')->getFont()->setBold(true); $firstSheet->getStyle($tableHeaderColumnt . '27')->getAlignment()->setWrapText(true); $firstSheet->getStyle($tableHeaderColumnt . '27')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $firstSheet->getStyle($tableHeaderColumnt . '27')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $firstSheet->getStyle($tableHeaderColumnt . '27')->getAlignment()->setTextRotation(-90); $progressBar->update(10); for ($i = 2; $i <= $totalLeftColumns - 1; $i++) { $col = $this->_getColumnLetterByIndex($i); $firstSheet->mergeCells($col . '27:' . $col . '39'); } $rightColumn = $totalLeftColumns + 2; $rowNumber = 1; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $rowNumber += 1; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getFont()->setSize(10); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn) . $rowNumber, trlKwf('Дальневосточное межрегиональное территориальное управление')); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getFont()->setBold(true); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $rowNumber += 1; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getFont()->setSize(10); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn) . $rowNumber, trlKwf('воздушного транспорта ФАВТ')); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getFont()->setBold(true); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $rowNumber += 1; $firstSheet->getColumnDimension($this->_getColumnLetterByIndex($rightColumn))->setWidth('15pt'); $firstSheet->getColumnDimension($this->_getColumnLetterByIndex($rightColumn + 1))->setWidth('15pt'); $firstSheet->getColumnDimension($this->_getColumnLetterByIndex($rightColumn + 2))->setWidth('10pt'); $firstSheet->getColumnDimension($this->_getColumnLetterByIndex($rightColumn + 3))->setWidth('10pt'); $firstSheet->getColumnDimension($this->_getColumnLetterByIndex($rightColumn + 4))->setWidth('15pt'); $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $rowNumber += 1; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . ($rowNumber + 3)); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('Logo'); $objDrawing->setDescription('Logo'); $objDrawing->setPath('./images/doc_logo.png'); $objDrawing->setCoordinates($this->_getColumnLetterByIndex($rightColumn) . $rowNumber); $objDrawing->setWidth('360px'); $objDrawing->setOffsetX(50); $objDrawing->setWorksheet($firstSheet); $rowNumber += 4; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $rowNumber += 1; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 2) . $rowNumber); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getFont()->setBold(true); $flightsModel = Kwf_Model_Abstract::getInstance('Flights'); $flightsSelect = $flightsModel->select()->whereEquals('planId', $row->planId)->order(array('subCompanyId', 'flightStartTime')); $flights = $flightsModel->getRows($flightsSelect); $flightSequenceNumber = 1; $lastSubcompanyId = 0; foreach ($flights as $flight) { if ($lastSubcompanyId != $flight->subCompanyId) { $flightSequenceNumber = 0; } $lastSubcompanyId = $flight->subCompanyId; $flightSequenceNumber += 1; if ($flight->id == $row->id) { break; } } $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn) . $rowNumber, trlKwf('ЗАДАНИЕ НА ПОЛЁТ №') . $row->number); #$firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn + 2) . $rowNumber, $flightSequenceNumber . ' / ' . $row->flightStartDate); #$firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber, $row->number); #$firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber)->getFont()->setBold(true); $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn + 3) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn + 3) . $rowNumber, 'ЮШ ' . $row->requestNumber); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn + 3) . $rowNumber)->getFont()->setBold(true); $rowNumber += 1; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $rowNumber += 1; $planesModel = Kwf_Model_Abstract::getInstance('Airplanes'); $planesSelect = $planesModel->select()->whereEquals('id', $row->planeId); $plane = $planesModel->getRow($planesSelect); $typeModel = Kwf_Model_Abstract::getInstance('Wstypes'); $typeSelect = $typeModel->select()->whereEquals('id', $plane->twsId); $planeType = $typeModel->getRow($typeSelect); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn) . $rowNumber, 'Экипажу вертолёта:'); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn + 2) . $rowNumber, $planeType->Name); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber, $row->planeName); $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn + 2) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 3) . $rowNumber); $rowNumber += 1; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $rowNumber += 1; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn) . $rowNumber, 'СОСТАВ ЭКИПАЖА'); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getFont()->setBold(true); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $rowNumber += 1; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn) . $rowNumber, 'Должность'); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getFont()->setBold(true); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber, 'ФИО'); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber)->getFont()->setBold(true); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $flightGroupsModel = Kwf_Model_Abstract::getInstance('Flightgroups'); $flightGroupsSelect = $flightGroupsModel->select()->whereEquals('flightId', $row->id)->whereEquals('mainCrew', TRUE)->order('id'); $flightMembers = $flightGroupsModel->getRows($flightGroupsSelect); $rowNumber += 1; $employeesModel = Kwf_Model_Abstract::getInstance('Employees'); $subSpecModel = Kwf_Model_Abstract::getInstance('Linkdata'); $kwsId = 0; $progressBar->update(50); foreach ($flightMembers as $flightMember) { $employeesSelect = $employeesModel->select()->whereEquals('id', $flightMember->employeeId); $employeeRow = $employeesModel->getRow($employeesSelect); if ($employeeRow == NULL) { continue; } $subSpecSelect = $subSpecModel->select()->whereEquals('id', $employeeRow->positionId); $subSpecRow = $subSpecModel->getRow($subSpecSelect); $position = $flightMember->positionName; if ($position == 'КВС') { $kwsId = $flightMember->employeeId; } $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn) . $rowNumber, $position); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber, (string) $employeeRow); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber)->getFont()->setBold(true); $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $rowNumber += 1; } $progressBar->update(60); $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $rowNumber += 1; $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn) . $rowNumber, 'Дата вылета: '); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 2) . $rowNumber); $flightDate = new DateTime($row->flightStartDate); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber, $this->russianDate($flightDate->format('d-m-Y'))); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber)->getFont()->setBold(true); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn + 3) . $rowNumber, 'Время: '); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn + 3) . $rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $flightStartTime = new DateTime($row->flightStartTime); $flightStartTime = $flightStartTime->format("H:i"); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber, $flightStartTime); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber)->getFont()->setBold(true); $rowNumber += 1; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); // $rowNumber += 1; // $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn) . ($rowNumber + 1)); // // $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn) . $rowNumber, 'Маршрут полёта'); // $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); // // $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber, $row->routeName); // $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); // $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn + 1) . ($rowNumber + 1) . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . ($rowNumber + 1)); $rowNumber += 1; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn) . ($rowNumber + 1)); $objectiveModel = Kwf_Model_Abstract::getInstance('Linkdata'); $objectiveSelect = $objectiveModel->select()->whereEquals('id', $row->objectiveId); $objective = $objectiveModel->getRow($objectiveSelect); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn) . $rowNumber, 'Цель полёта'); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber, $objective->desc); $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn + 1) . ($rowNumber + 1) . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . ($rowNumber + 1)); $rowNumber += 2; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn) . ($rowNumber + 1)); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn) . $rowNumber, 'Пункты посадки'); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber, $row->routeName); $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn + 1) . ($rowNumber + 1) . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . ($rowNumber + 1)); $rowNumber += 2; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $rowNumber += 1; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn) . $rowNumber, 'В ПОЛЕТНОЕ ЗАДАНИЕ ВКЛЮЧИТЬ'); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getFont()->setBold(true); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $rowNumber += 1; $flightGroupsModel = Kwf_Model_Abstract::getInstance('Flightgroups'); $flightGroupsSelect = $flightGroupsModel->select()->whereEquals('flightId', $row->id)->whereEquals('mainCrew', FALSE)->order('id'); $flightMembers = $flightGroupsModel->getRows($flightGroupsSelect); $employeesModel = Kwf_Model_Abstract::getInstance('Employees'); $subSpecModel = Kwf_Model_Abstract::getInstance('Linkdata'); $progressBar->update(70); foreach ($flightMembers as $flightMember) { $employeesSelect = $employeesModel->select()->whereEquals('id', $flightMember->employeeId); $employeeRow = $employeesModel->getRow($employeesSelect); $position = $flightMember->positionName; if ($position == 'По специальности') { if ($employeeRow->positionId == NULL) { $position = ''; } else { $subSpecSelect = $subSpecModel->select()->whereEquals('id', $employeeRow->positionId); $subSpecRow = $subSpecModel->getRow($subSpecSelect); $position = $subSpecRow->value; } } if ($employeeRow != NULL) { $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn) . $rowNumber, $position); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber, (string) $employeeRow); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber)->getFont()->setBold(true); $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn + 1) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $rowNumber += 1; } } $progressBar->update(80); $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $rowNumber += 1; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn) . $rowNumber, 'ЭКИПАЖ ДОПУЩЕН ПО МЕТЕОМИНИМУМУ'); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getFont()->setBold(true); $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . $rowNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $rowNumber += 1; $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $rowNumber += 1; $accessesModel = Kwf_Model_Abstract::getInstance('Flightaccesses'); $accessesSelect = $accessesModel->select()->where(new Kwf_Model_Select_Expr_Sql("`employeeId` = " . $kwsId . " AND `wsTypeId` = " . $planeType->id)); $accesses = $accessesModel->getRows($accessesSelect); foreach ($accesses as $access) { $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $firstSheet->setCellValue($this->_getColumnLetterByIndex($rightColumn) . $rowNumber, $access->accessName); $rowNumber += 1; } $firstSheet->mergeCells($this->_getColumnLetterByIndex($rightColumn) . $rowNumber . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . $rowNumber); $rowNumber += 1; $firstSheet->getStyle($this->_getColumnLetterByIndex($rightColumn) . 1 . ':' . $this->_getColumnLetterByIndex($rightColumn + 4) . 39)->applyFromArray($styleThinBlackBorderOutline); # Second page $xls->setActiveSheetIndex(1); $secondSheet = $xls->getActiveSheet(); $secondSheet->getPageSetup()->setFitToPage(true); $secondSheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $secondSheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $secondSheet->setTitle('Отчет о полете'); $pageMargins = $secondSheet->getPageMargins(); $margin = 0.2; $pageMargins->setTop($margin); $pageMargins->setBottom($margin); $pageMargins->setLeft($margin); $pageMargins->setRight($margin); for ($column = 0; $column < 70; $column++) { $secondSheet->getColumnDimension($this->_getColumnLetterByIndex($column))->setWidth('2.5pt'); } $secondSheet->getPageSetup()->setFitToPage(true); $xls->setActiveSheetIndex(0); $progressBar->update(100); }
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("B4", 'Empresa: ' . $_SESSION['empresa'] . ''); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B4:C4'); $objPHPExcel->getActiveSheet()->getStyle("B4:C4")->getFont()->setBold(false)->setName('Verdana')->setSize(12); ////////////////////////// $objPHPExcel->setActiveSheetIndex(0)->setCellValue("D4", 'Propietario: ' . $_SESSION['propietario'] . ''); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('D4:E4'); $objPHPExcel->getActiveSheet()->getStyle("D4:E4")->getFont()->setBold(false)->setName('Verdana')->setSize(12); ///////////////////////// $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('PHPExcel logo'); $objDrawing->setDescription('PHPExcel logo'); $objDrawing->setPath('../images/logo_empresa.jpg'); // $objDrawing->setHeight(50); // sets the image $objDrawing->setWidth(150); // sets the image $objDrawing->setCoordinates('G4'); // pins the top-left corner $objDrawing->setOffsetX(0); // pins the top left $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); $consulta = pg_query("select id_director, identificacion_dire ,nombres,direccion,telefono,celular from directores where id_director='{$_GET['id']}'"); while ($row = pg_fetch_row($consulta)) { $objPHPExcel->getActiveSheet()->getStyle('B6:H6:')->applyFromArray($borders); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("B6", 'RUC/CI: ' . $row[1]); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B6:C6'); $objPHPExcel->getActiveSheet()->getStyle("B6:C6")->getFont()->setBold(false)->setName('Verdana')->setSize(12); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("E6", 'DIRECTOR: ' . $row[2]); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E6:F6'); $objPHPExcel->getActiveSheet()->getStyle("E6:F6")->getFont()->setBold(false)->setName('Verdana')->setSize(12);
public function exportExcel($conf = array()) { $data = $conf['data']; $name = $conf['filename'] . '-' . date('Y-m-d H-i-s'); $field = explode(',', $conf['field'][0]); $fieldtitle = explode(',', $conf['field'][1]); $objPHPExcel = new \PHPExcel(); //以下是一些设置 ,什么作者 标题啊之类的 $objPHPExcel->getProperties()->setCreator("ainiku")->setLastModifiedBy("ainiku")->setTitle("feilv export")->setSubject("feilv export")->setDescription("bakdata")->setKeywords("excel")->setCategory("result file"); //设置表头 $obj = $objPHPExcel->setActiveSheetIndex(0); $fieldnum = count($fieldtitle); $j = 65; foreach ($fieldtitle as $v) { // $obj->setCellValue(chr($j++) . '1', ' ' . $v); } //Set border colors 设置边框颜色 //$obj->freezePane(chr(65).'1:'.chr($j).'1'); $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300'); $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300'); $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300'); $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300'); //Set border colors 设置背景颜色 //$objPHPExcel->getActiveSheet()->getStyle(chr(65).'1:'.chr($j).'1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID); //$objPHPExcel->getActiveSheet()->getStyle(chr(65).'1:'.chr($j).'1')->getFill()->getStartColor()->setARGB('FFededed'); //以下就是对处理Excel里的数据, 横着取数据,主要是这一步,其他基本都不要改 // 固定第一行 $obj->freezePane('A1'); $fieldnum = count($field); foreach ($data as $k => $v) { $num = $k + 2; $temfield = $field; $j = 'A'; $i = 0; //$obj=$objPHPExcel->setActiveSheetIndex(0); for ($i; $i < $fieldnum; $i++) { $temstr = array_shift($temfield); if (substr($temstr, 0, 1) == "'") { $temstr = str_replace("'", '', $temstr); $obj->setCellValue($j . $num, ' ' . $v[$temstr]); } else { if (substr($temstr, 0, 5) == "#pic#") { //插入图片 $temstr = str_replace("#pic#", '', $temstr); $img = new \PHPExcel_Worksheet_Drawing(); $img->setPath($v[$temstr]); //写入图片路径 $img->setHeight(100); //写入图片高度 $img->setWidth(100); //写入图片宽度 $img->setOffsetX(1); //写入图片在指定格中的X坐标值 $img->setOffsetY(1); //写入图片在指定格中的Y坐标值 $img->setRotation(1); //设置旋转角度 $img->getShadow()->setVisible(true); // $img->getShadow()->setDirection(50); // $img->setCoordinates($j . $num); //设置图片所在表格位置 //$objPHPExcel->getColumnDimension("$letter[$i]")->setWidth(20); $obj->getDefaultRowDimension()->setRowHeight(100); $img->setWorksheet($obj); //把图片写到当前的表格中 //$objActSheet->getCell('E26')->getHyperlink()->setUrl( 'http://www.phpexcel.net'); //超链接url地址 //$objActSheet->getCell('E26')->getHyperlink()->setTooltip( 'Navigate to website'); //鼠标移上去连接提示信息 //$obj->setCellValue($j.$num, $img); } else { if (substr($temstr, 0, 6) == "#link#") { $temstr = str_replace("#link#", '', $temstr); $obj->setCellValue($j . $num, $v[$temstr]); $obj->getCell($j . $num)->getHyperlink()->setUrl($v[$temstr]); //超链接url地址 $obj->getCell($j . $num)->getHyperlink()->setTooltip($v[$temstr]); //鼠标移上去连接提 } else { $obj->setCellValue($j . $num, $v[$temstr]); } } } $j++; } } $objPHPExcel->getActiveSheet()->setTitle('User'); $objPHPExcel->setActiveSheetIndex(0); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="' . $name . '.xls"'); header('Cache-Control: max-age=0'); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; }
$sheet->duplicateStyleArray(array('numberformat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00)), 'F4:G200'); $sheet->getColumnDimension('A')->setWidth(8); $sheet->getColumnDimension('C')->setWidth(36); $sheet->getColumnDimension('D')->setWidth(12); $sheet->getColumnDimension('E')->setWidth(12); $sheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $sheet->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 3); $sheet->getHeaderFooter()->setOddFooter('&CPage &P de &N'); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('Logo_AFUP'); $objDrawing->setDescription('Logo_AFUP'); $objDrawing->setPath(dirname(__FILE__) . '/../../templates/administration/images/logo_afup.png'); $objDrawing->setCoordinates('H1'); $objDrawing->setHeight(35); $objDrawing->setWidth(70); $objDrawing->setWorksheet($sheet); } //$workbook->removeSheetByIndex(0); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="compta_afup_' . date('Y', strtotime($periode_debut)) . '.xlsx"'); header('Cache-Control: max-age=0'); $writer = new PHPExcel_Writer_Excel2007($workbook); $writer->save('php://output'); exit; } elseif ($action === 'download_attachments') { try { // Get the year $year = date('Y', strtotime($listPeriode[$id_periode - 1]['date_debut'])); // Create the zip $zipFilename = sys_get_temp_dir() . DIRECTORY_SEPARATOR . 'afup_justificatifs-' . $year . '.zip';
function generate($tbl) { session_start(); $_SESSION['zprofile'] = 'yes'; $_SESSION['zprofile']['username'] = '******'; $_SESSION['zprofile']['useremail'] = '*****@*****.**'; $_SESSION['zprofile']['usercompany'] = 'hehehahhaha'; $_GET['var'] = 'tbl_tes'; $_SESSION['tbl_tes'] = $tbl; /* $_SESSION['tbl_tes'] = ' <table border="1"> <thead> <tr bgcolor="yellow"> <th>no</th> <th>col1</th> <th>col2</th> <th>col3</th> <th>col4</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>B2</td> <td>C2</td> <td rowspan="2">D2</td> <td>E3</td> </tr> <tr> <td>2</td> <td colspan="2">B3</td> <td>C3</td> </tr> <tr> <td>3</td> <td>B4</td> <td colspan="2" rowspan="2">C4</td> <td>D4</td> </tr> <tr> <td>4</td> <td> B5<img src="././assets/img/ecsi.png" name="gambar tes" title="hehehe coba titlenya" width="100" height="100"> <td><b>C5</b></td> </tr> </tbody> </table> '; */ //echo $_SESSION['tbl_tes'];exit(); ini_set("memory_limit", "-1"); ini_set("set_time_limit", "0"); set_time_limit(0); if (isset($_SESSION['zprofile'])) { $username = $_SESSION['zprofile']['username']; // user's name $usermail = $_SESSION['zprofile']['useremail']; // user's emailid $usercompany = $_SESSION['zprofile']['usercompany']; // user's company } else { header('Location: index.php?e=0'); } if (!isset($_GET['var'])) { echo "<br />No Table Variable Present, nothing to Export."; exit; } else { $tablevar = $_GET['var']; } if (!isset($_GET['limit'])) { $limit = 12; } else { $limit = $_GET['limit']; } if (!isset($_GET['debug'])) { $debug = false; } else { $debug = true; $handle = fopen("Auditlog/exportlog.txt", "w"); fwrite($handle, "\nDebugging On..."); } if (!isset($_SESSION[$tablevar]) or $_SESSION[$tablevar] == '') { echo "<br />Empty HTML Table, nothing to Export."; exit; } else { $htmltable = $_SESSION[$tablevar]; } if (strlen($htmltable) == strlen(strip_tags($htmltable))) { echo "<br />Invalid HTML Table after Stripping Tags, nothing to Export."; exit; } $htmltable = strip_tags($htmltable, "<table><tr><th><thead><tbody><tfoot><td><br><br /><b><span><img><img />"); $htmltable = str_replace("<br />", "\n", $htmltable); $htmltable = str_replace("<br/>", "\n", $htmltable); $htmltable = str_replace("<br>", "\n", $htmltable); $htmltable = str_replace(" ", " ", $htmltable); $htmltable = str_replace("\n\n", "\n", $htmltable); // // Extract HTML table contents to array // $dom = new domDocument(); $dom->loadHTML($htmltable); if (!$dom) { echo "<br />Invalid HTML DOM, nothing to Export."; exit; } $dom->preserveWhiteSpace = false; // remove redundant whitespace $tables = $dom->getElementsByTagName('table'); if (!is_object($tables)) { echo "<br />Invalid HTML Table DOM, nothing to Export."; exit; } if ($debug) { fwrite($handle, "\nTable Count: " . $tables->length); } $tbcnt = $tables->length - 1; // count minus 1 for 0 indexed loop over tables if ($tbcnt > $limit) { $tbcnt = $limit; } // // // Create new PHPExcel object with default attributes // require_once 'PHPExcel/PHPExcel.php'; $objPHPExcel = new PHPExcel(); $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial'); $objPHPExcel->getDefaultStyle()->getFont()->setSize(9); $tm = date(YmdHis); $pos = strpos($usermail, "@"); $user = substr($usermail, 0, $pos); $user = str_replace(".", "", $user); $tfn = $user . "_" . $tm . "_" . $tablevar . ".xlsx"; //$fname = "AuditLog/".$tfn; $fname = $tfn; $objPHPExcel->getProperties()->setCreator($username)->setLastModifiedBy($username)->setTitle("Automated Export")->setSubject("Automated Report Generation")->setDescription("Automated report generation.")->setKeywords("Exported File")->setCompany($usercompany)->setCategory("Export"); // // Loop over tables in DOM to create an array, each table becomes a worksheet // for ($z = 0; $z <= $tbcnt; $z++) { $maxcols = 0; $totrows = 0; $headrows = array(); $bodyrows = array(); $r = 0; $h = 0; $rows = $tables->item($z)->getElementsByTagName('tr'); $totrows = $rows->length; if ($debug) { fwrite($handle, "\nTotal Rows: " . $totrows); } foreach ($rows as $row) { $ths = $row->getElementsByTagName('th'); if (is_object($ths)) { if ($ths->length > 0) { $headrows[$h]['colcnt'] = $ths->length; if ($ths->length > $maxcols) { $maxcols = $ths->length; } $nodes = $ths->length - 1; for ($x = 0; $x <= $nodes; $x++) { $thishdg = $ths->item($x)->nodeValue; $headrows[$h]['th'][] = $thishdg; $headrows[$h]['bold'][] = $this->findWrapText('b', $this->innerHTML($ths->item($x))); $headrows[$h]['italic'][] = $this->findWrapText('i', $this->innerHTML($ths->item($x))); $headrows[$h]['underline'][] = $this->findWrapText('u', $this->innerHTML($ths->item($x))); if ($ths->item($x)->hasAttribute('style')) { $style = $ths->item($x)->getAttribute('style'); $stylecolor = $this->findStyleCSS('color', $style); if ($stylecolor == '') { $headrows[$h]['color'][] = $this->findSpanColor($this->innerHTML($ths->item($x))); } else { $headrows[$h]['color'][] = $stylecolor; } $headrows[$h]['font_name'][] = $this->findStyleCSS('font-family', $style); $headrows[$h]['font_size'][] = $this->findStyleCSS('font-size', $style); $headrows[$h]['border_top'][] = $this->findStyleCSS('border-top', $style); $headrows[$h]['border_bottom'][] = $this->findStyleCSS('border-bottom', $style); $headrows[$h]['border_left'][] = $this->findStyleCSS('border-left', $style); $headrows[$h]['border_right'][] = $this->findStyleCSS('border-right', $style); } else { $headrows[$h]['color'][] = $this->findSpanColor($this->innerHTML($ths->item($x))); } if ($ths->item($x)->hasAttribute('colspan')) { $headrows[$h]['colspan'][] = $ths->item($x)->getAttribute('colspan'); } else { $headrows[$h]['colspan'][] = 1; } if ($ths->item($x)->hasAttribute('rowspan')) { $headrows[$h]['rowspan'][] = $ths->item($x)->getAttribute('rowspan'); } else { $headrows[$h]['rowspan'][] = 1; } if ($ths->item($x)->hasAttribute('align')) { $headrows[$h]['align'][] = $ths->item($x)->getAttribute('align'); } else { $headrows[$h]['align'][] = 'left'; } if ($ths->item($x)->hasAttribute('valign')) { $headrows[$h]['valign'][] = $ths->item($x)->getAttribute('valign'); } else { $headrows[$h]['valign'][] = 'top'; } if ($ths->item($x)->hasAttribute('bgcolor')) { $headrows[$h]['bgcolor'][] = str_replace("#", "", $ths->item($x)->getAttribute('bgcolor')); } else { $headrows[$h]['bgcolor'][] = 'FFFFFF'; } } $h++; } } } $iRow = 0; $fillCell = array(); foreach ($rows as $row) { $iRow++; $tds = $row->getElementsByTagName('td'); if (is_object($tds)) { if ($tds->length > 0) { $bodyrows[$r]['colcnt'] = $tds->length; if ($tds->length > $maxcols) { $maxcols = $tds->length; } $nodes = $tds->length - 1; $iCol = 'A'; for ($x = 0; $x <= $nodes; $x++) { $thistxt = $tds->item($x)->nodeValue; $bodyrows[$r]['td'][] = $thistxt; $bodyrows[$r]['img'][] = $this->collecImg($tds->item($x)->getElementsByTagName('img')); $bodyrows[$r]['bold'][] = $this->findWrapText('b', $this->innerHTML($tds->item($x))); $bodyrows[$r]['italic'][] = $this->findWrapText('i', $this->innerHTML($tds->item($x))); $bodyrows[$r]['underline'][] = $this->findWrapText('u', $this->innerHTML($tds->item($x))); if ($tds->item($x)->hasAttribute('style')) { $style = $tds->item($x)->getAttribute('style'); $stylecolor = $this->findStyleCSS('color', $style); if ($stylecolor == '') { $bodyrows[$r]['color'][] = $this->findSpanColor($this->innerHTML($tds->item($x))); } else { $bodyrows[$r]['color'][] = $stylecolor; } $bodyrows[$h]['font_name'][] = $this->findStyleCSS('font-family', $style); $bodyrows[$h]['font_size'][] = $this->findStyleCSS('font-size', $style); $bodyrows[$h]['border_top'][] = $this->findStyleCSS('border-top', $style); $bodyrows[$h]['border_bottom'][] = $this->findStyleCSS('border-bottom', $style); $bodyrows[$h]['border_left'][] = $this->findStyleCSS('border-left', $style); $bodyrows[$h]['border_right'][] = $this->findStyleCSS('border-right', $style); } else { $bodyrows[$r]['color'][] = $this->findSpanColor($this->innerHTML($tds->item($x))); } if ($tds->item($x)->hasAttribute('colspan')) { $icolspan = $tds->item($x)->getAttribute('colspan'); $bodyrows[$r]['colspan'][] = $tds->item($x)->getAttribute('colspan'); } else { $icolspan = 1; $bodyrows[$r]['colspan'][] = 1; } if ($tds->item($x)->hasAttribute('rowspan')) { $irowspan = $tds->item($x)->getAttribute('rowspan'); $bodyrows[$r]['rowspan'][] = $irowspan; } else { $irowspan = 1; $bodyrows[$r]['rowspan'][] = 1; } if ($tds->item($x)->hasAttribute('align')) { $bodyrows[$r]['align'][] = $tds->item($x)->getAttribute('align'); } else { $bodyrows[$r]['align'][] = 'left'; } if ($tds->item($x)->hasAttribute('valign')) { $bodyrows[$r]['valign'][] = $tds->item($x)->getAttribute('valign'); } else { $bodyrows[$r]['valign'][] = 'top'; } if ($tds->item($x)->hasAttribute('bgcolor')) { $bodyrows[$r]['bgcolor'][] = str_replace("#", "", $tds->item($x)->getAttribute('bgcolor')); } else { $bodyrows[$r]['bgcolor'][] = 'FFFFFF'; } $lastIcol = $iCol; $lastIrow = $iRow; for ($ic = 1; $ic < $icolspan; $ic++) { $lastIcol++; $fillCell[$lastIcol . ':' . $lastIrow] = true; } $lastIcol = $iCol; $lastIrow = $iRow; for ($ir = 1; $ir < $irowspan; $ir++) { $lastIrow++; $fillCell[$lastIcol . ':' . $lastIrow] = true; } $lastIcol = $iCol; $lastIrow = $iRow; for ($ic = 1; $ic < $icolspan; $ic++) { for ($ir = 1; $ir < $irowspan; $ir++) { $lastIrow++; $fillCell[$lastIcol . ':' . $lastIrow] = true; } $lastIcol++; $fillCell[$lastIcol . ':' . $lastIrow] = true; } $iCol++; } $r++; } } } //echo '<pre>';print_r($fillCell); //exit(); if ($z > 0) { $objPHPExcel->createSheet($z); } $suf = $z + 1; $tableid = $tablevar . $suf; $wksheetname = ucfirst($tableid); $objPHPExcel->setActiveSheetIndex($z); // each sheet corresponds to a table in html $objPHPExcel->getActiveSheet()->setTitle($wksheetname); // tab name $worksheet = $objPHPExcel->getActiveSheet(); // set worksheet we're working on $style_overlay = array('font' => array('color' => array('rgb' => '000000'), 'bold' => false), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'CCCCFF')), 'alignment' => array('wrap' => true, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); $xcol = ''; $xrow = 1; $usedhdrows = 0; $heightvars = array(1 => '42', 2 => '42', 3 => '48', 4 => '52', 5 => '58', 6 => '64', 7 => '68', 8 => '76', 9 => '82'); for ($h = 0; $h < count($headrows); $h++) { $th = $headrows[$h]['th']; $colspans = $headrows[$h]['colspan']; $rowspans = $headrows[$h]['rowspan']; $aligns = $headrows[$h]['align']; $valigns = $headrows[$h]['valign']; $bgcolors = $headrows[$h]['bgcolor']; $colcnt = $headrows[$h]['colcnt']; $colors = $headrows[$h]['color']; $bolds = $headrows[$h]['bold']; $italics = $headrows[$h]['italic']; $underlines = $headrows[$h]['underline']; $font_sizes = $headrows[$h]['font_size']; $font_names = $headrows[$h]['font_name']; $border_tops = $headrows[$h]['border_top']; $border_bottoms = $headrows[$h]['border_bottom']; $border_lefts = $headrows[$h]['border_left']; $border_rights = $headrows[$h]['border_right']; $usedhdrows++; $mergedcells = false; for ($t = 0; $t < count($th); $t++) { if ($xcol == '') { $xcol = 'A'; } else { $xcol++; } $thishdg = $th[$t]; $thisalign = $aligns[$t]; $thisvalign = $valigns[$t]; $thiscolspan = (int) $colspans[$t]; $thisrowspan = (int) $rowspans[$t]; $thiscolor = $colors[$t]; $thisbg = $bgcolors[$t]; $thisbold = $bolds[$t]; $thisitalic = $italics[$t]; $thisunderline = $underlines[$t]; $thissize = (double) str_replace(array('pt', 'PT', 'px', 'PX'), '', $font_sizes[$t]); $thissize = $thissize > 0 ? $thissize : 9; $thisname = $font_names[$t]; $thisname = $thisname ? $thisname : 'Arial'; $thisbordertop = str_replace(array('px', 'PX'), '', $border_tops) > 0 && !empty($border_tops) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $thisborderbottom = str_replace(array('px', 'PX'), '', $border_bottoms) > 0 && !empty($border_bottoms) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $thisborderleft = str_replace(array('px', 'PX'), '', $border_lefts) > 0 && !empty($border_lefts) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $thisborderright = str_replace(array('px', 'PX'), '', $border_rights) > 0 && !empty($border_rights) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $strbordertop = str_replace(array('px', 'PX'), '', $border_tops) > 0 ? 'true' : 'false'; $strborderbottom = str_replace(array('px', 'PX'), '', $border_bottoms) > 0 ? 'true' : 'false'; $strborderleft = str_replace(array('px', 'PX'), '', $border_lefts) > 0 ? 'true' : 'false'; $strborderright = str_replace(array('px', 'PX'), '', $border_rights) > 0 ? 'true' : 'false'; $strbold = $thisbold == true ? 'true' : 'false'; $stritalic = $thisitalic == true ? 'true' : 'false'; $strunderline = $thisunderline == true ? 'true' : 'false'; if ($thisbg == 'FFFFFF') { $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_NONE; } else { $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID; } $style_overlay['alignment']['vertical'] = $thisvalign; // set styles for cell $style_overlay['alignment']['horizontal'] = $thisalign; $style_overlay['font']['color']['rgb'] = $thiscolor; $style_overlay['font']['bold'] = $thisbold; $style_overlay['font']['italic'] = $thisitalic; $style_overlay['font']['underline'] = $thisunderline == true ? PHPExcel_Style_Font::UNDERLINE_SINGLE : PHPExcel_Style_Font::UNDERLINE_NONE; $style_overlay['font']['size'] = $thissize; $style_overlay['font']['name'] = $thisname; $style_overlay['borders']['top']['style'] = $thisbordertop; $style_overlay['borders']['bottom']['style'] = $thisborderbottom; $style_overlay['borders']['left']['style'] = $thisborderleft; $style_overlay['borders']['right']['style'] = $thisborderright; $style_overlay['fill']['color']['rgb'] = $thisbg; $worksheet->setCellValue($xcol . $xrow, $thishdg); $worksheet->getStyle($xcol . $xrow)->applyFromArray($style_overlay); if ($debug) { fwrite($handle, "\n" . $xcol . ":" . $xrow . " Rowspan:" . $thisrowspan . " ColSpan:" . $thiscolspan . " Color:" . $thiscolor . " Align:" . $thisalign . " VAlign:" . $thisvalign . " BGColor:" . $thisbg . " Bold:" . $strbold . " Italic:" . $stritalic . " Underline:" . $strunderline . " Font-name:" . $thisname . " Font-size:" . $thissize . " Border-top: " . $strbordertop . " Border-bottom" . $strborderbottom . " Border-left:" . $strborderleft . " Border-right:" . $strborderright . " cellValue: " . $thishdg); } if ($thiscolspan > 1 && $thisrowspan < 1) { // spans more than 1 column $mergedcells = true; $lastxcol = $xcol; for ($j = 1; $j < $thiscolspan; $j++) { $lastxcol++; $worksheet->setCellValue($lastxcol . $xrow, ''); $worksheet->getStyle($lastxcol . $xrow)->applyFromArray($style_overlay); } $cellRange = $xcol . $xrow . ':' . $lastxcol . $xrow; if ($debug) { fwrite($handle, "\nmergeCells: " . $xcol . ":" . $xrow . " " . $lastxcol . ":" . $xrow); } $worksheet->mergeCells($cellRange); $worksheet->getStyle($cellRange)->applyFromArray($style_overlay); $num_newlines = substr_count($thishdg, "\n"); // count number of newline chars if ($num_newlines > 1) { $rowheight = $heightvars[1]; // default to 35 if (array_key_exists($num_newlines, $heightvars)) { $rowheight = $heightvars[$num_newlines]; } else { $rowheight = 75; } $worksheet->getRowDimension($xrow)->setRowHeight($rowheight); // adjust heading row height } $xcol = $lastxcol; } } $xrow++; $xcol = ''; } //Put an auto filter on last row of heading only if last row was not merged if (!$mergedcells) { $worksheet->setAutoFilter("A{$usedhdrows}:" . $worksheet->getHighestColumn() . $worksheet->getHighestRow()); } if ($debug) { fwrite($handle, "\nautoFilter: A" . $usedhdrows . ":" . $worksheet->getHighestColumn() . $worksheet->getHighestRow()); } // Freeze heading lines starting after heading lines $usedhdrows++; $worksheet->freezePane("A{$usedhdrows}"); if ($debug) { fwrite($handle, "\nfreezePane: A" . $usedhdrows); } // // Loop thru data rows and write them out // $xcol = ''; $xrow = $usedhdrows; for ($b = 0; $b < count($bodyrows); $b++) { $td = $bodyrows[$b]['td']; $img = $bodyrows[$b]['img']; $colcnt = $bodyrows[$b]['colcnt']; $colspans = $bodyrows[$b]['colspan']; $rowspans = $bodyrows[$b]['rowspan']; $aligns = $bodyrows[$b]['align']; $valigns = $bodyrows[$b]['valign']; $bgcolors = $bodyrows[$b]['bgcolor']; $colors = $bodyrows[$b]['color']; $bolds = $bodyrows[$b]['bold']; $italics = $bodyrows[$h]['italic']; $underlines = $bodyrows[$h]['underline']; $font_sizes = $bodyrows[$h]['font_size']; $font_names = $bodyrows[$h]['font_name']; $border_tops = $bodyrows[$h]['border_top']; $border_bottoms = $bodyrows[$h]['border_bottom']; $border_lefts = $bodyrows[$h]['border_left']; $border_rights = $bodyrows[$h]['border_right']; for ($t = 0; $t < count($td); $t++) { if ($xcol == '') { $xcol = 'A'; } else { $xcol++; } if (isset($fillCell[$xcol . ':' . $xrow])) { $xcol = $this->nextCol($xcol, $xrow); } $thistext = $td[$t]; $thisimg = $img[$t]; $thisalign = $aligns[$t]; $thisvalign = $valigns[$t]; $thiscolspan = (int) $colspans[$t]; $thisrowspan = (int) $rowspans[$t]; $thiscolor = $colors[$t]; $thisbg = $bgcolors[$t]; $thisbold = $bolds[$t]; $strbold = $thisbold == true ? 'true' : 'false'; $thisbold = $bolds[$t]; $thisitalic = $italics[$t]; $thisunderline = $underlines[$t]; $thissize = (double) str_replace(array('pt', 'PT', 'px', 'PX'), '', $font_sizes[$t]); $thissize = $thissize > 0 ? $thissize : 9; $thisname = $font_names[$t]; $thisname = $thisname ? $thisname : 'Arial'; $thisbordertop = str_replace(array('px', 'PX'), '', $border_tops) > 0 && !empty($border_tops) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $thisborderbottom = str_replace(array('px', 'PX'), '', $border_bottoms) > 0 && !empty($border_bottoms) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $thisborderleft = str_replace(array('px', 'PX'), '', $border_lefts) > 0 && !empty($border_lefts) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $thisborderright = str_replace(array('px', 'PX'), '', $border_rights) > 0 && !empty($border_rights) ? PHPExcel_Style_Border::BORDER_THIN : PHPExcel_Style_Border::BORDER_NONE; $strbold = $thisbold == true ? 'true' : 'false'; $stritalic = $thisitalic == true ? 'true' : 'false'; $strunderline = $thisunderline == true ? 'true' : 'false'; $strbordertop = str_replace(array('px', 'PX'), '', $border_tops) > 0 ? 'true' : 'false'; $strborderbottom = str_replace(array('px', 'PX'), '', $border_bottoms) > 0 ? 'true' : 'false'; $strborderleft = str_replace(array('px', 'PX'), '', $border_lefts) > 0 ? 'true' : 'false'; $strborderright = str_replace(array('px', 'PX'), '', $border_rights) > 0 ? 'true' : 'false'; if ($thisbg == 'FFFFFF') { $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_NONE; } else { $style_overlay['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID; } $style_overlay['alignment']['vertical'] = $thisvalign; // set styles for cell $style_overlay['alignment']['horizontal'] = $thisalign; $style_overlay['font']['color']['rgb'] = $thiscolor; $style_overlay['font']['bold'] = $thisbold; $style_overlay['font']['italic'] = $thisitalic; $style_overlay['font']['underline'] = $thisunderline == true ? PHPExcel_Style_Font::UNDERLINE_SINGLE : PHPExcel_Style_Font::UNDERLINE_NONE; $style_overlay['font']['size'] = $thissize; $style_overlay['font']['name'] = $thisname; $style_overlay['borders']['top']['style'] = $thisbordertop; $style_overlay['borders']['bottom']['style'] = $thisborderbottom; $style_overlay['borders']['left']['style'] = $thisborderleft; $style_overlay['borders']['right']['style'] = $thisborderright; $style_overlay['fill']['color']['rgb'] = $thisbg; if ($thiscolspan == 1) { $worksheet->getColumnDimension($xcol)->setWidth(25); } $worksheet->setCellValue($xcol . $xrow, $thistext); if (is_array($thisimg) && count($thisimg) > 0) { $thisCellWidth = $worksheet->getColumnDimension($xcol)->getWidth(); $thisCellHeight = 0; $offsetY = 5; foreach ($thisimg as $Vimg) { $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setWorksheet($worksheet); $objDrawing->setName($Vimg['name']); $objDrawing->setDescription($Vimg['title']); $objDrawing->setPath($Vimg['src']); $objDrawing->setCoordinates($xcol . $xrow); $objDrawing->setOffsetX(1); $objDrawing->setOffsetY($offsetY); $objDrawing->setWidth($Vimg['width']); $objDrawing->setHeight($Vimg['height']); $thisCellHeight += $Vimg['height']; if ($Vimg['width'] > $thisCellWidth) { $worksheet->getColumnDimension($xcol)->setWidth($Vimg['width'] / 5); } if ($Vimg['height'] > 0) { $worksheet->getRowDimension($xrow)->setRowHeight($thisCellHeight); } if ($debug) { fwrite($handle, "\n Insert Image on " . $xcol . ":" . $xrow . ' src:' . $Vimg['src'] . ' Width:' . $Vimg['width'] . ' Height:' . $Vimg['height'] . ' Offset:' . $offsetY); } $offsetY += $Vimg['height'] + 10; } } if ($debug) { fwrite($handle, "\n" . $xcol . ":" . $xrow . " Rowspan:" . $thisrowspan . " ColSpan:" . $thiscolspan . " Color:" . $thiscolor . " Align:" . $thisalign . " VAlign:" . $thisvalign . " BGColor:" . $thisbg . " Bold:" . $strbold . " Italic:" . $stritalic . " Underline:" . $strunderline . " Font-name:" . $thisname . " Font-size:" . $thissize . " Border-top: " . $strbordertop . " Border-bottom" . $strborderbottom . " Border-left:" . $strborderleft . " Border-right:" . $strborderright . " cellValue: " . $thistext); } $worksheet->getStyle($xcol . $xrow)->applyFromArray($style_overlay); if ($thiscolspan > 1 && $thisrowspan == 1) { // spans more than 1 column $lastxcol = $xcol; for ($j = 1; $j < $thiscolspan; $j++) { $lastxcol++; } $cellRange = $xcol . $xrow . ':' . $lastxcol . $xrow; if ($debug) { fwrite($handle, "\nmergeCells: " . $xcol . ":" . $xrow . " " . $lastxcol . ":" . $xrow); } $worksheet->mergeCells($cellRange); $worksheet->getStyle($cellRange)->applyFromArray($style_overlay); $xcol = $lastxcol; } elseif ($thiscolspan == 1 && $thisrowspan > 1) { // spans more than 1 column $lastxrow = $xrow; for ($j = 1; $j < $thisrowspan; $j++) { $lastxrow++; //$fillCell[$xcol.':'.$lastxrow] = true; } $cellRange = $xcol . $xrow . ':' . $xcol . $lastxrow; if ($debug) { fwrite($handle, "\nmergeCells: " . $xcol . ":" . $xrow . " " . $xcol . ":" . $lastxrow); } $worksheet->mergeCells($cellRange); $worksheet->getStyle($cellRange)->applyFromArray($style_overlay); //$xrow = $lastxrow; } elseif ($thiscolspan > 1 && $thisrowspan > 1) { // spans more than 1 column $lastxcol = $xcol; $lastxrow = $xrow; for ($j = 1; $j < $thiscolspan; $j++) { $lastxcol++; for ($k = 1; $k < $thisrowspan; $k++) { $lastxrow++; //$fillCell[$lastxcol.':'.$lastxrow] = true; } } $cellRange = $xcol . $xrow . ':' . $lastxcol . $lastxrow; if ($debug) { fwrite($handle, "\nmergeCells: " . $xcol . ":" . $xrow . " " . $lastxcol . ":" . $lastxrow); } $worksheet->mergeCells($cellRange); $worksheet->getStyle($cellRange)->applyFromArray($style_overlay); $xcol = $lastxcol; //$xrow = $lastxrow; } } $xrow++; $xcol = ''; } // autosize columns to fit data $azcol = 'A'; for ($x = 1; $x == $maxcols; $x++) { $worksheet->getColumnDimension($azcol)->setAutoSize(true); $azcol++; } if ($debug) { fwrite($handle, "\nHEADROWS: " . print_r($headrows, true)); fwrite($handle, "\nBODYROWS: " . print_r($bodyrows, true)); fwrite($handle, "\nFILLCELL: " . print_r($fillCell, true)); } } // end for over tables $objPHPExcel->setActiveSheetIndex(0); // set to first worksheet before close // // Write to Browser // if ($debug) { fclose($handle); } header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header("Content-Disposition: attachment;filename={$fname}"); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); //$objWriter->save($fname); $objWriter->save('php://output'); exit; }
protected function exportExcel($props) { $props = array_merge(array('title' => '', 'subtitle' => '', 'headers' => null, 'data' => null), $props); if ($props['subtitle']) { $props['title'] .= ' - ' . $props['subtitle']; } PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT); $logoPath = 'images/logo_academy.png'; $props['doc'] = array_merge(array('title' => 'Carus', 'subject' => 'Reporte', 'description' => 'Reporte de sistema', 'keywords' => 'Reporte', 'category' => 'Reporte'), isset($props['doc']) && is_array($props['doc']) ? $props['doc'] : array()); $title = preg_replace('/([^a-z0-9áéíóúàèìòùâêîôûñ\\-_ ]+)/i', '', isset($props['title']) ? $props['title'] : $props['doc']['title']); if (strlen($title) > 30) { $title = substr($title, 0, strripos($title, " ", 30 - strlen($title))); } $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("Imagen Digital")->setLastModifiedBy("Imagen Digital")->setTitle($props['doc']['title'])->setSubject($props['doc']['subject'])->setDescription($props['doc']['description'])->setKeywords($props['doc']['keywords'])->setCategory($props['doc']['category']); $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getDefaultStyle()->getFont()->setName('Verdana')->setSize(8)->setColor(new PHPExcel_Style_Color('FF333333')); $sheet = $objPHPExcel->getActiveSheet(); // Set page orientation, size, Print Area and Fit To Pages $objPageSetup = new PHPExcel_Worksheet_PageSetup(); $objPageSetup->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER); $objPageSetup->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objPageSetup->setFitToWidth(1); $sheet->setPageSetup($objPageSetup); $sheet->setTitle($title); $sheet->getDefaultStyle()->applyFromArray(array('alignment' => array('wrap' => false))); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setWorksheet($sheet); $objDrawing->setName("logo"); $objDrawing->setDescription("Carus"); $objDrawing->setPath($logoPath); $objDrawing->setCoordinates('A1'); $objDrawing->setWidth(148); $objDrawing->setHeight(30); $sheet->getRowDimension(1)->setRowHeight(20); $styleTitle = array('font' => array('bold' => true, 'color' => array('rgb' => 'FFFFFFFF'), 'size' => 8, 'name' => 'Verdana'), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FF333333')), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF333333'))), 'alignment' => array('wrap' => false, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)); $styleText = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF0B7C98'))), 'alignment' => array('vertical' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); $col = 0; $fil = 3; $colMax = 0; $sheet->setCellValueByColumnAndRow($col, $fil, $title); $sheet->getStyleByColumnAndRow($col, $fil)->applyFromArray($styleTitle); $fil++; foreach ($props['headers'] as $value) { $sheet->setCellValueByColumnAndRow($col, $fil, $value); $sheet->getStyleByColumnAndRow($col, $fil)->applyFromArray($styleTitle); $col++; } $colMax = max($colMax, $col); $fil++; foreach ($props['data'] as $row) { $col = 0; foreach ($props['headers'] as $key => $value) { $sheet->setCellValueByColumnAndRow($col, $fil, $row[$value]); $sheet->getStyleByColumnAndRow($col, $fil)->applyFromArray($styleText); $col++; } $fil++; } $colMax = max($colMax, $col); $sheet->mergeCellsByColumnAndRow(0, 1, $colMax - 1, 1); $sheet->mergeCellsByColumnAndRow(0, 3, $colMax - 1, 3); for ($c = 0; $c < $colMax; $c++) { $sheet->getColumnDimensionByColumn($c)->setAutoSize(true); $calculatedWidth = $sheet->getColumnDimensionByColumn($c)->getWidth(); $sheet->getColumnDimensionByColumn($c)->setAutoSize(false); $sheet->getColumnDimensionByColumn($c)->setWidth(max((int) $calculatedWidth * 1.05, 12)); } $sheet->calculateColumnWidths(); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); //header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="' . $title . '.xlsx"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified header('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header('Pragma: public'); // HTTP/1.0 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); //$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML'); $objWriter->save('php://output'); exit; }
//Start Image //C $path = DIR_FS_CATALOG . $p['products_image']; if (file_exists($path)) { $cache_path = DIR_FS_CATALOG . 'cache/' . $o['customers_orders_id'] . '_' . $p['orders_products_id'] . '.jpg'; if (!file_exists($cache_path)) { ImageManager::resize($path, $cache_path, 200); } if (!file_exists($cache_path)) { $cache_path = $path; } $drawing = new PHPExcel_Worksheet_Drawing(); $drawing->setName($p['products_name']); $drawing->setDescription($p['products_name']); $drawing->setPath($cache_path); $drawing->setWidth($width); //$drawing->setHeight($height); $drawing->setCoordinates('A' . $product_row); $drawing->setOffsetX($offset_x); $drawing->setOffsetY($offset_y); $drawing->setResizeProportional(40); $drawing->setWorksheet($sheet); } //End Image $product_row += 2; } $sheet->setCellValue('D' . $order_row, $o['order_total']); $sheet->mergeCells('D' . $order_row . ':D' . $order_row_max); $sheet->getStyle('D' . $order_row . ':D' . $order_row_max)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $sheet->setCellValue('E' . $order_row, $o['delivery_name']); $sheet->mergeCells('E' . $order_row . ':E' . $order_row_max);
$objPHPExcel->getActiveSheet()->setCellValue('F' . ($i + 3), $nationService->GetNation($studentInfo['nation'])->getnationName()); $objPHPExcel->getActiveSheet()->setCellValue('G' . ($i + 3), $studentInfo['byxx']); $objPHPExcel->getActiveSheet()->setCellValue('H' . ($i + 3), $studentInfo['hkszd']); $objPHPExcel->getActiveSheet()->setCellValue('I' . ($i + 3), $studentInfo['address']); $objPHPExcel->getActiveSheet()->setCellValue('J' . ($i + 3), $studentInfo['telephone']); $objPHPExcel->getActiveSheet()->setCellValue('K' . ($i + 3), $studentInfo['zcxx']); $objPHPExcel->getActiveSheet()->setCellValueExplicit('L' . ($i + 3), $studentInfo['cardNumber'], PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->getActiveSheet()->setCellValue('M' . ($i + 3), $studentInfo['xjh']); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('photo'); $objDrawing->setDescription('photo'); $objDrawing->setPath('..' . $studentInfo['photo']); $objDrawing->setResizeProportional(false); //取消按原图像缩放 $objDrawing->setHeight(68); $objDrawing->setWidth(85); $objDrawing->setCoordinates('N' . ($i + 3)); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); } $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setTitle('学生信息信息记录'); //保存到服务器 //$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //$FileName=iconv('utf-8','gbk','PHP导出Excel.xls'); //$objWriter->save($FileName); //选中保存路径 $outputFileName = iconv('UTF-8', 'gb2312', '学生信息记录.xls'); ob_end_clean(); //清除缓冲区,避免乱码 header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename=' . $outputFileName);