function ExportExcelRecord($arrdata, $datatype, $numberRow, $objPHPExcel, $pageObj) { global $cCharset, $locale_info; $col = -1; $objASIndex = $objPHPExcel->setActiveSheetIndex(0); $objASheet = $objPHPExcel->getActiveSheet(); $rowDim = $objASIndex->getRowDimension($numberRow + 1); foreach ($arrdata as $field => $data) { $col++; $colLetter = PHPExcel_Cell::stringFromColumnIndex($col); $colDim = $objASIndex->getColumnDimension($colLetter); if ($datatype[$field] == "binary") { if (!$data) { continue; } if (!function_exists("imagecreatefromstring")) { $objASIndex->setCellValueByColumnAndRow($col, $numberRow + 1, "LONG BINARY DATA - CANNOT BE DISPLAYED"); continue; } $error_handler = set_error_handler("empty_error_handler"); $gdImage = imagecreatefromstring($data); if ($error_handler) { set_error_handler($error_handler); } if ($gdImage) { $objDrawing = new PHPExcel_Worksheet_MemoryDrawing(); $objDrawing->setImageResource($gdImage); $objDrawing->setCoordinates($colLetter . ($row + 1)); $objDrawing->setWorksheet($objASheet); $width = $objDrawing->getWidth() * 0.143; $height = $objDrawing->getHeight() * 0.75; if ($rowDim->getRowHeight() < $height) { $rowDim->setRowHeight($height); } $colDimSh = $objASheet->getColumnDimension($colLetter); $colDimSh->setAutoSize(false); if ($colDim->getWidth() < $width) { $colDim->setWidth($width); } } } elseif ($datatype[$field] == "file") { $arr = my_json_decode($row[$field]); if (count($arr) == 0) { $data = PHPExcel_Shared_String::ConvertEncoding($data, 'UTF-8', $cCharset); if ($data == "<img src=\"images/no_image.gif\" />") { $arr[] = array("name" => "images/no_image.gif"); } else { if (substr($data, 0, 1) == '=') { $data = '="' . str_replace('"', '""', $data) . '"'; } $objASIndex->setCellValueByColumnAndRow($col, $numberRow + 1, $data); continue; } } $offsetY = 0; $height = 0; foreach ($arr as $img) { if (!file_exists($img["name"]) || !$img["name"]) { $data = PHPExcel_Shared_String::ConvertEncoding($data, 'UTF-8', $cCharset); if (substr($data, 0, 1) == '=') { $data = '="' . str_replace('"', '""', $data) . '"'; } $objASIndex->setCellValueByColumnAndRow($col, $numberRow + 1, $data); continue; } $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setPath($img["name"]); $objDrawing->setCoordinates($colLetter . ($numberRow + 1)); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); $objDrawing->setOffsetY($offsetY); $width = $objDrawing->getWidth() * 0.143; $height = $height + $objDrawing->getHeight() * 0.75; $offsetY = $offsetY + $objDrawing->getHeight(); if ($rowDim->getRowHeight() < $height) { $rowDim->setRowHeight($height); } $colDimSh = $objASheet->getColumnDimension($colLetter); $colDimSh->setAutoSize(false); if ($colDim->getWidth() < $width) { $colDim->setWidth($width); } } } else { $data = PHPExcel_Shared_String::ConvertEncoding($data, 'UTF-8', $cCharset); if (substr($data, 0, 1) == '=') { $data = '="' . str_replace('"', '""', $data) . '"'; } $objASIndex->setCellValueByColumnAndRow($col, $numberRow + 1, $data); if ($datatype[$field] == "date") { $objStyle = $objASIndex->getStyle($colLetter . ($numberRow + 1)); $objNumFrm = $objStyle->getNumberFormat(); $objNumFrm->setFormatCode($locale_info["LOCALE_SSHORTDATE"] . " hh:mm:ss"); } } } }
/** * 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 addImage($objPHPExcel) { $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); $objDrawing->setName("name"); $objDrawing->setDescription("Description"); // $objDrawing->setPath('../img/logo-set1.png'); $objDrawing->setPath('../img/LogoBSW.png'); $objDrawing->setCoordinates('A1'); $objDrawing->setOffsetX(5); $objDrawing->setWidth(115); $objDrawing->setOffsetY(5); }
private function getTeaserImage(Teasers $teaser) { $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName($teaser->title); $path = Yii::app()->params->imageBasePath . DIRECTORY_SEPARATOR . $teaser->picture; if (!file_exists($path)) { $path = $path = Yii::app()->params->imageBasePath . DIRECTORY_SEPARATOR . 'notfound.png'; } $objDrawing->setPath($path); $objDrawing->setWidthAndHeight(95, 95); $objDrawing->setOffsetX(10); $objDrawing->setOffsetY(2); return $objDrawing; }
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; }
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('*/*'); }
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; }
/** * 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; }
if ($aid == 0) { if (isset($article_rowstart)) { mergeArticleRowsToProduct($article_rowstart, $product_row - 1); } //default article $article_rowstart = $product_row; $p_image = DIR_WS_IMAGES . $p->image; if (is_file($p_image)) { //add product image $img_path = thumbimage($p_image, $pi_width, $pi_height, 1, 1, DIR_WS_IMAGES . 'imagecache'); $obj_excel_draw = new PHPExcel_Worksheet_Drawing(); $obj_excel_draw->setWorksheet($obj_ws_summary); $obj_excel_draw->setPath($img_path); $obj_excel_draw->setCoordinates('B' . $product_row); $obj_excel_draw->setOffsetX($img_offset); $obj_excel_draw->setOffsetY($img_offset); } $no = $product_counter; $pid = $p->id; $pcode = $p->code; } else { $no = ''; $pid = ''; $pcode = ''; } $obj_ws_summary->setCellValue('A' . $product_row, $no); //Image is already added above $obj_ws_summary->setCellValue('C' . $product_row, $pid); $obj_ws_summary->setCellValueExplicit('D' . $product_row, $pcode, PHPExcel_Cell_DataType::TYPE_STRING); $obj_ws_summary->setCellValueExplicit('E' . $product_row, $ean, PHPExcel_Cell_DataType::TYPE_STRING); $obj_ws_summary->setCellValue('F' . $product_row, $size);
/** * 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; }
/** * Вставить в указанную ячейку изображение */ 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); }
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; }
->setLastModifiedBy("Maarten Balliauw") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file"); // for exl logo $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('PHPExcel logo'); $objDrawing->setDescription('PHPExcel logo'); $objDrawing->setPath('../image/LOGO.png'); $objDrawing->setWidthAndHeight(200,100); $objDrawing->setResizeProportional(true); $objDrawing->setCoordinates('E1'); $objDrawing->setOffsetX(6); $objDrawing->setOffsetY(8); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); /** Create a new PHPExcel object 1.0 */ $activeempname=mysqli_query($con,"SELECT ULD_ID,ULD_WORKER_NAME FROM LMC_USER_LOGIN_DETAILS WHERE ULD_USERNAME='******'"); if($row=mysqli_fetch_array($activeempname)) { $activeemp=$row["ULD_ID"]; } // COMMOM DATA if($_REQUEST['option']=='COMMON_DATA') { //MEETING TOPIC $topic=mysqli_query($con,"SELECT MT_TOPIC FROM LMC_MEETING_TOPIC ORDER BY MT_TOPIC ASC"); while($row=mysqli_fetch_array($topic)){ $topicname[]=$row["MT_TOPIC"]; }
/** * Insert an image in a worksheet. * * @param integer $row The row we are going to insert the bitmap into * @param integer $col The column we are going to insert the bitmap into * @param string $bitmap The bitmap filename * @param integer $x The horizontal position (offset) of the image inside the cell. * @param integer $y The vertical position (offset) of the image inside the cell. * @param integer $scale_x The horizontal scale * @param integer $scale_y The vertical scale */ public function insert_bitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1) { $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setPath($bitmap); $objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($col) . ($row + 1)); $objDrawing->setOffsetX($x); $objDrawing->setOffsetY($y); $objDrawing->setWorksheet($this->worksheet); if ($scale_x != 1) { $objDrawing->setResizeProportional(false); $objDrawing->getWidth($objDrawing->getWidth() * $scale_x); } if ($scale_y != 1) { $objDrawing->setResizeProportional(false); $objDrawing->setHeight($objDrawing->getHeight() * $scale_y); } }
public function actionExcelDDC() { date_default_timezone_set('America/Tegucigalpa'); if (isset($_GET['Estudiante'], $_GET['Practica'])) { $IdEstudiante = $_GET['Estudiante']; $IdPractica = $_GET['Practica']; $Estudiante = UsuarioEstudiante::model()->findByPk($IdEstudiante); $PracticaProfesional = PracticaProfesional::model()->findByPk($IdPractica); Yii::import('ext.phpexcel.XPHPExcel'); $objPHPExcel = XPHPExcel::createPHPExcel(); $objPHPExcel->getProperties()->setCreator("Henry Arévalo")->setLastModifiedBy("Henry Arévalo")->setTitle("Sistema de Gestión de Prácticas Profesionales")->setSubject("UNITEC")->setDescription("SGPP")->setKeywords("HAF")->setCategory("SGPP"); $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.5)->setRight(0.25)->setLeft(0.25)->setBottom(0.5); // Bordes $BordesEncabezado = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); // Agregando imagen $objDrawingPType = new PHPExcel_Worksheet_Drawing(); $objDrawingPType->setWorksheet($objPHPExcel->setActiveSheetIndex(0)); $objDrawingPType->setName("Pareto By Type"); $objDrawingPType->setPath(Yii::app()->basePath . DIRECTORY_SEPARATOR . "../assets/img/logounitec.png"); $objDrawingPType->setCoordinates('H1'); $objDrawingPType->setOffsetX(3); $objDrawingPType->setOffsetY(0); $objDrawingPType->setWidthAndHeight(180, 90); $objDrawingPType->setResizeProportional(true); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Lista de Estudiantes'); // COMBINANDO CELDAS $objPHPExcel->setActiveSheetIndex(0)->mergeCells('C1:H1'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('C2:H2'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B5:D5'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B6:D6'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E6:G6'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H6:J6'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B7:D7'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E7:G7'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H7:J7'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B8:D8'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E8:J8'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B9:D9'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('F9:G9'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('I9:J9'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B10:J10'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B12:J12'); // EMPIEZA EMPRESA INSTITUCIÓN $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B13:J13'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B14:G14'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H14:J14'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B15:G15'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H15:J15'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B16:D16'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E16:G16'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H16:J16'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B17:D17'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E17:G17'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H17:J17'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B18:D18'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E18:G18'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H18:J18'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B19:D19'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E19:G19'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H19:J19'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B20:J20'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B22:J28'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B29:J29'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B29:J29'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B31:J42'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B43:J43'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B45:J46'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B49:F49'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B50:F50'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('G49:J49'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('G50:J50'); // APLICANDO BORDES $objPHPExcel->getActiveSheet()->getStyle('B6:D7')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('E6:G7')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('H6:J7')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B8:D9')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('E8:J9')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B12:J13')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B14:G15')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('H14:J15')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B16:D17')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B18:D19')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('E16:G17')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('E18:G19')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('H16:J17')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('H18:J19')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B22:J28')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B31:J42')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B45:J46')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B49:F50')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('G49:J50')->applyFromArray($BordesEncabezado); // FORMATOS DE TEXTO ( SIZE, FONT , BOLD) $NegritaTamanioCatorce = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); // Negrita tamaño 14 $NormalTamanioDoce = array('font' => array('bold' => true, 'size' => 12), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); // Negrita tamaño 12 $NormalTamanioOnce = array('font' => array('bold' => true, 'size' => 11), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)); // Negrita tamaño 12 $NormalTamanioDiez = array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); // Negrita tamaño 12 $NormalTamanioNueve = array('font' => array('size' => 9), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); // Negrita tamaño 12 $AlieadoArriba = array('font' => array('bold' => false), 'alignment' => array('vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY)); // Negrita Tamaño 12 $Normal = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); // Negrita tamaño 12 // ESCRIBIENDO DATOS $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', "DESCRIPCIÓN DEL CARGO"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2', "ESTUDIANTE EN PRÁCTICA PROFESIONAL"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B2', "FO-GR-002"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I4', "FECHA:"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J4', date('Y-m-d')); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B5', "1._DATOS DEL ESTUDIANTE"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B6', "APELLIDO(S)"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E6', "NOMBRE(S)"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H6', "NÚMERO DE CUENTA"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B8', "CARRERA"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E8', "PERÍODO DE PRÁCTICA"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E9', "DESDE"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H9', "HASTA"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B11', "2._ EMPRESA/INSTITUCIÓN"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B12', "NOMBRE"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B14', "COORDINADOR DE PRÁCTICA"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H14', "CARGO"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B16', "DIRECCIÓN"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E16', "CIUDAD"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H16', "TELEFONO"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B18', "FAX"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E18', "E-MAIL"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H18', "WEBSITE"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B21', "3._ OBJETIVO DE CARGO"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B30', "4._ RESPONSABILIDADES"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B44', "5._ OBSERVACIONES"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B48', "5._ FIRMAS"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B49', "COORDINADOR PRÁCTICA (EMPRESA) - " . ucwords(strtolower($PracticaProfesional->asesor->NombreCompleto))); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G49', "ESTUDIANTE - " . ucwords(strtolower($Estudiante->NombreCompleto))); // LLENANDO DATOS CON EL MODELO $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B7', ucwords(strtolower($Estudiante->PrimerApellido . " " . $Estudiante->SegundoApellido))); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E7', ucwords(strtolower($Estudiante->Nombre))); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H7', $Estudiante->NumeroDeCuenta); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B9', $Estudiante->carrera->NombreCarrera); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B9', $Estudiante->carrera->NombreCarrera); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B13', ucwords(strtolower($PracticaProfesional->asesor->usuarioEmpresa->NombreEmpresa))); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B15', ucwords(strtolower($PracticaProfesional->asesor->NombreCompleto))); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H15', ucwords(strtolower($PracticaProfesional->asesor->PuestoEmpresa))); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H17', "Tel: " . $PracticaProfesional->asesor->TelefonoFijo . " Cel: " . $PracticaProfesional->asesor->TelefonoCelular); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E19', $PracticaProfesional->asesor->CorreoElectronico); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E19', $PracticaProfesional->asesor->CorreoElectronico); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H19', $PracticaProfesional->asesor->usuarioEmpresa->SitioWebEmpresa); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B22', $PracticaProfesional->ObjetivoDelCargo); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B45', $PracticaProfesional->Observaciones); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B31', str_replace(",", " ", $PracticaProfesional->Reponsabilidades)); $objPHPExcel->getActiveSheet()->getStyle('B22:J28')->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->getStyle('B45:J46')->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->getStyle('B31:J42')->getAlignment()->setWrapText(true); // APLICANDO FORMATO $objPHPExcel->getActiveSheet()->getStyle('C1')->applyFromArray($NegritaTamanioCatorce); $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($NormalTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($NormalTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('I4')->applyFromArray($NormalTamanioOnce); $objPHPExcel->getActiveSheet()->getStyle('B5')->applyFromArray($NormalTamanioDiez); $objPHPExcel->getActiveSheet()->getStyle('B6')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('E6')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('H6')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('B8')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('E8')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('E9')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('H9')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('B11')->applyFromArray($NormalTamanioDiez); $objPHPExcel->getActiveSheet()->getStyle('B12')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('B14')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('H14')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('B16')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('E16')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('H16')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('B18')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('E18')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('H18')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('B21')->applyFromArray($NormalTamanioDiez); $objPHPExcel->getActiveSheet()->getStyle('B30')->applyFromArray($NormalTamanioDiez); $objPHPExcel->getActiveSheet()->getStyle('B44')->applyFromArray($NormalTamanioDiez); $objPHPExcel->getActiveSheet()->getStyle('B48')->applyFromArray($NormalTamanioDiez); $objPHPExcel->getActiveSheet()->getStyle('B49')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('G49')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('B22')->applyFromArray($AlieadoArriba); $objPHPExcel->getActiveSheet()->getStyle('B45')->applyFromArray($AlieadoArriba); $objPHPExcel->getActiveSheet()->getStyle('B31')->applyFromArray($AlieadoArriba); $objPHPExcel->getActiveSheet()->getStyle('B7')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('E7')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('H7')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('B9')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('B13')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('B15')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('H15')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('H17')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('E19')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('H19')->applyFromArray($Normal); //COLOREANDO EN BLANCO $objPHPExcel->getActiveSheet()->getStyle('B6:J7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('E6:G7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('B6:J7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('B8:D9')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('E8:J9')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('B12:J13')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('B14:G15')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('H14:J15')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('B16:J19')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('B23:J28')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('B49:J50')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a client’s web browser (Excel5) header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename= Descripción del cargo.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'); Yii::app()->end(); } else { $this->redirect(Yii::app()->request->urlReferrer); } }
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('У Вас нет прав на редактирование "Формирование отчета"'); } }
/** * 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; }
$sheet->getActiveSheet()->SetCellValue('A7', __("Position")); $sheet->getActiveSheet()->SetCellValue('B7', $dev->Position); // Excel limits sheet titles to 31 characters or less // Also invalid chars of /\*?[] $sheetTitle = substr($dev->Label, 1, 31); $sheetTitle = preg_replace('/[\\/\\*\\?\\[\\]]/', '_', $sheetTitle); $sheet->getActiveSheet()->setTitle($sheetTitle); // Insert a picture into the device specific worksheet if (file_exists("pictures/" . $devTmpl->FrontPictureFile)) { $img = new PHPExcel_Worksheet_Drawing(); $img->setWorksheet($sheet->setActiveSheetIndex($devNum)); $img->setName($dev->Label); $img->setPath("pictures/" . $devTmpl->FrontPictureFile); $img->setCoordinates('B9'); $img->setOffsetX(1); $img->setOffsetY(5); } foreach (range('A', 'B') as $columnID) { $sheet->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true); } $sheet->setActiveSheetIndex(0); $port = new DevicePorts(); $port->DeviceID = $dev->DeviceID; $portList = $port->getPorts(); /* if ( sizeof( $portList ) < 1 ) { // No ports for this device header('Location: '.redirect()); exit; } */
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); } } }
/** * Добавляет лого на страницу * * @param PHPExcel_Worksheet $activeSheet */ protected function addLogo(PHPExcel_Worksheet $activeSheet) { $logo_path = dirname(Yii::app()->basePath) . DIRECTORY_SEPARATOR . 'htdocs/images' . DIRECTORY_SEPARATOR . 'logo.jpg'; $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setWorksheet($activeSheet); $objDrawing->setName("Лого"); $objDrawing->setPath($logo_path); $objDrawing->setCoordinates('A1'); $objDrawing->setOffsetX(1); $objDrawing->setOffsetY(5); $activeSheet->getRowDimension(3)->setRowHeight(40); }
$objDrawing->setOffsetX(1); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('Logo'); $objDrawing->setDescription('Logo'); $objDrawing->setPath('./fotos/temporal/PREDIO OTROS.JPG'); $objDrawing->setCoordinates('C113'); $objDrawing->setOffsetY(1); $objDrawing->setOffsetX(1); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('Logo'); $objDrawing->setDescription('Logo'); $objDrawing->setPath('./fotos/temporal/PREDIO OTROS2.JPG'); $objDrawing->setCoordinates('L113'); $objDrawing->setOffsetY(1); $objDrawing->setOffsetX(1); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); //=================================================================================================== $nombre_sitio_2g = $_POST['nombre_sitio_2g']; //$tipo_sitio = $_POST['tipo_sitio']; //$nombre_sitio_3g = $_POST['nombre_sitio_3g']; //$propiedad = $_POST['propiedad']; //$direccion = $_POST['direccion']; $id2g = $_POST['id2g']; //$ubicacion = $_POST['ubicacion']; //$id3g = $_POST['id3g']; //$num_emp = $_POST['num_emp']; $fecha = date("Y-m-d"); $ingeniero = $_POST['ingeniero']; //echo "{$usuario}" : "{contrasena}";
private function addLogo($preheader) { $objDrawing = new \PHPExcel_Worksheet_Drawing(); $objDrawing->setName($preheader->name); $objDrawing->setDescription($preheader->desc); $objDrawing->setPath($preheader->logo); $objDrawing->setOffsetX($preheader->x); // setOffsetX works properly $objDrawing->setOffsetY($preheader->y); //setOffsetY has no effect $objDrawing->setCoordinates($preheader->coordinates); $objDrawing->setHeight($preheader->height); // logo height $objDrawing->setWorksheet($this->phpExcelObj->getActiveSheet()); }
foreach ($va_display_list as $vn_placement_id => $va_info) { if (strpos($va_info['bundle_name'], 'ca_object_representations.media') !== false && $va_info['settings']['display_mode'] == 'media') { $vs_version = str_replace("ca_object_representations.media.", "", $va_info['bundle_name']); $va_info = $vo_result->getMediaInfo('ca_object_representations.media', $vs_version); if ($va_info['MIMETYPE'] == 'image/jpeg') { // don't try to insert anything non-jpeg into an Excel file if (is_file($vs_path = $vo_result->getMediaPath('ca_object_representations.media', $vs_version))) { $image = "image" . $vs_supercol . $vs_column . $vn_line; $drawing = new PHPExcel_Worksheet_Drawing(); $drawing->setName($image); $drawing->setDescription($image); $drawing->setPath($vs_path); $drawing->setCoordinates($vs_supercol . $vs_column . $vn_line); $drawing->setWorksheet($o_sheet); $drawing->setOffsetX(10); $drawing->setOffsetY(10); } $vn_width = floor(intval($va_info['PROPERTIES']['width']) * $vn_ratio_pixels_to_excel_width); $vn_height = floor(intval($va_info['PROPERTIES']['height']) * $vn_ratio_pixels_to_excel_height); // set the calculated withs for the current row and column, // but make sure we don't make either smaller than they already are if ($vn_width > $o_sheet->getColumnDimension($vs_supercol . $vs_column)->getWidth()) { $o_sheet->getColumnDimension($vs_supercol . $vs_column)->setWidth($vn_width); } if ($vn_height > $o_sheet->getRowDimension($vn_line)->getRowHeight()) { $o_sheet->getRowDimension($vn_line)->setRowHeight($vn_height); } } } elseif ($vs_display_text = $t_display->getDisplayValue($vo_result, $vn_placement_id, array_merge(array('request' => $this->request, 'purify' => true), is_array($va_info['settings']) ? $va_info['settings'] : array()))) { $o_sheet->setCellValue($vs_supercol . $vs_column . $vn_line, html_entity_decode(strip_tags(br2nl($vs_display_text)), ENT_QUOTES | ENT_HTML5)); // We trust the autosizing up to a certain point, but
/** * Write the front page of the Excel spreadsheet * * @param PHPExcel_Worksheet $worksheet * @param array $config * @param array $DProps */ function writeFrontPageContent($worksheet, $config, $DProps) { $worksheet->SetTitle('Front Page'); // add logo $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setWorksheet($worksheet); $objDrawing->setName($DProps['Front Page']['Logo Name']); $objDrawing->setDescription($DProps['Front Page']['Logo Description']); $apath = __DIR__ . DIRECTORY_SEPARATOR . 'images' . DIRECTORY_SEPARATOR; $objDrawing->setPath($apath . $config->ParameterArray['PDFLogoFile']); $objDrawing->setCoordinates('A1'); $objDrawing->setOffsetX(5); $objDrawing->setOffsetY(5); // set the header of the print out $header_range = $DProps['Front Page']['HeaderRange']; $fillcolor = $config->ParameterArray['HeaderColor']; $fillcolor = strpos($fillcolor, '#') == 0 ? substr($fillcolor, 1) : $fillcolor; $worksheet->getStyle($header_range)->getFill()->getStartColor()->setRGB($fillcolor); $org_font_size = 20; $worksheet->setCellValue('A2', $config->ParameterArray['OrgName']); $worksheet->getStyle('A2')->getFont()->setSize($org_font_size); $worksheet->getStyle('A2')->getFont()->setBold(true); $worksheet->getRowDimension('2')->setRowHeight($org_font_size + 2); $worksheet->setCellValue('A4', 'Report generated by \'' . $DProps['Doc']['User'] . '\' on ' . date('Y-m-d H:i:s')); $worksheet->setCellValue('A7', 'Notes'); $worksheet->getStyle('A7')->getFont()->setSize(14); $worksheet->getStyle('A7')->getFont()->setBold(true); $max_remarks = count($DProps['Front Page']['remarks']); $offset = 8; for ($idx = 0; $idx < $max_remarks; $idx++) { $row = $offset + $idx; $worksheet->setCellValueExplicit('B' . $row, $DProps['Front Page']['remarks'][$idx], PHPExcel_Cell_DataType::TYPE_STRING); } $worksheet->getStyle('B' . $offset . ':B' . ($offset + $max_remarks - 1))->getAlignment()->setWrapText(true); $worksheet->getColumnDimension('B')->setWidth(120); $worksheet->getTabColor()->setRGB($fillcolor); }
/*// Merge cells echo date('H:i:s') . " Merge cells\n"; $objPHPExcel->getActiveSheet()->mergeCells('B3:O3');*/ //-----------Estilos-------------- $styleArray1 = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); //------------Imagen en encabezado--------- // $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&Please'); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setWorksheet($objWorksheet); $objDrawing->setName("logo"); $objDrawing->setDescription("Description"); $objDrawing->setPath('../img/GEM_hrz.jpg'); $objDrawing->setHeight(50); $objDrawing->setCoordinates('A1'); $objDrawing->setOffsetX(1); $objDrawing->setOffsetY(5); //-------------titulo de reporte-- $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A4:A4'); $objWorksheet->setCellValueByColumnAndRow(2, 4, 'LISTADO DE SOLICITUDES POR OBRA'); $objWorksheet->getStyle('A4')->applyFromArray($styleArray1); //----------------------------- // Add some data $objPHPExcel->getActiveSheet()->setCellValue('A6', 'No. Solicitud'); $objPHPExcel->getActiveSheet()->setCellValue('B6', 'Tipo de Solicitud'); $objPHPExcel->getActiveSheet()->setCellValue('C6', 'Nombre de la Obra'); $objPHPExcel->getActiveSheet()->setCellValue('D6', 'Monto'); $objPHPExcel->getActiveSheet()->setCellValue('E6', 'Estado'); $objPHPExcel->getActiveSheet()->getStyle('A6:F6')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('C4')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
$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); $sheet->getStyle('E' . $order_row . ':E' . $order_row_max)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $delivery_address = $o["delivery_street_address"] . " " . $o["delivery_city"] . " " . $o["delivery_state"] . ", " . $o["delivery_country"]; $delivery_address .= ' ' . $o['delivery_postcode']; $sheet->setCellValue('F' . $order_row, $delivery_address);