/**
* 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;
}
/**
* Add sheet
*
* @param string $name
* @return $this for method chaining
*/
public function addSheet($name)
{
$index = $this->_xls->getSheetCount();
$this->_xls->createSheet($index)->setTitle($name);
$this->setActiveSheet($index);
return $this;
}
/**
* Save PHPExcel to file
*
* @param string $pFileName
* @throws Exception
*/
public function save($pFilename = null)
{
if (!is_null($this->_spreadSheet)) {
// Create new ZIP file and open it for writing
$objZip = new ZipArchive();
// Try opening the ZIP file
if ($objZip->open($pFilename, ZIPARCHIVE::OVERWRITE) !== true) {
throw new Exception("Could not open " . $pFilename . " for writing.");
}
// Add media
for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); $i++) {
for ($j = 0; $j < $this->_spreadSheet->getSheet($i)->getDrawingCollection()->count(); $j++) {
if ($this->_spreadSheet->getSheet($i)->getDrawingCollection()->offsetGet($j) instanceof PHPExcel_Worksheet_BaseDrawing) {
$imgTemp = $this->_spreadSheet->getSheet($i)->getDrawingCollection()->offsetGet($j);
$objZip->addFromString('media/' . $imgTemp->getFilename(), file_get_contents($imgTemp->getPath()));
}
}
}
// Add phpexcel.xml to the document, which represents a PHP serialized PHPExcel object
$objZip->addFromString('phpexcel.xml', $this->_writeSerialized($this->_spreadSheet, $pFilename));
// Close file
if ($objZip->close() === false) {
throw new Exception("Could not close zip file {$pFilename}.");
}
} else {
throw new Exception("PHPExcel object unassigned.");
}
}
/**
* add body rows
*
* @param Tinebase_Record_RecordSet $records
*
* @todo add formulas
*/
public function processIteration($_records)
{
$this->_resolveRecords($_records);
// add record rows
$i = 0;
foreach ($_records as $record) {
//if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' ' . print_r($record->toArray(), true));
$columnId = 0;
foreach ($this->_config->columns->column as $field) {
// get type and value for cell
$cellType = isset($field->type) ? $field->type : 'string';
$cellValue = $this->_getCellValue($field, $record, $cellType);
// add formula
if ($field->formula) {
//if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Adding formula: ' . $field->formula);
$cellValue = $field->formula;
}
$this->_excelObject->getActiveSheet()->setCellValueByColumnAndRow($columnId++, $this->_currentRowIndex, $cellValue);
}
$i++;
$this->_currentRowIndex++;
}
// save number of records (only if we have more than 1 sheets / records are on the second sheet by default)
if ($this->_excelObject->getSheetCount() > 1) {
$this->_excelObject->setActiveSheetIndex(0);
$this->_excelObject->getActiveSheet()->setCellValueByColumnAndRow(5, 2, count($_records));
}
}
/**
* Generate sheet data
*
* @return string
* @throws Exception
*/
public function generateSheetData()
{
// PHPExcel object known?
if (is_null($this->_phpExcel)) {
throw new Exception('Internal PHPExcel object not set to an instance of an object.');
}
// Fetch sheets
$sheets = array();
if (is_null($this->_sheetIndex)) {
$sheets = $this->_phpExcel->getAllSheets();
} else {
$sheets[] = $this->_phpExcel->getSheet($this->_sheetIndex);
}
// Construct HTML
$html = '';
// Loop all sheets
$sheetId = 0;
foreach ($sheets as $sheet) {
// Get cell collection
$cellCollection = $sheet->getCellCollection();
// Write table header
$html .= $this->_generateTableHeader($sheet);
// Get worksheet dimension
$dimension = explode(':', $sheet->calculateWorksheetDimension());
$dimension[0] = PHPExcel_Cell::coordinateFromString($dimension[0]);
$dimension[0][0] = PHPExcel_Cell::columnIndexFromString($dimension[0][0]) - 1;
$dimension[1] = PHPExcel_Cell::coordinateFromString($dimension[1]);
$dimension[1][0] = PHPExcel_Cell::columnIndexFromString($dimension[1][0]) - 1;
// Loop trough cells
$rowData = null;
for ($row = $dimension[0][1]; $row <= $dimension[1][1]; ++$row) {
// Start a new row
$rowData = array();
// Loop trough columns
for ($column = $dimension[0][0]; $column <= $dimension[1][0]; ++$column) {
// Cell exists?
if ($sheet->cellExistsByColumnAndRow($column, $row)) {
$rowData[$column] = $sheet->getCellByColumnAndRow($column, $row);
} else {
$rowData[$column] = '';
}
}
// Write row
$html .= $this->_generateRow($sheet, $rowData, $row - 1);
}
// Write table footer
$html .= $this->_generateTableFooter();
// Writing PDF?
if ($this->_isPdf) {
if (is_null($this->_sheetIndex) && $sheetId + 1 < $this->_phpExcel->getSheetCount()) {
$html .= '<tcpdf method="AddPage" />';
}
}
// Next sheet
++$sheetId;
}
// Return
return $html;
}
/**
* (non-PHPdoc)
* @see Tinebase_Export_Abstract::_onAfterExportRecords()
*/
protected function _onAfterExportRecords($result)
{
// save number of records (only if we have more than 1 sheets / records are on the second sheet by default)
if ($this->_excelObject->getSheetCount() > 1) {
$this->_excelObject->setActiveSheetIndex(0);
$this->_excelObject->getActiveSheet()->setCellValueByColumnAndRow(5, 2, $result['totalcount']);
}
}
function crear_hoja($nombre = null)
{
$hoja = $this->excel->createSheet();
if (isset($nombre)) {
$hoja->setTitle(utf8_encode(strval($nombre)));
}
$this->excel->setActiveSheetIndex($this->excel->getSheetCount() - 1);
$this->cursor = $this->cursor_base;
}
/**
* Save PHPExcel to file
*
* @param string $pFileName
* @throws Exception
*/
public function save($pFilename = null)
{
// garbage collect
$this->_phpExcel->garbageCollect();
$saveDebugLog = PHPExcel_Calculation::getInstance()->writeDebugLog;
PHPExcel_Calculation::getInstance()->writeDebugLog = false;
$saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType();
PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
// initialize colors array
$this->_colors = array();
// Initialise workbook writer
$this->_writerWorkbook = new PHPExcel_Writer_Excel5_Workbook($this->_phpExcel, $this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser);
// Initialise worksheet writers
$countSheets = $this->_phpExcel->getSheetCount();
for ($i = 0; $i < $countSheets; ++$i) {
$this->_writerWorksheets[$i] = new PHPExcel_Writer_Excel5_Worksheet($this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_preCalculateFormulas, $this->_phpExcel->getSheet($i));
}
// build Escher objects. Escher objects for workbooks needs to be build before Escher object for workbook.
$this->_buildWorksheetEschers();
$this->_buildWorkbookEscher();
// add 15 identical cell style Xfs
// for now, we use the first cellXf instead of cellStyleXf
$cellXfCollection = $this->_phpExcel->getCellXfCollection();
for ($i = 0; $i < 15; ++$i) {
$this->_writerWorkbook->addXfWriter($cellXfCollection[0], true);
}
// add all the cell Xfs
foreach ($this->_phpExcel->getCellXfCollection() as $style) {
$this->_writerWorkbook->addXfWriter($style, false);
}
// initialize OLE file
$workbookStreamName = $this->_BIFF_version == 0x600 ? 'Workbook' : 'Book';
$OLE = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs($workbookStreamName));
// Write the worksheet streams before the global workbook stream,
// because the byte sizes of these are needed in the global workbook stream
$worksheetSizes = array();
for ($i = 0; $i < $countSheets; ++$i) {
$this->_writerWorksheets[$i]->close();
$worksheetSizes[] = $this->_writerWorksheets[$i]->_datasize;
}
// add binary data for global workbook stream
$OLE->append($this->_writerWorkbook->writeWorkbook($worksheetSizes));
// add binary data for sheet streams
for ($i = 0; $i < $countSheets; ++$i) {
$OLE->append($this->_writerWorksheets[$i]->getData());
}
$root = new PHPExcel_Shared_OLE_PPS_Root(time(), time(), array($OLE));
// save the OLE file
$res = $root->save($pFilename);
PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType);
PHPExcel_Calculation::getInstance()->writeDebugLog = $saveDebugLog;
}
/**
* Start render of a new spreadsheet
* @throws LaravelExcelException
* @return void
*/
protected function _render()
{
// There should be enough sheets to continue rendering
if ($this->excel->getSheetCount() < 0) {
throw new LaravelExcelException('[ERROR] Aborting spreadsheet render: no sheets were created.');
}
// Set the format
$this->_setFormat();
// Set the writer
$this->_setWriter();
// File has been rendered
$this->rendered = true;
}
/**
* Retrieve data and split to sheets.
* Example:
* <pre><code>
* <?php
$function = function($offset, $limit)
{
$result = ...
return array(
$result['data'],
$result['total'],
);
};
$xls = new XLSReport();
$xls->splitToSheets($function, $titles);
* ?>
* </code></pre>
* @param function $function
* @param array $titles
* @param PHPExcel_Chart $charts
*/
public function splitToSheets(&$function, &$titles = array(), $charts = null)
{
$offset = 0;
$limit = $this->limitLength;
do {
list($data, $total) = $function($offset, $limit);
if (!empty($data)) {
$this->newSheet($data, $titles, $charts);
}
$offset += $limit;
} while ($offset < $total);
$this->objPHPExcel->removeSheetByIndex($this->objPHPExcel->getSheetCount() - 1);
}
/**
*
* @param PHPExcel_Worksheet $hoja
* @param int $pk representa el Ãndice de la columna en el archivo de excel que está asociada con la llave primaria de la tabla
* @return string rerpesenta el nombre la ruta del archivo creado. Si no se pudo crear el archivo se regresa otra cosa :p
*/
function prepararArchivo($hoja, $pk = false, $incluirPrimeraFila = false)
{
$objetoExcel = new PHPExcel();
$hojaInsertar = $objetoExcel->getSheet(0);
$hojaInsertar->setTitle('Insertar');
if ($objetoExcel->getSheetCount() > 1) {
$hojaActualizar = $objetoExcel->getSheet(1);
$hojaActualizar->setTitle('Actualizar');
} else {
$hojaActualizar = new PHPExcel_Worksheet();
$hojaActualizar->setTitle('Actualizar');
$objetoExcel->addSheet($hojaActualizar);
}
$rango = $hoja->calculateWorksheetDataDimension();
if (!$incluirPrimeraFila) {
$rango[1] = '2';
}
$contenidoExcel = $hoja->rangeToArray($rango);
$datos = array();
$datos['insertar'] = array();
$datos['actualizar'] = array();
if ($pk) {
$db = new DbConnection();
$db->abrirConexion();
$llavePrimaria = $_SESSION['pk'];
foreach ($contenidoExcel as $fila) {
$existe = $db->existeRegistro($_SESSION['tabla'], $llavePrimaria, $fila[$pk]);
if ($existe) {
$datos['actualizar'][] = $fila;
} else {
$datos['insertar'][] = $fila;
}
}
$db->cerrarConexion();
} else {
foreach ($contenidoExcel as $fila) {
$datos['insertar'][] = $fila;
}
}
$hojaInsertar->fromArray($datos['insertar'], null, 'A1', true);
$hojaActualizar->fromArray($datos['actualizar'], null, 'A1', true);
$escritorExcel = PHPExcel_IOFactory::createWriter($objetoExcel, 'Excel2007');
$escritorExcel->save('excelTmp/tmp_import_upload.xlsx');
return 'excelTmp/tmp_import_upload.xlsx';
}
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param PHPExcel $objPHPExcel
* @return PHPExcel
* @throws Exception
*/
public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
// Create new PHPExcel
while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) {
$objPHPExcel->createSheet();
}
$objPHPExcel->setActiveSheetIndex($this->_sheetIndex);
$fromFormats = array('\\-', '\\ ');
$toFormats = array('-', ' ');
// Open file
$fileHandle = fopen($pFilename, 'r');
if ($fileHandle === false) {
throw new Exception("Could not open file {$pFilename} for reading.");
}
// Loop through file
$rowData = array();
$column = $row = '';
// loop through one row (line) at a time in the file
while (($rowData = fgets($fileHandle)) !== FALSE) {
// convert SYLK encoded $rowData to UTF-8
$rowData = Shared_String::SYLKtoUTF8($rowData);
// explode each row at semicolons while taking into account that literal semicolon (;)
// is escaped like this (;;)
$rowData = explode("\t", str_replace('¤', ';', str_replace(';', "\t", str_replace(';;', '¤', rtrim($rowData)))));
$dataType = array_shift($rowData);
// Read shared styles
if ($dataType == 'P') {
$formatArray = array();
foreach ($rowData as $rowDatum) {
switch ($rowDatum[0]) {
case 'P':
$formatArray['numberformat']['code'] = str_replace($fromFormats, $toFormats, substr($rowDatum, 1));
break;
case 'E':
case 'F':
$formatArray['font']['name'] = substr($rowDatum, 1);
break;
case 'L':
$formatArray['font']['size'] = substr($rowDatum, 1);
break;
case 'S':
$styleSettings = substr($rowDatum, 1);
for ($i = 0; $i < strlen($styleSettings); ++$i) {
switch ($styleSettings[$i]) {
case 'I':
$formatArray['font']['italic'] = true;
break;
case 'D':
$formatArray['font']['bold'] = true;
break;
case 'T':
$formatArray['borders']['top']['style'] = Style_Border::BORDER_THIN;
break;
case 'B':
$formatArray['borders']['bottom']['style'] = Style_Border::BORDER_THIN;
break;
case 'L':
$formatArray['borders']['left']['style'] = Style_Border::BORDER_THIN;
break;
case 'R':
$formatArray['borders']['right']['style'] = Style_Border::BORDER_THIN;
break;
}
}
break;
}
}
$this->_formats['P' . $this->_format++] = $formatArray;
// Read cell value data
} elseif ($dataType == 'C') {
$hasCalculatedValue = false;
$cellData = $cellDataFormula = '';
foreach ($rowData as $rowDatum) {
switch ($rowDatum[0]) {
case 'C':
case 'X':
$column = substr($rowDatum, 1);
break;
case 'R':
case 'Y':
$row = substr($rowDatum, 1);
break;
case 'K':
$cellData = substr($rowDatum, 1);
break;
case 'E':
$cellDataFormula = '=' . substr($rowDatum, 1);
// Convert R1C1 style references to A1 style references (but only when not quoted)
$temp = explode('"', $cellDataFormula);
foreach ($temp as $key => &$value) {
// Only count/replace in alternate array entries
if ($key % 2 == 0) {
preg_match_all('/(R(\\[?-?\\d*\\]?))(C(\\[?-?\\d*\\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
// Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
// through the formula from left to right. Reversing means that we work right to left.through
// the formula
$cellReferences = array_reverse($cellReferences);
// Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
// then modify the formula to use that new reference
foreach ($cellReferences as $cellReference) {
$rowReference = $cellReference[2][0];
// Empty R reference is the current row
if ($rowReference == '') {
$rowReference = $row;
}
// Bracketed R references are relative to the current row
if ($rowReference[0] == '[') {
$rowReference = $row + trim($rowReference, '[]');
}
$columnReference = $cellReference[4][0];
// Empty C reference is the current column
if ($columnReference == '') {
$columnReference = $column;
}
// Bracketed C references are relative to the current column
if ($columnReference[0] == '[') {
$columnReference = $column + trim($columnReference, '[]');
}
$A1CellReference = Cell::stringFromColumnIndex($columnReference - 1) . $rowReference;
$value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
}
}
}
unset($value);
// Then rebuild the formula string
$cellDataFormula = implode('"', $temp);
$hasCalculatedValue = true;
break;
}
}
$columnLetter = Cell::stringFromColumnIndex($column - 1);
$cellData = Calculation::_unwrapResult($cellData);
// Set cell value
$objPHPExcel->getActiveSheet()->getCell($columnLetter . $row)->setValue($hasCalculatedValue ? $cellDataFormula : $cellData);
if ($hasCalculatedValue) {
$cellData = Calculation::_unwrapResult($cellData);
$objPHPExcel->getActiveSheet()->getCell($columnLetter . $row)->setCalculatedValue($cellData);
}
// Read cell formatting
} elseif ($dataType == 'F') {
$formatStyle = $columnWidth = $styleSettings = '';
$styleData = array();
foreach ($rowData as $rowDatum) {
switch ($rowDatum[0]) {
case 'C':
case 'X':
$column = substr($rowDatum, 1);
break;
case 'R':
case 'Y':
$row = substr($rowDatum, 1);
break;
case 'P':
$formatStyle = $rowDatum;
break;
case 'W':
list($startCol, $endCol, $columnWidth) = explode(' ', substr($rowDatum, 1));
break;
case 'S':
$styleSettings = substr($rowDatum, 1);
for ($i = 0; $i < strlen($styleSettings); ++$i) {
switch ($styleSettings[$i]) {
case 'I':
$styleData['font']['italic'] = true;
break;
case 'D':
$styleData['font']['bold'] = true;
break;
case 'T':
$styleData['borders']['top']['style'] = Style_Border::BORDER_THIN;
break;
case 'B':
$styleData['borders']['bottom']['style'] = Style_Border::BORDER_THIN;
break;
case 'L':
$styleData['borders']['left']['style'] = Style_Border::BORDER_THIN;
break;
case 'R':
$styleData['borders']['right']['style'] = Style_Border::BORDER_THIN;
break;
}
}
break;
}
}
if ($formatStyle > '' && $column > '' && $row > '') {
$columnLetter = Cell::stringFromColumnIndex($column - 1);
$objPHPExcel->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($this->_formats[$formatStyle]);
}
if (count($styleData) > 0 && $column > '' && $row > '') {
$columnLetter = Cell::stringFromColumnIndex($column - 1);
$objPHPExcel->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($styleData);
}
if ($columnWidth > '') {
if ($startCol == $endCol) {
$startCol = Cell::stringFromColumnIndex($startCol - 1);
$objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
} else {
$startCol = Cell::stringFromColumnIndex($startCol - 1);
$endCol = Cell::stringFromColumnIndex($endCol - 1);
$objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
do {
$objPHPExcel->getActiveSheet()->getColumnDimension(++$startCol)->setWidth($columnWidth);
} while ($startCol != $endCol);
}
}
} else {
foreach ($rowData as $rowDatum) {
switch ($rowDatum[0]) {
case 'C':
case 'X':
$column = substr($rowDatum, 1);
break;
case 'R':
case 'Y':
$row = substr($rowDatum, 1);
break;
}
}
}
}
// Close file
fclose($fileHandle);
// Return
return $objPHPExcel;
}
/**
* 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;
}
/**
* Write Internal SUPBOOK record
*/
private function writeSupbookInternal()
{
$record = 0x1ae;
// Record identifier
$length = 0x4;
// Bytes to follow
$header = pack("vv", $record, $length);
$data = pack("vv", $this->phpExcel->getSheetCount(), 0x401);
return $this->writeData($header . $data);
}
/**
* Write workbook relationships to XML format
*
* @param PHPExcel $pPHPExcel
* @return string XML Output
* @throws Exception
*/
public function writeWorkbookRelationships(PHPExcel $pPHPExcel = null)
{
// Create XML writer
$objWriter = null;
if ($this->getParentWriter()->getUseDiskCaching()) {
$objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory());
} else {
$objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_MEMORY);
}
// XML header
$objWriter->startDocument('1.0', 'UTF-8', 'yes');
// Relationships
$objWriter->startElement('Relationships');
$objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/package/2006/relationships');
// Relationship styles.xml
$this->_writeRelationship($objWriter, 1, 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles', 'styles.xml');
// Relationship theme/theme1.xml
$this->_writeRelationship($objWriter, 2, 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme', 'theme/theme1.xml');
// Relationship sharedStrings.xml
$this->_writeRelationship($objWriter, 3, 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings', 'sharedStrings.xml');
// Relationships with sheets
$sheetCount = $pPHPExcel->getSheetCount();
for ($i = 0; $i < $sheetCount; ++$i) {
$this->_writeRelationship($objWriter, $i + 1 + 3, 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet', 'worksheets/sheet' . ($i + 1) . '.xml');
}
$objWriter->endElement();
// Return
return $objWriter->getData();
}
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param PHPExcel $objPHPExcel
* @throws Exception
*/
public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
// Create new PHPExcel
while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) {
$objPHPExcel->createSheet();
}
$objPHPExcel->setActiveSheetIndex($this->_sheetIndex);
// Open file
$fileHandle = fopen($pFilename, 'r');
if ($fileHandle === false) {
throw new Exception("Could not open file {$pFilename} for reading.");
}
// Loop trough file
$currentRow = 0;
$rowData = array();
while (($rowData = fgetcsv($fileHandle, 0, $this->_delimiter, $this->_enclosure)) !== FALSE) {
$currentRow++;
for ($i = 0; $i < count($rowData); $i++) {
if ($rowData[$i] != '') {
// Unescape enclosures
$rowData[$i] = str_replace("\\" . $this->_enclosure, $this->_enclosure, $rowData[$i]);
$rowData[$i] = str_replace($this->_enclosure . $this->_enclosure, $this->_enclosure, $rowData[$i]);
// Set cell value
$objPHPExcel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($i) . $currentRow, $rowData[$i]);
}
}
}
// Close file
fclose($fileHandle);
// Return
return $objPHPExcel;
}
/**
* Write content types to XML format
*
* @param PHPExcel $pPHPExcel
* @return string XML Output
* @throws Exception
*/
public function writeContentTypes(PHPExcel $pPHPExcel = null)
{
// Create XML writer
$objWriter = null;
if ($this->getParentWriter()->getUseDiskCaching()) {
$objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory());
} else {
$objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_MEMORY);
}
// XML header
$objWriter->startDocument('1.0', 'UTF-8', 'yes');
// Types
$objWriter->startElement('Types');
$objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/package/2006/content-types');
// Theme
$this->_writeOverrideContentType($objWriter, '/xl/theme/theme1.xml', 'application/vnd.openxmlformats-officedocument.theme+xml');
// Styles
$this->_writeOverrideContentType($objWriter, '/xl/styles.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml');
// Rels
$this->_writeDefaultContentType($objWriter, 'rels', 'application/vnd.openxmlformats-package.relationships+xml');
// XML
$this->_writeDefaultContentType($objWriter, 'xml', 'application/xml');
// VML
$this->_writeDefaultContentType($objWriter, 'vml', 'application/vnd.openxmlformats-officedocument.vmlDrawing');
// Workbook
$this->_writeOverrideContentType($objWriter, '/xl/workbook.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml');
// DocProps
$this->_writeOverrideContentType($objWriter, '/docProps/app.xml', 'application/vnd.openxmlformats-officedocument.extended-properties+xml');
$this->_writeOverrideContentType($objWriter, '/docProps/core.xml', 'application/vnd.openxmlformats-package.core-properties+xml');
// Worksheets
$sheetCount = $pPHPExcel->getSheetCount();
for ($i = 0; $i < $sheetCount; ++$i) {
$this->_writeOverrideContentType($objWriter, '/xl/worksheets/sheet' . ($i + 1) . '.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml');
}
// Shared strings
$this->_writeOverrideContentType($objWriter, '/xl/sharedStrings.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml');
// Add worksheet relationship content types
for ($i = 0; $i < $sheetCount; ++$i) {
if ($pPHPExcel->getSheet($i)->getDrawingCollection()->count() > 0) {
$this->_writeOverrideContentType($objWriter, '/xl/drawings/drawing' . ($i + 1) . '.xml', 'application/vnd.openxmlformats-officedocument.drawing+xml');
}
}
// Comments
for ($i = 0; $i < $sheetCount; ++$i) {
if (count($pPHPExcel->getSheet($i)->getComments()) > 0) {
$this->_writeOverrideContentType($objWriter, '/xl/comments' . ($i + 1) . '.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml');
}
}
// Add media content-types
$aMediaContentTypes = array();
$mediaCount = $this->getParentWriter()->getDrawingHashTable()->count();
for ($i = 0; $i < $mediaCount; ++$i) {
$extension = '';
$mimeType = '';
if ($this->getParentWriter()->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_Drawing) {
$extension = strtolower($this->getParentWriter()->getDrawingHashTable()->getByIndex($i)->getExtension());
$mimeType = $this->_getImageMimeType($this->getParentWriter()->getDrawingHashTable()->getByIndex($i)->getPath());
} else {
if ($this->getParentWriter()->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_MemoryDrawing) {
$extension = strtolower($this->getParentWriter()->getDrawingHashTable()->getByIndex($i)->getMimeType());
$extension = explode('/', $extension);
$extension = $extension[1];
$mimeType = $this->getParentWriter()->getDrawingHashTable()->getByIndex($i)->getMimeType();
}
}
if (!isset($aMediaContentTypes[$extension])) {
$aMediaContentTypes[$extension] = $mimeType;
$this->_writeDefaultContentType($objWriter, $extension, $mimeType);
}
}
$sheetCount = $pPHPExcel->getSheetCount();
for ($i = 0; $i < $sheetCount; ++$i) {
if (count($pPHPExcel->getSheet()->getHeaderFooter()->getImages()) > 0) {
foreach ($pPHPExcel->getSheet()->getHeaderFooter()->getImages() as $image) {
if (!isset($aMediaContentTypes[strtolower($image->getExtension())])) {
$aMediaContentTypes[strtolower($image->getExtension())] = $this->_getImageMimeType($image->getPath());
$this->_writeDefaultContentType($objWriter, strtolower($image->getExtension()), $aMediaContentTypes[strtolower($image->getExtension())]);
}
}
}
}
$objWriter->endElement();
// Return
return $objWriter->getData();
}
/**
* Calculate information about HTML colspan and rowspan which is not always the same as Excel's
*/
private function _calculateSpans()
{
// Identify all cells that should be omitted in HTML due to cell merge.
// In HTML only the upper-left cell should be written and it should have
// appropriate rowspan / colspan attribute
$sheetIndexes = $this->_sheetIndex !== null ? array($this->_sheetIndex) : range(0, $this->_phpExcel->getSheetCount() - 1);
foreach ($sheetIndexes as $sheetIndex) {
$sheet = $this->_phpExcel->getSheet($sheetIndex);
$candidateSpannedRow = array();
// loop through all Excel merged cells
foreach ($sheet->getMergeCells() as $cells) {
list($cells, ) = PHPExcel_Cell::splitRange($cells);
$first = $cells[0];
$last = $cells[1];
list($fc, $fr) = PHPExcel_Cell::coordinateFromString($first);
$fc = PHPExcel_Cell::columnIndexFromString($fc) - 1;
list($lc, $lr) = PHPExcel_Cell::coordinateFromString($last);
$lc = PHPExcel_Cell::columnIndexFromString($lc) - 1;
// loop through the individual cells in the individual merge
$r = $fr - 1;
while ($r++ < $lr) {
// also, flag this row as a HTML row that is candidate to be omitted
$candidateSpannedRow[$r] = $r;
$c = $fc - 1;
while ($c++ < $lc) {
if (!($c == $fc && $r == $fr)) {
// not the upper-left cell (should not be written in HTML)
$this->_isSpannedCell[$sheetIndex][$r][$c] = array('baseCell' => array($fr, $fc));
} else {
// upper-left is the base cell that should hold the colspan/rowspan attribute
$this->_isBaseCell[$sheetIndex][$r][$c] = array('xlrowspan' => $lr - $fr + 1, 'rowspan' => $lr - $fr + 1, 'xlcolspan' => $lc - $fc + 1, 'colspan' => $lc - $fc + 1);
}
}
}
}
// Identify which rows should be omitted in HTML. These are the rows where all the cells
// participate in a merge and the where base cells are somewhere above.
$countColumns = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn());
foreach ($candidateSpannedRow as $rowIndex) {
if (isset($this->_isSpannedCell[$sheetIndex][$rowIndex])) {
if (count($this->_isSpannedCell[$sheetIndex][$rowIndex]) == $countColumns) {
$this->_isSpannedRow[$sheetIndex][$rowIndex] = $rowIndex;
}
}
}
// For each of the omitted rows we found above, the affected rowspans should be subtracted by 1
if (isset($this->_isSpannedRow[$sheetIndex])) {
foreach ($this->_isSpannedRow[$sheetIndex] as $rowIndex) {
$adjustedBaseCells = array();
$c = -1;
$e = $countColumns - 1;
while ($c++ < $e) {
$baseCell = $this->_isSpannedCell[$sheetIndex][$rowIndex][$c]['baseCell'];
if (!in_array($baseCell, $adjustedBaseCells)) {
// subtract rowspan by 1
--$this->_isBaseCell[$sheetIndex][$baseCell[0]][$baseCell[1]]['rowspan'];
$adjustedBaseCells[] = $baseCell;
}
}
}
}
// TODO: Same for columns
}
// We have calculated the spans
$this->_spansAreCalculated = true;
}
/**
* Get an array of all conditional styles
*
* @param PHPExcel $pPHPExcel
* @return PHPExcel_Style_Conditional[] All conditional styles in PHPExcel
* @throws Exception
*/
public function allConditionalStyles(PHPExcel $pPHPExcel = null)
{
// Get an array of all styles
$aStyles = array();
$sheetCount = $pPHPExcel->getSheetCount();
for ($i = 0; $i < $sheetCount; ++$i) {
foreach ($pPHPExcel->getSheet($i)->getConditionalStylesCollection() as $conditionalStyles) {
foreach ($conditionalStyles as $conditionalStyle) {
$aStyles[] = $conditionalStyle;
}
}
}
return $aStyles;
}
/**
* Write content types to XML format
*
* @param PHPExcel $pPHPExcel
* @param boolean $includeCharts Flag indicating if we should include drawing details for charts
* @return string XML Output
* @throws PHPExcel_Writer_Exception
*/
public function writeContentTypes(PHPExcel $pPHPExcel = null, $includeCharts = FALSE)
{
// Create XML writer
$objWriter = null;
if ($this->getParentWriter()->getUseDiskCaching()) {
$objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory());
} else {
$objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_MEMORY);
}
// XML header
$objWriter->startDocument('1.0', 'UTF-8', 'yes');
// Types
$objWriter->startElement('Types');
$objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/package/2006/content-types');
// Theme
$this->_writeOverrideContentType($objWriter, '/xl/theme/theme1.xml', 'application/vnd.openxmlformats-officedocument.theme+xml');
// Styles
$this->_writeOverrideContentType($objWriter, '/xl/styles.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml');
// Rels
$this->_writeDefaultContentType($objWriter, 'rels', 'application/vnd.openxmlformats-package.relationships+xml');
// XML
$this->_writeDefaultContentType($objWriter, 'xml', 'application/xml');
// VML
$this->_writeDefaultContentType($objWriter, 'vml', 'application/vnd.openxmlformats-officedocument.vmlDrawing');
// Workbook
if ($pPHPExcel->hasMacros()) {
//Macros in workbook ?
// Yes : not standard content but "macroEnabled"
$this->_writeOverrideContentType($objWriter, '/xl/workbook.xml', 'application/vnd.ms-excel.sheet.macroEnabled.main+xml');
//... and define a new type for the VBA project
$this->_writeDefaultContentType($objWriter, 'bin', 'application/vnd.ms-office.vbaProject');
if ($pPHPExcel->hasMacrosCertificate()) {
// signed macros ?
// Yes : add needed information
$this->_writeOverrideContentType($objWriter, '/xl/vbaProjectSignature.bin', 'application/vnd.ms-office.vbaProjectSignature');
}
} else {
// no macros in workbook, so standard type
$this->_writeOverrideContentType($objWriter, '/xl/workbook.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml');
}
// DocProps
$this->_writeOverrideContentType($objWriter, '/docProps/app.xml', 'application/vnd.openxmlformats-officedocument.extended-properties+xml');
$this->_writeOverrideContentType($objWriter, '/docProps/core.xml', 'application/vnd.openxmlformats-package.core-properties+xml');
$customPropertyList = $pPHPExcel->getProperties()->getCustomProperties();
if (!empty($customPropertyList)) {
$this->_writeOverrideContentType($objWriter, '/docProps/custom.xml', 'application/vnd.openxmlformats-officedocument.custom-properties+xml');
}
// Worksheets
$sheetCount = $pPHPExcel->getSheetCount();
for ($i = 0; $i < $sheetCount; ++$i) {
$this->_writeOverrideContentType($objWriter, '/xl/worksheets/sheet' . ($i + 1) . '.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml');
}
// Shared strings
$this->_writeOverrideContentType($objWriter, '/xl/sharedStrings.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml');
// Add worksheet relationship content types
$chart = 1;
for ($i = 0; $i < $sheetCount; ++$i) {
$drawings = $pPHPExcel->getSheet($i)->getDrawingCollection();
$drawingCount = count($drawings);
$chartCount = $includeCharts ? $pPHPExcel->getSheet($i)->getChartCount() : 0;
// We need a drawing relationship for the worksheet if we have either drawings or charts
if ($drawingCount > 0 || $chartCount > 0) {
$this->_writeOverrideContentType($objWriter, '/xl/drawings/drawing' . ($i + 1) . '.xml', 'application/vnd.openxmlformats-officedocument.drawing+xml');
}
// If we have charts, then we need a chart relationship for every individual chart
if ($chartCount > 0) {
for ($c = 0; $c < $chartCount; ++$c) {
$this->_writeOverrideContentType($objWriter, '/xl/charts/chart' . $chart++ . '.xml', 'application/vnd.openxmlformats-officedocument.drawingml.chart+xml');
}
}
}
// Comments
for ($i = 0; $i < $sheetCount; ++$i) {
if (count($pPHPExcel->getSheet($i)->getComments()) > 0) {
$this->_writeOverrideContentType($objWriter, '/xl/comments' . ($i + 1) . '.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml');
}
}
// Add media content-types
$aMediaContentTypes = array();
$mediaCount = $this->getParentWriter()->getDrawingHashTable()->count();
for ($i = 0; $i < $mediaCount; ++$i) {
$extension = '';
$mimeType = '';
if ($this->getParentWriter()->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_Drawing) {
$extension = strtolower($this->getParentWriter()->getDrawingHashTable()->getByIndex($i)->getExtension());
$mimeType = $this->_getImageMimeType($this->getParentWriter()->getDrawingHashTable()->getByIndex($i)->getPath());
} else {
if ($this->getParentWriter()->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_MemoryDrawing) {
$extension = strtolower($this->getParentWriter()->getDrawingHashTable()->getByIndex($i)->getMimeType());
$extension = explode('/', $extension);
$extension = $extension[1];
$mimeType = $this->getParentWriter()->getDrawingHashTable()->getByIndex($i)->getMimeType();
}
}
if (!isset($aMediaContentTypes[$extension])) {
$aMediaContentTypes[$extension] = $mimeType;
$this->_writeDefaultContentType($objWriter, $extension, $mimeType);
}
}
if ($pPHPExcel->hasRibbonBinObjects()) {
//Some additional objects in the ribbon ?
//we need to write "Extension" but not already write for media content
$tabRibbonTypes = array_diff($pPHPExcel->getRibbonBinObjects('types'), array_keys($aMediaContentTypes));
foreach ($tabRibbonTypes as $aRibbonType) {
$mimeType = 'image/.' . $aRibbonType;
//we wrote $mimeType like customUI Editor
$this->_writeDefaultContentType($objWriter, $aRibbonType, $mimeType);
}
}
$sheetCount = $pPHPExcel->getSheetCount();
for ($i = 0; $i < $sheetCount; ++$i) {
if (count($pPHPExcel->getSheet()->getHeaderFooter()->getImages()) > 0) {
foreach ($pPHPExcel->getSheet()->getHeaderFooter()->getImages() as $image) {
if (!isset($aMediaContentTypes[strtolower($image->getExtension())])) {
$aMediaContentTypes[strtolower($image->getExtension())] = $this->_getImageMimeType($image->getPath());
$this->_writeDefaultContentType($objWriter, strtolower($image->getExtension()), $aMediaContentTypes[strtolower($image->getExtension())]);
}
}
}
}
$objWriter->endElement();
// Return
return $objWriter->getData();
}
/**
* Save PHPExcel to file
*
* @param string $pFileName
* @throws Exception
*/
public function save($pFilename = null)
{
if (!is_null($this->_spreadSheet)) {
// garbage collect
$this->_spreadSheet->garbageCollect();
// If $pFilename is php://output or php://stdout, make it a temporary file...
$originalFilename = $pFilename;
if (strtolower($pFilename) == 'php://output' || strtolower($pFilename) == 'php://stdout') {
$pFilename = @tempnam('./', 'phpxltmp');
if ($pFilename == '') {
$pFilename = $originalFilename;
}
}
$saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType();
PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
// Create string lookup table
$this->_stringTable = array();
for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) {
$this->_stringTable = $this->getWriterPart('StringTable')->createStringTable($this->_spreadSheet->getSheet($i), $this->_stringTable);
}
// Create styles dictionaries
$this->_stylesConditionalHashTable->addFromSource($this->getWriterPart('Style')->allConditionalStyles($this->_spreadSheet));
$this->_fillHashTable->addFromSource($this->getWriterPart('Style')->allFills($this->_spreadSheet));
$this->_fontHashTable->addFromSource($this->getWriterPart('Style')->allFonts($this->_spreadSheet));
$this->_bordersHashTable->addFromSource($this->getWriterPart('Style')->allBorders($this->_spreadSheet));
$this->_numFmtHashTable->addFromSource($this->getWriterPart('Style')->allNumberFormats($this->_spreadSheet));
// Create drawing dictionary
$this->_drawingHashTable->addFromSource($this->getWriterPart('Drawing')->allDrawings($this->_spreadSheet));
// Create new ZIP file and open it for writing
$objZip = new ZipArchive();
// Try opening the ZIP file
if ($objZip->open($pFilename, ZIPARCHIVE::OVERWRITE) !== true) {
if ($objZip->open($pFilename, ZIPARCHIVE::CREATE) !== true) {
throw new Exception("Could not open " . $pFilename . " for writing.");
}
}
// Add [Content_Types].xml to ZIP file
$objZip->addFromString('[Content_Types].xml', $this->getWriterPart('ContentTypes')->writeContentTypes($this->_spreadSheet));
// Add relationships to ZIP file
$objZip->addFromString('_rels/.rels', $this->getWriterPart('Rels')->writeRelationships($this->_spreadSheet));
$objZip->addFromString('xl/_rels/workbook.xml.rels', $this->getWriterPart('Rels')->writeWorkbookRelationships($this->_spreadSheet));
// Add document properties to ZIP file
$objZip->addFromString('docProps/app.xml', $this->getWriterPart('DocProps')->writeDocPropsApp($this->_spreadSheet));
$objZip->addFromString('docProps/core.xml', $this->getWriterPart('DocProps')->writeDocPropsCore($this->_spreadSheet));
// Add theme to ZIP file
$objZip->addFromString('xl/theme/theme1.xml', $this->getWriterPart('Theme')->writeTheme($this->_spreadSheet));
// Add string table to ZIP file
$objZip->addFromString('xl/sharedStrings.xml', $this->getWriterPart('StringTable')->writeStringTable($this->_stringTable));
// Add styles to ZIP file
$objZip->addFromString('xl/styles.xml', $this->getWriterPart('Style')->writeStyles($this->_spreadSheet));
// Add workbook to ZIP file
$objZip->addFromString('xl/workbook.xml', $this->getWriterPart('Workbook')->writeWorkbook($this->_spreadSheet));
// Add worksheets
for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) {
$objZip->addFromString('xl/worksheets/sheet' . ($i + 1) . '.xml', $this->getWriterPart('Worksheet')->writeWorksheet($this->_spreadSheet->getSheet($i), $this->_stringTable));
}
// Add worksheet relationships (drawings, ...)
for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) {
// Add relationships
$objZip->addFromString('xl/worksheets/_rels/sheet' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeWorksheetRelationships($this->_spreadSheet->getSheet($i), $i + 1));
// Add drawing relationship parts
if ($this->_spreadSheet->getSheet($i)->getDrawingCollection()->count() > 0) {
// Drawing relationships
$objZip->addFromString('xl/drawings/_rels/drawing' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeDrawingRelationships($this->_spreadSheet->getSheet($i)));
// Drawings
$objZip->addFromString('xl/drawings/drawing' . ($i + 1) . '.xml', $this->getWriterPart('Drawing')->writeDrawings($this->_spreadSheet->getSheet($i)));
}
// Add comment relationship parts
if (count($this->_spreadSheet->getSheet($i)->getComments()) > 0) {
// VML Comments
$objZip->addFromString('xl/drawings/vmlDrawing' . ($i + 1) . '.vml', $this->getWriterPart('Comments')->writeVMLComments($this->_spreadSheet->getSheet($i)));
// Comments
$objZip->addFromString('xl/comments' . ($i + 1) . '.xml', $this->getWriterPart('Comments')->writeComments($this->_spreadSheet->getSheet($i)));
}
// Add header/footer relationship parts
if (count($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages()) > 0) {
// VML Drawings
$objZip->addFromString('xl/drawings/vmlDrawingHF' . ($i + 1) . '.vml', $this->getWriterPart('Drawing')->writeVMLHeaderFooterImages($this->_spreadSheet->getSheet($i)));
// VML Drawing relationships
$objZip->addFromString('xl/drawings/_rels/vmlDrawingHF' . ($i + 1) . '.vml.rels', $this->getWriterPart('Rels')->writeHeaderFooterDrawingRelationships($this->_spreadSheet->getSheet($i)));
// Media
foreach ($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages() as $image) {
$objZip->addFromString('xl/media/' . $image->getIndexedFilename(), file_get_contents($image->getPath()));
}
}
}
// Add media
for ($i = 0; $i < $this->getDrawingHashTable()->count(); ++$i) {
if ($this->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_Drawing) {
$imageContents = null;
$imagePath = $this->getDrawingHashTable()->getByIndex($i)->getPath();
if (strpos($imagePath, 'zip://') !== false) {
$imagePath = substr($imagePath, 6);
$imagePathSplitted = explode('#', $imagePath);
$imageZip = new ZipArchive();
$imageZip->open($imagePathSplitted[0]);
$imageContents = $imageZip->getFromName($imagePathSplitted[1]);
$imageZip->close();
unset($imageZip);
} else {
$imageContents = file_get_contents($imagePath);
}
$objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents);
} else {
if ($this->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_MemoryDrawing) {
ob_start();
call_user_func($this->getDrawingHashTable()->getByIndex($i)->getRenderingFunction(), $this->getDrawingHashTable()->getByIndex($i)->getImageResource());
$imageContents = ob_get_contents();
ob_end_clean();
$objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents);
}
}
}
PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType);
// Close file
if ($objZip->close() === false) {
throw new Exception("Could not close zip file {$pFilename}.");
}
// If a temporary file was used, copy it to the correct file stream
if ($originalFilename != $pFilename) {
if (copy($pFilename, $originalFilename) === false) {
throw new Exception("Could not copy temporary zip file {$pFilename} to {$originalFilename}.");
}
@unlink($pFilename);
}
} else {
throw new Exception("PHPExcel object unassigned.");
}
}
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param PHPExcel $objPHPExcel
* @return PHPExcel
* @throws PHPExcel_Reader_Exception
*/
public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
{
// Open file to validate
$this->_openFile($pFilename);
if (!$this->_isValidFormat()) {
fclose($this->_fileHandle);
throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid HTML file.");
}
// Close after validating
fclose($this->_fileHandle);
// Create new PHPExcel
while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) {
$objPHPExcel->createSheet();
}
$objPHPExcel->setActiveSheetIndex($this->_sheetIndex);
// Create a new DOM object
$dom = new domDocument();
// Reload the HTML file into the DOM object
$loaded = $dom->loadHTMLFile($pFilename);
if ($loaded === FALSE) {
throw new PHPExcel_Reader_Exception('Failed to load ', $pFilename, ' as a DOM Document');
}
// Discard white space
$dom->preserveWhiteSpace = false;
$row = 0;
$column = 'A';
$content = '';
$this->_processDomElement($dom, $objPHPExcel->getActiveSheet(), $row, $column, $content);
// echo '<hr />';
// var_dump($this->_dataArray);
// Return
return $objPHPExcel;
}
/**
* Save PHPExcel to file
*
* @param string $pFilename
* @throws PHPExcel_Writer_Exception
*/
public function save($pFilename = null)
{
if ($this->_spreadSheet !== NULL) {
// garbage collect
$this->_spreadSheet->garbageCollect();
// If $pFilename is php://output or php://stdout, make it a temporary file...
$originalFilename = $pFilename;
if (strtolower($pFilename) == 'php://output' || strtolower($pFilename) == 'php://stdout') {
$pFilename = @tempnam(PHPExcel_Shared_File::sys_get_temp_dir(), 'phpxltmp');
if ($pFilename == '') {
$pFilename = $originalFilename;
}
}
$saveDebugLog = PHPExcel_Calculation::getInstance($this->_spreadSheet)->getDebugLog()->getWriteDebugLog();
PHPExcel_Calculation::getInstance($this->_spreadSheet)->getDebugLog()->setWriteDebugLog(FALSE);
$saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType();
PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
// Create string lookup table
$this->_stringTable = array();
for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) {
$this->_stringTable = $this->getWriterPart('StringTable')->createStringTable($this->_spreadSheet->getSheet($i), $this->_stringTable);
}
// Create styles dictionaries
$this->_styleHashTable->addFromSource($this->getWriterPart('Style')->allStyles($this->_spreadSheet));
$this->_stylesConditionalHashTable->addFromSource($this->getWriterPart('Style')->allConditionalStyles($this->_spreadSheet));
$this->_fillHashTable->addFromSource($this->getWriterPart('Style')->allFills($this->_spreadSheet));
$this->_fontHashTable->addFromSource($this->getWriterPart('Style')->allFonts($this->_spreadSheet));
$this->_bordersHashTable->addFromSource($this->getWriterPart('Style')->allBorders($this->_spreadSheet));
$this->_numFmtHashTable->addFromSource($this->getWriterPart('Style')->allNumberFormats($this->_spreadSheet));
// Create drawing dictionary
$this->_drawingHashTable->addFromSource($this->getWriterPart('Drawing')->allDrawings($this->_spreadSheet));
// Create new ZIP file and open it for writing
$zipClass = PHPExcel_Settings::getZipClass();
$objZip = new $zipClass();
// Retrieve OVERWRITE and CREATE constants from the instantiated zip class
// This method of accessing constant values from a dynamic class should work with all appropriate versions of PHP
$ro = new ReflectionObject($objZip);
$zipOverWrite = $ro->getConstant('OVERWRITE');
$zipCreate = $ro->getConstant('CREATE');
if (file_exists($pFilename)) {
unlink($pFilename);
}
// Try opening the ZIP file
if ($objZip->open($pFilename, $zipOverWrite) !== true) {
if ($objZip->open($pFilename, $zipCreate) !== true) {
throw new PHPExcel_Writer_Exception("Could not open " . $pFilename . " for writing.");
}
}
// Add [Content_Types].xml to ZIP file
$objZip->addFromString('[Content_Types].xml', $this->getWriterPart('ContentTypes')->writeContentTypes($this->_spreadSheet, $this->_includeCharts));
//if hasMacros, add the vbaProject.bin file, Certificate file(if exists)
if ($this->_spreadSheet->hasMacros()) {
$macrosCode = $this->_spreadSheet->getMacrosCode();
if (!is_null($macrosCode)) {
// we have the code ?
$objZip->addFromString('xl/vbaProject.bin', $macrosCode);
//allways in 'xl', allways named vbaProject.bin
if ($this->_spreadSheet->hasMacrosCertificate()) {
//signed macros ?
// Yes : add the certificate file and the related rels file
$objZip->addFromString('xl/vbaProjectSignature.bin', $this->_spreadSheet->getMacrosCertificate());
$objZip->addFromString('xl/_rels/vbaProject.bin.rels', $this->getWriterPart('RelsVBA')->writeVBARelationships($this->_spreadSheet));
}
}
}
//a custom UI in this workbook ? add it ("base" xml and additional objects (pictures) and rels)
if ($this->_spreadSheet->hasRibbon()) {
$tmpRibbonTarget = $this->_spreadSheet->getRibbonXMLData('target');
$objZip->addFromString($tmpRibbonTarget, $this->_spreadSheet->getRibbonXMLData('data'));
if ($this->_spreadSheet->hasRibbonBinObjects()) {
$tmpRootPath = dirname($tmpRibbonTarget) . '/';
$ribbonBinObjects = $this->_spreadSheet->getRibbonBinObjects('data');
//the files to write
foreach ($ribbonBinObjects as $aPath => $aContent) {
$objZip->addFromString($tmpRootPath . $aPath, $aContent);
}
//the rels for files
$objZip->addFromString($tmpRootPath . '_rels/' . basename($tmpRibbonTarget) . '.rels', $this->getWriterPart('RelsRibbonObjects')->writeRibbonRelationships($this->_spreadSheet));
}
}
// Add relationships to ZIP file
$objZip->addFromString('_rels/.rels', $this->getWriterPart('Rels')->writeRelationships($this->_spreadSheet));
$objZip->addFromString('xl/_rels/workbook.xml.rels', $this->getWriterPart('Rels')->writeWorkbookRelationships($this->_spreadSheet));
// Add document properties to ZIP file
$objZip->addFromString('docProps/app.xml', $this->getWriterPart('DocProps')->writeDocPropsApp($this->_spreadSheet));
$objZip->addFromString('docProps/core.xml', $this->getWriterPart('DocProps')->writeDocPropsCore($this->_spreadSheet));
$customPropertiesPart = $this->getWriterPart('DocProps')->writeDocPropsCustom($this->_spreadSheet);
if ($customPropertiesPart !== NULL) {
$objZip->addFromString('docProps/custom.xml', $customPropertiesPart);
}
// Add theme to ZIP file
$objZip->addFromString('xl/theme/theme1.xml', $this->getWriterPart('Theme')->writeTheme($this->_spreadSheet));
// Add string table to ZIP file
$objZip->addFromString('xl/sharedStrings.xml', $this->getWriterPart('StringTable')->writeStringTable($this->_stringTable));
// Add styles to ZIP file
$objZip->addFromString('xl/styles.xml', $this->getWriterPart('Style')->writeStyles($this->_spreadSheet));
// Add workbook to ZIP file
$objZip->addFromString('xl/workbook.xml', $this->getWriterPart('Workbook')->writeWorkbook($this->_spreadSheet, $this->_preCalculateFormulas));
$chartCount = 0;
// Add worksheets
for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) {
$objZip->addFromString('xl/worksheets/sheet' . ($i + 1) . '.xml', $this->getWriterPart('Worksheet')->writeWorksheet($this->_spreadSheet->getSheet($i), $this->_stringTable, $this->_includeCharts));
if ($this->_includeCharts) {
$charts = $this->_spreadSheet->getSheet($i)->getChartCollection();
if (count($charts) > 0) {
foreach ($charts as $chart) {
$objZip->addFromString('xl/charts/chart' . ($chartCount + 1) . '.xml', $this->getWriterPart('Chart')->writeChart($chart));
$chartCount++;
}
}
}
}
$chartRef1 = $chartRef2 = 0;
// Add worksheet relationships (drawings, ...)
for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) {
// Add relationships
$objZip->addFromString('xl/worksheets/_rels/sheet' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeWorksheetRelationships($this->_spreadSheet->getSheet($i), $i + 1, $this->_includeCharts));
$drawings = $this->_spreadSheet->getSheet($i)->getDrawingCollection();
$drawingCount = count($drawings);
if ($this->_includeCharts) {
$chartCount = $this->_spreadSheet->getSheet($i)->getChartCount();
}
// Add drawing and image relationship parts
if ($drawingCount > 0 || $chartCount > 0) {
// Drawing relationships
$objZip->addFromString('xl/drawings/_rels/drawing' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeDrawingRelationships($this->_spreadSheet->getSheet($i), $chartRef1, $this->_includeCharts));
// Drawings
$objZip->addFromString('xl/drawings/drawing' . ($i + 1) . '.xml', $this->getWriterPart('Drawing')->writeDrawings($this->_spreadSheet->getSheet($i), $chartRef2, $this->_includeCharts));
}
// Add comment relationship parts
if (count($this->_spreadSheet->getSheet($i)->getComments()) > 0) {
// VML Comments
$objZip->addFromString('xl/drawings/vmlDrawing' . ($i + 1) . '.vml', $this->getWriterPart('Comments')->writeVMLComments($this->_spreadSheet->getSheet($i)));
// Comments
$objZip->addFromString('xl/comments' . ($i + 1) . '.xml', $this->getWriterPart('Comments')->writeComments($this->_spreadSheet->getSheet($i)));
}
// Add header/footer relationship parts
if (count($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages()) > 0) {
// VML Drawings
$objZip->addFromString('xl/drawings/vmlDrawingHF' . ($i + 1) . '.vml', $this->getWriterPart('Drawing')->writeVMLHeaderFooterImages($this->_spreadSheet->getSheet($i)));
// VML Drawing relationships
$objZip->addFromString('xl/drawings/_rels/vmlDrawingHF' . ($i + 1) . '.vml.rels', $this->getWriterPart('Rels')->writeHeaderFooterDrawingRelationships($this->_spreadSheet->getSheet($i)));
// Media
foreach ($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages() as $image) {
$objZip->addFromString('xl/media/' . $image->getIndexedFilename(), file_get_contents($image->getPath()));
}
}
}
// Add media
for ($i = 0; $i < $this->getDrawingHashTable()->count(); ++$i) {
if ($this->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_Drawing) {
$imageContents = null;
$imagePath = $this->getDrawingHashTable()->getByIndex($i)->getPath();
if (strpos($imagePath, 'zip://') !== false) {
$imagePath = substr($imagePath, 6);
$imagePathSplitted = explode('#', $imagePath);
$imageZip = new ZipArchive();
$imageZip->open($imagePathSplitted[0]);
$imageContents = $imageZip->getFromName($imagePathSplitted[1]);
$imageZip->close();
unset($imageZip);
} else {
$imageContents = file_get_contents($imagePath);
}
$objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents);
} else {
if ($this->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_MemoryDrawing) {
ob_start();
call_user_func($this->getDrawingHashTable()->getByIndex($i)->getRenderingFunction(), $this->getDrawingHashTable()->getByIndex($i)->getImageResource());
$imageContents = ob_get_contents();
ob_end_clean();
$objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents);
}
}
}
PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType);
PHPExcel_Calculation::getInstance($this->_spreadSheet)->getDebugLog()->setWriteDebugLog($saveDebugLog);
// Close file
if ($objZip->close() === false) {
throw new PHPExcel_Writer_Exception("Could not close zip file {$pFilename}.");
}
// If a temporary file was used, copy it to the correct file stream
if ($originalFilename != $pFilename) {
if (copy($pFilename, $originalFilename) === false) {
throw new PHPExcel_Writer_Exception("Could not copy temporary zip file {$pFilename} to {$originalFilename}.");
}
@unlink($pFilename);
}
} else {
throw new PHPExcel_Writer_Exception("PHPExcel object unassigned.");
}
}
/**
* Save PHPExcel to file
*
* @param string $pFilename
*
* @throws PHPExcel_Writer_Exception
*/
public function save($pFilename = null)
{
// garbage collect
$this->_phpExcel->garbageCollect();
$saveDebugLog = PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->getWriteDebugLog();
PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog(false);
$saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType();
PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
// initialize colors array
$this->_colors = array();
// Initialise workbook writer
$this->_writerWorkbook = new PHPExcel_Writer_Excel5_Workbook($this->_phpExcel, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser);
// Initialise worksheet writers
$countSheets = $this->_phpExcel->getSheetCount();
for ($i = 0; $i < $countSheets; ++$i) {
$this->_writerWorksheets[$i] = new PHPExcel_Writer_Excel5_Worksheet($this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_preCalculateFormulas, $this->_phpExcel->getSheet($i));
}
// build Escher objects. Escher objects for workbooks needs to be build before Escher object for workbook.
$this->_buildWorksheetEschers();
$this->_buildWorkbookEscher();
// add 15 identical cell style Xfs
// for now, we use the first cellXf instead of cellStyleXf
$cellXfCollection = $this->_phpExcel->getCellXfCollection();
for ($i = 0; $i < 15; ++$i) {
$this->_writerWorkbook->addXfWriter($cellXfCollection[0], true);
}
// add all the cell Xfs
foreach ($this->_phpExcel->getCellXfCollection() as $style) {
$this->_writerWorkbook->addXfWriter($style, false);
}
// add fonts from rich text eleemnts
for ($i = 0; $i < $countSheets; ++$i) {
foreach ($this->_writerWorksheets[$i]->_phpSheet->getCellCollection() as $cellID) {
$cell = $this->_writerWorksheets[$i]->_phpSheet->getCell($cellID);
$cVal = $cell->getValue();
if ($cVal instanceof PHPExcel_RichText) {
$elements = $cVal->getRichTextElements();
foreach ($elements as $element) {
if ($element instanceof PHPExcel_RichText_Run) {
$font = $element->getFont();
$this->_writerWorksheets[$i]->_fntHashIndex[$font->getHashCode()] = $this->_writerWorkbook->_addFont($font);
}
}
}
}
}
// initialize OLE file
$workbookStreamName = 'Workbook';
$OLE = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs($workbookStreamName));
// Write the worksheet streams before the global workbook stream,
// because the byte sizes of these are needed in the global workbook stream
$worksheetSizes = array();
for ($i = 0; $i < $countSheets; ++$i) {
$this->_writerWorksheets[$i]->close();
$worksheetSizes[] = $this->_writerWorksheets[$i]->_datasize;
}
// add binary data for global workbook stream
$OLE->append($this->_writerWorkbook->writeWorkbook($worksheetSizes));
// add binary data for sheet streams
for ($i = 0; $i < $countSheets; ++$i) {
$OLE->append($this->_writerWorksheets[$i]->getData());
}
$this->_documentSummaryInformation = $this->_writeDocumentSummaryInformation();
// initialize OLE Document Summary Information
if (isset($this->_documentSummaryInformation) && !empty($this->_documentSummaryInformation)) {
$OLE_DocumentSummaryInformation = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs(chr(5) . 'DocumentSummaryInformation'));
$OLE_DocumentSummaryInformation->append($this->_documentSummaryInformation);
}
$this->_summaryInformation = $this->_writeSummaryInformation();
// initialize OLE Summary Information
if (isset($this->_summaryInformation) && !empty($this->_summaryInformation)) {
$OLE_SummaryInformation = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs(chr(5) . 'SummaryInformation'));
$OLE_SummaryInformation->append($this->_summaryInformation);
}
// define OLE Parts
$arrRootData = array($OLE);
// initialize OLE Properties file
if (isset($OLE_SummaryInformation)) {
$arrRootData[] = $OLE_SummaryInformation;
}
// initialize OLE Extended Properties file
if (isset($OLE_DocumentSummaryInformation)) {
$arrRootData[] = $OLE_DocumentSummaryInformation;
}
$root = new PHPExcel_Shared_OLE_PPS_Root(time(), time(), $arrRootData);
// save the OLE file
$res = $root->save($pFilename);
PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType);
PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog($saveDebugLog);
}
/**
* Write docProps/app.xml to XML format
*
* @param PHPExcel $pPHPExcel
* @return string XML Output
* @throws Exception
*/
public function writeDocPropsApp(PHPExcel $pPHPExcel = null)
{
// Create XML writer
$objWriter = null;
if ($this->getParentWriter()->getUseDiskCaching()) {
$objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory());
} else {
$objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_MEMORY);
}
// XML header
$objWriter->startDocument('1.0', 'UTF-8', 'yes');
// Properties
$objWriter->startElement('Properties');
$objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/officeDocument/2006/extended-properties');
$objWriter->writeAttribute('xmlns:vt', 'http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes');
// Application
$objWriter->writeElement('Application', 'Microsoft Excel');
// DocSecurity
$objWriter->writeElement('DocSecurity', '0');
// ScaleCrop
$objWriter->writeElement('ScaleCrop', 'false');
// HeadingPairs
$objWriter->startElement('HeadingPairs');
// Vector
$objWriter->startElement('vt:vector');
$objWriter->writeAttribute('size', '2');
$objWriter->writeAttribute('baseType', 'variant');
// Variant
$objWriter->startElement('vt:variant');
$objWriter->writeElement('vt:lpstr', 'Worksheets');
$objWriter->endElement();
// Variant
$objWriter->startElement('vt:variant');
$objWriter->writeElement('vt:i4', $pPHPExcel->getSheetCount());
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
// TitlesOfParts
$objWriter->startElement('TitlesOfParts');
// Vector
$objWriter->startElement('vt:vector');
$objWriter->writeAttribute('size', $pPHPExcel->getSheetCount());
$objWriter->writeAttribute('baseType', 'lpstr');
$sheetCount = $pPHPExcel->getSheetCount();
for ($i = 0; $i < $sheetCount; ++$i) {
$objWriter->writeElement('vt:lpstr', $pPHPExcel->getSheet($i)->getTitle());
}
$objWriter->endElement();
$objWriter->endElement();
// Company
$objWriter->writeElement('Company', $pPHPExcel->getProperties()->getCompany());
// LinksUpToDate
$objWriter->writeElement('LinksUpToDate', 'false');
// SharedDoc
$objWriter->writeElement('SharedDoc', 'false');
// HyperlinksChanged
$objWriter->writeElement('HyperlinksChanged', 'false');
// AppVersion
$objWriter->writeElement('AppVersion', '12.0000');
$objWriter->endElement();
// Return
return $objWriter->getData();
}
/**
* Get an array of all drawings
*
* @param PHPExcel $pPHPExcel
* @return PHPExcel_Worksheet_Drawing[] All drawings in PHPExcel
* @throws PHPExcel_Writer_Exception
*/
public function allDrawings(PHPExcel $pPHPExcel = null)
{
// Get an array of all drawings
$aDrawings = array();
// Loop through PHPExcel
$sheetCount = $pPHPExcel->getSheetCount();
for ($i = 0; $i < $sheetCount; ++$i) {
// Loop through images and add to array
$iterator = $pPHPExcel->getSheet($i)->getDrawingCollection()->getIterator();
while ($iterator->valid()) {
$aDrawings[] = $iterator->current();
$iterator->next();
}
}
return $aDrawings;
}
/**
* Write sheets
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel $pPHPExcel
* @throws PHPExcel_Writer_Exception
*/
private function _writeSheets(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel $pPHPExcel = null)
{
// Write sheets
$objWriter->startElement('sheets');
$sheetCount = $pPHPExcel->getSheetCount();
for ($i = 0; $i < $sheetCount; ++$i) {
// sheet
$this->_writeSheet($objWriter, $pPHPExcel->getSheet($i)->getTitle(), $i + 1, $i + 1 + 3, $pPHPExcel->getSheet($i)->getSheetState());
}
$objWriter->endElement();
}
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param PHPExcel $objPHPExcel
* @return PHPExcel
* @throws Exception
*/
public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
// Create new PHPExcel
while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) {
$objPHPExcel->createSheet();
}
$objPHPExcel->setActiveSheetIndex($this->_sheetIndex);
// Create a new DOM object
$dom = new domDocument();
// Load the HTML file into the DOM object
$loaded = $dom->loadHTMLFile($pFilename);
if ($loaded === false) {
throw new Exception('Failed to load ', $pFilename, ' as a DOM Document');
}
// Discard white space
$dom->preserveWhiteSpace = false;
$row = 0;
$column = 'A';
$content = '';
$this->_processDomElement($dom, $objPHPExcel->getActiveSheet(), $row, $column, $content);
echo '<hr />';
var_dump($this->_dataArray);
// Return
return $objPHPExcel;
}
/**
* More PHPExcel_Worksheet instances available?
*
* @return boolean
*/
public function valid()
{
return $this->_position < $this->_subject->getSheetCount();
}
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param PHPExcel $objPHPExcel
* @return PHPExcel
* @throws PHPExcel_Reader_Exception
*/
public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
{
$lineEnding = ini_get('auto_detect_line_endings');
ini_set('auto_detect_line_endings', true);
// Open file
$this->_openFile($pFilename);
if (!$this->_isValidFormat()) {
fclose($this->_fileHandle);
throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid Spreadsheet file.");
}
$fileHandle = $this->_fileHandle;
// Skip BOM, if any
$this->_skipBOM();
// Create new PHPExcel object
while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) {
$objPHPExcel->createSheet();
}
$sheet = $objPHPExcel->setActiveSheetIndex($this->_sheetIndex);
$escapeEnclosures = array("\\" . $this->_enclosure, $this->_enclosure . $this->_enclosure);
// Set our starting row based on whether we're in contiguous mode or not
$currentRow = 1;
if ($this->_contiguous) {
$currentRow = $this->_contiguousRow == -1 ? $sheet->getHighestRow() : $this->_contiguousRow;
}
// Loop through each line of the file in turn
while (($rowData = fgetcsv($fileHandle, 0, $this->_delimiter, $this->_enclosure)) !== FALSE) {
$columnLetter = 'A';
foreach ($rowData as $rowDatum) {
if ($rowDatum != '' && $this->_readFilter->readCell($columnLetter, $currentRow)) {
// Unescape enclosures
$rowDatum = str_replace($escapeEnclosures, $this->_enclosure, $rowDatum);
// Convert encoding if necessary
if ($this->_inputEncoding !== 'UTF-8') {
$rowDatum = PHPExcel_Shared_String::ConvertEncoding($rowDatum, 'UTF-8', $this->_inputEncoding);
}
// Set cell value
$sheet->getCell($columnLetter . $currentRow)->setValue($rowDatum);
}
++$columnLetter;
}
++$currentRow;
}
// Close file
fclose($fileHandle);
if ($this->_contiguous) {
$this->_contiguousRow = $currentRow;
}
ini_set('auto_detect_line_endings', $lineEnding);
// Return
return $objPHPExcel;
}
