/** * Get a worksheet by title (name) * * @param string $title name of the worksheet * * @return \Google\Spreadsheet\Worksheet */ public function getByTitle($title) { foreach ($this->xml->entry as $entry) { if ($entry->title->__toString() == $title) { $worksheet = new Worksheet($entry); $worksheet->setPostUrl($this->getPostUrl()); return $worksheet; } } return null; }
/** * Convert the height of a cell from user's units to pixels. By interpolation * the relationship is: y = 4/3x. If the height hasn't been set by the user we * use the default value. If the row is hidden we use a value of zero. * * @param Worksheet $sheet The sheet * @param integer $row The row index (1-based) * @return integer The width in pixels */ public static function sizeRow($sheet, $row = 1) { // default font of the workbook $font = $sheet->getParent()->getDefaultStyle()->getFont(); $rowDimensions = $sheet->getRowDimensions(); // first find the true row height in pixels (uncollapsed and unhidden) if (isset($rowDimensions[$row]) and $rowDimensions[$row]->getRowHeight() != -1) { // then we have a row dimension $rowDimension = $rowDimensions[$row]; $rowHeight = $rowDimension->getRowHeight(); $pixelRowHeight = (int) ceil(4 * $rowHeight / 3); // here we assume Arial 10 } else { if ($sheet->getDefaultRowDimension()->getRowHeight() != -1) { // then we have a default row dimension with explicit height $defaultRowDimension = $sheet->getDefaultRowDimension(); $rowHeight = $defaultRowDimension->getRowHeight(); $pixelRowHeight = Shared_Drawing::pointsToPixels($rowHeight); } else { // we don't even have any default row dimension. Height depends on default font $pointRowHeight = Shared_Font::getDefaultRowHeightByFont($font); $pixelRowHeight = Shared_Font::fontSizeToPixels($pointRowHeight); } } // now find the effective row height in pixels if (isset($rowDimensions[$row]) and !$rowDimensions[$row]->getVisible()) { $effectivePixelRowHeight = 0; } else { $effectivePixelRowHeight = $pixelRowHeight; } return $effectivePixelRowHeight; }
/** * Set Hyperlink * * @param Cell_Hyperlink $pHyperlink * @throws Exception * @return Cell */ public function setHyperlink(Cell_Hyperlink $pHyperlink = null) { if (!isset($this->_parent)) { throw new Exception('Cannot set hyperlink for cell that is not bound to a worksheet'); } $this->_parent->setHyperlink($this->getCoordinate(), $pHyperlink); $this->notifyCacheController(); return $this; }
public function frameResponse(\FrameResponseObject $frameResponseObject) { $myExtension = \Worksheet::getInstance(); $worksheet = new \Worksheet\Worksheet($this->id); $role = $worksheet->getRole(); if ($role == "build") { header("Location: " . PATH_URL . "worksheet/Build/" . $this->id); } elseif ($role == "view") { header("Location: " . PATH_URL . "worksheet/View/" . $this->id); } elseif ($role == "edit") { header("Location: " . PATH_URL . "worksheet/Edit/" . $this->id); } return $frameResponseObject; }
/** * More Cell instances available? * * @return boolean */ public function valid() { // columnIndexFromString() returns an index based at one, // treat it as a count when comparing it to the base zero // position. $columnCount = Cell::columnIndexFromString($this->_subject->getHighestColumn()); if ($this->_onlyExistingCells) { // If we aren't looking at an existing cell, either // because the first column doesn't exist or next() has // been called onto a nonexistent cell, then loop until we // find one, or pass the last column. while ($this->_position < $columnCount && !$this->_subject->cellExistsByColumnAndRow($this->_position, $this->_rowIndex)) { ++$this->_position; } } return $this->_position < $columnCount; }
public function account($id) { @$this->loadModel("Itdepartment"); $datan = ""; $this->view->mee = $this->model->getEmployee($id); $ptasks = Worksheet::find_by_sql("SELECT * FROM work_sheet_form WHERE cse_emp_id =" . (int) trim($id) . ""); if ($ptasks) { $datan .= "<table width='100%'>\n <thead><tr>\n \t<th>S/N</th><th>Prod ID</th><th>Status</th><th>Emp ID</th><th>Issue</th><th>Date Generated </th><th></th><th></th><th></th>\n </tr>\n </thead>\n <tbody>"; foreach ($ptasks as $task) { $datan .= "<td>{$x}</td><td>{$worksheet->prod_id}</td><td>{$worksheet->status} </td><td>{$worksheet->cse_emp_id}</td><td>{$worksheet->problem}</td><td>{$worksheet->sheet_date}</td><td><a href='" . $uri->link("itdepartment/worksheetdetail/" . $worksheet->id . "") . "'>View Detail</a></td><td></td><td></td>"; } } else { } $datan .= "</tbody></table>"; $this->view->oldtask = $datan; $this->view->render("itdepartment/staffaccount"); }
/** * Set name * * @param string $value * @return NamedRange */ public function setName($value = null) { if (!is_null($value)) { // Old title $oldTitle = $this->_name; // Re-attach if (!is_null($this->_worksheet)) { $this->_worksheet->getParent()->removeNamedRange($this->_name, $this->_worksheet); } $this->_name = $value; if (!is_null($this->_worksheet)) { $this->_worksheet->getParent()->addNamedRange($this); } // New title $newTitle = $this->_name; ReferenceHelper::getInstance()->updateNamedFormulas($this->_worksheet->getParent(), $oldTitle, $newTitle); } return $this; }
/** * */ public function account($id = "") { @$this->loadModel("Itdepartment"); global $session; $datan = ""; $uri = new Url(""); //$empworkdata = $this->model->getWorkSheetEmployee($id,""); $ptasks = Worksheet::find_by_sql("SELECT * FROM work_sheet_form WHERE cse_emp_id =" . $_SESSION['emp_ident']); // print_r($ptasks); //$empworkdata['worksheet']; $x = 1; $datan .= "<table width='100%'>\n <thead><tr>\n \t<th>S/N</th><th>Prod ID</th><th>Status</th><th>Emp ID</th><th>Issue</th><th>Date Generated </th><th></th><th></th>\n </tr>\n </thead>\n <tbody>"; if ($ptasks) { foreach ($ptasks as $task) { $datan .= "<tr><td>{$x}</td><td>{$task->prod_id}</td><td>{$task->status} </td><td>{$task->cse_emp_id}</td><td>{$task->problem}</td><td>{$task->sheet_date}</td><td><a href='" . $uri->link("itdepartment/worksheetdetail/" . $task->id . "") . "'>View Detail</a></td><td></td></tr>"; $x++; } } else { $datan .= "<tr><td colspan='8'></td></tr>"; } $datan .= "</tbody></table>"; $mysched = ""; $psched = Schedule::find_by_sql("SELECT * FROM schedule WHERE status='Open' AND emp_id =" . $_SESSION['emp_ident'] . " ORDER BY id DESC"); //print_r($psched); //$empworkdata['worksheet']; $x = 1; $mysched .= "<table width='100%'>\n <thead><tr>\n \t<th>S/N</th><th>Machine</th><th>Issue</th><th>Location</th><th>Task Type</th><th>Task Date </th><th></th><th></th><th></th>\n </tr>\n </thead>\n <tbody>"; if ($psched) { foreach ($psched as $task) { $mysched .= "<tr><td>{$x}</td><td>{$task->prod_name}</td><td>{$task->issue} </td>"; $machine = Cproduct::find_by_id($task->prod_id); $mysched .= "<td>{$machine->install_address} {$machine->install_city}</td><td>{$task->maint_type}</td><td>{$task->s_date}</td><td><a href='" . $uri->link("itdepartment/addworksheet/" . $task->id . "") . "'>Get Work Sheet</a></td><td></td><td></td></tr>"; $x++; } } else { $mysched .= "<tr><td colspan='8'>There is no task currently</td></tr>"; } $mysched .= "</tbody></table>"; $this->view->oldtask = $datan; $this->view->schedule = $mysched; $this->view->mee = $this->model->getEmployee($id); $this->view->render("itdepartment/staffaccount"); }
/** * Store the DATAVALIDATIONS and DATAVALIDATION records. */ private function _writeDataValidity() { // Datavalidation collection $dataValidationCollection = $this->_phpSheet->getDataValidationCollection(); // Write data validations? if (count($dataValidationCollection) > 0) { // DATAVALIDATIONS record $record = 0x1b2; // Record identifier $length = 0x12; // Bytes to follow $grbit = 0x0; // Prompt box at cell, no cached validity data at DV records $horPos = 0x0; // Horizontal position of prompt box, if fixed position $verPos = 0x0; // Vertical position of prompt box, if fixed position $objId = 0xffffffff; // Object identifier of drop down arrow object, or -1 if not visible $header = pack('vv', $record, $length); $data = pack('vVVVV', $grbit, $horPos, $verPos, $objId, count($dataValidationCollection)); $this->_append($header . $data); // DATAVALIDATION records $record = 0x1be; // Record identifier foreach ($dataValidationCollection as $cellCoordinate => $dataValidation) { // initialize record data $data = ''; // options $options = 0x0; // data type $type = $dataValidation->getType(); switch ($type) { case Cell_DataValidation::TYPE_NONE: $type = 0x0; break; case Cell_DataValidation::TYPE_WHOLE: $type = 0x1; break; case Cell_DataValidation::TYPE_DECIMAL: $type = 0x2; break; case Cell_DataValidation::TYPE_LIST: $type = 0x3; break; case Cell_DataValidation::TYPE_DATE: $type = 0x4; break; case Cell_DataValidation::TYPE_TIME: $type = 0x5; break; case Cell_DataValidation::TYPE_TEXTLENGTH: $type = 0x6; break; case Cell_DataValidation::TYPE_CUSTOM: $type = 0x7; break; } $options |= $type << 0; // error style $errorStyle = $dataValidation->getType(); switch ($errorStyle) { case Cell_DataValidation::STYLE_STOP: $errorStyle = 0x0; break; case Cell_DataValidation::STYLE_WARNING: $errorStyle = 0x1; break; case Cell_DataValidation::STYLE_INFORMATION: $errorStyle = 0x2; break; } $options |= $errorStyle << 4; // explicit formula? if ($type == 0x3 && preg_match('/^\\".*\\"$/', $dataValidation->getFormula1())) { $options |= 0x1 << 7; } // empty cells allowed $options |= $dataValidation->getAllowBlank() << 8; // show drop down $options |= !$dataValidation->getShowDropDown() << 9; // show input message $options |= $dataValidation->getShowInputMessage() << 18; // show error message $options |= $dataValidation->getShowErrorMessage() << 19; // condition operator $operator = $dataValidation->getOperator(); switch ($operator) { case Cell_DataValidation::OPERATOR_BETWEEN: $operator = 0x0; break; case Cell_DataValidation::OPERATOR_NOTBETWEEN: $operator = 0x1; break; case Cell_DataValidation::OPERATOR_EQUAL: $operator = 0x2; break; case Cell_DataValidation::OPERATOR_NOTEQUAL: $operator = 0x3; break; case Cell_DataValidation::OPERATOR_GREATERTHAN: $operator = 0x4; break; case Cell_DataValidation::OPERATOR_LESSTHAN: $operator = 0x5; break; case Cell_DataValidation::OPERATOR_GREATERTHANOREQUAL: $operator = 0x6; break; case Cell_DataValidation::OPERATOR_LESSTHANOREQUAL: $operator = 0x7; break; } $options |= $operator << 20; $data = pack('V', $options); // prompt title $promptTitle = $dataValidation->getPromptTitle() !== '' ? $dataValidation->getPromptTitle() : chr(0); $data .= Shared_String::UTF8toBIFF8UnicodeLong($promptTitle); // error title $errorTitle = $dataValidation->getErrorTitle() !== '' ? $dataValidation->getErrorTitle() : chr(0); $data .= Shared_String::UTF8toBIFF8UnicodeLong($errorTitle); // prompt text $prompt = $dataValidation->getPrompt() !== '' ? $dataValidation->getPrompt() : chr(0); $data .= Shared_String::UTF8toBIFF8UnicodeLong($prompt); // error text $error = $dataValidation->getError() !== '' ? $dataValidation->getError() : chr(0); $data .= Shared_String::UTF8toBIFF8UnicodeLong($error); // formula 1 try { $formula1 = $dataValidation->getFormula1(); if ($type == 0x3) { // list type $formula1 = str_replace(',', chr(0), $formula1); } $this->_parser->parse($formula1); $formula1 = $this->_parser->toReversePolish(); $sz1 = strlen($formula1); } catch (Exception $e) { $sz1 = 0; $formula1 = ''; } $data .= pack('vv', $sz1, 0x0); $data .= $formula1; // formula 2 try { $formula2 = $dataValidation->getFormula2(); if ($formula2 === '') { throw new Exception('No formula2'); } $this->_parser->parse($formula2); $formula2 = $this->_parser->toReversePolish(); $sz2 = strlen($formula2); } catch (Exception $e) { $sz2 = 0; $formula2 = ''; } $data .= pack('vv', $sz2, 0x0); $data .= $formula2; // cell range address list $data .= pack('v', 0x1); $data .= $this->_writeBIFF8CellRangeAddressFixed($cellCoordinate); $length = strlen($data); $header = pack("vv", $record, $length); $this->_append($header . $data); } } }
/** * Add external sheet * * @param Worksheet $pSheet External sheet to add * @param int|null $iSheetIndex Index where sheet should go (0,1,..., or null for last) * @throws Exception * @return Worksheet */ public function addExternalSheet(Worksheet $pSheet, $iSheetIndex = null) { if (!is_null($this->getSheetByName($pSheet->getTitle()))) { throw new Exception("Workbook already contains a worksheet named '{$pSheet->getTitle()}'. Rename the external sheet first."); } // count how many cellXfs there are in this workbook currently, we will need this below $countCellXfs = count($this->_cellXfCollection); // copy all the shared cellXfs from the external workbook and append them to the current foreach ($pSheet->getParent()->getCellXfCollection() as $cellXf) { $this->addCellXf(clone $cellXf); } // move sheet to this workbook $pSheet->rebindParent($this); // update the cellXfs foreach ($pSheet->getCellCollection(false) as $cellID) { $cell = $sheet->getCell($cellID); $cell->setXfIndex($cell->getXfIndex() + $countCellXfs); } return $this->addSheet($pSheet, $iSheetIndex); }
public function createAddPart() { if (isset($_REQUEST['itemid']) && !empty($_REQUEST['wid'])) { $worksheet = Worksheet::find_by_id($_REQUEST['wid']); $myItem = Items::find_by_id($_REQUEST['itemid']); $propart = new Prodpart(); $propart->part_name = $myItem->item_name; $propart->qty = $_REQUEST['qty']; $propart->works_id = $_REQUEST['wid']; $propart->prod_id = $worksheet->prod_id; $propart->item_id = $_REQUEST['itemid']; $propart->datecreated = date("Y:m:d H:i:s"); $propart->des = ""; $propart->unit_cost = $_REQUEST['price']; $propart->total_cost = (int) $_REQUEST['price'] * (int) $_REQUEST['qty']; $Tlog = new Transaction(); $Tlog->com_id = $myItem->item_id; $Tlog->trans_type = "WORKSHEET PART SUPPLY"; $Tlog->trans_description = "allocation of resources to worksheet"; $Tlog->datecreated = date("Y-m-d H:i:s"); $Tlog->user_id = $_SESSION['emp_ident']; if ($propart->create()) { $Tlog->create(); return $propart; } else { return false; } } }
/** * Get hash code * * @return string Hash code */ public function getHashCode() { return md5($this->_name . $this->_description . $this->_worksheet->getHashCode() . $this->_coordinates . $this->_offsetX . $this->_offsetY . $this->_width . $this->_height . $this->_rotation . $this->_shadow->getHashCode() . __CLASS__); }
/** * More Worksheet_Row instances available? * * @return boolean */ public function valid() { return $this->_position <= $this->_subject->getHighestRow(); }
/** * Write VML header/footer images to XML format * * @param Worksheet $pWorksheet * @return string XML Output * @throws Exception */ public function writeVMLHeaderFooterImages(Worksheet $pWorksheet = null) { // Create XML writer $objWriter = null; if ($this->getParentWriter()->getUseDiskCaching()) { $objWriter = new Shared_XMLWriter(Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory()); } else { $objWriter = new Shared_XMLWriter(Shared_XMLWriter::STORAGE_MEMORY); } // XML header $objWriter->startDocument('1.0', 'UTF-8', 'yes'); // Header/footer images $images = $pWorksheet->getHeaderFooter()->getImages(); // xml $objWriter->startElement('xml'); $objWriter->writeAttribute('xmlns:v', 'urn:schemas-microsoft-com:vml'); $objWriter->writeAttribute('xmlns:o', 'urn:schemas-microsoft-com:office:office'); $objWriter->writeAttribute('xmlns:x', 'urn:schemas-microsoft-com:office:excel'); // o:shapelayout $objWriter->startElement('o:shapelayout'); $objWriter->writeAttribute('v:ext', 'edit'); // o:idmap $objWriter->startElement('o:idmap'); $objWriter->writeAttribute('v:ext', 'edit'); $objWriter->writeAttribute('data', '1'); $objWriter->endElement(); $objWriter->endElement(); // v:shapetype $objWriter->startElement('v:shapetype'); $objWriter->writeAttribute('id', '_x0000_t75'); $objWriter->writeAttribute('coordsize', '21600,21600'); $objWriter->writeAttribute('o:spt', '75'); $objWriter->writeAttribute('o:preferrelative', 't'); $objWriter->writeAttribute('path', 'm@4@5l@4@11@9@11@9@5xe'); $objWriter->writeAttribute('filled', 'f'); $objWriter->writeAttribute('stroked', 'f'); // v:stroke $objWriter->startElement('v:stroke'); $objWriter->writeAttribute('joinstyle', 'miter'); $objWriter->endElement(); // v:formulas $objWriter->startElement('v:formulas'); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'if lineDrawn pixelLineWidth 0'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'sum @0 1 0'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'sum 0 0 @1'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'prod @2 1 2'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'prod @3 21600 pixelWidth'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'prod @3 21600 pixelHeight'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'sum @0 0 1'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'prod @6 1 2'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'prod @7 21600 pixelWidth'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'sum @8 21600 0'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'prod @7 21600 pixelHeight'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'sum @10 21600 0'); $objWriter->endElement(); $objWriter->endElement(); // v:path $objWriter->startElement('v:path'); $objWriter->writeAttribute('o:extrusionok', 'f'); $objWriter->writeAttribute('gradientshapeok', 't'); $objWriter->writeAttribute('o:connecttype', 'rect'); $objWriter->endElement(); // o:lock $objWriter->startElement('o:lock'); $objWriter->writeAttribute('v:ext', 'edit'); $objWriter->writeAttribute('aspectratio', 't'); $objWriter->endElement(); $objWriter->endElement(); // Loop through images foreach ($images as $key => $value) { $this->_writeVMLHeaderFooterImage($objWriter, $key, $value); } $objWriter->endElement(); // Return return $objWriter->getData(); }
/** * Remove named range * * @param string $namedRange * @param Worksheet|null $pSheet Scope: use null for global scope. * @return PHPExcel */ public function removeNamedRange($namedRange, Worksheet $pSheet = null) { if ($pSheet === null) { if (isset($this->namedRanges[$namedRange])) { unset($this->namedRanges[$namedRange]); } } else { if (isset($this->namedRanges[$pSheet->getTitle() . '!' . $namedRange])) { unset($this->namedRanges[$pSheet->getTitle() . '!' . $namedRange]); } } return $this; }
public function createAddPart() { if (isset($_POST['itemid']) && !empty($_POST['wid'])) { $worksheet = Worksheet::find_by_id($_POST['wid']); $myItem = Items::find_by_id($_POST['itemid']); $propart = new Prodpart(); $propart->part_name = $myItem->item_name; $propart->qty = $_POST['qty']; $propart->works_id = $_POST['wid']; $propart->prod_id = $worksheet->prod_id; $propart->item_id = $_POST['itemid']; $propart->date_changed = date("Y:m:d H:i:s"); $propart->desc = ""; $propart->unit_cost = $_POST['price']; $propart->total_cost = (int) $_POST['price'] * (int) $_POST['qty']; if ($propart->create()) { return $propart; } else { return false; } } }
/** * Loads PHPExcel from file * * @param string $pFilename * @throws Exception */ public function load($pFilename) { // Check if file exists if (!file_exists($pFilename)) { throw new Exception("Could not open " . $pFilename . " for reading! File does not exist."); } // Initialisations $excel = new PHPExcel(); $excel->removeSheetByIndex(0); if (!$this->_readDataOnly) { $excel->removeCellStyleXfByIndex(0); // remove the default style $excel->removeCellXfByIndex(0); // remove the default style } $zip = new ZipArchive(); $zip->open($pFilename); $rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); foreach ($rels->Relationship as $rel) { switch ($rel["Type"]) { case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties": $xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); if ($xmlCore) { $xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/"); $xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/"); $xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties"); $docProps = $excel->getProperties(); $docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator"))); $docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy"))); $docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created")))); //! respect xsi:type $docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified")))); //! respect xsi:type $docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title"))); $docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description"))); $docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject"))); $docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords"))); $docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category"))); } break; case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument": $dir = dirname($rel["Target"]); $relsWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/_rels/" . basename($rel["Target"]) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); $relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships"); $sharedStrings = array(); $xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']")); $xmlStrings = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/{$xpath['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main"); if (isset($xmlStrings) && isset($xmlStrings->si)) { foreach ($xmlStrings->si as $val) { if (isset($val->t)) { $sharedStrings[] = Shared_String::ControlCharacterOOXML2PHP((string) $val->t); } elseif (isset($val->r)) { $sharedStrings[] = $this->_parseRichText($val); } } } $worksheets = array(); foreach ($relsWorkbook->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") { $worksheets[(string) $ele["Id"]] = $ele["Target"]; } } $styles = array(); $cellStyles = array(); $xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']")); $xmlStyles = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/{$xpath['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main"); $numFmts = null; if ($xmlStyles && $xmlStyles->numFmts[0]) { $numFmts = $xmlStyles->numFmts[0]; } if (isset($numFmts) && !is_null($numFmts)) { $numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main"); } if (!$this->_readDataOnly && $xmlStyles) { foreach ($xmlStyles->cellXfs->xf as $xf) { $numFmt = Style_NumberFormat::FORMAT_GENERAL; if ($xf["numFmtId"]) { if (isset($numFmts)) { $tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]")); if (isset($tmpNumFmt["formatCode"])) { $numFmt = (string) $tmpNumFmt["formatCode"]; } } if ((int) $xf["numFmtId"] < 164) { $numFmt = Style_NumberFormat::builtInFormatCode((int) $xf["numFmtId"]); } } //$numFmt = str_replace('mm', 'i', $numFmt); //$numFmt = str_replace('h', 'H', $numFmt); $style = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection); $styles[] = $style; // add style to cellXf collection $objStyle = new Style(); $this->_readStyle($objStyle, $style); $excel->addCellXf($objStyle); } foreach ($xmlStyles->cellStyleXfs->xf as $xf) { $numFmt = Style_NumberFormat::FORMAT_GENERAL; if ($numFmts && $xf["numFmtId"]) { $tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]")); if (isset($tmpNumFmt["formatCode"])) { $numFmt = (string) $tmpNumFmt["formatCode"]; } else { if ((int) $xf["numFmtId"] < 165) { $numFmt = Style_NumberFormat::builtInFormatCode((int) $xf["numFmtId"]); } } } $cellStyle = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection); $cellStyles[] = $cellStyle; // add style to cellStyleXf collection $objStyle = new Style(); $this->_readStyle($objStyle, $cellStyle); $excel->addCellStyleXf($objStyle); } } $dxfs = array(); if (!$this->_readDataOnly && $xmlStyles) { if ($xmlStyles->dxfs) { foreach ($xmlStyles->dxfs->dxf as $dxf) { $style = new Style(); $this->_readStyle($style, $dxf); $dxfs[] = $style; } } if ($xmlStyles->cellStyles) { foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) { if (intval($cellStyle['builtinId']) == 0) { if (isset($cellStyles[intval($cellStyle['xfId'])])) { // Set default style $style = new Style(); $this->_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]); // normal style, currently not using it for anything } } } } } $xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main"); // Set base date if ($xmlWorkbook->workbookPr) { Shared_Date::setExcelCalendar(Shared_Date::CALENDAR_WINDOWS_1900); if (isset($xmlWorkbook->workbookPr['date1904'])) { $date1904 = (string) $xmlWorkbook->workbookPr['date1904']; if ($date1904 == "true" || $date1904 == "1") { Shared_Date::setExcelCalendar(Shared_Date::CALENDAR_MAC_1904); } } } $sheetId = 0; // keep track of new sheet id in final workbook $oldSheetId = -1; // keep track of old sheet id in final workbook $countSkippedSheets = 0; // keep track of number of skipped sheets $mapSheetId = array(); // mapping of sheet ids from old to new if ($xmlWorkbook->sheets) { foreach ($xmlWorkbook->sheets->sheet as $eleSheet) { ++$oldSheetId; // Check if sheet should be skipped if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) { ++$countSkippedSheets; $mapSheetId[$oldSheetId] = null; continue; } // Map old sheet id in original workbook to new sheet id. // They will differ if loadSheetsOnly() is being used $mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets; // Load sheet $docSheet = $excel->createSheet(); $docSheet->setTitle((string) $eleSheet["name"]); $fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")]; $xmlSheet = simplexml_load_string($this->_getFromZipArchive($zip, "{$dir}/{$fileWorksheet}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main"); $sharedFormulas = array(); if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') { $docSheet->setSheetState((string) $eleSheet["state"]); } if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) { if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) { $docSheet->getSheetView()->setZoomScale(intval($xmlSheet->sheetViews->sheetView['zoomScale'])); } if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) { $docSheet->getSheetView()->setZoomScaleNormal(intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])); } if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) { $docSheet->setShowGridLines((string) $xmlSheet->sheetViews->sheetView['showGridLines'] ? true : false); } if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) { $docSheet->setShowRowColHeaders((string) $xmlSheet->sheetViews->sheetView['showRowColHeaders'] ? true : false); } if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) { $docSheet->setRightToLeft((string) $xmlSheet->sheetViews->sheetView['rightToLeft'] ? true : false); } if (isset($xmlSheet->sheetViews->sheetView->pane)) { if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) { $docSheet->freezePane((string) $xmlSheet->sheetViews->sheetView->pane['topLeftCell']); } else { $xSplit = 0; $ySplit = 0; if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) { $xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']); } if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) { $ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']); } $docSheet->freezePaneByColumnAndRow($xSplit, $ySplit); } } if (isset($xmlSheet->sheetViews->sheetView->selection)) { if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) { $sqref = (string) $xmlSheet->sheetViews->sheetView->selection['sqref']; $sqref = explode(' ', $sqref); $sqref = $sqref[0]; $docSheet->setSelectedCells($sqref); } } } if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) { if (isset($xmlSheet->sheetPr->tabColor['rgb'])) { $docSheet->getTabColor()->setARGB((string) $xmlSheet->sheetPr->tabColor['rgb']); } } if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) { if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && $xmlSheet->sheetPr->outlinePr['summaryRight'] == false) { $docSheet->setShowSummaryRight(false); } else { $docSheet->setShowSummaryRight(true); } if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && $xmlSheet->sheetPr->outlinePr['summaryBelow'] == false) { $docSheet->setShowSummaryBelow(false); } else { $docSheet->setShowSummaryBelow(true); } } if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) { if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && $xmlSheet->sheetPr->pageSetUpPr['fitToPage'] == false) { $docSheet->getPageSetup()->setFitToPage(false); } else { $docSheet->getPageSetup()->setFitToPage(true); } } if (isset($xmlSheet->sheetFormatPr)) { if (isset($xmlSheet->sheetFormatPr['customHeight']) && ((string) $xmlSheet->sheetFormatPr['customHeight'] == '1' || strtolower((string) $xmlSheet->sheetFormatPr['customHeight']) == 'true') && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) { $docSheet->getDefaultRowDimension()->setRowHeight((double) $xmlSheet->sheetFormatPr['defaultRowHeight']); } if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) { $docSheet->getDefaultColumnDimension()->setWidth((double) $xmlSheet->sheetFormatPr['defaultColWidth']); } } if (isset($xmlSheet->cols) && !$this->_readDataOnly) { foreach ($xmlSheet->cols->col as $col) { for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) { if ($col["style"] && !$this->_readDataOnly) { $docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"])); } if ($col["bestFit"]) { //$docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setAutoSize(true); } if ($col["hidden"]) { $docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setVisible(false); } if ($col["collapsed"]) { $docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setCollapsed(true); } if ($col["outlineLevel"] > 0) { $docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"])); } $docSheet->getColumnDimension(Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"])); if (intval($col["max"]) == 16384) { break; } } } } if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) { if ($xmlSheet->printOptions['gridLinesSet'] == 'true' && $xmlSheet->printOptions['gridLinesSet'] == '1') { $docSheet->setShowGridlines(true); } if ($xmlSheet->printOptions['gridLines'] == 'true' || $xmlSheet->printOptions['gridLines'] == '1') { $docSheet->setPrintGridlines(true); } if ($xmlSheet->printOptions['horizontalCentered']) { $docSheet->getPageSetup()->setHorizontalCentered(true); } if ($xmlSheet->printOptions['verticalCentered']) { $docSheet->getPageSetup()->setVerticalCentered(true); } } if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) { foreach ($xmlSheet->sheetData->row as $row) { if ($row["ht"] && !$this->_readDataOnly) { $docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"])); } if ($row["hidden"] && !$this->_readDataOnly) { $docSheet->getRowDimension(intval($row["r"]))->setVisible(false); } if ($row["collapsed"]) { $docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true); } if ($row["outlineLevel"] > 0) { $docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"])); } if ($row["s"] && !$this->_readDataOnly) { $docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"])); } foreach ($row->c as $c) { $r = (string) $c["r"]; $cellDataType = (string) $c["t"]; $value = null; $calculatedValue = null; // Read cell? if (!is_null($this->getReadFilter())) { $coordinates = Cell::coordinateFromString($r); if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) { continue; } } // echo '<b>Reading cell '.$coordinates[0].$coordinates[1].'</b><br />'; // print_r($c); // echo '<br />'; // echo 'Cell Data Type is '.$cellDataType.': '; // // Read cell! switch ($cellDataType) { case "s": // echo 'String<br />'; if ((string) $c->v != '') { $value = $sharedStrings[intval($c->v)]; if ($value instanceof RichText) { $value = clone $value; } } else { $value = ''; } break; case "b": // echo 'Boolean<br />'; if (!isset($c->f)) { $value = $this->_castToBool($c); } else { // Formula $this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToBool'); // echo '$calculatedValue = '.$calculatedValue.'<br />'; } break; case "inlineStr": // echo 'Inline String<br />'; $value = $this->_parseRichText($c->is); break; case "e": // echo 'Error<br />'; if (!isset($c->f)) { $value = $this->_castToError($c); } else { // Formula $this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToError'); // echo '$calculatedValue = '.$calculatedValue.'<br />'; } break; default: // echo 'Default<br />'; if (!isset($c->f)) { // echo 'Not a Formula<br />'; $value = $this->_castToString($c); } else { // echo 'Treat as Formula<br />'; // Formula $this->_castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, '_castToString'); // echo '$calculatedValue = '.$calculatedValue.'<br />'; } break; } // echo 'Value is '.$value.'<br />'; // Check for numeric values if (is_numeric($value) && $cellDataType != 's') { if ($value == (int) $value) { $value = (int) $value; } elseif ($value == (double) $value) { $value = (double) $value; } elseif ($value == (double) $value) { $value = (double) $value; } } // Rich text? if ($value instanceof RichText && $this->_readDataOnly) { $value = $value->getPlainText(); } $cell = $docSheet->getCell($r); // Assign value if ($cellDataType != '') { $cell->setValueExplicit($value, $cellDataType); } else { $cell->setValue($value); } if (!is_null($calculatedValue)) { $cell->setCalculatedValue($calculatedValue); } // Style information? if ($c["s"] && !$this->_readDataOnly) { // no style index means 0, it seems $cell->setXfIndex(isset($styles[intval($c["s"])]) ? intval($c["s"]) : 0); } } } } $conditionals = array(); if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) { foreach ($xmlSheet->conditionalFormatting as $conditional) { foreach ($conditional->cfRule as $cfRule) { if (((string) $cfRule["type"] == Style_Conditional::CONDITION_NONE || (string) $cfRule["type"] == Style_Conditional::CONDITION_CELLIS || (string) $cfRule["type"] == Style_Conditional::CONDITION_CONTAINSTEXT || (string) $cfRule["type"] == Style_Conditional::CONDITION_EXPRESSION) && isset($dxfs[intval($cfRule["dxfId"])])) { $conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule; } } } foreach ($conditionals as $ref => $cfRules) { ksort($cfRules); $conditionalStyles = array(); foreach ($cfRules as $cfRule) { $objConditional = new Style_Conditional(); $objConditional->setConditionType((string) $cfRule["type"]); $objConditional->setOperatorType((string) $cfRule["operator"]); if ((string) $cfRule["text"] != '') { $objConditional->setText((string) $cfRule["text"]); } if (count($cfRule->formula) > 1) { foreach ($cfRule->formula as $formula) { $objConditional->addCondition((string) $formula); } } else { $objConditional->addCondition((string) $cfRule->formula); } $objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]); $conditionalStyles[] = $objConditional; } // Extract all cell references in $ref $aReferences = Cell::extractAllCellReferencesInRange($ref); foreach ($aReferences as $reference) { $docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles); } } } $aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells"); if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) { foreach ($aKeys as $key) { $method = "set" . ucfirst($key); $docSheet->getProtection()->{$method}($xmlSheet->sheetProtection[$key] == "true"); } } if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) { $docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], true); if ($xmlSheet->protectedRanges->protectedRange) { foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) { $docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true); } } } if ($xmlSheet && $xmlSheet->autoFilter && !$this->_readDataOnly) { $docSheet->setAutoFilter((string) $xmlSheet->autoFilter["ref"]); } if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) { foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) { $docSheet->mergeCells((string) $mergeCell["ref"]); } } if ($xmlSheet && $xmlSheet->pageMargins && !$this->_readDataOnly) { $docPageMargins = $docSheet->getPageMargins(); $docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"])); $docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"])); $docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"])); $docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"])); $docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"])); $docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"])); } if ($xmlSheet && $xmlSheet->pageSetup && !$this->_readDataOnly) { $docPageSetup = $docSheet->getPageSetup(); if (isset($xmlSheet->pageSetup["orientation"])) { $docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]); } if (isset($xmlSheet->pageSetup["paperSize"])) { $docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"])); } if (isset($xmlSheet->pageSetup["scale"])) { $docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), false); } if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) { $docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), false); } if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) { $docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), false); } if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) && ((string) $xmlSheet->pageSetup["useFirstPageNumber"] == 'true' || (string) $xmlSheet->pageSetup["useFirstPageNumber"] == '1')) { $docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"])); } } if ($xmlSheet && $xmlSheet->headerFooter && !$this->_readDataOnly) { $docHeaderFooter = $docSheet->getHeaderFooter(); if (isset($xmlSheet->headerFooter["differentOddEven"]) && ((string) $xmlSheet->headerFooter["differentOddEven"] == 'true' || (string) $xmlSheet->headerFooter["differentOddEven"] == '1')) { $docHeaderFooter->setDifferentOddEven(true); } else { $docHeaderFooter->setDifferentOddEven(false); } if (isset($xmlSheet->headerFooter["differentFirst"]) && ((string) $xmlSheet->headerFooter["differentFirst"] == 'true' || (string) $xmlSheet->headerFooter["differentFirst"] == '1')) { $docHeaderFooter->setDifferentFirst(true); } else { $docHeaderFooter->setDifferentFirst(false); } if (isset($xmlSheet->headerFooter["scaleWithDoc"]) && ((string) $xmlSheet->headerFooter["scaleWithDoc"] == 'false' || (string) $xmlSheet->headerFooter["scaleWithDoc"] == '0')) { $docHeaderFooter->setScaleWithDocument(false); } else { $docHeaderFooter->setScaleWithDocument(true); } if (isset($xmlSheet->headerFooter["alignWithMargins"]) && ((string) $xmlSheet->headerFooter["alignWithMargins"] == 'false' || (string) $xmlSheet->headerFooter["alignWithMargins"] == '0')) { $docHeaderFooter->setAlignWithMargins(false); } else { $docHeaderFooter->setAlignWithMargins(true); } $docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader); $docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter); $docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader); $docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter); $docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader); $docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter); } if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->_readDataOnly) { foreach ($xmlSheet->rowBreaks->brk as $brk) { if ($brk["man"]) { $docSheet->setBreak("A{$brk['id']}", Worksheet::BREAK_ROW); } } } if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) { foreach ($xmlSheet->colBreaks->brk as $brk) { if ($brk["man"]) { $docSheet->setBreak(Cell::stringFromColumnIndex($brk["id"]) . "1", Worksheet::BREAK_COLUMN); } } } if ($xmlSheet && $xmlSheet->dataValidations && !$this->_readDataOnly) { foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) { // Uppercase coordinate $range = strtoupper($dataValidation["sqref"]); $rangeSet = explode(' ', $range); foreach ($rangeSet as $range) { $stRange = $docSheet->shrinkRangeToFit($range); // Extract all cell references in $range $aReferences = Cell::extractAllCellReferencesInRange($stRange); foreach ($aReferences as $reference) { // Create validation $docValidation = $docSheet->getCell($reference)->getDataValidation(); $docValidation->setType((string) $dataValidation["type"]); $docValidation->setErrorStyle((string) $dataValidation["errorStyle"]); $docValidation->setOperator((string) $dataValidation["operator"]); $docValidation->setAllowBlank($dataValidation["allowBlank"] != 0); $docValidation->setShowDropDown($dataValidation["showDropDown"] == 0); $docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0); $docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0); $docValidation->setErrorTitle((string) $dataValidation["errorTitle"]); $docValidation->setError((string) $dataValidation["error"]); $docValidation->setPromptTitle((string) $dataValidation["promptTitle"]); $docValidation->setPrompt((string) $dataValidation["prompt"]); $docValidation->setFormula1((string) $dataValidation->formula1); $docValidation->setFormula2((string) $dataValidation->formula2); } } } } // Add hyperlinks $hyperlinks = array(); if (!$this->_readDataOnly) { // Locate hyperlink relations if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") { $hyperlinks[(string) $ele["Id"]] = (string) $ele["Target"]; } } } // Loop through hyperlinks if ($xmlSheet && $xmlSheet->hyperlinks) { foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) { // Link url $linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships'); foreach (Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) { $cell = $docSheet->getCell($cellReference); if (isset($linkRel['id'])) { $cell->getHyperlink()->setUrl($hyperlinks[(string) $linkRel['id']]); } if (isset($hyperlink['location'])) { $cell->getHyperlink()->setUrl('sheet://' . (string) $hyperlink['location']); } // Tooltip if (isset($hyperlink['tooltip'])) { $cell->getHyperlink()->setTooltip((string) $hyperlink['tooltip']); } } } } } // Add comments $comments = array(); $vmlComments = array(); if (!$this->_readDataOnly) { // Locate comment relations if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") { $comments[(string) $ele["Id"]] = (string) $ele["Target"]; } if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") { $vmlComments[(string) $ele["Id"]] = (string) $ele["Target"]; } } } // Loop through comments foreach ($comments as $relName => $relPath) { // Load comments file $relPath = Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath); $commentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath)); // Utility variables $authors = array(); // Loop through authors foreach ($commentsFile->authors->author as $author) { $authors[] = (string) $author; } // Loop through contents foreach ($commentsFile->commentList->comment as $comment) { $docSheet->getComment((string) $comment['ref'])->setAuthor($authors[(string) $comment['authorId']]); $docSheet->getComment((string) $comment['ref'])->setText($this->_parseRichText($comment->text)); } } // Loop through VML comments foreach ($vmlComments as $relName => $relPath) { // Load VML comments file $relPath = Shared_File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath); $vmlCommentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath)); $vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); $shapes = $vmlCommentsFile->xpath('//v:shape'); foreach ($shapes as $shape) { $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); if (isset($shape['style'])) { $style = (string) $shape['style']; $fillColor = strtoupper(substr((string) $shape['fillcolor'], 1)); $column = null; $row = null; $clientData = $shape->xpath('.//x:ClientData'); if (is_array($clientData) && count($clientData) > 0) { $clientData = $clientData[0]; if (isset($clientData['ObjectType']) && (string) $clientData['ObjectType'] == 'Note') { $temp = $clientData->xpath('.//x:Row'); if (is_array($temp)) { $row = $temp[0]; } $temp = $clientData->xpath('.//x:Column'); if (is_array($temp)) { $column = $temp[0]; } } } if (!is_null($column) && !is_null($row)) { // Set comment properties $comment = $docSheet->getCommentByColumnAndRow($column, $row + 1); $comment->getFillColor()->setRGB($fillColor); // Parse style $styleArray = explode(';', str_replace(' ', '', $style)); foreach ($styleArray as $stylePair) { $stylePair = explode(':', $stylePair); if ($stylePair[0] == 'margin-left') { $comment->setMarginLeft($stylePair[1]); } if ($stylePair[0] == 'margin-top') { $comment->setMarginTop($stylePair[1]); } if ($stylePair[0] == 'width') { $comment->setWidth($stylePair[1]); } if ($stylePair[0] == 'height') { $comment->setHeight($stylePair[1]); } if ($stylePair[0] == 'visibility') { $comment->setVisible($stylePair[1] == 'visible'); } } } } } } // Header/footer images if ($xmlSheet && $xmlSheet->legacyDrawingHF && !$this->_readDataOnly) { if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); $vmlRelationship = ''; foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") { $vmlRelationship = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]); } } if ($vmlRelationship != '') { // Fetch linked images $relsVML = simplexml_load_string($this->_getFromZipArchive($zip, dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels')); //~ http://schemas.openxmlformats.org/package/2006/relationships"); $drawings = array(); foreach ($relsVML->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") { $drawings[(string) $ele["Id"]] = self::dir_add($vmlRelationship, $ele["Target"]); } } // Fetch VML document $vmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $vmlRelationship)); $vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); $hfImages = array(); $shapes = $vmlDrawing->xpath('//v:shape'); foreach ($shapes as $shape) { $shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml'); $imageData = $shape->xpath('//v:imagedata'); $imageData = $imageData[0]; $imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office'); $style = self::toCSSArray((string) $shape['style']); $hfImages[(string) $shape['id']] = new Worksheet_HeaderFooterDrawing(); if (isset($imageData['title'])) { $hfImages[(string) $shape['id']]->setName((string) $imageData['title']); } $hfImages[(string) $shape['id']]->setPath("zip://{$pFilename}#" . $drawings[(string) $imageData['relid']], false); $hfImages[(string) $shape['id']]->setResizeProportional(false); $hfImages[(string) $shape['id']]->setWidth($style['width']); $hfImages[(string) $shape['id']]->setHeight($style['height']); $hfImages[(string) $shape['id']]->setOffsetX($style['margin-left']); $hfImages[(string) $shape['id']]->setOffsetY($style['margin-top']); $hfImages[(string) $shape['id']]->setResizeProportional(true); } $docSheet->getHeaderFooter()->setImages($hfImages); } } } } // TODO: Make sure drawings and graph are loaded differently! if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) { $relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); $drawings = array(); foreach ($relsWorksheet->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") { $drawings[(string) $ele["Id"]] = self::dir_add("{$dir}/{$fileWorksheet}", $ele["Target"]); } } if ($xmlSheet->drawing && !$this->_readDataOnly) { foreach ($xmlSheet->drawing as $drawing) { $fileDrawing = $drawings[(string) self::array_item($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")]; $relsDrawing = simplexml_load_string($this->_getFromZipArchive($zip, dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships"); $images = array(); if ($relsDrawing && $relsDrawing->Relationship) { foreach ($relsDrawing->Relationship as $ele) { if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") { $images[(string) $ele["Id"]] = self::dir_add($fileDrawing, $ele["Target"]); } } } $xmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $fileDrawing))->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"); if ($xmlDrawing->oneCellAnchor) { foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) { if ($oneCellAnchor->pic->blipFill) { $blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip; $xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm; $outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw; $objDrawing = new Worksheet_Drawing(); $objDrawing->setName((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name")); $objDrawing->setDescription((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr")); $objDrawing->setPath("zip://{$pFilename}#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false); $objDrawing->setCoordinates(Cell::stringFromColumnIndex($oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1)); $objDrawing->setOffsetX(Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff)); $objDrawing->setOffsetY(Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff)); $objDrawing->setResizeProportional(false); $objDrawing->setWidth(Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx"))); $objDrawing->setHeight(Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy"))); if ($xfrm) { $objDrawing->setRotation(Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot"))); } if ($outerShdw) { $shadow = $objDrawing->getShadow(); $shadow->setVisible(true); $shadow->setBlurRadius(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad"))); $shadow->setDistance(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist"))); $shadow->setDirection(Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir"))); $shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn")); $shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val")); $shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000); } $objDrawing->setWorksheet($docSheet); } } } if ($xmlDrawing->twoCellAnchor) { foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) { if ($twoCellAnchor->pic->blipFill) { $blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip; $xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm; $outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw; $objDrawing = new Worksheet_Drawing(); $objDrawing->setName((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name")); $objDrawing->setDescription((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr")); $objDrawing->setPath("zip://{$pFilename}#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false); $objDrawing->setCoordinates(Cell::stringFromColumnIndex($twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1)); $objDrawing->setOffsetX(Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff)); $objDrawing->setOffsetY(Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff)); $objDrawing->setResizeProportional(false); $objDrawing->setWidth(Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx"))); $objDrawing->setHeight(Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy"))); if ($xfrm) { $objDrawing->setRotation(Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot"))); } if ($outerShdw) { $shadow = $objDrawing->getShadow(); $shadow->setVisible(true); $shadow->setBlurRadius(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad"))); $shadow->setDistance(Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist"))); $shadow->setDirection(Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir"))); $shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn")); $shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val")); $shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000); } $objDrawing->setWorksheet($docSheet); } } } } } } // Loop through definedNames if ($xmlWorkbook->definedNames) { foreach ($xmlWorkbook->definedNames->definedName as $definedName) { // Extract range $extractedRange = (string) $definedName; $extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange); $extractedRange = str_replace('$', '', $extractedRange); // Valid range? if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') { continue; } // Some definedNames are only applicable if we are on the same sheet... if ((string) $definedName['localSheetId'] != '' && (string) $definedName['localSheetId'] == $sheetId) { // Switch on type switch ((string) $definedName['name']) { case '_xlnm._FilterDatabase': $docSheet->setAutoFilter($extractedRange); break; case '_xlnm.Print_Titles': // Split $extractedRange $extractedRange = explode(',', $extractedRange); // Set print titles foreach ($extractedRange as $range) { $matches = array(); // check for repeating columns, e g. 'A:A' or 'A:D' if (preg_match('/^([A-Z]+)\\:([A-Z]+)$/', $range, $matches)) { $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($matches[1], $matches[2])); } elseif (preg_match('/^(\\d+)\\:(\\d+)$/', $range, $matches)) { $docSheet->getPageSetup()->setRowsToRepeatAtTop(array($matches[1], $matches[2])); } } break; case '_xlnm.Print_Area': $range = explode('!', $extractedRange); $extractedRange = isset($range[1]) ? $range[1] : $range[0]; $docSheet->getPageSetup()->setPrintArea($extractedRange); break; default: break; } } } } // Next sheet id ++$sheetId; } // Loop through definedNames if ($xmlWorkbook->definedNames) { foreach ($xmlWorkbook->definedNames->definedName as $definedName) { // Extract range $extractedRange = (string) $definedName; $extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange); $extractedRange = str_replace('$', '', $extractedRange); // Valid range? if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') { continue; } // Some definedNames are only applicable if we are on the same sheet... if ((string) $definedName['localSheetId'] != '') { // Local defined name // Switch on type switch ((string) $definedName['name']) { case '_xlnm._FilterDatabase': case '_xlnm.Print_Titles': case '_xlnm.Print_Area': break; default: $range = explode('!', (string) $definedName); if (count($range) == 2) { $range[0] = str_replace("''", "'", $range[0]); $range[0] = str_replace("'", "", $range[0]); if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) { $extractedRange = str_replace('$', '', $range[1]); $scope = $docSheet->getParent()->getSheet((string) $definedName['localSheetId']); $excel->addNamedRange(new NamedRange((string) $definedName['name'], $worksheet, $extractedRange, true, $scope)); } } break; } } else { if (!isset($definedName['localSheetId'])) { // "Global" definedNames $locatedSheet = null; $extractedSheetName = ''; if (strpos((string) $definedName, '!') !== false) { // Extract sheet name $extractedSheetName = Worksheet::extractSheetTitle((string) $definedName, true); $extractedSheetName = $extractedSheetName[0]; // Locate sheet $locatedSheet = $excel->getSheetByName($extractedSheetName); // Modify range $range = explode('!', $extractedRange); $extractedRange = isset($range[1]) ? $range[1] : $range[0]; } if (!is_null($locatedSheet)) { $excel->addNamedRange(new NamedRange((string) $definedName['name'], $locatedSheet, $extractedRange, false)); } } } } } } if (!$this->_readDataOnly) { // active sheet index $activeTab = intval($xmlWorkbook->bookViews->workbookView["activeTab"]); // refers to old sheet index // keep active sheet index if sheet is still loaded, else first sheet is set as the active if (isset($mapSheetId[$activeTab]) && $mapSheetId[$activeTab] !== null) { $excel->setActiveSheetIndex($mapSheetId[$activeTab]); } else { if ($excel->getSheetCount() == 0) { $excel->createSheet(); } $excel->setActiveSheetIndex(0); } } break; } } return $excel; }
/** * Insert a new column or row, updating all possible related data * * @param string $pBefore Insert before this cell address (e.g. 'A1') * @param integer $pNumCols Number of columns to insert/delete (negative values indicate deletion) * @param integer $pNumRows Number of rows to insert/delete (negative values indicate deletion) * @param Worksheet $pSheet The worksheet that we're editing * @throws Exception */ public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, Worksheet $pSheet = null) { $remove = $pNumCols < 0 || $pNumRows < 0; $aCellCollection = $pSheet->getCellCollection(); // Get coordinates of $pBefore $beforeColumn = 'A'; $beforeRow = 1; list($beforeColumn, $beforeRow) = Cell::coordinateFromString($pBefore); $beforeColumnIndex = Cell::columnIndexFromString($beforeColumn); // Clear cells if we are removing columns or rows $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); // 1. Clear column strips if we are removing columns if ($pNumCols < 0 && $beforeColumnIndex - 2 + $pNumCols > 0) { for ($i = 1; $i <= $highestRow - 1; ++$i) { for ($j = $beforeColumnIndex - 1 + $pNumCols; $j <= $beforeColumnIndex - 2; ++$j) { $coordinate = Cell::stringFromColumnIndex($j) . $i; $pSheet->removeConditionalStyles($coordinate); if ($pSheet->cellExists($coordinate)) { $pSheet->getCell($coordinate)->setValueExplicit('', Cell\DataType::TYPE_NULL); $pSheet->getCell($coordinate)->setXfIndex(0); } } } } // 2. Clear row strips if we are removing rows if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) { for ($i = $beforeColumnIndex - 1; $i <= Cell::columnIndexFromString($highestColumn) - 1; ++$i) { for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) { $coordinate = Cell::stringFromColumnIndex($i) . $j; $pSheet->removeConditionalStyles($coordinate); if ($pSheet->cellExists($coordinate)) { $pSheet->getCell($coordinate)->setValueExplicit('', Cell\DataType::TYPE_NULL); $pSheet->getCell($coordinate)->setXfIndex(0); } } } } // Loop through cells, bottom-up, and change cell coordinates if ($remove) { // It's faster to reverse and pop than to use unshift, especially with large cell collections $aCellCollection = array_reverse($aCellCollection); } while ($cellID = array_pop($aCellCollection)) { $cell = $pSheet->getCell($cellID); $cellIndex = Cell::columnIndexFromString($cell->getColumn()); if ($cellIndex - 1 + $pNumCols < 0) { continue; } // New coordinates $newCoordinates = Cell::stringFromColumnIndex($cellIndex - 1 + $pNumCols) . ($cell->getRow() + $pNumRows); // Should the cell be updated? Move value and cellXf index from one cell to another. if ($cellIndex >= $beforeColumnIndex && $cell->getRow() >= $beforeRow) { // Update cell styles $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex()); // Insert this cell at its new location if ($cell->getDataType() == Cell\DataType::TYPE_FORMULA) { // Formula should be adjusted $pSheet->getCell($newCoordinates)->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle())); } else { // Formula should not be adjusted $pSheet->getCell($newCoordinates)->setValue($cell->getValue()); } // Clear the original cell $pSheet->getCellCacheController()->deleteCacheData($cellID); } else { /* We don't need to update styles for rows/columns before our insertion position, but we do still need to adjust any formulae in those cells */ if ($cell->getDataType() == Cell\DataType::TYPE_FORMULA) { // Formula should be adjusted $cell->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows, $pSheet->getTitle())); } } } // Duplicate styles for the newly inserted cells $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); if ($pNumCols > 0 && $beforeColumnIndex - 2 > 0) { for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) { // Style $coordinate = Cell::stringFromColumnIndex($beforeColumnIndex - 2) . $i; if ($pSheet->cellExists($coordinate)) { $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false; for ($j = $beforeColumnIndex - 1; $j <= $beforeColumnIndex - 2 + $pNumCols; ++$j) { $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex); if ($conditionalStyles) { $cloned = array(); foreach ($conditionalStyles as $conditionalStyle) { $cloned[] = clone $conditionalStyle; } $pSheet->setConditionalStyles(Cell::stringFromColumnIndex($j) . $i, $cloned); } } } } } if ($pNumRows > 0 && $beforeRow - 1 > 0) { for ($i = $beforeColumnIndex - 1; $i <= Cell::columnIndexFromString($highestColumn) - 1; ++$i) { // Style $coordinate = Cell::stringFromColumnIndex($i) . ($beforeRow - 1); if ($pSheet->cellExists($coordinate)) { $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false; for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) { $pSheet->getCell(Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex); if ($conditionalStyles) { $cloned = array(); foreach ($conditionalStyles as $conditionalStyle) { $cloned[] = clone $conditionalStyle; } $pSheet->setConditionalStyles(Cell::stringFromColumnIndex($i) . $j, $cloned); } } } } } // Update worksheet: column dimensions $this->adjustColumnDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: row dimensions $this->adjustRowDimensions($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: page breaks $this->adjustPageBreaks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: comments $this->adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: hyperlinks $this->adjustHyperlinks($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: data validations $this->adjustDataValidations($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: merge cells $this->adjustMergeCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: protected cells $this->adjustProtectedCells($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows); // Update worksheet: autofilter $autoFilter = $pSheet->getAutoFilter(); $autoFilterRange = $autoFilter->getRange(); if (!empty($autoFilterRange)) { if ($pNumCols != 0) { $autoFilterColumns = array_keys($autoFilter->getColumns()); if (count($autoFilterColumns) > 0) { sscanf($pBefore, '%[A-Z]%d', $column, $row); $columnIndex = Cell::columnIndexFromString($column); list($rangeStart, $rangeEnd) = Cell::rangeBoundaries($autoFilterRange); if ($columnIndex <= $rangeEnd[0]) { if ($pNumCols < 0) { // If we're actually deleting any columns that fall within the autofilter range, // then we delete any rules for those columns $deleteColumn = $columnIndex + $pNumCols - 1; $deleteCount = abs($pNumCols); for ($i = 1; $i <= $deleteCount; ++$i) { if (in_array(Cell::stringFromColumnIndex($deleteColumn), $autoFilterColumns)) { $autoFilter->clearColumn(Cell::stringFromColumnIndex($deleteColumn)); } ++$deleteColumn; } } $startCol = $columnIndex > $rangeStart[0] ? $columnIndex : $rangeStart[0]; // Shuffle columns in autofilter range if ($pNumCols > 0) { // For insert, we shuffle from end to beginning to avoid overwriting $startColID = Cell::stringFromColumnIndex($startCol - 1); $toColID = Cell::stringFromColumnIndex($startCol + $pNumCols - 1); $endColID = Cell::stringFromColumnIndex($rangeEnd[0]); $startColRef = $startCol; $endColRef = $rangeEnd[0]; $toColRef = $rangeEnd[0] + $pNumCols; do { $autoFilter->shiftColumn(Cell::stringFromColumnIndex($endColRef - 1), Cell::stringFromColumnIndex($toColRef - 1)); --$endColRef; --$toColRef; } while ($startColRef <= $endColRef); } else { // For delete, we shuffle from beginning to end to avoid overwriting $startColID = Cell::stringFromColumnIndex($startCol - 1); $toColID = Cell::stringFromColumnIndex($startCol + $pNumCols - 1); $endColID = Cell::stringFromColumnIndex($rangeEnd[0]); do { $autoFilter->shiftColumn($startColID, $toColID); ++$startColID; ++$toColID; } while ($startColID != $endColID); } } } } $pSheet->setAutoFilter($this->updateCellReference($autoFilterRange, $pBefore, $pNumCols, $pNumRows)); } // Update worksheet: freeze pane if ($pSheet->getFreezePane() != '') { $pSheet->freezePane($this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows)); } // Page setup if ($pSheet->getPageSetup()->isPrintAreaSet()) { $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows)); } // Update worksheet: drawings $aDrawings = $pSheet->getDrawingCollection(); foreach ($aDrawings as $objDrawing) { $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows); if ($objDrawing->getCoordinates() != $newReference) { $objDrawing->setCoordinates($newReference); } } // Update workbook: named ranges if (count($pSheet->getParent()->getNamedRanges()) > 0) { foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) { if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) { $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows)); } } } // Garbage collect $pSheet->garbageCollect(); }
function write_url($row, $col, $url, $string = '') { parent::write_url($row, $col, $url, $string, $this->m_format); }
/** * Write VML comments to XML format * * @param Worksheet $pWorksheet * @return string XML Output * @throws Exception */ public function writeVMLComments(Worksheet $pWorksheet = null) { // Create XML writer $objWriter = null; if ($this->getParentWriter()->getUseDiskCaching()) { $objWriter = new Shared_XMLWriter(Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory()); } else { $objWriter = new Shared_XMLWriter(Shared_XMLWriter::STORAGE_MEMORY); } // XML header $objWriter->startDocument('1.0', 'UTF-8', 'yes'); // Comments cache $comments = $pWorksheet->getComments(); // xml $objWriter->startElement('xml'); $objWriter->writeAttribute('xmlns:v', 'urn:schemas-microsoft-com:vml'); $objWriter->writeAttribute('xmlns:o', 'urn:schemas-microsoft-com:office:office'); $objWriter->writeAttribute('xmlns:x', 'urn:schemas-microsoft-com:office:excel'); // o:shapelayout $objWriter->startElement('o:shapelayout'); $objWriter->writeAttribute('v:ext', 'edit'); // o:idmap $objWriter->startElement('o:idmap'); $objWriter->writeAttribute('v:ext', 'edit'); $objWriter->writeAttribute('data', '1'); $objWriter->endElement(); $objWriter->endElement(); // v:shapetype $objWriter->startElement('v:shapetype'); $objWriter->writeAttribute('id', '_x0000_t202'); $objWriter->writeAttribute('coordsize', '21600,21600'); $objWriter->writeAttribute('o:spt', '202'); $objWriter->writeAttribute('path', 'm,l,21600r21600,l21600,xe'); // v:stroke $objWriter->startElement('v:stroke'); $objWriter->writeAttribute('joinstyle', 'miter'); $objWriter->endElement(); // v:path $objWriter->startElement('v:path'); $objWriter->writeAttribute('gradientshapeok', 't'); $objWriter->writeAttribute('o:connecttype', 'rect'); $objWriter->endElement(); $objWriter->endElement(); // Loop through comments foreach ($comments as $key => $value) { $this->_writeVMLComment($objWriter, $key, $value); } $objWriter->endElement(); // Return return $objWriter->getData(); }
public function index() { @$this->loadModel("Dashboard"); global $session; $dashData = array(); $dashData = $this->model->getDashboardStat(); $this->view->oticketcount = $dashData['otcount']; $this->view->aticketcount = $dashData['atcount']; $this->view->oschedule = $dashData['oschedule']; $this->view->oworksheet = $dashData['oworksheet']; $this->view->clients = $dashData['clients']; $this->view->pendings = $dashData['openPend']; $this->view->cproducts = $dashData['cproducts']; $lastmonth = (int) date("n") - 1; $curmonth = date("n"); $this->view->monthreport = $this->model->getMonthlyReportFinance(" Month(datecreated) ='" . $curmonth . "' AND Year(datecreated)='" . date("Y") . "'"); $this->view->lastmonthreport = $this->model->getLastMonthlyReportFinance(" Month(datecreated) ='" . $lastmonth . "' AND Year(datecreated)='" . date("Y") . "'"); $this->view->thisquarter = $this->model->getThisQuaterReportFinance(" Quarter(datecreated) ='" . self::date_quarter() . "' AND Year(datecreated)='" . date("Y") . "'"); global $session; if ($session->empright == "Super Admin") { $this->view->render("dashboard/index"); } elseif ($session->empright == "Customer Support Services" || $session->empright == "Customer Support Service") { $this->view->render("support/index"); } elseif ($session->empright == "Customer Support Engineer" || $session->empright == "Customer Service Engineer") { @$this->loadModel("Itdepartment"); global $session; $datan = ""; $uri = new Url(""); //$empworkdata = $this->model->getWorkSheetEmployee($id,""); $ptasks = Worksheet::find_by_sql("SELECT * FROM work_sheet_form WHERE cse_emp_id =" . $_SESSION['emp_ident']); // print_r($ptasks); //$empworkdata['worksheet']; $x = 1; $datan .= "<table width='100%'>\n <thead><tr>\n \t<th>S/N</th><th>Prod ID</th><th>Status</th><th>Emp ID</th><th>Issue</th><th>Date Generated </th><th></th><th></th>\n </tr>\n </thead>\n <tbody>"; if ($ptasks) { foreach ($ptasks as $task) { $datan .= "<tr><td>{$x}</td><td>{$task->prod_id}</td><td>{$task->status} </td><td>{$task->cse_emp_id}</td><td>{$task->problem}</td><td>{$task->sheet_date}</td><td><a href='" . $uri->link("itdepartment/worksheetdetail/" . $task->id . "") . "'>View Detail</a></td><td></td></tr>"; $x++; } } else { $datan .= "<tr><td colspan='8'></td></tr>"; } $datan .= "</tbody></table>"; $mysched = "<div id='transalert'>"; $mysched .= isset($_SESSION['message']) && !empty($_SESSION['message']) ? $_SESSION['message'] : ""; $mysched .= "</div>"; $psched = Schedule::find_by_sql("SELECT * FROM schedule WHERE status !='Closed' AND emp_id =" . $_SESSION['emp_ident'] . " ORDER BY id DESC"); //print_r($psched); //$empworkdata['worksheet']; $x = 1; $mysched .= "<table width='100%'>\n <thead><tr>\n \t<th>S/N</th><th>Machine</th><th>Issue</th><th>Location</th><th>Task Type</th><th>Task Date </th><th></th><th></th><th></th>\n </tr>\n </thead>\n <tbody>"; if ($psched) { foreach ($psched as $task) { $mysched .= "<tr><td>{$x}</td><td>{$task->prod_name}</td><td>{$task->issue} </td>"; $machine = Cproduct::find_by_id($task->prod_id); $mysched .= "<td>{$machine->install_address} {$machine->install_city}</td><td>{$task->maint_type}</td><td>{$task->s_date}</td><td>"; if ($task->status == "Open") { $mysched .= "<a scheddata='{$task->id}' class='acceptTask' href='#'>Accept Task</a>"; } if ($task->status == "In Progress") { $mysched .= "<a href='" . $uri->link("itdepartment/worksheetupdateEmp/" . $task->id . "") . "'>Get Work Sheet</a>"; } $mysched .= "\n \n <div id='myModal{$task->id}' class='reveal-modal'>\n <h2>Accept Task </h2>\n <p class='lead'>Click on the button below to accept task! </p>\n <form action='?url=itdepartment/doAcceptTask' method='post'>\n <input type='hidden' value='{$task->id}' name='mtaskid' id='mtaskid' />\n <p><a href='#' data-reveal-id='secondModal' class='secondary button acceptTast' >Accept</a></p>\n </form>\n <a class='close-reveal-modal'>×</a>\n</div>\n\n\n \n \n </td><td></td><td></td></tr>"; $x++; } } else { $mysched .= "<tr><td colspan='8'>There is no task currently</td></tr>"; } $mysched .= "</tbody></table>"; $this->view->oldtask = $datan; $this->view->schedule = $mysched; $this->view->mee = $this->model->getEmployee($_SESSION['emp_ident']); $this->view->render("itdepartment/staffaccount"); } else { $this->view->render("login/index", true); } }
/** * Write Defined Name for PrintTitles * * @param Shared_XMLWriter $objWriter XML Writer * @param Worksheet $pSheet * @param int $pSheetId * @throws Exception */ private function _writeDefinedNameForPrintArea(Shared_XMLWriter $objWriter = null, Worksheet $pSheet = null, $pSheetId = 0) { // definedName for PrintArea if ($pSheet->getPageSetup()->isPrintAreaSet()) { $objWriter->startElement('definedName'); $objWriter->writeAttribute('name', '_xlnm.Print_Area'); $objWriter->writeAttribute('localSheetId', $pSheetId); // Setting string $settingString = ''; // Print area $printArea = Cell::splitRange($pSheet->getPageSetup()->getPrintArea()); $chunks = array(); foreach ($printArea as $printAreaRect) { $printAreaRect[0] = Cell::absoluteCoordinate($printAreaRect[0]); $printAreaRect[1] = Cell::absoluteCoordinate($printAreaRect[1]); $chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect); } $objWriter->writeRaw(implode(',', $chunks)); $objWriter->endElement(); } }
/** * Read RANGEPROTECTION record * Reading of this record is based on Microsoft Office Excel 97-2000 Binary File Format Specification, * where it is referred to as FEAT record */ private function _readRangeProtection() { $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; // local pointer in record data $offset = 0; if (!$this->_readDataOnly) { $offset += 12; // offset: 12; size: 2; shared feature type, 2 = enhanced protection, 4 = smart tag $isf = $this->_GetInt2d($recordData, 12); if ($isf != 2) { // we only read FEAT records of type 2 return; } $offset += 2; $offset += 5; // offset: 19; size: 2; count of ref ranges this feature is on $cref = $this->_GetInt2d($recordData, 19); $offset += 2; $offset += 6; // offset: 27; size: 8 * $cref; list of cell ranges (like in hyperlink record) $cellRanges = array(); for ($i = 0; $i < $cref; ++$i) { try { $cellRange = $this->_readBIFF8CellRangeAddressFixed(substr($recordData, 27 + 8 * $i, 8)); } catch (Exception $e) { return; } $cellRanges[] = $cellRange; $offset += 8; } // offset: var; size: var; variable length of feature specific data $rgbFeat = substr($recordData, $offset); $offset += 4; // offset: var; size: 4; the encrypted password (only 16-bit although field is 32-bit) $wPassword = $this->_GetInt4d($recordData, $offset); $offset += 4; // Apply range protection to sheet if ($cellRanges) { $this->_phpSheet->protectCells(implode(' ', $cellRanges), strtoupper(dechex($wPassword)), true); } } }
/** * Cell at a specific coordinate exists? * * @param string $pCoordinate Coordinate of the cell * @throws Exception * @return boolean */ public function cellExists($pCoordinate = 'A1') { // Worksheet reference? if (strpos($pCoordinate, '!') !== false) { $worksheetReference = Worksheet::extractSheetTitle($pCoordinate, true); return $this->getParent()->getSheetByName($worksheetReference[0])->cellExists($worksheetReference[1]); } // Named range? if (!preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $pCoordinate, $matches) && preg_match('/^' . Calculation::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $pCoordinate, $matches)) { $namedRange = NamedRange::resolveRange($pCoordinate, $this); if (!is_null($namedRange)) { $pCoordinate = $namedRange->getRange(); if ($this->getHashCode() != $namedRange->getWorksheet()->getHashCode()) { if (!$namedRange->getLocalOnly()) { return $namedRange->getWorksheet()->cellExists($pCoordinate); } else { throw new Exception('Named range ' . $namedRange->getName() . ' is not accessible from within sheet ' . $this->getTitle()); } } } } // Uppercase coordinate $pCoordinate = strtoupper($pCoordinate); if (strpos($pCoordinate, ':') !== false || strpos($pCoordinate, ',') !== false) { throw new Exception('Cell coordinate can not be a range of cells.'); } elseif (strpos($pCoordinate, '$') !== false) { throw new Exception('Cell coordinate must not be absolute.'); } else { // Coordinates $aCoordinates = Cell::coordinateFromString($pCoordinate); // Cell exists? return $this->_cellCollection->isDataSet($pCoordinate); } }
/** * Extract range values * * @param string &$pRange String based range representation * @param Worksheet $pSheet Worksheet * @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned. * @throws Exception */ public function extractNamedRange(&$pRange = 'A1', Worksheet $pSheet = null, $resetLog = true) { // Return value $returnValue = array(); // echo 'extractNamedRange('.$pRange.')<br />'; if (!is_null($pSheet)) { // echo 'Current sheet name is '.$pSheet->getTitle().'<br />'; // echo 'Range reference is '.$pRange.'<br />'; if (strpos($pRange, '!') !== false) { // echo '$pRange reference includes sheet reference<br />'; $worksheetReference = Worksheet::extractSheetTitle($pRange, true); $pSheet = $pSheet->getParent()->getSheetByName($worksheetReference[0]); // echo 'New sheet name is '.$pSheet->getTitle().'<br />'; $pRange = $worksheetReference[1]; // echo 'Adjusted Range reference is '.$pRange.'<br />'; } // Named range? $namedRange = NamedRange::resolveRange($pRange, $pSheet); if (!is_null($namedRange)) { $pSheet = $namedRange->getWorksheet(); //// echo 'Named Range '.$pRange.' ('; $pRange = $namedRange->getRange(); //// echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />'; // if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) { // if (!$namedRange->getLocalOnly()) { // $pSheet = $namedRange->getWorksheet(); // } else { // return $returnValue; // } // } } else { return Calculation_Functions::REF(); } // Extract range $aReferences = Cell::extractAllCellReferencesInRange($pRange); if (count($aReferences) == 1) { list($currentCol, $currentRow) = Cell::coordinateFromString($aReferences[0]); if ($pSheet->cellExists($aReferences[0])) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = NULL; } } else { // Extract cell data foreach ($aReferences as $reference) { // Extract range list($currentCol, $currentRow) = Cell::coordinateFromString($reference); // echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />'; if ($pSheet->cellExists($reference)) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = NULL; } } } // print_r($returnValue); // echo '<br />'; } // Return return $returnValue; }
<?php include_once "classes/Worksheet.php"; include_once "classes/Client.php"; $worksheet = new Worksheet(); //check if client has to be added if ($_POST['worksheet']['addCompany']) { $info = $_POST['worksheet']['addCompany']; // add through Client class, get last id and setClient $client = new Client(); $client->setClientAddress($info['address']); $client->setClientCity($info['city']); $client->setClientEmail($info['email']); $client->setClientName($info['name']); $client->setClientPostal($info['postal']); $client->setClientTelephone($info['phone']); $client->setCompany($info['company']); $client_id = $client->addClient(); $worksheet->setClient($client_id); } else { // its set as false, take saved id $worksheet->setClient($_POST['worksheet']['client']); } // set data $worksheet->setCRM($_POST['worksheet']['CRMNumber']); $worksheet->setCRMDate($_POST['worksheet']['CRMDate']); $worksheet->setDate($_POST['worksheet']['date']); $worksheet->setExpenses($_POST['worksheet']['expenses']); $worksheet->setMaterial($_POST['worksheet']['material']); $worksheet->setNotes($_POST['worksheet']['notes']); $worksheet->setSignatureClient($_POST['worksheet']['signature']);
/** * Extract range values * * @param string &$pRange String based range representation * @param Worksheet $pSheet Worksheet * @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned. * @param boolean $resetLog Flag indicating whether calculation log should be reset or not * @throws Calculation\Exception */ public function extractNamedRange(&$pRange = 'A1', Worksheet $pSheet = null, $resetLog = true) { // Return value $returnValue = array(); // echo 'extractNamedRange('.$pRange.')<br />'; if ($pSheet !== null) { $pSheetName = $pSheet->getTitle(); // echo 'Current sheet name is '.$pSheetName.'<br />'; // echo 'Range reference is '.$pRange.'<br />'; if (strpos($pRange, '!') !== false) { // echo '$pRange reference includes sheet reference', PHP_EOL; list($pSheetName, $pRange) = Worksheet::extractSheetTitle($pRange, true); // echo 'New sheet name is '.$pSheetName, PHP_EOL; // echo 'Adjusted Range reference is '.$pRange, PHP_EOL; $pSheet = $this->spreadsheet->getSheetByName($pSheetName); } // Named range? $namedRange = NamedRange::resolveRange($pRange, $pSheet); if ($namedRange !== null) { $pSheet = $namedRange->getWorksheet(); // echo 'Named Range '.$pRange.' ('; $pRange = $namedRange->getRange(); $splitRange = Cell::splitRange($pRange); // Convert row and column references if (ctype_alpha($splitRange[0][0])) { $pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow(); } elseif (ctype_digit($splitRange[0][0])) { $pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1]; } // echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />'; // if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) { // if (!$namedRange->getLocalOnly()) { // $pSheet = $namedRange->getWorksheet(); // } else { // return $returnValue; // } // } } else { return Calculation\Functions::REF(); } // Extract range $aReferences = Cell::extractAllCellReferencesInRange($pRange); // var_dump($aReferences); if (!isset($aReferences[1])) { // Single cell (or single column or row) in range list($currentCol, $currentRow) = Cell::coordinateFromString($aReferences[0]); $cellValue = null; if ($pSheet->cellExists($aReferences[0])) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = null; } } else { // Extract cell data for all cells in the range foreach ($aReferences as $reference) { // Extract range list($currentCol, $currentRow) = Cell::coordinateFromString($reference); // echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />'; $cellValue = null; if ($pSheet->cellExists($reference)) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = null; } } } // print_r($returnValue); // echo '<br />'; } return $returnValue; }
/** * Insert a new column, updating all possible related data * * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to insert * @param int $pNumRows Number of rows to insert * @throws Exception */ public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, Worksheet $pSheet = null) { $aCellCollection = $pSheet->getCellCollection(); // Get coordinates of $pBefore $beforeColumn = 'A'; $beforeRow = 1; list($beforeColumn, $beforeRow) = Cell::coordinateFromString($pBefore); // Clear cells if we are removing columns or rows $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); // 1. Clear column strips if we are removing columns if ($pNumCols < 0 && Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) { for ($i = 1; $i <= $highestRow - 1; ++$i) { for ($j = Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols; $j <= Cell::columnIndexFromString($beforeColumn) - 2; ++$j) { $coordinate = Cell::stringFromColumnIndex($j) . $i; $pSheet->removeConditionalStyles($coordinate); if ($pSheet->cellExists($coordinate)) { $pSheet->getCell($coordinate)->setValueExplicit('', Cell_DataType::TYPE_NULL); $pSheet->getCell($coordinate)->setXfIndex(0); } } } } // 2. Clear row strips if we are removing rows if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) { for ($i = Cell::columnIndexFromString($beforeColumn) - 1; $i <= Cell::columnIndexFromString($highestColumn) - 1; ++$i) { for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) { $coordinate = Cell::stringFromColumnIndex($i) . $j; $pSheet->removeConditionalStyles($coordinate); if ($pSheet->cellExists($coordinate)) { $pSheet->getCell($coordinate)->setValueExplicit('', Cell_DataType::TYPE_NULL); $pSheet->getCell($coordinate)->setXfIndex(0); } } } } // Loop through cells, bottom-up, and change cell coordinates while ($cellID = $pNumCols < 0 || $pNumRows < 0 ? array_shift($aCellCollection) : array_pop($aCellCollection)) { $cell = $pSheet->getCell($cellID); // New coordinates $newCoordinates = Cell::stringFromColumnIndex(Cell::columnIndexFromString($cell->getColumn()) - 1 + $pNumCols) . ($cell->getRow() + $pNumRows); // Should the cell be updated? Move value and cellXf index from one cell to another. if (Cell::columnIndexFromString($cell->getColumn()) >= Cell::columnIndexFromString($beforeColumn) && $cell->getRow() >= $beforeRow) { // Update cell styles $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex()); $cell->setXfIndex(0); // Insert this cell at its new location if ($cell->getDataType() == Cell_DataType::TYPE_FORMULA) { // Formula should be adjusted $pSheet->getCell($newCoordinates)->setValue($this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows)); } else { // Formula should not be adjusted $pSheet->getCell($newCoordinates)->setValue($cell->getValue()); } // Clear the original cell $pSheet->getCell($cell->getCoordinate())->setValue(''); } } // Duplicate styles for the newly inserted cells $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); if ($pNumCols > 0 && Cell::columnIndexFromString($beforeColumn) - 2 > 0) { for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) { // Style $coordinate = Cell::stringFromColumnIndex(Cell::columnIndexFromString($beforeColumn) - 2) . $i; if ($pSheet->cellExists($coordinate)) { $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false; for ($j = Cell::columnIndexFromString($beforeColumn) - 1; $j <= Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols; ++$j) { $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex); if ($conditionalStyles) { $cloned = array(); foreach ($conditionalStyles as $conditionalStyle) { $cloned[] = clone $conditionalStyle; } $pSheet->setConditionalStyles(Cell::stringFromColumnIndex($j) . $i, $cloned); } } } } } if ($pNumRows > 0 && $beforeRow - 1 > 0) { for ($i = Cell::columnIndexFromString($beforeColumn) - 1; $i <= Cell::columnIndexFromString($highestColumn) - 1; ++$i) { // Style $coordinate = Cell::stringFromColumnIndex($i) . ($beforeRow - 1); if ($pSheet->cellExists($coordinate)) { $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false; for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) { $pSheet->getCell(Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex); if ($conditionalStyles) { $cloned = array(); foreach ($conditionalStyles as $conditionalStyle) { $cloned[] = clone $conditionalStyle; } $pSheet->setConditionalStyles(Cell::stringFromColumnIndex($i) . $j, $cloned); } } } } } // Update worksheet: column dimensions $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true); if (count($aColumnDimensions) > 0) { foreach ($aColumnDimensions as $objColumnDimension) { $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows); list($newReference) = Cell::coordinateFromString($newReference); if ($objColumnDimension->getColumnIndex() != $newReference) { $objColumnDimension->setColumnIndex($newReference); } } $pSheet->refreshColumnDimensions(); } // Update worksheet: row dimensions $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true); if (count($aRowDimensions) > 0) { foreach ($aRowDimensions as $objRowDimension) { $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows); list(, $newReference) = Cell::coordinateFromString($newReference); if ($objRowDimension->getRowIndex() != $newReference) { $objRowDimension->setRowIndex($newReference); } } $pSheet->refreshRowDimensions(); $copyDimension = $pSheet->getRowDimension($beforeRow - 1); for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) { $newDimension = $pSheet->getRowDimension($i); $newDimension->setRowHeight($copyDimension->getRowHeight()); $newDimension->setVisible($copyDimension->getVisible()); $newDimension->setOutlineLevel($copyDimension->getOutlineLevel()); $newDimension->setCollapsed($copyDimension->getCollapsed()); } } // Update worksheet: breaks $aBreaks = array_reverse($pSheet->getBreaks(), true); foreach ($aBreaks as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->setBreak($newReference, $value); $pSheet->setBreak($key, Worksheet::BREAK_NONE); } } // Update worksheet: hyperlinks $aHyperlinkCollection = array_reverse($pSheet->getHyperlinkCollection(), true); foreach ($aHyperlinkCollection as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->setHyperlink($newReference, $value); $pSheet->setHyperlink($key, null); } } // Update worksheet: data validations $aDataValidationCollection = array_reverse($pSheet->getDataValidationCollection(), true); foreach ($aDataValidationCollection as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->setDataValidation($newReference, $value); $pSheet->setDataValidation($key, null); } } // Update worksheet: merge cells $aMergeCells = $pSheet->getMergeCells(); $aNewMergeCells = array(); // the new array of all merge cells foreach ($aMergeCells as $key => &$value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); $aNewMergeCells[$newReference] = $newReference; } $pSheet->setMergeCells($aNewMergeCells); // replace the merge cells array // Update worksheet: protected cells $aProtectedCells = array_reverse($pSheet->getProtectedCells(), true); foreach ($aProtectedCells as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->protectCells($newReference, $value, true); $pSheet->unprotectCells($key); } } // Update worksheet: autofilter if ($pSheet->getAutoFilter() != '') { $pSheet->setAutoFilter($this->updateCellReference($pSheet->getAutoFilter(), $pBefore, $pNumCols, $pNumRows)); } // Update worksheet: freeze pane if ($pSheet->getFreezePane() != '') { $pSheet->freezePane($this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows)); } // Page setup if ($pSheet->getPageSetup()->isPrintAreaSet()) { $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows)); } // Update worksheet: drawings $aDrawings = $pSheet->getDrawingCollection(); foreach ($aDrawings as $objDrawing) { $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows); if ($objDrawing->getCoordinates() != $newReference) { $objDrawing->setCoordinates($newReference); } } // Update workbook: named ranges if (count($pSheet->getParent()->getNamedRanges()) > 0) { foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) { if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) { $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows)); } } } // Garbage collect $pSheet->garbageCollect(); }
/** * this section is * needed to list out worksheets */ public function worksheetlist() { $worksheetlisting = ""; $this->loadModel("Support"); $datum = $this->model->getWorkSheetList("", "support"); $this->view->worksheets = $datum['worksheet']; $uri = new Url(""); $worksheetlisting .= "<div class='row'><div class='large-12 columns'>" . $datum['mypagin']; $worksheetlisting .= "</div></div><div class='row'><div class='large-12 columns'><table width='100%'>\n<thead><tr>\n\t<th>S/N</th><th>Product/Machine</th><th>Issue</th><th>Status</th><th>Technician</th><th>Date Generated </th><th>Expenses</th><th></th><th></th><th></th>\n</tr>\n</thead>\n<tbody>"; if ($this->view->worksheets) { $x = 1; foreach ($this->view->worksheets as $worksheet) { $worksheetlisting .= "<tr>\n \t<td>{$x}</td><td>"; $cprod = $this->model->getClientProdByID($worksheet->prod_id); $worksheetlisting .= $cprod->prod_name . " " . $cprod->install_address . " " . $cprod->install_city; //print_r($worksheet->cse_emp_id); $worksheetlisting .= "</td><td>{$worksheet->problem}</td><td>{$worksheet->status} </td><td>"; $emp = $this->model->getEmployee((int) preg_replace('#[^0-9]#i', '', $worksheet->cse_emp_id)); //print_r($cprod); $worksheetlisting .= $emp->emp_fname . " " . $emp->emp_lname; $worksheetlisting .= "</td><td>" . date_format(date_create($worksheet->sheet_date), "M d Y H:i:s") . "</td><td>" . Worksheet::getExpensesById($worksheet->id) . "</td>"; /** * section to set grant and\ * previledge */ global $session; foreach ($session->employee_role as $erole) { $emodule = Modules::find_by_module($erole->module); $grant = array(); $grant = explode(",", $erole->access); if ($erole->module == "worksheetform") { if (in_array("Modify", $grant)) { $worksheetlisting .= "<td><a href='" . $uri->link("support/tasksupport/" . $worksheet->id . "") . "'>Allocate Resource</a></td>"; } elseif (in_array("Modify", $grant)) { $worksheetlisting .= "<td><a href='" . $uri->link("support/worksheetedit/" . $worksheet->id . "") . "'>Edit</a></td>"; } elseif ($session->employee_role == '10') { $worksheetlisting .= "<td><a href='" . $uri->link("support/worksheetedit/" . $worksheet->id . "") . "'>Edit</a></td>"; } else { $worksheetlisting .= "<td></td>"; } if (in_array("View", $grant)) { $worksheetlisting .= "<td><a href='" . $uri->link("support/worksheetdetail/" . $worksheet->id . "") . "'>View Detail</a></td>"; } else { $worksheetlisting .= "<td></td>"; } if (in_array("Delete", $grant)) { $worksheetlisting .= "<td></td>"; } else { $worksheetlisting .= "<td></td>"; } } } $worksheetlisting .= "</tr>"; $x++; } } else { $worksheetlisting .= "<tr><td colspan='9'>No record to display</td></tr>"; } $worksheetlisting .= "</tbody>\n</table></div></div><div class='row'><div class='large-12 columns'>"; $worksheetlisting .= $datum['mypagin'] . "</div><p> </p></div>"; $this->view->myvends = $worksheetlisting; if (isset($_POST['clientid'])) { echo $worksheetlisting; } elseif (isset($_POST['rec'])) { echo $worksheetlisting; } else { $this->view->render("support/worksheetlist"); } }
/** * Get forms and ccurrent entity * * @param $dossierId * @param $stepName * @param $disabled * @param null $page * @return array */ public function getFormsAndEntity($dossierId, $stepName, $disabled, $page = null) { $em = $this->getDoctrine()->getManager(); $dossier = $em->getRepository('BraemBackofficeBundle:Dossier')->find($dossierId); $worksheet = $dossier->getWorksheets()->last(); $worksheetId = 0; if (!$worksheet) { $worksheet = new Worksheet(); $worksheet->setDossier($dossier); } list($formType, $entityName) = $this->getWorksheetConfigs($stepName); $stepEntity = new $entityName(); if ($worksheet->getId()) { $worksheetId = $worksheet->getId(); $stepEntity = $em->getRepository($entityName)->findOneBy(array('worksheetId' => $worksheetId)); if (!$stepEntity) { $stepEntity = new $entityName(); } } if ($page == 'edit') { $form = $this->createForm(new WorksheetType(), $worksheet, array('disabled' => true)); } else { $form = $this->createForm(new WorksheetType(), $worksheet, array('disabled' => $disabled)); } $worksheetStepForm = $this->createForm($formType, $stepEntity, array('disabled' => $disabled)); return array($worksheetId, $dossier, $form, $worksheetStepForm, $stepEntity, $worksheet); }