/**
* Read ClientAnchor record. This record holds information about where the shape is anchored in worksheet
*/
private function _readClientAnchor()
{
$length = $this->_GetInt4d($this->_data, $this->_pos + 4);
$recordData = substr($this->_data, $this->_pos + 8, $length);
// move stream pointer to next record
$this->_pos += 8 + $length;
// offset: 2; size: 2; upper-left corner column index (0-based)
$c1 = $this->_GetInt2d($recordData, 2);
// offset: 4; size: 2; upper-left corner horizontal offset in 1/1024 of column width
$startOffsetX = $this->_GetInt2d($recordData, 4);
// offset: 6; size: 2; upper-left corner row index (0-based)
$r1 = $this->_GetInt2d($recordData, 6);
// offset: 8; size: 2; upper-left corner vertical offset in 1/256 of row height
$startOffsetY = $this->_GetInt2d($recordData, 8);
// offset: 10; size: 2; bottom-right corner column index (0-based)
$c2 = $this->_GetInt2d($recordData, 10);
// offset: 12; size: 2; bottom-right corner horizontal offset in 1/1024 of column width
$endOffsetX = $this->_GetInt2d($recordData, 12);
// offset: 14; size: 2; bottom-right corner row index (0-based)
$r2 = $this->_GetInt2d($recordData, 14);
// offset: 16; size: 2; bottom-right corner vertical offset in 1/256 of row height
$endOffsetY = $this->_GetInt2d($recordData, 16);
// set the start coordinates
$this->_object->setStartCoordinates(Cell::stringFromColumnIndex($c1) . ($r1 + 1));
// set the start offsetX
$this->_object->setStartOffsetX($startOffsetX);
// set the start offsetY
$this->_object->setStartOffsetY($startOffsetY);
// set the end coordinates
$this->_object->setEndCoordinates(Cell::stringFromColumnIndex($c2) . ($r2 + 1));
// set the end offsetX
$this->_object->setEndOffsetX($endOffsetX);
// set the end offsetY
$this->_object->setEndOffsetY($endOffsetY);
}
/**
* 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[] = 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 = 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 = 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 Style();
$this->_readStyle($objStyle, $style);
$excel->addCellXf($objStyle);
}
foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
$numFmt = 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 = 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 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 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 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) {
Shared_Date::setExcelCalendar(Shared_Date::CALENDAR_WINDOWS_1900);
if (isset($xmlWorkbook->workbookPr['date1904'])) {
$date1904 = (string) $xmlWorkbook->workbookPr['date1904'];
if ($date1904 == "true" || $date1904 == "1") {
Shared_Date::setExcelCalendar(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((double) $xmlSheet->sheetFormatPr['defaultRowHeight']);
}
if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
$docSheet->getDefaultColumnDimension()->setWidth((double) $xmlSheet->sheetFormatPr['defaultColWidth']);
}
}
if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
foreach ($xmlSheet->cols->col as $col) {
for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
if ($col["style"] && !$this->_readDataOnly) {
$docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"]));
}
if ($col["bestFit"]) {
//$docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setAutoSize(true);
}
if ($col["hidden"]) {
$docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setVisible(false);
}
if ($col["collapsed"]) {
$docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setCollapsed(true);
}
if ($col["outlineLevel"] > 0) {
$docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
}
$docSheet->getColumnDimension(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 = 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 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 == (double) $value) {
$value = (double) $value;
} elseif ($value == (double) $value) {
$value = (double) $value;
}
}
// Rich text?
if ($value instanceof 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"] == Style_Conditional::CONDITION_NONE || (string) $cfRule["type"] == Style_Conditional::CONDITION_CELLIS || (string) $cfRule["type"] == Style_Conditional::CONDITION_CONTAINSTEXT || (string) $cfRule["type"] == 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 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 = 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']}", Worksheet::BREAK_ROW);
}
}
}
if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
foreach ($xmlSheet->colBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak(Cell::stringFromColumnIndex($brk["id"]) . "1", 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 = 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 (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 = 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 = 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 Worksheet_HeaderFooterDrawing();
if (isset($imageData['title'])) {
$hfImages[(string) $shape['id']]->setName((string) $imageData['title']);
}
$hfImages[(string) $shape['id']]->setPath("zip://{$pFilename}#" . $drawings[(string) $imageData['relid']], false);
$hfImages[(string) $shape['id']]->setResizeProportional(false);
$hfImages[(string) $shape['id']]->setWidth($style['width']);
$hfImages[(string) $shape['id']]->setHeight($style['height']);
$hfImages[(string) $shape['id']]->setOffsetX($style['margin-left']);
$hfImages[(string) $shape['id']]->setOffsetY($style['margin-top']);
$hfImages[(string) $shape['id']]->setResizeProportional(true);
}
$docSheet->getHeaderFooter()->setImages($hfImages);
}
}
}
}
// TODO: Make sure drawings and graph are loaded differently!
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($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 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(Cell::stringFromColumnIndex($oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1));
$objDrawing->setOffsetX(Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff));
$objDrawing->setOffsetY(Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx")));
$objDrawing->setHeight(Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(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 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(Cell::stringFromColumnIndex($twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
$objDrawing->setOffsetX(Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff));
$objDrawing->setOffsetY(Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx")));
$objDrawing->setHeight(Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(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]));
} 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 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 = 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 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;
}
/**
* Reads a cell range address in BIFF8 for shared formulas. Uses positive and negative values for row and column
* to indicate offsets from a base cell
* section 3.3.4
*
* @param string $subData
* @param string $baseCell Base cell
* @return string Cell range address
*/
private function _readBIFF8CellRangeAddressB($subData, $baseCell = 'A1')
{
list($baseCol, $baseRow) = Cell::coordinateFromString($baseCell);
$baseCol = Cell::columnIndexFromString($baseCol) - 1;
// TODO: if cell range is just a single cell, should this funciton
// not just return e.g. 'A1' and not 'A1:A1' ?
// offset: 0; size: 2; first row
$frIndex = $this->_GetInt2d($subData, 0);
// adjust below
// offset: 2; size: 2; relative index to first row (0... 65535) should be treated as offset (-32768... 32767)
$lrIndex = $this->_GetInt2d($subData, 2);
// adjust below
// offset: 4; size: 2; first column with relative/absolute flags
// bit: 7-0; mask 0x00FF; column index
$fcIndex = 0xff & $this->_GetInt2d($subData, 4);
// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
if (!(0x4000 & $this->_GetInt2d($subData, 4))) {
// absolute column index
$fc = Cell::stringFromColumnIndex($fcIndex);
$fc = '$' . $fc;
} else {
// column offset
$fcIndex = $fcIndex <= 127 ? $fcIndex : $fcIndex - 256;
$fc = Cell::stringFromColumnIndex($baseCol + $fcIndex);
}
// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
if (!(0x8000 & $this->_GetInt2d($subData, 4))) {
// absolute row index
$fr = $frIndex + 1;
$fr = '$' . $fr;
} else {
// row offset
$frIndex = $frIndex <= 32767 ? $frIndex : $frIndex - 65536;
$fr = $baseRow + $frIndex;
}
// offset: 6; size: 2; last column with relative/absolute flags
// bit: 7-0; mask 0x00FF; column index
$lcIndex = 0xff & $this->_GetInt2d($subData, 6);
$lcIndex = $lcIndex <= 127 ? $lcIndex : $lcIndex - 256;
$lc = Cell::stringFromColumnIndex($baseCol + $lcIndex);
// bit: 14; mask 0x4000; (1 = relative column index, 0 = absolute column index)
if (!(0x4000 & $this->_GetInt2d($subData, 6))) {
// absolute column index
$lc = Cell::stringFromColumnIndex($lcIndex);
$lc = '$' . $lc;
} else {
// column offset
$lcIndex = $lcIndex <= 127 ? $lcIndex : $lcIndex - 256;
$lc = Cell::stringFromColumnIndex($baseCol + $lcIndex);
}
// bit: 15; mask 0x8000; (1 = relative row index, 0 = absolute row index)
if (!(0x8000 & $this->_GetInt2d($subData, 6))) {
// absolute row index
$lr = $lrIndex + 1;
$lr = '$' . $lr;
} else {
// row offset
$lrIndex = $lrIndex <= 32767 ? $lrIndex : $lrIndex - 65536;
$lr = $baseRow + $lrIndex;
}
return "{$fc}{$fr}:{$lc}{$lr}";
}
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @access public
* @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);
// Open file
$fileHandle = fopen($pFilename, 'r');
if ($fileHandle === false) {
throw new Exception("Could not open file {$pFilename} for reading.");
}
// Skip BOM, if any
switch ($this->_inputEncoding) {
case 'UTF-8':
fgets($fileHandle, 4) == "" ? fseek($fileHandle, 3) : fseek($fileHandle, 0);
break;
default:
break;
}
// Loop through file
$currentRow = 0;
$rowData = array();
while (($rowData = fgetcsv($fileHandle, 0, $this->_delimiter, $this->_enclosure)) !== FALSE) {
++$currentRow;
$rowDataCount = count($rowData);
for ($i = 0; $i < $rowDataCount; ++$i) {
$columnLetter = Cell::stringFromColumnIndex($i);
if ($rowData[$i] != '' && $this->_readFilter->readCell($columnLetter, $currentRow)) {
// Unescape enclosures
$rowData[$i] = str_replace("\\" . $this->_enclosure, $this->_enclosure, $rowData[$i]);
$rowData[$i] = str_replace($this->_enclosure . $this->_enclosure, $this->_enclosure, $rowData[$i]);
// Convert encoding if necessary
if ($this->_inputEncoding !== 'UTF-8') {
$rowData[$i] = Shared_String::ConvertEncoding($rowData[$i], 'UTF-8', $this->_inputEncoding);
}
// Set cell value
$objPHPExcel->getActiveSheet()->getCell($columnLetter . $currentRow)->setValue($rowData[$i]);
}
}
}
// Close file
fclose($fileHandle);
// Return
return $objPHPExcel;
}
/**
* Apply styles from array
*
* <code>
* $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray(
* array(
* 'font' => array(
* 'name' => 'Arial',
* 'bold' => true,
* 'italic' => false,
* 'underline' => \PHPExcel\Style\Font::UNDERLINE_DOUBLE,
* 'strike' => false,
* 'color' => array(
* 'rgb' => '808080'
* )
* ),
* 'borders' => array(
* 'bottom' => array(
* 'style' => \PHPExcel\Style\Border::BORDER_DASHDOT,
* 'color' => array(
* 'rgb' => '808080'
* )
* ),
* 'top' => array(
* 'style' => \PHPExcel\Style\Border::BORDER_DASHDOT,
* 'color' => array(
* 'rgb' => '808080'
* )
* )
* ),
* 'quotePrefix' => true
* )
* );
* </code>
*
* @param array $pStyles Array containing style information
* @param boolean $pAdvanced Advanced mode for setting borders.
* @throws Exception
* @return Style
*/
public function applyFromArray($pStyles = null, $pAdvanced = true)
{
if (is_array($pStyles)) {
if ($this->isSupervisor) {
$pRange = $this->getSelectedCells();
// Uppercase coordinate
$pRange = strtoupper($pRange);
// Is it a cell range or a single cell?
if (strpos($pRange, ':') === false) {
$rangeA = $pRange;
$rangeB = $pRange;
} else {
list($rangeA, $rangeB) = explode(':', $pRange);
}
// Calculate range outer borders
$rangeStart = Cell::coordinateFromString($rangeA);
$rangeEnd = Cell::coordinateFromString($rangeB);
// Translate column into index
$rangeStart[0] = Cell::columnIndexFromString($rangeStart[0]) - 1;
$rangeEnd[0] = Cell::columnIndexFromString($rangeEnd[0]) - 1;
// Make sure we can loop upwards on rows and columns
if ($rangeStart[0] > $rangeEnd[0] && $rangeStart[1] > $rangeEnd[1]) {
$tmp = $rangeStart;
$rangeStart = $rangeEnd;
$rangeEnd = $tmp;
}
// ADVANCED MODE:
if ($pAdvanced && isset($pStyles['borders'])) {
// 'allborders' is a shorthand property for 'outline' and 'inside' and
// it applies to components that have not been set explicitly
if (isset($pStyles['borders']['allborders'])) {
foreach (array('outline', 'inside') as $component) {
if (!isset($pStyles['borders'][$component])) {
$pStyles['borders'][$component] = $pStyles['borders']['allborders'];
}
}
unset($pStyles['borders']['allborders']);
// not needed any more
}
// 'outline' is a shorthand property for 'top', 'right', 'bottom', 'left'
// it applies to components that have not been set explicitly
if (isset($pStyles['borders']['outline'])) {
foreach (array('top', 'right', 'bottom', 'left') as $component) {
if (!isset($pStyles['borders'][$component])) {
$pStyles['borders'][$component] = $pStyles['borders']['outline'];
}
}
unset($pStyles['borders']['outline']);
// not needed any more
}
// 'inside' is a shorthand property for 'vertical' and 'horizontal'
// it applies to components that have not been set explicitly
if (isset($pStyles['borders']['inside'])) {
foreach (array('vertical', 'horizontal') as $component) {
if (!isset($pStyles['borders'][$component])) {
$pStyles['borders'][$component] = $pStyles['borders']['inside'];
}
}
unset($pStyles['borders']['inside']);
// not needed any more
}
// width and height characteristics of selection, 1, 2, or 3 (for 3 or more)
$xMax = min($rangeEnd[0] - $rangeStart[0] + 1, 3);
$yMax = min($rangeEnd[1] - $rangeStart[1] + 1, 3);
// loop through up to 3 x 3 = 9 regions
for ($x = 1; $x <= $xMax; ++$x) {
// start column index for region
$colStart = $x == 3 ? Cell::stringFromColumnIndex($rangeEnd[0]) : Cell::stringFromColumnIndex($rangeStart[0] + $x - 1);
// end column index for region
$colEnd = $x == 1 ? Cell::stringFromColumnIndex($rangeStart[0]) : Cell::stringFromColumnIndex($rangeEnd[0] - $xMax + $x);
for ($y = 1; $y <= $yMax; ++$y) {
// which edges are touching the region
$edges = array();
if ($x == 1) {
// are we at left edge
$edges[] = 'left';
}
if ($x == $xMax) {
// are we at right edge
$edges[] = 'right';
}
if ($y == 1) {
// are we at top edge?
$edges[] = 'top';
}
if ($y == $yMax) {
// are we at bottom edge?
$edges[] = 'bottom';
}
// start row index for region
$rowStart = $y == 3 ? $rangeEnd[1] : $rangeStart[1] + $y - 1;
// end row index for region
$rowEnd = $y == 1 ? $rangeStart[1] : $rangeEnd[1] - $yMax + $y;
// build range for region
$range = $colStart . $rowStart . ':' . $colEnd . $rowEnd;
// retrieve relevant style array for region
$regionStyles = $pStyles;
unset($regionStyles['borders']['inside']);
// what are the inner edges of the region when looking at the selection
$innerEdges = array_diff(array('top', 'right', 'bottom', 'left'), $edges);
// inner edges that are not touching the region should take the 'inside' border properties if they have been set
foreach ($innerEdges as $innerEdge) {
switch ($innerEdge) {
case 'top':
case 'bottom':
// should pick up 'horizontal' border property if set
if (isset($pStyles['borders']['horizontal'])) {
$regionStyles['borders'][$innerEdge] = $pStyles['borders']['horizontal'];
} else {
unset($regionStyles['borders'][$innerEdge]);
}
break;
case 'left':
case 'right':
// should pick up 'vertical' border property if set
if (isset($pStyles['borders']['vertical'])) {
$regionStyles['borders'][$innerEdge] = $pStyles['borders']['vertical'];
} else {
unset($regionStyles['borders'][$innerEdge]);
}
break;
}
}
// apply region style to region by calling applyFromArray() in simple mode
$this->getActiveSheet()->getStyle($range)->applyFromArray($regionStyles, false);
}
}
return $this;
}
// SIMPLE MODE:
// Selection type, inspect
if (preg_match('/^[A-Z]+1:[A-Z]+1048576$/', $pRange)) {
$selectionType = 'COLUMN';
} elseif (preg_match('/^A[0-9]+:XFD[0-9]+$/', $pRange)) {
$selectionType = 'ROW';
} else {
$selectionType = 'CELL';
}
// First loop through columns, rows, or cells to find out which styles are affected by this operation
switch ($selectionType) {
case 'COLUMN':
$oldXfIndexes = array();
for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
$oldXfIndexes[$this->getActiveSheet()->getColumnDimensionByColumn($col)->getXfIndex()] = true;
}
break;
case 'ROW':
$oldXfIndexes = array();
for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
if ($this->getActiveSheet()->getRowDimension($row)->getXfIndex() == null) {
$oldXfIndexes[0] = true;
// row without explicit style should be formatted based on default style
} else {
$oldXfIndexes[$this->getActiveSheet()->getRowDimension($row)->getXfIndex()] = true;
}
}
break;
case 'CELL':
$oldXfIndexes = array();
for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
$oldXfIndexes[$this->getActiveSheet()->getCellByColumnAndRow($col, $row)->getXfIndex()] = true;
}
}
break;
}
// clone each of the affected styles, apply the style array, and add the new styles to the workbook
$workbook = $this->getActiveSheet()->getParent();
foreach ($oldXfIndexes as $oldXfIndex => $dummy) {
$style = $workbook->getCellXfByIndex($oldXfIndex);
$newStyle = clone $style;
$newStyle->applyFromArray($pStyles);
if ($existingStyle = $workbook->getCellXfByHashCode($newStyle->getHashCode())) {
// there is already such cell Xf in our collection
$newXfIndexes[$oldXfIndex] = $existingStyle->getIndex();
} else {
// we don't have such a cell Xf, need to add
$workbook->addCellXf($newStyle);
$newXfIndexes[$oldXfIndex] = $newStyle->getIndex();
}
}
// Loop through columns, rows, or cells again and update the XF index
switch ($selectionType) {
case 'COLUMN':
for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
$columnDimension = $this->getActiveSheet()->getColumnDimensionByColumn($col);
$oldXfIndex = $columnDimension->getXfIndex();
$columnDimension->setXfIndex($newXfIndexes[$oldXfIndex]);
}
break;
case 'ROW':
for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
$rowDimension = $this->getActiveSheet()->getRowDimension($row);
$oldXfIndex = $rowDimension->getXfIndex() === null ? 0 : $rowDimension->getXfIndex();
// row without explicit style should be formatted based on default style
$rowDimension->setXfIndex($newXfIndexes[$oldXfIndex]);
}
break;
case 'CELL':
for ($col = $rangeStart[0]; $col <= $rangeEnd[0]; ++$col) {
for ($row = $rangeStart[1]; $row <= $rangeEnd[1]; ++$row) {
$cell = $this->getActiveSheet()->getCellByColumnAndRow($col, $row);
$oldXfIndex = $cell->getXfIndex();
$cell->setXfIndex($newXfIndexes[$oldXfIndex]);
}
}
break;
}
} else {
// not a supervisor, just apply the style array directly on style object
if (array_key_exists('fill', $pStyles)) {
$this->getFill()->applyFromArray($pStyles['fill']);
}
if (array_key_exists('font', $pStyles)) {
$this->getFont()->applyFromArray($pStyles['font']);
}
if (array_key_exists('borders', $pStyles)) {
$this->getBorders()->applyFromArray($pStyles['borders']);
}
if (array_key_exists('alignment', $pStyles)) {
$this->getAlignment()->applyFromArray($pStyles['alignment']);
}
if (array_key_exists('numberformat', $pStyles)) {
$this->getNumberFormat()->applyFromArray($pStyles['numberformat']);
}
if (array_key_exists('protection', $pStyles)) {
$this->getProtection()->applyFromArray($pStyles['protection']);
}
if (array_key_exists('quotePrefix', $pStyles)) {
$this->quotePrefix = $pStyles['quotePrefix'];
}
}
} else {
throw new Exception("Invalid style array passed.");
}
return $this;
}
/**
* Update single cell reference
*
* @param string $pCellReference Single cell reference
* @param int $pBefore Insert before this one
* @param int $pNumCols Number of columns to increment
* @param int $pNumRows Number of rows to increment
* @return string Updated cell reference
* @throws Exception
*/
private function updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
{
if (strpos($pCellReference, ':') === false && strpos($pCellReference, ',') === false) {
// Get coordinates of $pBefore
list($beforeColumn, $beforeRow) = Cell::coordinateFromString($pBefore);
// Get coordinates of $pCellReference
list($newColumn, $newRow) = Cell::coordinateFromString($pCellReference);
// Verify which parts should be updated
$updateColumn = $newColumn[0] != '$' && $beforeColumn[0] != '$' && Cell::columnIndexFromString($newColumn) >= Cell::columnIndexFromString($beforeColumn);
$updateRow = $newRow[0] != '$' && $beforeRow[0] != '$' && $newRow >= $beforeRow;
// Create new column reference
if ($updateColumn) {
$newColumn = Cell::stringFromColumnIndex(Cell::columnIndexFromString($newColumn) - 1 + $pNumCols);
}
// Create new row reference
if ($updateRow) {
$newRow = $newRow + $pNumRows;
}
// Return new reference
return $newColumn . $newRow;
} else {
throw new Exception("Only single cell references may be passed to this method.");
}
}
/**
* Accepts a range, returning it as a range that falls within the current highest row and column of the worksheet
*
* @param string $range
* @return string Adjusted range value
*/
public function shrinkRangeToFit($range)
{
$maxCol = $this->getHighestColumn();
$maxRow = $this->getHighestRow();
$maxCol = Cell::columnIndexFromString($maxCol);
$rangeBlocks = explode(' ', $range);
foreach ($rangeBlocks as &$rangeSet) {
$rangeBoundaries = Cell::getRangeBoundaries($rangeSet);
if (Cell::columnIndexFromString($rangeBoundaries[0][0]) > $maxCol) {
$rangeBoundaries[0][0] = Cell::stringFromColumnIndex($maxCol);
}
if ($rangeBoundaries[0][1] > $maxRow) {
$rangeBoundaries[0][1] = $maxRow;
}
if (Cell::columnIndexFromString($rangeBoundaries[1][0]) > $maxCol) {
$rangeBoundaries[1][0] = Cell::stringFromColumnIndex($maxCol);
}
if ($rangeBoundaries[1][1] > $maxRow) {
$rangeBoundaries[1][1] = $maxRow;
}
$rangeSet = $rangeBoundaries[0][0] . $rangeBoundaries[0][1] . ':' . $rangeBoundaries[1][0] . $rangeBoundaries[1][1];
}
unset($rangeSet);
$stRange = implode(' ', $rangeBlocks);
return $stRange;
}
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param PHPExcel $objPHPExcel
* @return PHPExcel
* @throws Exception
*/
public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
{
$fromFormats = array('\\-', '\\ ');
$toFormats = array('-', ' ');
$underlineStyles = array(Style_Font::UNDERLINE_NONE, Style_Font::UNDERLINE_DOUBLE, Style_Font::UNDERLINE_DOUBLEACCOUNTING, Style_Font::UNDERLINE_SINGLE, Style_Font::UNDERLINE_SINGLEACCOUNTING);
$verticalAlignmentStyles = array(Style_Alignment::VERTICAL_BOTTOM, Style_Alignment::VERTICAL_TOP, Style_Alignment::VERTICAL_CENTER, Style_Alignment::VERTICAL_JUSTIFY);
$horizontalAlignmentStyles = array(Style_Alignment::HORIZONTAL_GENERAL, Style_Alignment::HORIZONTAL_LEFT, Style_Alignment::HORIZONTAL_RIGHT, Style_Alignment::HORIZONTAL_CENTER, Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS, Style_Alignment::HORIZONTAL_JUSTIFY);
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
$xml = simplexml_load_file($pFilename);
$namespaces = $xml->getNamespaces(true);
// echo '<pre>';
// print_r($namespaces);
// echo '</pre><hr />';
//
// echo '<pre>';
// print_r($xml);
// echo '</pre><hr />';
//
$docProps = $objPHPExcel->getProperties();
foreach ($xml->DocumentProperties[0] as $propertyName => $propertyValue) {
switch ($propertyName) {
case 'Title':
$docProps->setTitle($propertyValue);
break;
case 'Subject':
$docProps->setSubject($propertyValue);
break;
case 'Author':
$docProps->setCreator($propertyValue);
break;
case 'Created':
$creationDate = strtotime($propertyValue);
$docProps->setCreated($creationDate);
break;
case 'LastAuthor':
$docProps->setLastModifiedBy($propertyValue);
break;
case 'Company':
$docProps->setCompany($propertyValue);
break;
case 'Category':
$docProps->setCategory($propertyValue);
break;
case 'Keywords':
$docProps->setKeywords($propertyValue);
break;
case 'Description':
$docProps->setDescription($propertyValue);
break;
}
}
foreach ($xml->Styles[0] as $style) {
$style_ss = $style->attributes($namespaces['ss']);
$styleID = (string) $style_ss['ID'];
// echo 'Style ID = '.$styleID.'<br />';
if ($styleID == 'Default') {
$this->_styles['Default'] = array();
} else {
$this->_styles[$styleID] = $this->_styles['Default'];
}
foreach ($style as $styleType => $styleData) {
$styleAttributes = $styleData->attributes($namespaces['ss']);
// echo $styleType.'<br />';
switch ($styleType) {
case 'Alignment':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
$styleAttributeValue = (string) $styleAttributeValue;
switch ($styleAttributeKey) {
case 'Vertical':
if (self::identifyFixedStyleValue($verticalAlignmentStyles, $styleAttributeValue)) {
$this->_styles[$styleID]['alignment']['vertical'] = $styleAttributeValue;
}
break;
case 'Horizontal':
if (self::identifyFixedStyleValue($horizontalAlignmentStyles, $styleAttributeValue)) {
$this->_styles[$styleID]['alignment']['horizontal'] = $styleAttributeValue;
}
break;
case 'WrapText':
$this->_styles[$styleID]['alignment']['wrap'] = true;
break;
}
}
break;
case 'Borders':
foreach ($styleData->Border as $borderStyle) {
$borderAttributes = $borderStyle->attributes($namespaces['ss']);
$thisBorder = array();
foreach ($borderAttributes as $borderStyleKey => $borderStyleValue) {
// echo $borderStyleKey.' = '.$borderStyleValue.'<br />';
switch ($borderStyleKey) {
case 'LineStyle':
$thisBorder['style'] = Style_Border::BORDER_MEDIUM;
// $thisBorder['style'] = $borderStyleValue;
break;
case 'Weight':
// $thisBorder['style'] = $borderStyleValue;
break;
case 'Position':
$borderPosition = strtolower($borderStyleValue);
break;
case 'Color':
$borderColour = substr($borderStyleValue, 1);
$thisBorder['color']['rgb'] = $borderColour;
break;
}
}
if (count($thisBorder) > 0) {
if ($borderPosition == 'left' || $borderPosition == 'right' || $borderPosition == 'top' || $borderPosition == 'bottom') {
$this->_styles[$styleID]['borders'][$borderPosition] = $thisBorder;
}
}
}
break;
case 'Font':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
$styleAttributeValue = (string) $styleAttributeValue;
switch ($styleAttributeKey) {
case 'FontName':
$this->_styles[$styleID]['font']['name'] = $styleAttributeValue;
break;
case 'Size':
$this->_styles[$styleID]['font']['size'] = $styleAttributeValue;
break;
case 'Color':
$this->_styles[$styleID]['font']['color']['rgb'] = substr($styleAttributeValue, 1);
break;
case 'Bold':
$this->_styles[$styleID]['font']['bold'] = true;
break;
case 'Italic':
$this->_styles[$styleID]['font']['italic'] = true;
break;
case 'Underline':
if (self::identifyFixedStyleValue($underlineStyles, $styleAttributeValue)) {
$this->_styles[$styleID]['font']['underline'] = $styleAttributeValue;
}
break;
}
}
break;
case 'Interior':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
switch ($styleAttributeKey) {
case 'Color':
$this->_styles[$styleID]['fill']['color']['rgb'] = substr($styleAttributeValue, 1);
break;
}
}
break;
case 'NumberFormat':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
$styleAttributeValue = str_replace($fromFormats, $toFormats, $styleAttributeValue);
switch ($styleAttributeValue) {
case 'Short Date':
$styleAttributeValue = 'dd/mm/yyyy';
break;
}
if ($styleAttributeValue > '') {
$this->_styles[$styleID]['numberformat']['code'] = $styleAttributeValue;
}
}
break;
case 'Protection':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
}
break;
}
}
// print_r($this->_styles[$styleID]);
// echo '<hr />';
}
// echo '<hr />';
$worksheetID = 0;
foreach ($xml->Worksheet as $worksheet) {
$worksheet_ss = $worksheet->attributes($namespaces['ss']);
if (isset($this->_loadSheetsOnly) && isset($worksheet_ss['Name']) && !in_array($worksheet_ss['Name'], $this->_loadSheetsOnly)) {
continue;
}
// Create new Worksheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($worksheetID);
if (isset($worksheet_ss['Name'])) {
$worksheetName = (string) $worksheet_ss['Name'];
$objPHPExcel->getActiveSheet()->setTitle($worksheetName);
}
$columnID = 'A';
foreach ($worksheet->Table->Column as $columnData) {
$columnData_ss = $columnData->attributes($namespaces['ss']);
if (isset($columnData_ss['Index'])) {
$columnID = Cell::stringFromColumnIndex($columnData_ss['Index'] - 1);
}
if (isset($columnData_ss['Width'])) {
$columnWidth = $columnData_ss['Width'];
// echo '<b>Setting column width for '.$columnID.' to '.$columnWidth.'</b><br />';
$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4);
}
++$columnID;
}
$rowID = 1;
foreach ($worksheet->Table->Row as $rowData) {
$row_ss = $rowData->attributes($namespaces['ss']);
if (isset($row_ss['Index'])) {
$rowID = (int) $row_ss['Index'];
}
// echo '<b>Row '.$rowID.'</b><br />';
if (isset($row_ss['StyleID'])) {
$rowStyle = $row_ss['StyleID'];
}
if (isset($row_ss['Height'])) {
$rowHeight = $row_ss['Height'];
// echo '<b>Setting row height to '.$rowHeight.'</b><br />';
$objPHPExcel->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight);
}
$columnID = 'A';
foreach ($rowData->Cell as $cell) {
$cell_ss = $cell->attributes($namespaces['ss']);
if (isset($cell_ss['Index'])) {
$columnID = Cell::stringFromColumnIndex($cell_ss['Index'] - 1);
}
$cellRange = $columnID . $rowID;
if (isset($cell_ss['MergeAcross']) || isset($cell_ss['MergeDown'])) {
$columnTo = $columnID;
if (isset($cell_ss['MergeAcross'])) {
$columnTo = Cell::stringFromColumnIndex(Cell::columnIndexFromString($columnID) + $cell_ss['MergeAcross'] - 1);
}
$rowTo = $rowID;
if (isset($cell_ss['MergeDown'])) {
$rowTo = $rowTo + $cell_ss['MergeDown'];
}
$cellRange .= ':' . $columnTo . $rowTo;
$objPHPExcel->getActiveSheet()->mergeCells($cellRange);
}
$hasCalculatedValue = false;
$cellDataFormula = '';
if (isset($cell_ss['Formula'])) {
$cellDataFormula = $cell_ss['Formula'];
$hasCalculatedValue = true;
}
if (isset($cell->Data)) {
$cellValue = $cellData = $cell->Data;
$type = Cell_DataType::TYPE_NULL;
$cellData_ss = $cellData->attributes($namespaces['ss']);
if (isset($cellData_ss['Type'])) {
$cellDataType = $cellData_ss['Type'];
switch ($cellDataType) {
/*
const TYPE_STRING = 's';
const TYPE_FORMULA = 'f';
const TYPE_NUMERIC = 'n';
const TYPE_BOOL = 'b';
const TYPE_NULL = 's';
const TYPE_INLINE = 'inlineStr';
const TYPE_ERROR = 'e';
*/
case 'String':
$type = Cell_DataType::TYPE_STRING;
break;
case 'Number':
$type = Cell_DataType::TYPE_NUMERIC;
$cellValue = (double) $cellValue;
if (floor($cellValue) == $cellValue) {
$cellValue = (int) $cellValue;
}
break;
case 'Boolean':
$type = Cell_DataType::TYPE_BOOL;
$cellValue = $cellValue != 0;
break;
case 'DateTime':
$type = Cell_DataType::TYPE_NUMERIC;
$cellValue = Shared_Date::PHPToExcel(strtotime($cellValue));
break;
case 'Error':
$type = Cell_DataType::TYPE_ERROR;
break;
}
}
if ($hasCalculatedValue) {
$type = Cell_DataType::TYPE_FORMULA;
$columnNumber = Cell::columnIndexFromString($columnID);
// Convert R1C1 style references to A1 style references (but only when not quoted)
$temp = explode('"', $cellDataFormula);
foreach ($temp as $key => &$value) {
// Only replace in alternate array entries (i.e. non-quoted blocks)
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 = $rowID;
}
// Bracketed R references are relative to the current row
if ($rowReference[0] == '[') {
$rowReference = $rowID + trim($rowReference, '[]');
}
$columnReference = $cellReference[4][0];
// Empty C reference is the current column
if ($columnReference == '') {
$columnReference = $columnNumber;
}
// Bracketed C references are relative to the current column
if ($columnReference[0] == '[') {
$columnReference = $columnNumber + 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);
}
// echo 'Cell '.$columnID.$rowID.' is a '.$type.' with a value of '.(($hasCalculatedValue) ? $cellDataFormula : $cellValue).'<br />';
//
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit($hasCalculatedValue ? $cellDataFormula : $cellValue, $type);
if ($hasCalculatedValue) {
// echo 'Forumla result is '.$cellValue.'<br />';
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue);
}
}
if (isset($cell_ss['StyleID'])) {
$style = (string) $cell_ss['StyleID'];
// echo 'Cell style for '.$columnID.$rowID.' is '.$style.'<br />';
if (isset($this->_styles[$style]) && count($this->_styles[$style]) > 0) {
// echo 'Cell '.$columnID.$rowID.'<br />';
// print_r($this->_styles[$style]);
// echo '<br />';
if (!$objPHPExcel->getActiveSheet()->cellExists($columnID . $rowID)) {
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValue(NULL);
}
$objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->_styles[$style]);
}
}
++$columnID;
}
++$rowID;
}
++$worksheetID;
}
// Return
return $objPHPExcel;
}
/**
* Convert 1-cell anchor coordinates to 2-cell anchor coordinates
* This function is ported from PEAR Spreadsheet_Writer_Excel with small modifications
*
* Calculate the vertices that define the position of the image as required by
* the OBJ record.
*
* +------------+------------+
* | A | B |
* +-----+------------+------------+
* | |(x1,y1) | |
* | 1 |(A1)._______|______ |
* | | | | |
* | | | | |
* +-----+----| BITMAP |-----+
* | | | | |
* | 2 | |______________. |
* | | | (B2)|
* | | | (x2,y2)|
* +---- +------------+------------+
*
* Example of a bitmap that covers some of the area from cell A1 to cell B2.
*
* Based on the width and height of the bitmap we need to calculate 8 vars:
* $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
* The width and height of the cells are also variable and have to be taken into
* account.
* The values of $col_start and $row_start are passed in from the calling
* function. The values of $col_end and $row_end are calculated by subtracting
* the width and height of the bitmap from the width and height of the
* underlying cells.
* The vertices are expressed as a percentage of the underlying cell width as
* follows (rhs values are in pixels):
*
* x1 = X / W *1024
* y1 = Y / H *256
* x2 = (X-1) / W *1024
* y2 = (Y-1) / H *256
*
* Where: X is distance from the left side of the underlying cell
* Y is distance from the top of the underlying cell
* W is the width of the cell
* H is the height of the cell
*
* @param Worksheet $sheet
* @param string $coordinates E.g. 'A1'
* @param integer $offsetX Horizontal offset in pixels
* @param integer $offsetY Vertical offset in pixels
* @param integer $width Width in pixels
* @param integer $height Height in pixels
* @return array
*/
public static function oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height)
{
list($column, $row) = Cell::coordinateFromString($coordinates);
$col_start = Cell::columnIndexFromString($column) - 1;
$row_start = $row - 1;
$x1 = $offsetX;
$y1 = $offsetY;
// Initialise end cell to the same as the start cell
$col_end = $col_start;
// Col containing lower right corner of object
$row_end = $row_start;
// Row containing bottom right corner of object
// Zero the specified offset if greater than the cell dimensions
if ($x1 >= self::sizeCol($sheet, Cell::stringFromColumnIndex($col_start))) {
$x1 = 0;
}
if ($y1 >= self::sizeRow($sheet, $row_start + 1)) {
$y1 = 0;
}
$width = $width + $x1 - 1;
$height = $height + $y1 - 1;
// Subtract the underlying cell widths to find the end cell of the image
while ($width >= self::sizeCol($sheet, Cell::stringFromColumnIndex($col_end))) {
$width -= self::sizeCol($sheet, Cell::stringFromColumnIndex($col_end));
++$col_end;
}
// Subtract the underlying cell heights to find the end cell of the image
while ($height >= self::sizeRow($sheet, $row_end + 1)) {
$height -= self::sizeRow($sheet, $row_end + 1);
++$row_end;
}
// Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
// with zero height or width.
if (self::sizeCol($sheet, Cell::stringFromColumnIndex($col_start)) == 0) {
return;
}
if (self::sizeCol($sheet, Cell::stringFromColumnIndex($col_end)) == 0) {
return;
}
if (self::sizeRow($sheet, $row_start + 1) == 0) {
return;
}
if (self::sizeRow($sheet, $row_end + 1) == 0) {
return;
}
// Convert the pixel values to the percentage value expected by Excel
$x1 = $x1 / self::sizeCol($sheet, Cell::stringFromColumnIndex($col_start)) * 1024;
$y1 = $y1 / self::sizeRow($sheet, $row_start + 1) * 256;
$x2 = ($width + 1) / self::sizeCol($sheet, Cell::stringFromColumnIndex($col_end)) * 1024;
// Distance to right side of object
$y2 = ($height + 1) / self::sizeRow($sheet, $row_end + 1) * 256;
// Distance to bottom of object
$startCoordinates = Cell::stringFromColumnIndex($col_start) . ($row_start + 1);
$endCoordinates = Cell::stringFromColumnIndex($col_end) . ($row_end + 1);
$twoAnchor = array('startCoordinates' => $startCoordinates, 'startOffsetX' => $x1, 'startOffsetY' => $y1, 'endCoordinates' => $endCoordinates, 'endOffsetX' => $x2, 'endOffsetY' => $y2);
return $twoAnchor;
}
/**
* Extract all cell references in range
*
* @param string $pRange Range (e.g. A1 or A1:A10 or A1:A10 A100:A1000)
* @return array Array containing single cell references
*/
public static function extractAllCellReferencesInRange($pRange = 'A1')
{
// Returnvalue
$returnValue = array();
// Explode spaces
$aExplodeSpaces = explode(' ', str_replace('$', '', strtoupper($pRange)));
foreach ($aExplodeSpaces as $explodedSpaces) {
// Single cell?
if (strpos($explodedSpaces, ':') === false && strpos($explodedSpaces, ',') === false) {
$col = 'A';
$row = 1;
list($col, $row) = Cell::coordinateFromString($explodedSpaces);
if (strlen($col) <= 2) {
$returnValue[] = $explodedSpaces;
}
continue;
}
// Range...
$range = Cell::splitRange($explodedSpaces);
for ($i = 0; $i < count($range); ++$i) {
// Single cell?
if (count($range[$i]) == 1) {
$col = 'A';
$row = 1;
list($col, $row) = Cell::coordinateFromString($range[$i]);
if (strlen($col) <= 2) {
$returnValue[] = $explodedSpaces;
}
}
// Range...
$rangeStart = $rangeEnd = '';
$startingCol = $startingRow = $endingCol = $endingRow = 0;
list($rangeStart, $rangeEnd) = $range[$i];
list($startingCol, $startingRow) = Cell::coordinateFromString($rangeStart);
list($endingCol, $endingRow) = Cell::coordinateFromString($rangeEnd);
// Conversions...
$startingCol = Cell::columnIndexFromString($startingCol);
$endingCol = Cell::columnIndexFromString($endingCol);
// Current data
$currentCol = --$startingCol;
$currentRow = $startingRow;
// Loop cells
while ($currentCol < $endingCol) {
$loopColumn = Cell::stringFromColumnIndex($currentCol);
while ($currentRow <= $endingRow) {
$returnValue[] = $loopColumn . $currentRow;
++$currentRow;
}
++$currentCol;
$currentRow = $startingRow;
}
}
}
// Return value
return $returnValue;
}
/**
* Set print area
*
* @param int $column1 Column 1
* @param int $row1 Row 1
* @param int $column2 Column 2
* @param int $row2 Row 2
* @return Worksheet_PageSetup
*/
public function setPrintAreaByColumnAndRow($column1, $row1, $column2, $row2)
{
return $this->setPrintArea(Cell::stringFromColumnIndex($column1) . $row1 . ':' . Cell::stringFromColumnIndex($column2) . $row2);
}
/**
* Generate row
*
* @param Worksheet $pSheet Worksheet
* @param array $pValues Array containing cells in a row
* @param int $pRow Row number (0-based)
* @return string
* @throws Exception
*/
private function _generateRow(Worksheet $pSheet, $pValues = null, $pRow = 0)
{
if (is_array($pValues)) {
// Construct HTML
$html = '';
// Sheet index
$sheetIndex = $pSheet->getParent()->getIndex($pSheet);
// TCPDF and breaks
if ($this->_isPdf && count($pSheet->getBreaks()) > 0) {
$breaks = $pSheet->getBreaks();
// check if a break is needed before this row
if (isset($breaks['A' . $pRow])) {
// close table: </table>
$html .= $this->_generateTableFooter();
// insert page break
$html .= '<tcpdf method="AddPage" />';
// open table again: <table> + <col> etc.
$html .= $this->_generateTableHeader($pSheet);
}
}
// Write row start
if (!$this->_useInlineCss) {
$html .= ' <tr class="row' . $pRow . '">' . "\r\n";
} else {
$style = isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) ? $this->_assembleCSS($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) : '';
$html .= ' <tr style="' . $style . '">' . "\r\n";
}
// Write cells
$colNum = 0;
foreach ($pValues as $cell) {
$coordinate = Cell::stringFromColumnIndex($colNum) . ($pRow + 1);
if (!$this->_useInlineCss) {
$cssClass = '';
$cssClass = 'column' . $colNum;
} else {
$cssClass = array();
if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum])) {
$this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum];
}
}
$colSpan = 1;
$rowSpan = 1;
$writeCell = true;
// Write cell
// initialize
$cellData = '';
// Cell
if ($cell instanceof Cell) {
if (is_null($cell->getParent())) {
$cell->attach($pSheet);
}
// Value
if ($cell->getValue() instanceof RichText) {
// Loop through rich text elements
$elements = $cell->getValue()->getRichTextElements();
foreach ($elements as $element) {
// Rich text start?
if ($element instanceof RichText_Run) {
$cellData .= '<span style="' . $this->_assembleCSS($this->_createCSSStyleFont($element->getFont())) . '">';
if ($element->getFont()->getSuperScript()) {
$cellData .= '<sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '<sub>';
}
}
}
// Convert UTF8 data to PCDATA
$cellText = $element->getText();
$cellData .= htmlspecialchars($cellText);
if ($element instanceof RichText_Run) {
if ($element->getFont()->getSuperScript()) {
$cellData .= '</sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '</sub>';
}
}
$cellData .= '</span>';
}
}
} else {
if ($this->_preCalculateFormulas) {
$cellData = Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode(), array($this, 'formatColor'));
} else {
$cellData = Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode(), array($this, 'formatColor'));
}
}
// replace leading spaces on each line with
$cellData = $this->_convertNbsp($cellData);
// convert newline "\n" to '<br>'
$cellData = str_replace("\n", '<br/>', $cellData);
// Extend CSS class?
if (!$this->_useInlineCss) {
$cssClass .= ' style' . $cell->getXfIndex();
$cssClass .= ' ' . $cell->getDataType();
} else {
if (isset($this->_cssStyles['td.style' . $cell->getXfIndex()])) {
$cssClass = array_merge($cssClass, $this->_cssStyles['td.style' . $cell->getXfIndex()]);
}
// General horizontal alignment: Actual horizontal alignment depends on dataType
$sharedStyle = $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex());
if ($sharedStyle->getAlignment()->getHorizontal() == Style_Alignment::HORIZONTAL_GENERAL && isset($this->_cssStyles['.' . $cell->getDataType()]['text-align'])) {
$cssClass['text-align'] = $this->_cssStyles['.' . $cell->getDataType()]['text-align'];
}
}
}
// Hyperlink?
if ($pSheet->hyperlinkExists($coordinate) && !$pSheet->getHyperlink($coordinate)->isInternal()) {
$cellData = '<a href="' . htmlspecialchars($pSheet->getHyperlink($coordinate)->getUrl()) . '" title="' . htmlspecialchars($pSheet->getHyperlink($coordinate)->getTooltip()) . '">' . $cellData . '</a>';
}
// Should the cell be written or is it swallowed by a rowspan or colspan?
$writeCell = !(isset($this->_isSpannedCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum]) && $this->_isSpannedCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum]);
// Colspan and Rowspan
$colspan = 1;
$rowspan = 1;
if (isset($this->_isBaseCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum])) {
$spans = $this->_isBaseCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum];
$rowSpan = $spans['rowspan'];
$colSpan = $spans['colspan'];
}
// Write
if ($writeCell) {
// Column start
$html .= ' <td';
if (!$this->_useInlineCss) {
$html .= ' class="' . $cssClass . '"';
} else {
//** Necessary redundant code for the sake of Writer_PDF **
// We must explicitly write the width of the <td> element because TCPDF
// does not recognize e.g. <col style="width:42pt">
$width = 0;
for ($i = $colNum; $i < $colNum + $colSpan; ++$i) {
if (isset($this->_columnWidths[$sheetIndex][$i])) {
$width += $this->_columnWidths[$sheetIndex][$i];
}
}
$cssClass['width'] = $width . 'pt';
// We must also explicitly write the height of the <td> element because TCPDF
// does not recognize e.g. <tr style="height:50pt">
if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height'])) {
$height = $this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height'];
$cssClass['height'] = $height;
}
//** end of redundant code **
$html .= ' style="' . $this->_assembleCSS($cssClass) . '"';
}
if ($colSpan > 1) {
$html .= ' colspan="' . $colSpan . '"';
}
if ($rowSpan > 1) {
$html .= ' rowspan="' . $rowSpan . '"';
}
$html .= '>';
// Image?
$html .= $this->_writeImageTagInCell($pSheet, $coordinate);
// Cell data
$html .= $cellData;
// Column end
$html .= '</td>' . "\r\n";
}
// Next column
++$colNum;
}
// Write row end
$html .= ' </tr>' . "\r\n";
// Return
return $html;
} else {
throw new Exception("Invalid parameters passed.");
}
}
/**
* OFFSET
*
* Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
* The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and
* the number of columns to be returned.
*
* @param cellAddress The reference from which you want to base the offset. Reference must refer to a cell or
* range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
* @param rows The number of rows, up or down, that you want the upper-left cell to refer to.
* Using 5 as the rows argument specifies that the upper-left cell in the reference is
* five rows below reference. Rows can be positive (which means below the starting reference)
* or negative (which means above the starting reference).
* @param cols The number of columns, to the left or right, that you want the upper-left cell of the result
* to refer to. Using 5 as the cols argument specifies that the upper-left cell in the
* reference is five columns to the right of reference. Cols can be positive (which means
* to the right of the starting reference) or negative (which means to the left of the
* starting reference).
* @param height The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
* @param width The width, in number of columns, that you want the returned reference to be. Width must be a positive number.
* @return string A reference to a cell or range of cells
*/
public static function OFFSET($cellAddress = Null, $rows = 0, $columns = 0, $height = null, $width = null)
{
$rows = self::flattenSingleValue($rows);
$columns = self::flattenSingleValue($columns);
$height = self::flattenSingleValue($height);
$width = self::flattenSingleValue($width);
if ($cellAddress == Null) {
return 0;
}
$args = func_get_args();
$pCell = array_pop($args);
if (!is_object($pCell)) {
return self::$_errorCodes['reference'];
}
$sheetName = null;
if (strpos($cellAddress, "!")) {
list($sheetName, $cellAddress) = explode("!", $cellAddress);
}
if (strpos($cellAddress, ":")) {
list($startCell, $endCell) = explode(":", $cellAddress);
} else {
$startCell = $endCell = $cellAddress;
}
list($startCellColumn, $startCellRow) = Cell::coordinateFromString($startCell);
list($endCellColumn, $endCellRow) = Cell::coordinateFromString($endCell);
$startCellRow += $rows;
$startCellColumn = Cell::columnIndexFromString($startCellColumn) - 1;
$startCellColumn += $columns;
if ($startCellRow <= 0 || $startCellColumn < 0) {
return self::$_errorCodes['reference'];
}
$endCellColumn = Cell::columnIndexFromString($endCellColumn) - 1;
if ($width != null && !is_object($width)) {
$endCellColumn = $startCellColumn + $width - 1;
} else {
$endCellColumn += $columns;
}
$startCellColumn = Cell::stringFromColumnIndex($startCellColumn);
if ($height != null && !is_object($height)) {
$endCellRow = $startCellRow + $height - 1;
} else {
$endCellRow += $rows;
}
if ($endCellRow <= 0 || $endCellColumn < 0) {
return self::$_errorCodes['reference'];
}
$endCellColumn = Cell::stringFromColumnIndex($endCellColumn);
$cellAddress = $startCellColumn . $startCellRow;
if ($startCellColumn != $endCellColumn || $startCellRow != $endCellRow) {
$cellAddress .= ':' . $endCellColumn . $endCellRow;
}
if ($sheetName !== null) {
$pSheet = $pCell->getParent()->getParent()->getSheetByName($sheetName);
} else {
$pSheet = $pCell->getParent();
}
return Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, False);
}
private function processTokenStack($tokens, $cellID = null, Cell $pCell = null)
{
if ($tokens == false) {
return false;
}
// If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent cell collection),
// so we store the parent cell collection so that we can re-attach it when necessary
$pCellWorksheet = $pCell !== null ? $pCell->getWorksheet() : null;
$pCellParent = $pCell !== null ? $pCell->getParent() : null;
$stack = new Calculation\Token\Stack();
// Loop through each token in turn
foreach ($tokens as $tokenData) {
// print_r($tokenData);
// echo '<br />';
$token = $tokenData['value'];
// echo '<b>Token is '.$token.'</b><br />';
// if the token is a binary operator, pop the top two values off the stack, do the operation, and push the result back on the stack
if (isset(self::$binaryOperators[$token])) {
// echo 'Token is a binary operator<br />';
// We must have two operands, error if we don't
if (($operand2Data = $stack->pop()) === null) {
return $this->raiseFormulaError('Internal error - Operand value missing from stack');
}
if (($operand1Data = $stack->pop()) === null) {
return $this->raiseFormulaError('Internal error - Operand value missing from stack');
}
$operand1 = self::dataTestReference($operand1Data);
$operand2 = self::dataTestReference($operand2Data);
// Log what we're doing
if ($token == ':') {
$this->_debugLog->writeDebugLog('Evaluating Range ', $this->showValue($operand1Data['reference']), ' ', $token, ' ', $this->showValue($operand2Data['reference']));
} else {
$this->_debugLog->writeDebugLog('Evaluating ', $this->showValue($operand1), ' ', $token, ' ', $this->showValue($operand2));
}
// Process the operation in the appropriate manner
switch ($token) {
// Comparison (Boolean) Operators
case '>':
// Greater than
// Greater than
case '<':
// Less than
// Less than
case '>=':
// Greater than or Equal to
// Greater than or Equal to
case '<=':
// Less than or Equal to
// Less than or Equal to
case '=':
// Equality
// Equality
case '<>':
// Inequality
$this->executeBinaryComparisonOperation($cellID, $operand1, $operand2, $token, $stack);
break;
// Binary Operators
// Binary Operators
case ':':
// Range
$sheet1 = $sheet2 = '';
if (strpos($operand1Data['reference'], '!') !== false) {
list($sheet1, $operand1Data['reference']) = explode('!', $operand1Data['reference']);
} else {
$sheet1 = $pCellParent !== null ? $pCellWorksheet->getTitle() : '';
}
if (strpos($operand2Data['reference'], '!') !== false) {
list($sheet2, $operand2Data['reference']) = explode('!', $operand2Data['reference']);
} else {
$sheet2 = $sheet1;
}
if ($sheet1 == $sheet2) {
if ($operand1Data['reference'] === null) {
if (trim($operand1Data['value']) != '' && is_numeric($operand1Data['value'])) {
$operand1Data['reference'] = $pCell->getColumn() . $operand1Data['value'];
} elseif (trim($operand1Data['reference']) == '') {
$operand1Data['reference'] = $pCell->getCoordinate();
} else {
$operand1Data['reference'] = $operand1Data['value'] . $pCell->getRow();
}
}
if ($operand2Data['reference'] === null) {
if (trim($operand2Data['value']) != '' && is_numeric($operand2Data['value'])) {
$operand2Data['reference'] = $pCell->getColumn() . $operand2Data['value'];
} elseif (trim($operand2Data['reference']) == '') {
$operand2Data['reference'] = $pCell->getCoordinate();
} else {
$operand2Data['reference'] = $operand2Data['value'] . $pCell->getRow();
}
}
$oData = array_merge(explode(':', $operand1Data['reference']), explode(':', $operand2Data['reference']));
$oCol = $oRow = array();
foreach ($oData as $oDatum) {
$oCR = Cell::coordinateFromString($oDatum);
$oCol[] = Cell::columnIndexFromString($oCR[0]) - 1;
$oRow[] = $oCR[1];
}
$cellRef = Cell::stringFromColumnIndex(min($oCol)) . min($oRow) . ':' . Cell::stringFromColumnIndex(max($oCol)) . max($oRow);
if ($pCellParent !== null) {
$cellValue = $this->extractCellRange($cellRef, $this->spreadsheet->getSheetByName($sheet1), false);
} else {
return $this->raiseFormulaError('Unable to access Cell Reference');
}
$stack->push('Cell Reference', $cellValue, $cellRef);
} else {
$stack->push('Error', Calculation\Functions::REF(), null);
}
break;
case '+':
// Addition
$this->executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'plusEquals', $stack);
break;
case '-':
// Subtraction
$this->executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'minusEquals', $stack);
break;
case '*':
// Multiplication
$this->executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'arrayTimesEquals', $stack);
break;
case '/':
// Division
$this->executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'arrayRightDivide', $stack);
break;
case '^':
// Exponential
$this->executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'power', $stack);
break;
case '&':
// Concatenation
// If either of the operands is a matrix, we need to treat them both as matrices
// (converting the other operand to a matrix if need be); then perform the required
// matrix operation
if (is_bool($operand1)) {
$operand1 = $operand1 ? self::$localeBoolean['TRUE'] : self::$localeBoolean['FALSE'];
}
if (is_bool($operand2)) {
$operand2 = $operand2 ? self::$localeBoolean['TRUE'] : self::$localeBoolean['FALSE'];
}
if (is_array($operand1) || is_array($operand2)) {
// Ensure that both operands are arrays/matrices
self::checkMatrixOperands($operand1, $operand2, 2);
try {
// Convert operand 1 from a PHP array to a matrix
$matrix = new Shared\JAMA\Matrix($operand1);
// Perform the required operation against the operand 1 matrix, passing in operand 2
$matrixResult = $matrix->concat($operand2);
$result = $matrixResult->getArray();
} catch (Exception $ex) {
$this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
$result = '#VALUE!';
}
} else {
$result = '"' . str_replace('""', '"', self::unwrapResult($operand1, '"') . self::unwrapResult($operand2, '"')) . '"';
}
$this->_debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($result));
$stack->push('Value', $result);
break;
case '|':
// Intersect
$rowIntersect = array_intersect_key($operand1, $operand2);
$cellIntersect = $oCol = $oRow = array();
foreach (array_keys($rowIntersect) as $row) {
$oRow[] = $row;
foreach ($rowIntersect[$row] as $col => $data) {
$oCol[] = Cell::columnIndexFromString($col) - 1;
$cellIntersect[$row] = array_intersect_key($operand1[$row], $operand2[$row]);
}
}
$cellRef = Cell::stringFromColumnIndex(min($oCol)) . min($oRow) . ':' . Cell::stringFromColumnIndex(max($oCol)) . max($oRow);
$this->_debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($cellIntersect));
$stack->push('Value', $cellIntersect, $cellRef);
break;
}
// if the token is a unary operator, pop one value off the stack, do the operation, and push it back on
} elseif ($token === '~' || $token === '%') {
// echo 'Token is a unary operator<br />';
if (($arg = $stack->pop()) === null) {
return $this->raiseFormulaError('Internal error - Operand value missing from stack');
}
$arg = $arg['value'];
if ($token === '~') {
// echo 'Token is a negation operator<br />';
$this->_debugLog->writeDebugLog('Evaluating Negation of ', $this->showValue($arg));
$multiplier = -1;
} else {
// echo 'Token is a percentile operator<br />';
$this->_debugLog->writeDebugLog('Evaluating Percentile of ', $this->showValue($arg));
$multiplier = 0.01;
}
if (is_array($arg)) {
self::checkMatrixOperands($arg, $multiplier, 2);
try {
$matrix1 = new Shared\JAMA\Matrix($arg);
$matrixResult = $matrix1->arrayTimesEquals($multiplier);
$result = $matrixResult->getArray();
} catch (Exception $ex) {
$this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
$result = '#VALUE!';
}
$this->_debugLog->writeDebugLog('Evaluation Result is ', $this->showTypeDetails($result));
$stack->push('Value', $result);
} else {
$this->executeNumericBinaryOperation($cellID, $multiplier, $arg, '*', 'arrayTimesEquals', $stack);
}
} elseif (preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $token, $matches)) {
$cellRef = null;
// echo 'Element '.$token.' is a Cell reference<br />';
if (isset($matches[8])) {
// echo 'Reference is a Range of cells<br />';
if ($pCell === null) {
// We can't access the range, so return a REF error
$cellValue = Calculation\Functions::REF();
} else {
$cellRef = $matches[6] . $matches[7] . ':' . $matches[9] . $matches[10];
if ($matches[2] > '') {
$matches[2] = trim($matches[2], "\"'");
if (strpos($matches[2], '[') !== false || strpos($matches[2], ']') !== false) {
// It's a Reference to an external spreadsheet (not currently supported)
return $this->raiseFormulaError('Unable to access External Workbook');
}
$matches[2] = trim($matches[2], "\"'");
// echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />';
$this->_debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in worksheet ', $matches[2]);
if ($pCellParent !== null) {
$cellValue = $this->extractCellRange($cellRef, $this->spreadsheet->getSheetByName($matches[2]), false);
} else {
return $this->raiseFormulaError('Unable to access Cell Reference');
}
$this->_debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->showTypeDetails($cellValue));
// $cellRef = $matches[2].'!'.$cellRef;
} else {
// echo '$cellRef='.$cellRef.' in current worksheet<br />';
$this->_debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in current worksheet');
if ($pCellParent !== null) {
$cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, false);
} else {
return $this->raiseFormulaError('Unable to access Cell Reference');
}
$this->_debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' is ', $this->showTypeDetails($cellValue));
}
}
} else {
// echo 'Reference is a single Cell<br />';
if ($pCell === null) {
// We can't access the cell, so return a REF error
$cellValue = Calculation\Functions::REF();
} else {
$cellRef = $matches[6] . $matches[7];
if ($matches[2] > '') {
$matches[2] = trim($matches[2], "\"'");
if (strpos($matches[2], '[') !== false || strpos($matches[2], ']') !== false) {
// It's a Reference to an external spreadsheet (not currently supported)
return $this->raiseFormulaError('Unable to access External Workbook');
}
// echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />';
$this->_debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in worksheet ', $matches[2]);
if ($pCellParent !== null) {
$cellSheet = $this->spreadsheet->getSheetByName($matches[2]);
if ($cellSheet && $cellSheet->cellExists($cellRef)) {
$cellValue = $this->extractCellRange($cellRef, $this->spreadsheet->getSheetByName($matches[2]), false);
$pCell->attach($pCellParent);
} else {
$cellValue = null;
}
} else {
return $this->raiseFormulaError('Unable to access Cell Reference');
}
$this->_debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->showTypeDetails($cellValue));
// $cellRef = $matches[2].'!'.$cellRef;
} else {
// echo '$cellRef='.$cellRef.' in current worksheet<br />';
$this->_debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in current worksheet');
if ($pCellParent->isDataSet($cellRef)) {
$cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, false);
$pCell->attach($pCellParent);
} else {
$cellValue = null;
}
$this->_debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' is ', $this->showTypeDetails($cellValue));
}
}
}
$stack->push('Value', $cellValue, $cellRef);
// if the token is a function, pop arguments off the stack, hand them to the function, and push the result back on
} elseif (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $token, $matches)) {
// echo 'Token is a function<br />';
$functionName = $matches[1];
$argCount = $stack->pop();
$argCount = $argCount['value'];
if ($functionName != 'MKMATRIX') {
$this->_debugLog->writeDebugLog('Evaluating Function ', self::localeFunc($functionName), '() with ', $argCount == 0 ? 'no' : $argCount, ' argument', $argCount == 1 ? '' : 's');
}
if (isset(self::$PHPExcelFunctions[$functionName]) || isset(self::$controlFunctions[$functionName])) {
// function
if (isset(self::$PHPExcelFunctions[$functionName])) {
$functionCall = self::$PHPExcelFunctions[$functionName]['functionCall'];
$passByReference = isset(self::$PHPExcelFunctions[$functionName]['passByReference']);
$passCellReference = isset(self::$PHPExcelFunctions[$functionName]['passCellReference']);
} elseif (isset(self::$controlFunctions[$functionName])) {
$functionCall = self::$controlFunctions[$functionName]['functionCall'];
$passByReference = isset(self::$controlFunctions[$functionName]['passByReference']);
$passCellReference = isset(self::$controlFunctions[$functionName]['passCellReference']);
}
// get the arguments for this function
// echo 'Function '.$functionName.' expects '.$argCount.' arguments<br />';
$args = $argArrayVals = array();
for ($i = 0; $i < $argCount; ++$i) {
$arg = $stack->pop();
$a = $argCount - $i - 1;
if ($passByReference && isset(self::$PHPExcelFunctions[$functionName]['passByReference'][$a]) && self::$PHPExcelFunctions[$functionName]['passByReference'][$a]) {
if ($arg['reference'] === null) {
$args[] = $cellID;
if ($functionName != 'MKMATRIX') {
$argArrayVals[] = $this->showValue($cellID);
}
} else {
$args[] = $arg['reference'];
if ($functionName != 'MKMATRIX') {
$argArrayVals[] = $this->showValue($arg['reference']);
}
}
} else {
$args[] = self::unwrapResult($arg['value']);
if ($functionName != 'MKMATRIX') {
$argArrayVals[] = $this->showValue($arg['value']);
}
}
}
// Reverse the order of the arguments
krsort($args);
if ($passByReference && $argCount == 0) {
$args[] = $cellID;
$argArrayVals[] = $this->showValue($cellID);
}
// echo 'Arguments are: ';
// print_r($args);
// echo '<br />';
if ($functionName != 'MKMATRIX') {
if ($this->_debugLog->getWriteDebugLog()) {
krsort($argArrayVals);
$this->_debugLog->writeDebugLog('Evaluating ', self::localeFunc($functionName), '( ', implode(self::$localeArgumentSeparator . ' ', Calculation\Functions::flattenArray($argArrayVals)), ' )');
}
}
// Process each argument in turn, building the return value as an array
// if (($argCount == 1) && (is_array($args[1])) && ($functionName != 'MKMATRIX')) {
// $operand1 = $args[1];
// $this->_debugLog->writeDebugLog('Argument is a matrix: ', $this->showValue($operand1));
// $result = array();
// $row = 0;
// foreach($operand1 as $args) {
// if (is_array($args)) {
// foreach($args as $arg) {
// $this->_debugLog->writeDebugLog('Evaluating ', self::localeFunc($functionName), '( ', $this->showValue($arg), ' )');
// $r = call_user_func_array($functionCall, $arg);
// $this->_debugLog->writeDebugLog('Evaluation Result for ', self::localeFunc($functionName), '() function call is ', $this->showTypeDetails($r));
// $result[$row][] = $r;
// }
// ++$row;
// } else {
// $this->_debugLog->writeDebugLog('Evaluating ', self::localeFunc($functionName), '( ', $this->showValue($args), ' )');
// $r = call_user_func_array($functionCall, $args);
// $this->_debugLog->writeDebugLog('Evaluation Result for ', self::localeFunc($functionName), '() function call is ', $this->showTypeDetails($r));
// $result[] = $r;
// }
// }
// } else {
// Process the argument with the appropriate function call
if ($passCellReference) {
$args[] = $pCell;
}
if (strpos($functionCall, '::') !== false) {
$result = call_user_func_array(explode('::', $functionCall), $args);
} else {
foreach ($args as &$arg) {
$arg = Calculation\Functions::flattenSingleValue($arg);
}
unset($arg);
$result = call_user_func_array($functionCall, $args);
}
if ($functionName != 'MKMATRIX') {
$this->_debugLog->writeDebugLog('Evaluation Result for ', self::localeFunc($functionName), '() function call is ', $this->showTypeDetails($result));
}
$stack->push('Value', self::wrapResult($result));
}
} else {
// if the token is a number, boolean, string or an Excel error, push it onto the stack
if (isset(self::$excelConstants[strtoupper($token)])) {
$excelConstant = strtoupper($token);
// echo 'Token is a PHPExcel constant: '.$excelConstant.'<br />';
$stack->push('Constant Value', self::$excelConstants[$excelConstant]);
$this->_debugLog->writeDebugLog('Evaluating Constant ', $excelConstant, ' as ', $this->showTypeDetails(self::$excelConstants[$excelConstant]));
} elseif (is_numeric($token) || $token === null || is_bool($token) || $token == '' || $token[0] == '"' || $token[0] == '#') {
// echo 'Token is a number, boolean, string, null or an Excel error<br />';
$stack->push('Value', $token);
// if the token is a named range, push the named range name onto the stack
} elseif (preg_match('/^' . self::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $token, $matches)) {
// echo 'Token is a named range<br />';
$namedRange = $matches[6];
// echo 'Named Range is '.$namedRange.'<br />';
$this->_debugLog->writeDebugLog('Evaluating Named Range ', $namedRange);
$cellValue = $this->extractNamedRange($namedRange, null !== $pCell ? $pCellWorksheet : null, false);
$pCell->attach($pCellParent);
$this->_debugLog->writeDebugLog('Evaluation Result for named range ', $namedRange, ' is ', $this->showTypeDetails($cellValue));
$stack->push('Named Range', $cellValue, $namedRange);
} else {
return $this->raiseFormulaError("undefined variable '{$token}'");
}
}
}
// when we're out of tokens, the stack should have a single element, the final result
if ($stack->count() != 1) {
return $this->raiseFormulaError("internal error");
}
$output = $stack->pop();
$output = $output['value'];
// if ((is_array($output)) && (self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY)) {
// return array_shift(Calculation\Functions::flattenArray($output));
// }
return $output;
}
/**
* Calculate the vertices that define the position of the image as required by
* the OBJ record.
*
* +------------+------------+
* | A | B |
* +-----+------------+------------+
* | |(x1,y1) | |
* | 1 |(A1)._______|______ |
* | | | | |
* | | | | |
* +-----+----| BITMAP |-----+
* | | | | |
* | 2 | |______________. |
* | | | (B2)|
* | | | (x2,y2)|
* +---- +------------+------------+
*
* Example of a bitmap that covers some of the area from cell A1 to cell B2.
*
* Based on the width and height of the bitmap we need to calculate 8 vars:
* $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
* The width and height of the cells are also variable and have to be taken into
* account.
* The values of $col_start and $row_start are passed in from the calling
* function. The values of $col_end and $row_end are calculated by subtracting
* the width and height of the bitmap from the width and height of the
* underlying cells.
* The vertices are expressed as a percentage of the underlying cell width as
* follows (rhs values are in pixels):
*
* x1 = X / W *1024
* y1 = Y / H *256
* x2 = (X-1) / W *1024
* y2 = (Y-1) / H *256
*
* Where: X is distance from the left side of the underlying cell
* Y is distance from the top of the underlying cell
* W is the width of the cell
* H is the height of the cell
* The SDK incorrectly states that the height should be expressed as a
* percentage of 1024.
*
* @access private
* @param integer $col_start Col containing upper left corner of object
* @param integer $row_start Row containing top left corner of object
* @param integer $x1 Distance to left side of object
* @param integer $y1 Distance to top of object
* @param integer $width Width of image frame
* @param integer $height Height of image frame
*/
function _positionImage($col_start, $row_start, $x1, $y1, $width, $height)
{
// Initialise end cell to the same as the start cell
$col_end = $col_start;
// Col containing lower right corner of object
$row_end = $row_start;
// Row containing bottom right corner of object
// Zero the specified offset if greater than the cell dimensions
if ($x1 >= Shared_Excel5::sizeCol($this->_phpSheet, Cell::stringFromColumnIndex($col_start))) {
$x1 = 0;
}
if ($y1 >= Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1)) {
$y1 = 0;
}
$width = $width + $x1 - 1;
$height = $height + $y1 - 1;
// Subtract the underlying cell widths to find the end cell of the image
while ($width >= Shared_Excel5::sizeCol($this->_phpSheet, Cell::stringFromColumnIndex($col_end))) {
$width -= Shared_Excel5::sizeCol($this->_phpSheet, Cell::stringFromColumnIndex($col_end));
++$col_end;
}
// Subtract the underlying cell heights to find the end cell of the image
while ($height >= Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1)) {
$height -= Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1);
++$row_end;
}
// Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
// with zero eight or width.
//
if (Shared_Excel5::sizeCol($this->_phpSheet, Cell::stringFromColumnIndex($col_start)) == 0) {
return;
}
if (Shared_Excel5::sizeCol($this->_phpSheet, Cell::stringFromColumnIndex($col_end)) == 0) {
return;
}
if (Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1) == 0) {
return;
}
if (Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1) == 0) {
return;
}
// Convert the pixel values to the percentage value expected by Excel
$x1 = $x1 / Shared_Excel5::sizeCol($this->_phpSheet, Cell::stringFromColumnIndex($col_start)) * 1024;
$y1 = $y1 / Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1) * 256;
$x2 = $width / Shared_Excel5::sizeCol($this->_phpSheet, Cell::stringFromColumnIndex($col_end)) * 1024;
// Distance to right side of object
$y2 = $height / Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1) * 256;
// Distance to bottom of object
$this->_writeObjPicture($col_start, $x1, $row_start, $y1, $col_end, $x2, $row_end, $y2);
}
/**
* 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 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.");
}
$zip = new ZipArchive();
if ($zip->open($pFilename) === true) {
// echo '<h1>Meta Information</h1>';
$xml = simplexml_load_string($zip->getFromName("meta.xml"));
$namespacesMeta = $xml->getNamespaces(true);
// echo '<pre>';
// print_r($namespacesMeta);
// echo '</pre><hr />';
$docProps = $objPHPExcel->getProperties();
$officeProperty = $xml->children($namespacesMeta['office']);
foreach ($officeProperty as $officePropertyData) {
$officePropertyDC = array();
if (isset($namespacesMeta['dc'])) {
$officePropertyDC = $officePropertyData->children($namespacesMeta['dc']);
}
foreach ($officePropertyDC as $propertyName => $propertyValue) {
// echo $propertyName.' = '.$propertyValue.'<hr />';
switch ($propertyName) {
case 'title':
$docProps->setTitle($propertyValue);
break;
case 'subject':
$docProps->setSubject($propertyValue);
break;
case 'creator':
$docProps->setCreator($propertyValue);
break;
case 'date':
$creationDate = strtotime($propertyValue);
$docProps->setCreated($creationDate);
break;
case 'description':
$docProps->setDescription($propertyValue);
break;
}
}
$officePropertyMeta = array();
if (isset($namespacesMeta['dc'])) {
$officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']);
}
foreach ($officePropertyMeta as $propertyName => $propertyValue) {
$propertyValueAttributes = $propertyValue->attributes($namespacesMeta['meta']);
// echo $propertyName.' = '.$propertyValue.'<br />';
// foreach ($propertyValueAttributes as $key => $value) {
// echo $key.' = '.$value.'<br />';
// }
// echo '<hr />';
//
switch ($propertyName) {
case 'keyword':
$docProps->setKeywords($propertyValue);
break;
}
}
}
// echo '<h1>Workbook Content</h1>';
$xml = simplexml_load_string($zip->getFromName("content.xml"));
$namespacesContent = $xml->getNamespaces(true);
// echo '<pre>';
// print_r($namespacesContent);
// echo '</pre><hr />';
$workbook = $xml->children($namespacesContent['office']);
foreach ($workbook->body->spreadsheet as $workbookData) {
$workbookData = $workbookData->children($namespacesContent['table']);
$worksheetID = 0;
foreach ($workbookData->table as $worksheetDataSet) {
$worksheetData = $worksheetDataSet->children($namespacesContent['table']);
// print_r($worksheetData);
// echo '<br />';
$worksheetDataAttributes = $worksheetDataSet->attributes($namespacesContent['table']);
// print_r($worksheetDataAttributes);
// echo '<br />';
if (isset($this->_loadSheetsOnly) && isset($worksheetDataAttributes['name']) && !in_array($worksheetDataAttributes['name'], $this->_loadSheetsOnly)) {
continue;
}
// echo '<h2>Worksheet '.$worksheetDataAttributes['name'].'</h2>';
// Create new Worksheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($worksheetID);
if (isset($worksheetDataAttributes['name'])) {
$worksheetName = (string) $worksheetDataAttributes['name'];
$objPHPExcel->getActiveSheet()->setTitle($worksheetName);
}
$rowID = 1;
foreach ($worksheetData as $key => $rowData) {
// echo '<b>'.$key.'</b><br />';
switch ($key) {
case 'table-row':
$columnID = 'A';
foreach ($rowData as $key => $cellData) {
// echo '<b>'.$columnID.$rowID.'</b><br />';
$cellDataText = $cellData->children($namespacesContent['text']);
$cellDataOfficeAttributes = $cellData->attributes($namespacesContent['office']);
$cellDataTableAttributes = $cellData->attributes($namespacesContent['table']);
// echo 'Office Attributes: ';
// print_r($cellDataOfficeAttributes);
// echo '<br />Table Attributes: ';
// print_r($cellDataTableAttributes);
// echo '<br />Cell Data Text';
// print_r($cellDataText);
// echo '<br />';
//
$type = $formatting = $hyperlink = null;
$hasCalculatedValue = false;
$cellDataFormula = '';
if (isset($cellDataTableAttributes['formula'])) {
$cellDataFormula = $cellDataTableAttributes['formula'];
$hasCalculatedValue = true;
}
if (isset($cellDataText->p)) {
// echo 'Value Type is '.$cellDataOfficeAttributes['value-type'].'<br />';
switch ($cellDataOfficeAttributes['value-type']) {
case 'string':
$type = Cell_DataType::TYPE_STRING;
$dataValue = $cellDataText->p;
if (isset($dataValue->a)) {
$dataValue = $dataValue->a;
$cellXLinkAttributes = $dataValue->attributes($namespacesContent['xlink']);
$hyperlink = $cellXLinkAttributes['href'];
}
break;
case 'boolean':
$type = Cell_DataType::TYPE_BOOL;
$dataValue = $cellDataText->p == 'TRUE' ? True : False;
break;
case 'float':
$type = Cell_DataType::TYPE_NUMERIC;
$dataValue = (double) $cellDataOfficeAttributes['value'];
if (floor($dataValue) == $dataValue) {
$dataValue = (int) $dataValue;
}
break;
case 'date':
$type = Cell_DataType::TYPE_NUMERIC;
$dateObj = date_create($cellDataOfficeAttributes['date-value']);
list($year, $month, $day, $hour, $minute, $second) = explode(' ', $dateObj->format('Y m d H i s'));
$dataValue = Shared_Date::FormattedPHPToExcel($year, $month, $day, $hour, $minute, $second);
if ($dataValue != floor($dataValue)) {
$formatting = Style_NumberFormat::FORMAT_DATE_XLSX15 . ' ' . Style_NumberFormat::FORMAT_DATE_TIME4;
} else {
$formatting = Style_NumberFormat::FORMAT_DATE_XLSX15;
}
break;
case 'time':
$type = Cell_DataType::TYPE_NUMERIC;
$dataValue = Shared_Date::PHPToExcel(strtotime('01-01-1970 ' . implode(':', sscanf($cellDataOfficeAttributes['time-value'], 'PT%dH%dM%dS'))));
$formatting = Style_NumberFormat::FORMAT_DATE_TIME4;
break;
}
// echo 'Data value is '.$dataValue.'<br />';
// if (!is_null($hyperlink)) {
// echo 'Hyperlink is '.$hyperlink.'<br />';
// }
}
if ($hasCalculatedValue) {
$type = Cell_DataType::TYPE_FORMULA;
// echo 'Formula: '.$cellDataFormula.'<br />';
$cellDataFormula = substr($cellDataFormula, strpos($cellDataFormula, ':=') + 1);
$temp = explode('"', $cellDataFormula);
foreach ($temp as $key => &$value) {
// Only replace in alternate array entries (i.e. non-quoted blocks)
if ($key % 2 == 0) {
$value = preg_replace('/\\[\\.(.*):\\.(.*)\\]/Ui', '$1:$2', $value);
$value = preg_replace('/\\[\\.(.*)\\]/Ui', '$1', $value);
$value = Calculation::_translateSeparator(';', ',', $value);
}
}
unset($value);
// Then rebuild the formula string
$cellDataFormula = implode('"', $temp);
// echo 'Adjusted Formula: '.$cellDataFormula.'<br />';
}
if (!is_null($type)) {
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit($hasCalculatedValue ? $cellDataFormula : $dataValue, $type);
if ($hasCalculatedValue) {
// echo 'Forumla result is '.$dataValue.'<br />';
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($dataValue);
}
if ($cellDataOfficeAttributes['value-type'] == 'date' || $cellDataOfficeAttributes['value-type'] == 'time') {
$objPHPExcel->getActiveSheet()->getStyle($columnID . $rowID)->getNumberFormat()->setFormatCode($formatting);
}
if (!is_null($hyperlink)) {
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->getHyperlink()->setUrl($hyperlink);
}
}
// Merged cells
if (isset($cellDataTableAttributes['number-columns-spanned']) || isset($cellDataTableAttributes['number-rows-spanned'])) {
$columnTo = $columnID;
if (isset($cellDataTableAttributes['number-columns-spanned'])) {
$columnTo = Cell::stringFromColumnIndex(Cell::columnIndexFromString($columnID) + $cellDataTableAttributes['number-columns-spanned'] - 2);
}
$rowTo = $rowID;
if (isset($cellDataTableAttributes['number-rows-spanned'])) {
$rowTo = $rowTo + $cellDataTableAttributes['number-rows-spanned'] - 1;
}
$cellRange = $columnID . $rowID . ':' . $columnTo . $rowTo;
$objPHPExcel->getActiveSheet()->mergeCells($cellRange);
}
if (isset($cellDataTableAttributes['number-columns-repeated'])) {
// echo 'Repeated '.$cellDataTableAttributes['number-columns-repeated'].' times<br />';
$columnID = Cell::stringFromColumnIndex(Cell::columnIndexFromString($columnID) + $cellDataTableAttributes['number-columns-repeated'] - 2);
}
++$columnID;
}
++$rowID;
break;
}
}
++$worksheetID;
}
}
}
// Return
return $objPHPExcel;
}
