/** * TIMEVALUE * * @param string $timeValue * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, * depending on the value of the ReturnDateType flag */ public static function TIMEVALUE($timeValue) { $timeValue = trim(self::flattenSingleValue($timeValue), '"'); $timeValue = str_replace(array('/', '.'), array('-', '-'), $timeValue); $PHPDateArray = date_parse($timeValue); if ($PHPDateArray !== False && $PHPDateArray['error_count'] == 0) { if (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE) { $excelDateValue = Shared_Date::FormattedPHPToExcel($PHPDateArray['year'], $PHPDateArray['month'], $PHPDateArray['day'], $PHPDateArray['hour'], $PHPDateArray['minute'], $PHPDateArray['second']); } else { $excelDateValue = Shared_Date::FormattedPHPToExcel(1900, 1, 1, $PHPDateArray['hour'], $PHPDateArray['minute'], $PHPDateArray['second']) - 1; } switch (self::getReturnDateType()) { case self::RETURNDATE_EXCEL: return (double) $excelDateValue; break; case self::RETURNDATE_PHP_NUMERIC: return (int) ($phpDateValue = Shared_Date::ExcelToPHP($excelDateValue + 25569) - 3600); break; case self::RETURNDATE_PHP_OBJECT: return new DateTime('1900-01-01 ' . $PHPDateArray['hour'] . ':' . $PHPDateArray['minute'] . ':' . $PHPDateArray['second']); break; } } return self::$_errorCodes['value']; }
/** * Convert a date/time string to Excel time * * @param string $dateValue Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10' * @return float|false Excel date/time serial value */ public static function stringToExcel($dateValue = '') { // restrict to dates and times like these because date_parse accepts too many strings // '2009-12-31' // '2009-12-31 15:59' // '2009-12-31 15:59:10' if (!preg_match('/^\\d{4}\\-\\d{1,2}\\-\\d{1,2}( \\d{1,2}:\\d{1,2}(:\\d{1,2})?)?$/', $dateValue)) { return false; } // now try with date_parse $PHPDateArray = date_parse($dateValue); if ($PHPDateArray['error_count'] == 0) { $year = $PHPDateArray['year'] !== false ? $PHPDateArray['year'] : self::getExcelCalendar(); $month = $PHPDateArray['month'] !== false ? $PHPDateArray['month'] : 1; $day = $PHPDateArray['day'] !== false ? $PHPDateArray['day'] : 0; $hour = $PHPDateArray['hour'] !== false ? $PHPDateArray['hour'] : 0; $minute = $PHPDateArray['minute'] !== false ? $PHPDateArray['minute'] : 0; $second = $PHPDateArray['second'] !== false ? $PHPDateArray['second'] : 0; $excelDateValue = Shared_Date::FormattedPHPToExcel($year, $month, $day, $hour, $minute, $second); return $excelDateValue; } return false; }
/** * Loads PHPExcel from file into PHPExcel instance * * @param string $pFilename * @param PHPExcel $objPHPExcel * @return PHPExcel * @throws Exception */ public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel) { // Check if file exists if (!file_exists($pFilename)) { throw new Exception("Could not open " . $pFilename . " for reading! File does not exist."); } $zip = new ZipArchive(); if ($zip->open($pFilename) === true) { // echo '<h1>Meta Information</h1>'; $xml = simplexml_load_string($zip->getFromName("meta.xml")); $namespacesMeta = $xml->getNamespaces(true); // echo '<pre>'; // print_r($namespacesMeta); // echo '</pre><hr />'; $docProps = $objPHPExcel->getProperties(); $officeProperty = $xml->children($namespacesMeta['office']); foreach ($officeProperty as $officePropertyData) { $officePropertyDC = array(); if (isset($namespacesMeta['dc'])) { $officePropertyDC = $officePropertyData->children($namespacesMeta['dc']); } foreach ($officePropertyDC as $propertyName => $propertyValue) { // echo $propertyName.' = '.$propertyValue.'<hr />'; switch ($propertyName) { case 'title': $docProps->setTitle($propertyValue); break; case 'subject': $docProps->setSubject($propertyValue); break; case 'creator': $docProps->setCreator($propertyValue); break; case 'date': $creationDate = strtotime($propertyValue); $docProps->setCreated($creationDate); break; case 'description': $docProps->setDescription($propertyValue); break; } } $officePropertyMeta = array(); if (isset($namespacesMeta['dc'])) { $officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']); } foreach ($officePropertyMeta as $propertyName => $propertyValue) { $propertyValueAttributes = $propertyValue->attributes($namespacesMeta['meta']); // echo $propertyName.' = '.$propertyValue.'<br />'; // foreach ($propertyValueAttributes as $key => $value) { // echo $key.' = '.$value.'<br />'; // } // echo '<hr />'; // switch ($propertyName) { case 'keyword': $docProps->setKeywords($propertyValue); break; } } } // echo '<h1>Workbook Content</h1>'; $xml = simplexml_load_string($zip->getFromName("content.xml")); $namespacesContent = $xml->getNamespaces(true); // echo '<pre>'; // print_r($namespacesContent); // echo '</pre><hr />'; $workbook = $xml->children($namespacesContent['office']); foreach ($workbook->body->spreadsheet as $workbookData) { $workbookData = $workbookData->children($namespacesContent['table']); $worksheetID = 0; foreach ($workbookData->table as $worksheetDataSet) { $worksheetData = $worksheetDataSet->children($namespacesContent['table']); // print_r($worksheetData); // echo '<br />'; $worksheetDataAttributes = $worksheetDataSet->attributes($namespacesContent['table']); // print_r($worksheetDataAttributes); // echo '<br />'; if (isset($this->_loadSheetsOnly) && isset($worksheetDataAttributes['name']) && !in_array($worksheetDataAttributes['name'], $this->_loadSheetsOnly)) { continue; } // echo '<h2>Worksheet '.$worksheetDataAttributes['name'].'</h2>'; // Create new Worksheet $objPHPExcel->createSheet(); $objPHPExcel->setActiveSheetIndex($worksheetID); if (isset($worksheetDataAttributes['name'])) { $worksheetName = (string) $worksheetDataAttributes['name']; $objPHPExcel->getActiveSheet()->setTitle($worksheetName); } $rowID = 1; foreach ($worksheetData as $key => $rowData) { // echo '<b>'.$key.'</b><br />'; switch ($key) { case 'table-row': $columnID = 'A'; foreach ($rowData as $key => $cellData) { // echo '<b>'.$columnID.$rowID.'</b><br />'; $cellDataText = $cellData->children($namespacesContent['text']); $cellDataOfficeAttributes = $cellData->attributes($namespacesContent['office']); $cellDataTableAttributes = $cellData->attributes($namespacesContent['table']); // echo 'Office Attributes: '; // print_r($cellDataOfficeAttributes); // echo '<br />Table Attributes: '; // print_r($cellDataTableAttributes); // echo '<br />Cell Data Text'; // print_r($cellDataText); // echo '<br />'; // $type = $formatting = $hyperlink = null; $hasCalculatedValue = false; $cellDataFormula = ''; if (isset($cellDataTableAttributes['formula'])) { $cellDataFormula = $cellDataTableAttributes['formula']; $hasCalculatedValue = true; } if (isset($cellDataText->p)) { // echo 'Value Type is '.$cellDataOfficeAttributes['value-type'].'<br />'; switch ($cellDataOfficeAttributes['value-type']) { case 'string': $type = Cell_DataType::TYPE_STRING; $dataValue = $cellDataText->p; if (isset($dataValue->a)) { $dataValue = $dataValue->a; $cellXLinkAttributes = $dataValue->attributes($namespacesContent['xlink']); $hyperlink = $cellXLinkAttributes['href']; } break; case 'boolean': $type = Cell_DataType::TYPE_BOOL; $dataValue = $cellDataText->p == 'TRUE' ? True : False; break; case 'float': $type = Cell_DataType::TYPE_NUMERIC; $dataValue = (double) $cellDataOfficeAttributes['value']; if (floor($dataValue) == $dataValue) { $dataValue = (int) $dataValue; } break; case 'date': $type = Cell_DataType::TYPE_NUMERIC; $dateObj = date_create($cellDataOfficeAttributes['date-value']); list($year, $month, $day, $hour, $minute, $second) = explode(' ', $dateObj->format('Y m d H i s')); $dataValue = Shared_Date::FormattedPHPToExcel($year, $month, $day, $hour, $minute, $second); if ($dataValue != floor($dataValue)) { $formatting = Style_NumberFormat::FORMAT_DATE_XLSX15 . ' ' . Style_NumberFormat::FORMAT_DATE_TIME4; } else { $formatting = Style_NumberFormat::FORMAT_DATE_XLSX15; } break; case 'time': $type = Cell_DataType::TYPE_NUMERIC; $dataValue = Shared_Date::PHPToExcel(strtotime('01-01-1970 ' . implode(':', sscanf($cellDataOfficeAttributes['time-value'], 'PT%dH%dM%dS')))); $formatting = Style_NumberFormat::FORMAT_DATE_TIME4; break; } // echo 'Data value is '.$dataValue.'<br />'; // if (!is_null($hyperlink)) { // echo 'Hyperlink is '.$hyperlink.'<br />'; // } } if ($hasCalculatedValue) { $type = Cell_DataType::TYPE_FORMULA; // echo 'Formula: '.$cellDataFormula.'<br />'; $cellDataFormula = substr($cellDataFormula, strpos($cellDataFormula, ':=') + 1); $temp = explode('"', $cellDataFormula); foreach ($temp as $key => &$value) { // Only replace in alternate array entries (i.e. non-quoted blocks) if ($key % 2 == 0) { $value = preg_replace('/\\[\\.(.*):\\.(.*)\\]/Ui', '$1:$2', $value); $value = preg_replace('/\\[\\.(.*)\\]/Ui', '$1', $value); $value = Calculation::_translateSeparator(';', ',', $value); } } unset($value); // Then rebuild the formula string $cellDataFormula = implode('"', $temp); // echo 'Adjusted Formula: '.$cellDataFormula.'<br />'; } if (!is_null($type)) { $objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit($hasCalculatedValue ? $cellDataFormula : $dataValue, $type); if ($hasCalculatedValue) { // echo 'Forumla result is '.$dataValue.'<br />'; $objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($dataValue); } if ($cellDataOfficeAttributes['value-type'] == 'date' || $cellDataOfficeAttributes['value-type'] == 'time') { $objPHPExcel->getActiveSheet()->getStyle($columnID . $rowID)->getNumberFormat()->setFormatCode($formatting); } if (!is_null($hyperlink)) { $objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->getHyperlink()->setUrl($hyperlink); } } // Merged cells if (isset($cellDataTableAttributes['number-columns-spanned']) || isset($cellDataTableAttributes['number-rows-spanned'])) { $columnTo = $columnID; if (isset($cellDataTableAttributes['number-columns-spanned'])) { $columnTo = Cell::stringFromColumnIndex(Cell::columnIndexFromString($columnID) + $cellDataTableAttributes['number-columns-spanned'] - 2); } $rowTo = $rowID; if (isset($cellDataTableAttributes['number-rows-spanned'])) { $rowTo = $rowTo + $cellDataTableAttributes['number-rows-spanned'] - 1; } $cellRange = $columnID . $rowID . ':' . $columnTo . $rowTo; $objPHPExcel->getActiveSheet()->mergeCells($cellRange); } if (isset($cellDataTableAttributes['number-columns-repeated'])) { // echo 'Repeated '.$cellDataTableAttributes['number-columns-repeated'].' times<br />'; $columnID = Cell::stringFromColumnIndex(Cell::columnIndexFromString($columnID) + $cellDataTableAttributes['number-columns-repeated'] - 2); } ++$columnID; } ++$rowID; break; } } ++$worksheetID; } } } // Return return $objPHPExcel; }