/**
* Push a new entry onto the stack
*
* @param mixed $type
* @param mixed $value
* @param mixed $reference
*/
public function push($type, $value, $reference = null)
{
$this->stack[$this->count++] = array('type' => $type, 'value' => $value, 'reference' => $reference);
if ($type == 'Function') {
$localeFunction = \PHPExcel\Calculation::localeFunc($value);
if ($localeFunction != $value) {
$this->stack[$this->count - 1]['localeValue'] = $localeFunction;
}
}
}
/**
* 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);
}
* @copyright Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
* @version ##VERSION##, ##DATE##
*/
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');
define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />');
date_default_timezone_set('Europe/London');
/** PHPExcel */
require_once dirname(__FILE__) . '/../src/Bootstrap.php';
// List functions
echo date('H:i:s') . " List implemented functions\n";
$objCalc = \PHPExcel\Calculation::getInstance();
print_r($objCalc->listFunctionNames());
// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new \PHPExcel\Spreadsheet();
// Add some data, we will use some formulas here
echo date('H:i:s') . " Add some data\n";
$objPHPExcel->getActiveSheet()->setCellValue('A14', 'Count:');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Range 1');
$objPHPExcel->getActiveSheet()->setCellValue('B2', 2);
$objPHPExcel->getActiveSheet()->setCellValue('B3', 8);
$objPHPExcel->getActiveSheet()->setCellValue('B4', 10);
$objPHPExcel->getActiveSheet()->setCellValue('B5', True);
$objPHPExcel->getActiveSheet()->setCellValue('B6', False);
$objPHPExcel->getActiveSheet()->setCellValue('B7', 'Text String');
$objPHPExcel->getActiveSheet()->setCellValue('B9', '22');
/**
* OFFSET
*
* Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
* The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and
* the number of columns to be returned.
*
* Excel Function:
* =OFFSET(cellAddress, rows, cols, [height], [width])
*
* @param cellAddress The reference from which you want to base the offset. Reference must refer to a cell or
* range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
* @param rows The number of rows, up or down, that you want the upper-left cell to refer to.
* Using 5 as the rows argument specifies that the upper-left cell in the reference is
* five rows below reference. Rows can be positive (which means below the starting reference)
* or negative (which means above the starting reference).
* @param cols The number of columns, to the left or right, that you want the upper-left cell of the result
* to refer to. Using 5 as the cols argument specifies that the upper-left cell in the
* reference is five columns to the right of reference. Cols can be positive (which means
* to the right of the starting reference) or negative (which means to the left of the
* starting reference).
* @param height The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
* @param width The width, in number of columns, that you want the returned reference to be. Width must be a positive number.
* @return string A reference to a cell or range of cells
*/
public static function OFFSET($cellAddress = null, $rows = 0, $columns = 0, $height = null, $width = null)
{
$rows = Functions::flattenSingleValue($rows);
$columns = Functions::flattenSingleValue($columns);
$height = Functions::flattenSingleValue($height);
$width = Functions::flattenSingleValue($width);
if ($cellAddress == null) {
return 0;
}
$args = func_get_args();
$pCell = array_pop($args);
if (!is_object($pCell)) {
return Functions::REF();
}
$sheetName = null;
if (strpos($cellAddress, "!")) {
list($sheetName, $cellAddress) = explode("!", $cellAddress);
$sheetName = trim($sheetName, "'");
}
if (strpos($cellAddress, ":")) {
list($startCell, $endCell) = explode(":", $cellAddress);
} else {
$startCell = $endCell = $cellAddress;
}
list($startCellColumn, $startCellRow) = \PHPExcel\Cell::coordinateFromString($startCell);
list($endCellColumn, $endCellRow) = \PHPExcel\Cell::coordinateFromString($endCell);
$startCellRow += $rows;
$startCellColumn = \PHPExcel\Cell::columnIndexFromString($startCellColumn) - 1;
$startCellColumn += $columns;
if ($startCellRow <= 0 || $startCellColumn < 0) {
return Functions::REF();
}
$endCellColumn = \PHPExcel\Cell::columnIndexFromString($endCellColumn) - 1;
if ($width != null && !is_object($width)) {
$endCellColumn = $startCellColumn + $width - 1;
} else {
$endCellColumn += $columns;
}
$startCellColumn = \PHPExcel\Cell::stringFromColumnIndex($startCellColumn);
if ($height != null && !is_object($height)) {
$endCellRow = $startCellRow + $height - 1;
} else {
$endCellRow += $rows;
}
if ($endCellRow <= 0 || $endCellColumn < 0) {
return Functions::REF();
}
$endCellColumn = \PHPExcel\Cell::stringFromColumnIndex($endCellColumn);
$cellAddress = $startCellColumn . $startCellRow;
if ($startCellColumn != $endCellColumn || $startCellRow != $endCellRow) {
$cellAddress .= ':' . $endCellColumn . $endCellRow;
}
if ($sheetName !== null) {
$pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName);
} else {
$pSheet = $pCell->getWorksheet();
}
return \PHPExcel\Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, false);
}
/**
* Apply the AutoFilter rules to the AutoFilter Range
*
* @throws \PHPExcel\Exception
* @return AutoFilter
*/
public function showHideRows()
{
list($rangeStart, $rangeEnd) = \PHPExcel\Cell::rangeBoundaries($this->range);
// The heading row should always be visible
// echo 'AutoFilter Heading Row ', $rangeStart[1],' is always SHOWN',PHP_EOL;
$this->workSheet->getRowDimension($rangeStart[1])->setVisible(true);
$columnFilterTests = array();
foreach ($this->columns as $columnID => $filterColumn) {
$rules = $filterColumn->getRules();
switch ($filterColumn->getFilterType()) {
case AutoFilter\Column::AUTOFILTER_FILTERTYPE_FILTER:
$ruleValues = array();
// Build a list of the filter value selections
foreach ($rules as $rule) {
$ruleType = $rule->getRuleType();
$ruleValues[] = $rule->getValue();
}
// Test if we want to include blanks in our filter criteria
$blanks = false;
$ruleDataSet = array_filter($ruleValues);
if (count($ruleValues) != count($ruleDataSet)) {
$blanks = true;
}
if ($ruleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_FILTER) {
// Filter on absolute values
$columnFilterTests[$columnID] = array('method' => 'filterTestInSimpleDataSet', 'arguments' => array('filterValues' => $ruleDataSet, 'blanks' => $blanks));
} else {
// Filter on date group values
$arguments = array('date' => array(), 'time' => array(), 'dateTime' => array());
foreach ($ruleDataSet as $ruleValue) {
$date = $time = '';
if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR] !== '') {
$date .= sprintf('%04d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_YEAR]);
}
if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH] != '') {
$date .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MONTH]);
}
if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY] !== '') {
$date .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_DAY]);
}
if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR] !== '') {
$time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_HOUR]);
}
if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE] !== '') {
$time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_MINUTE]);
}
if (isset($ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]) && $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND] !== '') {
$time .= sprintf('%02d', $ruleValue[AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DATEGROUP_SECOND]);
}
$dateTime = $date . $time;
$arguments['date'][] = $date;
$arguments['time'][] = $time;
$arguments['dateTime'][] = $dateTime;
}
// Remove empty elements
$arguments['date'] = array_filter($arguments['date']);
$arguments['time'] = array_filter($arguments['time']);
$arguments['dateTime'] = array_filter($arguments['dateTime']);
$columnFilterTests[$columnID] = array('method' => 'filterTestInDateGroupSet', 'arguments' => array('filterValues' => $arguments, 'blanks' => $blanks));
}
break;
case AutoFilter\Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER:
$customRuleForBlanks = false;
$ruleValues = array();
// Build a list of the filter value selections
foreach ($rules as $rule) {
$ruleType = $rule->getRuleType();
$ruleValue = $rule->getValue();
if (!is_numeric($ruleValue)) {
// Convert to a regexp allowing for regexp reserved characters, wildcards and escaped wildcards
$ruleValue = preg_quote($ruleValue);
$ruleValue = str_replace(self::$fromReplace, self::$toReplace, $ruleValue);
if (trim($ruleValue) == '') {
$customRuleForBlanks = true;
$ruleValue = trim($ruleValue);
}
}
$ruleValues[] = array('operator' => $rule->getOperator(), 'value' => $ruleValue);
}
$join = $filterColumn->getJoin();
$columnFilterTests[$columnID] = array('method' => 'filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => $join, 'customRuleForBlanks' => $customRuleForBlanks));
break;
case AutoFilter\Column::AUTOFILTER_FILTERTYPE_DYNAMICFILTER:
$ruleValues = array();
foreach ($rules as $rule) {
// We should only ever have one Dynamic Filter Rule anyway
$dynamicRuleType = $rule->getGrouping();
if ($dynamicRuleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE || $dynamicRuleType == AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE) {
// Number (Average) based
// Calculate the average
$averageFormula = '=AVERAGE(' . $columnID . ($rangeStart[1] + 1) . ':' . $columnID . $rangeEnd[1] . ')';
$average = \PHPExcel\Calculation::getInstance()->calculateFormula($averageFormula, null, $this->workSheet->getCell('A1'));
// Set above/below rule based on greaterThan or LessTan
$operator = $dynamicRuleType === AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE ? AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHAN : AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN;
$ruleValues[] = array('operator' => $operator, 'value' => $average);
$columnFilterTests[$columnID] = array('method' => 'filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR));
} else {
// Date based
if ($dynamicRuleType[0] == 'M' || $dynamicRuleType[0] == 'Q') {
// Month or Quarter
sscanf($dynamicRuleType, '%[A-Z]%d', $periodType, $period);
if ($periodType == 'M') {
$ruleValues = array($period);
} else {
--$period;
$periodEnd = (1 + $period) * 3;
$periodStart = 1 + $period * 3;
$ruleValues = range($periodStart, $periodEnd);
}
$columnFilterTests[$columnID] = array('method' => 'filterTestInPeriodDateSet', 'arguments' => $ruleValues);
$filterColumn->setAttributes(array());
} else {
// Date Range
$columnFilterTests[$columnID] = $this->dynamicFilterDateRange($dynamicRuleType, $filterColumn);
break;
}
}
}
break;
case AutoFilter\Column::AUTOFILTER_FILTERTYPE_TOPTENFILTER:
$ruleValues = array();
$dataRowCount = $rangeEnd[1] - $rangeStart[1];
foreach ($rules as $rule) {
// We should only ever have one Dynamic Filter Rule anyway
$toptenRuleType = $rule->getGrouping();
$ruleValue = $rule->getValue();
$ruleOperator = $rule->getOperator();
}
if ($ruleOperator === AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT) {
$ruleValue = floor($ruleValue * ($dataRowCount / 100));
}
if ($ruleValue < 1) {
$ruleValue = 1;
}
if ($ruleValue > 500) {
$ruleValue = 500;
}
$maxVal = $this->calculateTopTenValue($columnID, $rangeStart[1] + 1, $rangeEnd[1], $toptenRuleType, $ruleValue);
$operator = $toptenRuleType == AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP ? AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL : AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL;
$ruleValues[] = array('operator' => $operator, 'value' => $maxVal);
$columnFilterTests[$columnID] = array('method' => 'filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => AutoFilter\Column::AUTOFILTER_COLUMN_JOIN_OR));
$filterColumn->setAttributes(array('maxVal' => $maxVal));
break;
}
}
// echo 'Column Filter Test CRITERIA',PHP_EOL;
// var_dump($columnFilterTests);
//
// Execute the column tests for each row in the autoFilter range to determine show/hide,
for ($row = $rangeStart[1] + 1; $row <= $rangeEnd[1]; ++$row) {
// echo 'Testing Row = ', $row,PHP_EOL;
$result = true;
foreach ($columnFilterTests as $columnID => $columnFilterTest) {
// echo 'Testing cell ', $columnID.$row,PHP_EOL;
$cellValue = $this->workSheet->getCell($columnID . $row)->getCalculatedValue();
// echo 'Value is ', $cellValue,PHP_EOL;
// Execute the filter test
$result = $result && call_user_func_array(array('\\PHPExcel\\Worksheet\\AutoFilter', $columnFilterTest['method']), array($cellValue, $columnFilterTest['arguments']));
// echo (($result) ? 'VALID' : 'INVALID'),PHP_EOL;
// If filter test has resulted in FALSE, exit the loop straightaway rather than running any more tests
if (!$result) {
break;
}
}
// Set show/hide for the row based on the result of the autoFilter result
// echo (($result) ? 'SHOW' : 'HIDE'),PHP_EOL;
$this->workSheet->getRowDimension($row)->setVisible($result);
}
return $this;
}
/**
* MINIF
*
* Returns the minimum value within a range of cells that contain numbers within the list of arguments
*
* Excel Function:
* MINIF(value1[,value2[, ...]],condition)
*
* @access public
* @category Mathematical and Trigonometric Functions
* @param mixed $arg,... Data values
* @param string $condition The criteria that defines which cells will be checked.
* @return float
*/
public static function MINIF($aArgs, $condition, $sumArgs = array())
{
$returnValue = null;
$aArgs = Functions::flattenArray($aArgs);
$sumArgs = Functions::flattenArray($sumArgs);
if (empty($sumArgs)) {
$sumArgs = $aArgs;
}
$condition = Functions::ifCondition($condition);
// Loop through arguments
foreach ($aArgs as $key => $arg) {
if (!is_numeric($arg)) {
$arg = \PHPExcel\Calculation::wrapResult(strtoupper($arg));
}
$testCondition = '=' . $arg . $condition;
if (\PHPExcel\Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
if (is_null($returnValue) || $arg < $returnValue) {
$returnValue = $arg;
}
}
}
return $returnValue;
}
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param \PHPExcel\Spreadsheet $objPHPExcel
* @return \PHPExcel\Spreadsheet
* @throws Exception
*/
public function loadIntoExisting($pFilename, \PHPExcel\Spreadsheet $objPHPExcel)
{
// Open file
$this->openFile($pFilename);
if (!$this->isValidFormat()) {
fclose($this->fileHandle);
throw new Exception($pFilename . " is an Invalid Spreadsheet file.");
}
$fileHandle = $this->fileHandle;
rewind($fileHandle);
// Create new Worksheets
while ($objPHPExcel->getSheetCount() <= $this->sheetIndex) {
$objPHPExcel->createSheet();
}
$objPHPExcel->setActiveSheetIndex($this->sheetIndex);
$fromFormats = array('\\-', '\\ ');
$toFormats = array('-', ' ');
// Loop through file
$rowData = array();
$column = $row = '';
// loop through one row (line) at a time in the file
while (($rowData = fgets($fileHandle)) !== false) {
// convert SYLK encoded $rowData to UTF-8
$rowData = \PHPExcel\Shared\StringHelper::SYLKtoUTF8($rowData);
// explode each row at semicolons while taking into account that literal semicolon (;)
// is escaped like this (;;)
$rowData = explode("\t", str_replace('¤', ';', str_replace(';', "\t", str_replace(';;', '¤', rtrim($rowData)))));
$dataType = array_shift($rowData);
// Read shared styles
if ($dataType == 'P') {
$formatArray = array();
foreach ($rowData as $rowDatum) {
switch ($rowDatum[0]) {
case 'P':
$formatArray['numberformat']['code'] = str_replace($fromFormats, $toFormats, substr($rowDatum, 1));
break;
case 'E':
case 'F':
$formatArray['font']['name'] = substr($rowDatum, 1);
break;
case 'L':
$formatArray['font']['size'] = substr($rowDatum, 1);
break;
case 'S':
$styleSettings = substr($rowDatum, 1);
for ($i = 0; $i < strlen($styleSettings); ++$i) {
switch ($styleSettings[$i]) {
case 'I':
$formatArray['font']['italic'] = true;
break;
case 'D':
$formatArray['font']['bold'] = true;
break;
case 'T':
$formatArray['borders']['top']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
break;
case 'B':
$formatArray['borders']['bottom']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
break;
case 'L':
$formatArray['borders']['left']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
break;
case 'R':
$formatArray['borders']['right']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
break;
}
}
break;
}
}
$this->formats['P' . $this->format++] = $formatArray;
// Read cell value data
} elseif ($dataType == 'C') {
$hasCalculatedValue = false;
$cellData = $cellDataFormula = '';
foreach ($rowData as $rowDatum) {
switch ($rowDatum[0]) {
case 'C':
case 'X':
$column = substr($rowDatum, 1);
break;
case 'R':
case 'Y':
$row = substr($rowDatum, 1);
break;
case 'K':
$cellData = substr($rowDatum, 1);
break;
case 'E':
$cellDataFormula = '=' . substr($rowDatum, 1);
// Convert R1C1 style references to A1 style references (but only when not quoted)
$temp = explode('"', $cellDataFormula);
$key = false;
foreach ($temp as &$value) {
// Only count/replace in alternate array entries
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 = $row;
}
// Bracketed R references are relative to the current row
if ($rowReference[0] == '[') {
$rowReference = $row + trim($rowReference, '[]');
}
$columnReference = $cellReference[4][0];
// Empty C reference is the current column
if ($columnReference == '') {
$columnReference = $column;
}
// Bracketed C references are relative to the current column
if ($columnReference[0] == '[') {
$columnReference = $column + 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);
$hasCalculatedValue = true;
break;
}
}
$columnLetter = \PHPExcel\Cell::stringFromColumnIndex($column - 1);
$cellData = \PHPExcel\Calculation::unwrapResult($cellData);
// Set cell value
$objPHPExcel->getActiveSheet()->getCell($columnLetter . $row)->setValue($hasCalculatedValue ? $cellDataFormula : $cellData);
if ($hasCalculatedValue) {
$cellData = \PHPExcel\Calculation::unwrapResult($cellData);
$objPHPExcel->getActiveSheet()->getCell($columnLetter . $row)->setCalculatedValue($cellData);
}
// Read cell formatting
} elseif ($dataType == 'F') {
$formatStyle = $columnWidth = $styleSettings = '';
$styleData = array();
foreach ($rowData as $rowDatum) {
switch ($rowDatum[0]) {
case 'C':
case 'X':
$column = substr($rowDatum, 1);
break;
case 'R':
case 'Y':
$row = substr($rowDatum, 1);
break;
case 'P':
$formatStyle = $rowDatum;
break;
case 'W':
list($startCol, $endCol, $columnWidth) = explode(' ', substr($rowDatum, 1));
break;
case 'S':
$styleSettings = substr($rowDatum, 1);
for ($i = 0; $i < strlen($styleSettings); ++$i) {
switch ($styleSettings[$i]) {
case 'I':
$styleData['font']['italic'] = true;
break;
case 'D':
$styleData['font']['bold'] = true;
break;
case 'T':
$styleData['borders']['top']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
break;
case 'B':
$styleData['borders']['bottom']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
break;
case 'L':
$styleData['borders']['left']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
break;
case 'R':
$styleData['borders']['right']['style'] = \PHPExcel\Style\Border::BORDER_THIN;
break;
}
}
break;
}
}
if ($formatStyle > '' && $column > '' && $row > '') {
$columnLetter = \PHPExcel\Cell::stringFromColumnIndex($column - 1);
if (isset($this->formats[$formatStyle])) {
$objPHPExcel->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($this->formats[$formatStyle]);
}
}
if (!empty($styleData) && $column > '' && $row > '') {
$columnLetter = \PHPExcel\Cell::stringFromColumnIndex($column - 1);
$objPHPExcel->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($styleData);
}
if ($columnWidth > '') {
if ($startCol == $endCol) {
$startCol = \PHPExcel\Cell::stringFromColumnIndex($startCol - 1);
$objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
} else {
$startCol = \PHPExcel\Cell::stringFromColumnIndex($startCol - 1);
$endCol = \PHPExcel\Cell::stringFromColumnIndex($endCol - 1);
$objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
do {
$objPHPExcel->getActiveSheet()->getColumnDimension(++$startCol)->setWidth($columnWidth);
} while ($startCol != $endCol);
}
}
} else {
foreach ($rowData as $rowDatum) {
switch ($rowDatum[0]) {
case 'C':
case 'X':
$column = substr($rowDatum, 1);
break;
case 'R':
case 'Y':
$row = substr($rowDatum, 1);
break;
}
}
}
}
// Close file
fclose($fileHandle);
// Return
return $objPHPExcel;
}
/**
* SUMIFS
*
* Counts the number of cells that contain numbers within the list of arguments
*
* Excel Function:
* SUMIFS(value1[,value2[, ...]],condition)
*
* @access public
* @category Mathematical and Trigonometric Functions
* @param mixed $arg,... Data values
* @param string $condition The criteria that defines which cells will be summed.
* @return float
*/
public static function SUMIFS()
{
$arrayList = func_get_args();
// Return value
$returnValue = 0;
$sumArgs = Functions::flattenArray(array_shift($arrayList));
while (count($arrayList) > 0) {
$aArgsArray[] = Functions::flattenArray(array_shift($arrayList));
$conditions[] = Functions::ifCondition(array_shift($arrayList));
}
// Loop through each set of arguments and conditions
foreach ($conditions as $index => $condition) {
$aArgs = $aArgsArray[$index];
// Loop through arguments
foreach ($aArgs as $key => $arg) {
if (!is_numeric($arg)) {
$arg = \PHPExcel\Calculation::wrapResult(strtoupper($arg));
}
$testCondition = '=' . $arg . $condition;
if (\PHPExcel\Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
// Is it a value within our criteria
$returnValue += $sumArgs[$key];
}
}
}
// Return
return $returnValue;
}
public function refresh(\PHPExcel\Worksheet $worksheet, $flatten = true)
{
if ($this->dataSource !== null) {
$calcEngine = \PHPExcel\Calculation::getInstance($worksheet->getParent());
$newDataValues = \PHPExcel\Calculation::unwrapResult($calcEngine->_calculateFormulaValue('=' . $this->dataSource, null, $worksheet->getCell('A1')));
if ($flatten) {
$this->dataValues = \PHPExcel\Calculation\Functions::flattenArray($newDataValues);
foreach ($this->dataValues as &$dataValue) {
if (!empty($dataValue) && $dataValue[0] == '#') {
$dataValue = 0.0;
}
}
unset($dataValue);
} else {
$cellRange = explode('!', $this->dataSource);
if (count($cellRange) > 1) {
list(, $cellRange) = $cellRange;
}
$dimensions = \PHPExcel\Cell::rangeDimension(str_replace('$', '', $cellRange));
if ($dimensions[0] == 1 || $dimensions[1] == 1) {
$this->dataValues = \PHPExcel\Calculation\Functions::flattenArray($newDataValues);
} else {
$newArray = array_values(array_shift($newDataValues));
foreach ($newArray as $i => $newDataSet) {
$newArray[$i] = array($newDataSet);
}
foreach ($newDataValues as $newDataSet) {
$i = 0;
foreach ($newDataSet as $newDataVal) {
array_unshift($newArray[$i++], $newDataVal);
}
}
$this->dataValues = $newArray;
}
}
$this->pointCount = count($this->dataValues);
}
}
/**
* Save PHPExcel to file
*
* @param string $pFilename
* @throws Exception
*/
public function save($pFilename = null)
{
// Fetch sheet
$sheet = $this->phpExcel->getSheet($this->sheetIndex);
$saveDebugLog = \PHPExcel\Calculation::getInstance($this->phpExcel)->getDebugLog()->getWriteDebugLog();
\PHPExcel\Calculation::getInstance($this->phpExcel)->getDebugLog()->setWriteDebugLog(false);
$saveArrayReturnType = \PHPExcel\Calculation::getArrayReturnType();
\PHPExcel\Calculation::setArrayReturnType(\PHPExcel\Calculation::RETURN_ARRAY_AS_VALUE);
// Open file
$fileHandle = fopen($pFilename, 'wb+');
if ($fileHandle === false) {
throw new Exception("Could not open file {$pFilename} for writing.");
}
if ($this->excelCompatibility) {
$this->setUseBOM(true);
// Enforce UTF-8 BOM Header
$this->setIncludeSeparatorLine(true);
// Set separator line
$this->setEnclosure('"');
// Set enclosure to "
$this->setDelimiter(";");
// Set delimiter to a semi-colon
$this->setLineEnding("\r\n");
}
if ($this->useBOM) {
// Write the UTF-8 BOM code if required
fwrite($fileHandle, "");
}
if ($this->includeSeparatorLine) {
// Write the separator line if required
fwrite($fileHandle, 'sep=' . $this->getDelimiter() . $this->lineEnding);
}
// Identify the range that we need to extract from the worksheet
$maxCol = $sheet->getHighestDataColumn();
$maxRow = $sheet->getHighestDataRow();
// Write rows to file
for ($row = 1; $row <= $maxRow; ++$row) {
// Convert the row to an array...
$cellsArray = $sheet->rangeToArray('A' . $row . ':' . $maxCol . $row, '', $this->preCalculateFormulas);
// ... and write to the file
$this->writeLine($fileHandle, $cellsArray[0]);
}
// Close file
fclose($fileHandle);
\PHPExcel\Calculation::setArrayReturnType($saveArrayReturnType);
\PHPExcel\Calculation::getInstance($this->phpExcel)->getDebugLog()->setWriteDebugLog($saveDebugLog);
}
/**
* 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;
}
/**
* filter
*
* Parses the selection criteria, extracts the database rows that match those criteria, and
* returns that subset of rows.
*
* @access private
* @param mixed[] $database The range of cells that makes up the list or database.
* A database is a list of related data in which rows of related
* information are records, and columns of data are fields. The
* first row of the list contains labels for each column.
* @param mixed[] $criteria The range of cells that contains the conditions you specify.
* You can use any range for the criteria argument, as long as it
* includes at least one column label and at least one cell below
* the column label in which you specify a condition for the
* column.
* @return array of mixed
*
*/
private static function filter($database, $criteria)
{
$fieldNames = array_shift($database);
$criteriaNames = array_shift($criteria);
// Convert the criteria into a set of AND/OR conditions with [:placeholders]
$testConditions = $testValues = array();
$testConditionsCount = 0;
foreach ($criteriaNames as $key => $criteriaName) {
$testCondition = array();
$testConditionCount = 0;
foreach ($criteria as $row => $criterion) {
if ($criterion[$key] > '') {
$testCondition[] = '[:' . $criteriaName . ']' . Functions::ifCondition($criterion[$key]);
$testConditionCount++;
}
}
if ($testConditionCount > 1) {
$testConditions[] = 'OR(' . implode(',', $testCondition) . ')';
$testConditionsCount++;
} elseif ($testConditionCount == 1) {
$testConditions[] = $testCondition[0];
$testConditionsCount++;
}
}
if ($testConditionsCount > 1) {
$testConditionSet = 'AND(' . implode(',', $testConditions) . ')';
} elseif ($testConditionsCount == 1) {
$testConditionSet = $testConditions[0];
}
// Loop through each row of the database
foreach ($database as $dataRow => $dataValues) {
// Substitute actual values from the database row for our [:placeholders]
$testConditionList = $testConditionSet;
foreach ($criteriaNames as $key => $criteriaName) {
$k = array_search($criteriaName, $fieldNames);
if (isset($dataValues[$k])) {
$dataValue = $dataValues[$k];
$dataValue = is_string($dataValue) ? \PHPExcel\Calculation::wrapResult(strtoupper($dataValue)) : $dataValue;
$testConditionList = str_replace('[:' . $criteriaName . ']', $dataValue, $testConditionList);
}
}
// evaluate the criteria against the row data
$result = \PHPExcel\Calculation::getInstance()->_calculateFormulaValue('=' . $testConditionList);
// If the row failed to meet the criteria, remove it from the database
if (!$result) {
unset($database[$dataRow]);
}
}
return $database;
}
/**
* Save Spreadsheet to file
*
* @param string $pFilename
* @throws \PHPExcel\Writer\Exception
*/
public function save($pFilename = null)
{
// garbage collect
$this->phpExcel->garbageCollect();
$saveDebugLog = \PHPExcel\Calculation::getInstance($this->phpExcel)->getDebugLog()->getWriteDebugLog();
\PHPExcel\Calculation::getInstance($this->phpExcel)->getDebugLog()->setWriteDebugLog(false);
$saveDateReturnType = \PHPExcel\Calculation\Functions::getReturnDateType();
\PHPExcel\Calculation\Functions::setReturnDateType(\PHPExcel\Calculation\Functions::RETURNDATE_EXCEL);
// initialize colors array
$this->colors = array();
// Initialise workbook writer
$this->writerWorkbook = new Excel5\Workbook($this->phpExcel, $this->strTotal, $this->strUnique, $this->strTable, $this->colors, $this->parser);
// Initialise worksheet writers
$countSheets = $this->phpExcel->getSheetCount();
for ($i = 0; $i < $countSheets; ++$i) {
$this->writerWorksheets[$i] = new Excel5\Worksheet($this->strTotal, $this->strUnique, $this->strTable, $this->colors, $this->parser, $this->preCalculateFormulas, $this->phpExcel->getSheet($i));
}
// build Escher objects. Escher objects for workbooks needs to be build before Escher object for workbook.
$this->buildWorksheetEschers();
$this->buildWorkbookEscher();
// add 15 identical cell style Xfs
// for now, we use the first cellXf instead of cellStyleXf
$cellXfCollection = $this->phpExcel->getCellXfCollection();
for ($i = 0; $i < 15; ++$i) {
$this->writerWorkbook->addXfWriter($cellXfCollection[0], true);
}
// add all the cell Xfs
foreach ($this->phpExcel->getCellXfCollection() as $style) {
$this->writerWorkbook->addXfWriter($style, false);
}
// add fonts from rich text eleemnts
for ($i = 0; $i < $countSheets; ++$i) {
foreach ($this->writerWorksheets[$i]->phpSheet->getCellCollection() as $cellID) {
$cell = $this->writerWorksheets[$i]->phpSheet->getCell($cellID);
$cVal = $cell->getValue();
if ($cVal instanceof \PHPExcel\RichText) {
$elements = $cVal->getRichTextElements();
foreach ($elements as $element) {
if ($element instanceof \PHPExcel\RichText\Run) {
$font = $element->getFont();
$this->writerWorksheets[$i]->fontHashIndex[$font->getHashCode()] = $this->writerWorkbook->addFont($font);
}
}
}
}
}
// initialize OLE file
$workbookStreamName = 'Workbook';
$OLE = new \PHPExcel\Shared\OLE\PPS\File(\PHPExcel\Shared\OLE::ascToUcs($workbookStreamName));
// Write the worksheet streams before the global workbook stream,
// because the byte sizes of these are needed in the global workbook stream
$worksheetSizes = array();
for ($i = 0; $i < $countSheets; ++$i) {
$this->writerWorksheets[$i]->close();
$worksheetSizes[] = $this->writerWorksheets[$i]->_datasize;
}
// add binary data for global workbook stream
$OLE->append($this->writerWorkbook->writeWorkbook($worksheetSizes));
// add binary data for sheet streams
for ($i = 0; $i < $countSheets; ++$i) {
$OLE->append($this->writerWorksheets[$i]->getData());
}
$this->documentSummaryInformation = $this->writeDocumentSummaryInformation();
// initialize OLE Document Summary Information
if (isset($this->documentSummaryInformation) && !empty($this->documentSummaryInformation)) {
$OLE_DocumentSummaryInformation = new \PHPExcel\Shared\OLE\PPS\File(\PHPExcel\Shared\OLE::ascToUcs(chr(5) . 'DocumentSummaryInformation'));
$OLE_DocumentSummaryInformation->append($this->documentSummaryInformation);
}
$this->summaryInformation = $this->writeSummaryInformation();
// initialize OLE Summary Information
if (isset($this->summaryInformation) && !empty($this->summaryInformation)) {
$OLE_SummaryInformation = new \PHPExcel\Shared\OLE\PPS\File(\PHPExcel\Shared\OLE::ascToUcs(chr(5) . 'SummaryInformation'));
$OLE_SummaryInformation->append($this->summaryInformation);
}
// define OLE Parts
$arrRootData = array($OLE);
// initialize OLE Properties file
if (isset($OLE_SummaryInformation)) {
$arrRootData[] = $OLE_SummaryInformation;
}
// initialize OLE Extended Properties file
if (isset($OLE_DocumentSummaryInformation)) {
$arrRootData[] = $OLE_DocumentSummaryInformation;
}
$root = new \PHPExcel\Shared\OLE\PPS\Root(time(), time(), $arrRootData);
// save the OLE file
$res = $root->save($pFilename);
\PHPExcel\Calculation\Functions::setReturnDateType($saveDateReturnType);
\PHPExcel\Calculation::getInstance($this->phpExcel)->getDebugLog()->setWriteDebugLog($saveDebugLog);
}
/**
* NOT
*
* Returns the boolean inverse of the argument.
*
* Excel Function:
* =NOT(logical)
*
* The argument must evaluate to a logical value such as TRUE or FALSE
*
* Boolean arguments are treated as True or False as appropriate
* Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
* If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
* the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
*
* @access public
* @category Logical Functions
* @param mixed $logical A value or expression that can be evaluated to TRUE or FALSE
* @return boolean The boolean inverse of the argument.
*/
public static function NOT($logical = false)
{
$logical = Functions::flattenSingleValue($logical);
if (is_string($logical)) {
$logical = strtoupper($logical);
if ($logical == 'TRUE' || $logical == \PHPExcel\Calculation::getTRUE()) {
return false;
} elseif ($logical == 'FALSE' || $logical == \PHPExcel\Calculation::getFALSE()) {
return true;
} else {
return Functions::VALUE();
}
}
return !$logical;
}
/**
* PROPERCASE
*
* Converts a string value to upper case.
*
* @param string $mixedCaseString
* @return string
*/
public static function PROPERCASE($mixedCaseString)
{
$mixedCaseString = Functions::flattenSingleValue($mixedCaseString);
if (is_bool($mixedCaseString)) {
$mixedCaseString = $mixedCaseString ? \PHPExcel\Calculation::getTRUE() : \PHPExcel\Calculation::getFALSE();
}
return \PHPExcel\Shared\StringHelper::strToTitle($mixedCaseString);
}
public static function ifCondition($condition)
{
$condition = Functions::flattenSingleValue($condition);
if (!isset($condition[0])) {
$condition = '=""';
}
if (!in_array($condition[0], array('>', '<', '='))) {
if (!is_numeric($condition)) {
$condition = \PHPExcel\Calculation::wrapResult(strtoupper($condition));
}
return '=' . $condition;
} else {
preg_match('/([<>=]+)(.*)/', $condition, $matches);
list(, $operator, $operand) = $matches;
if (!is_numeric($operand)) {
$operand = str_replace('"', '""', $operand);
$operand = \PHPExcel\Calculation::wrapResult(strtoupper($operand));
}
return $operator . $operand;
}
}
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');
define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />');
date_default_timezone_set('Europe/London');
/** 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();
// Add some data, we will use some formulas here
echo date('H:i:s'), " Add some data and formulas", EOL;
$objPHPExcel->getActiveSheet()->setCellValue('A1', '=B1')->setCellValue('A2', '=B2+1')->setCellValue('B1', '=A1+1')->setCellValue('B2', '=A2');
\PHPExcel\Calculation::getInstance($objPHPExcel)->cyclicFormulaCount = 100;
// Calculated data
echo date('H:i:s'), " Calculated data", EOL;
for ($row = 1; $row <= 2; ++$row) {
for ($col = 'A'; $col != 'C'; ++$col) {
if (!is_null($formula = $objPHPExcel->getActiveSheet()->getCell($col . $row)->getValue()) && $formula[0] == '=') {
echo 'Value of ', $col, $row, ' [', $formula, ']: ', $objPHPExcel->getActiveSheet()->getCell($col . $row)->getCalculatedValue() . EOL;
}
}
}
// Save Excel 2007 file
echo date('H:i:s'), " Write to Excel2007 format", EOL;
$callStartTime = microtime(true);
$objWriter = \PHPExcel\IOFactory::createWriter($objPHPExcel, 'Excel2007');
//
// If we set Pre Calculated Formulas to true then PHPExcel will calculate all formulae in the
/**
* Identify whether a string contains a fractional numeric value,
* and convert it to a numeric if it is
*
* @param string &$operand string value to test
* @return boolean
*/
public static function convertToNumberIfFraction(&$operand)
{
if (preg_match('/^' . self::STRING_REGEXP_FRACTION . '$/i', $operand, $match)) {
$sign = $match[1] == '-' ? '-' : '+';
$fractionFormula = '=' . $sign . $match[2] . $sign . $match[3];
$operand = \PHPExcel\Calculation::getInstance()->_calculateFormulaValue($fractionFormula);
return true;
}
return false;
}
/**
* Save PHPExcel to file
*
* @param string $pFilename
* @throws \PHPExcel\Writer\Exception
*/
public function save($pFilename = null)
{
if ($this->spreadSheet !== null) {
// garbage collect
$this->spreadSheet->garbageCollect();
// If $pFilename is php://output or php://stdout, make it a temporary file...
$originalFilename = $pFilename;
if (strtolower($pFilename) == 'php://output' || strtolower($pFilename) == 'php://stdout') {
$pFilename = @tempnam(\PHPExcel\Shared\File::sysGetTempDir(), 'phpxltmp');
if ($pFilename == '') {
$pFilename = $originalFilename;
}
}
$saveDebugLog = \PHPExcel\Calculation::getInstance($this->spreadSheet)->getDebugLog()->getWriteDebugLog();
\PHPExcel\Calculation::getInstance($this->spreadSheet)->getDebugLog()->setWriteDebugLog(false);
$saveDateReturnType = \PHPExcel\Calculation\Functions::getReturnDateType();
\PHPExcel\Calculation\Functions::setReturnDateType(\PHPExcel\Calculation\Functions::RETURNDATE_EXCEL);
// Create string lookup table
$this->stringTable = array();
for ($i = 0; $i < $this->spreadSheet->getSheetCount(); ++$i) {
$this->stringTable = $this->getWriterPart('StringTable')->createStringTable($this->spreadSheet->getSheet($i), $this->stringTable);
}
// Create styles dictionaries
$this->styleHashTable->addFromSource($this->getWriterPart('Style')->allStyles($this->spreadSheet));
$this->stylesConditionalHashTable->addFromSource($this->getWriterPart('Style')->allConditionalStyles($this->spreadSheet));
$this->fillHashTable->addFromSource($this->getWriterPart('Style')->allFills($this->spreadSheet));
$this->fontHashTable->addFromSource($this->getWriterPart('Style')->allFonts($this->spreadSheet));
$this->bordersHashTable->addFromSource($this->getWriterPart('Style')->allBorders($this->spreadSheet));
$this->numFmtHashTable->addFromSource($this->getWriterPart('Style')->allNumberFormats($this->spreadSheet));
// Create drawing dictionary
$this->drawingHashTable->addFromSource($this->getWriterPart('Drawing')->allDrawings($this->spreadSheet));
// Create new ZIP file and open it for writing
$zipClass = \PHPExcel\Settings::getZipClass();
/** @var \ZipArchive $objZip */
$objZip = new $zipClass();
// Retrieve OVERWRITE and CREATE constants from the instantiated zip class
// This method of accessing constant values from a dynamic class should work with all appropriate versions of PHP
$ro = new \ReflectionObject($objZip);
$zipOverWrite = $ro->getConstant('OVERWRITE');
$zipCreate = $ro->getConstant('CREATE');
if (file_exists($pFilename)) {
unlink($pFilename);
}
// Try opening the ZIP file
if ($objZip->open($pFilename, $zipOverWrite) !== true) {
if ($objZip->open($pFilename, $zipCreate) !== true) {
throw new \PHPExcel\Writer\Exception("Could not open " . $pFilename . " for writing.");
}
}
// Add [Content_Types].xml to ZIP file
$objZip->addFromString('[Content_Types].xml', $this->getWriterPart('ContentTypes')->writeContentTypes($this->spreadSheet, $this->includeCharts));
//if hasMacros, add the vbaProject.bin file, Certificate file(if exists)
if ($this->spreadSheet->hasMacros()) {
$macrosCode = $this->spreadSheet->getMacrosCode();
if (!is_null($macrosCode)) {
// we have the code ?
$objZip->addFromString('xl/vbaProject.bin', $macrosCode);
//allways in 'xl', allways named vbaProject.bin
if ($this->spreadSheet->hasMacrosCertificate()) {
//signed macros ?
// Yes : add the certificate file and the related rels file
$objZip->addFromString('xl/vbaProjectSignature.bin', $this->spreadSheet->getMacrosCertificate());
$objZip->addFromString('xl/_rels/vbaProject.bin.rels', $this->getWriterPart('RelsVBA')->writeVBARelationships($this->spreadSheet));
}
}
}
//a custom UI in this workbook ? add it ("base" xml and additional objects (pictures) and rels)
if ($this->spreadSheet->hasRibbon()) {
$tmpRibbonTarget = $this->spreadSheet->getRibbonXMLData('target');
$objZip->addFromString($tmpRibbonTarget, $this->spreadSheet->getRibbonXMLData('data'));
if ($this->spreadSheet->hasRibbonBinObjects()) {
$tmpRootPath = dirname($tmpRibbonTarget) . '/';
$ribbonBinObjects = $this->spreadSheet->getRibbonBinObjects('data');
//the files to write
foreach ($ribbonBinObjects as $aPath => $aContent) {
$objZip->addFromString($tmpRootPath . $aPath, $aContent);
}
//the rels for files
$objZip->addFromString($tmpRootPath . '_rels/' . basename($tmpRibbonTarget) . '.rels', $this->getWriterPart('RelsRibbonObjects')->writeRibbonRelationships($this->spreadSheet));
}
}
// Add relationships to ZIP file
$objZip->addFromString('_rels/.rels', $this->getWriterPart('Rels')->writeRelationships($this->spreadSheet));
$objZip->addFromString('xl/_rels/workbook.xml.rels', $this->getWriterPart('Rels')->writeWorkbookRelationships($this->spreadSheet));
// Add document properties to ZIP file
$objZip->addFromString('docProps/app.xml', $this->getWriterPart('DocProps')->writeDocPropsApp($this->spreadSheet));
$objZip->addFromString('docProps/core.xml', $this->getWriterPart('DocProps')->writeDocPropsCore($this->spreadSheet));
$customPropertiesPart = $this->getWriterPart('DocProps')->writeDocPropsCustom($this->spreadSheet);
if ($customPropertiesPart !== null) {
$objZip->addFromString('docProps/custom.xml', $customPropertiesPart);
}
// Add theme to ZIP file
$objZip->addFromString('xl/theme/theme1.xml', $this->getWriterPart('Theme')->writeTheme($this->spreadSheet));
// Add string table to ZIP file
$objZip->addFromString('xl/sharedStrings.xml', $this->getWriterPart('StringTable')->writeStringTable($this->stringTable));
// Add styles to ZIP file
$objZip->addFromString('xl/styles.xml', $this->getWriterPart('Style')->writeStyles($this->spreadSheet));
// Add workbook to ZIP file
$objZip->addFromString('xl/workbook.xml', $this->getWriterPart('Workbook')->writeWorkbook($this->spreadSheet, $this->preCalculateFormulas));
$chartCount = 0;
// Add worksheets
for ($i = 0; $i < $this->spreadSheet->getSheetCount(); ++$i) {
$objZip->addFromString('xl/worksheets/sheet' . ($i + 1) . '.xml', $this->getWriterPart('Worksheet')->writeWorksheet($this->spreadSheet->getSheet($i), $this->stringTable, $this->includeCharts));
if ($this->includeCharts) {
$charts = $this->spreadSheet->getSheet($i)->getChartCollection();
if (count($charts) > 0) {
foreach ($charts as $chart) {
$objZip->addFromString('xl/charts/chart' . ($chartCount + 1) . '.xml', $this->getWriterPart('Chart')->writeChart($chart, $this->preCalculateFormulas));
$chartCount++;
}
}
}
}
$chartRef1 = $chartRef2 = 0;
// Add worksheet relationships (drawings, ...)
for ($i = 0; $i < $this->spreadSheet->getSheetCount(); ++$i) {
// Add relationships
$objZip->addFromString('xl/worksheets/_rels/sheet' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeWorksheetRelationships($this->spreadSheet->getSheet($i), $i + 1, $this->includeCharts));
$drawings = $this->spreadSheet->getSheet($i)->getDrawingCollection();
$drawingCount = count($drawings);
if ($this->includeCharts) {
$chartCount = $this->spreadSheet->getSheet($i)->getChartCount();
}
// Add drawing and image relationship parts
if ($drawingCount > 0 || $chartCount > 0) {
// Drawing relationships
$objZip->addFromString('xl/drawings/_rels/drawing' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeDrawingRelationships($this->spreadSheet->getSheet($i), $chartRef1, $this->includeCharts));
// Drawings
$objZip->addFromString('xl/drawings/drawing' . ($i + 1) . '.xml', $this->getWriterPart('Drawing')->writeDrawings($this->spreadSheet->getSheet($i), $chartRef2, $this->includeCharts));
}
// Add comment relationship parts
if (count($this->spreadSheet->getSheet($i)->getComments()) > 0) {
// VML Comments
$objZip->addFromString('xl/drawings/vmlDrawing' . ($i + 1) . '.vml', $this->getWriterPart('Comments')->writeVMLComments($this->spreadSheet->getSheet($i)));
// Comments
$objZip->addFromString('xl/comments' . ($i + 1) . '.xml', $this->getWriterPart('Comments')->writeComments($this->spreadSheet->getSheet($i)));
}
// Add header/footer relationship parts
if (count($this->spreadSheet->getSheet($i)->getHeaderFooter()->getImages()) > 0) {
// VML Drawings
$objZip->addFromString('xl/drawings/vmlDrawingHF' . ($i + 1) . '.vml', $this->getWriterPart('Drawing')->writeVMLHeaderFooterImages($this->spreadSheet->getSheet($i)));
// VML Drawing relationships
$objZip->addFromString('xl/drawings/_rels/vmlDrawingHF' . ($i + 1) . '.vml.rels', $this->getWriterPart('Rels')->writeHeaderFooterDrawingRelationships($this->spreadSheet->getSheet($i)));
// Media
foreach ($this->spreadSheet->getSheet($i)->getHeaderFooter()->getImages() as $image) {
$objZip->addFromString('xl/media/' . $image->getIndexedFilename(), file_get_contents($image->getPath()));
}
}
}
// Add media
for ($i = 0; $i < $this->getDrawingHashTable()->count(); ++$i) {
if ($this->getDrawingHashTable()->getByIndex($i) instanceof \PHPExcel\Worksheet\Drawing) {
$imageContents = null;
$imagePath = $this->getDrawingHashTable()->getByIndex($i)->getPath();
if (strpos($imagePath, 'zip://') !== false) {
$imagePath = substr($imagePath, 6);
$imagePathSplitted = explode('#', $imagePath);
$imageZip = new ZipArchive();
$imageZip->open($imagePathSplitted[0]);
$imageContents = $imageZip->getFromName($imagePathSplitted[1]);
$imageZip->close();
unset($imageZip);
} else {
$imageContents = file_get_contents($imagePath);
}
$objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents);
} elseif ($this->getDrawingHashTable()->getByIndex($i) instanceof \PHPExcel\Worksheet\MemoryDrawing) {
ob_start();
call_user_func($this->getDrawingHashTable()->getByIndex($i)->getRenderingFunction(), $this->getDrawingHashTable()->getByIndex($i)->getImageResource());
$imageContents = ob_get_contents();
ob_end_clean();
$objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents);
}
}
\PHPExcel\Calculation\Functions::setReturnDateType($saveDateReturnType);
\PHPExcel\Calculation::getInstance($this->spreadSheet)->getDebugLog()->setWriteDebugLog($saveDebugLog);
// Close file
if ($objZip->close() === false) {
throw new \PHPExcel\Writer\Exception("Could not close zip file {$pFilename}.");
}
// If a temporary file was used, copy it to the correct file stream
if ($originalFilename != $pFilename) {
if (copy($pFilename, $originalFilename) === false) {
throw new \PHPExcel\Writer\Exception("Could not copy temporary zip file {$pFilename} to {$originalFilename}.");
}
@unlink($pFilename);
}
} else {
throw new \PHPExcel\Writer\Exception("PHPExcel object unassigned.");
}
}
/**
* Save PHPExcel to file
*
* @param string $pFilename
* @throws \PHPExcel\Writer\Exception
*/
public function save($pFilename = null)
{
// garbage collect
$this->phpExcel->garbageCollect();
$saveDebugLog = Calculation::getInstance($this->phpExcel)->getDebugLog()->getWriteDebugLog();
Calculation::getInstance($this->phpExcel)->getDebugLog()->setWriteDebugLog(false);
$saveArrayReturnType = Calculation::getArrayReturnType();
Calculation::setArrayReturnType(Calculation::RETURN_ARRAY_AS_VALUE);
// Build CSS
$this->buildCSS(!$this->useInlineCss);
// Open file
$fileHandle = fopen($pFilename, 'wb+');
if ($fileHandle === false) {
throw new \PHPExcel\Writer\Exception("Could not open file {$pFilename} for writing.");
}
// Write headers
fwrite($fileHandle, $this->generateHTMLHeader(!$this->useInlineCss));
// Write navigation (tabs)
if (!$this->isPdf && $this->generateSheetNavigationBlock) {
fwrite($fileHandle, $this->generateNavigation());
}
// Write data
fwrite($fileHandle, $this->generateSheetData());
// Write footer
fwrite($fileHandle, $this->generateHTMLFooter());
// Close file
fclose($fileHandle);
Calculation::setArrayReturnType($saveArrayReturnType);
Calculation::getInstance($this->phpExcel)->getDebugLog()->setWriteDebugLog($saveDebugLog);
}
