Пример #1
0
 /**
  * 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');
Пример #4
0
 /**
  * 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);
 }
Пример #5
0
 /**
  *    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;
 }
Пример #6
0
 /**
  * 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;
 }
Пример #7
0
 /**
  * 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;
 }
Пример #8
0
 /**
  *    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;
 }
Пример #9
0
 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);
     }
 }
Пример #10
0
 /**
  * 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);
 }
Пример #11
0
 /**
  * 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;
 }
Пример #12
0
 /**
  * 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;
 }
Пример #13
0
 /**
  * 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);
 }
Пример #14
0
 /**
  * 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;
 }
Пример #15
0
 /**
  * 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);
 }
Пример #16
0
 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
Пример #18
0
 /**
  * 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;
 }
Пример #19
0
 /**
  * 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.");
     }
 }
Пример #20
0
 /**
  * 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);
 }