/** * 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 = self::flattenSingleValue($timeValue); if (($PHPDateArray = date_parse($timeValue)) !== False && $PHPDateArray['error_count'] == 0) { if (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE) { $excelDateValue = PHPExcel_Shared_Date::FormattedPHPToExcel($PHPDateArray['year'], $PHPDateArray['month'], $PHPDateArray['day'], $PHPDateArray['hour'], $PHPDateArray['minute'], $PHPDateArray['second']); } else { $excelDateValue = PHPExcel_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 = PHPExcel_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']; }
/** * Loads PHPExcel from file into PHPExcel instance * * @param string $pFilename * @param PHPExcel $objPHPExcel * @return PHPExcel * @throws PHPExcel_Reader_Exception */ public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel) { // Check if file exists if (!file_exists($pFilename)) { throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist."); } $timezoneObj = new DateTimeZone('Europe/London'); $GMT = new DateTimeZone('UTC'); $zipClass = PHPExcel_Settings::getZipClass(); $zip = new $zipClass(); if (!$zip->open($pFilename)) { throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! Error opening file."); } // echo '<h1>Meta Information</h1>'; $xml = simplexml_load_string($zip->getFromName("meta.xml"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); $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) { $propertyValue = (string) $propertyValue; switch ($propertyName) { case 'title': $docProps->setTitle($propertyValue); break; case 'subject': $docProps->setSubject($propertyValue); break; case 'creator': $docProps->setCreator($propertyValue); $docProps->setLastModifiedBy($propertyValue); break; case 'date': $creationDate = strtotime($propertyValue); $docProps->setCreated($creationDate); $docProps->setModified($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']); $propertyValue = (string) $propertyValue; switch ($propertyName) { case 'initial-creator': $docProps->setCreator($propertyValue); break; case 'keyword': $docProps->setKeywords($propertyValue); break; case 'creation-date': $creationDate = strtotime($propertyValue); $docProps->setCreated($creationDate); break; case 'user-defined': $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING; foreach ($propertyValueAttributes as $key => $value) { if ($key == 'name') { $propertyValueName = (string) $value; } elseif ($key == 'value-type') { switch ($value) { case 'date': $propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'date'); $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_DATE; break; case 'boolean': $propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'bool'); $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_BOOLEAN; break; case 'float': $propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'r4'); $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_FLOAT; break; default: $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING; } } } $docProps->setCustomProperty($propertyValueName, $propertyValue, $propertyValueType); break; } } } // echo '<h1>Workbook Content</h1>'; $xml = simplexml_load_string($zip->getFromName("content.xml"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); $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']; // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in // formula cells... during the load, all formulae should be correct, and we're simply // bringing the worksheet name in line with the formula, not the reverse $objPHPExcel->getActiveSheet()->setTitle($worksheetName, false); } $rowID = 1; foreach ($worksheetData as $key => $rowData) { // echo '<b>'.$key.'</b><br />'; switch ($key) { case 'table-header-rows': foreach ($rowData as $key => $cellData) { $rowData = $cellData; break; } case 'table-row': $rowDataTableAttributes = $rowData->attributes($namespacesContent['table']); $rowRepeats = isset($rowDataTableAttributes['number-rows-repeated']) ? $rowDataTableAttributes['number-rows-repeated'] : 1; $columnID = 'A'; foreach ($rowData as $key => $cellData) { if ($this->getReadFilter() !== NULL) { if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) { continue; } } // echo '<b>'.$columnID.$rowID.'</b><br />'; $cellDataText = isset($namespacesContent['text']) ? $cellData->children($namespacesContent['text']) : ''; $cellDataOffice = $cellData->children($namespacesContent['office']); $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($cellDataOffice->annotation)) { // echo 'Cell has comment<br />'; $annotationText = $cellDataOffice->annotation->children($namespacesContent['text']); $textArray = array(); foreach ($annotationText as $t) { foreach ($t->span as $text) { $textArray[] = (string) $text; } } $text = implode("\n", $textArray); // echo $text,'<br />'; $objPHPExcel->getActiveSheet()->getComment($columnID . $rowID)->setText($this->_parseRichText($text)); } if (isset($cellDataText->p)) { // Consolidate if there are multiple p records (maybe with spans as well) $dataArray = array(); // Text can have multiple text:p and within those, multiple text:span. // text:p newlines, but text:span does not. // Also, here we assume there is no text data is span fields are specified, since // we have no way of knowing proper positioning anyway. foreach ($cellDataText->p as $pData) { if (isset($pData->span)) { // span sections do not newline, so we just create one large string here $spanSection = ""; foreach ($pData->span as $spanData) { $spanSection .= $spanData; } array_push($dataArray, $spanSection); } else { array_push($dataArray, $pData); } } $allCellDataText = implode($dataArray, "\n"); // echo 'Value Type is '.$cellDataOfficeAttributes['value-type'].'<br />'; switch ($cellDataOfficeAttributes['value-type']) { case 'string': $type = PHPExcel_Cell_DataType::TYPE_STRING; $dataValue = $allCellDataText; if (isset($dataValue->a)) { $dataValue = $dataValue->a; $cellXLinkAttributes = $dataValue->attributes($namespacesContent['xlink']); $hyperlink = $cellXLinkAttributes['href']; } break; case 'boolean': $type = PHPExcel_Cell_DataType::TYPE_BOOL; $dataValue = $allCellDataText == 'TRUE' ? True : False; break; case 'percentage': $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; $dataValue = (double) $cellDataOfficeAttributes['value']; if (floor($dataValue) == $dataValue) { $dataValue = (int) $dataValue; } $formatting = PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00; break; case 'currency': $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; $dataValue = (double) $cellDataOfficeAttributes['value']; if (floor($dataValue) == $dataValue) { $dataValue = (int) $dataValue; } $formatting = PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE; break; case 'float': $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; $dataValue = (double) $cellDataOfficeAttributes['value']; if (floor($dataValue) == $dataValue) { if ($dataValue == (int) $dataValue) { $dataValue = (int) $dataValue; } else { $dataValue = (double) $dataValue; } } break; case 'date': $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; $dateObj = new DateTime($cellDataOfficeAttributes['date-value'], $GMT); $dateObj->setTimeZone($timezoneObj); list($year, $month, $day, $hour, $minute, $second) = explode(' ', $dateObj->format('Y m d H i s')); $dataValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hour, $minute, $second); if ($dataValue != floor($dataValue)) { $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15 . ' ' . PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4; } else { $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15; } break; case 'time': $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; $dataValue = PHPExcel_Shared_Date::PHPToExcel(strtotime('01-01-1970 ' . implode(':', sscanf($cellDataOfficeAttributes['time-value'], 'PT%dH%dM%dS')))); $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4; break; } // echo 'Data value is '.$dataValue.'<br />'; // if ($hyperlink !== NULL) { // echo 'Hyperlink is '.$hyperlink.'<br />'; // } } else { $type = PHPExcel_Cell_DataType::TYPE_NULL; $dataValue = NULL; } if ($hasCalculatedValue) { $type = PHPExcel_Cell_DataType::TYPE_FORMULA; // echo 'Formula: ', $cellDataFormula, PHP_EOL; $cellDataFormula = substr($cellDataFormula, strpos($cellDataFormula, ':=') + 1); $temp = explode('"', $cellDataFormula); $tKey = false; foreach ($temp as &$value) { // Only replace in alternate array entries (i.e. non-quoted blocks) if ($tKey = !$tKey) { $value = preg_replace('/\\[([^\\.]+)\\.([^\\.]+):\\.([^\\.]+)\\]/Ui', '$1!$2:$3', $value); // Cell range reference in another sheet $value = preg_replace('/\\[([^\\.]+)\\.([^\\.]+)\\]/Ui', '$1!$2', $value); // Cell reference in another sheet $value = preg_replace('/\\[\\.([^\\.]+):\\.([^\\.]+)\\]/Ui', '$1:$2', $value); // Cell range reference $value = preg_replace('/\\[\\.([^\\.]+)\\]/Ui', '$1', $value); // Simple cell reference $value = PHPExcel_Calculation::_translateSeparator(';', ',', $value, $inBraces); } } unset($value); // Then rebuild the formula string $cellDataFormula = implode('"', $temp); // echo 'Adjusted Formula: ', $cellDataFormula, PHP_EOL; } $colRepeats = isset($cellDataTableAttributes['number-columns-repeated']) ? $cellDataTableAttributes['number-columns-repeated'] : 1; if ($type !== NULL) { for ($i = 0; $i < $colRepeats; ++$i) { if ($i > 0) { ++$columnID; } if ($type !== PHPExcel_Cell_DataType::TYPE_NULL) { for ($rowAdjust = 0; $rowAdjust < $rowRepeats; ++$rowAdjust) { $rID = $rowID + $rowAdjust; $objPHPExcel->getActiveSheet()->getCell($columnID . $rID)->setValueExplicit($hasCalculatedValue ? $cellDataFormula : $dataValue, $type); if ($hasCalculatedValue) { // echo 'Forumla result is '.$dataValue.'<br />'; $objPHPExcel->getActiveSheet()->getCell($columnID . $rID)->setCalculatedValue($dataValue); } if ($formatting !== NULL) { $objPHPExcel->getActiveSheet()->getStyle($columnID . $rID)->getNumberFormat()->setFormatCode($formatting); } else { $objPHPExcel->getActiveSheet()->getStyle($columnID . $rID)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL); } if ($hyperlink !== NULL) { $objPHPExcel->getActiveSheet()->getCell($columnID . $rID)->getHyperlink()->setUrl($hyperlink); } } } } } // Merged cells if (isset($cellDataTableAttributes['number-columns-spanned']) || isset($cellDataTableAttributes['number-rows-spanned'])) { if ($type !== PHPExcel_Cell_DataType::TYPE_NULL || !$this->_readDataOnly) { $columnTo = $columnID; if (isset($cellDataTableAttributes['number-columns-spanned'])) { $columnTo = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_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); } } ++$columnID; } $rowID += $rowRepeats; break; } } ++$worksheetID; } } // Return return $objPHPExcel; }
/** * 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(PHPExcel_Calculation_Functions::flattenSingleValue($timeValue), '"'); $timeValue = str_replace(array('/', '.'), array('-', '-'), $timeValue); $PHPDateArray = date_parse($timeValue); if ($PHPDateArray !== False && $PHPDateArray['error_count'] == 0) { if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) { $excelDateValue = PHPExcel_Shared_Date::FormattedPHPToExcel($PHPDateArray['year'], $PHPDateArray['month'], $PHPDateArray['day'], $PHPDateArray['hour'], $PHPDateArray['minute'], $PHPDateArray['second']); } else { $excelDateValue = PHPExcel_Shared_Date::FormattedPHPToExcel(1900, 1, 1, $PHPDateArray['hour'], $PHPDateArray['minute'], $PHPDateArray['second']) - 1; } switch (PHPExcel_Calculation_Functions::getReturnDateType()) { case PHPExcel_Calculation_Functions::RETURNDATE_EXCEL: return (double) $excelDateValue; break; case PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC: return (int) ($phpDateValue = PHPExcel_Shared_Date::ExcelToPHP($excelDateValue + 25569) - 3600); break; case PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT: return new DateTime('1900-01-01 ' . $PHPDateArray['hour'] . ':' . $PHPDateArray['minute'] . ':' . $PHPDateArray['second']); break; } } return PHPExcel_Calculation_Functions::VALUE(); }
echo date('H:i:s') . ' Add data' . EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Financial Year')->setCellValue('B1', 'Financial Period')->setCellValue('C1', 'Country')->setCellValue('D1', 'Date')->setCellValue('E1', 'Sales Value')->setCellValue('F1', 'Expenditure'); $startYear = $endYear = $currentYear = date('Y'); $startYear--; $endYear++; $years = range($startYear, $endYear); $periods = range(1, 12); $countries = array('United States', 'UK', 'France', 'Germany', 'Italy', 'Spain', 'Portugal', 'Japan'); $row = 2; foreach ($years as $year) { foreach ($periods as $period) { foreach ($countries as $country) { $endDays = date('t', mktime(0, 0, 0, $period, 1, $year)); for ($i = 1; $i <= $endDays; ++$i) { $eDate = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $period, $i); $value = rand(500, 1000) * (1 + rand(-0.25, +0.25)); $salesValue = $invoiceValue = NULL; $incomeOrExpenditure = rand(-1, 1); if ($incomeOrExpenditure == -1) { $expenditure = rand(-500, -1000) * (1 + rand(-0.25, +0.25)); $income = NULL; } elseif ($incomeOrExpenditure == 1) { $expenditure = rand(-500, -1000) * (1 + rand(-0.25, +0.25)); $income = rand(500, 1000) * (1 + rand(-0.25, +0.25)); } else { $expenditure = NULL; $income = rand(500, 1000) * (1 + rand(-0.25, +0.25)); } $dataArray = array($year, $period, $country, $eDate, $income, $expenditure); $objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A' . $row++);
/** * 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."); } $timezoneObj = new DateTimeZone('Europe/London'); $GMT = new DateTimeZone('UTC'); $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) { switch ($propertyName) { case 'title': $docProps->setTitle($propertyValue); break; case 'subject': $docProps->setSubject($propertyValue); break; case 'creator': $docProps->setCreator($propertyValue); $docProps->setLastModifiedBy($propertyValue); break; case 'date': $creationDate = strtotime($propertyValue); $docProps->setCreated($creationDate); $docProps->setModified($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']); switch ($propertyName) { case 'initial-creator': $docProps->setCreator($propertyValue); break; case 'keyword': $docProps->setKeywords($propertyValue); break; case 'creation-date': $creationDate = strtotime($propertyValue); $docProps->setCreated($creationDate); break; case 'user-defined': $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING; foreach ($propertyValueAttributes as $key => $value) { if ($key == 'name') { $propertyValueName = (string) $value; } elseif ($key == 'value-type') { switch ($value) { case 'date': $propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'date'); $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_DATE; break; case 'boolean': $propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'bool'); $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_BOOLEAN; break; case 'float': $propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'r4'); $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_FLOAT; break; default: $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING; } } } $docProps->setCustomProperty($propertyValueName, $propertyValue, $propertyValueType); 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-header-rows': foreach ($rowData as $key => $cellData) { $rowData = $cellData; break; } case 'table-row': $columnID = 'A'; foreach ($rowData as $key => $cellData) { if (!is_null($this->getReadFilter())) { if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) { continue; } } // 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 = PHPExcel_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 = PHPExcel_Cell_DataType::TYPE_BOOL; $dataValue = $cellDataText->p == 'TRUE' ? True : False; break; case 'float': $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; $dataValue = (double) $cellDataOfficeAttributes['value']; if (floor($dataValue) == $dataValue) { $dataValue = (int) $dataValue; } break; case 'date': $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; $dateObj = new DateTime($cellDataOfficeAttributes['date-value'], $GMT); $dateObj->setTimeZone($timezoneObj); list($year, $month, $day, $hour, $minute, $second) = explode(' ', $dateObj->format('Y m d H i s')); $dataValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hour, $minute, $second); if ($dataValue != floor($dataValue)) { $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15 . ' ' . PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4; } else { $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15; } break; case 'time': $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; $dataValue = PHPExcel_Shared_Date::PHPToExcel(strtotime('01-01-1970 ' . implode(':', sscanf($cellDataOfficeAttributes['time-value'], 'PT%dH%dM%dS')))); $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4; break; } // echo 'Data value is '.$dataValue.'<br />'; // if (!is_null($hyperlink)) { // echo 'Hyperlink is '.$hyperlink.'<br />'; // } } if ($hasCalculatedValue) { $type = PHPExcel_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 = PHPExcel_Calculation::_translateSeparator(';', ',', $value, $inBraces); } } 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 = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_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 = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cellDataTableAttributes['number-columns-repeated'] - 2); } ++$columnID; } ++$rowID; break; } } ++$worksheetID; } } } // Return return $objPHPExcel; }
/** * Write one date somewhere in the worksheet. * @param integer $row Zero indexed row * @param integer $col Zero indexed column * @param string $date The date to write in UNIX timestamp format * @param mixed $format The XF format for the cell */ public function write_date($row, $col, $date, $format = null) { $getdate = usergetdate($date); $exceldate = PHPExcel_Shared_Date::FormattedPHPToExcel($getdate['year'], $getdate['mon'], $getdate['mday'], $getdate['hours'], $getdate['minutes'], $getdate['seconds']); $this->worksheet->setCellValueByColumnAndRow($col, $row + 1, $exceldate); $this->worksheet->getStyleByColumnAndRow($col, $row + 1)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX22); $this->apply_format($row, $col, $format); }
/** * 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 = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hour, $minute, $second); return $excelDateValue; } return false; }
public function export($competition, $exportFormsts, $all = false, $xlsx = false, $extra = false, $order = 'date') { $registrations = $this->getRegistrations($competition, $all, $order); $template = PHPExcel_IOFactory::load(Yii::getPathOfAlias('application.data.results') . '.xls'); $export = new PHPExcel(); $export->getProperties()->setCreator(Yii::app()->params->author)->setLastModifiedBy(Yii::app()->params->author)->setTitle($competition->wca_competition_id ?: $competition->name)->setSubject($competition->name); $export->removeSheetByIndex(0); //注册页 $sheet = $template->getSheet(0); $sheet->setCellValue('A1', $competition->wca_competition_id ?: $competition->name); $events = $competition->getRegistrationEvents(); $col = 'J'; $cubecompsEvents = array('333' => '3x3', '444' => '4x4', '555' => '5x5', '666' => '6x6', '777' => '7x7', '222' => '2x2', '333bf' => '333bld', '333fm' => 'fmc', 'minx' => 'mega', 'pyram' => 'pyra', '444bf' => '444bld', '555bf' => '555bld', '333mbf' => '333mlt'); foreach ($events as $event => $data) { $sheet->setCellValue($col . 2, "=SUM({$col}4:{$col}" . (count($registrations) + 4) . ')'); $sheet->setCellValue($col . 3, isset($cubecompsEvents[$event]) ? $cubecompsEvents[$event] : $event); $sheet->getColumnDimension($col)->setWidth(5.5); $col++; } foreach ($registrations as $key => $registration) { $user = $registration->user; $row = $key + 4; $sheet->setCellValue('A' . $row, $registration->number)->setCellValue('B' . $row, $user->getCompetitionName())->setCellValue('C' . $row, $user->country->name)->setCellValue('D' . $row, $user->wcaid)->setCellValue('E' . $row, $user->getWcaGender())->setCellValue('F' . $row, PHPExcel_Shared_Date::FormattedPHPToExcel(date('Y', $user->birthday), date('m', $user->birthday), date('d', $user->birthday))); $col = 'J'; foreach ($events as $event => $data) { if (in_array("{$event}", $registration->events)) { $sheet->setCellValue($col . $row, 1); } $col++; } if ($extra) { $col++; $fee = $registration->getTotalFee(); if ($registration->isPaid()) { $fee .= Yii::t('common', ' (paid)'); } $sheet->setCellValue($col . $row, $fee); $col++; $sheet->setCellValue($col . $row, $user->mobile); $col++; $sheet->setCellValue($col . $row, $user->email); $col++; $sheet->setCellValue($col . $row, $registration->comments); } if (!$registration->isAccepted()) { $sheet->getStyle("A{$row}:D{$row}")->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFF00')))); } } $export->addExternalSheet($sheet); //各个项目 foreach ($exportFormsts as $event => $rounds) { $count = count($rounds); foreach ($rounds as $round => $format) { if ($round == $count - 1) { $round = 'f'; } else { $round++; } $sheet = $template->getSheetByName($format); if ($sheet === null) { continue; } $sheet = clone $sheet; $sheet->setTitle("{$event}-{$round}"); $template->addSheet($sheet); $sheet->setCellValue('A1', Events::getFullEventName($event) . ' - ' . Rounds::getFullRoundName($round)); if ($round == 1 || $count == 1) { $row = 5; foreach ($registrations as $registration) { if (!in_array("{$event}", $registration->events)) { continue; } $user = $registration->user; $sheet->setCellValue('B' . $row, $user->getCompetitionName())->setCellValue('C' . $row, $user->country->name)->setCellValue('D' . $row, $user->wcaid); if ($row > 5) { $formula = $sheet->getCell('A' . ($row - 1))->getValue(); $formula = strtr($formula, array('-4' => '_temp_', $row - 1 => $row, $row - 2 => $row - 1, $row => $row + 1)); $formula = str_replace('_temp_', '-4', $formula); $sheet->setCellValue('A' . $row, $formula); } if (!$registration->isAccepted()) { $sheet->getStyle("A{$row}:D{$row}")->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFF00')))); } $row++; } } $export->addExternalSheet($sheet); } } $this->exportToExcel($export, 'php://output', $competition->name, $xlsx); }
function FormatDate($date, $format_id = false) { $date = $this->_ensureTimeStamp($date); return PHPExcel_Shared_Date::FormattedPHPToExcel(date("Y", $date), date("m", $date), date("d", $date)); }
/** * TIMEVALUE * * @param string $timeValue * @return long Excel time serial value */ public static function TIMEVALUE($timeValue) { $timeValue = self::flattenSingleValue($timeValue); if (($PHPDateArray = date_parse($timeValue)) !== False && $PHPDateArray['error_count'] == 0) { if (self::$compatibilityMode == self::COMPATIBILITY_OPENOFFICE) { return PHPExcel_Shared_Date::FormattedPHPToExcel($PHPDateArray['year'], $PHPDateArray['month'], $PHPDateArray['day'], $PHPDateArray['hour'], $PHPDateArray['minute'], $PHPDateArray['second']); } else { return PHPExcel_Shared_Date::FormattedPHPToExcel(1900, 1, 1, $PHPDateArray['hour'], $PHPDateArray['minute'], $PHPDateArray['second']) - 1; } } return self::$_errorCodes['value']; }