private static function couponFirstPeriodDate($settlement, $maturity, $frequency, $next)
{
$months = 12 / $frequency;
$result = \PHPExcel\Shared\Date::excelToPHPObject($maturity);
$eom = self::isLastDayOfMonth($result);
while ($settlement < \PHPExcel\Shared\Date::PHPToExcel($result)) {
$result->modify('-' . $months . ' months');
}
if ($next) {
$result->modify('+' . $months . ' months');
}
if ($eom) {
$result->modify('-1 day');
}
return \PHPExcel\Shared\Date::PHPToExcel($result);
}
/**
* Write WorkbookPr
*
* @param \PHPExcel\Shared\XMLWriter $objWriter XML Writer
* @throws \PHPExcel\Writer\Exception
*/
private function writeWorkbookPr(\PHPExcel\Shared\XMLWriter $objWriter)
{
$objWriter->startElement('workbookPr');
if (\PHPExcel\Shared\Date::getExcelCalendar() == \PHPExcel\Shared\Date::CALENDAR_MAC_1904) {
$objWriter->writeAttribute('date1904', '1');
}
$objWriter->writeAttribute('codeName', 'ThisWorkbook');
$objWriter->endElement();
}
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)
{
$fromFormats = array('\\-', '\\ ');
$toFormats = array('-', ' ');
$underlineStyles = array(\PHPExcel\Style\Font::UNDERLINE_NONE, \PHPExcel\Style\Font::UNDERLINE_DOUBLE, \PHPExcel\Style\Font::UNDERLINE_DOUBLEACCOUNTING, \PHPExcel\Style\Font::UNDERLINE_SINGLE, \PHPExcel\Style\Font::UNDERLINE_SINGLEACCOUNTING);
$verticalAlignmentStyles = array(\PHPExcel\Style\Alignment::VERTICAL_BOTTOM, \PHPExcel\Style\Alignment::VERTICAL_TOP, \PHPExcel\Style\Alignment::VERTICAL_CENTER, \PHPExcel\Style\Alignment::VERTICAL_JUSTIFY);
$horizontalAlignmentStyles = array(\PHPExcel\Style\Alignment::HORIZONTAL_GENERAL, \PHPExcel\Style\Alignment::HORIZONTAL_LEFT, \PHPExcel\Style\Alignment::HORIZONTAL_RIGHT, \PHPExcel\Style\Alignment::HORIZONTAL_CENTER, \PHPExcel\Style\Alignment::HORIZONTAL_CENTER_CONTINUOUS, \PHPExcel\Style\Alignment::HORIZONTAL_JUSTIFY);
$timezoneObj = new \DateTimeZone('Europe/London');
$GMT = new \DateTimeZone('UTC');
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
if (!$this->canRead($pFilename)) {
throw new Exception($pFilename . " is an Invalid Spreadsheet file.");
}
$xml = simplexml_load_string($this->securityScan(file_get_contents($pFilename)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
$namespaces = $xml->getNamespaces(true);
$docProps = $objPHPExcel->getProperties();
if (isset($xml->DocumentProperties[0])) {
foreach ($xml->DocumentProperties[0] as $propertyName => $propertyValue) {
switch ($propertyName) {
case 'Title':
$docProps->setTitle(self::convertStringEncoding($propertyValue, $this->charSet));
break;
case 'Subject':
$docProps->setSubject(self::convertStringEncoding($propertyValue, $this->charSet));
break;
case 'Author':
$docProps->setCreator(self::convertStringEncoding($propertyValue, $this->charSet));
break;
case 'Created':
$creationDate = strtotime($propertyValue);
$docProps->setCreated($creationDate);
break;
case 'LastAuthor':
$docProps->setLastModifiedBy(self::convertStringEncoding($propertyValue, $this->charSet));
break;
case 'LastSaved':
$lastSaveDate = strtotime($propertyValue);
$docProps->setModified($lastSaveDate);
break;
case 'Company':
$docProps->setCompany(self::convertStringEncoding($propertyValue, $this->charSet));
break;
case 'Category':
$docProps->setCategory(self::convertStringEncoding($propertyValue, $this->charSet));
break;
case 'Manager':
$docProps->setManager(self::convertStringEncoding($propertyValue, $this->charSet));
break;
case 'Keywords':
$docProps->setKeywords(self::convertStringEncoding($propertyValue, $this->charSet));
break;
case 'Description':
$docProps->setDescription(self::convertStringEncoding($propertyValue, $this->charSet));
break;
}
}
}
if (isset($xml->CustomDocumentProperties)) {
foreach ($xml->CustomDocumentProperties[0] as $propertyName => $propertyValue) {
$propertyAttributes = $propertyValue->attributes($namespaces['dt']);
$propertyName = preg_replace_callback('/_x([0-9a-z]{4})_/', '\\PHPExcel\\Reader\\Excel2003XML::hex2str', $propertyName);
$propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_UNKNOWN;
switch ((string) $propertyAttributes) {
case 'string':
$propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_STRING;
$propertyValue = trim($propertyValue);
break;
case 'boolean':
$propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_BOOLEAN;
$propertyValue = (bool) $propertyValue;
break;
case 'integer':
$propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_INTEGER;
$propertyValue = intval($propertyValue);
break;
case 'float':
$propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_FLOAT;
$propertyValue = floatval($propertyValue);
break;
case 'dateTime.tz':
$propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_DATE;
$propertyValue = strtotime(trim($propertyValue));
break;
}
$docProps->setCustomProperty($propertyName, $propertyValue, $propertyType);
}
}
foreach ($xml->Styles[0] as $style) {
$style_ss = $style->attributes($namespaces['ss']);
$styleID = (string) $style_ss['ID'];
// echo 'Style ID = '.$styleID.'<br />';
$this->styles[$styleID] = isset($this->styles['Default']) ? $this->styles['Default'] : array();
foreach ($style as $styleType => $styleData) {
$styleAttributes = $styleData->attributes($namespaces['ss']);
// echo $styleType.'<br />';
switch ($styleType) {
case 'Alignment':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
$styleAttributeValue = (string) $styleAttributeValue;
switch ($styleAttributeKey) {
case 'Vertical':
if (self::identifyFixedStyleValue($verticalAlignmentStyles, $styleAttributeValue)) {
$this->styles[$styleID]['alignment']['vertical'] = $styleAttributeValue;
}
break;
case 'Horizontal':
if (self::identifyFixedStyleValue($horizontalAlignmentStyles, $styleAttributeValue)) {
$this->styles[$styleID]['alignment']['horizontal'] = $styleAttributeValue;
}
break;
case 'WrapText':
$this->styles[$styleID]['alignment']['wrap'] = true;
break;
}
}
break;
case 'Borders':
foreach ($styleData->Border as $borderStyle) {
$borderAttributes = $borderStyle->attributes($namespaces['ss']);
$thisBorder = array();
foreach ($borderAttributes as $borderStyleKey => $borderStyleValue) {
// echo $borderStyleKey.' = '.$borderStyleValue.'<br />';
switch ($borderStyleKey) {
case 'LineStyle':
$thisBorder['style'] = \PHPExcel\Style\Border::BORDER_MEDIUM;
// $thisBorder['style'] = $borderStyleValue;
break;
case 'Weight':
// $thisBorder['style'] = $borderStyleValue;
break;
case 'Position':
$borderPosition = strtolower($borderStyleValue);
break;
case 'Color':
$borderColour = substr($borderStyleValue, 1);
$thisBorder['color']['rgb'] = $borderColour;
break;
}
}
if (!empty($thisBorder)) {
if ($borderPosition == 'left' || $borderPosition == 'right' || $borderPosition == 'top' || $borderPosition == 'bottom') {
$this->styles[$styleID]['borders'][$borderPosition] = $thisBorder;
}
}
}
break;
case 'Font':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
$styleAttributeValue = (string) $styleAttributeValue;
switch ($styleAttributeKey) {
case 'FontName':
$this->styles[$styleID]['font']['name'] = $styleAttributeValue;
break;
case 'Size':
$this->styles[$styleID]['font']['size'] = $styleAttributeValue;
break;
case 'Color':
$this->styles[$styleID]['font']['color']['rgb'] = substr($styleAttributeValue, 1);
break;
case 'Bold':
$this->styles[$styleID]['font']['bold'] = true;
break;
case 'Italic':
$this->styles[$styleID]['font']['italic'] = true;
break;
case 'Underline':
if (self::identifyFixedStyleValue($underlineStyles, $styleAttributeValue)) {
$this->styles[$styleID]['font']['underline'] = $styleAttributeValue;
}
break;
}
}
break;
case 'Interior':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
switch ($styleAttributeKey) {
case 'Color':
$this->styles[$styleID]['fill']['color']['rgb'] = substr($styleAttributeValue, 1);
break;
}
}
break;
case 'NumberFormat':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
$styleAttributeValue = str_replace($fromFormats, $toFormats, $styleAttributeValue);
switch ($styleAttributeValue) {
case 'Short Date':
$styleAttributeValue = 'dd/mm/yyyy';
break;
}
if ($styleAttributeValue > '') {
$this->styles[$styleID]['numberformat']['code'] = $styleAttributeValue;
}
}
break;
case 'Protection':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
}
break;
}
}
// print_r($this->styles[$styleID]);
// echo '<hr />';
}
// echo '<hr />';
$worksheetID = 0;
$xml_ss = $xml->children($namespaces['ss']);
foreach ($xml_ss->Worksheet as $worksheet) {
$worksheet_ss = $worksheet->attributes($namespaces['ss']);
if (isset($this->loadSheetsOnly) && isset($worksheet_ss['Name']) && !in_array($worksheet_ss['Name'], $this->loadSheetsOnly)) {
continue;
}
// echo '<h3>Worksheet: ', $worksheet_ss['Name'],'<h3>';
//
// Create new Worksheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($worksheetID);
if (isset($worksheet_ss['Name'])) {
$worksheetName = self::convertStringEncoding((string) $worksheet_ss['Name'], $this->charSet);
// 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);
}
$columnID = 'A';
if (isset($worksheet->Table->Column)) {
foreach ($worksheet->Table->Column as $columnData) {
$columnData_ss = $columnData->attributes($namespaces['ss']);
if (isset($columnData_ss['Index'])) {
$columnID = \PHPExcel\Cell::stringFromColumnIndex($columnData_ss['Index'] - 1);
}
if (isset($columnData_ss['Width'])) {
$columnWidth = $columnData_ss['Width'];
// echo '<b>Setting column width for '.$columnID.' to '.$columnWidth.'</b><br />';
$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4);
}
++$columnID;
}
}
$rowID = 1;
if (isset($worksheet->Table->Row)) {
$additionalMergedCells = 0;
foreach ($worksheet->Table->Row as $rowData) {
$rowHasData = false;
$row_ss = $rowData->attributes($namespaces['ss']);
if (isset($row_ss['Index'])) {
$rowID = (int) $row_ss['Index'];
}
// echo '<b>Row '.$rowID.'</b><br />';
$columnID = 'A';
foreach ($rowData->Cell as $cell) {
$cell_ss = $cell->attributes($namespaces['ss']);
if (isset($cell_ss['Index'])) {
$columnID = \PHPExcel\Cell::stringFromColumnIndex($cell_ss['Index'] - 1);
}
$cellRange = $columnID . $rowID;
if ($this->getReadFilter() !== null) {
if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
continue;
}
}
if (isset($cell_ss['MergeAcross']) || isset($cell_ss['MergeDown'])) {
$columnTo = $columnID;
if (isset($cell_ss['MergeAcross'])) {
$additionalMergedCells += (int) $cell_ss['MergeAcross'];
$columnTo = \PHPExcel\Cell::stringFromColumnIndex(\PHPExcel\Cell::columnIndexFromString($columnID) + $cell_ss['MergeAcross'] - 1);
}
$rowTo = $rowID;
if (isset($cell_ss['MergeDown'])) {
$rowTo = $rowTo + $cell_ss['MergeDown'];
}
$cellRange .= ':' . $columnTo . $rowTo;
$objPHPExcel->getActiveSheet()->mergeCells($cellRange);
}
$cellIsSet = $hasCalculatedValue = false;
$cellDataFormula = '';
if (isset($cell_ss['Formula'])) {
$cellDataFormula = $cell_ss['Formula'];
// added this as a check for array formulas
if (isset($cell_ss['ArrayRange'])) {
$cellDataCSEFormula = $cell_ss['ArrayRange'];
// echo "found an array formula at ".$columnID.$rowID."<br />";
}
$hasCalculatedValue = true;
}
if (isset($cell->Data)) {
$cellValue = $cellData = $cell->Data;
$type = \PHPExcel\Cell_DataType::TYPE_NULL;
$cellData_ss = $cellData->attributes($namespaces['ss']);
if (isset($cellData_ss['Type'])) {
$cellDataType = $cellData_ss['Type'];
switch ($cellDataType) {
/*
const TYPE_STRING = 's';
const TYPE_FORMULA = 'f';
const TYPE_NUMERIC = 'n';
const TYPE_BOOL = 'b';
const TYPE_NULL = 'null';
const TYPE_INLINE = 'inlineStr';
const TYPE_ERROR = 'e';
*/
case 'String':
$cellValue = self::convertStringEncoding($cellValue, $this->charSet);
$type = \PHPExcel\Cell\DataType::TYPE_STRING;
break;
case 'Number':
$type = \PHPExcel\Cell\DataType::TYPE_NUMERIC;
$cellValue = (double) $cellValue;
if (floor($cellValue) == $cellValue) {
$cellValue = (int) $cellValue;
}
break;
case 'Boolean':
$type = \PHPExcel\Cell\DataType::TYPE_BOOL;
$cellValue = $cellValue != 0;
break;
case 'DateTime':
$type = \PHPExcel\Cell\DataType::TYPE_NUMERIC;
$cellValue = \PHPExcel\Shared\Date::PHPToExcel(strtotime($cellValue));
break;
case 'Error':
$type = \PHPExcel\Cell\DataType::TYPE_ERROR;
break;
}
}
if ($hasCalculatedValue) {
// echo 'FORMULA<br />';
$type = \PHPExcel\Cell\DataType::TYPE_FORMULA;
$columnNumber = \PHPExcel\Cell::columnIndexFromString($columnID);
if (substr($cellDataFormula, 0, 3) == 'of:') {
$cellDataFormula = substr($cellDataFormula, 3);
// echo 'Before: ', $cellDataFormula,'<br />';
$temp = explode('"', $cellDataFormula);
$key = false;
foreach ($temp as &$value) {
// Only replace in alternate array entries (i.e. non-quoted blocks)
if ($key = !$key) {
$value = str_replace(array('[.', '.', ']'), '', $value);
}
}
} else {
// Convert R1C1 style references to A1 style references (but only when not quoted)
// echo 'Before: ', $cellDataFormula,'<br />';
$temp = explode('"', $cellDataFormula);
$key = false;
foreach ($temp as &$value) {
// Only replace in alternate array entries (i.e. non-quoted blocks)
if ($key = !$key) {
preg_match_all('/(R(\\[?-?\\d*\\]?))(C(\\[?-?\\d*\\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
// Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
// through the formula from left to right. Reversing means that we work right to left.through
// the formula
$cellReferences = array_reverse($cellReferences);
// Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
// then modify the formula to use that new reference
foreach ($cellReferences as $cellReference) {
$rowReference = $cellReference[2][0];
// Empty R reference is the current row
if ($rowReference == '') {
$rowReference = $rowID;
}
// Bracketed R references are relative to the current row
if ($rowReference[0] == '[') {
$rowReference = $rowID + trim($rowReference, '[]');
}
$columnReference = $cellReference[4][0];
// Empty C reference is the current column
if ($columnReference == '') {
$columnReference = $columnNumber;
}
// Bracketed C references are relative to the current column
if ($columnReference[0] == '[') {
$columnReference = $columnNumber + trim($columnReference, '[]');
}
$A1CellReference = \PHPExcel\Cell::stringFromColumnIndex($columnReference - 1) . $rowReference;
$value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
}
}
}
}
unset($value);
// Then rebuild the formula string
$cellDataFormula = implode('"', $temp);
// echo 'After: ', $cellDataFormula,'<br />';
}
// echo 'Cell '.$columnID.$rowID.' is a '.$type.' with a value of '.(($hasCalculatedValue) ? $cellDataFormula : $cellValue).'<br />';
//
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit($hasCalculatedValue ? $cellDataFormula : $cellValue, $type);
if ($hasCalculatedValue) {
// echo 'Formula result is '.$cellValue.'<br />';
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue);
}
$cellIsSet = $rowHasData = true;
}
if (isset($cell->Comment)) {
// echo '<b>comment found</b><br />';
$commentAttributes = $cell->Comment->attributes($namespaces['ss']);
$author = 'unknown';
if (isset($commentAttributes->Author)) {
$author = (string) $commentAttributes->Author;
// echo 'Author: ', $author,'<br />';
}
$node = $cell->Comment->Data->asXML();
// $annotation = str_replace('html:','',substr($node,49,-10));
// echo $annotation,'<br />';
$annotation = strip_tags($node);
// echo 'Annotation: ', $annotation,'<br />';
$objPHPExcel->getActiveSheet()->getComment($columnID . $rowID)->setAuthor(self::convertStringEncoding($author, $this->charSet))->setText($this->parseRichText($annotation));
}
if ($cellIsSet && isset($cell_ss['StyleID'])) {
$style = (string) $cell_ss['StyleID'];
// echo 'Cell style for '.$columnID.$rowID.' is '.$style.'<br />';
if (isset($this->styles[$style]) && !empty($this->styles[$style])) {
// echo 'Cell '.$columnID.$rowID.'<br />';
// print_r($this->styles[$style]);
// echo '<br />';
if (!$objPHPExcel->getActiveSheet()->cellExists($columnID . $rowID)) {
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValue(null);
}
$objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->styles[$style]);
}
}
++$columnID;
while ($additionalMergedCells > 0) {
++$columnID;
$additionalMergedCells--;
}
}
if ($rowHasData) {
if (isset($row_ss['StyleID'])) {
$rowStyle = $row_ss['StyleID'];
}
if (isset($row_ss['Height'])) {
$rowHeight = $row_ss['Height'];
// echo '<b>Setting row height to '.$rowHeight.'</b><br />';
$objPHPExcel->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight);
}
}
++$rowID;
}
}
++$worksheetID;
}
// Return
return $objPHPExcel;
}
/**
* TEXTFORMAT
*
* @param mixed $value Value to check
* @param string $format Format mask to use
* @return boolean
*/
public static function TEXTFORMAT($value, $format)
{
$value = Functions::flattenSingleValue($value);
$format = Functions::flattenSingleValue($format);
if (is_string($value) && !is_numeric($value) && \PHPExcel\Shared\Date::isDateTimeFormatCode($format)) {
$value = DateTime::DATEVALUE($value);
}
return (string) \PHPExcel\Style\NumberFormat::toFormattedString($value, $format);
}
/**
* Bind value to a cell
*
* @param \PHPExcel\Cell $cell Cell to bind value to
* @param mixed $value Value to bind in cell
* @return boolean
*/
public function bindValue(\PHPExcel\Cell $cell, $value = null)
{
// sanitize UTF-8 strings
if (is_string($value)) {
$value = \PHPExcel\Shared\StringHelper::sanitizeUTF8($value);
}
// Find out data type
$dataType = parent::dataTypeForValue($value);
// Style logic - strings
if ($dataType === DataType::TYPE_STRING && !$value instanceof \PHPExcel\RichText) {
// Test for booleans using locale-setting
if ($value == \PHPExcel\Calculation::getTRUE()) {
$cell->setValueExplicit(true, DataType::TYPE_BOOL);
return true;
} elseif ($value == \PHPExcel\Calculation::getFALSE()) {
$cell->setValueExplicit(false, DataType::TYPE_BOOL);
return true;
}
// Check for number in scientific format
if (preg_match('/^' . \PHPExcel\Calculation::CALCULATION_REGEXP_NUMBER . '$/', $value)) {
$cell->setValueExplicit((double) $value, DataType::TYPE_NUMERIC);
return true;
}
// Check for fraction
if (preg_match('/^([+-]?)\\s*([0-9]+)\\s?\\/\\s*([0-9]+)$/', $value, $matches)) {
// Convert value to number
$value = $matches[2] / $matches[3];
if ($matches[1] == '-') {
$value = 0 - $value;
}
$cell->setValueExplicit((double) $value, DataType::TYPE_NUMERIC);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode('??/??');
return true;
} elseif (preg_match('/^([+-]?)([0-9]*) +([0-9]*)\\s?\\/\\s*([0-9]*)$/', $value, $matches)) {
// Convert value to number
$value = $matches[2] + $matches[3] / $matches[4];
if ($matches[1] == '-') {
$value = 0 - $value;
}
$cell->setValueExplicit((double) $value, DataType::TYPE_NUMERIC);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode('# ??/??');
return true;
}
// Check for percentage
if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) {
// Convert value to number
$value = (double) str_replace('%', '', $value) / 100;
$cell->setValueExplicit($value, DataType::TYPE_NUMERIC);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_PERCENTAGE_00);
return true;
}
// Check for currency
$currencyCode = \PHPExcel\Shared\StringHelper::getCurrencyCode();
$decimalSeparator = \PHPExcel\Shared\StringHelper::getDecimalSeparator();
$thousandsSeparator = \PHPExcel\Shared\StringHelper::getThousandsSeparator();
if (preg_match('/^' . preg_quote($currencyCode) . ' *(\\d{1,3}(' . preg_quote($thousandsSeparator) . '\\d{3})*|(\\d+))(' . preg_quote($decimalSeparator) . '\\d{2})?$/', $value)) {
// Convert value to number
$value = (double) trim(str_replace(array($currencyCode, $thousandsSeparator, $decimalSeparator), array('', '', '.'), $value));
$cell->setValueExplicit($value, DataType::TYPE_NUMERIC);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(str_replace('$', $currencyCode, \PHPExcel\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE));
return true;
} elseif (preg_match('/^\\$ *(\\d{1,3}(\\,\\d{3})*|(\\d+))(\\.\\d{2})?$/', $value)) {
// Convert value to number
$value = (double) trim(str_replace(array('$', ','), '', $value));
$cell->setValueExplicit($value, DataType::TYPE_NUMERIC);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
return true;
}
// Check for time without seconds e.g. '9:45', '09:45'
if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d$/', $value)) {
// Convert value to number
list($h, $m) = explode(':', $value);
$days = $h / 24 + $m / 1440;
$cell->setValueExplicit($days, DataType::TYPE_NUMERIC);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_DATE_TIME3);
return true;
}
// Check for time with seconds '9:45:59', '09:45:59'
if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d:[0-5]\\d$/', $value)) {
// Convert value to number
list($h, $m, $s) = explode(':', $value);
$days = $h / 24 + $m / 1440 + $s / 86400;
// Convert value to number
$cell->setValueExplicit($days, DataType::TYPE_NUMERIC);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_DATE_TIME4);
return true;
}
// Check for datetime, e.g. '2008-12-31', '2008-12-31 15:59', '2008-12-31 15:59:10'
if (($d = \PHPExcel\Shared\Date::stringToExcel($value)) !== false) {
// Convert value to number
$cell->setValueExplicit($d, DataType::TYPE_NUMERIC);
// Determine style. Either there is a time part or not. Look for ':'
if (strpos($value, ':') !== false) {
$formatCode = 'yyyy-mm-dd h:mm';
} else {
$formatCode = 'yyyy-mm-dd';
}
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode($formatCode);
return true;
}
// Check for newline character "\n"
if (strpos($value, "\n") !== false) {
$value = \PHPExcel\Shared\StringHelper::sanitizeUTF8($value);
$cell->setValueExplicit($value, DataType::TYPE_STRING);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getAlignment()->setWrapText(true);
return true;
}
}
// Not bound yet? Use parent...
return parent::bindValue($cell, $value);
}
/**
* Convert a dynamic rule daterange to a custom filter range expression for ease of calculation
*
* @param string $dynamicRuleType
* @param AutoFilter\Column &$filterColumn
* @return mixed[]
*/
private function dynamicFilterDateRange($dynamicRuleType, &$filterColumn)
{
$rDateType = \PHPExcel\Calculation\Functions::getReturnDateType();
\PHPExcel\Calculation\Functions::setReturnDateType(\PHPExcel\Calculation\Functions::RETURNDATE_PHP_NUMERIC);
$val = $maxVal = null;
$ruleValues = array();
$baseDate = \PHPExcel\Calculation\DateTime::DATENOW();
// Calculate start/end dates for the required date range based on current date
switch ($dynamicRuleType) {
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK:
$baseDate = strtotime('-7 days', $baseDate);
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK:
$baseDate = strtotime('-7 days', $baseDate);
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH:
$baseDate = strtotime('-1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH:
$baseDate = strtotime('+1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER:
$baseDate = strtotime('-3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER:
$baseDate = strtotime('+3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR:
$baseDate = strtotime('-1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR:
$baseDate = strtotime('+1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
}
switch ($dynamicRuleType) {
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_TODAY:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW:
$maxVal = (int) \PHPExcel\Shared\Date::PHPtoExcel(strtotime('+1 day', $baseDate));
$val = (int) \PHPExcel\Shared\Date::PHPToExcel($baseDate);
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE:
$maxVal = (int) \PHPExcel\Shared\Date::PHPtoExcel(strtotime('+1 day', $baseDate));
$val = (int) \PHPExcel\Shared\Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR:
$maxVal = (int) \PHPExcel\Shared\Date::PHPToExcel(gmmktime(0, 0, 0, 31, 12, date('Y', $baseDate)));
++$maxVal;
$val = (int) \PHPExcel\Shared\Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER:
$thisMonth = date('m', $baseDate);
$thisQuarter = floor(--$thisMonth / 3);
$maxVal = (int) \PHPExcel\Shared\Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), (1 + $thisQuarter) * 3, date('Y', $baseDate)));
++$maxVal;
$val = (int) \PHPExcel\Shared\Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1 + $thisQuarter * 3, date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH:
$maxVal = (int) \PHPExcel\Shared\Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), date('m', $baseDate), date('Y', $baseDate)));
++$maxVal;
$val = (int) \PHPExcel\Shared\Date::PHPToExcel(gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK:
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK:
$dayOfWeek = date('w', $baseDate);
$val = (int) \PHPExcel\Shared\Date::PHPToExcel($baseDate) - $dayOfWeek;
$maxVal = $val + 7;
break;
}
switch ($dynamicRuleType) {
// Adjust Today dates for Yesterday and Tomorrow
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY:
--$maxVal;
--$val;
break;
case AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW:
++$maxVal;
++$val;
break;
}
// Set the filter column rule attributes ready for writing
$filterColumn->setAttributes(array('val' => $val, 'maxVal' => $maxVal));
// Set the rules for identifying rows for hide/show
$ruleValues[] = array('operator' => AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL, 'value' => $val);
$ruleValues[] = array('operator' => AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN, 'value' => $maxVal);
\PHPExcel\Calculation\Functions::setReturnDateType($rDateType);
return array('method' => 'filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_AND));
}
/**
* Write DATEMODE record to indicate the date system in use (1904 or 1900).
*/
private function writeDateMode()
{
$record = 0x22;
// Record identifier
$length = 0x2;
// Bytes to follow
$f1904 = \PHPExcel\Shared\Date::getExcelCalendar() == \PHPExcel\Shared\Date::CALENDAR_MAC_1904 ? 1 : 0;
// Flag for 1904 date system
$header = pack("vv", $record, $length);
$data = pack("v", $f1904);
$this->append($header . $data);
}
*/
/** Error reporting */
error_reporting(E_ALL);
/** Include PHPExcel */
require_once dirname(__FILE__) . '/../src/Bootstrap.php';
// Create new PHPExcel object
echo date('H:i:s'), " Create new PHPExcel object", EOL;
$objPHPExcel = new \PHPExcel\Spreadsheet();
// Set document properties
echo date('H:i:s'), " Set document properties", EOL;
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file");
// Create a first sheet, representing sales data
echo date('H:i:s'), " Add some data", EOL;
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Invoice');
$objPHPExcel->getActiveSheet()->setCellValue('D1', \PHPExcel\Shared\Date::PHPToExcel(gmmktime(0, 0, 0, date('m'), date('d'), date('Y'))));
$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_DATE_XLSX15);
$objPHPExcel->getActiveSheet()->setCellValue('E1', '#12566');
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Product Id');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Description');
$objPHPExcel->getActiveSheet()->setCellValue('C3', 'Price');
$objPHPExcel->getActiveSheet()->setCellValue('D3', 'Amount');
$objPHPExcel->getActiveSheet()->setCellValue('E3', 'Total');
$objPHPExcel->getActiveSheet()->setCellValue('A4', '1001');
$objPHPExcel->getActiveSheet()->setCellValue('B4', 'PHP for dummies');
$objPHPExcel->getActiveSheet()->setCellValue('C4', '20');
$objPHPExcel->getActiveSheet()->setCellValue('D4', '1');
$objPHPExcel->getActiveSheet()->setCellValue('E4', '=IF(D4<>"",C4*D4,"")');
$objPHPExcel->getActiveSheet()->setCellValue('A5', '1012');
$objPHPExcel->getActiveSheet()->setCellValue('B5', 'OpenXML for dummies');
$objPHPExcel->getActiveSheet()->setCellValue('C5', '22');
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param \PHPExcel\Spreadsheet $objPHPExcel
* @return \PHPExcel\Spreadsheet
* @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');
$zipClass = \PHPExcel\Settings::getZipClass();
$zip = new $zipClass();
if (!$zip->open($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! Error opening file.");
}
// echo '<h1>Meta Information</h1>';
$xml = simplexml_load_string($this->securityScan($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\Document\Properties::PROPERTY_TYPE_STRING;
foreach ($propertyValueAttributes as $key => $value) {
if ($key == 'name') {
$propertyValueName = (string) $value;
} elseif ($key == 'value-type') {
switch ($value) {
case 'date':
$propertyValue = \PHPExcel\Document\Properties::convertProperty($propertyValue, 'date');
$propertyValueType = \PHPExcel\Document\Properties::PROPERTY_TYPE_DATE;
break;
case 'boolean':
$propertyValue = \PHPExcel\Document\Properties::convertProperty($propertyValue, 'bool');
$propertyValueType = \PHPExcel\Document\Properties::PROPERTY_TYPE_BOOLEAN;
break;
case 'float':
$propertyValue = \PHPExcel\Document\Properties::convertProperty($propertyValue, 'r4');
$propertyValueType = \PHPExcel\Document\Properties::PROPERTY_TYPE_FLOAT;
break;
default:
$propertyValueType = \PHPExcel\Document\Properties::PROPERTY_TYPE_STRING;
}
}
}
$docProps->setCustomProperty($propertyValueName, $propertyValue, $propertyValueType);
break;
}
}
}
// echo '<h1>Workbook Content</h1>';
$xml = simplexml_load_string($this->securityScan($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 $keyRowData => $cellData) {
$rowData = $cellData;
break;
}
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) {
if (isset($t->span)) {
foreach ($t->span as $text) {
$textArray[] = (string) $text;
}
} else {
$textArray[] = (string) $t;
}
}
$text = implode("\n", $textArray);
// echo $text, '<br />';
$objPHPExcel->getActiveSheet()->getComment($columnID . $rowID)->setText($this->parseRichText($text));
// ->setAuthor( $author )
}
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;
}
/**
* EOMONTH
*
* Returns the date value for the last day of the month that is the indicated number of months
* before or after start_date.
* Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
*
* Excel Function:
* EOMONTH(dateValue,adjustmentMonths)
*
* @param mixed $dateValue Excel date serial value (float), PHP date timestamp (integer),
* PHP DateTime object, or a standard date string
* @param int $adjustmentMonths The number of months before or after start_date.
* A positive value for months yields a future date;
* a negative value yields a past date.
* @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 EOMONTH($dateValue = 1, $adjustmentMonths = 0)
{
$dateValue = Functions::flattenSingleValue($dateValue);
$adjustmentMonths = Functions::flattenSingleValue($adjustmentMonths);
if (!is_numeric($adjustmentMonths)) {
return Functions::VALUE();
}
$adjustmentMonths = floor($adjustmentMonths);
if (is_string($dateValue = self::getDateValue($dateValue))) {
return Functions::VALUE();
}
// Execute function
$PHPDateObject = self::adjustDateByMonths($dateValue, $adjustmentMonths + 1);
$adjustDays = (int) $PHPDateObject->format('d');
$adjustDaysString = '-' . $adjustDays . ' days';
$PHPDateObject->modify($adjustDaysString);
switch (Functions::getReturnDateType()) {
case Functions::RETURNDATE_EXCEL:
return (double) \PHPExcel\Shared\Date::PHPToExcel($PHPDateObject);
case Functions::RETURNDATE_PHP_NUMERIC:
return (int) \PHPExcel\Shared\Date::excelToPHP(\PHPExcel\Shared\Date::PHPToExcel($PHPDateObject));
case Functions::RETURNDATE_PHP_OBJECT:
return $PHPDateObject;
}
}
// Add some data, resembling some different data types
echo date('H:i:s'), " Add some data", EOL;
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'String')->setCellValue('B1', 'Simple')->setCellValue('C1', 'PHPExcel');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'String')->setCellValue('B2', 'Symbols')->setCellValue('C2', '!+&=()~§±æþ');
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'String')->setCellValue('B3', 'UTF-8')->setCellValue('C3', 'Создать MS Excel Книги из PHP скриптов');
$objPHPExcel->getActiveSheet()->setCellValue('A4', 'Number')->setCellValue('B4', 'Integer')->setCellValue('C4', 12);
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Number')->setCellValue('B5', 'Float')->setCellValue('C5', 34.56);
$objPHPExcel->getActiveSheet()->setCellValue('A6', 'Number')->setCellValue('B6', 'Negative')->setCellValue('C6', -7.89);
$objPHPExcel->getActiveSheet()->setCellValue('A7', 'Boolean')->setCellValue('B7', 'True')->setCellValue('C7', true);
$objPHPExcel->getActiveSheet()->setCellValue('A8', 'Boolean')->setCellValue('B8', 'False')->setCellValue('C8', false);
$dateTimeNow = time();
$objPHPExcel->getActiveSheet()->setCellValue('A9', 'Date/Time')->setCellValue('B9', 'Date')->setCellValue('C9', \PHPExcel\Shared\Date::PHPToExcel($dateTimeNow));
$objPHPExcel->getActiveSheet()->getStyle('C9')->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2);
$objPHPExcel->getActiveSheet()->setCellValue('A10', 'Date/Time')->setCellValue('B10', 'Time')->setCellValue('C10', \PHPExcel\Shared\Date::PHPToExcel($dateTimeNow));
$objPHPExcel->getActiveSheet()->getStyle('C10')->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_DATE_TIME4);
$objPHPExcel->getActiveSheet()->setCellValue('A11', 'Date/Time')->setCellValue('B11', 'Date and Time')->setCellValue('C11', \PHPExcel\Shared\Date::PHPToExcel($dateTimeNow));
$objPHPExcel->getActiveSheet()->getStyle('C11')->getNumberFormat()->setFormatCode(\PHPExcel\Style\NumberFormat::FORMAT_DATE_DATETIME);
$objPHPExcel->getActiveSheet()->setCellValue('A12', 'NULL')->setCellValue('C12', NULL);
$objRichText = new \PHPExcel\RichText();
$objRichText->createText('你好 ');
$objPayable = $objRichText->createTextRun('你 好 吗？');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor(new \PHPExcel\Style\Color(\PHPExcel\Style\Color::COLOR_DARKGREEN));
$objRichText->createText(', unless specified otherwise on the invoice.');
$objPHPExcel->getActiveSheet()->setCellValue('A13', 'Rich Text')->setCellValue('C13', $objRichText);
$objRichText2 = new \PHPExcel\RichText();
$objRichText2->createText("black text\n");
$objRed = $objRichText2->createTextRun("red text");
$objRed->getFont()->setColor(new \PHPExcel\Style\Color(\PHPExcel\Style\Color::COLOR_RED));
$objPHPExcel->getActiveSheet()->getCell("C14")->setValue($objRichText2);
/**
* 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');
$gFileData = $this->gzfileGetContents($pFilename);
// echo '<pre>';
// echo htmlentities($gFileData,ENT_QUOTES,'UTF-8');
// echo '</pre><hr />';
//
$xml = simplexml_load_string($this->securityScan($gFileData), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
$namespacesMeta = $xml->getNamespaces(true);
// var_dump($namespacesMeta);
//
$gnmXML = $xml->children($namespacesMeta['gnm']);
$docProps = $objPHPExcel->getProperties();
// Document Properties are held differently, depending on the version of Gnumeric
if (isset($namespacesMeta['office'])) {
$officeXML = $xml->children($namespacesMeta['office']);
$officeDocXML = $officeXML->{'document-meta'};
$officeDocMetaXML = $officeDocXML->meta;
foreach ($officeDocMetaXML 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(trim($propertyValue));
break;
case 'subject':
$docProps->setSubject(trim($propertyValue));
break;
case 'creator':
$docProps->setCreator(trim($propertyValue));
$docProps->setLastModifiedBy(trim($propertyValue));
break;
case 'date':
$creationDate = strtotime(trim($propertyValue));
$docProps->setCreated($creationDate);
$docProps->setModified($creationDate);
break;
case 'description':
$docProps->setDescription(trim($propertyValue));
break;
}
}
$officePropertyMeta = array();
if (isset($namespacesMeta['meta'])) {
$officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']);
}
foreach ($officePropertyMeta as $propertyName => $propertyValue) {
$attributes = $propertyValue->attributes($namespacesMeta['meta']);
$propertyValue = (string) $propertyValue;
switch ($propertyName) {
case 'keyword':
$docProps->setKeywords(trim($propertyValue));
break;
case 'initial-creator':
$docProps->setCreator(trim($propertyValue));
$docProps->setLastModifiedBy(trim($propertyValue));
break;
case 'creation-date':
$creationDate = strtotime(trim($propertyValue));
$docProps->setCreated($creationDate);
$docProps->setModified($creationDate);
break;
case 'user-defined':
list(, $attrName) = explode(':', $attributes['name']);
switch ($attrName) {
case 'publisher':
$docProps->setCompany(trim($propertyValue));
break;
case 'category':
$docProps->setCategory(trim($propertyValue));
break;
case 'manager':
$docProps->setManager(trim($propertyValue));
break;
}
break;
}
}
}
} elseif (isset($gnmXML->Summary)) {
foreach ($gnmXML->Summary->Item as $summaryItem) {
$propertyName = $summaryItem->name;
$propertyValue = $summaryItem->{'val-string'};
switch ($propertyName) {
case 'title':
$docProps->setTitle(trim($propertyValue));
break;
case 'comments':
$docProps->setDescription(trim($propertyValue));
break;
case 'keywords':
$docProps->setKeywords(trim($propertyValue));
break;
case 'category':
$docProps->setCategory(trim($propertyValue));
break;
case 'manager':
$docProps->setManager(trim($propertyValue));
break;
case 'author':
$docProps->setCreator(trim($propertyValue));
$docProps->setLastModifiedBy(trim($propertyValue));
break;
case 'company':
$docProps->setCompany(trim($propertyValue));
break;
}
}
}
$worksheetID = 0;
foreach ($gnmXML->Sheets->Sheet as $sheet) {
$worksheetName = (string) $sheet->Name;
// echo '<b>Worksheet: ', $worksheetName,'</b><br />';
if (isset($this->loadSheetsOnly) && !in_array($worksheetName, $this->loadSheetsOnly)) {
continue;
}
$maxRow = $maxCol = 0;
// Create new Worksheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($worksheetID);
// 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);
if (!$this->readDataOnly && isset($sheet->PrintInformation)) {
if (isset($sheet->PrintInformation->Margins)) {
foreach ($sheet->PrintInformation->Margins->children('gnm', true) as $key => $margin) {
$marginAttributes = $margin->attributes();
$marginSize = 72 / 100;
// Default
switch ($marginAttributes['PrefUnit']) {
case 'mm':
$marginSize = intval($marginAttributes['Points']) / 100;
break;
}
switch ($key) {
case 'top':
$objPHPExcel->getActiveSheet()->getPageMargins()->setTop($marginSize);
break;
case 'bottom':
$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom($marginSize);
break;
case 'left':
$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft($marginSize);
break;
case 'right':
$objPHPExcel->getActiveSheet()->getPageMargins()->setRight($marginSize);
break;
case 'header':
$objPHPExcel->getActiveSheet()->getPageMargins()->setHeader($marginSize);
break;
case 'footer':
$objPHPExcel->getActiveSheet()->getPageMargins()->setFooter($marginSize);
break;
}
}
}
}
foreach ($sheet->Cells->Cell as $cell) {
$cellAttributes = $cell->attributes();
$row = (int) $cellAttributes->Row + 1;
$column = (int) $cellAttributes->Col;
if ($row > $maxRow) {
$maxRow = $row;
}
if ($column > $maxCol) {
$maxCol = $column;
}
$column = \PHPExcel\Cell::stringFromColumnIndex($column);
// Read cell?
if ($this->getReadFilter() !== null) {
if (!$this->getReadFilter()->readCell($column, $row, $worksheetName)) {
continue;
}
}
$ValueType = $cellAttributes->ValueType;
$ExprID = (string) $cellAttributes->ExprID;
// echo 'Cell ', $column, $row,'<br />';
// echo 'Type is ', $ValueType,'<br />';
// echo 'Value is ', $cell,'<br />';
$type = \PHPExcel\Cell\DataType::TYPE_FORMULA;
if ($ExprID > '') {
if ((string) $cell > '') {
$this->expressions[$ExprID] = array('column' => $cellAttributes->Col, 'row' => $cellAttributes->Row, 'formula' => (string) $cell);
// echo 'NEW EXPRESSION ', $ExprID,'<br />';
} else {
$expression = $this->expressions[$ExprID];
$cell = $this->referenceHelper->updateFormulaReferences($expression['formula'], 'A1', $cellAttributes->Col - $expression['column'], $cellAttributes->Row - $expression['row'], $worksheetName);
// echo 'SHARED EXPRESSION ', $ExprID,'<br />';
// echo 'New Value is ', $cell,'<br />';
}
$type = \PHPExcel\Cell\DataType::TYPE_FORMULA;
} else {
switch ($ValueType) {
case '10':
// NULL
$type = \PHPExcel\Cell\DataType::TYPE_NULL;
break;
case '20':
// Boolean
$type = \PHPExcel\Cell\DataType::TYPE_BOOL;
$cell = $cell == 'TRUE' ? true : false;
break;
case '30':
// Integer
$cell = intval($cell);
// Excel 2007+ doesn't differentiate between integer and float, so set the value and dropthru to the next (numeric) case
// Excel 2007+ doesn't differentiate between integer and float, so set the value and dropthru to the next (numeric) case
case '40':
// Float
$type = \PHPExcel\Cell\DataType::TYPE_NUMERIC;
break;
case '50':
// Error
$type = \PHPExcel\Cell\DataType::TYPE_ERROR;
break;
case '60':
// String
$type = \PHPExcel\Cell\DataType::TYPE_STRING;
break;
case '70':
// Cell Range
// Cell Range
case '80':
// Array
}
}
$objPHPExcel->getActiveSheet()->getCell($column . $row)->setValueExplicit($cell, $type);
}
if (!$this->readDataOnly && isset($sheet->Objects)) {
foreach ($sheet->Objects->children('gnm', true) as $key => $comment) {
$commentAttributes = $comment->attributes();
// Only comment objects are handled at the moment
if ($commentAttributes->Text) {
$objPHPExcel->getActiveSheet()->getComment((string) $commentAttributes->ObjectBound)->setAuthor((string) $commentAttributes->Author)->setText($this->parseRichText((string) $commentAttributes->Text));
}
}
}
// echo '$maxCol=', $maxCol,'; $maxRow=', $maxRow,'<br />';
//
foreach ($sheet->Styles->StyleRegion as $styleRegion) {
$styleAttributes = $styleRegion->attributes();
if ($styleAttributes['startRow'] <= $maxRow && $styleAttributes['startCol'] <= $maxCol) {
$startColumn = \PHPExcel\Cell::stringFromColumnIndex((int) $styleAttributes['startCol']);
$startRow = $styleAttributes['startRow'] + 1;
$endColumn = $styleAttributes['endCol'] > $maxCol ? $maxCol : (int) $styleAttributes['endCol'];
$endColumn = \PHPExcel\Cell::stringFromColumnIndex($endColumn);
$endRow = $styleAttributes['endRow'] > $maxRow ? $maxRow : $styleAttributes['endRow'];
$endRow += 1;
$cellRange = $startColumn . $startRow . ':' . $endColumn . $endRow;
// echo $cellRange,'<br />';
$styleAttributes = $styleRegion->Style->attributes();
// var_dump($styleAttributes);
// echo '<br />';
// We still set the number format mask for date/time values, even if readDataOnly is true
if (!$this->readDataOnly || \PHPExcel\Shared\Date::isDateTimeFormatCode((string) $styleAttributes['Format'])) {
$styleArray = array();
$styleArray['numberformat']['code'] = (string) $styleAttributes['Format'];
// If readDataOnly is false, we set all formatting information
if (!$this->readDataOnly) {
switch ($styleAttributes['HAlign']) {
case '1':
$styleArray['alignment']['horizontal'] = \PHPExcel\Style\Alignment::HORIZONTAL_GENERAL;
break;
case '2':
$styleArray['alignment']['horizontal'] = \PHPExcel\Style\Alignment::HORIZONTAL_LEFT;
break;
case '4':
$styleArray['alignment']['horizontal'] = \PHPExcel\Style\Alignment::HORIZONTAL_RIGHT;
break;
case '8':
$styleArray['alignment']['horizontal'] = \PHPExcel\Style\Alignment::HORIZONTAL_CENTER;
break;
case '16':
case '64':
$styleArray['alignment']['horizontal'] = \PHPExcel\Style\Alignment::HORIZONTAL_CENTER_CONTINUOUS;
break;
case '32':
$styleArray['alignment']['horizontal'] = \PHPExcel\Style\Alignment::HORIZONTAL_JUSTIFY;
break;
}
switch ($styleAttributes['VAlign']) {
case '1':
$styleArray['alignment']['vertical'] = \PHPExcel\Style\Alignment::VERTICAL_TOP;
break;
case '2':
$styleArray['alignment']['vertical'] = \PHPExcel\Style\Alignment::VERTICAL_BOTTOM;
break;
case '4':
$styleArray['alignment']['vertical'] = \PHPExcel\Style\Alignment::VERTICAL_CENTER;
break;
case '8':
$styleArray['alignment']['vertical'] = \PHPExcel\Style\Alignment::VERTICAL_JUSTIFY;
break;
}
$styleArray['alignment']['wrap'] = $styleAttributes['WrapText'] == '1' ? true : false;
$styleArray['alignment']['shrinkToFit'] = $styleAttributes['ShrinkToFit'] == '1' ? true : false;
$styleArray['alignment']['indent'] = intval($styleAttributes["Indent"]) > 0 ? $styleAttributes["indent"] : 0;
$RGB = self::parseGnumericColour($styleAttributes["Fore"]);
$styleArray['font']['color']['rgb'] = $RGB;
$RGB = self::parseGnumericColour($styleAttributes["Back"]);
$shade = $styleAttributes["Shade"];
if ($RGB != '000000' || $shade != '0') {
$styleArray['fill']['color']['rgb'] = $styleArray['fill']['startcolor']['rgb'] = $RGB;
$RGB2 = self::parseGnumericColour($styleAttributes["PatternColor"]);
$styleArray['fill']['endcolor']['rgb'] = $RGB2;
switch ($shade) {
case '1':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_SOLID;
break;
case '2':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_GRADIENT_LINEAR;
break;
case '3':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_GRADIENT_PATH;
break;
case '4':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_DARKDOWN;
break;
case '5':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_DARKGRAY;
break;
case '6':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_DARKGRID;
break;
case '7':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_DARKHORIZONTAL;
break;
case '8':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_DARKTRELLIS;
break;
case '9':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_DARKUP;
break;
case '10':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_DARKVERTICAL;
break;
case '11':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_GRAY0625;
break;
case '12':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_GRAY125;
break;
case '13':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_LIGHTDOWN;
break;
case '14':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_LIGHTGRAY;
break;
case '15':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_LIGHTGRID;
break;
case '16':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_LIGHTHORIZONTAL;
break;
case '17':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_LIGHTTRELLIS;
break;
case '18':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_LIGHTUP;
break;
case '19':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_LIGHTVERTICAL;
break;
case '20':
$styleArray['fill']['type'] = \PHPExcel\Style\Fill::FILL_PATTERN_MEDIUMGRAY;
break;
}
}
$fontAttributes = $styleRegion->Style->Font->attributes();
// var_dump($fontAttributes);
// echo '<br />';
$styleArray['font']['name'] = (string) $styleRegion->Style->Font;
$styleArray['font']['size'] = intval($fontAttributes['Unit']);
$styleArray['font']['bold'] = $fontAttributes['Bold'] == '1' ? true : false;
$styleArray['font']['italic'] = $fontAttributes['Italic'] == '1' ? true : false;
$styleArray['font']['strike'] = $fontAttributes['StrikeThrough'] == '1' ? true : false;
switch ($fontAttributes['Underline']) {
case '1':
$styleArray['font']['underline'] = \PHPExcel\Style\Font::UNDERLINE_SINGLE;
break;
case '2':
$styleArray['font']['underline'] = \PHPExcel\Style\Font::UNDERLINE_DOUBLE;
break;
case '3':
$styleArray['font']['underline'] = \PHPExcel\Style\Font::UNDERLINE_SINGLEACCOUNTING;
break;
case '4':
$styleArray['font']['underline'] = \PHPExcel\Style\Font::UNDERLINE_DOUBLEACCOUNTING;
break;
default:
$styleArray['font']['underline'] = \PHPExcel\Style\Font::UNDERLINE_NONE;
break;
}
switch ($fontAttributes['Script']) {
case '1':
$styleArray['font']['superScript'] = true;
break;
case '-1':
$styleArray['font']['subScript'] = true;
break;
}
if (isset($styleRegion->Style->StyleBorder)) {
if (isset($styleRegion->Style->StyleBorder->Top)) {
$styleArray['borders']['top'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Top->attributes());
}
if (isset($styleRegion->Style->StyleBorder->Bottom)) {
$styleArray['borders']['bottom'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Bottom->attributes());
}
if (isset($styleRegion->Style->StyleBorder->Left)) {
$styleArray['borders']['left'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Left->attributes());
}
if (isset($styleRegion->Style->StyleBorder->Right)) {
$styleArray['borders']['right'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Right->attributes());
}
if (isset($styleRegion->Style->StyleBorder->Diagonal) && isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'})) {
$styleArray['borders']['diagonal'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes());
$styleArray['borders']['diagonaldirection'] = \PHPExcel\Style\Borders::DIAGONAL_BOTH;
} elseif (isset($styleRegion->Style->StyleBorder->Diagonal)) {
$styleArray['borders']['diagonal'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes());
$styleArray['borders']['diagonaldirection'] = \PHPExcel\Style\Borders::DIAGONAL_UP;
} elseif (isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'})) {
$styleArray['borders']['diagonal'] = self::parseBorderAttributes($styleRegion->Style->StyleBorder->{'Rev-Diagonal'}->attributes());
$styleArray['borders']['diagonaldirection'] = \PHPExcel\Style\Borders::DIAGONAL_DOWN;
}
}
if (isset($styleRegion->Style->HyperLink)) {
// TO DO
$hyperlink = $styleRegion->Style->HyperLink->attributes();
}
}
// var_dump($styleArray);
// echo '<br />';
$objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($styleArray);
}
}
}
if (!$this->readDataOnly && isset($sheet->Cols)) {
// Column Widths
$columnAttributes = $sheet->Cols->attributes();
$defaultWidth = $columnAttributes['DefaultSizePts'] / 5.4;
$c = 0;
foreach ($sheet->Cols->ColInfo as $columnOverride) {
$columnAttributes = $columnOverride->attributes();
$column = $columnAttributes['No'];
$columnWidth = $columnAttributes['Unit'] / 5.4;
$hidden = isset($columnAttributes['Hidden']) && $columnAttributes['Hidden'] == '1' ? true : false;
$columnCount = isset($columnAttributes['Count']) ? $columnAttributes['Count'] : 1;
while ($c < $column) {
$objPHPExcel->getActiveSheet()->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($c))->setWidth($defaultWidth);
++$c;
}
while ($c < $column + $columnCount && $c <= $maxCol) {
$objPHPExcel->getActiveSheet()->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($c))->setWidth($columnWidth);
if ($hidden) {
$objPHPExcel->getActiveSheet()->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($c))->setVisible(false);
}
++$c;
}
}
while ($c <= $maxCol) {
$objPHPExcel->getActiveSheet()->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($c))->setWidth($defaultWidth);
++$c;
}
}
if (!$this->readDataOnly && isset($sheet->Rows)) {
// Row Heights
$rowAttributes = $sheet->Rows->attributes();
$defaultHeight = $rowAttributes['DefaultSizePts'];
$r = 0;
foreach ($sheet->Rows->RowInfo as $rowOverride) {
$rowAttributes = $rowOverride->attributes();
$row = $rowAttributes['No'];
$rowHeight = $rowAttributes['Unit'];
$hidden = isset($rowAttributes['Hidden']) && $rowAttributes['Hidden'] == '1' ? true : false;
$rowCount = isset($rowAttributes['Count']) ? $rowAttributes['Count'] : 1;
while ($r < $row) {
++$r;
$objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight);
}
while ($r < $row + $rowCount && $r < $maxRow) {
++$r;
$objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($rowHeight);
if ($hidden) {
$objPHPExcel->getActiveSheet()->getRowDimension($r)->setVisible(false);
}
}
}
while ($r < $maxRow) {
++$r;
$objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight);
}
}
// Handle Merged Cells in this worksheet
if (isset($sheet->MergedRegions)) {
foreach ($sheet->MergedRegions->Merge as $mergeCells) {
if (strpos($mergeCells, ':') !== false) {
$objPHPExcel->getActiveSheet()->mergeCells($mergeCells);
}
}
}
$worksheetID++;
}
// Loop through definedNames (global named ranges)
if (isset($gnmXML->Names)) {
foreach ($gnmXML->Names->Name as $namedRange) {
$name = (string) $namedRange->name;
$range = (string) $namedRange->value;
if (stripos($range, '#REF!') !== false) {
continue;
}
$range = explode('!', $range);
$range[0] = trim($range[0], "'");
if ($worksheet = $objPHPExcel->getSheetByName($range[0])) {
$extractedRange = str_replace('$', '', $range[1]);
$objPHPExcel->addNamedRange(new \PHPExcel\NamedRange($name, $worksheet, $extractedRange));
}
}
}
// Return
return $objPHPExcel;
}
private static function formatAsDate(&$value, &$format)
{
// strip off first part containing e.g. [$-F800] or [$USD-409]
// general syntax: [$<Currency string>-<language info>]
// language info is in hexadecimal
$format = preg_replace('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])/i', '', $format);
// OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case;
// but we don't want to change any quoted strings
$format = preg_replace_callback('/(?:^|")([^"]*)(?:$|")/', ['self', 'setLowercaseCallback'], $format);
// Only process the non-quoted blocks for date format characters
$blocks = explode('"', $format);
foreach ($blocks as $key => &$block) {
if ($key % 2 == 0) {
$block = strtr($block, self::$dateFormatReplacements);
if (!strpos($block, 'A')) {
// 24-hour time format
$block = strtr($block, self::$dateFormatReplacements24);
} else {
// 12-hour time format
$block = strtr($block, self::$dateFormatReplacements12);
}
}
}
$format = implode('"', $blocks);
// escape any quoted characters so that DateTime format() will render them correctly
$format = preg_replace_callback('/"(.*)"/U', ['self', 'escapeQuotesCallback'], $format);
$dateObj = \PHPExcel\Shared\Date::excelToPHPObject($value);
$value = $dateObj->format($format);
}
/**
* Loads Spreadsheet from file
*
* @param string $pFilename
* @return Spreadsheet
* @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\Spreadsheet();
$excel->removeSheetByIndex(0);
if (!$this->readDataOnly) {
$excel->removeCellStyleXfByIndex(0);
// remove the default style
$excel->removeCellXfByIndex(0);
// remove the default style
}
$zipClass = \PHPExcel\Settings::getZipClass();
$zip = new $zipClass();
$zip->open($pFilename);
// Read the theme first, because we need the colour scheme when reading the styles
$wbRels = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "xl/_rels/workbook.xml.rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
foreach ($wbRels->Relationship as $rel) {
switch ($rel["Type"]) {
case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme":
$themeOrderArray = array('lt1', 'dk1', 'lt2', 'dk2');
$themeOrderAdditional = count($themeOrderArray);
$xmlTheme = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "xl/{$rel['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
if (is_object($xmlTheme)) {
$xmlThemeName = $xmlTheme->attributes();
$xmlTheme = $xmlTheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
$themeName = (string) $xmlThemeName['name'];
$colourScheme = $xmlTheme->themeElements->clrScheme->attributes();
$colourSchemeName = (string) $colourScheme['name'];
$colourScheme = $xmlTheme->themeElements->clrScheme->children("http://schemas.openxmlformats.org/drawingml/2006/main");
$themeColours = array();
foreach ($colourScheme as $k => $xmlColour) {
$themePos = array_search($k, $themeOrderArray);
if ($themePos === false) {
$themePos = $themeOrderAdditional++;
}
if (isset($xmlColour->sysClr)) {
$xmlColourData = $xmlColour->sysClr->attributes();
$themeColours[$themePos] = $xmlColourData['lastClr'];
} elseif (isset($xmlColour->srgbClr)) {
$xmlColourData = $xmlColour->srgbClr->attributes();
$themeColours[$themePos] = $xmlColourData['val'];
}
}
self::$theme = new Excel2007\Theme($themeName, $colourSchemeName, $themeColours);
}
break;
}
}
$rels = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "_rels/.rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
foreach ($rels->Relationship as $rel) {
switch ($rel["Type"]) {
case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
$xmlCore = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
if (is_object($xmlCore)) {
$xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
$xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
$xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
$docProps = $excel->getProperties();
$docProps->setCreator((string) self::getArrayItem($xmlCore->xpath("dc:creator")));
$docProps->setLastModifiedBy((string) self::getArrayItem($xmlCore->xpath("cp:lastModifiedBy")));
$docProps->setCreated(strtotime(self::getArrayItem($xmlCore->xpath("dcterms:created"))));
//! respect xsi:type
$docProps->setModified(strtotime(self::getArrayItem($xmlCore->xpath("dcterms:modified"))));
//! respect xsi:type
$docProps->setTitle((string) self::getArrayItem($xmlCore->xpath("dc:title")));
$docProps->setDescription((string) self::getArrayItem($xmlCore->xpath("dc:description")));
$docProps->setSubject((string) self::getArrayItem($xmlCore->xpath("dc:subject")));
$docProps->setKeywords((string) self::getArrayItem($xmlCore->xpath("cp:keywords")));
$docProps->setCategory((string) self::getArrayItem($xmlCore->xpath("cp:category")));
}
break;
case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties":
$xmlCore = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
if (is_object($xmlCore)) {
$docProps = $excel->getProperties();
if (isset($xmlCore->Company)) {
$docProps->setCompany((string) $xmlCore->Company);
}
if (isset($xmlCore->Manager)) {
$docProps->setManager((string) $xmlCore->Manager);
}
}
break;
case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties":
$xmlCore = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
if (is_object($xmlCore)) {
$docProps = $excel->getProperties();
foreach ($xmlCore as $xmlProperty) {
$cellDataOfficeAttributes = $xmlProperty->attributes();
if (isset($cellDataOfficeAttributes['name'])) {
$propertyName = (string) $cellDataOfficeAttributes['name'];
$cellDataOfficeChildren = $xmlProperty->children('http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes');
$attributeType = $cellDataOfficeChildren->getName();
$attributeValue = (string) $cellDataOfficeChildren->{$attributeType};
$attributeValue = \PHPExcel\Document\Properties::convertProperty($attributeValue, $attributeType);
$attributeType = \PHPExcel\Document\Properties::convertPropertyType($attributeType);
$docProps->setCustomProperty($propertyName, $attributeValue, $attributeType);
}
}
}
break;
//Ribbon
//Ribbon
case "http://schemas.microsoft.com/office/2006/relationships/ui/extensibility":
$customUI = $rel['Target'];
if (!is_null($customUI)) {
$this->readRibbon($excel, $customUI, $zip);
}
break;
case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
$dir = dirname($rel["Target"]);
$relsWorkbook = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$dir}/_rels/" . basename($rel["Target"]) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
$relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
$sharedStrings = array();
$xpath = self::getArrayItem($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']"));
$xmlStrings = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$dir}/{$xpath['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
if (isset($xmlStrings) && isset($xmlStrings->si)) {
foreach ($xmlStrings->si as $val) {
if (isset($val->t)) {
$sharedStrings[] = \PHPExcel\Shared\StringHelper::controlCharacterOOXML2PHP((string) $val->t);
} elseif (isset($val->r)) {
$sharedStrings[] = $this->parseRichText($val);
}
}
}
$worksheets = array();
$macros = $customUI = null;
foreach ($relsWorkbook->Relationship as $ele) {
switch ($ele['Type']) {
case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet":
$worksheets[(string) $ele["Id"]] = $ele["Target"];
break;
// a vbaProject ? (: some macros)
// a vbaProject ? (: some macros)
case "http://schemas.microsoft.com/office/2006/relationships/vbaProject":
$macros = $ele["Target"];
break;
}
}
if (!is_null($macros)) {
$macrosCode = $this->getFromZipArchive($zip, 'xl/vbaProject.bin');
//vbaProject.bin always in 'xl' dir and always named vbaProject.bin
if ($macrosCode !== false) {
$excel->setMacrosCode($macrosCode);
$excel->setHasMacros(true);
//short-circuit : not reading vbaProject.bin.rel to get Signature =>allways vbaProjectSignature.bin in 'xl' dir
$Certificate = $this->getFromZipArchive($zip, 'xl/vbaProjectSignature.bin');
if ($Certificate !== false) {
$excel->setMacrosCertificate($Certificate);
}
}
}
$styles = array();
$cellStyles = array();
$xpath = self::getArrayItem($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']"));
$xmlStyles = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$dir}/{$xpath['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
$numFmts = null;
if ($xmlStyles && $xmlStyles->numFmts[0]) {
$numFmts = $xmlStyles->numFmts[0];
}
if (isset($numFmts) && $numFmts !== null) {
$numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
}
if (!$this->readDataOnly && $xmlStyles) {
foreach ($xmlStyles->cellXfs->xf as $xf) {
$numFmt = \PHPExcel\Style\NumberFormat::FORMAT_GENERAL;
if ($xf["numFmtId"]) {
if (isset($numFmts)) {
$tmpNumFmt = self::getArrayItem($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]"));
if (isset($tmpNumFmt["formatCode"])) {
$numFmt = (string) $tmpNumFmt["formatCode"];
}
}
// We shouldn't override any of the built-in MS Excel values (values below id 164)
// But there's a lot of naughty homebrew xlsx writers that do use "reserved" id values that aren't actually used
// So we make allowance for them rather than lose formatting masks
if ((int) $xf["numFmtId"] < 164 && \PHPExcel\Style\NumberFormat::builtInFormatCode((int) $xf["numFmtId"]) !== '') {
$numFmt = \PHPExcel\Style\NumberFormat::builtInFormatCode((int) $xf["numFmtId"]);
}
}
$quotePrefix = false;
if (isset($xf["quotePrefix"])) {
$quotePrefix = (bool) $xf["quotePrefix"];
}
$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, "quotePrefix" => $quotePrefix);
$styles[] = $style;
// add style to cellXf collection
$objStyle = new \PHPExcel\Style();
self::readStyle($objStyle, $style);
$excel->addCellXf($objStyle);
}
foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
$numFmt = \PHPExcel\Style\NumberFormat::FORMAT_GENERAL;
if ($numFmts && $xf["numFmtId"]) {
$tmpNumFmt = self::getArrayItem($numFmts->xpath("sml:numFmt[@numFmtId={$xf['numFmtId']}]"));
if (isset($tmpNumFmt["formatCode"])) {
$numFmt = (string) $tmpNumFmt["formatCode"];
} elseif ((int) $xf["numFmtId"] < 165) {
$numFmt = \PHPExcel\Style\NumberFormat::builtInFormatCode((int) $xf["numFmtId"]);
}
}
$cellStyle = (object) array("numFmt" => $numFmt, "font" => $xmlStyles->fonts->font[intval($xf["fontId"])], "fill" => $xmlStyles->fills->fill[intval($xf["fillId"])], "border" => $xmlStyles->borders->border[intval($xf["borderId"])], "alignment" => $xf->alignment, "protection" => $xf->protection, "quotePrefix" => $quotePrefix);
$cellStyles[] = $cellStyle;
// add style to cellStyleXf collection
$objStyle = new \PHPExcel\Style();
self::readStyle($objStyle, $cellStyle);
$excel->addCellStyleXf($objStyle);
}
}
$dxfs = array();
if (!$this->readDataOnly && $xmlStyles) {
// Conditional Styles
if ($xmlStyles->dxfs) {
foreach ($xmlStyles->dxfs->dxf as $dxf) {
$style = new \PHPExcel\Style(false, true);
self::readStyle($style, $dxf);
$dxfs[] = $style;
}
}
// Cell Styles
if ($xmlStyles->cellStyles) {
foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
if (intval($cellStyle['builtinId']) == 0) {
if (isset($cellStyles[intval($cellStyle['xfId'])])) {
// Set default style
$style = new \PHPExcel\Style();
self::readStyle($style, $cellStyles[intval($cellStyle['xfId'])]);
// normal style, currently not using it for anything
}
}
}
}
}
$xmlWorkbook = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$rel['Target']}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
// Set base date
if ($xmlWorkbook->workbookPr) {
\PHPExcel\Shared\Date::setExcelCalendar(\PHPExcel\Shared\Date::CALENDAR_WINDOWS_1900);
if (isset($xmlWorkbook->workbookPr['date1904'])) {
if (self::boolean((string) $xmlWorkbook->workbookPr['date1904'])) {
\PHPExcel\Shared\Date::setExcelCalendar(\PHPExcel\Shared\Date::CALENDAR_MAC_1904);
}
}
}
$sheetId = 0;
// keep track of new sheet id in final workbook
$oldSheetId = -1;
// keep track of old sheet id in final workbook
$countSkippedSheets = 0;
// keep track of number of skipped sheets
$mapSheetId = array();
// mapping of sheet ids from old to new
$charts = $chartDetails = array();
if ($xmlWorkbook->sheets) {
foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
++$oldSheetId;
// Check if sheet should be skipped
if (isset($this->loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->loadSheetsOnly)) {
++$countSkippedSheets;
$mapSheetId[$oldSheetId] = null;
continue;
}
// Map old sheet id in original workbook to new sheet id.
// They will differ if loadSheetsOnly() is being used
$mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets;
// Load sheet
$docSheet = $excel->createSheet();
// Use false for $updateFormulaCellReferences to prevent adjustment of worksheet
// references in formula cells... during the load, all formulae should be correct,
// and we're simply bringing the worksheet name in line with the formula, not the
// reverse
$docSheet->setTitle((string) $eleSheet["name"], false);
$fileWorksheet = $worksheets[(string) self::getArrayItem($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$xmlSheet = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "{$dir}/{$fileWorksheet}")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
$sharedFormulas = array();
if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') {
$docSheet->setSheetState((string) $eleSheet["state"]);
}
if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) {
$docSheet->getSheetView()->setZoomScale(intval($xmlSheet->sheetViews->sheetView['zoomScale']));
}
if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) {
$docSheet->getSheetView()->setZoomScaleNormal(intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal']));
}
if (isset($xmlSheet->sheetViews->sheetView['view'])) {
$docSheet->getSheetView()->setView((string) $xmlSheet->sheetViews->sheetView['view']);
}
if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
$docSheet->setShowGridLines(self::boolean((string) $xmlSheet->sheetViews->sheetView['showGridLines']));
}
if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) {
$docSheet->setShowRowColHeaders(self::boolean((string) $xmlSheet->sheetViews->sheetView['showRowColHeaders']));
}
if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) {
$docSheet->setRightToLeft(self::boolean((string) $xmlSheet->sheetViews->sheetView['rightToLeft']));
}
if (isset($xmlSheet->sheetViews->sheetView->pane)) {
if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) {
$docSheet->freezePane((string) $xmlSheet->sheetViews->sheetView->pane['topLeftCell']);
} else {
$xSplit = 0;
$ySplit = 0;
if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) {
$xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']);
}
if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) {
$ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']);
}
$docSheet->freezePaneByColumnAndRow($xSplit, $ySplit);
}
}
if (isset($xmlSheet->sheetViews->sheetView->selection)) {
if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) {
$sqref = (string) $xmlSheet->sheetViews->sheetView->selection['sqref'];
$sqref = explode(' ', $sqref);
$sqref = $sqref[0];
$docSheet->setSelectedCells($sqref);
}
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) {
if (isset($xmlSheet->sheetPr->tabColor['rgb'])) {
$docSheet->getTabColor()->setARGB((string) $xmlSheet->sheetPr->tabColor['rgb']);
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr['codeName'])) {
$docSheet->setCodeName((string) $xmlSheet->sheetPr['codeName']);
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && !self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryRight'])) {
$docSheet->setShowSummaryRight(false);
} else {
$docSheet->setShowSummaryRight(true);
}
if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && !self::boolean((string) $xmlSheet->sheetPr->outlinePr['summaryBelow'])) {
$docSheet->setShowSummaryBelow(false);
} else {
$docSheet->setShowSummaryBelow(true);
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) {
if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && !self::boolean((string) $xmlSheet->sheetPr->pageSetUpPr['fitToPage'])) {
$docSheet->getPageSetup()->setFitToPage(false);
} else {
$docSheet->getPageSetup()->setFitToPage(true);
}
}
if (isset($xmlSheet->sheetFormatPr)) {
if (isset($xmlSheet->sheetFormatPr['customHeight']) && self::boolean((string) $xmlSheet->sheetFormatPr['customHeight']) && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
$docSheet->getDefaultRowDimension()->setRowHeight((double) $xmlSheet->sheetFormatPr['defaultRowHeight']);
}
if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
$docSheet->getDefaultColumnDimension()->setWidth((double) $xmlSheet->sheetFormatPr['defaultColWidth']);
}
if (isset($xmlSheet->sheetFormatPr['zeroHeight']) && (string) $xmlSheet->sheetFormatPr['zeroHeight'] == '1') {
$docSheet->getDefaultRowDimension()->setZeroHeight(true);
}
}
if (isset($xmlSheet->cols) && !$this->readDataOnly) {
foreach ($xmlSheet->cols->col as $col) {
for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
if ($col["style"] && !$this->readDataOnly) {
$docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"]));
}
if (self::boolean($col["bestFit"])) {
//$docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setAutoSize(true);
}
if (self::boolean($col["hidden"])) {
// echo \PHPExcel\Cell::stringFromColumnIndex($i), ': HIDDEN COLUMN',PHP_EOL;
$docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setVisible(false);
}
if (self::boolean($col["collapsed"])) {
$docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setCollapsed(true);
}
if ($col["outlineLevel"] > 0) {
$docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
}
$docSheet->getColumnDimension(\PHPExcel\Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));
if (intval($col["max"]) == 16384) {
break;
}
}
}
}
if (isset($xmlSheet->printOptions) && !$this->readDataOnly) {
if (self::boolean((string) $xmlSheet->printOptions['gridLinesSet'])) {
$docSheet->setShowGridlines(true);
}
if (self::boolean((string) $xmlSheet->printOptions['gridLines'])) {
$docSheet->setPrintGridlines(true);
}
if (self::boolean((string) $xmlSheet->printOptions['horizontalCentered'])) {
$docSheet->getPageSetup()->setHorizontalCentered(true);
}
if (self::boolean((string) $xmlSheet->printOptions['verticalCentered'])) {
$docSheet->getPageSetup()->setVerticalCentered(true);
}
}
if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
foreach ($xmlSheet->sheetData->row as $row) {
if ($row["ht"] && !$this->readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
}
if (self::boolean($row["hidden"]) && !$this->readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setVisible(false);
}
if (self::boolean($row["collapsed"])) {
$docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true);
}
if ($row["outlineLevel"] > 0) {
$docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
}
if ($row["s"] && !$this->readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"]));
}
foreach ($row->c as $c) {
$r = (string) $c["r"];
$cellDataType = (string) $c["t"];
$value = null;
$calculatedValue = null;
// Read cell?
if ($this->getReadFilter() !== null) {
$coordinates = \PHPExcel\Cell::coordinateFromString($r);
if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
continue;
}
}
// echo 'Reading cell ', $coordinates[0], $coordinates[1], PHP_EOL;
// print_r($c);
// echo PHP_EOL;
// echo 'Cell Data Type is ', $cellDataType, ': ';
//
// Read cell!
switch ($cellDataType) {
case "s":
// echo 'String', PHP_EOL;
if ((string) $c->v != '') {
$value = $sharedStrings[intval($c->v)];
if ($value instanceof \PHPExcel\RichText) {
$value = clone $value;
}
} else {
$value = '';
}
break;
case "b":
// echo 'Boolean', PHP_EOL;
if (!isset($c->f)) {
$value = self::castToBoolean($c);
} else {
// Formula
$this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToBoolean');
if (isset($c->f['t'])) {
$att = array();
$att = $c->f;
$docSheet->getCell($r)->setFormulaAttributes($att);
}
// echo '$calculatedValue = ', $calculatedValue, PHP_EOL;
}
break;
case "inlineStr":
// echo 'Inline String', PHP_EOL;
if (isset($c->f)) {
$this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToError');
} else {
$value = $this->parseRichText($c->is);
}
break;
case "e":
// echo 'Error', PHP_EOL;
if (!isset($c->f)) {
$value = self::castToError($c);
} else {
// Formula
$this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToError');
// echo '$calculatedValue = ', $calculatedValue, PHP_EOL;
}
break;
default:
// echo 'Default', PHP_EOL;
if (!isset($c->f)) {
// echo 'Not a Formula', PHP_EOL;
$value = self::castToString($c);
} else {
// echo 'Treat as Formula', PHP_EOL;
// Formula
$this->castToFormula($c, $r, $cellDataType, $value, $calculatedValue, $sharedFormulas, 'castToString');
// echo '$calculatedValue = ', $calculatedValue, PHP_EOL;
}
break;
}
// echo 'Value is ', $value, PHP_EOL;
// Check for numeric values
if (is_numeric($value) && $cellDataType != 's') {
if ($value == (int) $value) {
$value = (int) $value;
} elseif ($value == (double) $value) {
$value = (double) $value;
} elseif ($value == (double) $value) {
$value = (double) $value;
}
}
// Rich text?
if ($value instanceof \PHPExcel\RichText && $this->readDataOnly) {
$value = $value->getPlainText();
}
$cell = $docSheet->getCell($r);
// Assign value
if ($cellDataType != '') {
$cell->setValueExplicit($value, $cellDataType);
} else {
$cell->setValue($value);
}
if ($calculatedValue !== null) {
$cell->setCalculatedValue($calculatedValue);
}
// Style information?
if ($c["s"] && !$this->readDataOnly) {
// no style index means 0, it seems
$cell->setXfIndex(isset($styles[intval($c["s"])]) ? intval($c["s"]) : 0);
}
}
}
}
$conditionals = array();
if (!$this->readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) {
foreach ($xmlSheet->conditionalFormatting as $conditional) {
foreach ($conditional->cfRule as $cfRule) {
if (((string) $cfRule["type"] == \PHPExcel\Style\Conditional::CONDITION_NONE || (string) $cfRule["type"] == \PHPExcel\Style\Conditional::CONDITION_CELLIS || (string) $cfRule["type"] == \PHPExcel\Style\Conditional::CONDITION_CONTAINSTEXT || (string) $cfRule["type"] == \PHPExcel\Style\Conditional::CONDITION_EXPRESSION) && isset($dxfs[intval($cfRule["dxfId"])])) {
$conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule;
}
}
}
foreach ($conditionals as $ref => $cfRules) {
ksort($cfRules);
$conditionalStyles = array();
foreach ($cfRules as $cfRule) {
$objConditional = new \PHPExcel\Style\Conditional();
$objConditional->setConditionType((string) $cfRule["type"]);
$objConditional->setOperatorType((string) $cfRule["operator"]);
if ((string) $cfRule["text"] != '') {
$objConditional->setText((string) $cfRule["text"]);
}
if (count($cfRule->formula) > 1) {
foreach ($cfRule->formula as $formula) {
$objConditional->addCondition((string) $formula);
}
} else {
$objConditional->addCondition((string) $cfRule->formula);
}
$objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]);
$conditionalStyles[] = $objConditional;
}
// Extract all cell references in $ref
$cellBlocks = explode(' ', str_replace('$', '', strtoupper($ref)));
foreach ($cellBlocks as $cellBlock) {
$docSheet->getStyle($cellBlock)->setConditionalStyles($conditionalStyles);
}
}
}
$aKeys = ["sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells"];
if (!$this->readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
foreach ($aKeys as $key) {
$method = "set" . ucfirst($key);
$docSheet->getProtection()->{$method}(self::boolean((string) $xmlSheet->sheetProtection[$key]));
}
}
if (!$this->readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
$docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], true);
if ($xmlSheet->protectedRanges->protectedRange) {
foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
$docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
}
}
}
if ($xmlSheet && $xmlSheet->autoFilter && !$this->readDataOnly) {
$autoFilterRange = (string) $xmlSheet->autoFilter["ref"];
if (strpos($autoFilterRange, ':') !== false) {
$autoFilter = $docSheet->getAutoFilter();
$autoFilter->setRange($autoFilterRange);
foreach ($xmlSheet->autoFilter->filterColumn as $filterColumn) {
$column = $autoFilter->getColumnByOffset((int) $filterColumn["colId"]);
// Check for standard filters
if ($filterColumn->filters) {
$column->setFilterType(\PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_FILTER);
$filters = $filterColumn->filters;
if (isset($filters["blank"]) && $filters["blank"] == 1) {
// Operator is undefined, but always treated as EQUAL
$column->createRule()->setRule(null, '')->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_FILTER);
}
// Standard filters are always an OR join, so no join rule needs to be set
// Entries can be either filter elements
foreach ($filters->filter as $filterRule) {
// Operator is undefined, but always treated as EQUAL
$column->createRule()->setRule(null, (string) $filterRule["val"])->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_FILTER);
}
// Or Date Group elements
foreach ($filters->dateGroupItem as $dateGroupItem) {
$column->createRule()->setRule(null, array('year' => (string) $dateGroupItem["year"], 'month' => (string) $dateGroupItem["month"], 'day' => (string) $dateGroupItem["day"], 'hour' => (string) $dateGroupItem["hour"], 'minute' => (string) $dateGroupItem["minute"], 'second' => (string) $dateGroupItem["second"]), (string) $dateGroupItem["dateTimeGrouping"])->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP);
}
}
// Check for custom filters
if ($filterColumn->customFilters) {
$column->setFilterType(\PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER);
$customFilters = $filterColumn->customFilters;
// Custom filters can an AND or an OR join;
// and there should only ever be one or two entries
if (isset($customFilters["and"]) && $customFilters["and"] == 1) {
$column->setJoin(\PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_AND);
}
foreach ($customFilters->customFilter as $filterRule) {
$column->createRule()->setRule((string) $filterRule["operator"], (string) $filterRule["val"])->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER);
}
}
// Check for dynamic filters
if ($filterColumn->dynamicFilter) {
$column->setFilterType(\PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER);
// We should only ever have one dynamic filter
foreach ($filterColumn->dynamicFilter as $filterRule) {
$column->createRule()->setRule(null, (string) $filterRule["val"], (string) $filterRule["type"])->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER);
if (isset($filterRule["val"])) {
$column->setAttribute('val', (string) $filterRule["val"]);
}
if (isset($filterRule["maxVal"])) {
$column->setAttribute('maxVal', (string) $filterRule["maxVal"]);
}
}
}
// Check for dynamic filters
if ($filterColumn->top10) {
$column->setFilterType(\PHPExcel\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER);
// We should only ever have one top10 filter
foreach ($filterColumn->top10 as $filterRule) {
$column->createRule()->setRule(isset($filterRule["percent"]) && $filterRule["percent"] == 1 ? \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT : \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE, (string) $filterRule["val"], isset($filterRule["top"]) && $filterRule["top"] == 1 ? \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP : \PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM)->setRuleType(\PHPExcel\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_TOPTENFILTER);
}
}
}
}
}
if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->readDataOnly) {
foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
$mergeRef = (string) $mergeCell["ref"];
if (strpos($mergeRef, ':') !== false) {
$docSheet->mergeCells((string) $mergeCell["ref"]);
}
}
}
if ($xmlSheet && $xmlSheet->pageMargins && !$this->readDataOnly) {
$docPageMargins = $docSheet->getPageMargins();
$docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"]));
$docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"]));
$docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"]));
$docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"]));
$docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"]));
$docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"]));
}
if ($xmlSheet && $xmlSheet->pageSetup && !$this->readDataOnly) {
$docPageSetup = $docSheet->getPageSetup();
if (isset($xmlSheet->pageSetup["orientation"])) {
$docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
}
if (isset($xmlSheet->pageSetup["paperSize"])) {
$docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"]));
}
if (isset($xmlSheet->pageSetup["scale"])) {
$docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), false);
}
if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) {
$docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), false);
}
if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) {
$docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), false);
}
if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) && self::boolean((string) $xmlSheet->pageSetup["useFirstPageNumber"])) {
$docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"]));
}
}
if ($xmlSheet && $xmlSheet->headerFooter && !$this->readDataOnly) {
$docHeaderFooter = $docSheet->getHeaderFooter();
if (isset($xmlSheet->headerFooter["differentOddEven"]) && self::boolean((string) $xmlSheet->headerFooter["differentOddEven"])) {
$docHeaderFooter->setDifferentOddEven(true);
} else {
$docHeaderFooter->setDifferentOddEven(false);
}
if (isset($xmlSheet->headerFooter["differentFirst"]) && self::boolean((string) $xmlSheet->headerFooter["differentFirst"])) {
$docHeaderFooter->setDifferentFirst(true);
} else {
$docHeaderFooter->setDifferentFirst(false);
}
if (isset($xmlSheet->headerFooter["scaleWithDoc"]) && !self::boolean((string) $xmlSheet->headerFooter["scaleWithDoc"])) {
$docHeaderFooter->setScaleWithDocument(false);
} else {
$docHeaderFooter->setScaleWithDocument(true);
}
if (isset($xmlSheet->headerFooter["alignWithMargins"]) && !self::boolean((string) $xmlSheet->headerFooter["alignWithMargins"])) {
$docHeaderFooter->setAlignWithMargins(false);
} else {
$docHeaderFooter->setAlignWithMargins(true);
}
$docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
$docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
$docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
$docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
$docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
$docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
}
if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->readDataOnly) {
foreach ($xmlSheet->rowBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak("A{$brk['id']}", \PHPExcel\Worksheet::BREAK_ROW);
}
}
}
if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->readDataOnly) {
foreach ($xmlSheet->colBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak(\PHPExcel\Cell::stringFromColumnIndex((string) $brk["id"]) . "1", \PHPExcel\Worksheet::BREAK_COLUMN);
}
}
}
if ($xmlSheet && $xmlSheet->dataValidations && !$this->readDataOnly) {
foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
// Uppercase coordinate
$range = strtoupper($dataValidation["sqref"]);
$rangeSet = explode(' ', $range);
foreach ($rangeSet as $range) {
$stRange = $docSheet->shrinkRangeToFit($range);
// Extract all cell references in $range
foreach (\PHPExcel\Cell::extractAllCellReferencesInRange($stRange) 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->securityScan($this->getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
$hyperlinks[(string) $ele["Id"]] = (string) $ele["Target"];
}
}
}
// Loop through hyperlinks
if ($xmlSheet && $xmlSheet->hyperlinks) {
foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
// Link url
$linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
foreach (\PHPExcel\Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) {
$cell = $docSheet->getCell($cellReference);
if (isset($linkRel['id'])) {
$hyperlinkUrl = $hyperlinks[(string) $linkRel['id']];
if (isset($hyperlink['location'])) {
$hyperlinkUrl .= '#' . (string) $hyperlink['location'];
}
$cell->getHyperlink()->setUrl($hyperlinkUrl);
} elseif (isset($hyperlink['location'])) {
$cell->getHyperlink()->setUrl('sheet://' . (string) $hyperlink['location']);
}
// Tooltip
if (isset($hyperlink['tooltip'])) {
$cell->getHyperlink()->setTooltip((string) $hyperlink['tooltip']);
}
}
}
}
}
// Add comments
$comments = array();
$vmlComments = array();
if (!$this->readDataOnly) {
// Locate comment relations
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") {
$comments[(string) $ele["Id"]] = (string) $ele["Target"];
}
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
$vmlComments[(string) $ele["Id"]] = (string) $ele["Target"];
}
}
}
// Loop through comments
foreach ($comments as $relName => $relPath) {
// Load comments file
$relPath = \PHPExcel\Shared\File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath);
$commentsFile = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, $relPath)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
// 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) {
if (!empty($comment['authorId'])) {
$docSheet->getComment((string) $comment['ref'])->setAuthor($authors[(string) $comment['authorId']]);
}
$docSheet->getComment((string) $comment['ref'])->setText($this->parseRichText($comment->text));
}
}
// Loop through VML comments
foreach ($vmlComments as $relName => $relPath) {
// Load VML comments file
$relPath = \PHPExcel\Shared\File::realpath(dirname("{$dir}/{$fileWorksheet}") . "/" . $relPath);
$vmlCommentsFile = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, $relPath)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
$vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$shapes = $vmlCommentsFile->xpath('//v:shape');
foreach ($shapes as $shape) {
$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
if (isset($shape['style'])) {
$style = (string) $shape['style'];
$fillColor = strtoupper(substr((string) $shape['fillcolor'], 1));
$column = null;
$row = null;
$clientData = $shape->xpath('.//x:ClientData');
if (is_array($clientData) && !empty($clientData)) {
$clientData = $clientData[0];
if (isset($clientData['ObjectType']) && (string) $clientData['ObjectType'] == 'Note') {
$temp = $clientData->xpath('.//x:Row');
if (is_array($temp)) {
$row = $temp[0];
}
$temp = $clientData->xpath('.//x:Column');
if (is_array($temp)) {
$column = $temp[0];
}
}
}
if ($column !== null && $row !== null) {
// Set comment properties
$comment = $docSheet->getCommentByColumnAndRow((string) $column, $row + 1);
$comment->getFillColor()->setRGB($fillColor);
// Parse style
$styleArray = explode(';', str_replace(' ', '', $style));
foreach ($styleArray as $stylePair) {
$stylePair = explode(':', $stylePair);
if ($stylePair[0] == 'margin-left') {
$comment->setMarginLeft($stylePair[1]);
}
if ($stylePair[0] == 'margin-top') {
$comment->setMarginTop($stylePair[1]);
}
if ($stylePair[0] == 'width') {
$comment->setWidth($stylePair[1]);
}
if ($stylePair[0] == 'height') {
$comment->setHeight($stylePair[1]);
}
if ($stylePair[0] == 'visibility') {
$comment->setVisible($stylePair[1] == 'visible');
}
}
}
}
}
}
// Header/footer images
if ($xmlSheet && $xmlSheet->legacyDrawingHF && !$this->readDataOnly) {
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
$vmlRelationship = '';
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
$vmlRelationship = self::dirAdd("{$dir}/{$fileWorksheet}", $ele["Target"]);
}
}
if ($vmlRelationship != '') {
// Fetch linked images
$relsVML = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels')), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
$drawings = array();
foreach ($relsVML->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
$drawings[(string) $ele["Id"]] = self::dirAdd($vmlRelationship, $ele["Target"]);
}
}
// Fetch VML document
$vmlDrawing = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, $vmlRelationship)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
$vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$hfImages = array();
$shapes = $vmlDrawing->xpath('//v:shape');
foreach ($shapes as $idx => $shape) {
$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$imageData = $shape->xpath('//v:imagedata');
$imageData = $imageData[$idx];
$imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office');
$style = self::toCSSArray((string) $shape['style']);
$hfImages[(string) $shape['id']] = new \PHPExcel\Worksheet\HeaderFooterDrawing();
if (isset($imageData['title'])) {
$hfImages[(string) $shape['id']]->setName((string) $imageData['title']);
}
$hfImages[(string) $shape['id']]->setPath("zip://" . \PHPExcel\Shared_File::realpath($pFilename) . "#" . $drawings[(string) $imageData['relid']], false);
$hfImages[(string) $shape['id']]->setResizeProportional(false);
$hfImages[(string) $shape['id']]->setWidth($style['width']);
$hfImages[(string) $shape['id']]->setHeight($style['height']);
if (isset($style['margin-left'])) {
$hfImages[(string) $shape['id']]->setOffsetX($style['margin-left']);
}
$hfImages[(string) $shape['id']]->setOffsetY($style['margin-top']);
$hfImages[(string) $shape['id']]->setResizeProportional(true);
}
$docSheet->getHeaderFooter()->setImages($hfImages);
}
}
}
}
// TODO: Autoshapes from twoCellAnchors!
if ($zip->locateName(dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname("{$dir}/{$fileWorksheet}") . "/_rels/" . basename($fileWorksheet) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
$drawings = array();
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") {
$drawings[(string) $ele["Id"]] = self::dirAdd("{$dir}/{$fileWorksheet}", $ele["Target"]);
}
}
if ($xmlSheet->drawing && !$this->readDataOnly) {
foreach ($xmlSheet->drawing as $drawing) {
$fileDrawing = $drawings[(string) self::getArrayItem($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$relsDrawing = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
$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::dirAdd($fileDrawing, $ele["Target"]);
} elseif ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart") {
if ($this->includeCharts) {
$charts[self::dirAdd($fileDrawing, $ele["Target"])] = array('id' => (string) $ele["Id"], 'sheet' => $docSheet->getTitle());
}
}
}
}
$xmlDrawing = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, $fileDrawing)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions())->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
if ($xmlDrawing->oneCellAnchor) {
foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) {
if ($oneCellAnchor->pic->blipFill) {
$blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new \PHPExcel\Worksheet\Drawing();
$objDrawing->setName((string) self::getArrayItem($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::getArrayItem($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://" . \PHPExcel\Shared\File::realpath($pFilename) . "#" . $images[(string) self::getArrayItem($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(\PHPExcel\Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1));
$objDrawing->setOffsetX(\PHPExcel\Shared\Drawing::EMUToPixels($oneCellAnchor->from->colOff));
$objDrawing->setOffsetY(\PHPExcel\Shared\Drawing::EMUToPixels($oneCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(\PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($oneCellAnchor->ext->attributes(), "cx")));
$objDrawing->setHeight(\PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($oneCellAnchor->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(\PHPExcel\Shared\Drawing::angleToDegrees(self::getArrayItem($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(\PHPExcel\Shared\Drawing::EMUTopixels(self::getArrayItem($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(\PHPExcel\Shared\Drawing::EMUTopixels(self::getArrayItem($outerShdw->attributes(), "dist")));
$shadow->setDirection(\PHPExcel\Shared\Drawing::angleToDegrees(self::getArrayItem($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::getArrayItem($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::getArrayItem($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::getArrayItem($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
} else {
// ? Can charts be positioned with a oneCellAnchor ?
$coordinates = \PHPExcel\Cell::stringFromColumnIndex((string) $oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1);
$offsetX = \PHPExcel\Shared\Drawing::EMUToPixels($oneCellAnchor->from->colOff);
$offsetY = \PHPExcel\Shared\Drawing::EMUToPixels($oneCellAnchor->from->rowOff);
$width = \PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($oneCellAnchor->ext->attributes(), "cx"));
$height = \PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($oneCellAnchor->ext->attributes(), "cy"));
}
}
}
if ($xmlDrawing->twoCellAnchor) {
foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) {
if ($twoCellAnchor->pic->blipFill) {
$blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new \PHPExcel\Worksheet\Drawing();
$objDrawing->setName((string) self::getArrayItem($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::getArrayItem($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://" . \PHPExcel\Shared\File::realpath($pFilename) . "#" . $images[(string) self::getArrayItem($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(\PHPExcel\Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
$objDrawing->setOffsetX(\PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->from->colOff));
$objDrawing->setOffsetY(\PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
if ($xfrm) {
$objDrawing->setWidth(\PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($xfrm->ext->attributes(), "cx")));
$objDrawing->setHeight(\PHPExcel\Shared\Drawing::EMUToPixels(self::getArrayItem($xfrm->ext->attributes(), "cy")));
$objDrawing->setRotation(\PHPExcel\Shared\Drawing::angleToDegrees(self::getArrayItem($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(\PHPExcel\Shared\Drawing::EMUTopixels(self::getArrayItem($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(\PHPExcel\Shared\Drawing::EMUTopixels(self::getArrayItem($outerShdw->attributes(), "dist")));
$shadow->setDirection(\PHPExcel\Shared\Drawing::angleToDegrees(self::getArrayItem($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::getArrayItem($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::getArrayItem($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::getArrayItem($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
} elseif ($this->includeCharts && $twoCellAnchor->graphicFrame) {
$fromCoordinate = \PHPExcel\Cell::stringFromColumnIndex((string) $twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1);
$fromOffsetX = \PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->from->colOff);
$fromOffsetY = \PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->from->rowOff);
$toCoordinate = \PHPExcel\Cell::stringFromColumnIndex((string) $twoCellAnchor->to->col) . ($twoCellAnchor->to->row + 1);
$toOffsetX = \PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->to->colOff);
$toOffsetY = \PHPExcel\Shared\Drawing::EMUToPixels($twoCellAnchor->to->rowOff);
$graphic = $twoCellAnchor->graphicFrame->children("http://schemas.openxmlformats.org/drawingml/2006/main")->graphic;
$chartRef = $graphic->graphicData->children("http://schemas.openxmlformats.org/drawingml/2006/chart")->chart;
$thisChart = (string) $chartRef->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships");
$chartDetails[$docSheet->getTitle() . '!' . $thisChart] = array('fromCoordinate' => $fromCoordinate, 'fromOffsetX' => $fromOffsetX, 'fromOffsetY' => $fromOffsetY, 'toCoordinate' => $toCoordinate, 'toOffsetX' => $toOffsetX, 'toOffsetY' => $toOffsetY, 'worksheetTitle' => $docSheet->getTitle());
}
}
}
}
}
}
// Loop through definedNames
if ($xmlWorkbook->definedNames) {
foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
// Extract range
$extractedRange = (string) $definedName;
$extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange);
if (($spos = strpos($extractedRange, '!')) !== false) {
$extractedRange = substr($extractedRange, 0, $spos) . str_replace('$', '', substr($extractedRange, $spos));
} else {
$extractedRange = str_replace('$', '', $extractedRange);
}
// Valid range?
if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') {
continue;
}
// Some definedNames are only applicable if we are on the same sheet...
if ((string) $definedName['localSheetId'] != '' && (string) $definedName['localSheetId'] == $sheetId) {
// Switch on type
switch ((string) $definedName['name']) {
case '_xlnm._FilterDatabase':
if ((string) $definedName['hidden'] !== '1') {
$extractedRange = explode(',', $extractedRange);
foreach ($extractedRange as $range) {
$autoFilterRange = $range;
if (strpos($autoFilterRange, ':') !== false) {
$docSheet->getAutoFilter()->setRange($autoFilterRange);
}
}
}
break;
case '_xlnm.Print_Titles':
// Split $extractedRange
$extractedRange = explode(',', $extractedRange);
// Set print titles
foreach ($extractedRange as $range) {
$matches = array();
$range = str_replace('$', '', $range);
// 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)) {
// check for repeating rows, e.g. '1:1' or '1:5'
$docSheet->getPageSetup()->setRowsToRepeatAtTop(array($matches[1], $matches[2]));
}
}
break;
case '_xlnm.Print_Area':
$rangeSets = explode(',', $extractedRange);
// FIXME: what if sheetname contains comma?
$newRangeSets = array();
foreach ($rangeSets as $rangeSet) {
$range = explode('!', $rangeSet);
// FIXME: what if sheetname contains exclamation mark?
$rangeSet = isset($range[1]) ? $range[1] : $range[0];
if (strpos($rangeSet, ':') === false) {
$rangeSet = $rangeSet . ':' . $rangeSet;
}
$newRangeSets[] = str_replace('$', '', $rangeSet);
}
$docSheet->getPageSetup()->setPrintArea(implode(',', $newRangeSets));
break;
default:
break;
}
}
}
}
// Next sheet id
++$sheetId;
}
// Loop through definedNames
if ($xmlWorkbook->definedNames) {
foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
// Extract range
$extractedRange = (string) $definedName;
$extractedRange = preg_replace('/\'(\\w+)\'\\!/', '', $extractedRange);
if (($spos = strpos($extractedRange, '!')) !== false) {
$extractedRange = substr($extractedRange, 0, $spos) . str_replace('$', '', substr($extractedRange, $spos));
} else {
$extractedRange = str_replace('$', '', $extractedRange);
}
// Valid range?
if (stripos((string) $definedName, '#REF!') !== false || $extractedRange == '') {
continue;
}
// Some definedNames are only applicable if we are on the same sheet...
if ((string) $definedName['localSheetId'] != '') {
// Local defined name
// Switch on type
switch ((string) $definedName['name']) {
case '_xlnm._FilterDatabase':
case '_xlnm.Print_Titles':
case '_xlnm.Print_Area':
break;
default:
if ($mapSheetId[(int) $definedName['localSheetId']] !== null) {
$range = explode('!', (string) $definedName);
if (count($range) == 2) {
$range[0] = str_replace("''", "'", $range[0]);
$range[0] = str_replace("'", "", $range[0]);
if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) {
$extractedRange = str_replace('$', '', $range[1]);
$scope = $docSheet->getParent()->getSheet($mapSheetId[(int) $definedName['localSheetId']]);
$excel->addNamedRange(new \PHPExcel\NamedRange((string) $definedName['name'], $worksheet, $extractedRange, true, $scope));
}
}
}
break;
}
} elseif (!isset($definedName['localSheetId'])) {
// "Global" definedNames
$locatedSheet = null;
$extractedSheetName = '';
if (strpos((string) $definedName, '!') !== false) {
// Extract sheet name
$extractedSheetName = \PHPExcel\Worksheet::extractSheetTitle((string) $definedName, true);
$extractedSheetName = $extractedSheetName[0];
// Locate sheet
$locatedSheet = $excel->getSheetByName($extractedSheetName);
// Modify range
$range = explode('!', $extractedRange);
$extractedRange = isset($range[1]) ? $range[1] : $range[0];
}
if ($locatedSheet !== null) {
$excel->addNamedRange(new \PHPExcel\NamedRange((string) $definedName['name'], $locatedSheet, $extractedRange, false));
}
}
}
}
}
if (!$this->readDataOnly || !empty($this->loadSheetsOnly)) {
// active sheet index
$activeTab = intval($xmlWorkbook->bookViews->workbookView["activeTab"]);
// refers to old sheet index
// keep active sheet index if sheet is still loaded, else first sheet is set as the active
if (isset($mapSheetId[$activeTab]) && $mapSheetId[$activeTab] !== null) {
$excel->setActiveSheetIndex($mapSheetId[$activeTab]);
} else {
if ($excel->getSheetCount() == 0) {
$excel->createSheet();
}
$excel->setActiveSheetIndex(0);
}
}
break;
}
}
if (!$this->readDataOnly) {
$contentTypes = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, "[Content_Types].xml")), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
foreach ($contentTypes->Override as $contentType) {
switch ($contentType["ContentType"]) {
case "application/vnd.openxmlformats-officedocument.drawingml.chart+xml":
if ($this->includeCharts) {
$chartEntryRef = ltrim($contentType['PartName'], '/');
$chartElements = simplexml_load_string($this->securityScan($this->getFromZipArchive($zip, $chartEntryRef)), 'SimpleXMLElement', \PHPExcel\Settings::getLibXmlLoaderOptions());
$objChart = \PHPExcel\Reader\Excel2007\Chart::readChart($chartElements, basename($chartEntryRef, '.xml'));
// echo 'Chart ', $chartEntryRef, '<br />';
// var_dump($charts[$chartEntryRef]);
//
if (isset($charts[$chartEntryRef])) {
$chartPositionRef = $charts[$chartEntryRef]['sheet'] . '!' . $charts[$chartEntryRef]['id'];
// echo 'Position Ref ', $chartPositionRef, '<br />';
if (isset($chartDetails[$chartPositionRef])) {
// var_dump($chartDetails[$chartPositionRef]);
$excel->getSheetByName($charts[$chartEntryRef]['sheet'])->addChart($objChart);
$objChart->setWorksheet($excel->getSheetByName($charts[$chartEntryRef]['sheet']));
$objChart->setTopLeftPosition($chartDetails[$chartPositionRef]['fromCoordinate'], $chartDetails[$chartPositionRef]['fromOffsetX'], $chartDetails[$chartPositionRef]['fromOffsetY']);
$objChart->setBottomRightPosition($chartDetails[$chartPositionRef]['toCoordinate'], $chartDetails[$chartPositionRef]['toOffsetX'], $chartDetails[$chartPositionRef]['toOffsetY']);
}
}
}
}
}
}
$zip->close();
return $excel;
}
* @version ##VERSION##, ##DATE##
*/
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />');
date_default_timezone_set('Europe/London');
/** Include PHPExcel */
require_once dirname(__FILE__) . '/../src/Bootstrap.php';
echo date('H:i:s'), " Load from Excel5 template", EOL;
$objReader = \PHPExcel\IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load("templates/30template.xls");
echo date('H:i:s'), " Add new data to the template", EOL;
$data = array(array('title' => 'Excel for dummies', 'price' => 17.99, 'quantity' => 2), array('title' => 'PHP for dummies', 'price' => 15.99, 'quantity' => 1), array('title' => 'Inside OOP', 'price' => 12.95, 'quantity' => 1));
$objPHPExcel->getActiveSheet()->setCellValue('D1', \PHPExcel\Shared\Date::PHPToExcel(time()));
$baseRow = 5;
foreach ($data as $r => $dataRow) {
$row = $baseRow + $r;
$objPHPExcel->getActiveSheet()->insertNewRowBefore($row, 1);
$objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $r + 1)->setCellValue('B' . $row, $dataRow['title'])->setCellValue('C' . $row, $dataRow['price'])->setCellValue('D' . $row, $dataRow['quantity'])->setCellValue('E' . $row, '=C' . $row . '*D' . $row);
}
$objPHPExcel->getActiveSheet()->removeRow($baseRow - 1, 1);
echo date('H:i:s'), " Write to Excel5 format", EOL;
$objWriter = \PHPExcel\IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(str_replace('.php', '.xls', __FILE__));
echo date('H:i:s'), " File written to ", str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL;
// Echo memory peak usage
echo date('H:i:s'), " Peak memory usage: ", memory_get_peak_usage(true) / 1024 / 1024, " MB", EOL;
// Echo done
echo date('H:i:s'), " Done writing file", EOL;
