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