/**
* 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);
}
