/**
* Loads PHPExcel from file
*
* @param string $pFilename
* @throws Exception
*/
public function load($pFilename)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
// Initialisations
$excel = new PHPExcel;
$excel->removeSheetByIndex(0);
if (!$this->_readDataOnly) {
$excel->removeCellStyleXfByIndex(0); // remove the default style
$excel->removeCellXfByIndex(0); // remove the default style
}
$zip = new ZipArchive;
$zip->open($pFilename);
$rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($rels->Relationship as $rel) {
switch ($rel["Type"]) {
case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
$xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
if ($xmlCore) {
$xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
$xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
$xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
$docProps = $excel->getProperties();
$docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator")));
$docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy")));
$docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created")))); //! respect xsi:type
$docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified")))); //! respect xsi:type
$docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title")));
$docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description")));
$docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject")));
$docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords")));
$docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category")));
}
break;
case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
$dir = dirname($rel["Target"]);
$relsWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/_rels/" . basename($rel["Target"]) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
$relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
$sharedStrings = array();
$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']"));
$xmlStrings = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
if (isset($xmlStrings) && isset($xmlStrings->si)) {
foreach ($xmlStrings->si as $val) {
if (isset($val->t)) {
$sharedStrings[] = PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $val->t );
} elseif (isset($val->r)) {
$sharedStrings[] = $this->_parseRichText($val);
}
}
}
$worksheets = array();
foreach ($relsWorkbook->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
$worksheets[(string) $ele["Id"]] = $ele["Target"];
}
}
$styles = array();
$cellStyles = array();
$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']"));
$xmlStyles = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
$numFmts = null;
if ($xmlStyles && $xmlStyles->numFmts[0]) {
$numFmts = $xmlStyles->numFmts[0];
}
if (isset($numFmts) && !is_null($numFmts)) {
$numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
}
if (!$this->_readDataOnly && $xmlStyles) {
foreach ($xmlStyles->cellXfs->xf as $xf) {
$numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
if ($xf["numFmtId"]) {
if (isset($numFmts)) {
$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
if (isset($tmpNumFmt["formatCode"])) {
$numFmt = (string) $tmpNumFmt["formatCode"];
}
}
if ((int)$xf["numFmtId"] < 164) {
$numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
}
}
//$numFmt = str_replace('mm', 'i', $numFmt);
//$numFmt = str_replace('h', 'H', $numFmt);
$style = (object) array(
"numFmt" => $numFmt,
"font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
"fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
"border" => $xmlStyles->borders->border[intval($xf["borderId"])],
"alignment" => $xf->alignment,
"protection" => $xf->protection,
);
$styles[] = $style;
// add style to cellXf collection
$objStyle = new PHPExcel_Style;
$this->_readStyle($objStyle, $style);
$excel->addCellXf($objStyle);
}
foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
$numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
if ($numFmts && $xf["numFmtId"]) {
$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
if (isset($tmpNumFmt["formatCode"])) {
$numFmt = (string) $tmpNumFmt["formatCode"];
} else if ((int)$xf["numFmtId"] < 165) {
$numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
}
}
$cellStyle = (object) array(
"numFmt" => $numFmt,
"font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
"fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
"border" => $xmlStyles->borders->border[intval($xf["borderId"])],
"alignment" => $xf->alignment,
"protection" => $xf->protection,
);
$cellStyles[] = $cellStyle;
// add style to cellStyleXf collection
$objStyle = new PHPExcel_Style;
$this->_readStyle($objStyle, $cellStyle);
$excel->addCellStyleXf($objStyle);
}
}
$dxfs = array();
if (!$this->_readDataOnly && $xmlStyles) {
if ($xmlStyles->dxfs) {
foreach ($xmlStyles->dxfs->dxf as $dxf) {
$style = new PHPExcel_Style;
$this->_readStyle($style, $dxf);
$dxfs[] = $style;
}
}
if ($xmlStyles->cellStyles)
{
foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
if (intval($cellStyle['builtinId']) == 0) {
if (isset($cellStyles[intval($cellStyle['xfId'])])) {
// Set default style
$style = new PHPExcel_Style;
$this->_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]);
// normal style, currently not using it for anything
}
}
}
}
}
$xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
// Set base date
if ($xmlWorkbook->workbookPr) {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
if (isset($xmlWorkbook->workbookPr['date1904'])) {
$date1904 = (string)$xmlWorkbook->workbookPr['date1904'];
if ($date1904 == "true" || $date1904 == "1") {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
}
}
}
$sheetId = 0; // keep track of new sheet id in final workbook
$oldSheetId = -1; // keep track of old sheet id in final workbook
$countSkippedSheets = 0; // keep track of number of skipped sheets
$mapSheetId = array(); // mapping of sheet ids from old to new
if ($xmlWorkbook->sheets)
{
foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
++$oldSheetId;
// Check if sheet should be skipped
if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) {
++$countSkippedSheets;
$mapSheetId[$oldSheetId] = null;
continue;
}
// Map old sheet id in original workbook to new sheet id.
// They will differ if loadSheetsOnly() is being used
$mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets;
// Load sheet
$docSheet = $excel->createSheet();
$docSheet->setTitle((string) $eleSheet["name"]);
$fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$xmlSheet = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$fileWorksheet")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
$sharedFormulas = array();
if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') {
$docSheet->setSheetState( (string) $eleSheet["state"] );
}
if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) {
$docSheet->getSheetView()->setZoomScale( intval($xmlSheet->sheetViews->sheetView['zoomScale']) );
}
if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) {
$docSheet->getSheetView()->setZoomScaleNormal( intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal']) );
}
if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
$docSheet->setShowGridLines((string)$xmlSheet->sheetViews->sheetView['showGridLines'] ? true : false);
}
if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) {
$docSheet->setShowRowColHeaders((string)$xmlSheet->sheetViews->sheetView['showRowColHeaders'] ? true : false);
}
if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) {
$docSheet->setRightToLeft((string)$xmlSheet->sheetViews->sheetView['rightToLeft'] ? true : false);
}
if (isset($xmlSheet->sheetViews->sheetView->pane)) {
if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) {
$docSheet->freezePane( (string)$xmlSheet->sheetViews->sheetView->pane['topLeftCell'] );
} else {
$xSplit = 0;
$ySplit = 0;
if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) {
$xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']);
}
if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) {
$ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']);
}
$docSheet->freezePaneByColumnAndRow($xSplit, $ySplit);
}
}
if (isset($xmlSheet->sheetViews->sheetView->selection)) {
if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) {
$sqref = (string)$xmlSheet->sheetViews->sheetView->selection['sqref'];
$sqref = explode(' ', $sqref);
$sqref = $sqref[0];
$docSheet->setSelectedCells($sqref);
}
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) {
if (isset($xmlSheet->sheetPr->tabColor['rgb'])) {
$docSheet->getTabColor()->setARGB( (string)$xmlSheet->sheetPr->tabColor['rgb'] );
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && $xmlSheet->sheetPr->outlinePr['summaryRight'] == false) {
$docSheet->setShowSummaryRight(false);
} else {
$docSheet->setShowSummaryRight(true);
}
if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && $xmlSheet->sheetPr->outlinePr['summaryBelow'] == false) {
$docSheet->setShowSummaryBelow(false);
} else {
$docSheet->setShowSummaryBelow(true);
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) {
if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && $xmlSheet->sheetPr->pageSetUpPr['fitToPage'] == false) {
$docSheet->getPageSetup()->setFitToPage(false);
} else {
$docSheet->getPageSetup()->setFitToPage(true);
}
}
if (isset($xmlSheet->sheetFormatPr)) {
if (isset($xmlSheet->sheetFormatPr['customHeight']) && ((string)$xmlSheet->sheetFormatPr['customHeight'] == '1' || strtolower((string)$xmlSheet->sheetFormatPr['customHeight']) == 'true') && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
$docSheet->getDefaultRowDimension()->setRowHeight( (float)$xmlSheet->sheetFormatPr['defaultRowHeight'] );
}
if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
$docSheet->getDefaultColumnDimension()->setWidth( (float)$xmlSheet->sheetFormatPr['defaultColWidth'] );
}
}
if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
foreach ($xmlSheet->cols->col as $col) {
for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
if ($col["style"] && !$this->_readDataOnly) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"]));
}
if ($col["bestFit"]) {
//$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(true);
}
if ($col["hidden"]) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setVisible(false);
}
if ($col["collapsed"]) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setCollapsed(true);
}
if ($col["outlineLevel"] > 0) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
}
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));
if (intval($col["max"]) == 16384) {
break;
}
}
}
}
if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
if ($xmlSheet->printOptions['gridLinesSet'] == 'true' && $xmlSheet->printOptions['gridLinesSet'] == '1') {
$docSheet->setShowGridlines(true);
}
if ($xmlSheet->printOptions['gridLines'] == 'true' || $xmlSheet->printOptions['gridLines'] == '1') {
$docSheet->setPrintGridlines(true);
}
if ($xmlSheet->printOptions['horizontalCentered']) {
$docSheet->getPageSetup()->setHorizontalCentered(true);
}
if ($xmlSheet->printOptions['verticalCentered']) {
$docSheet->getPageSetup()->setVerticalCentered(true);
}
}
if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
foreach ($xmlSheet->sheetData->row as $row) {
if ($row["ht"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
}
if ($row["hidden"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setVisible(false);
}
if ($row["collapsed"]) {
$docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true);
}
if ($row["outlineLevel"] > 0) {
$docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
}
if ($row["s"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"]));
}
foreach ($row->c as $c) {
$r = (string) $c["r"];
$cellDataType = (string) $c["t"];
$value = null;
$calculatedValue = null;
// Read cell?
if (!is_null($this->getReadFilter())) {
$coordinates = PHPExcel_Cell::coordinateFromString($r);
if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
continue;
}
}
// echo '<b>Reading cell '.$coordinates[0].$coordinates[1].'</b><br />';
// print_r($c);
// echo '<br />';
// echo 'Cell Data Type is '.$cellDataType.': ';
//
// Read cell!
switch ($cellDataType) {
case "s":
// echo 'String<br />';
if ((string)$c->v != '') {
$value = $sharedStrings[intval($c->v)];
if ($value instanceof PHPExcel_RichText) {
$value = clone $value;
}
} else {
$value = '';
}
break;
case "b":
// echo 'Boolean<br />';
if (!isset($c->f)) {
$value = $this->_castToBool($c);
} else {
// Formula
$this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToBool');
// echo '$calculatedValue = '.$calculatedValue.'<br />';
}
break;
case "inlineStr":
// echo 'Inline String<br />';
$value = $this->_parseRichText($c->is);
break;
case "e":
// echo 'Error<br />';
if (!isset($c->f)) {
$value = $this->_castToError($c);
} else {
// Formula
$this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToError');
// echo '$calculatedValue = '.$calculatedValue.'<br />';
}
break;
default:
// echo 'Default<br />';
if (!isset($c->f)) {
// echo 'Not a Formula<br />';
$value = $this->_castToString($c);
} else {
// echo 'Treat as Formula<br />';
// Formula
$this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToString');
// echo '$calculatedValue = '.$calculatedValue.'<br />';
}
break;
}
// echo 'Value is '.$value.'<br />';
// Check for numeric values
if (is_numeric($value) && $cellDataType != 's') {
if ($value == (int)$value) $value = (int)$value;
elseif ($value == (float)$value) $value = (float)$value;
elseif ($value == (double)$value) $value = (double)$value;
}
// Rich text?
if ($value instanceof PHPExcel_RichText && $this->_readDataOnly) {
$value = $value->getPlainText();
}
$cell = $docSheet->getCell($r);
// Assign value
if ($cellDataType != '') {
$cell->setValueExplicit($value, $cellDataType);
} else {
$cell->setValue($value);
}
if (!is_null($calculatedValue)) {
$cell->setCalculatedValue($calculatedValue);
}
// Style information?
if ($c["s"] && !$this->_readDataOnly) {
// no style index means 0, it seems
$cell->setXfIndex(isset($styles[intval($c["s"])]) ?
intval($c["s"]) : 0);
}
}
}
}
$conditionals = array();
if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) {
foreach ($xmlSheet->conditionalFormatting as $conditional) {
foreach ($conditional->cfRule as $cfRule) {
if (
(
(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_NONE ||
(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CELLIS ||
(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT ||
(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_EXPRESSION
) && isset($dxfs[intval($cfRule["dxfId"])])
) {
$conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule;
}
}
}
foreach ($conditionals as $ref => $cfRules) {
ksort($cfRules);
$conditionalStyles = array();
foreach ($cfRules as $cfRule) {
$objConditional = new PHPExcel_Style_Conditional();
$objConditional->setConditionType((string)$cfRule["type"]);
$objConditional->setOperatorType((string)$cfRule["operator"]);
if ((string)$cfRule["text"] != '') {
$objConditional->setText((string)$cfRule["text"]);
}
if (count($cfRule->formula) > 1) {
foreach ($cfRule->formula as $formula) {
$objConditional->addCondition((string)$formula);
}
} else {
$objConditional->addCondition((string)$cfRule->formula);
}
$objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]);
$conditionalStyles[] = $objConditional;
}
// Extract all cell references in $ref
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($ref);
foreach ($aReferences as $reference) {
$docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles);
}
}
}
$aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells");
if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
foreach ($aKeys as $key) {
$method = "set" . ucfirst($key);
$docSheet->getProtection()->$method($xmlSheet->sheetProtection[$key] == "true");
}
}
if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
$docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], true);
if ($xmlSheet->protectedRanges->protectedRange) {
foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
$docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
}
}
}
if ($xmlSheet && $xmlSheet->autoFilter && !$this->_readDataOnly) {
$docSheet->setAutoFilter((string) $xmlSheet->autoFilter["ref"]);
}
if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
$docSheet->mergeCells((string) $mergeCell["ref"]);
}
}
if ($xmlSheet && $xmlSheet->pageMargins && !$this->_readDataOnly) {
$docPageMargins = $docSheet->getPageMargins();
$docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"]));
$docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"]));
$docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"]));
$docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"]));
$docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"]));
$docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"]));
}
if ($xmlSheet && $xmlSheet->pageSetup && !$this->_readDataOnly) {
$docPageSetup = $docSheet->getPageSetup();
if (isset($xmlSheet->pageSetup["orientation"])) {
$docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
}
if (isset($xmlSheet->pageSetup["paperSize"])) {
$docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"]));
}
if (isset($xmlSheet->pageSetup["scale"])) {
$docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), false);
}
if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) {
$docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), false);
}
if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) {
$docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), false);
}
if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) &&
((string)$xmlSheet->pageSetup["useFirstPageNumber"] == 'true' || (string)$xmlSheet->pageSetup["useFirstPageNumber"] == '1')) {
$docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"]));
}
}
if ($xmlSheet && $xmlSheet->headerFooter && !$this->_readDataOnly) {
$docHeaderFooter = $docSheet->getHeaderFooter();
if (isset($xmlSheet->headerFooter["differentOddEven"]) &&
((string)$xmlSheet->headerFooter["differentOddEven"] == 'true' || (string)$xmlSheet->headerFooter["differentOddEven"] == '1')) {
$docHeaderFooter->setDifferentOddEven(true);
} else {
$docHeaderFooter->setDifferentOddEven(false);
}
if (isset($xmlSheet->headerFooter["differentFirst"]) &&
((string)$xmlSheet->headerFooter["differentFirst"] == 'true' || (string)$xmlSheet->headerFooter["differentFirst"] == '1')) {
$docHeaderFooter->setDifferentFirst(true);
} else {
$docHeaderFooter->setDifferentFirst(false);
}
if (isset($xmlSheet->headerFooter["scaleWithDoc"]) &&
((string)$xmlSheet->headerFooter["scaleWithDoc"] == 'false' || (string)$xmlSheet->headerFooter["scaleWithDoc"] == '0')) {
$docHeaderFooter->setScaleWithDocument(false);
} else {
$docHeaderFooter->setScaleWithDocument(true);
}
if (isset($xmlSheet->headerFooter["alignWithMargins"]) &&
((string)$xmlSheet->headerFooter["alignWithMargins"] == 'false' || (string)$xmlSheet->headerFooter["alignWithMargins"] == '0')) {
$docHeaderFooter->setAlignWithMargins(false);
} else {
$docHeaderFooter->setAlignWithMargins(true);
}
$docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
$docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
$docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
$docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
$docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
$docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
}
if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->_readDataOnly) {
foreach ($xmlSheet->rowBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak("A$brk[id]", PHPExcel_Worksheet::BREAK_ROW);
}
}
}
if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
foreach ($xmlSheet->colBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak(PHPExcel_Cell::stringFromColumnIndex($brk["id"]) . "1", PHPExcel_Worksheet::BREAK_COLUMN);
}
}
}
if ($xmlSheet && $xmlSheet->dataValidations && !$this->_readDataOnly) {
foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
// Uppercase coordinate
$range = strtoupper($dataValidation["sqref"]);
$rangeSet = explode(' ',$range);
foreach($rangeSet as $range) {
$stRange = $docSheet->shrinkRangeToFit($range);
// Extract all cell references in $range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($stRange);
foreach ($aReferences as $reference) {
// Create validation
$docValidation = $docSheet->getCell($reference)->getDataValidation();
$docValidation->setType((string) $dataValidation["type"]);
$docValidation->setErrorStyle((string) $dataValidation["errorStyle"]);
$docValidation->setOperator((string) $dataValidation["operator"]);
$docValidation->setAllowBlank($dataValidation["allowBlank"] != 0);
$docValidation->setShowDropDown($dataValidation["showDropDown"] == 0);
$docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0);
$docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0);
$docValidation->setErrorTitle((string) $dataValidation["errorTitle"]);
$docValidation->setError((string) $dataValidation["error"]);
$docValidation->setPromptTitle((string) $dataValidation["promptTitle"]);
$docValidation->setPrompt((string) $dataValidation["prompt"]);
$docValidation->setFormula1((string) $dataValidation->formula1);
$docValidation->setFormula2((string) $dataValidation->formula2);
}
}
}
}
// Add hyperlinks
$hyperlinks = array();
if (!$this->_readDataOnly) {
// Locate hyperlink relations
if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
$hyperlinks[(string)$ele["Id"]] = (string)$ele["Target"];
}
}
}
// Loop through hyperlinks
if ($xmlSheet && $xmlSheet->hyperlinks) {
foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
// Link url
$linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
foreach (PHPExcel_Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) {
$cell = $docSheet->getCell( $cellReference );
if (isset($linkRel['id'])) {
$cell->getHyperlink()->setUrl( $hyperlinks[ (string)$linkRel['id'] ] );
}
if (isset($hyperlink['location'])) {
$cell->getHyperlink()->setUrl( 'sheet://' . (string)$hyperlink['location'] );
}
// Tooltip
if (isset($hyperlink['tooltip'])) {
$cell->getHyperlink()->setTooltip( (string)$hyperlink['tooltip'] );
}
}
}
}
}
// Add comments
$comments = array();
$vmlComments = array();
if (!$this->_readDataOnly) {
// Locate comment relations
if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") {
$comments[(string)$ele["Id"]] = (string)$ele["Target"];
}
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
$vmlComments[(string)$ele["Id"]] = (string)$ele["Target"];
}
}
}
// Loop through comments
foreach ($comments as $relName => $relPath) {
// Load comments file
$relPath = PHPExcel_Shared_File::realpath(dirname("$dir/$fileWorksheet") . "/" . $relPath);
$commentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath) );
// Utility variables
$authors = array();
// Loop through authors
foreach ($commentsFile->authors->author as $author) {
$authors[] = (string)$author;
}
// Loop through contents
foreach ($commentsFile->commentList->comment as $comment) {
$docSheet->getComment( (string)$comment['ref'] )->setAuthor( $authors[(string)$comment['authorId']] );
$docSheet->getComment( (string)$comment['ref'] )->setText( $this->_parseRichText($comment->text) );
}
}
// Loop through VML comments
foreach ($vmlComments as $relName => $relPath) {
// Load VML comments file
$relPath = PHPExcel_Shared_File::realpath(dirname("$dir/$fileWorksheet") . "/" . $relPath);
$vmlCommentsFile = simplexml_load_string( $this->_getFromZipArchive($zip, $relPath) );
$vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$shapes = $vmlCommentsFile->xpath('//v:shape');
foreach ($shapes as $shape) {
$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
if (isset($shape['style'])) {
$style = (string)$shape['style'];
$fillColor = strtoupper( substr( (string)$shape['fillcolor'], 1 ) );
$column = null;
$row = null;
$clientData = $shape->xpath('.//x:ClientData');
if (is_array($clientData) && count($clientData) > 0) {
$clientData = $clientData[0];
if ( isset($clientData['ObjectType']) && (string)$clientData['ObjectType'] == 'Note' ) {
$temp = $clientData->xpath('.//x:Row');
if (is_array($temp)) $row = $temp[0];
$temp = $clientData->xpath('.//x:Column');
if (is_array($temp)) $column = $temp[0];
}
}
if (!is_null($column) && !is_null($row)) {
// Set comment properties
$comment = $docSheet->getCommentByColumnAndRow($column, $row + 1);
$comment->getFillColor()->setRGB( $fillColor );
// Parse style
$styleArray = explode(';', str_replace(' ', '', $style));
foreach ($styleArray as $stylePair) {
$stylePair = explode(':', $stylePair);
if ($stylePair[0] == 'margin-left') $comment->setMarginLeft($stylePair[1]);
if ($stylePair[0] == 'margin-top') $comment->setMarginTop($stylePair[1]);
if ($stylePair[0] == 'width') $comment->setWidth($stylePair[1]);
if ($stylePair[0] == 'height') $comment->setHeight($stylePair[1]);
if ($stylePair[0] == 'visibility') $comment->setVisible( $stylePair[1] == 'visible' );
}
}
}
}
}
// Header/footer images
if ($xmlSheet && $xmlSheet->legacyDrawingHF && !$this->_readDataOnly) {
if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
$vmlRelationship = '';
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
$vmlRelationship = self::dir_add("$dir/$fileWorksheet", $ele["Target"]);
}
}
if ($vmlRelationship != '') {
// Fetch linked images
$relsVML = simplexml_load_string($this->_getFromZipArchive($zip, dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels' )); //~ http://schemas.openxmlformats.org/package/2006/relationships");
$drawings = array();
foreach ($relsVML->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
$drawings[(string) $ele["Id"]] = self::dir_add($vmlRelationship, $ele["Target"]);
}
}
// Fetch VML document
$vmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $vmlRelationship));
$vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$hfImages = array();
$shapes = $vmlDrawing->xpath('//v:shape');
foreach ($shapes as $shape) {
$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$imageData = $shape->xpath('//v:imagedata');
$imageData = $imageData[0];
$imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office');
$style = self::toCSSArray( (string)$shape['style'] );
$hfImages[ (string)$shape['id'] ] = new PHPExcel_Worksheet_HeaderFooterDrawing();
if (isset($imageData['title'])) {
$hfImages[ (string)$shape['id'] ]->setName( (string)$imageData['title'] );
}
$hfImages[ (string)$shape['id'] ]->setPath("zip://$pFilename#" . $drawings[(string)$imageData['relid']], false);
$hfImages[ (string)$shape['id'] ]->setResizeProportional(false);
$hfImages[ (string)$shape['id'] ]->setWidth($style['width']);
$hfImages[ (string)$shape['id'] ]->setHeight($style['height']);
$hfImages[ (string)$shape['id'] ]->setOffsetX($style['margin-left']);
$hfImages[ (string)$shape['id'] ]->setOffsetY($style['margin-top']);
$hfImages[ (string)$shape['id'] ]->setResizeProportional(true);
}
$docSheet->getHeaderFooter()->setImages($hfImages);
}
}
}
}
// ----: Make sure drawings and graph are loaded differently!
if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
$drawings = array();
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") {
$drawings[(string) $ele["Id"]] = self::dir_add("$dir/$fileWorksheet", $ele["Target"]);
}
}
if ($xmlSheet->drawing && !$this->_readDataOnly) {
foreach ($xmlSheet->drawing as $drawing) {
$fileDrawing = $drawings[(string) self::array_item($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$relsDrawing = simplexml_load_string($this->_getFromZipArchive($zip, dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
$images = array();
if ($relsDrawing && $relsDrawing->Relationship) {
foreach ($relsDrawing->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
$images[(string) $ele["Id"]] = self::dir_add($fileDrawing, $ele["Target"]);
}
}
}
$xmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $fileDrawing))->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
if ($xmlDrawing->oneCellAnchor) {
foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) {
if ($oneCellAnchor->pic->blipFill) {
$blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new PHPExcel_Worksheet_Drawing;
$objDrawing->setName((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://$pFilename#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1));
$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff));
$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx")));
$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
}
}
}
if ($xmlDrawing->twoCellAnchor) {
foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) {
if ($twoCellAnchor->pic->blipFill) {
$blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new PHPExcel_Worksheet_Drawing;
$objDrawing->setName((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://$pFilename#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff));
$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx")));
$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
}
}
}
}
}
}
// Loop through definedNames
if ($xmlWorkbook->definedNames) {
foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
// Extract range
$extractedRange = (string)$definedName;
$extractedRange = preg_replace('/\'(\w+)\'\!/', '', $extractedRange);
$extractedRange = str_replace('$', '', $extractedRange);
// Valid range?
if (stripos((string)$definedName, '#REF!') !== false || $extractedRange == '') {
continue;
}
// Some definedNames are only applicable if we are on the same sheet...
if ((string)$definedName['localSheetId'] != '' && (string)$definedName['localSheetId'] == $sheetId) {
// Switch on type
switch ((string)$definedName['name']) {
case '_xlnm._FilterDatabase':
$docSheet->setAutoFilter($extractedRange);
break;
case '_xlnm.Print_Titles':
// Split $extractedRange
$extractedRange = explode(',', $extractedRange);
// Set print titles
foreach ($extractedRange as $range) {
$matches = array();
// check for repeating columns, e g. 'A:A' or 'A:D'
if (preg_match('/^([A-Z]+)\:([A-Z]+)$/', $range, $matches)) {
$docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($matches[1], $matches[2]));
}
// check for repeating rows, e.g. '1:1' or '1:5'
elseif (preg_match('/^(\d+)\:(\d+)$/', $range, $matches)) {
$docSheet->getPageSetup()->setRowsToRepeatAtTop(array($matches[1], $matches[2]));
}
}
break;
case '_xlnm.Print_Area':
$range = explode('!', $extractedRange);
$extractedRange = isset($range[1]) ? $range[1] : $range[0];
$docSheet->getPageSetup()->setPrintArea($extractedRange);
break;
default:
break;
}
}
}
}
// Next sheet id
++$sheetId;
}
// Loop through definedNames
if ($xmlWorkbook->definedNames) {
foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
// Extract range
$extractedRange = (string)$definedName;
$extractedRange = preg_replace('/\'(\w+)\'\!/', '', $extractedRange);
$extractedRange = str_replace('$', '', $extractedRange);
// Valid range?
if (stripos((string)$definedName, '#REF!') !== false || $extractedRange == '') {
continue;
}
// Some definedNames are only applicable if we are on the same sheet...
if ((string)$definedName['localSheetId'] != '') {
// Local defined name
// Switch on type
switch ((string)$definedName['name']) {
case '_xlnm._FilterDatabase':
case '_xlnm.Print_Titles':
case '_xlnm.Print_Area':
break;
default:
$range = explode('!', (string)$definedName);
if (count($range) == 2) {
$range[0] = str_replace("''", "'", $range[0]);
$range[0] = str_replace("'", "", $range[0]);
if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) {
$extractedRange = str_replace('$', '', $range[1]);
$scope = $docSheet->getParent()->getSheet((string)$definedName['localSheetId']);
$excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $worksheet, $extractedRange, true, $scope) );
}
}
break;
}
} else if (!isset($definedName['localSheetId'])) {
// "Global" definedNames
$locatedSheet = null;
$extractedSheetName = '';
if (strpos( (string)$definedName, '!' ) !== false) {
// Extract sheet name
$extractedSheetName = PHPExcel_Worksheet::extractSheetTitle( (string)$definedName, true );
$extractedSheetName = $extractedSheetName[0];
// Locate sheet
$locatedSheet = $excel->getSheetByName($extractedSheetName);
// Modify range
$range = explode('!', $extractedRange);
$extractedRange = isset($range[1]) ? $range[1] : $range[0];
}
if (!is_null($locatedSheet)) {
$excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $locatedSheet, $extractedRange, false) );
}
}
}
}
}
if (!$this->_readDataOnly) {
// active sheet index
$activeTab = intval($xmlWorkbook->bookViews->workbookView["activeTab"]); // refers to old sheet index
// keep active sheet index if sheet is still loaded, else first sheet is set as the active
if (isset($mapSheetId[$activeTab]) && $mapSheetId[$activeTab] !== null) {
$excel->setActiveSheetIndex($mapSheetId[$activeTab]);
} else {
if ($excel->getSheetCount() == 0)
{
$excel->createSheet();
}
$excel->setActiveSheetIndex(0);
}
}
break;
}
}
return $excel;
}
public function testSimple()
{
$data = ['ROOT' => ['LEVEL1' => [['LEVEL1_CAPTION' => '1'], ['LEVEL1_CAPTION' => '2']]]];
$namedRange = $this->output->getNamedRange('ROOT');
$this->sut->write($data, $namedRange);
$this->output->setActiveSheetIndexByName('TEMPLATE');
$this->output->removeSheetByIndex($this->output->getActiveSheetIndex());
$excelWriter = \PHPExcel_IOFactory::createWriter($this->output, 'Excel2007');
$excelWriter->save(__DIR__ . '/../metadata/output/verticalrangewritertest.xlsx');
}
/**
* Constructs one Moodle Workbook.
*
* @param string $filename The name of the file
* @param string $type file format type 'Excel5' or 'Excel2007'
*/
public function __construct($filename, $type = 'Excel2007')
{
global $CFG;
require_once "{$CFG->libdir}/phpexcel/PHPExcel.php";
$this->objPHPExcel = new PHPExcel();
$this->objPHPExcel->removeSheetByIndex(0);
$this->filename = $filename;
if (strtolower($type) === 'excel5') {
$this->type = 'Excel5';
} else {
$this->type = 'Excel2007';
}
}
/**
* Excel with one sheet
* @param string|null $filePath
* @param bool $withoutSheets
* @throws PhpExcelException
*/
public function __construct($filePath = null, $withoutSheets = true)
{
try {
$this->excel = $filePath ? PhpOffice_PHPExcel_IOFactory::load($filePath) : new PhpOffice_PHPExcel();
} catch (PhpOffice_PHPExcel_Reader_Exception $ex) {
throw new PhpExcelException('Unable to create excel object.', 0, $e);
}
if ($filePath) {
$this->excelType = PhpOffice_PHPExcel_IOFactory::identify($filePath);
}
if (!$filePath && $withoutSheets) {
$this->excel->removeSheetByIndex();
}
}
/**
* Writes cells to the spreadsheet
* array(
* 1 => array('A1', 'B1', 'C1', 'D1', 'E1'),
* 2 => array('A2', 'B2', 'C2', 'D2', 'E2'),
* 3 => array('A3', 'B3', 'C3', 'D3', 'E3'),
* );
*
* @param array of array( [row] => array([col]=>[value]) ) ie $arr[row][col] => value
* @return void
*/
public function set_data(array $data, $multi_sheet = FALSE)
{
// Single sheet ones can just dump everything to the current sheet
if (!$multi_sheet) {
$sheet = $this->_spreadsheet->getActiveSheet();
$this->set_sheet_data($data, $sheet);
} else {
foreach ($data as $sheetname => $sheetData) {
$sheet = $this->_spreadsheet->createSheet();
$sheet->setTitle($sheetname);
$this->set_sheet_data($sheetData, $sheet);
}
// Now remove the auto-created blank sheet at start of XLS
$this->_spreadsheet->removeSheetByIndex(0);
}
}
/**
* @param null|array $properties
*
* @throws \PHPExcel_Exception
*/
public function start(array $properties = null)
{
$this->object = new \PHPExcel();
$this->object->removeSheetByIndex(0);
$this->attributes['properties'] = $properties ?: [];
if ($properties !== null) {
$this->setProperties($properties, $this->mappings);
}
}
/**
* Re-bind parent
*
* @param PHPExcel $parent
*/
public function rebindParent(PHPExcel $parent)
{
$namedRanges = $this->_parent->getNamedRanges();
foreach ($namedRanges as $namedRange) {
$parent->addNamedRange($namedRange);
}
$this->_parent->removeSheetByIndex($this->_parent->getindex($this));
$this->_parent = $parent;
}
/**
* Re-bind parent
*
* @param PHPExcel $parent
* @return PHPExcel_Worksheet
*/
public function rebindParent(PHPExcel $parent)
{
if ($this->parent !== null) {
$namedRanges = $this->parent->getNamedRanges();
foreach ($namedRanges as $namedRange) {
$parent->addNamedRange($namedRange);
}
$this->parent->removeSheetByIndex($this->parent->getIndex($this));
}
$this->parent = $parent;
return $this;
}
/**
* Retrieve data and split to sheets.
* Example:
* <pre><code>
* <?php
$function = function($offset, $limit)
{
$result = ...
return array(
$result['data'],
$result['total'],
);
};
$xls = new XLSReport();
$xls->splitToSheets($function, $titles);
* ?>
* </code></pre>
* @param function $function
* @param array $titles
* @param PHPExcel_Chart $charts
*/
public function splitToSheets(&$function, &$titles = array(), $charts = null)
{
$offset = 0;
$limit = $this->limitLength;
do {
list($data, $total) = $function($offset, $limit);
if (!empty($data)) {
$this->newSheet($data, $titles, $charts);
}
$offset += $limit;
} while ($offset < $total);
$this->objPHPExcel->removeSheetByIndex($this->objPHPExcel->getSheetCount() - 1);
}
/**
* (non-PHPdoc)
* @see \scipper\Datatransfer\TransferService::generateEmptyDocument()
*/
public function generateDocument(Map $map)
{
if (!class_exists("PHPExcel")) {
throw new GenerationException("dependency 'PHPExcel' not found");
}
$excel = new \PHPExcel();
$excel->removeSheetByIndex(0);
$excel->getProperties()->setCreator($map->getCreator());
$excel->getProperties()->setTitle($map->getTitle());
$protectedStyle = new \PHPExcel_Style();
$protectedStyle->applyFromArray(array("fill" => array("type" => \PHPExcel_Style_Fill::FILL_SOLID, "color" => array("argb" => "55CCCCCC")), "borders" => array("bottom" => array("style" => \PHPExcel_Style_Border::BORDER_THIN), "right" => array("style" => \PHPExcel_Style_Border::BORDER_MEDIUM))));
$i = 0;
foreach ($map->getSheets() as $sheet) {
$active = $excel->addSheet(new \PHPExcel_Worksheet(NULL, $sheet->getTitle()), $i);
$active->getProtection()->setSheet(true);
$active->getStyle("A1:Z30")->getProtection()->setLocked(\PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
foreach ($sheet->getCells() as $cell) {
//Convert content to list format ist necessary
if ($cell->getType() == "select") {
$dataValidation = $active->getCell($cell->getCoord())->getDataValidation();
$dataValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST);
$dataValidation->setAllowBlank(false);
$dataValidation->setShowInputMessage(true);
$dataValidation->setShowDropDown(true);
$dataValidation->setFormula1($cell->getContent());
} else {
$active->setCellValue($cell->getCoord(), $cell->getValue());
}
//Add protection is necessary
if ($cell->isProtected()) {
$active->protectCells($cell->getCoord(), "123");
$active->setSharedStyle($protectedStyle, $cell->getCoord());
// } elseif(!$cell->isProtected() && $active->getProtection()->isProtectionEnabled()) {
// $active->unprotectCells($cell->getCoord());
}
$active->getColumnDimension($cell->getX())->setAutoSize(true);
if (!$cell->isVisible()) {
$active->getColumnDimension($cell->getX())->setVisible(false);
}
}
$i++;
}
$excel->setActiveSheetIndex(0);
$writer = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$filename = $this->documentRoot . $excel->getProperties()->getTitle() . ".xlsx";
$writer->save($filename);
return $filename;
}
/**
* Loads PHPExcel from file
*
* @param string $pFilename
* @throws PHPExcel_Reader_Exception
*/
public function load($pFilename)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
// Initialisations
$excel = new PHPExcel();
$excel->removeSheetByIndex(0);
if (!$this->_readDataOnly) {
$excel->removeCellStyleXfByIndex(0);
// remove the default style
$excel->removeCellXfByIndex(0);
// remove the default style
}
$zip = new ZipArchive();
$zip->open($pFilename);
// Read the theme first, because we need the colour scheme when reading the styles
$wbRels = simplexml_load_string($this->_getFromZipArchive($zip, "xl/_rels/workbook.xml.rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($wbRels->Relationship as $rel) {
switch ($rel["Type"]) {
case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme":
$themeOrderArray = array('lt1', 'dk1', 'lt2', 'dk2');
$themeOrderAdditional = count($themeOrderArray);
$xmlTheme = simplexml_load_string($this->_getFromZipArchive($zip, "xl/{$rel['Target']}"));
if (is_object($xmlTheme)) {
$xmlThemeName = $xmlTheme->attributes();
$xmlTheme = $xmlTheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
$themeName = (string) $xmlThemeName['name'];
$colourScheme = $xmlTheme->themeElements->clrScheme->attributes();
$colourSchemeName = (string) $colourScheme['name'];
$colourScheme = $xmlTheme->themeElements->clrScheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
$themeColours = array();
foreach ($colourScheme as $k => $xmlColour) {
$themePos = array_search($k, $themeOrderArray);
if ($themePos === false) {
$themePos = $themeOrderAdditional++;
}
if (isset($xmlColour->sysClr)) {
$xmlColourData = $xmlColour->sysClr->attributes();
$themeColours[$themePos] = $xmlColourData['lastClr'];
} elseif (isset($xmlColour->srgbClr)) {
$xmlColourData = $xmlColour->srgbClr->attributes();
$themeColours[$themePos] = $xmlColourData['val'];
}
}
self::$_theme = new PHPExcel_Reader_Excel2007_Theme($themeName, $colourSchemeName, $themeColours);
}
break;
}
}
$rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($rels->Relationship as $rel) {
switch ($rel["Type"]) {
case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
$xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
if (is_object($xmlCore)) {
$xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
$xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
$xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
$docProps = $excel->getProperties();
$docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator")));
$docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy")));
$docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created"))));
//! respect xsi:type
$docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified"))));
//! respect xsi:type
$docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title")));
$docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description")));
$docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject")));
$docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords")));
$docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category")));
}
break;
case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties":
$xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
if (is_object($xmlCore)) {
$docProps = $excel->getProperties();
if (isset($xmlCore->Company)) {
$docProps->setCompany((string) $xmlCore->Company);
}
if (isset($xmlCore->Manager)) {
$docProps->setManager((string) $xmlCore->Manager);
}
}
break;
case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties":
$xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
if (is_object($xmlCore)) {
$docProps = $excel->getProperties();
foreach ($xmlCore as $xmlProperty) {
$cellDataOfficeAttributes = $xmlProperty->attributes();
if (isset($cellDataOfficeAttributes['name'])) {
$propertyName = (string) $cellDataOfficeAttributes['name'];
$cellDataOfficeChildren = $xmlProperty->children('http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes');
$attributeType = $cellDataOfficeChildren->getName();
$attributeValue = (string) $cellDataOfficeChildren->{$attributeType};
$attributeValue = PHPExcel_DocumentProperties::convertProperty($attributeValue, $attributeType);
$attributeType = PHPExcel_DocumentProperties::convertPropertyType($attributeType);
$docProps->setCustomProperty($propertyName, $attributeValue, $attributeType);
}
}
}
break;
case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
$dir = dirname($rel["Target"]);
$relsWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/_rels/" . basename($rel["Target"]) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
$sharedStrings = array();
$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']"));
$xmlStrings = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/{$xpath['Target']}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
if (isset($xmlStrings) && isset($xmlStrings->si)) {
foreach ($xmlStrings->si as $val) {
if (isset($val->t)) {
$sharedStrings[] = PHPExcel_Shared_String::ControlCharacterOOXML2PHP((string) $val->t);
} elseif (isset($val->r)) {
$sharedStrings[] = $this->_parseRichText($val);
}
}
}
$worksheets = array();
foreach ($relsWorkbook->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
$worksheets[(string) $ele["Id"]] = $ele["Target"];
}
}
$styles = array();
$cellStyles = array();
$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']"));
$xmlStyles = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/{$xpath['Target']}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
$numFmts = null;
if ($xmlStyles && $xmlStyles->numFmts[0]) {
$numFmts = $xmlStyles->numFmts[0];
}
if (isset($numFmts) && $numFmts !== NULL) {
$numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
}
if (!$this->_readDataOnly && $xmlStyles) {
foreach ($xmlStyles->cellXfs->xf as $xf) {
$numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
if ($xf["numFmtId"]) {
if (isset($numFmts)) {
$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]"));
if (isset($tmpNumFmt["formatCode"])) {
$numFmt = (string) $tmpNumFmt["formatCode"];
}
}
if ((int) $xf["numFmtId"] < 164) {
$numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int) $xf["numFmtId"]);
}
}
//$numFmt = str_replace('mm', 'i', $numFmt);
//$numFmt = str_replace('h', 'H', $numFmt);
$style = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection);
$styles[] = $style;
// add style to cellXf collection
$objStyle = new PHPExcel_Style();
self::_readStyle($objStyle, $style);
$excel->addCellXf($objStyle);
}
foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
$numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
if ($numFmts && $xf["numFmtId"]) {
$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]"));
if (isset($tmpNumFmt["formatCode"])) {
$numFmt = (string) $tmpNumFmt["formatCode"];
} else {
if ((int) $xf["numFmtId"] < 165) {
$numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int) $xf["numFmtId"]);
}
}
}
$cellStyle = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection);
$cellStyles[] = $cellStyle;
// add style to cellStyleXf collection
$objStyle = new PHPExcel_Style();
self::_readStyle($objStyle, $cellStyle);
$excel->addCellStyleXf($objStyle);
}
}
$dxfs = array();
if (!$this->_readDataOnly && $xmlStyles) {
// Conditional Styles
if ($xmlStyles->dxfs) {
foreach ($xmlStyles->dxfs->dxf as $dxf) {
$style = new PHPExcel_Style(FALSE, TRUE);
self::_readStyle($style, $dxf);
$dxfs[] = $style;
}
}
// Cell Styles
if ($xmlStyles->cellStyles) {
foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
if (intval($cellStyle['builtinId']) == 0) {
if (isset($cellStyles[intval($cellStyle['xfId'])])) {
// Set default style
$style = new PHPExcel_Style();
self::_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]);
// normal style, currently not using it for anything
}
}
}
}
}
$xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
// Set base date
if ($xmlWorkbook->workbookPr) {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
if (isset($xmlWorkbook->workbookPr['date1904'])) {
if (self::boolean((string) $xmlWorkbook->workbookPr['date1904'])) {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
}
}
}
$sheetId = 0;
// keep track of new sheet id in final workbook
$oldSheetId = -1;
// keep track of old sheet id in final workbook
$countSkippedSheets = 0;
// keep track of number of skipped sheets
$mapSheetId = array();
// mapping of sheet ids from old to new
$charts = $chartDetails = array();
if ($xmlWorkbook->sheets) {
foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
++$oldSheetId;
// Check if sheet should be skipped
if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) {
++$countSkippedSheets;
$mapSheetId[$oldSheetId] = null;
continue;
}
// Map old sheet id in original workbook to new sheet id.
// They will differ if loadSheetsOnly() is being used
$mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets;
// Load sheet
$docSheet = $excel->createSheet();
// Use false for $updateFormulaCellReferences to prevent adjustment of worksheet
// references in formula cells... during the load, all formulae should be correct,
// and we're simply bringing the worksheet name in line with the formula, not the
// reverse
$docSheet->setTitle((string) $eleSheet["name"], false);
$fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$xmlSheet = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/{$fileWorksheet}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
$sharedFormulas = array();
if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') {
$docSheet->setSheetState((string) $eleSheet["state"]);
}
if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) {
$docSheet->getSheetView()->setZoomScale(intval($xmlSheet->sheetViews->sheetView['zoomScale']));
}
if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) {
$docSheet->getSheetView()->setZoomScaleNormal(intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal']));
}
if (isset($xmlSheet->sheetViews->sheetView['view'])) {
$docSheet->getSheetView()->setView((string) $xmlSheet->sheetViews->sheetView['view']);
}
if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
$docSheet->setShowGridLines(self::boolean((string) $xmlSheet->sheetViews->sheetView['showGridLines']));
}
if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) {
$docSheet->setShowRowColHeaders(self::boolean((string) $xmlSheet->sheetViews->sheetView['showRowColHeaders']));
}
if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) {
$docSheet->setRightToLeft(self::boolean((string) $xmlSheet->sheetViews->sheetView['rightToLeft']));
}
if (isset($xmlSheet->sheetViews->sheetView->pane)) {
if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) {
$docSheet->freezePane((string) $xmlSheet->sheetViews->sheetView->pane['topLeftCell']);
} else {
$xSplit = 0;
$ySplit = 0;
if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) {
$xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']);
}
if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) {
$ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']);
}
$docSheet->freezePaneByColumnAndRow($xSplit, $ySplit);
}
}
if (isset($xmlSheet->sheetViews->sheetView->selection)) {
if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) {
$sqref = (string) $xmlSheet->sheetViews->sheetView->selection['sqref'];
$sqref = explode(' ', $sqref);
$sqref = $sqref[0];
$docSheet->setSelectedCells($sqref);
}
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) {
if (isset($xmlSheet->sheetPr->tabColor['rgb'])) {
$docSheet->getTabColor()->setARGB((string) $xmlSheet->sheetPr->tabColor['rgb']);
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && !self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryRight'])) {
$docSheet->setShowSummaryRight(FALSE);
} else {
$docSheet->setShowSummaryRight(TRUE);
}
if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && !self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryBelow'])) {
$docSheet->setShowSummaryBelow(FALSE);
} else {
$docSheet->setShowSummaryBelow(TRUE);
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) {
if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && !self::boolean((string) $xmlSheet->sheetPr->pageSetUpPr['fitToPage'])) {
$docSheet->getPageSetup()->setFitToPage(FALSE);
} else {
$docSheet->getPageSetup()->setFitToPage(TRUE);
}
}
if (isset($xmlSheet->sheetFormatPr)) {
if (isset($xmlSheet->sheetFormatPr['customHeight']) && self::boolean((string) $xmlSheet->sheetFormatPr['customHeight']) && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
$docSheet->getDefaultRowDimension()->setRowHeight((double) $xmlSheet->sheetFormatPr['defaultRowHeight']);
}
if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
$docSheet->getDefaultColumnDimension()->setWidth((double) $xmlSheet->sheetFormatPr['defaultColWidth']);
}
if (isset($xmlSheet->sheetFormatPr['zeroHeight']) && (string) $xmlSheet->sheetFormatPr['zeroHeight'] == '1') {
$docSheet->getDefaultRowDimension()->setzeroHeight(true);
}
}
if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
foreach ($xmlSheet->cols->col as $col) {
for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
if ($col["style"] && !$this->_readDataOnly) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"]));
}
if (self::boolean($col["bestFit"])) {
//$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(TRUE);
}
if (self::boolean($col["hidden"])) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setVisible(FALSE);
}
if (self::boolean($col["collapsed"])) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setCollapsed(TRUE);
}
if ($col["outlineLevel"] > 0) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
}
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));
if (intval($col["max"]) == 16384) {
break;
}
}
}
}
if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
if (self::boolean((string) $xmlSheet->printOptions['gridLinesSet'])) {
$docSheet->setShowGridlines(TRUE);
}
if (self::boolean((string) $xmlSheet->printOptions['gridLines'])) {
$docSheet->setPrintGridlines(TRUE);
}
if (self::boolean((string) $xmlSheet->printOptions['horizontalCentered'])) {
$docSheet->getPageSetup()->setHorizontalCentered(TRUE);
}
if (self::boolean((string) $xmlSheet->printOptions['verticalCentered'])) {
$docSheet->getPageSetup()->setVerticalCentered(TRUE);
}
}
if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
foreach ($xmlSheet->sheetData->row as $row) {
if ($row["ht"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
}
if (self::boolean($row["hidden"]) && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setVisible(FALSE);
}
if (self::boolean($row["collapsed"])) {
$docSheet->getRowDimension(intval($row["r"]))->setCollapsed(TRUE);
}
if ($row["outlineLevel"] > 0) {
$docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
}
if ($row["s"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"]));
}
foreach ($row->c as $c) {
$r = (string) $c["r"];
$cellDataType = (string) $c["t"];
$value = null;
$calculatedValue = null;
// Read cell?
if ($this->getReadFilter() !== NULL) {
$coordinates = PHPExcel_Cell::coordinateFromString($r);
if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
continue;
}
}
// echo '<b>Reading cell '.$coordinates[0].$coordinates[1].'</b><br />';
// print_r($c);
// echo '<br />';
// echo 'Cell Data Type is '.$cellDataType.': ';
//
// Read cell!
switch ($cellDataType) {
case "s":
// echo 'String<br />';
if ((string) $c->v != '') {
$value = $sharedStrings[intval($c->v)];
if ($value instanceof PHPExcel_RichText) {
$value = clone $value;
}
} else {
$value = '';
}
break;
case "b":
// echo 'Boolean<br />';
if (!isset($c->f)) {
$value = self::_castToBool($c);
} else {
// Formula
$this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToBool');
if (isset($c->f['t'])) {
$att = array();
$att = $c->f;
$docSheet->getCell($r)->setFormulaAttributes($att);
}
// echo '$calculatedValue = '.$calculatedValue.'<br />';
}
break;
case "inlineStr":
// echo 'Inline String<br />';
$value = $this->_parseRichText($c->is);
break;
case "e":
// echo 'Error<br />';
if (!isset($c->f)) {
$value = self::_castToError($c);
} else {
// Formula
$this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToError');
// echo '$calculatedValue = '.$calculatedValue.'<br />';
}
break;
default:
// echo 'Default<br />';
if (!isset($c->f)) {
// echo 'Not a Formula<br />';
$value = self::_castToString($c);
} else {
// echo 'Treat as Formula<br />';
// Formula
$this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToString');
// echo '$calculatedValue = '.$calculatedValue.'<br />';
}
break;
}
// echo 'Value is '.$value.'<br />';
// Check for numeric values
if (is_numeric($value) && $cellDataType != 's') {
if ($value == (int) $value) {
$value = (int) $value;
} elseif ($value == (double) $value) {
$value = (double) $value;
} elseif ($value == (double) $value) {
$value = (double) $value;
}
}
// Rich text?
if ($value instanceof PHPExcel_RichText && $this->_readDataOnly) {
$value = $value->getPlainText();
}
$cell = $docSheet->getCell($r);
// Assign value
if ($cellDataType != '') {
$cell->setValueExplicit($value, $cellDataType);
} else {
$cell->setValue($value);
}
if ($calculatedValue !== NULL) {
$cell->setCalculatedValue($calculatedValue);
}
// Style information?
if ($c["s"] && !$this->_readDataOnly) {
// no style index means 0, it seems
$cell->setXfIndex(isset($styles[intval($c["s"])]) ? intval($c["s"]) : 0);
}
}
}
}
$conditionals = array();
if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) {
foreach ($xmlSheet->conditionalFormatting as $conditional) {
foreach ($conditional->cfRule as $cfRule) {
if (((string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_NONE || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CELLIS || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_EXPRESSION) && isset($dxfs[intval($cfRule["dxfId"])])) {
$conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule;
}
}
}
foreach ($conditionals as $ref => $cfRules) {
ksort($cfRules);
$conditionalStyles = array();
foreach ($cfRules as $cfRule) {
$objConditional = new PHPExcel_Style_Conditional();
$objConditional->setConditionType((string) $cfRule["type"]);
$objConditional->setOperatorType((string) $cfRule["operator"]);
if ((string) $cfRule["text"] != '') {
$objConditional->setText((string) $cfRule["text"]);
}
if (count($cfRule->formula) > 1) {
foreach ($cfRule->formula as $formula) {
$objConditional->addCondition((string) $formula);
}
} else {
$objConditional->addCondition((string) $cfRule->formula);
}
$objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]);
$conditionalStyles[] = $objConditional;
}
// Extract all cell references in $ref
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($ref);
foreach ($aReferences as $reference) {
$docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles);
}
}
}
$aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells");
if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
foreach ($aKeys as $key) {
$method = "set" . ucfirst($key);
$docSheet->getProtection()->{$method}(self::boolean((string) $xmlSheet->sheetProtection[$key]));
}
}
if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
$docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], TRUE);
if ($xmlSheet->protectedRanges->protectedRange) {
foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
$docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
}
}
}
if ($xmlSheet && $xmlSheet->autoFilter && !$this->_readDataOnly) {
$autoFilter = $docSheet->getAutoFilter();
$autoFilter->setRange((string) $xmlSheet->autoFilter["ref"]);
foreach ($xmlSheet->autoFilter->filterColumn as $filterColumn) {
$column = $autoFilter->getColumnByOffset((int) $filterColumn["colId"]);
// Check for standard filters
if ($filterColumn->filters) {
$column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER);
$filters = $filterColumn->filters;
if (isset($filters["blank"]) && $filters["blank"] == 1) {
$column->createRule()->setRule(NULL, '')->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER);
}
// Standard filters are always an OR join, so no join rule needs to be set
// Entries can be either filter elements
foreach ($filters->filter as $filterRule) {
$column->createRule()->setRule(NULL, (string) $filterRule["val"])->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_FILTER);
}
// Or Date Group elements
foreach ($filters->dateGroupItem as $dateGroupItem) {
$column->createRule()->setRule(NULL, array('year' => (string) $dateGroupItem["year"], 'month' => (string) $dateGroupItem["month"], 'day' => (string) $dateGroupItem["day"], 'hour' => (string) $dateGroupItem["hour"], 'minute' => (string) $dateGroupItem["minute"], 'second' => (string) $dateGroupItem["second"]), (string) $dateGroupItem["dateTimeGrouping"])->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP);
}
}
// Check for custom filters
if ($filterColumn->customFilters) {
$column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER);
$customFilters = $filterColumn->customFilters;
// Custom filters can an AND or an OR join;
// and there should only ever be one or two entries
if (isset($customFilters["and"]) && $customFilters["and"] == 1) {
$column->setJoin(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND);
}
foreach ($customFilters->customFilter as $filterRule) {
$column->createRule()->setRule((string) $filterRule["operator"], (string) $filterRule["val"])->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER);
}
}
// Check for dynamic filters
if ($filterColumn->dynamicFilter) {
$column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER);
// We should only ever have one dynamic filter
foreach ($filterColumn->dynamicFilter as $filterRule) {
$column->createRule()->setRule(NULL, (string) $filterRule["val"], (string) $filterRule["type"])->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER);
if (isset($filterRule["val"])) {
$column->setAttribute('val', (string) $filterRule["val"]);
}
if (isset($filterRule["maxVal"])) {
$column->setAttribute('maxVal', (string) $filterRule["maxVal"]);
}
}
}
// Check for dynamic filters
if ($filterColumn->top10) {
$column->setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER);
// We should only ever have one top10 filter
foreach ($filterColumn->top10 as $filterRule) {
$column->createRule()->setRule(isset($filterRule["percent"]) && $filterRule["percent"] == 1 ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE, (string) $filterRule["val"], isset($filterRule["top"]) && $filterRule["top"] == 1 ? PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP : PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM)->setRuleType(PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_TOPTENFILTER);
}
}
}
}
if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
$mergeRef = (string) $mergeCell["ref"];
if (strpos($mergeRef, ':') !== FALSE) {
$docSheet->mergeCells((string) $mergeCell["ref"]);
}
}
}
if ($xmlSheet && $xmlSheet->pageMargins && !$this->_readDataOnly) {
$docPageMargins = $docSheet->getPageMargins();
$docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"]));
$docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"]));
$docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"]));
$docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"]));
$docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"]));
$docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"]));
}
if ($xmlSheet && $xmlSheet->pageSetup && !$this->_readDataOnly) {
$docPageSetup = $docSheet->getPageSetup();
if (isset($xmlSheet->pageSetup["orientation"])) {
$docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
}
if (isset($xmlSheet->pageSetup["paperSize"])) {
$docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"]));
}
if (isset($xmlSheet->pageSetup["scale"])) {
$docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), FALSE);
}
if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) {
$docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), FALSE);
}
if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) {
$docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), FALSE);
}
if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) && self::boolean((string) $xmlSheet->pageSetup["useFirstPageNumber"])) {
$docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"]));
}
}
if ($xmlSheet && $xmlSheet->headerFooter && !$this->_readDataOnly) {
$docHeaderFooter = $docSheet->getHeaderFooter();
if (isset($xmlSheet->headerFooter["differentOddEven"]) && self::boolean((string) $xmlSheet->headerFooter["differentOddEven"])) {
$docHeaderFooter->setDifferentOddEven(TRUE);
} else {
$docHeaderFooter->setDifferentOddEven(FALSE);
}
if (isset($xmlSheet->headerFooter["differentFirst"]) && self::boolean((string) $xmlSheet->headerFooter["differentFirst"])) {
$docHeaderFooter->setDifferentFirst(TRUE);
} else {
$docHeaderFooter->setDifferentFirst(FALSE);
}
if (isset($xmlSheet->headerFooter["scaleWithDoc"]) && !self::boolean((string) $xmlSheet->headerFooter["scaleWithDoc"])) {
$docHeaderFooter->setScaleWithDocument(FALSE);
} else {
$docHeaderFooter->setScaleWithDocument(TRUE);
}
if (isset($xmlSheet->headerFooter["alignWithMargins"]) && !self::boolean((string) $xmlSheet->headerFooter["alignWithMargins"])) {
$docHeaderFooter->setAlignWithMargins(FALSE);
} else {
$docHeaderFooter->setAlignWithMargins(TRUE);
}
$docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
$docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
$docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
$docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
$docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
$docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
}
if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->_readDataOnly) {
foreach ($xmlSheet->rowBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak("A{$brk['id']}", PHPExcel_Worksheet::BREAK_ROW);
}
}
}
if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
foreach ($xmlSheet->colBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak(PHPExcel_Cell::stringFromColumnIndex((string) $brk["id"]) . "1", PHPExcel_Worksheet::BREAK_COLUMN);
}
}
}
if ($xmlSheet && $xmlSheet->dataValidations && !$this->_readDataOnly) {
foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
// Uppercase coordinate
$range = strtoupper($dataValidation["sqref"]);
$rangeSet = explode(' ', $range);
foreach ($rangeSet as $range) {
$stRange = $docSheet->shrinkRangeToFit($range);
// Extract all cell references in $range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($stRange);
foreach ($aReferences as $reference) {
// Create validation
$docValidation = $docSheet->getCell($reference)->getDataValidation();
$docValidation->setType((string) $dataValidation["type"]);
$docValidation->setErrorStyle((string) $dataValidation["errorStyle"]);
$docValidation->setOperator((string) $dataValidation["operator"]);
$docValidation->setAllowBlank($dataValidation["allowBlank"] != 0);
$docValidation->setShowDropDown($dataValidation["showDropDown"] == 0);
$docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0);
$docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0);
$docValidation->setErrorTitle((string) $dataValidation["errorTitle"]);
$docValidation->setError((string) $dataValidation["error"]);
$docValidation->setPromptTitle((string) $dataValidation["promptTitle"]);
$docValidation->setPrompt((string) $dataValidation["prompt"]);
$docValidation->setFormula1((string) $dataValidation->formula1);
$docValidation->setFormula2((string) $dataValidation->formula2);
}
}
}
}
// Add hyperlinks
$hyperlinks = array();
if (!$this->_readDataOnly) {
// Locate hyperlink relations
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
$hyperlinks[(string) $ele["Id"]] = (string) $ele["Target"];
}
}
}
// Loop through hyperlinks
if ($xmlSheet && $xmlSheet->hyperlinks) {
foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
// Link url
$linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
foreach (PHPExcel_Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) {
$cell = $docSheet->getCell($cellReference);
if (isset($linkRel['id'])) {
$hyperlinkUrl = $hyperlinks[(string) $linkRel['id']];
if (isset($hyperlink['location'])) {
$hyperlinkUrl .= '#' . (string) $hyperlink['location'];
}
$cell->getHyperlink()->setUrl($hyperlinkUrl);
} elseif (isset($hyperlink['location'])) {
$cell->getHyperlink()->setUrl('sheet://' . (string) $hyperlink['location']);
}
// Tooltip
if (isset($hyperlink['tooltip'])) {
$cell->getHyperlink()->setTooltip((string) $hyperlink['tooltip']);
}
}
}
}
}
// Add comments
$comments = array();
$vmlComments = array();
if (!$this->_readDataOnly) {
// Locate comment relations
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") {
$comments[(string) $ele["Id"]] = (string) $ele["Target"];
}
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
$vmlComments[(string) $ele["Id"]] = (string) $ele["Target"];
}
}
}
// Loop through comments
foreach ($comments as $relName => $relPath) {
// Load comments file
$relPath = PHPExcel_Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath);
$commentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath));
// Utility variables
$authors = array();
// Loop through authors
foreach ($commentsFile->authors->author as $author) {
$authors[] = (string) $author;
}
// Loop through contents
foreach ($commentsFile->commentList->comment as $comment) {
$docSheet->getComment((string) $comment['ref'])->setAuthor($authors[(string) $comment['authorId']]);
$docSheet->getComment((string) $comment['ref'])->setText($this->_parseRichText($comment->text));
}
}
// Loop through VML comments
foreach ($vmlComments as $relName => $relPath) {
// Load VML comments file
$relPath = PHPExcel_Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath);
$vmlCommentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath));
$vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$shapes = $vmlCommentsFile->xpath('//v:shape');
foreach ($shapes as $shape) {
$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
if (isset($shape['style'])) {
$style = (string) $shape['style'];
$fillColor = strtoupper(substr((string) $shape['fillcolor'], 1));
$column = null;
$row = null;
$clientData = $shape->xpath('.//x:ClientData');
if (is_array($clientData) && !empty($clientData)) {
$clientData = $clientData[0];
if (isset($clientData['ObjectType']) && (string) $clientData['ObjectType'] == 'Note') {
$temp = $clientData->xpath('.//x:Row');
if (is_array($temp)) {
$row = $temp[0];
}
$temp = $clientData->xpath('.//x:Column');
if (is_array($temp)) {
$column = $temp[0];
}
}
}
if ($column !== NULL && $row !== NULL) {
// Set comment properties
$comment = $docSheet->getCommentByColumnAndRow((string) $column, $row + 1);
$comment->getFillColor()->setRGB($fillColor);
// Parse style
$styleArray = explode(';', str_replace(' ', '', $style));
foreach ($styleArray as $stylePair) {
$stylePair = explode(':', $stylePair);
if ($stylePair[0] == 'margin-left') {
$comment->setMarginLeft($stylePair[1]);
}
if ($stylePair[0] == 'margin-top') {
$comment->setMarginTop($stylePair[1]);
}
if ($stylePair[0] == 'width') {
$comment->setWidth($stylePair[1]);
}
if ($stylePair[0] == 'height') {
$comment->setHeight($stylePair[1]);
}
if ($stylePair[0] == 'visibility') {
$comment->setVisible($stylePair[1] == 'visible');
}
}
}
}
}
}
// Header/footer images
if ($xmlSheet && $xmlSheet->legacyDrawingHF && !$this->_readDataOnly) {
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$vmlRelationship = '';
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
$vmlRelationship = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]);
}
}
if ($vmlRelationship != '') {
// Fetch linked images
$relsVML = simplexml_load_string($this->_getFromZipArchive($zip, dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels'));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$drawings = array();
foreach ($relsVML->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
$drawings[(string) $ele["Id"]] = self::dir_add($vmlRelationship, $ele["Target"]);
}
}
// Fetch VML document
$vmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $vmlRelationship));
$vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$hfImages = array();
$shapes = $vmlDrawing->xpath('//v:shape');
foreach ($shapes as $shape) {
$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$imageData = $shape->xpath('//v:imagedata');
$imageData = $imageData[0];
$imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office');
$style = self::toCSSArray((string) $shape['style']);
$hfImages[(string) $shape['id']] = new PHPExcel_Worksheet_HeaderFooterDrawing();
if (isset($imageData['title'])) {
$hfImages[(string) $shape['id']]->setName((string) $imageData['title']);
}
$hfImages[(string) $shape['id']]->setPath("zip://" . PHPExcel_Shared_File::realpath($pFilename) . "#" . $drawings[(string) $imageData['relid']], false);
$hfImages[(string) $shape['id']]->setResizeProportional(false);
$hfImages[(string) $shape['id']]->setWidth($style['width']);
$hfImages[(string) $shape['id']]->setHeight($style['height']);
$hfImages[(string) $shape['id']]->setOffsetX($style['margin-left']);
$hfImages[(string) $shape['id']]->setOffsetY($style['margin-top']);
$hfImages[(string) $shape['id']]->setResizeProportional(true);
}
$docSheet->getHeaderFooter()->setImages($hfImages);
}
}
}
}
// TODO: Autoshapes from twoCellAnchors!
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$drawings = array();
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") {
$drawings[(string) $ele["Id"]] = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]);
}
}
if ($xmlSheet->drawing && !$this->_readDataOnly) {
foreach ($xmlSheet->drawing as $drawing) {
$fileDrawing = $drawings[(string) self::array_item($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$relsDrawing = simplexml_load_string($this->_getFromZipArchive($zip, dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$images = array();
if ($relsDrawing && $relsDrawing->Relationship) {
foreach ($relsDrawing->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
$images[(string) $ele["Id"]] = self::dir_add($fileDrawing, $ele["Target"]);
} elseif ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart") {
if ($this->_includeCharts) {
$charts[self::dir_add($fileDrawing, $ele["Target"])] = array('id' => (string) $ele["Id"], 'sheet' => $docSheet->getTitle());
}
}
}
}
$xmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $fileDrawing))->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
if ($xmlDrawing->oneCellAnchor) {
foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) {
if ($oneCellAnchor->pic->blipFill) {
$blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://" . PHPExcel_Shared_File::realpath($pFilename) . "#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1));
$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff));
$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx")));
$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
} else {
// ? Can charts be positioned with a oneCellAnchor ?
$coordinates = PHPExcel_Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1);
$offsetX = PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff);
$offsetY = PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff);
$width = PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx"));
$height = PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy"));
}
}
}
if ($xmlDrawing->twoCellAnchor) {
foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) {
if ($twoCellAnchor->pic->blipFill) {
$blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://" . PHPExcel_Shared_File::realpath($pFilename) . "#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff));
$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx")));
$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
} elseif ($this->_includeCharts && $twoCellAnchor->graphicFrame) {
$fromCoordinate = PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1);
$fromOffsetX = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff);
$fromOffsetY = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff);
$toCoordinate = PHPExcel_Cell::stringFromColumnIndex((string) $twoCellAnchor->to->col) . ($twoCellAnchor->to->row + 1);
$toOffsetX = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->to->colOff);
$toOffsetY = PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->to->rowOff);
$graphic = $twoCellAnchor->graphicFrame->children("http://schemas.openxmlformats.org/drawingml/2006/main")->graphic;
$chartRef = $graphic->graphicData->children("http://schemas.openxmlformats.org/drawingml/2006/chart")->chart;
$thisChart = (string) $chartRef->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships");
$chartDetails[$docSheet->getTitle() . '!' . $thisChart] = array('fromCoordinate' => $fromCoordinate, 'fromOffsetX' => $fromOffsetX, 'fromOffsetY' => $fromOffsetY, 'toCoordinate' => $toCoordinate, 'toOffsetX' => $toOffsetX, 'toOffsetY' => $toOffsetY, 'worksheetTitle' => $docSheet->getTitle());
}
}
}
}
}
}
// Loop through definedNames
if ($xmlWorkbook->definedNames) {
foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
// Extract range
$extractedRange = (string) $definedName;
$extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange);
if (($spos = strpos($extractedRange, '!')) !== false) {
$extractedRange = substr($extractedRange, 0, $spos) . str_replace('$', '', substr($extractedRange, $spos));
} else {
$extractedRange = str_replace('$', '', $extractedRange);
}
// Valid range?
if (stripos((string) $definedName, '#REF!') !== FALSE || $extractedRange == '') {
continue;
}
// Some definedNames are only applicable if we are on the same sheet...
if ((string) $definedName['localSheetId'] != '' && (string) $definedName['localSheetId'] == $sheetId) {
// Switch on type
switch ((string) $definedName['name']) {
case '_xlnm._FilterDatabase':
if ((string) $definedName['hidden'] !== '1') {
$docSheet->getAutoFilter()->setRange($extractedRange);
}
break;
case '_xlnm.Print_Titles':
// Split $extractedRange
$extractedRange = explode(',', $extractedRange);
// Set print titles
foreach ($extractedRange as $range) {
$matches = array();
// check for repeating columns, e g. 'A:A' or 'A:D'
if (preg_match('/^([A-Z]+)\\:([A-Z]+)$/', $range, $matches)) {
$docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($matches[1], $matches[2]));
} elseif (preg_match('/^(\\d+)\\:(\\d+)$/', $range, $matches)) {
$docSheet->getPageSetup()->setRowsToRepeatAtTop(array($matches[1], $matches[2]));
}
}
break;
case '_xlnm.Print_Area':
$rangeSets = explode(',', $extractedRange);
// FIXME: what if sheetname contains comma?
$newRangeSets = array();
foreach ($rangeSets as $rangeSet) {
$range = explode('!', $rangeSet);
// FIXME: what if sheetname contains exclamation mark?
$rangeSet = isset($range[1]) ? $range[1] : $range[0];
if (strpos($rangeSet, ':') === FALSE) {
$rangeSet = $rangeSet . ':' . $rangeSet;
}
$newRangeSets[] = str_replace('$', '', $rangeSet);
}
$docSheet->getPageSetup()->setPrintArea(implode(',', $newRangeSets));
break;
default:
break;
}
}
}
}
// Next sheet id
++$sheetId;
}
// Loop through definedNames
if ($xmlWorkbook->definedNames) {
foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
// Extract range
$extractedRange = (string) $definedName;
$extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange);
if (($spos = strpos($extractedRange, '!')) !== false) {
$extractedRange = substr($extractedRange, 0, $spos) . str_replace('$', '', substr($extractedRange, $spos));
} else {
$extractedRange = str_replace('$', '', $extractedRange);
}
// Valid range?
if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') {
continue;
}
// Some definedNames are only applicable if we are on the same sheet...
if ((string) $definedName['localSheetId'] != '') {
// Local defined name
// Switch on type
switch ((string) $definedName['name']) {
case '_xlnm._FilterDatabase':
case '_xlnm.Print_Titles':
case '_xlnm.Print_Area':
break;
default:
if ($mapSheetId[(int) $definedName['localSheetId']] !== null) {
$range = explode('!', (string) $definedName);
if (count($range) == 2) {
$range[0] = str_replace("''", "'", $range[0]);
$range[0] = str_replace("'", "", $range[0]);
if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) {
$extractedRange = str_replace('$', '', $range[1]);
$scope = $docSheet->getParent()->getSheet($mapSheetId[(int) $definedName['localSheetId']]);
$excel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $worksheet, $extractedRange, true, $scope));
}
}
}
break;
}
} else {
if (!isset($definedName['localSheetId'])) {
// "Global" definedNames
$locatedSheet = null;
$extractedSheetName = '';
if (strpos((string) $definedName, '!') !== false) {
// Extract sheet name
$extractedSheetName = PHPExcel_Worksheet::extractSheetTitle((string) $definedName, true);
$extractedSheetName = $extractedSheetName[0];
// Locate sheet
$locatedSheet = $excel->getSheetByName($extractedSheetName);
// Modify range
$range = explode('!', $extractedRange);
$extractedRange = isset($range[1]) ? $range[1] : $range[0];
}
if ($locatedSheet !== NULL) {
$excel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $locatedSheet, $extractedRange, false));
}
}
}
}
}
}
if (!$this->_readDataOnly || !empty($this->_loadSheetsOnly)) {
// active sheet index
$activeTab = intval($xmlWorkbook->bookViews->workbookView["activeTab"]);
// refers to old sheet index
// keep active sheet index if sheet is still loaded, else first sheet is set as the active
if (isset($mapSheetId[$activeTab]) && $mapSheetId[$activeTab] !== null) {
$excel->setActiveSheetIndex($mapSheetId[$activeTab]);
} else {
if ($excel->getSheetCount() == 0) {
$excel->createSheet();
}
$excel->setActiveSheetIndex(0);
}
}
break;
}
}
if (!$this->_readDataOnly) {
$contentTypes = simplexml_load_string($this->_getFromZipArchive($zip, "[Content_Types].xml"));
foreach ($contentTypes->Override as $contentType) {
switch ($contentType["ContentType"]) {
case "application/vnd.openxmlformats-officedocument.drawingml.chart+xml":
if ($this->_includeCharts) {
$chartEntryRef = ltrim($contentType['PartName'], '/');
$chartElements = simplexml_load_string($this->_getFromZipArchive($zip, $chartEntryRef));
$objChart = PHPExcel_Reader_Excel2007_Chart::readChart($chartElements, basename($chartEntryRef, '.xml'));
// echo 'Chart ',$chartEntryRef,'<br />';
// var_dump($charts[$chartEntryRef]);
//
if (isset($charts[$chartEntryRef])) {
$chartPositionRef = $charts[$chartEntryRef]['sheet'] . '!' . $charts[$chartEntryRef]['id'];
// echo 'Position Ref ',$chartPositionRef,'<br />';
if (isset($chartDetails[$chartPositionRef])) {
// var_dump($chartDetails[$chartPositionRef]);
$excel->getSheetByName($charts[$chartEntryRef]['sheet'])->addChart($objChart);
$objChart->setWorksheet($excel->getSheetByName($charts[$chartEntryRef]['sheet']));
$objChart->setTopLeftPosition($chartDetails[$chartPositionRef]['fromCoordinate'], $chartDetails[$chartPositionRef]['fromOffsetX'], $chartDetails[$chartPositionRef]['fromOffsetY']);
$objChart->setBottomRightPosition($chartDetails[$chartPositionRef]['toCoordinate'], $chartDetails[$chartPositionRef]['toOffsetX'], $chartDetails[$chartPositionRef]['toOffsetY']);
}
}
}
}
}
}
$zip->close();
return $excel;
}
/**
* @author caochunhui@dachuwang.com
* @description 用数组和地址直接生成excel文件
* 每一个数组占一个sheet
*/
private function _convert_array_to_excel($arr = array(), $sheet_titles = array(), $out_name = '', $barcode_arr = array())
{
//下面的代码是抄的。
//set cache
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
//open excel file
$write_objPHPExcel = new PHPExcel();
$write_objPHPExcel->getDefaultStyle()->getFont()->setName('simsun')->setSize(10);
//下面要循环了
$sheet_cnt = 0;
foreach ($arr as $item) {
//用订单id.csv来命名每一个sheet
$out_sheet = new PHPExcel_Worksheet($write_objPHPExcel, $sheet_titles[$sheet_cnt]);
//$out_sheet->setTitle($item);
//row index start from 1
$row_index = 0;
foreach ($item as $row) {
$row_index++;
//$cellIterator = $row->getCellIterator();
//$cellIterator->setIterateOnlyExistingCells(false);
//column index start from 0
$column_index = -1;
foreach ($row as $cell) {
$column_index++;
//var_dump($cell);
$out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell, PHPExcel_Cell_DataType::TYPE_STRING);
}
}
//如果条码数组不为空，那么说明需要在sheet里插入条码
if (!empty($barcode_arr) && isset($barcode_arr[$sheet_cnt])) {
$barcode_download_res = $this->_download_barcode($barcode_arr[$sheet_cnt]);
if ($barcode_download_res['code'] == 200) {
//no pic you say a jb
$pic_path = $barcode_download_res['file'];
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('barcode');
$objDrawing->setDescription('');
$objDrawing->setPath($pic_path);
$objDrawing->setHeight(50);
$objDrawing->setCoordinates('D26');
//$objDrawing->setOffsetX(10);
//$objDrawing->getShadow()->setVisible(true);
//$objDrawing->getShadow()->setDirection(36);
$objDrawing->setWorksheet($out_sheet);
//no pic you say a jb
}
}
$write_objPHPExcel->addSheet($out_sheet);
$sheet_cnt++;
}
$write_objPHPExcel->removeSheetByIndex(0);
//删除第一个空sheet
//上面要循环了
//上面的代码是抄的
//write excel file
$objWriter = new PHPExcel_Writer_Excel2007($write_objPHPExcel);
$dir_name = dirname($out_name);
if (!is_dir($dir_name)) {
$res = mkdir($dir_name, 0777, TRUE);
}
$objWriter->save($out_name);
}
public function export($competition, $exportFormsts, $all = false, $xlsx = false, $extra = false, $order = 'date')
{
$registrations = $this->getRegistrations($competition, $all, $order);
$template = PHPExcel_IOFactory::load(Yii::getPathOfAlias('application.data.results') . '.xls');
$export = new PHPExcel();
$export->getProperties()->setCreator(Yii::app()->params->author)->setLastModifiedBy(Yii::app()->params->author)->setTitle($competition->wca_competition_id ?: $competition->name)->setSubject($competition->name);
$export->removeSheetByIndex(0);
//注册页
$sheet = $template->getSheet(0);
$sheet->setCellValue('A1', $competition->wca_competition_id ?: $competition->name);
$events = $competition->getRegistrationEvents();
$col = 'J';
$cubecompsEvents = array('333' => '3x3', '444' => '4x4', '555' => '5x5', '666' => '6x6', '777' => '7x7', '222' => '2x2', '333bf' => '333bld', '333fm' => 'fmc', 'minx' => 'mega', 'pyram' => 'pyra', '444bf' => '444bld', '555bf' => '555bld', '333mbf' => '333mlt');
foreach ($events as $event => $data) {
$sheet->setCellValue($col . 2, "=SUM({$col}4:{$col}" . (count($registrations) + 4) . ')');
$sheet->setCellValue($col . 3, isset($cubecompsEvents[$event]) ? $cubecompsEvents[$event] : $event);
$sheet->getColumnDimension($col)->setWidth(5.5);
$col++;
}
foreach ($registrations as $key => $registration) {
$user = $registration->user;
$row = $key + 4;
$sheet->setCellValue('A' . $row, $registration->number)->setCellValue('B' . $row, $user->getCompetitionName())->setCellValue('C' . $row, $user->country->name)->setCellValue('D' . $row, $user->wcaid)->setCellValue('E' . $row, $user->getWcaGender())->setCellValue('F' . $row, PHPExcel_Shared_Date::FormattedPHPToExcel(date('Y', $user->birthday), date('m', $user->birthday), date('d', $user->birthday)));
$col = 'J';
foreach ($events as $event => $data) {
if (in_array("{$event}", $registration->events)) {
$sheet->setCellValue($col . $row, 1);
}
$col++;
}
if ($extra) {
$col++;
$fee = $registration->getTotalFee();
if ($registration->isPaid()) {
$fee .= Yii::t('common', ' (paid)');
}
$sheet->setCellValue($col . $row, $fee);
$col++;
$sheet->setCellValue($col . $row, $user->mobile);
$col++;
$sheet->setCellValue($col . $row, $user->email);
$col++;
$sheet->setCellValue($col . $row, $registration->comments);
}
if (!$registration->isAccepted()) {
$sheet->getStyle("A{$row}:D{$row}")->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFF00'))));
}
}
$export->addExternalSheet($sheet);
//各个项目
foreach ($exportFormsts as $event => $rounds) {
$count = count($rounds);
foreach ($rounds as $round => $format) {
if ($round == $count - 1) {
$round = 'f';
} else {
$round++;
}
$sheet = $template->getSheetByName($format);
if ($sheet === null) {
continue;
}
$sheet = clone $sheet;
$sheet->setTitle("{$event}-{$round}");
$template->addSheet($sheet);
$sheet->setCellValue('A1', Events::getFullEventName($event) . ' - ' . Rounds::getFullRoundName($round));
if ($round == 1 || $count == 1) {
$row = 5;
foreach ($registrations as $registration) {
if (!in_array("{$event}", $registration->events)) {
continue;
}
$user = $registration->user;
$sheet->setCellValue('B' . $row, $user->getCompetitionName())->setCellValue('C' . $row, $user->country->name)->setCellValue('D' . $row, $user->wcaid);
if ($row > 5) {
$formula = $sheet->getCell('A' . ($row - 1))->getValue();
$formula = strtr($formula, array('-4' => '_temp_', $row - 1 => $row, $row - 2 => $row - 1, $row => $row + 1));
$formula = str_replace('_temp_', '-4', $formula);
$sheet->setCellValue('A' . $row, $formula);
}
if (!$registration->isAccepted()) {
$sheet->getStyle("A{$row}:D{$row}")->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFF00'))));
}
$row++;
}
}
$export->addExternalSheet($sheet);
}
}
$this->exportToExcel($export, 'php://output', $competition->name, $xlsx);
}
$activeSheet->setCellValue('A' . $stt, 'Người báo làm thêm giờ');
$activeSheet->mergeCells('A' . $stt . ':B' . $stt);
$activeSheet->setCellValue('C' . $stt, 'Trưởng phòng');
$activeSheet->mergeCells('C' . $stt . ':G' . $stt);
$activeSheet->getStyle('A' . $stt . ':' . 'G' . $stt)->applyFromArray($indam)->applyFromArray($canhgiua);
$activeSheet->getColumnDimension('A')->setWidth(15);
$activeSheet->getColumnDimension('B')->setWidth(50);
$activeSheet->getColumnDimension('C')->setWidth(8);
$activeSheet->getColumnDimension('D')->setWidth(8);
$activeSheet->getColumnDimension('E')->setWidth(8);
$activeSheet->getColumnDimension('F')->setWidth(8);
$activeSheet->getColumnDimension('G')->setWidth(15);
$objWorkSheet->setTitle("T{$key}");
$sheet++;
}
$objPHPExcel->removeSheetByIndex(0);
/**
* start export
*/
ob_end_clean();
$excelFileName = 'Làm thêm giờ - ' . $tennv;
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $excelFileName . '.xlsx"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header('Expires: Mon, 20 Jan 2015 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1
/**
* Loads PHPExcel from file
*
* @param string $pFilename
* @throws Exception
*/
public function load($pFilename)
{
// Initialisations
$this->_phpExcel = new PHPExcel();
$this->_phpExcel->removeSheetByIndex(0);
// remove 1st sheet
if (!$this->_readDataOnly) {
$this->_phpExcel->removeCellStyleXfByIndex(0);
// remove the default style
$this->_phpExcel->removeCellXfByIndex(0);
// remove the default style
}
// Use ParseXL for the hard work.
$this->_ole = new PHPExcel_Shared_OLERead();
// get excel data
$res = $this->_ole->read($pFilename);
$this->_data = $this->_ole->getWorkBook();
// total byte size of Excel data (workbook global substream + sheet substreams)
$this->_dataSize = strlen($this->_data);
// initialize
$this->_pos = 0;
$this->_codepage = 'CP1252';
$this->_formats = array();
$this->_objFonts = array();
$this->_palette = array();
$this->_sheets = array();
$this->_externalBooks = array();
$this->_ref = array();
$this->_definedname = array();
$this->_sst = array();
$this->_drawingGroupData = '';
$this->_xfIndex = '';
$this->_mapCellXfIndex = array();
$this->_mapCellStyleXfIndex = array();
// Parse Workbook Global Substream
while ($this->_pos < $this->_dataSize) {
$code = $this->_GetInt2d($this->_data, $this->_pos);
switch ($code) {
case self::XLS_Type_BOF:
$pos = $this->_pos;
$length = $this->_GetInt2d($this->_data, $pos + 2);
$recordData = substr($this->_data, $pos + 4, $length);
// offset: 0; size: 2; BIFF version
$this->_version = $this->_GetInt2d($this->_data, $pos + 4);
if ($this->_version != self::XLS_BIFF8 && $this->_version != self::XLS_BIFF7) {
return false;
}
// offset: 2; size: 2; type of stream
$substreamType = $this->_GetInt2d($this->_data, $pos + 6);
if ($substreamType != self::XLS_WorkbookGlobals) {
return false;
}
$this->_pos += 4 + $length;
break;
case self::XLS_Type_FILEPASS:
$this->_readFilepass();
break;
case self::XLS_Type_CODEPAGE:
$this->_readCodepage();
break;
case self::XLS_Type_DATEMODE:
$this->_readDateMode();
break;
case self::XLS_Type_FONT:
$this->_readFont();
break;
case self::XLS_Type_FORMAT:
$this->_readFormat();
break;
case self::XLS_Type_XF:
$this->_readXf();
break;
case self::XLS_Type_STYLE:
$this->_readStyle();
break;
case self::XLS_Type_PALETTE:
$this->_readPalette();
break;
case self::XLS_Type_SHEET:
$this->_readSheet();
break;
case self::XLS_Type_EXTERNALBOOK:
$this->_readExternalBook();
break;
case self::XLS_Type_EXTERNSHEET:
$this->_readExternSheet();
break;
case self::XLS_Type_DEFINEDNAME:
$this->_readDefinedName();
break;
case self::XLS_Type_MSODRAWINGGROUP:
$this->_readMsoDrawingGroup();
break;
case self::XLS_Type_SST:
$this->_readSst();
break;
case self::XLS_Type_EOF:
$this->_readDefault();
break 2;
default:
$this->_readDefault();
break;
}
}
// Resolve indexed colors for font, fill, and border colors
// Cannot be resolved already in XF record, because PALETTE record comes afterwards
if (!$this->_readDataOnly) {
foreach ($this->_objFonts as $objFont) {
$color = $this->_readColor($objFont->colorIndex);
$objFont->getColor()->setRGB($color['rgb']);
}
foreach ($this->_phpExcel->getCellXfCollection() as $objStyle) {
// fill start and end color
$startColor = $this->_readColor($objStyle->getFill()->startcolorIndex);
$objStyle->getFill()->getStartColor()->setRGB($startColor['rgb']);
$endColor = $this->_readColor($objStyle->getFill()->endcolorIndex);
$objStyle->getFill()->getEndColor()->setRGB($endColor['rgb']);
// border colors
$borderTopColor = $this->_readColor($objStyle->getBorders()->getTop()->colorIndex);
$objStyle->getBorders()->getTop()->getColor()->setRGB($borderTopColor['rgb']);
$borderRightColor = $this->_readColor($objStyle->getBorders()->getRight()->colorIndex);
$objStyle->getBorders()->getRight()->getColor()->setRGB($borderRightColor['rgb']);
$borderBottomColor = $this->_readColor($objStyle->getBorders()->getBottom()->colorIndex);
$objStyle->getBorders()->getBottom()->getColor()->setRGB($borderBottomColor['rgb']);
$borderLeftColor = $this->_readColor($objStyle->getBorders()->getLeft()->colorIndex);
$objStyle->getBorders()->getLeft()->getColor()->setRGB($borderLeftColor['rgb']);
}
}
// treat MSODRAWINGGROUP records, workbook-level Escher
if (!$this->_readDataOnly && $this->_drawingGroupData) {
$escherWorkbook = new PHPExcel_Shared_Escher();
$reader = new PHPExcel_Reader_Excel5_Escher($escherWorkbook);
$escherWorkbook = $reader->load($this->_drawingGroupData);
// debug Escher stream
//$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
//$debug->load($this->_drawingGroupData);
}
// Parse the individual sheets
foreach ($this->_sheets as $sheet) {
// check if sheet should be skipped
if (isset($this->_loadSheetsOnly) && !in_array($sheet['name'], $this->_loadSheetsOnly)) {
continue;
}
// add sheet to PHPExcel object
$this->_phpSheet = $this->_phpExcel->createSheet();
$this->_phpSheet->setTitle($sheet['name']);
$this->_phpSheet->setSheetState($sheet['sheetState']);
$this->_pos = $sheet['offset'];
// Initialize isFitToPages. May change after reading SHEETPR record.
$this->_isFitToPages = false;
// Initialize drawingData
$this->_drawingData = '';
// Initialize objs
$this->_objs = array();
// Initialize shared formula parts
$this->_sharedFormulaParts = array();
// Initialize shared formulas
$this->_sharedFormulas = array();
while ($this->_pos < $this->_dataSize) {
$code = $this->_GetInt2d($this->_data, $this->_pos);
switch ($code) {
case self::XLS_Type_BOF:
$length = $this->_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
// do not use this version information for anything
// it is unreliable (OpenOffice doc, 5.8), use only version information from the global stream
// offset: 2; size: 2; type of the following data
$substreamType = $this->_GetInt2d($recordData, 2);
if ($substreamType != self::XLS_Worksheet) {
break 2;
}
break;
case self::XLS_Type_PRINTGRIDLINES:
$this->_readPrintGridlines();
break;
case self::XLS_Type_DEFAULTROWHEIGHT:
$this->_readDefaultRowHeight();
break;
case self::XLS_Type_SHEETPR:
$this->_readSheetPr();
break;
case self::XLS_Type_HORIZONTALPAGEBREAKS:
$this->_readHorizontalPageBreaks();
break;
case self::XLS_Type_VERTICALPAGEBREAKS:
$this->_readVerticalPageBreaks();
break;
case self::XLS_Type_HEADER:
$this->_readHeader();
break;
case self::XLS_Type_FOOTER:
$this->_readFooter();
break;
case self::XLS_Type_HCENTER:
$this->_readHcenter();
break;
case self::XLS_Type_VCENTER:
$this->_readVcenter();
break;
case self::XLS_Type_LEFTMARGIN:
$this->_readLeftMargin();
break;
case self::XLS_Type_RIGHTMARGIN:
$this->_readRightMargin();
break;
case self::XLS_Type_TOPMARGIN:
$this->_readTopMargin();
break;
case self::XLS_Type_BOTTOMMARGIN:
$this->_readBottomMargin();
break;
case self::XLS_Type_PAGESETUP:
$this->_readPageSetup();
break;
case self::XLS_Type_PROTECT:
$this->_readProtect();
break;
case self::XLS_Type_PASSWORD:
$this->_readPassword();
break;
case self::XLS_Type_DEFCOLWIDTH:
$this->_readDefColWidth();
break;
case self::XLS_Type_COLINFO:
$this->_readColInfo();
break;
case self::XLS_Type_DIMENSION:
$this->_readDefault();
break;
case self::XLS_Type_ROW:
$this->_readRow();
break;
case self::XLS_Type_DBCELL:
$this->_readDefault();
break;
case self::XLS_Type_RK:
$this->_readRk();
break;
case self::XLS_Type_LABELSST:
$this->_readLabelSst();
break;
case self::XLS_Type_MULRK:
$this->_readMulRk();
break;
case self::XLS_Type_NUMBER:
$this->_readNumber();
break;
case self::XLS_Type_FORMULA:
$this->_readFormula();
break;
case self::XLS_Type_SHAREDFMLA:
$this->_readSharedFmla();
break;
case self::XLS_Type_BOOLERR:
$this->_readBoolErr();
break;
case self::XLS_Type_MULBLANK:
$this->_readMulBlank();
break;
case self::XLS_Type_LABEL:
$this->_readLabel();
break;
case self::XLS_Type_BLANK:
$this->_readBlank();
break;
case self::XLS_Type_MSODRAWING:
$this->_readMsoDrawing();
break;
case self::XLS_Type_OBJ:
$this->_readObj();
break;
case self::XLS_Type_WINDOW2:
$this->_readWindow2();
break;
case self::XLS_Type_SCL:
$this->_readScl();
break;
case self::XLS_Type_PANE:
$this->_readPane();
break;
case self::XLS_Type_MERGEDCELLS:
$this->_readMergedCells();
break;
case self::XLS_Type_HYPERLINK:
$this->_readHyperLink();
break;
case self::XLS_Type_SHEETLAYOUT:
$this->_readSheetLayout();
break;
case self::XLS_Type_RANGEPROTECTION:
$this->_readRangeProtection();
break;
//case self::XLS_Type_IMDATA: $this->_readImData(); break;
//case self::XLS_Type_IMDATA: $this->_readImData(); break;
case self::XLS_Type_CONTINUE:
$this->_readContinue();
break;
case self::XLS_Type_EOF:
$this->_readDefault();
break 2;
default:
$this->_readDefault();
break;
}
}
// treat MSODRAWING records, sheet-level Escher
if (!$this->_readDataOnly && $this->_drawingData) {
$escherWorksheet = new PHPExcel_Shared_Escher();
$reader = new PHPExcel_Reader_Excel5_Escher($escherWorksheet);
$escherWorksheet = $reader->load($this->_drawingData);
// debug Escher stream
//$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
//$debug->load($this->_drawingData);
// get all spContainers in one long array, so they can be mapped to OBJ records
$allSpContainers = $escherWorksheet->getDgContainer()->getSpgrContainer()->getAllSpContainers();
}
// treat OBJ records
foreach ($this->_objs as $n => $obj) {
// the first shape container never has a corresponding OBJ record, hence $n + 1
$spContainer = $allSpContainers[$n + 1];
// we skip all spContainers that are a part of a group shape since we cannot yet handle those
if ($spContainer->getNestingLevel() > 1) {
continue;
}
// calculate the width and height of the shape
list($startColumn, $startRow) = PHPExcel_Cell::coordinateFromString($spContainer->getStartCoordinates());
list($endColumn, $endRow) = PHPExcel_Cell::coordinateFromString($spContainer->getEndCoordinates());
$startOffsetX = $spContainer->getStartOffsetX();
$startOffsetY = $spContainer->getStartOffsetY();
$endOffsetX = $spContainer->getEndOffsetX();
$endOffsetY = $spContainer->getEndOffsetY();
$width = PHPExcel_Shared_Excel5::getDistanceX($this->_phpSheet, $startColumn, $startOffsetX, $endColumn, $endOffsetX);
$height = PHPExcel_Shared_Excel5::getDistanceY($this->_phpSheet, $startRow, $startOffsetY, $endRow, $endOffsetY);
// calculate offsetX and offsetY of the shape
$offsetX = $startOffsetX * PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, $startColumn) / 1024;
$offsetY = $startOffsetY * PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $startRow) / 256;
switch ($obj['type']) {
case 0x8:
// picture
// get index to BSE entry (1-based)
$BSEindex = $spContainer->getOPT(0x104);
$BSECollection = $escherWorkbook->getDggContainer()->getBstoreContainer()->getBSECollection();
$BSE = $BSECollection[$BSEindex - 1];
$blipType = $BSE->getBlipType();
// need check because some blip types are not supported by Escher reader such as EMF
if ($blip = $BSE->getBlip()) {
$ih = imagecreatefromstring($blip->getData());
$drawing = new PHPExcel_Worksheet_MemoryDrawing();
$drawing->setImageResource($ih);
// width, height, offsetX, offsetY
$drawing->setResizeProportional(false);
$drawing->setWidth($width);
$drawing->setHeight($height);
$drawing->setOffsetX($offsetX);
$drawing->setOffsetY($offsetY);
switch ($blipType) {
case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG:
$drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_JPEG);
break;
case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG:
$drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG);
$drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_PNG);
break;
}
$drawing->setWorksheet($this->_phpSheet);
$drawing->setCoordinates($spContainer->getStartCoordinates());
}
break;
default:
// other object type
break;
}
}
// treat SHAREDFMLA records
if ($this->_version == self::XLS_BIFF8) {
foreach ($this->_sharedFormulaParts as $cell => $baseCell) {
$formula = $this->_getFormulaFromStructure($this->_sharedFormulas[$baseCell], $cell);
$this->_phpSheet->getCell($cell)->setValueExplicit('=' . $formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
}
}
}
// add the named ranges (defined names)
foreach ($this->_definedname as $definedName) {
if ($definedName['isBuiltInName']) {
switch ($definedName['name']) {
case pack('C', 0x6):
// print area
// in general, formula looks like this: Foo!$C$7:$J$66,Bar!$A$1:$IV$2
$ranges = explode(',', $definedName['formula']);
// FIXME: what if sheetname contains comma?
foreach ($ranges as $range) {
// $range should look like this one of these
// Foo!$C$7:$J$66
// Bar!$A$1:$IV$2
$explodes = explode('!', $range);
if (count($explodes) == 2) {
if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
$extractedRange = $explodes[1];
$extractedRange = str_replace('$', '', $extractedRange);
$docSheet->getPageSetup()->setPrintArea($extractedRange);
}
}
}
break;
case pack('C', 0x7):
// print titles (repeating rows)
// Assuming BIFF8, there are 3 cases
// 1. repeating rows
// formula looks like this: Sheet!$A$1:$IV$2
// rows 1-2 repeat
// 2. repeating columns
// formula looks like this: Sheet!$A$1:$B$65536
// columns A-B repeat
// 3. both repeating rows and repeating columns
// formula looks like this: Sheet!$A$1:$B$65536,Sheet!$A$1:$IV$2
$ranges = explode(',', $definedName['formula']);
// FIXME: what if sheetname contains comma?
foreach ($ranges as $range) {
// $range should look like this one of these
// Sheet!$A$1:$B$65536
// Sheet!$A$1:$IV$2
$explodes = explode('!', $range);
if (count($explodes) == 2) {
if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
$extractedRange = $explodes[1];
$extractedRange = str_replace('$', '', $extractedRange);
$coordinateStrings = explode(':', $extractedRange);
if (count($coordinateStrings) == 2) {
list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[0]);
list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[1]);
if ($firstColumn == 'A' and $lastColumn == 'IV') {
// then we have repeating rows
$docSheet->getPageSetup()->setRowsToRepeatAtTop(array($firstRow, $lastRow));
} elseif ($firstRow == 1 and $lastRow == 65536) {
// then we have repeating columns
$docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($firstColumn, $lastColumn));
}
}
}
}
}
break;
}
} else {
// Extract range
$explodes = explode('!', $definedName['formula']);
if (count($explodes) == 2) {
if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
$extractedRange = $explodes[1];
$extractedRange = str_replace('$', '', $extractedRange);
$this->_phpExcel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $docSheet, $extractedRange, false));
}
}
}
}
return $this->_phpExcel;
}
<?php
// パスを通す
set_include_path('/_lib/Classes/');
// インクルード
include_once dirname(__FILE__) . '/_lib/Classes/PHPExcel.php';
include_once dirname(__FILE__) . '/_lib/Classes/PHPExcel/IOFactory.php';
// テンプレート読み込み
$reader = PHPExcel_IOFactory::createReader('Excel5');
$template = $reader->load(dirname(__FILE__) . '/template.xls');
print dirname(__FILE__) . '\\template.xls';
$templateSheet = clone $template->getActiveSheet();
$excel = new PHPExcel();
$needlessSheet = $excel->getActiveSheet();
$sheet = $excel->addExternalSheet($templateSheet);
$sheet->setTitle('テスト');
// 出力
// 必要ないシートを削除
$sheetIndex = $excel->GetIndex($needlessSheet);
$excel->removeSheetByIndex($sheetIndex);
$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
$outputFileName = '/2015_07.xls';
$writer->save(dirname(__FILE__) . $outputFileName);
print dirname(__FILE__);
print $outputFileName;
/**
* Loads PHPExcel from file
*
* @param string $pFilename
* @throws Exception
*/
public function load($pFilename)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
// Initialisations
$excel = new PHPExcel();
$excel->removeSheetByIndex(0);
$zip = new ZipArchive();
$zip->open($pFilename);
$rels = simplexml_load_string($zip->getFromName("_rels/.rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($rels->Relationship as $rel) {
switch ($rel["Type"]) {
case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
$xmlCore = simplexml_load_string($zip->getFromName("{$rel['Target']}"));
$xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
$xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
$xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
$docProps = $excel->getProperties();
$docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator")));
$docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy")));
$docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created"))));
//! respect xsi:type
$docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified"))));
//! respect xsi:type
$docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title")));
$docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description")));
$docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject")));
$docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords")));
$docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category")));
break;
case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
$dir = dirname($rel["Target"]);
$relsWorkbook = simplexml_load_string($zip->getFromName("{$dir}/_rels/" . basename($rel["Target"]) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
$sharedStrings = array();
$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']"));
$xmlStrings = simplexml_load_string($zip->getFromName("{$dir}/{$xpath['Target']}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
if (isset($xmlStrings) && isset($xmlStrings->si)) {
foreach ($xmlStrings->si as $val) {
if (isset($val->t)) {
$sharedStrings[] = PHPExcel_Shared_String::ControlCharacterOOXML2PHP((string) $val->t);
} elseif (isset($val->r)) {
$sharedStrings[] = $this->_parseRichText($val);
}
}
}
$worksheets = array();
foreach ($relsWorkbook->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
$worksheets[(string) $ele["Id"]] = $ele["Target"];
}
}
$styles = array();
$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']"));
$xmlStyles = simplexml_load_string($zip->getFromName("{$dir}/{$xpath['Target']}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
$numFmts = $xmlStyles->numFmts[0];
if ($numFmts) {
$numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
}
if (!$this->_readDataOnly) {
foreach ($xmlStyles->cellXfs->xf as $xf) {
$numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
if ($numFmts && $xf["numFmtId"]) {
$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]"));
if (isset($tmpNumFmt["formatCode"])) {
$numFmt = (string) $tmpNumFmt["formatCode"];
} else {
if ((int) $xf["numFmtId"] < 165) {
$numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int) $xf["numFmtId"]);
}
}
}
//$numFmt = str_replace('mm', 'i', $numFmt);
//$numFmt = str_replace('h', 'H', $numFmt);
$styles[] = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection);
}
}
$dxfs = array();
if (!$this->_readDataOnly) {
foreach ($xmlStyles->dxfs->dxf as $dxf) {
$style = new PHPExcel_Style();
$this->_readStyle($style, $dxf);
$dxfs[] = $style;
}
}
$xmlWorkbook = simplexml_load_string($zip->getFromName("{$rel['Target']}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
$sheetId = 0;
foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
$docSheet = $excel->createSheet();
$docSheet->setTitle((string) $eleSheet["name"]);
$fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$xmlSheet = simplexml_load_string($zip->getFromName("{$dir}/{$fileWorksheet}"));
//~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
$sharedFormulas = array();
if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
$docSheet->setShowGridLines($xmlSheet->sheetViews->sheetView['showGridLines'] ? true : false);
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && $xmlSheet->sheetPr->outlinePr['summaryRight'] == false) {
$docSheet->setShowSummaryRight(false);
} else {
$docSheet->setShowSummaryRight(true);
}
if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && $xmlSheet->sheetPr->outlinePr['summaryBelow'] == false) {
$docSheet->setShowSummaryBelow(false);
} else {
$docSheet->setShowSummaryBelow(true);
}
}
if (isset($xmlSheet->sheetFormatPr)) {
if (isset($xmlSheet->sheetFormatPr['customHeight']) && ((string) $xmlSheet->sheetFormatPr['customHeight'] == '1' || strtolower((string) $xmlSheet->sheetFormatPr['customHeight']) == 'true') && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
$docSheet->getDefaultRowDimension()->setRowHeight((double) $xmlSheet->sheetFormatPr['defaultRowHeight']);
}
if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
$docSheet->getDefaultColumnDimension()->setWidth((double) $xmlSheet->sheetFormatPr['defaultColWidth']);
}
}
if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
foreach ($xmlSheet->cols->col as $col) {
for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); $i++) {
if ($col["bestFit"]) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(true);
}
if ($col["hidden"]) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setVisible(false);
}
if ($col["collapsed"]) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setCollapsed(true);
}
if ($col["outlineLevel"] > 0) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
}
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));
if (intval($col["max"]) == 16384) {
break;
}
}
}
}
if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
if ($xmlSheet->printOptions['gridLinesSet'] == 'true' && $xmlSheet->printOptions['gridLinesSet'] == '1') {
$docSheet->setShowGridlines(true);
}
if ($xmlSheet->printOptions['gridLines'] == 'true' || $xmlSheet->printOptions['gridLines'] == '1') {
$docSheet->setPrintGridlines(true);
}
if ($xmlSheet->printOptions['horizontalCentered']) {
$docSheet->getPageSetup()->setHorizontalCentered(true);
}
if ($xmlSheet->printOptions['verticalCentered']) {
$docSheet->getPageSetup()->setVerticalCentered(true);
}
}
foreach ($xmlSheet->sheetData->row as $row) {
if ($row["ht"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
}
if ($row["hidden"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setVisible(false);
}
if ($row["collapsed"]) {
$docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true);
}
if ($row["outlineLevel"] > 0) {
$docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
}
foreach ($row->c as $c) {
$r = (string) $c["r"];
switch ($c["t"]) {
case "s":
if ((string) $c->v != '') {
$value = $sharedStrings[intval($c->v)];
if ($value instanceof PHPExcel_RichText) {
$value = clone $value;
}
} else {
$value = '';
}
break;
case "b":
$value = (string) $c->v;
if ($value == '0') {
$value = false;
} else {
if ($value == '1') {
$value = true;
} else {
$value = (bool) $c->v;
}
}
break;
case "inlineStr":
$value = $this->_parseRichText($c->is);
$value->setParent($docSheet->getCell($r));
break;
default:
if (!isset($c->f)) {
$value = (string) $c->v;
} else {
// Formula
$value = "={$c->f}";
// Shared formula?
if (isset($c->f['t']) && strtolower((string) $c->f['t']) == 'shared') {
$instance = (string) $c->f['si'];
if (!isset($sharedFormulas[(string) $c->f['si']])) {
$sharedFormulas[$instance] = array('master' => $r, 'formula' => $value);
} else {
$master = PHPExcel_Cell::coordinateFromString($sharedFormulas[$instance]['master']);
$current = PHPExcel_Cell::coordinateFromString($r);
$difference = array(0, 0);
$difference[0] = PHPExcel_Cell::columnIndexFromString($current[0]) - PHPExcel_Cell::columnIndexFromString($master[0]);
$difference[1] = $current[1] - $master[1];
$helper = PHPExcel_ReferenceHelper::getInstance();
$x = $helper->updateFormulaReferences($sharedFormulas[$instance]['formula'], 'A1', $difference[0], $difference[1]);
$value = $x;
}
}
}
break;
}
// Check for numeric values
if (is_numeric($value)) {
if ($value == (int) $value) {
$value = (int) $value;
} elseif ($value == (double) $value) {
$value = (double) $value;
} elseif ($value == (double) $value) {
$value = (double) $value;
}
}
$docSheet->setCellValue($r, $value);
if ($c["s"] && !$this->_readDataOnly) {
if (isset($styles[intval($c["s"])])) {
$this->_readStyle($docSheet->getStyle($r), $styles[intval($c["s"])]);
}
if (PHPExcel_Shared_Date::isDateTimeFormat($docSheet->getStyle($r)->getNumberFormat())) {
if (preg_match("/^([0-9.,-]+)\$/", $value)) {
$docSheet->setCellValue($r, PHPExcel_Shared_Date::ExcelToPHP($value));
}
}
}
}
}
$conditionals = array();
if (!$this->_readDataOnly) {
foreach ($xmlSheet->conditionalFormatting as $conditional) {
foreach ($conditional->cfRule as $cfRule) {
if (((string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_NONE || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CELLIS || (string) $cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT) && isset($dxfs[intval($cfRule["dxfId"])])) {
$conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule;
}
}
}
foreach ($conditionals as $ref => $cfRules) {
ksort($cfRules);
$conditionalStyles = array();
foreach ($cfRules as $cfRule) {
$objConditional = new PHPExcel_Style_Conditional();
$objConditional->setConditionType((string) $cfRule["type"]);
$objConditional->setOperatorType((string) $cfRule["operator"]);
$objConditional->setCondition((string) $cfRule->formula);
$objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]);
$conditionalStyles[] = $objConditional;
}
// Extract all cell references in $ref
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($ref);
foreach ($aReferences as $reference) {
$docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles);
}
}
}
$aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells");
if (!$this->_readDataOnly) {
foreach ($aKeys as $key) {
$method = "set" . ucfirst($key);
$docSheet->getProtection()->{$method}($xmlSheet->sheetProtection[$key] == "true");
}
}
if (!$this->_readDataOnly) {
$docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], true);
if ($xmlSheet->protectedRanges->protectedRange) {
foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
$docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
}
}
}
if ($xmlSheet->autoFilter && !$this->_readDataOnly) {
$docSheet->setAutoFilter((string) $xmlSheet->autoFilter["ref"]);
}
if ($xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
$docSheet->mergeCells((string) $mergeCell["ref"]);
}
}
if (!$this->_readDataOnly) {
$docPageMargins = $docSheet->getPageMargins();
$docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"]));
$docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"]));
$docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"]));
$docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"]));
$docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"]));
$docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"]));
}
if (!$this->_readDataOnly) {
$docPageSetup = $docSheet->getPageSetup();
if (isset($xmlSheet->pageSetup["orientation"])) {
$docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
}
if (isset($xmlSheet->pageSetup["paperSize"])) {
$docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"]));
}
if (isset($xmlSheet->pageSetup["scale"])) {
$docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]));
}
if (isset($xmlSheet->pageSetup["fitToHeight"])) {
$docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]));
}
if (isset($xmlSheet->pageSetup["fitToWidth"])) {
$docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]));
}
}
if (!$this->_readDataOnly) {
$docHeaderFooter = $docSheet->getHeaderFooter();
$docHeaderFooter->setDifferentOddEven($xmlSheet->headerFooter["differentOddEven"] == 'true');
$docHeaderFooter->setDifferentFirst($xmlSheet->headerFooter["differentFirst"] == 'true');
$docHeaderFooter->setScaleWithDocument($xmlSheet->headerFooter["scaleWithDoc"] == 'true');
$docHeaderFooter->setAlignWithMargins($xmlSheet->headerFooter["alignWithMargins"] == 'true');
$docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
$docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
$docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
$docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
$docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
$docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
}
if ($xmlSheet->rowBreaks->brk && !$this->_readDataOnly) {
foreach ($xmlSheet->rowBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak("A{$brk['id']}", PHPExcel_Worksheet::BREAK_ROW);
}
}
}
if ($xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
foreach ($xmlSheet->colBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak(PHPExcel_Cell::stringFromColumnIndex($brk["id"]) . "1", PHPExcel_Worksheet::BREAK_COLUMN);
}
}
}
if ($xmlSheet->dataValidations && !$this->_readDataOnly) {
foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
// Uppercase coordinate
$range = strtoupper($dataValidation["sqref"]);
// Extract all cell references in $range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($range);
foreach ($aReferences as $reference) {
// Create validation
$docValidation = $docSheet->getCell($reference)->getDataValidation();
$docValidation->setType((string) $dataValidation["type"]);
$docValidation->setErrorStyle((string) $dataValidation["errorStyle"]);
$docValidation->setOperator((string) $dataValidation["operator"]);
$docValidation->setAllowBlank($dataValidation["allowBlank"] != 0);
$docValidation->setShowDropDown($dataValidation["showDropDown"] == 0);
$docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0);
$docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0);
$docValidation->setErrorTitle((string) $dataValidation["errorTitle"]);
$docValidation->setError((string) $dataValidation["error"]);
$docValidation->setPromptTitle((string) $dataValidation["promptTitle"]);
$docValidation->setPrompt((string) $dataValidation["prompt"]);
$docValidation->setFormula1((string) $dataValidation->formula1);
$docValidation->setFormula2((string) $dataValidation->formula2);
}
}
}
// Add hyperlinks
$hyperlinks = array();
if (!$this->_readDataOnly) {
// Locate hyperlink relations
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($zip->getFromName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
$hyperlinks[(string) $ele["Id"]] = (string) $ele["Target"];
}
}
}
// Loop trough hyperlinks
if ($xmlSheet->hyperlinks) {
foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
// Link url
$linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
if (isset($linkRel['id'])) {
$docSheet->getCell($hyperlink['ref'])->getHyperlink()->setUrl($hyperlinks[(string) $linkRel['id']]);
}
if (isset($hyperlink['location'])) {
$docSheet->getCell($hyperlink['ref'])->getHyperlink()->setUrl('sheet://' . (string) $hyperlink['location']);
}
// Tooltip
if (isset($hyperlink['tooltip'])) {
$docSheet->getCell($hyperlink['ref'])->getHyperlink()->setTooltip((string) $hyperlink['tooltip']);
}
}
}
}
// Add comments
$comments = array();
if (!$this->_readDataOnly) {
// Locate comment relations
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($zip->getFromName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") {
$comments[(string) $ele["Id"]] = (string) $ele["Target"];
}
}
}
// Loop trough comments
foreach ($comments as $relName => $relPath) {
// Load comments file
$relPath = PHPExcel_Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath);
$commentsFile = simplexml_load_string($zip->getFromName($relPath));
// Utility variables
$authors = array();
// Loop trough authors
foreach ($commentsFile->authors->author as $author) {
$authors[] = (string) $author;
}
// Loop trough contents
foreach ($commentsFile->commentList->comment as $comment) {
$docSheet->getComment((string) $comment['ref'])->setAuthor($authors[(string) $comment['authorId']]);
$docSheet->getComment((string) $comment['ref'])->setText($this->_parseRichText($comment->text));
}
}
// Header/footer images
if ($xmlSheet->legacyDrawingHF && !$this->_readDataOnly) {
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($zip->getFromName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$vmlRelationship = '';
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
$vmlRelationship = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]);
}
}
if ($vmlRelationship != '') {
// Fetch linked images
$relsVML = simplexml_load_string($zip->getFromName(dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels'));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$drawings = array();
foreach ($relsVML->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
$drawings[(string) $ele["Id"]] = self::dir_add($vmlRelationship, $ele["Target"]);
}
}
// Fetch VML document
$vmlDrawing = simplexml_load_string($zip->getFromName($vmlRelationship));
$vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$hfImages = array();
$shapes = $vmlDrawing->xpath('//v:shape');
foreach ($shapes as $shape) {
$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$imageData = $shape->xpath('//v:imagedata');
$imageData = $imageData[0];
$imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office');
$style = self::toCSSArray((string) $shape['style']);
$hfImages[(string) $shape['id']] = new PHPExcel_Worksheet_HeaderFooterDrawing();
if (isset($imageData['title'])) {
$hfImages[(string) $shape['id']]->setName((string) $imageData['title']);
}
$hfImages[(string) $shape['id']]->setPath("zip://{$pFilename}#" . $drawings[(string) $imageData['relid']], false);
$hfImages[(string) $shape['id']]->setResizeProportional(false);
$hfImages[(string) $shape['id']]->setWidth($style['width']);
$hfImages[(string) $shape['id']]->setHeight($style['height']);
$hfImages[(string) $shape['id']]->setOffsetX($style['margin-left']);
$hfImages[(string) $shape['id']]->setOffsetY($style['margin-top']);
$hfImages[(string) $shape['id']]->setResizeProportional(true);
}
$docSheet->getHeaderFooter()->setImages($hfImages);
}
}
}
}
// TODO: Make sure drawings and graph are loaded differently!
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($zip->getFromName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$drawings = array();
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") {
$drawings[(string) $ele["Id"]] = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]);
}
}
if ($xmlSheet->drawing && !$this->_readDataOnly) {
foreach ($xmlSheet->drawing as $drawing) {
$fileDrawing = $drawings[(string) self::array_item($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$relsDrawing = simplexml_load_string($zip->getFromName(dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels"));
//~ http://schemas.openxmlformats.org/package/2006/relationships");
$images = array();
if ($relsDrawing && $relsDrawing->Relationship) {
foreach ($relsDrawing->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
$images[(string) $ele["Id"]] = self::dir_add($fileDrawing, $ele["Target"]);
}
}
}
$xmlDrawing = simplexml_load_string($zip->getFromName($fileDrawing))->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
if ($xmlDrawing->oneCellAnchor) {
foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) {
if ($oneCellAnchor->pic->blipFill) {
$blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://{$pFilename}#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1));
$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff));
$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx")));
$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
}
}
}
if ($xmlDrawing->twoCellAnchor) {
foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) {
if ($twoCellAnchor->pic->blipFill) {
$blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://{$pFilename}#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff));
$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx")));
$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
}
}
}
}
}
}
// Loop trough definedNames
if ($xmlWorkbook->definedNames) {
foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
// Extract range
$extractedRange = (string) $definedName;
if (strpos($extractedRange, '!') !== false) {
$extractedRange = substr($extractedRange, strpos($extractedRange, '!') + 1);
}
$extractedRange = str_replace('$', '', $extractedRange);
// Valid range?
if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') {
continue;
}
// Some definedNames are only applicable if we are on the same sheet...
if ($definedName['localSheetId'] == $sheetId) {
// Switch on type
switch ((string) $definedName['name']) {
case '_xlnm._FilterDatabase':
$docSheet->setAutoFilter($extractedRange);
break;
case '_xlnm.Print_Titles':
// Split $extractedRange
$extractedRange = explode(',', $extractedRange);
// Set print titles
if (isset($extractedRange[0])) {
$docSheet->getPageSetup()->setColumnsToRepeatAtLeft(explode(':', $extractedRange[0]));
}
if (isset($extractedRange[1])) {
$docSheet->getPageSetup()->setRowsToRepeatAtTop(explode(':', $extractedRange[1]));
}
break;
case '_xlnm.Print_Area':
$docSheet->getPageSetup()->setPrintArea($extractedRange);
break;
default:
$excel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $docSheet, $extractedRange, true));
break;
}
} else {
// "Global" definedNames
$locatedSheet = null;
$extractedSheetName = '';
if (strpos((string) $definedName, '!') !== false) {
// Extract sheet name
$extractedSheetName = PHPExcel_Worksheet::extractSheetTitle((string) $definedName);
// Locate sheet
$locatedSheet = $excel->getSheetByName($extractedSheetName);
}
if (!is_null($locatedSheet)) {
$excel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $locatedSheet, $extractedRange, false));
}
}
}
}
// Garbage collect...
$docSheet->garbageCollect();
// Next sheet id
$sheetId++;
}
if (!$this->_readDataOnly) {
$excel->setActiveSheetIndex(intval($xmlWorkbook->bookView->workbookView["activeTab"]));
}
break;
}
}
return $excel;
}
/**
* 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
$this->_phpExcel = new PHPExcel();
$this->_phpExcel->removeSheetByIndex(0);
// Use ParseXL for the hard work.
$this->_ole = new PHPExcel_Shared_OLERead();
$this->_encoderFunction = function_exists('mb_convert_encoding') ? 'mb_convert_encoding' : 'iconv';
// get excel data
$res = $this->_ole->read($pFilename);
// oops, something goes wrong (Darko Miljanovic)
if ($res === false) {
// check error code
if ($this->_ole->error == 1) {
// bad file
throw new Exception('The filename ' . $pFilename . ' is not readable');
} elseif ($this->_ole->error == 2) {
throw new Exception('The filename ' . $pFilename . ' is not recognised as an Excel file');
}
// check other error codes here (eg bad fileformat, etc...)
}
$this->_data = $this->_ole->getWorkBook();
// total byte size of Excel data (workbook global substream + sheet substreams)
$this->_dataSize = strlen($this->_data);
$this->_pos = 0;
// Parse Workbook Global Substream
while ($this->_pos < $this->_dataSize) {
$code = $this->_GetInt2d($this->_data, $this->_pos);
switch ($code) {
case self::XLS_Type_BOF:
$pos = $this->_pos;
$length = $this->_GetInt2d($this->_data, $pos + 2);
$recordData = substr($this->_data, $pos + 4, $length);
// offset: 0; size: 2; BIFF version
$this->_version = $this->_GetInt2d($this->_data, $pos + 4);
if ($this->_version != self::XLS_BIFF8 && $this->_version != self::XLS_BIFF7) {
return false;
}
// offset: 2; size: 2; type of stream
$substreamType = $this->_GetInt2d($this->_data, $pos + 6);
if ($substreamType != self::XLS_WorkbookGlobals) {
return false;
}
$this->_pos += 4 + $length;
break;
case self::XLS_Type_FILEPASS:
$this->_readFilepass();
break;
case self::XLS_Type_CODEPAGE:
$this->_readCodepage();
break;
case self::XLS_Type_DATEMODE:
$this->_readDateMode();
break;
case self::XLS_Type_FONT:
$this->_readFont();
break;
case self::XLS_Type_FORMAT:
$this->_readFormat();
break;
case self::XLS_Type_XF:
$this->_readXf();
break;
case self::XLS_Type_STYLE:
$this->_readStyle();
break;
case self::XLS_Type_PALETTE:
$this->_readPalette();
break;
case self::XLS_Type_SHEET:
$this->_readSheet();
break;
case self::XLS_Type_EXTERNALBOOK:
$this->_readExternalBook();
break;
case self::XLS_Type_EXTERNSHEET:
$this->_readExternSheet();
break;
case self::XLS_Type_DEFINEDNAME:
$this->_readDefinedName();
break;
case self::XLS_Type_MSODRAWINGGROUP:
$this->_readMsoDrawingGroup();
break;
case self::XLS_Type_SST:
$this->_readSst();
break;
case self::XLS_Type_EOF:
$this->_readDefault();
break 2;
default:
$this->_readDefault();
break;
}
}
// Resolve indexed colors for font, fill, and border colors
// Cannot be resolved already in XF record, because PALETTE record comes afterwards
if (!$this->_readDataOnly) {
foreach ($this->_fonts as &$font) {
$font['color'] = $this->_readColor($font['colorIndex']);
}
foreach ($this->_xf as &$xf) {
// fonts
$xf['font']['color'] = $this->_readColor($xf['font']['colorIndex']);
// fill start and end color
$xf['fill']['startcolor'] = $this->_readColor($xf['fill']['startcolorIndex']);
$xf['fill']['endcolor'] = $this->_readColor($xf['fill']['endcolorIndex']);
// border colors
$xf['borders']['top']['color'] = $this->_readColor($xf['borders']['top']['colorIndex']);
$xf['borders']['right']['color'] = $this->_readColor($xf['borders']['right']['colorIndex']);
$xf['borders']['bottom']['color'] = $this->_readColor($xf['borders']['bottom']['colorIndex']);
$xf['borders']['left']['color'] = $this->_readColor($xf['borders']['left']['colorIndex']);
}
foreach ($this->_builtInStyles as &$builtInStyle) {
// fonts
$builtInStyle['font']['color'] = $this->_readColor($builtInStyle['font']['colorIndex']);
// fill start and end color
$builtInStyle['fill']['startcolor'] = $this->_readColor($builtInStyle['fill']['startcolorIndex']);
$builtInStyle['fill']['endcolor'] = $this->_readColor($builtInStyle['fill']['endcolorIndex']);
// border colors
$builtInStyle['borders']['top']['color'] = $this->_readColor($builtInStyle['borders']['top']['colorIndex']);
$builtInStyle['borders']['right']['color'] = $this->_readColor($builtInStyle['borders']['right']['colorIndex']);
$builtInStyle['borders']['bottom']['color'] = $this->_readColor($builtInStyle['borders']['bottom']['colorIndex']);
$builtInStyle['borders']['left']['color'] = $this->_readColor($builtInStyle['borders']['left']['colorIndex']);
}
}
// treat MSODRAWINGGROUP records, workbook-level Escher
if (!$this->_readDataOnly && $this->_drawingGroupData) {
$escherWorkbook = new PHPExcel_Shared_Escher();
$reader = new PHPExcel_Reader_Excel5_Escher($escherWorkbook);
$escherWorkbook = $reader->load($this->_drawingGroupData);
// debug Escher stream
//$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
//$debug->load($this->_drawingGroupData);
}
// Parse the individual sheets
foreach ($this->_sheets as $sheet) {
// check if sheet should be skipped
if (isset($this->_loadSheetsOnly) && !in_array($sheet['name'], $this->_loadSheetsOnly)) {
continue;
}
// add sheet to PHPExcel object
$this->_phpSheet = $this->_phpExcel->createSheet();
$this->_phpSheet->setTitle($sheet['name']);
// default style
if (!$this->_readDataOnly && isset($this->_builtInStyles[0])) {
$this->_phpSheet->getDefaultStyle()->applyFromArray($this->_builtInStyles[0]);
}
$this->_pos = $sheet['offset'];
// Initialize isFitToPages. May change after reading SHEETPR record.
$this->_isFitToPages = false;
// Initialize drawingData
$this->_drawingData = '';
// Initialize objs
$this->_objs = array();
while ($this->_pos < $this->_dataSize) {
$code = $this->_GetInt2d($this->_data, $this->_pos);
switch ($code) {
case self::XLS_Type_BOF:
$length = $this->_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
// do not use this version information for anything
// it is unreliable (OpenOffice doc, 5.8), use only version information from the global stream
// offset: 2; size: 2; type of the following data
$substreamType = $this->_GetInt2d($recordData, 2);
if ($substreamType != self::XLS_Worksheet) {
break 2;
}
break;
case self::XLS_Type_PRINTGRIDLINES:
$this->_readPrintGridlines();
break;
case self::XLS_Type_DEFAULTROWHEIGHT:
$this->_readDefaultRowHeight();
break;
case self::XLS_Type_SHEETPR:
$this->_readSheetPr();
break;
case self::XLS_Type_HORIZONTALPAGEBREAKS:
$this->_readHorizontalPageBreaks();
break;
case self::XLS_Type_VERTICALPAGEBREAKS:
$this->_readVerticalPageBreaks();
break;
case self::XLS_Type_HEADER:
$this->_readHeader();
break;
case self::XLS_Type_FOOTER:
$this->_readFooter();
break;
case self::XLS_Type_HCENTER:
$this->_readHcenter();
break;
case self::XLS_Type_VCENTER:
$this->_readVcenter();
break;
case self::XLS_Type_LEFTMARGIN:
$this->_readLeftMargin();
break;
case self::XLS_Type_RIGHTMARGIN:
$this->_readRightMargin();
break;
case self::XLS_Type_TOPMARGIN:
$this->_readTopMargin();
break;
case self::XLS_Type_BOTTOMMARGIN:
$this->_readBottomMargin();
break;
case self::XLS_Type_PAGESETUP:
$this->_readPageSetup();
break;
case self::XLS_Type_PROTECT:
$this->_readProtect();
break;
case self::XLS_Type_PASSWORD:
$this->_readPassword();
break;
case self::XLS_Type_DEFCOLWIDTH:
$this->_readDefColWidth();
break;
case self::XLS_Type_COLINFO:
$this->_readColInfo();
break;
case self::XLS_Type_DIMENSION:
$this->_readDefault();
break;
case self::XLS_Type_ROW:
$this->_readRow();
break;
case self::XLS_Type_DBCELL:
$this->_readDefault();
break;
case self::XLS_Type_RK:
$this->_readRk();
break;
case self::XLS_Type_LABELSST:
$this->_readLabelSst();
break;
case self::XLS_Type_MULRK:
$this->_readMulRk();
break;
case self::XLS_Type_NUMBER:
$this->_readNumber();
break;
case self::XLS_Type_FORMULA:
$this->_readFormula();
break;
case self::XLS_Type_BOOLERR:
$this->_readBoolErr();
break;
case self::XLS_Type_MULBLANK:
$this->_readMulBlank();
break;
case self::XLS_Type_LABEL:
$this->_readLabel();
break;
case self::XLS_Type_BLANK:
$this->_readBlank();
break;
case self::XLS_Type_MSODRAWING:
$this->_readMsoDrawing();
break;
case self::XLS_Type_OBJ:
$this->_readObj();
break;
case self::XLS_Type_WINDOW2:
$this->_readWindow2();
break;
case self::XLS_Type_SCL:
$this->_readScl();
break;
case self::XLS_Type_PANE:
$this->_readPane();
break;
case self::XLS_Type_MERGEDCELLS:
$this->_readMergedCells();
break;
case self::XLS_Type_HYPERLINK:
$this->_readHyperLink();
break;
case self::XLS_Type_RANGEPROTECTION:
$this->_readRangeProtection();
break;
//case self::XLS_Type_IMDATA: $this->_readImData(); break;
//case self::XLS_Type_IMDATA: $this->_readImData(); break;
case self::XLS_Type_EOF:
$this->_readDefault();
break 2;
default:
$this->_readDefault();
break;
}
}
// treat MSODRAWING records, sheet-level Escher
if (!$this->_readDataOnly && $this->_drawingData) {
$escherWorksheet = new PHPExcel_Shared_Escher();
$reader = new PHPExcel_Reader_Excel5_Escher($escherWorksheet);
$escherWorksheet = $reader->load($this->_drawingData);
// debug Escher stream
//$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
//$debug->load($this->_drawingData);
$spContainerCollection = $escherWorksheet->getDgContainer()->getSpgrContainer()->getSpContainerCollection();
}
// treat OBJ records
foreach ($this->_objs as $n => $obj) {
// skip first shape container which holds the shape group, hence $n + 1
$spContainer = $spContainerCollection[$n + 1];
switch ($obj['type']) {
case 0x8:
// picture
// get index to BSE entry (1-based)
$BSEindex = $spContainer->getOPT(0x104);
$BSECollection = $escherWorkbook->getDggContainer()->getBstoreContainer()->getBSECollection();
$BSE = $BSECollection[$BSEindex - 1];
$blipType = $BSE->getBlipType();
$blip = $BSE->getBlip();
$ih = imagecreatefromstring($blip->getData());
$drawing = new PHPExcel_Worksheet_MemoryDrawing();
$drawing->setImageResource($ih);
switch ($blipType) {
case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG:
$drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_JPEG);
break;
case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG:
$drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG);
$drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_PNG);
break;
}
$drawing->setWorksheet($this->_phpSheet);
$drawing->setCoordinates($spContainer->getStartCoordinates());
break;
default:
// other object type
break;
}
}
}
// add the named ranges (defined names)
foreach ($this->_definedname as $definedName) {
if ($definedName['isBuiltInName']) {
switch ($definedName['name']) {
case pack('C', 0x6):
// print area
// in general, formula looks like this: Foo!$C$7:$J$66,Bar!$A$1:$IV$2
$ranges = explode(',', $definedName['formula']);
// FIXME: what if sheetname contains comma?
foreach ($ranges as $range) {
// $range should look like this one of these
// Foo!$C$7:$J$66
// Bar!$A$1:$IV$2
$explodes = explode('!', $range);
if (count($explodes) == 2) {
if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
$extractedRange = $explodes[1];
$extractedRange = str_replace('$', '', $extractedRange);
$docSheet->getPageSetup()->setPrintArea($extractedRange);
}
}
}
break;
case pack('C', 0x7):
// print titles (repeating rows)
// Assuming BIFF8, there are 3 cases
// 1. repeating rows
// formula looks like this: Sheet!$A$1:$IV$2
// rows 1-2 repeat
// 2. repeating columns
// formula looks like this: Sheet!$A$1:$B$65536
// columns A-B repeat
// 3. both repeating rows and repeating columns
// formula looks like this: Sheet!$A$1:$B$65536,Sheet!$A$1:$IV$2
$ranges = explode(',', $definedName['formula']);
// FIXME: what if sheetname contains comma?
foreach ($ranges as $range) {
// $range should look like this one of these
// Sheet!$A$1:$B$65536
// Sheet!$A$1:$IV$2
$explodes = explode('!', $range);
if (count($explodes) == 2) {
if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
$extractedRange = $explodes[1];
$extractedRange = str_replace('$', '', $extractedRange);
$coordinateStrings = explode(':', $extractedRange);
if (count($coordinateStrings) == 2) {
list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[0]);
list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[1]);
if ($firstColumn == 'A' and $lastColumn == 'IV') {
// then we have repeating rows
$docSheet->getPageSetup()->setRowsToRepeatAtTop(array($firstRow, $lastRow));
} elseif ($firstRow == 1 and $lastRow == 65536) {
// then we have repeating columns
$docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($firstColumn, $lastColumn));
}
}
}
}
}
break;
}
} else {
// Extract range
$explodes = explode('!', $definedName['formula']);
if (count($explodes) == 2) {
if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
$extractedRange = $explodes[1];
$extractedRange = str_replace('$', '', $extractedRange);
$this->_phpExcel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $docSheet, $extractedRange, true));
}
}
}
}
return $this->_phpExcel;
}
<?php
require 'config.php';
require 'testexcel/Classes/PHPExcel.php';
$sql = "SELECT * FROM devices";
$result = $conn->query($sql);
$objPHPExcel = new PHPExcel();
require 'getfloorrooms.php';
//print_r($allrooms);
//die();
$sheetindex = 0;
$objPHPExcel->removeSheetByIndex($sheetindex);
foreach ($allrooms as &$room) {
$objPHPExcel->createSheet($sheetindex);
$objPHPExcel->setActiveSheetIndex($sheetindex);
$objPHPExcel->getActiveSheet()->setTitle($room['name']);
$roomid = $room['id'];
unset($devices);
require 'getdevices.php';
$objPHPExcel->getActiveSheet()->SetCellValue('A' . "1", "Device ID");
$objPHPExcel->getActiveSheet()->SetCellValue('B' . "1", "HB Number");
$objPHPExcel->getActiveSheet()->SetCellValue('C' . "1", "Type");
$objPHPExcel->getActiveSheet()->SetCellValue('D' . "1", "Make");
$objPHPExcel->getActiveSheet()->SetCellValue('E' . "1", "Working?");
$objPHPExcel->getActiveSheet()->SetCellValue('F' . "1", "Added by User");
$objPHPExcel->getActiveSheet()->SetCellValue('G' . "1", "Comments");
$objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFont()->setBold(true);
$rowCount = 2;
foreach ($devices as &$row) {
$objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $row['id']);
$objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $row['hbnumber']);
/**
* remove worksheet
*
* @access protected
* @param int $index worksheet index
*/
protected function remove_worksheet($index)
{
$this->_spreadsheet->removeSheetByIndex($index);
unset($this->_worksheets[$index]);
}
function genera_excel_obras($header_datos = array(), $data = array(), $plazas = array())
{
if (empty($header_datos) || empty($data)) {
return false;
}
$this->load->library('excel');
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Ape plazas")->setLastModifiedBy("Ape plazas")->setTitle("Ape plazas");
// Header
$current_col = 0;
$XXAPE_INMUEBLE = $objPHPExcel->createSheet();
$XXAPE_INMUEBLE->setTitle("XXAPE_INMUEBLE");
$header_xxape_inmueble = array('INMUEBLE_ID', 'CODIGO_INMUEBLE', 'NOMBRE_INMUEBLE', 'STATUS', 'FECHA_INICIO', 'FECHA_FIN', 'RAZON_SOCIAL_ID', 'BANCO_ID', 'NO_CUENTA', 'CLAVE_SERVICIO', 'DIAS_PAGO', 'MOTIVO_CAMBIO', 'CREATED_BY', 'CREATION_DATE', 'LAST_UPDATED_BY', 'LAST_UPDATE_DATE', 'PLANO');
foreach ($header_xxape_inmueble as $head) {
$XXAPE_INMUEBLE->setCellValueByColumnAndRow($current_col, 1, $head);
++$current_col;
}
// Freeze panes
$XXAPE_INMUEBLE->freezePane('A2');
// Rows to repeat at top
$XXAPE_INMUEBLE->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);
//Rows
$row_col = 2;
foreach ($plazas as $value) {
$XXAPE_INMUEBLE->setCellValueByColumnAndRow(0, $row_col, $value->Sucursal);
++$row_col;
}
// Header
$current_col = 0;
$XXAPE_PREDIAL = $objPHPExcel->createSheet();
$XXAPE_PREDIAL->setTitle("XXAPE_PREDIAL");
$header_xxape_predial = array('PREDIO_ID', 'INMUEBLE_ID', 'INMUEBLE_ID', 'NOMBRE_DE_PREDIO', 'ESTATUS_DE_PREDIO', 'FECHA_INICIO', 'FECHA_FIN', 'SUPERFICIE_TERRENO', 'CALLE', 'NUMERO_EXTERIOR', 'NUMERO_INTERIOR', 'COLONIA', 'DELEGACION_MUNICIPIO', 'CODIGO_POSTAL', 'ESTADO', 'CIUDAD', 'PAIS', 'NUMERO_PREDIAL', 'CREATED_BY', 'CREATION_DATE', 'LAST_UPDATED_BY', 'LAST_UPDATE_DATE', 'MOTIVO_CAMBIO');
foreach ($header_xxape_predial as $head) {
$XXAPE_PREDIAL->setCellValueByColumnAndRow($current_col, 1, $head);
++$current_col;
}
// Header
$current_col = 0;
$XXAPE_PISO = $objPHPExcel->createSheet();
$XXAPE_PISO->setTitle("XXAPE_PISO");
$header_xxape_piso = array('PISO_ID', 'INMUEBLE_ID', 'PREDIO_ID', 'ESTATUS_PISO', 'CATEGORIA_PISO', 'NIVEL_PISO', 'AREA_CONSTRUIDA', 'AREA_RENTABLE', 'CREATED_BY', 'CREATION_DATE', 'LAST_UPDATED_BY', 'LAST_UPDATE_DATE', 'MOTIVO_CAMBIO');
foreach ($header_xxape_piso as $head) {
$XXAPE_PISO->setCellValueByColumnAndRow($current_col, 1, $head);
++$current_col;
}
// Header
$current_col = 0;
$XXAPE_LOCAL = $objPHPExcel->createSheet();
$XXAPE_LOCAL->setTitle("XXAPE_LOCAL");
$header_xxape_local = array('LOCAL_ID', 'INMUEBLE_ID', 'PREDIO_ID', 'PISO_ID', 'NUMERO', 'AREA_RENTABLE', 'TIPO_DE_LOCAL', 'CATEGORIA_LOCAL', 'ESTATUS_LOCAL', 'FECHA_INICIO_LOCAL', 'FECHA_FIN_LOCAL', 'AGRUPADO', 'LOCALES_AGRUPADOS', 'PLANO', 'CREATED_BY', 'CREATION_DATE', 'LAST_UPDATED_BY', 'LAST_UPDATE_DATE', 'MOTIVO_CAMBIO', 'USO_DE_LOCAL', 'LOCAL_INTELISIS_ID');
foreach ($header_xxape_local as $head) {
$XXAPE_LOCAL->setCellValueByColumnAndRow($current_col, 1, $head);
++$current_col;
}
// Header
$current_col = 0;
//$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->removeSheetByIndex(0);
$DATOS = $objPHPExcel->createSheet();
$DATOS->setTitle("DATOS");
foreach ($header_datos as $head) {
$DATOS->setCellValueByColumnAndRow($current_col, 1, $head);
++$current_col;
}
// Freeze panes
$DATOS->freezePane('A2');
// Rows to repeat at top
$DATOS->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);
//Rows
foreach ($data as $key => $dat) {
$current_col = 0;
if (is_array($dat) || is_object($dat)) {
foreach ($dat as $value) {
$DATOS->setCellValueByColumnAndRow($current_col, $key + 2, $value);
++$current_col;
}
}
}
//Autosizes Columns
$currentColDim = 0;
foreach ($header_datos as $head) {
$DATOS->getColumnDimensionByColumn($currentColDim)->setAutoSize(true);
++$currentColDim;
}
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Products_' . date('dMy') . '.xls"');
header('Cache-control: private, must-revalidate');
$objWriter->save('php://output');
exit;
}
public function exportRsvpReport(SS_HTTPRequest $request)
{
try {
$query_string = $request->getVars();
$search_term = isset($query_string['term']) ? Convert::raw2sql($query_string['term']) : '';
$summit_id = intval($request->param('SUMMIT_ID'));
$summit = $this->summit_repository->getById($summit_id);
if (is_null($summit)) {
throw new NotFoundEntityException('Summit', sprintf(' id %s', $summit_id));
}
$ext = 'csv';
$events = $this->event_repository->searchBySummitTermAndHasRSVP($summit, $search_term);
if (count($events)) {
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("OpenStack");
$objPHPExcel->getProperties()->setTitle("RSVP per Event Report");
foreach ($events as $event) {
$room_cap = $event->Location() && $event->Location()->is_a('SummitVenueRoom') ? $event->Location()->Capacity : 0;
list($regulars, $reg_count) = $this->rsvp_repository->getByEventAndType($event->ID, 'Regular');
list($waitlists, $wait_count) = $this->rsvp_repository->getByEventAndType($event->ID, 'WaitList');
list($rsvps, $total) = $this->rsvp_repository->getByEventPaged($event->ID, null, null);
$rsvp_array_template = array();
$headers = array();
$active_sheet = $objPHPExcel->createSheet();
$active_sheet->setTitle('Event ' . $event->Location());
foreach ($event->RSVPTemplate()->Questions()->sort('Order') as $question) {
if ($question->Label) {
$rsvp_array_template[$question->Label] = '';
$headers[] = $question->Label;
}
}
$headers[] = 'Seat Type';
$active_sheet->setCellValue('A1', $event->getTitleAndTime());
$active_sheet->mergeCells('A1:K1');
$active_sheet->setCellValue('A3', 'Room Total:');
$active_sheet->setCellValue('B3', $room_cap);
$active_sheet->setCellValue('A4', 'RSVP #:');
$active_sheet->setCellValue('B4', $reg_count);
$active_sheet->setCellValue('C4', 'WaitList #:');
$active_sheet->setCellValue('D4', $wait_count);
$active_sheet->fromArray($headers, NULL, 'A6');
$active_sheet->getStyle("A6:K6")->getFont()->setBold(true);
if (count($rsvps)) {
foreach ($rsvps as $key => $rsvp) {
$row = $key + 7;
$rsvp_array = $rsvp_array_template;
foreach ($rsvp->Answers() as $answer) {
$rsvp_array[$answer->Question()->Label] = $answer->getFormattedAnswer();
}
$rsvp_array['Seat Type'] = $rsvp->SeatType;
$active_sheet->fromArray($rsvp_array, NULL, 'A' . $row);
}
}
}
$filename = count($events) == 1 ? $event->getTitleForUrl() . "-" . date('Ymd') . ".xlsx" : "rsvp_report-" . date('Ymd') . ".xlsx";
$objPHPExcel->removeSheetByIndex(0);
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="' . $filename . '"');
$objWriter->save('php://output');
return;
}
return $this->notFound();
} catch (NotFoundEntityException $ex2) {
SS_Log::log($ex2->getMessage(), SS_Log::WARN);
return $this->notFound($ex2->getMessage());
} catch (Exception $ex) {
SS_Log::log($ex->getMessage(), SS_Log::ERR);
return $ex->getMessage();
}
}
public function componentResults_normal_sheet()
{
$this->custom_columns = entities\CustomDatatype::getAll();
$this->cc = isset($this->cc) ? $this->cc : 0;
require realpath(THEBUGGENIE_VENDOR_PATH) . DS . 'phpoffice' . DS . 'phpexcel' . DS . 'Classes' . DS . 'PHPExcel.php';
$phpexcel = new \PHPExcel();
foreach ($phpexcel->getAllSheets() as $index => $sheet) {
$phpexcel->removeSheetByIndex($index);
}
$this->phpexcel = $phpexcel;
$this->sheet = $phpexcel->createSheet();
}
/**
* Cleanup the Excel file
*/
protected function cleanup()
{
if (count($this->labels)) {
$this->xls->removeSheetByIndex(0);
}
}
/**
* Panes are frozen? (in sheet currently being read). See WINDOW2 record.
*
* @var boolean
*/
private $_frozen;
/**
* Fit printout to number of pages? (in sheet currently being read). See SHEETPR record.
*
* @var boolean
*/
private $_isFitToPages;
/**
* Objects. One OBJ record contributes with one entry.
*
* @var array
*/
private $_objs;
/**
* Text Objects. One TXO record corresponds with one entry.
*
* @var array
*/
private $_textObjects;
/**
* Cell Annotations (BIFF8)
*
* @var array
*/
private $_cellNotes;
/**
* The combined MSODRAWINGGROUP data
*
* @var string
*/
private $_drawingGroupData;
/**
* The combined MSODRAWING data (per sheet)
*
* @var string
*/
private $_drawingData;
/**
* Keep track of XF index
*
* @var int
*/
private $_xfIndex;
/**
* Mapping of XF index (that is a cell XF) to final index in cellXf collection
*
* @var array
*/
private $_mapCellXfIndex;
/**
* Mapping of XF index (that is a style XF) to final index in cellStyleXf collection
*
* @var array
*/
private $_mapCellStyleXfIndex;
/**
* The shared formulas in a sheet. One SHAREDFMLA record contributes with one value.
*
* @var array
*/
private $_sharedFormulas;
/**
* The shared formula parts in a sheet. One FORMULA record contributes with one value if it
* refers to a shared formula.
*
* @var array
*/
private $_sharedFormulaParts;
/**
* Read data only?
* If this is true, then the Reader will only read data values for cells, it will not read any formatting information.
* If false (the default) it will read data and formatting.
*
* @return boolean
*/
public function getReadDataOnly()
{
return $this->_readDataOnly;
}
/**
* Set read data only
* Set to true, to advise the Reader only to read data values for cells, and to ignore any formatting information.
* Set to false (the default) to advise the Reader to read both data and formatting for cells.
*
* @param boolean $pValue
*
* @return PHPExcel_Reader_Excel5
*/
public function setReadDataOnly($pValue = false)
{
$this->_readDataOnly = $pValue;
return $this;
}
/**
* Get which sheets to load
* Returns either an array of worksheet names (the list of worksheets that should be loaded), or a null
* indicating that all worksheets in the workbook should be loaded.
*
* @return mixed
*/
public function getLoadSheetsOnly()
{
return $this->_loadSheetsOnly;
}
/**
* Set which sheets to load
*
* @param mixed $value
* This should be either an array of worksheet names to be loaded, or a string containing a single worksheet name.
* If NULL, then it tells the Reader to read all worksheets in the workbook
*
* @return PHPExcel_Reader_Excel5
*/
public function setLoadSheetsOnly($value = null)
{
$this->_loadSheetsOnly = is_array($value) ? $value : array($value);
return $this;
}
/**
* Set all sheets to load
* Tells the Reader to load all worksheets from the workbook.
*
* @return PHPExcel_Reader_Excel5
*/
public function setLoadAllSheets()
{
$this->_loadSheetsOnly = null;
return $this;
}
/**
* Read filter
*
* @return PHPExcel_Reader_IReadFilter
*/
public function getReadFilter()
{
return $this->_readFilter;
}
/**
* Set read filter
*
* @param PHPExcel_Reader_IReadFilter $pValue
* @return PHPExcel_Reader_Excel5
*/
public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue)
{
$this->_readFilter = $pValue;
return $this;
}
/**
* Create a new PHPExcel_Reader_Excel5 instance
*/
public function __construct()
{
$this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
}
/**
* Can the current PHPExcel_Reader_IReader read the file?
*
* @param string $pFileName
* @return boolean
*/
public function canRead($pFilename)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
try {
// Use ParseXL for the hard work.
$ole = new PHPExcel_Shared_OLERead();
// get excel data
$res = $ole->read($pFilename);
return true;
} catch (Exception $e) {
return false;
}
}
/**
* Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object
*
* @param string $pFilename
* @throws Exception
*/
public function listWorksheetNames($pFilename)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
$worksheetNames = array();
// Read the OLE file
$this->_loadOLE($pFilename);
// total byte size of Excel data (workbook global substream + sheet substreams)
$this->_dataSize = strlen($this->_data);
$this->_pos = 0;
$this->_sheets = array();
// Parse Workbook Global Substream
while ($this->_pos < $this->_dataSize) {
$code = self::_GetInt2d($this->_data, $this->_pos);
switch ($code) {
case self::XLS_Type_BOF:
$this->_readBof();
break;
case self::XLS_Type_SHEET:
$this->_readSheet();
break;
case self::XLS_Type_EOF:
$this->_readDefault();
break 2;
default:
$this->_readDefault();
break;
}
}
foreach ($this->_sheets as $sheet) {
if ($sheet['sheetType'] != 0x0) {
// 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
continue;
}
$worksheetNames[] = $sheet['name'];
}
return $worksheetNames;
}
/**
* Loads PHPExcel from file
*
* @param string $pFilename
* @return PHPExcel
* @throws Exception
*/
public function load($pFilename)
{
// Read the OLE file
$this->_loadOLE($pFilename);
// Initialisations
$this->_phpExcel = new PHPExcel();
$this->_phpExcel->removeSheetByIndex(0);
// remove 1st sheet
if (!$this->_readDataOnly) {
$this->_phpExcel->removeCellStyleXfByIndex(0);
// remove the default style
$this->_phpExcel->removeCellXfByIndex(0);
// remove the default style
}
// Read the summary information stream (containing meta data)
$this->_readSummaryInformation();
// Read the Additional document summary information stream (containing application-specific meta data)
$this->_readDocumentSummaryInformation();
// total byte size of Excel data (workbook global substream + sheet substreams)
$this->_dataSize = strlen($this->_data);
// initialize
$this->_pos = 0;
$this->_codepage = 'CP1252';
$this->_formats = array();
$this->_objFonts = array();
$this->_palette = array();
$this->_sheets = array();
$this->_externalBooks = array();
$this->_ref = array();
$this->_definedname = array();
$this->_sst = array();
$this->_drawingGroupData = '';
$this->_xfIndex = '';
$this->_mapCellXfIndex = array();
$this->_mapCellStyleXfIndex = array();
// Parse Workbook Global Substream
while ($this->_pos < $this->_dataSize) {
$code = self::_GetInt2d($this->_data, $this->_pos);
switch ($code) {
case self::XLS_Type_BOF:
$this->_readBof();
break;
case self::XLS_Type_FILEPASS:
$this->_readFilepass();
break;
case self::XLS_Type_CODEPAGE:
$this->_readCodepage();
break;
case self::XLS_Type_DATEMODE:
$this->_readDateMode();
break;
case self::XLS_Type_FONT:
$this->_readFont();
break;
case self::XLS_Type_FORMAT:
$this->_readFormat();
break;
case self::XLS_Type_XF:
$this->_readXf();
break;
case self::XLS_Type_XFEXT:
$this->_readXfExt();
break;
case self::XLS_Type_STYLE:
$this->_readStyle();
break;
case self::XLS_Type_PALETTE:
$this->_readPalette();
break;
case self::XLS_Type_SHEET:
$this->_readSheet();
break;
case self::XLS_Type_EXTERNALBOOK:
$this->_readExternalBook();
break;
case self::XLS_Type_EXTERNNAME:
$this->_readExternName();
break;
case self::XLS_Type_EXTERNSHEET:
$this->_readExternSheet();
break;
case self::XLS_Type_DEFINEDNAME:
$this->_readDefinedName();
break;
case self::XLS_Type_MSODRAWINGGROUP:
$this->_readMsoDrawingGroup();
break;
case self::XLS_Type_SST:
$this->_readSst();
break;
case self::XLS_Type_EOF:
$this->_readDefault();
break 2;
default:
$this->_readDefault();
break;
}
}
// Resolve indexed colors for font, fill, and border colors
// Cannot be resolved already in XF record, because PALETTE record comes afterwards
if (!$this->_readDataOnly) {
foreach ($this->_objFonts as $objFont) {
if (isset($objFont->colorIndex)) {
$color = self::_readColor($objFont->colorIndex, $this->_palette, $this->_version);
$objFont->getColor()->setRGB($color['rgb']);
}
}
foreach ($this->_phpExcel->getCellXfCollection() as $objStyle) {
// fill start and end color
$fill = $objStyle->getFill();
if (isset($fill->startcolorIndex)) {
$startColor = self::_readColor($fill->startcolorIndex, $this->_palette, $this->_version);
$fill->getStartColor()->setRGB($startColor['rgb']);
}
if (isset($fill->endcolorIndex)) {
$endColor = self::_readColor($fill->endcolorIndex, $this->_palette, $this->_version);
$fill->getEndColor()->setRGB($endColor['rgb']);
}
// border colors
$top = $objStyle->getBorders()->getTop();
$right = $objStyle->getBorders()->getRight();
$bottom = $objStyle->getBorders()->getBottom();
$left = $objStyle->getBorders()->getLeft();
$diagonal = $objStyle->getBorders()->getDiagonal();
if (isset($top->colorIndex)) {
$borderTopColor = self::_readColor($top->colorIndex, $this->_palette, $this->_version);
$top->getColor()->setRGB($borderTopColor['rgb']);
}
if (isset($right->colorIndex)) {
$borderRightColor = self::_readColor($right->colorIndex, $this->_palette, $this->_version);
$right->getColor()->setRGB($borderRightColor['rgb']);
}
if (isset($bottom->colorIndex)) {
$borderBottomColor = self::_readColor($bottom->colorIndex, $this->_palette, $this->_version);
$bottom->getColor()->setRGB($borderBottomColor['rgb']);
}
if (isset($left->colorIndex)) {
$borderLeftColor = self::_readColor($left->colorIndex, $this->_palette, $this->_version);
$left->getColor()->setRGB($borderLeftColor['rgb']);
}
if (isset($diagonal->colorIndex)) {
$borderDiagonalColor = self::_readColor($diagonal->colorIndex, $this->_palette, $this->_version);
$diagonal->getColor()->setRGB($borderDiagonalColor['rgb']);
}
}
}
// treat MSODRAWINGGROUP records, workbook-level Escher
if (!$this->_readDataOnly && $this->_drawingGroupData) {
$escherWorkbook = new PHPExcel_Shared_Escher();
$reader = new PHPExcel_Reader_Excel5_Escher($escherWorkbook);
$escherWorkbook = $reader->load($this->_drawingGroupData);
// debug Escher stream
//$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
//$debug->load($this->_drawingGroupData);
}
// Parse the individual sheets
foreach ($this->_sheets as $sheet) {
if ($sheet['sheetType'] != 0x0) {
// 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
continue;
}
// check if sheet should be skipped
if (isset($this->_loadSheetsOnly) && !in_array($sheet['name'], $this->_loadSheetsOnly)) {
continue;
}
// add sheet to PHPExcel object
$this->_phpSheet = $this->_phpExcel->createSheet();
$this->_phpSheet->setTitle($sheet['name']);
$this->_phpSheet->setSheetState($sheet['sheetState']);
$this->_pos = $sheet['offset'];
// Initialize isFitToPages. May change after reading SHEETPR record.
$this->_isFitToPages = false;
// Initialize drawingData
$this->_drawingData = '';
// Initialize objs
$this->_objs = array();
// Initialize shared formula parts
$this->_sharedFormulaParts = array();
// Initialize shared formulas
$this->_sharedFormulas = array();
// Initialize text objs
$this->_textObjects = array();
// Initialize cell annotations
$this->_cellNotes = array();
$this->textObjRef = -1;
while ($this->_pos <= $this->_dataSize - 4) {
$code = self::_GetInt2d($this->_data, $this->_pos);
switch ($code) {
case self::XLS_Type_BOF:
$this->_readBof();
break;
case self::XLS_Type_PRINTGRIDLINES:
$this->_readPrintGridlines();
break;
case self::XLS_Type_DEFAULTROWHEIGHT:
$this->_readDefaultRowHeight();
break;
case self::XLS_Type_SHEETPR:
$this->_readSheetPr();
break;
case self::XLS_Type_HORIZONTALPAGEBREAKS:
$this->_readHorizontalPageBreaks();
break;
case self::XLS_Type_VERTICALPAGEBREAKS:
$this->_readVerticalPageBreaks();
break;
case self::XLS_Type_HEADER:
$this->_readHeader();
break;
case self::XLS_Type_FOOTER:
$this->_readFooter();
break;
case self::XLS_Type_HCENTER:
$this->_readHcenter();
break;
case self::XLS_Type_VCENTER:
$this->_readVcenter();
break;
case self::XLS_Type_LEFTMARGIN:
$this->_readLeftMargin();
break;
case self::XLS_Type_RIGHTMARGIN:
$this->_readRightMargin();
break;
case self::XLS_Type_TOPMARGIN:
$this->_readTopMargin();
break;
case self::XLS_Type_BOTTOMMARGIN:
$this->_readBottomMargin();
break;
case self::XLS_Type_PAGESETUP:
$this->_readPageSetup();
break;
case self::XLS_Type_PROTECT:
$this->_readProtect();
break;
case self::XLS_Type_SCENPROTECT:
$this->_readScenProtect();
break;
case self::XLS_Type_OBJECTPROTECT:
$this->_readObjectProtect();
break;
case self::XLS_Type_PASSWORD:
$this->_readPassword();
break;
case self::XLS_Type_DEFCOLWIDTH:
$this->_readDefColWidth();
break;
case self::XLS_Type_COLINFO:
$this->_readColInfo();
break;
case self::XLS_Type_DIMENSION:
$this->_readDefault();
break;
case self::XLS_Type_ROW:
$this->_readRow();
break;
case self::XLS_Type_DBCELL:
$this->_readDefault();
break;
case self::XLS_Type_RK:
$this->_readRk();
break;
case self::XLS_Type_LABELSST:
$this->_readLabelSst();
break;
case self::XLS_Type_MULRK:
$this->_readMulRk();
break;
case self::XLS_Type_NUMBER:
$this->_readNumber();
break;
case self::XLS_Type_FORMULA:
$this->_readFormula();
break;
case self::XLS_Type_SHAREDFMLA:
$this->_readSharedFmla();
break;
case self::XLS_Type_BOOLERR:
$this->_readBoolErr();
break;
case self::XLS_Type_MULBLANK:
$this->_readMulBlank();
break;
case self::XLS_Type_LABEL:
$this->_readLabel();
break;
case self::XLS_Type_BLANK:
$this->_readBlank();
break;
case self::XLS_Type_MSODRAWING:
$this->_readMsoDrawing();
break;
case self::XLS_Type_OBJ:
$this->_readObj();
break;
case self::XLS_Type_WINDOW2:
$this->_readWindow2();
break;
case self::XLS_Type_SCL:
$this->_readScl();
break;
case self::XLS_Type_PANE:
$this->_readPane();
break;
case self::XLS_Type_SELECTION:
$this->_readSelection();
break;
case self::XLS_Type_MERGEDCELLS:
$this->_readMergedCells();
break;
/**
* 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);
// Use ParseXL for the hard work.
$this->_ole = new PHPExcel_Shared_OLERead();
$this->_rowoffset = $this->_coloffset = 0;
$this->_defaultEncoding = 'ISO-8859-1';
$this->_encoderFunction = function_exists('mb_convert_encoding') ? 'mb_convert_encoding' : 'iconv';
// get excel data
$res = $this->_ole->read($pFilename);
// oops, something goes wrong (Darko Miljanovic)
if ($res === false) {
// check error code
if ($this->_ole->error == 1) {
// bad file
throw new Exception('The filename ' . $pFilename . ' is not readable');
} elseif ($this->_ole->error == 2) {
throw new Exception('The filename ' . $pFilename . ' is not recognised as an Excel file');
}
// check other error codes here (eg bad fileformat, etc...)
}
$this->_data = $this->_ole->getWorkBook();
$this->_pos = 0;
/**
* PARSE WORKBOOK
*
**/
$pos = 0;
$code = ord($this->_data[$pos]) | ord($this->_data[$pos + 1]) << 8;
$length = ord($this->_data[$pos + 2]) | ord($this->_data[$pos + 3]) << 8;
$version = ord($this->_data[$pos + 4]) | ord($this->_data[$pos + 5]) << 8;
$substreamType = ord($this->_data[$pos + 6]) | ord($this->_data[$pos + 7]) << 8;
if ($version != self::XLS_BIFF8 && $version != self::XLS_BIFF7) {
return false;
}
if ($substreamType != self::XLS_WorkbookGlobals) {
return false;
}
$pos += $length + 4;
$code = ord($this->_data[$pos]) | ord($this->_data[$pos + 1]) << 8;
$length = ord($this->_data[$pos + 2]) | ord($this->_data[$pos + 3]) << 8;
$recordData = substr($this->_data, $pos + 4, $length);
while ($code != self::XLS_Type_EOF) {
switch ($code) {
case self::XLS_Type_SST:
/**
* SST - Shared String Table
*
* This record contains a list of all strings used anywhere
* in the workbook. Each string occurs only once. The
* workbook uses indexes into the list to reference the
* strings.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
**/
// offset: 0; size: 4; total number of strings
// offset: 4; size: 4; number of unique strings
$spos = $pos + 4;
$limitpos = $spos + $length;
$uniqueStrings = $this->_GetInt4d($this->_data, $spos + 4);
$spos += 8;
// loop through the Unicode strings (16-bit length)
for ($i = 0; $i < $uniqueStrings; $i++) {
if ($spos == $limitpos) {
// then we have reached end of SST record data
$opcode = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$conlength = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
if ($opcode != self::XLS_Type_CONTINUE) {
// broken file, something is wrong
return -1;
}
$spos += 4;
$limitpos = $spos + $conlength;
}
// Read in the number of characters in the Unicode string
$numChars = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$spos += 2;
// option flags
$optionFlags = ord($this->_data[$spos]);
$spos++;
// bit: 0; mask: 0x01; 0 = compressed; 1 = uncompressed
$asciiEncoding = ($optionFlags & 0x1) == 0;
// bit: 2; mask: 0x02; 0 = ordinary; 1 = Asian phonetic
$extendedString = ($optionFlags & 0x4) != 0;
// Asian phonetic
// bit: 3; mask: 0x03; 0 = ordinary; 1 = Rich-Text
$richString = ($optionFlags & 0x8) != 0;
if ($richString) {
// Read in the crun
// number of Rich-Text formatting runs
$formattingRuns = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$spos += 2;
}
if ($extendedString) {
// size of Asian phonetic setting
$extendedRunLength = $this->_GetInt4d($this->_data, $spos);
$spos += 4;
}
// read in the characters
$len = $asciiEncoding ? $numChars : $numChars * 2;
if ($spos + $len < $limitpos) {
$retstr = substr($this->_data, $spos, $len);
$spos += $len;
} else {
// found countinue record
$retstr = substr($this->_data, $spos, $limitpos - $spos);
$bytesRead = $limitpos - $spos;
// remaining characters in Unicode string
$charsLeft = $numChars - ($asciiEncoding ? $bytesRead : $bytesRead / 2);
$spos = $limitpos;
// keep reading the characters
while ($charsLeft > 0) {
// record data
$opcode = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
// length of continue record data
$conlength = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
if ($opcode != self::XLS_Type_CONTINUE) {
// broken file, something is wrong
return -1;
}
$spos += 4;
$limitpos = $spos + $conlength;
// option flags are repeated when Unicode string is split by a continue record
// OpenOffice.org documentation 5.21
$option = ord($this->_data[$spos]);
$spos += 1;
if ($asciiEncoding && $option == 0) {
// 1st fragment compressed
// this fragment compressed
$len = min($charsLeft, $limitpos - $spos);
$retstr .= substr($this->_data, $spos, $len);
$charsLeft -= $len;
$asciiEncoding = true;
} elseif (!$asciiEncoding && $option != 0) {
// 1st fragment uncompressed
// this fragment uncompressed
$len = min($charsLeft * 2, $limitpos - $spos);
$retstr .= substr($this->_data, $spos, $len);
$charsLeft -= $len / 2;
$asciiEncoding = false;
} elseif (!$asciiEncoding && $option == 0) {
// 1st fragment uncompressed
// this fragment compressed
$len = min($charsLeft, $limitpos - $spos);
for ($j = 0; $j < $len; $j++) {
$retstr .= $this->_data[$spos + $j] . chr(0);
}
$charsLeft -= $len;
$asciiEncoding = false;
} else {
// 1st fragment compressed
// this fragment uncompressed
$newstr = '';
for ($j = 0; $j < strlen($retstr); $j++) {
$newstr = $retstr[$j] . chr(0);
}
$retstr = $newstr;
$len = min($charsLeft * 2, $limitpos - $spos);
$retstr .= substr($this->_data, $spos, $len);
$charsLeft -= $len / 2;
$asciiEncoding = false;
}
$spos += $len;
}
}
//$retstr = ($asciiEncoding) ?
// $retstr : $this->_encodeUTF16($retstr);
// convert string according codepage and BIFF version
if ($version == self::XLS_BIFF8) {
$retstr = $this->_encodeUTF16($retstr, $asciiEncoding);
} else {
// SST only occurs in BIFF8, so why this part?
$retstr = $this->_decodeCodepage($retstr);
}
$fmtRuns = array();
if ($richString) {
// list of formatting runs
for ($j = 0; $j < $formattingRuns; $j++) {
// first formatted character; zero-based
$charPos = $this->_getInt2d($this->_data, $spos + $j * 4);
// index to font record
$fontIndex = $this->_getInt2d($this->_data, $spos + 2 + $j * 4);
$fmtRuns[] = array('charPos' => $charPos, 'fontIndex' => $fontIndex);
}
$spos += 4 * $formattingRuns;
}
if ($extendedString) {
// For Asian phonetic settings, we skip the extended string data
$spos += $extendedRunLength;
}
$this->_sst[] = array('value' => $retstr, 'fmtRuns' => $fmtRuns);
}
break;
case self::XLS_Type_FILEPASS:
/**
* SHEETPROTECTION
*
* This record is part of the File Protection Block. It
* contains information about the read/write password of the
* file. All record contents following this record will be
* encrypted.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
return false;
break;
case self::XLS_Type_EXTERNALBOOK:
break;
case self::XLS_Type_EXTSHEET:
// external sheet references provided for named cells
if ($version == self::XLS_BIFF8) {
$xpos = $pos + 4;
$xcnt = ord($this->_data[$xpos]) | ord($this->_data[$xpos + 1]) << 8;
for ($x = 0; $x < $xcnt; $x++) {
$this->_extshref[$x] = ord($this->_data[$xpos + 4 + 6 * $x]) | ord($this->_data[$xpos + 5 + 6 * $x]) << 8;
}
}
// this if statement is going to replace the above one later
if ($version == self::XLS_BIFF8) {
// offset: 0; size: 2; number of following ref structures
$nm = $this->_GetInt2d($recordData, 0);
for ($i = 0; $i < $nm; $i++) {
$this->_ref[] = array('externalBookIndex' => $this->_getInt2d($recordData, 2 + 6 * $i), 'firstSheetIndex' => $this->_getInt2d($recordData, 4 + 6 * $i), 'lastSheetIndex' => $this->_getInt2d($recordData, 6 + 6 * $i));
}
}
break;
case self::XLS_Type_NAME:
/**
* DEFINEDNAME
*
* This record is part of a Link Table. It contains the name
* and the token array of an internal defined name. Token
* arrays of defined names contain tokens with aberrant
* token classes.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// retrieves named cells
$npos = $pos + 4;
$opts = ord($this->_data[$npos]) | ord($this->_data[$npos + 1]) << 8;
$nlen = ord($this->_data[$npos + 3]);
$flen = ord($this->_data[$npos + 4]) | ord($this->_data[$npos + 5]) << 8;
$fpos = $npos + 14 + 1 + $nlen;
$nstr = substr($this->_data, $npos + 15, $nlen);
$ftoken = ord($this->_data[$fpos]);
if ($ftoken == 58 && $opts == 0 && $flen == 7) {
$xref = ord($this->_data[$fpos + 1]) | ord($this->_data[$fpos + 2]) << 8;
$frow = ord($this->_data[$fpos + 3]) | ord($this->_data[$fpos + 4]) << 8;
$fcol = ord($this->_data[$fpos + 5]);
if (array_key_exists($xref, $this->_extshref)) {
$fsheet = $this->_extshref[$xref];
} else {
$fsheet = '';
}
$this->_namedcells[$nstr] = array('sheet' => $fsheet, 'row' => $frow, 'column' => $fcol);
}
break;
case self::XLS_Type_FORMAT:
/**
* FORMAT
*
* This record contains information about a number format.
* All FORMAT records occur together in a sequential list.
*
* In BIFF2-BIFF4 other records referencing a FORMAT record
* contain a zero-based index into this list. From BIFF5 on
* the FORMAT record contains the index itself that will be
* used by other records.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
//$indexCode = ord($this->_data[$pos + 4]) | ord($this->_data[$pos + 5]) << 8;
$indexCode = $this->_GetInt2d($recordData, 0);
/*
if ($version == self::XLS_BIFF8) {
*/
$formatString = $this->_readUnicodeStringLong(substr($recordData, 2));
/*
} else {
$numchars = ord($this->_data[$pos + 6]);
$formatString = substr($this->_data, $pos + 7, $numchars*2);
}
*/
$this->_formatRecords[$indexCode] = $formatString;
// now also stored in array _format[]
// _formatRecords[] will be removed from code later
$this->_numberFormat[$indexCode] = $formatString;
break;
case self::XLS_Type_FONT:
/**
* FONT
*/
$this->_font[] = $this->_readFont($recordData);
break;
case self::XLS_Type_XF:
/**
* XF - Extended Format
*
* This record contains formatting information for cells,
* rows, columns or styles.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$indexCode = ord($this->_data[$pos + 6]) | ord($this->_data[$pos + 7]) << 8;
if (array_key_exists($indexCode, $this->_dateFormats)) {
$this->_formatRecords['xfrecords'][] = array('type' => 'date', 'format' => $this->_dateFormats[$indexCode], 'code' => $indexCode);
} elseif (array_key_exists($indexCode, $this->_percentFormats)) {
$this->_formatRecords['xfrecords'][] = array('type' => 'percent', 'format' => $this->_percentFormats[$indexCode], 'code' => $indexCode);
} elseif (array_key_exists($indexCode, $this->_numberFormats)) {
$this->_formatRecords['xfrecords'][] = array('type' => 'number', 'format' => $this->_numberFormats[$indexCode], 'code' => $indexCode);
} else {
if ($indexCode > 0 && isset($this->_formatRecords[$indexCode])) {
// custom formats...
$formatstr = $this->_formatRecords[$indexCode];
if ($formatstr) {
// dvc: reg exp changed to custom date/time format chars
if (preg_match("/^[hmsdy]/i", $formatstr)) {
// custom datetime format
// dvc: convert Excel formats to PHP date formats
// first remove escapes related to non-format characters
$formatstr = str_replace('\\', '', $formatstr);
// 4-digit year
$formatstr = str_replace('yyyy', 'Y', $formatstr);
// 2-digit year
$formatstr = str_replace('yy', 'y', $formatstr);
// first letter of month - no php equivalent
$formatstr = str_replace('mmmmm', 'M', $formatstr);
// full month name
$formatstr = str_replace('mmmm', 'F', $formatstr);
// short month name
$formatstr = str_replace('mmm', 'M', $formatstr);
// mm is minutes if time or month w/leading zero
$formatstr = str_replace(':mm', ':i', $formatstr);
// tmp place holder
$formatstr = str_replace('mm', 'x', $formatstr);
// month no leading zero
$formatstr = str_replace('m', 'n', $formatstr);
// month leading zero
$formatstr = str_replace('x', 'm', $formatstr);
// 12-hour suffix
$formatstr = str_replace('AM/PM', 'A', $formatstr);
// tmp place holder
$formatstr = str_replace('dd', 'x', $formatstr);
// days no leading zero
$formatstr = str_replace('d', 'j', $formatstr);
// days leading zero
$formatstr = str_replace('x', 'd', $formatstr);
// seconds
$formatstr = str_replace('ss', 's', $formatstr);
// fractional seconds - no php equivalent
$formatstr = str_replace('.S', '', $formatstr);
if (!strpos($formatstr, 'A')) {
// 24-hour format
$formatstr = str_replace('h', 'H', $formatstr);
}
// user defined flag symbol????
$formatstr = str_replace(';@', '', $formatstr);
$this->_formatRecords['xfrecords'][] = array('type' => 'date', 'format' => $formatstr, 'code' => $indexCode);
} else {
if (preg_match('/%$/', $formatstr)) {
// % number format
if (preg_match('/\\.[#0]+/i', $formatstr, $m)) {
$s = substr($m[0], 0, 1) . (strlen($m[0]) - 1);
$formatstr = str_replace($m[0], $s, $formatstr);
}
if (preg_match('/^[#0]+/', $formatstr, $m)) {
$formatstr = str_replace($m[0], strlen($m[0]), $formatstr);
}
$formatstr = '%' . str_replace('%', "f%%", $formatstr);
$this->_formatRecords['xfrecords'][] = array('type' => 'percent', 'format' => $formatstr, 'code' => $indexCode);
} else {
// dvc: changed to add format to unknown for debug
$this->_formatRecords['xfrecords'][] = array('type' => 'other', 'format' => $this->_defaultFormat, 'code' => $indexCode);
}
}
}
} else {
// dvc: changed to add format to unknown for debug
if (isset($this->_formatRecords[$indexCode])) {
$formatstr = $this->_formatRecords[$indexCode];
$type = 'undefined';
} else {
$formatstr = $this->_defaultFormat;
$type = 'default';
}
$this->_formatRecords['xfrecords'][] = array('type' => $type, 'format' => $formatstr, 'code' => $indexCode);
}
}
// store styles in xf array
$this->_xf[] = $this->_readBIFF8Style($recordData);
break;
case self::XLS_Type_NINETEENFOUR:
/**
* DATEMODE
*
* This record specifies the base date for displaying date
* values. All dates are stored as count of days past this
* base date. In BIFF2-BIFF4 this record is part of the
* Calculation Settings Block. In BIFF5-BIFF8 it is
* stored in the Workbook Globals Substream.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$this->_nineteenFour = ord($this->_data[$pos + 4]) == 1;
/*
if (ord($this->_data[$pos + 4]) == 1) {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
} else {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
}
*/
break;
case self::XLS_Type_BOUNDSHEET:
/**
* SHEET
*
* This record is located in the Workbook Globals
* Substream and represents a sheet inside the workbook.
* One SHEET record is written for each sheet. It stores the
* sheet name and a stream offset to the BOF record of the
* respective Sheet Substream within the Workbook Stream.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$rec_offset = $this->_GetInt4d($this->_data, $pos + 4);
$rec_typeFlag = ord($this->_data[$pos + 8]);
$rec_visibilityFlag = ord($this->_data[$pos + 9]);
$rec_length = ord($this->_data[$pos + 10]);
if ($version == self::XLS_BIFF8) {
$compressedUTF16 = (ord($this->_data[$pos + 11]) & 0x1) == 0;
$rec_length = $compressedUTF16 ? $rec_length : $rec_length * 2;
$rec_name = $this->_encodeUTF16(substr($this->_data, $pos + 12, $rec_length), $compressedUTF16);
} elseif ($version == self::XLS_BIFF7) {
$rec_name = substr($this->_data, $pos + 11, $rec_length);
}
$this->_boundsheets[] = array('name' => $rec_name, 'offset' => $rec_offset);
break;
case self::XLS_Type_CODEPAGE:
/**
* CODEPAGE
*
* This record stores the text encoding used to write byte
* strings, stored as MS Windows code page identifier.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$codepage = $this->_GetInt2d($this->_data, $pos + 4);
switch ($codepage) {
case 367:
// ASCII
$this->_codepage = "ASCII";
break;
case 437:
//OEM US
$this->_codepage = "CP437";
break;
case 720:
//OEM Arabic
// currently not supported by libiconv
$this->_codepage = "";
break;
case 737:
//OEM Greek
$this->_codepage = "CP737";
break;
case 775:
//OEM Baltic
$this->_codepage = "CP775";
break;
case 850:
//OEM Latin I
$this->_codepage = "CP850";
break;
case 852:
//OEM Latin II (Central European)
$this->_codepage = "CP852";
break;
case 855:
//OEM Cyrillic
$this->_codepage = "CP855";
break;
case 857:
//OEM Turkish
$this->_codepage = "CP857";
break;
case 858:
//OEM Multilingual Latin I with Euro
$this->_codepage = "CP858";
break;
case 860:
//OEM Portugese
$this->_codepage = "CP860";
break;
case 861:
//OEM Icelandic
$this->_codepage = "CP861";
break;
case 862:
//OEM Hebrew
$this->_codepage = "CP862";
break;
case 863:
//OEM Canadian (French)
$this->_codepage = "CP863";
break;
case 864:
//OEM Arabic
$this->_codepage = "CP864";
break;
case 865:
//OEM Nordic
$this->_codepage = "CP865";
break;
case 866:
//OEM Cyrillic (Russian)
$this->_codepage = "CP866";
break;
case 869:
//OEM Greek (Modern)
$this->_codepage = "CP869";
break;
case 874:
//ANSI Thai
$this->_codepage = "CP874";
break;
case 932:
//ANSI Japanese Shift-JIS
$this->_codepage = "CP932";
break;
case 936:
//ANSI Chinese Simplified GBK
$this->_codepage = "CP936";
break;
case 949:
//ANSI Korean (Wansung)
$this->_codepage = "CP949";
break;
case 950:
//ANSI Chinese Traditional BIG5
$this->_codepage = "CP950";
break;
case 1200:
//UTF-16 (BIFF8)
$this->_codepage = "UTF-16LE";
break;
case 1250:
// ANSI Latin II (Central European)
$this->_codepage = "CP1250";
break;
case 1251:
//ANSI Cyrillic
$this->_codepage = "CP1251";
break;
case 1252:
//ANSI Latin I (BIFF4-BIFF7)
$this->_codepage = "CP1252";
break;
case 1253:
//ANSI Greek
$this->_codepage = "CP1253";
break;
case 1254:
//ANSI Turkish
$this->_codepage = "CP1254";
break;
case 1255:
//ANSI Hebrew
$this->_codepage = "CP1255";
break;
case 1256:
//ANSI Arabic
$this->_codepage = "CP1256";
break;
case 1257:
//ANSI Baltic
$this->_codepage = "CP1257";
break;
case 1258:
//ANSI Vietnamese
$this->_codepage = "CP1258";
break;
case 1361:
//ANSI Korean (Johab)
$this->_codepage = "CP1361";
break;
case 10000:
//Apple Roman
// currently not supported by libiconv
$this->_codepage = "";
break;
case 32768:
//Apple Roman
// currently not supported by libiconv
$this->_codepage = "";
break;
case 32769:
//ANSI Latin I (BIFF2-BIFF3)
// currently not supported by libiconv
$this->_codepage = "";
break;
}
break;
}
$pos += $length + 4;
$code = ord($this->_data[$pos]) | ord($this->_data[$pos + 1]) << 8;
$length = ord($this->_data[$pos + 2]) | ord($this->_data[$pos + 3]) << 8;
$recordData = substr($this->_data, $pos + 4, $length);
}
/**
*
* PARSE THE INDIVIDUAL SHEETS
*
**/
foreach ($this->_boundsheets as $key => $val) {
// add sheet to PHPExcel object
$sheet = $excel->createSheet();
$sheet->setTitle((string) $val['name']);
$this->_sn = $key;
$spos = $val['offset'];
$cont = true;
// read BOF
$code = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$length = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$version = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
$substreamType = ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8;
if ($version != self::XLS_BIFF8 && $version != self::XLS_BIFF7) {
return -1;
}
if ($substreamType != self::XLS_Worksheet) {
return -2;
}
$spos += $length + 4;
while ($cont) {
$lowcode = ord($this->_data[$spos]);
if ($lowcode == self::XLS_Type_EOF) {
break;
}
$code = $lowcode | ord($this->_data[$spos + 1]) << 8;
$length = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$recordData = substr($this->_data, $spos + 4, $length);
$spos += 4;
$this->_sheets[$this->_sn]['maxrow'] = $this->_rowoffset - 1;
$this->_sheets[$this->_sn]['maxcol'] = $this->_coloffset - 1;
unset($this->_rectype);
unset($this->_formula);
unset($this->_formula_result);
$this->_multiplier = 1;
// need for format with %
switch ($code) {
case self::XLS_Type_DIMENSION:
/**
* DIMENSION
*
* This record contains the range address of the used area
* in the current sheet.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
if (!isset($this->_numRows)) {
if ($length == 10 || $version == self::XLS_BIFF7) {
$this->_sheets[$this->_sn]['numRows'] = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$this->_sheets[$this->_sn]['numCols'] = ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8;
} else {
$this->_sheets[$this->_sn]['numRows'] = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
$this->_sheets[$this->_sn]['numCols'] = ord($this->_data[$spos + 10]) | ord($this->_data[$spos + 11]) << 8;
}
}
break;
case self::XLS_Type_MERGEDCELLS:
/**
* MERGEDCELLS
*
* This record contains the addresses of merged cell ranges
* in the current sheet.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$cellRanges = $this->_GetInt2d($this->_data, $spos);
for ($i = 0; $i < $cellRanges; $i++) {
$fr = $this->_GetInt2d($this->_data, $spos + 8 * $i + 2);
// first row
$lr = $this->_GetInt2d($this->_data, $spos + 8 * $i + 4);
// last row
$fc = $this->_GetInt2d($this->_data, $spos + 8 * $i + 6);
// first column
$lc = $this->_GetInt2d($this->_data, $spos + 8 * $i + 8);
// last column
// this part no longer needed, instead apply cell merge on PHPExcel worksheet object
/*
if ($lr - $fr > 0) {
$this->_sheets[$this->_sn]['cellsInfo'][$fr + 1][$fc + 1]['rowspan'] = $lr - $fr + 1;
}
if ($lc - $fc > 0) {
$this->_sheets[$this->_sn]['cellsInfo'][$fr + 1][$fc + 1]['colspan'] = $lc - $fc + 1;
}
*/
$sheet->mergeCellsByColumnAndRow($fc, $fr + 1, $lc, $lr + 1);
}
}
break;
case self::XLS_Type_RK:
case self::XLS_Type_RK2:
/**
* RK
*
* This record represents a cell that contains an RK value
* (encoded integer or floating-point value). If a
* floating-point value cannot be encoded to an RK value,
* a NUMBER record will be written. This record replaces the
* record INTEGER written in BIFF2.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$rknum = $this->_GetInt4d($this->_data, $spos + 6);
$numValue = $this->_GetIEEE754($rknum);
/*
if ($this->_isDate($spos)) {
list($string, $raw) = $this->_createDate($numValue);
} else {
$raw = $numValue;
if (isset($this->_columnsFormat[$column + 1])){
$this->_curformat = $this->_columnsFormat[$column + 1];
}
$string = sprintf($this->_curformat,$numValue*$this->_multiplier);
}
*/
// offset 4; size: 2; index to XF record
$xfindex = $this->_getInt2d($recordData, 4);
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$numValue = (int) PHPExcel_Shared_Date::ExcelToPHP($numValue);
}
}
//$this->_addcell($row, $column, $string, $raw);
//$sheet->setCellValueByColumnAndRow($column, $row + 1, $string);
$sheet->setCellValueByColumnAndRow($column, $row + 1, $numValue);
break;
case self::XLS_Type_LABELSST:
/**
* LABELSST
*
* This record represents a cell that contains a string. It
* replaces the LABEL record and RSTRING record used in
* BIFF2-BIFF5.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$xfindex = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
$index = $this->_GetInt4d($this->_data, $spos + 6);
//$this->_addcell($row, $column, $this->_sst[$index]);
if ($fmtRuns = $this->_sst[$index]['fmtRuns']) {
// then we have rich text
$richText = new PHPExcel_RichText($sheet->getCellByColumnAndRow($column, $row + 1));
$charPos = 0;
for ($i = 0; $i <= count($this->_sst[$index]['fmtRuns']); $i++) {
if (isset($fmtRuns[$i])) {
$text = mb_substr($this->_sst[$index]['value'], $charPos, $fmtRuns[$i]['charPos'] - $charPos, 'UTF-8');
$charPos = $fmtRuns[$i]['charPos'];
} else {
$text = mb_substr($this->_sst[$index]['value'], $charPos, mb_strlen($this->_sst[$index]['value']), 'UTF-8');
}
if (mb_strlen($text) > 0) {
$textRun = $richText->createTextRun($text);
if (isset($fmtRuns[$i - 1])) {
if ($fmtRuns[$i - 1]['fontIndex'] < 4) {
$fontIndex = $fmtRuns[$i - 1]['fontIndex'];
} else {
// this has to do with that index 4 is omitted in all BIFF versions for some strange reason
// check the OpenOffice documentation of the FONT record
$fontIndex = $fmtRuns[$i - 1]['fontIndex'] - 1;
}
$textRun->getFont()->applyFromArray($this->_font[$fontIndex]);
}
}
}
} else {
$sheet->setCellValueByColumnAndRow($column, $row + 1, $this->_sst[$index]['value']);
}
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
break;
case self::XLS_Type_MULRK:
/**
* MULRK - Multiple RK
*
* This record represents a cell range containing RK value
* cells. All cells are located in the same row.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$colFirst = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
$colLast = ord($this->_data[$spos + $length - 2]) | ord($this->_data[$spos + $length - 1]) << 8;
$columns = $colLast - $colFirst + 1;
$tmppos = $spos + 4;
for ($i = 0; $i < $columns; $i++) {
// offset: 0; size: 2; index to XF record
$xfindex = $this->_getInt2d($recordData, 4 + 6 * $i);
// offset: 2; size: 4; RK value
$numValue = $this->_GetIEEE754($this->_GetInt4d($this->_data, $tmppos + 2));
/*
if ($this->_isDate($tmppos-4)) {
list($string, $raw) = $this->_createDate($numValue);
} else {
$raw = $numValue;
if (isset($this->_columnsFormat[$colFirst + $i + 1])){
$this->_curformat = $this->_columnsFormat[$colFirst+ $i + 1];
}
$string = sprintf($this->_curformat, $numValue *
$this->_multiplier);
}
*/
//$this->_addcell($row, $colFirst + $i, $string, $raw);
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($colFirst + $i, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$numValue = (int) PHPExcel_Shared_Date::ExcelToPHP($numValue);
}
}
//$sheet->setCellValueByColumnAndRow($colFirst + $i, $row + 1, $string);
$sheet->setCellValueByColumnAndRow($colFirst + $i, $row + 1, $numValue);
$tmppos += 6;
}
break;
case self::XLS_Type_NUMBER:
/**
* NUMBER
*
* This record represents a cell that contains a
* floating-point value.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
// offset 4; size: 2; index to XF record
$xfindex = $this->_GetInt2d($recordData, 4);
$numValue = $this->_createNumber($spos);
/*
if ($this->_isDate($spos)) {
$numValue = $this->_createNumber($spos);
list($string, $raw) = $this->_createDate($numValue);
} else {
if (isset($this->_columnsFormat[$column + 1])) {
$this->_curformat = $this->_columnsFormat[$column + 1];
}
$raw = $this->_createNumber($spos);
$string = sprintf($this->_curformat, $raw * $this->_multiplier);
}
*/
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$numValue = (int) PHPExcel_Shared_Date::ExcelToPHP($numValue);
}
}
//$this->_addcell($row, $column, $string, $raw);
//$sheet->setCellValueByColumnAndRow($column, $row + 1, $string);
$sheet->setCellValueByColumnAndRow($column, $row + 1, $numValue);
break;
case self::XLS_Type_FORMULA:
case self::XLS_Type_FORMULA2:
/**
* FORMULA
*
* This record contains the token array and the result of a
* formula cell.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// offset: 0; size: 2; row index
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
// offset: 2; size: 2; col index
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
// offset: 4; size: 2; XF index
$xfindex = ord($this->_data[$spos + 4]) | ord($this->_data[$spos + 5]) << 8;
// offset: 6; size: 8; result of the formula
if (ord($this->_data[$spos + 6]) == 0 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//String formula. Result follows in appended STRING record
$this->_formula_result = 'string';
$soff = $spos + $length;
$scode = ord($this->_data[$soff]) | ord($this->_data[$soff + 1]) << 8;
$sopt = ord($this->_data[$soff + 6]);
// only reads byte strings...
if ($scode == self::XLS_Type_STRING && $sopt == '0') {
$slen = ord($this->_data[$soff + 4]) | ord($this->_data[$soff + 5]) << 8;
$string = substr($this->_data, $soff + 7, ord($this->_data[$soff + 4]) | ord($this->_data[$soff + 5]) << 8);
} else {
$string = 'NOT FOUND';
}
$raw = $string;
} elseif (ord($this->_data[$spos + 6]) == 1 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//Boolean formula. Result is in +2; 0=false,1=true
$this->_formula_result = 'boolean';
$raw = ord($this->_data[$spos + 8]);
if ($raw) {
$string = "TRUE";
} else {
$string = "FALSE";
}
} elseif (ord($this->_data[$spos + 6]) == 2 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//Error formula. Error code is in +2
$this->_formula_result = 'error';
$raw = ord($this->_data[$spos + 8]);
$string = 'ERROR:' . $raw;
} elseif (ord($this->_data[$spos + 6]) == 3 && ord($this->_data[$spos + 12]) == 255 && ord($this->_data[$spos + 13]) == 255) {
//Formula result is a null string
$this->_formula_result = 'null';
$raw = '';
$string = '';
} else {
// forumla result is a number, first 14 bytes like _NUMBER record
$string = $this->_createNumber($spos);
/*
$this->_formula_result = 'number';
if ($this->_isDate($spos)) {
$numValue = $this->_createNumber($spos);
list($string, $raw) = $this->_createDate($numValue);
} else {
if (isset($this->_columnsFormat[$column + 1])){
$this->_curformat = $this->_columnsFormat[$column + 1];
}
$raw = $this->_createNumber($spos);
$string = sprintf($this->_curformat, $raw * $this->_multiplier);
}
*/
}
// save the raw formula tokens for end user interpretation
// Excel stores as a token record
$this->_rectype = 'formula';
// read formula record tokens ...
$tokenlength = ord($this->_data[$spos + 20]) | ord($this->_data[$spos + 21]) << 8;
for ($i = 0; $i < $tokenlength; $i++) {
$this->_formula[$i] = ord($this->_data[$spos + 22 + $i]);
}
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
if (PHPExcel_Shared_Date::isDateTimeFormatCode($this->_xf[$xfindex]['numberformat']['code'])) {
$string = (int) PHPExcel_Shared_Date::ExcelToPHP($string);
}
}
//$this->_addcell($row, $column, $string, $raw);
$sheet->setCellValueByColumnAndRow($column, $row + 1, $string);
// offset: 14: size: 2; option flags, recalculate always, recalculate on open etc.
// offset: 16: size: 4; not used
// offset: 20: size: variable; formula structure
// WORK IN PROGRESS: TRUE FORMULA SUPPORT
// resolve BIFF8 formula tokens into human readable formula
// so it can be added as formula
// $formulaStructure = substr($recordData, 20);
// $formulaString = $this->_getFormulaStringFromStructure($formulaStructure); // get human language
break;
case self::XLS_Type_BOOLERR:
/**
* BOOLERR
*
* This record represents a Boolean value or error value
* cell.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// offset: 0; size: 2; row index
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
// offset: 2; size: 2; column index
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
// offset: 4; size: 2; index to XF record
$xfindex = $this->_GetInt2d($recordData, 4);
// offset: 6; size: 1; the boolean value or error value
$value = ord($recordData[6]);
// offset: 7; size: 1; 0=boolean; 1=error
$isError = ord($recordData[7]);
if (!$isError) {
$sheet->getCellByColumnAndRow($column, $row + 1)->setValueExplicit((bool) $value, PHPExcel_Cell_DataType::TYPE_BOOL);
}
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($column, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
break;
case self::XLS_Type_ROW:
/**
* ROW
*
* This record contains the properties of a single row in a
* sheet. Rows and cells in a sheet are divided into blocks
* of 32 rows.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2; index of this row
$r = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; index to column of the first cell which is described by a cell record
// offset: 4; size: 2; index to column of the last cell which is described by a cell record, increased by 1
// offset: 6; size: 2;
// bit: 14-0; mask: 0x7FF; height of the row, in twips = 1/20 of a point
$height = (0x7ff & $this->_GetInt2d($recordData, 6)) >> 0;
// bit: 15: mask: 0x8000; 0 = row has custom height; 1= row has default height
$useDefaultHeight = (0x8000 & $this->_GetInt2d($recordData, 6)) >> 15;
if (!$useDefaultHeight) {
$sheet->getRowDimension($r + 1)->setRowHeight($height / 20);
}
// offset: 8; size: 2; not used
// offset: 10; size: 2; not used in BIFF5-BIFF8
// offset: 12; size: 4; option flags and default row formatting
// bit: 2-0: mask: 0x00000007; outline level of the row
$level = (0x7 & $this->_GetInt4d($recordData, 12)) >> 0;
$sheet->getRowDimension($r + 1)->setOutlineLevel($level);
// bit: 4; mask: 0x00000010; 1 = outline group start or ends here... and is collapsed
$isCollapsed = (0x10 & $this->_GetInt4d($recordData, 12)) >> 4;
$sheet->getRowDimension($r + 1)->setCollapsed($isCollapsed);
// bit: 5; mask: 0x00000020; 1 = row is hidden
$isHidden = (0x20 & $this->_GetInt4d($recordData, 12)) >> 5;
$sheet->getRowDimension($r + 1)->setVisible(!$isHidden);
}
break;
case self::XLS_Type_DBCELL:
/**
* DBCELL
*
* This record is written once in a Row Block. It contains
* relative offsets to calculate the stream position of the
* first cell record for each row. The offset list in this
* record contains as many offsets as ROW records are
* present in the Row Block.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
break;
case self::XLS_Type_MULBLANK:
/**
* MULBLANK - Multiple BLANK
*
* This record represents a cell range of empty cells. All
* cells are located in the same row
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
// offset: 0; size: 2; index to row
$row = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; index to first column
$fc = $this->_GetInt2d($recordData, 2);
// offset: 4; size: 2 x nc; list of indexes to XF records
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
for ($i = 0; $i < $length / 2 - 4; $i++) {
$xfindex = $this->_GetInt2d($recordData, 4 + 2 * $i);
$sheet->getStyleByColumnAndRow($fc + $i, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
}
// offset: 6; size 2; index to last column (not needed)
break;
case self::XLS_Type_LABEL:
/**
* LABEL
*
* This record represents a cell that contains a string. In
* BIFF8 it is usually replaced by the LABELSST record.
* Excel still uses this record, if it copies unformatted
* text cells to the clipboard.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
$row = ord($this->_data[$spos]) | ord($this->_data[$spos + 1]) << 8;
$column = ord($this->_data[$spos + 2]) | ord($this->_data[$spos + 3]) << 8;
/*
$this->_addcell($row, $column, substr($this->_data, $spos + 8,
ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8));
*/
$sheet->setCellValueByColumnAndRow($column, $row + 1, substr($this->_data, $spos + 8, ord($this->_data[$spos + 6]) | ord($this->_data[$spos + 7]) << 8));
break;
case self::XLS_Type_PROTECT:
/**
* PROTECT - Sheet protection (BIFF2 through BIFF8)
* if this record is omitted, then it also means no sheet protection
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2;
// bit 0, mask 0x01; sheet protection
$isSheetProtected = (0x1 & $this->_GetInt2d($recordData, 0)) >> 0;
switch ($isSheetProtected) {
case 0:
break;
case 1:
$sheet->getProtection()->setSheet(true);
break;
}
}
break;
case self::XLS_Type_PASSWORD:
/**
* PASSWORD - Sheet protection (hashed) password (BIFF2 through BIFF8)
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2; 16-bit hash value of password
$password = strtoupper(dechex($this->_GetInt2d($recordData, 0)));
// the hashed password
$sheet->getProtection()->setPassword($password, true);
}
break;
case self::XLS_Type_COLINFO:
/**
* COLINFO - Column information
*/
if (!$this->_readDataOnly) {
// offset: 0; size: 2; index to first column in range
$fc = $this->_GetInt2d($recordData, 0);
// first column index
// offset: 2; size: 2; index to last column in range
$lc = $this->_GetInt2d($recordData, 2);
// first column index
// offset: 4; size: 2; width of the column in 1/256 of the width of the zero character
$width = $this->_GetInt2d($recordData, 4);
// offset: 6; size: 2; index to XF record for default column formatting
// offset: 8; size: 2; option flags
// bit: 0; mask: 0x0001; 1= columns are hidden
$isHidden = (0x1 & $this->_GetInt2d($recordData, 8)) >> 0;
// bit: 10-8; mask: 0x0700; outline level of the columns (0 = no outline)
$level = (0x700 & $this->_GetInt2d($recordData, 8)) >> 8;
// bit: 12; mask: 0x1000; 1 = collapsed
$isCollapsed = (0x1000 & $this->_GetInt2d($recordData, 8)) >> 12;
// offset: 10; size: 2; not used
for ($i = $fc; $i <= $lc; $i++) {
$sheet->getColumnDimensionByColumn($i)->setWidth($width / 256);
$sheet->getColumnDimensionByColumn($i)->setVisible(!$isHidden);
$sheet->getColumnDimensionByColumn($i)->setOutlineLevel($level);
$sheet->getColumnDimensionByColumn($i)->setCollapsed($isCollapsed);
}
}
break;
case self::XLS_Type_DEFCOLWIDTH:
// offset: 0; size: 2; row index
$width = $this->_GetInt2d($recordData, 0);
$sheet->getDefaultColumnDimension()->setWidth($width);
break;
case self::XLS_Type_DEFAULTROWHEIGHT:
// offset: 0; size: 2; option flags
// offset: 2; size: 2; default height for unused rows, (twips 1/20 point)
$height = $this->_GetInt2d($recordData, 2);
$sheet->getDefaultRowDimension()->setRowHeight($height / 20);
break;
case self::XLS_Type_BLANK:
// offset: 0; size: 2; row index
$row = $this->_GetInt2d($recordData, 0);
// offset: 2; size: 2; col index
$col = $this->_GetInt2d($recordData, 2);
// offset: 4; size: 2; XF index
$xfindex = $this->_GetInt2d($recordData, 4);
// add BIFF8 style information
if ($version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$sheet->getStyleByColumnAndRow($col, $row + 1)->applyFromArray($this->_xf[$xfindex]);
}
break;
case self::XLS_Type_SHEETPR:
// offset: 0; size: 2
// bit: 6; mask: 0x0040; 0 = outline buttons above outline group
$isSummaryBelow = (0x40 & $this->_GetInt2d($recordData, 0)) >> 6;
$sheet->setShowSummaryBelow($isSummaryBelow);
// bit: 7; mask: 0x0080; 0 = outline buttons left of outline group
$isSummaryRight = (0x80 & $this->_GetInt2d($recordData, 0)) >> 7;
$sheet->setShowSummaryRight($isSummaryRight);
break;
case self::XLS_Type_EOF:
$cont = false;
break;
default:
break;
}
$spos += $length;
}
if (!isset($this->_sheets[$this->_sn]['numRows'])) {
$this->_sheets[$this->_sn]['numRows'] = $this->_sheets[$this->_sn]['maxrow'];
}
if (!isset($this->_sheets[$this->_sn]['numCols'])) {
$this->_sheets[$this->_sn]['numCols'] = $this->_sheets[$this->_sn]['maxcol'];
}
}
/*
foreach($this->_boundsheets as $index => $details) {
$sheet = $excel->getSheet($index);
// read all the columns of all the rows !
$numrows = $this->_sheets[$index]['numRows'];
$numcols = $this->_sheets[$index]['numCols'];
for ($row = 0; $row < $numrows; $row++) {
for ($col = 0; $col < $numcols; $col++) {
$cellcontent = $cellinfo = null;
if (isset($this->_sheets[$index]['cells'][$row][$col])===true) {
$cellcontent = $this->_sheets[$index]['cells'][$row][$col];
} else {
continue;
}
if (isset($this->_sheets[$index]['cellsInfo'][$row][$col])===true) {
$cellinfo = $this->_sheets[$index]['cellsInfo'][$row][$col];
}
$sheet->setCellValueByColumnAndRow($col, $row + 1,
$cellcontent);
}
}
};
*/
return $excel;
}
public function index($userID = NULL)
{
$this->load->model('User_model', 'Curriculum_model');
$this->load->helper('url');
//Loader user from passed ID and advisor from session ID
// If the session id isn't the passed user or an advisor for the user, immediantly fail.
$user = new User_Model();
$user->loadPropertiesFromPrimaryKey($userID);
$advisor = new User_Model();
if (!isset($_SESSION["UserID"])) {
redirect('login');
}
$advisor->loadPropertiesFromPrimaryKey($_SESSION["UserID"]);
$flag = TRUE;
foreach ($advisor->getAdvisees() as $student) {
if ($student->getUserID() == $userID) {
$flag = FALSE;
}
}
if ($advisor->getUserID() == $user->getUserID()) {
$flag = FALSE;
}
if ($flag == TRUE) {
echo "YOU DON'T HAVE PERMISSION TO ACCESS THIS USER'S INFORMATION";
exit;
}
$curriculums = $user->getCurriculums();
$degree = FALSE;
foreach ($curriculums as $c) {
if ($c->getCurriculumType() == Curriculum_model::CURRICULUM_TYPE_DEGREE) {
$degree = TRUE;
}
}
if (!$degree) {
echo "ERROR NO CURRICULUM SET FOR USER";
exit;
}
//Create excel file
$Excel = new PHPExcel();
$Excel->getProperties()->setCreator("CSC 404 - 2015 App")->setLastModifiedBy("CSC 404 - 2015 App")->setTitle($user->getName() . " Checklist")->setSubject("Advising Checklist")->setDescription("Auto Generated Checklist")->setCategory("Advisee checklist file");
//Set global defaults
$Excel->getDefaultStyle()->getFont()->setSize(10)->setName('Arial');
$Excel->removeSheetByIndex(0);
//Generate course sheets
$sheetnumber = 0;
foreach ($curriculums as $c) {
if ($c->getCurriculumType() == Curriculum_model::CURRICULUM_TYPE_DEGREE) {
//Generate Checklist
$checklist = $Excel->createSheet(NULL, $sheetnumber++);
$checklist->setTitle("Checklist");
$this->generatechecklist($checklist, $user, $c, $curriculums);
//Generate Quarter View
$qview = $Excel->createSheet(NULL, $sheetnumber++);
$qview->setTitle("Quarter View");
$this->generate_quarter_view($qview, $user, $c);
break;
}
}
//Generate advisor checklist sheet
$advcheck = $Excel->createSheet(NULL, $sheetnumber++);
$advcheck->setTitle("Advisor Checklist");
$this->generateadvchecklist($advcheck);
//Download file object (PDF or XLS)
$Excel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($Excel, 'Excel5');
header("Content-type: application/vnd.ms-exel");
header("Content-Disposition: attachment; filename=\"" . $user->getName() . " Checklist.xls\"");
$objWriter->save('php://output');
}
/**
* 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);
// Use ParseXL for the hard work.
$this->_ole =& new PHPExcel_Shared_OLERead();
$this->_rowoffset = $this->_coloffset = 0;
$this->_defaultEncoding = 'UTF-8';
//ISO-8859-1
$this->_encoderFunction = function_exists('mb_convert_encoding') ? 'mb_convert_encoding' : 'iconv';
// get excel data
$this->_read($pFilename);
foreach ($this->_boundsheets as $index => $details) {
$sheet =& $excel->createSheet();
$sheet->setTitle((string) $details['name']);
// read all the columns of all the rows !
$numrows = $this->_sheets[$index]['numRows'];
$numcols = $this->_sheets[$index]['numCols'];
for ($row = 0; $row < $numrows; $row++) {
for ($col = 0; $col < $numcols; $col++) {
@($cellcontent = $this->_sheets[$index]['cells'][$row][$col]);
@($cellinfo = $this->_sheets[$index]['cellsInfo'][$row][$col]);
if (is_null($cellcontent)) {
continue;
}
$sheet->setCellValueByColumnAndRow($col, $row + 1, $cellcontent);
}
}
}
return $excel;
}
/**
* Loads PHPExcel from file
*
* @param string $pFilename
* @return \PHPExcel\Spreadsheet
* @throws Exception
*/
public function load($pFilename)
{
// Create new PHPExcel
$objPHPExcel = new PHPExcel();
$objPHPExcel->removeSheetByIndex(0);
// Load into this instance
return $this->loadIntoExisting($pFilename, $objPHPExcel);
}
/**
* @author caochunhui@dachuwang.com
* @description 用数组和地址直接生成excel文件
* 每一个数组占一个sheet
*/
private function _convert_array_to_excel($arr = array(), $sheet_titles = array(), $out_name = '')
{
//下面的代码是抄的。
//set cache
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
//open excel file
$write_objPHPExcel = new PHPExcel();
//下面要循环了
$sheet_cnt = 0;
foreach ($arr as $item) {
//用订单id.csv来命名每一个sheet
$out_sheet = new PHPExcel_Worksheet($write_objPHPExcel, $sheet_titles[$sheet_cnt]);
//$out_sheet->setTitle($item);
//row index start from 1
$row_index = 0;
foreach ($item as $row) {
$row_index++;
//$cellIterator = $row->getCellIterator();
//$cellIterator->setIterateOnlyExistingCells(false);
//column index start from 0
$column_index = -1;
foreach ($row as $cell) {
$column_index++;
//var_dump($cell);
$out_sheet->setCellValueByColumnAndRow($column_index, $row_index, $cell);
}
}
$write_objPHPExcel->addSheet($out_sheet);
$sheet_cnt++;
}
$write_objPHPExcel->removeSheetByIndex(0);
//删除第一个空sheet
//上面要循环了
//上面的代码是抄的
//write excel file
$objWriter = new PHPExcel_Writer_Excel2007($write_objPHPExcel);
$dir_name = dirname($out_name);
if (!is_dir($dir_name)) {
$res = mkdir($dir_name, 0777, TRUE);
}
$objWriter->save($out_name);
}
