public function setUp()
{
if (!defined('PHPEXCEL_ROOT')) {
define('PHPEXCEL_ROOT', APPLICATION_PATH . '/');
}
require_once PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php';
PHPExcel_Shared_String::setDecimalSeparator('.');
PHPExcel_Shared_String::setThousandsSeparator(',');
}
/**
* 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_String::SanitizeUTF8($value);
}
// Find out data type
$dataType = parent::dataTypeForValue($value);
// Style logic - strings
if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) {
// Check for percentage
if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) {
// Convert value to number
$cell->setValueExplicit((double) str_replace('%', '', $value) / 100, PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
return true;
}
// Check for time e.g. '9:45', '09:45'
if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d$/', $value)) {
list($h, $m) = explode(':', $value);
$days = $h / 24 + $m / 1440;
// Convert value to number
$cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);
return true;
}
// Check for date
if (strtotime($value) !== false) {
// make sure we have UTC for the sake of strtotime
$saveTimeZone = date_default_timezone_get();
date_default_timezone_set('UTC');
// Convert value to Excel date
$cell->setValueExplicit(PHPExcel_Shared_Date::PHPToExcel(strtotime($value)), PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
// restore original value for timezone
date_default_timezone_set($saveTimeZone);
return true;
}
}
// Style logic - Numbers
if ($dataType === PHPExcel_Cell_DataType::TYPE_NUMERIC) {
// Leading zeroes?
if (preg_match('/^\\-?[0]+[0-9]*\\.?[0-9]*$/', $value)) {
// Convert value to string
$cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
// Set style
$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
return true;
}
}
// Not bound yet? Use parent...
return parent::bindValue($cell, $value);
}
/**
* Check a string that it satisfies Excel requirements
*
* @param mixed Value to sanitize to an Excel string
* @return mixed Sanitized value
*/
public static function checkString($pValue = null)
{
if ($pValue instanceof PHPExcel_RichText) {
return $pValue;
}
// string must never be longer than 32,767 characters, truncate if necessary
$pValue = PHPExcel_Shared_String::Substring($pValue, 0, 32767);
// we require that newline is represented as "\n" in core, not as "\r\n" or "\r"
$pValue = str_replace(array("\r\n", "\r"), "\n", $pValue);
return $pValue;
}
/**
* 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_String::SanitizeUTF8($value);
}
// Set value explicit
$cell->setValueExplicit($value, self::dataTypeForValue($value));
// Done!
return TRUE;
}
function getImportExcelData($data, $fields)
{
global $total_records, $cCharset, $columnIndex;
foreach ($data->getWorksheetIterator() as $worksheet) {
$highestRow = $worksheet->getHighestRow();
for ($row = 2; $row <= $highestRow; ++$row) {
for ($col = 0; $col < $columnIndex; ++$col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
if (PHPExcel_Shared_Date::isDateTime($cell)) {
$date_format = $cell->getParent()->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode();
$value = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $date_format);
if (is_a($value, 'PHPExcel_RichText')) {
$value = $value->getPlainText();
}
if ($value) {
$time = array();
if (strtotime($value)) {
$value = strtotime($value);
} else {
$d_format = "";
for ($i = 0; $i < strlen($date_format); $i++) {
$letter = substr(strtolower($date_format), $i, 1);
if ($letter == "d" || $letter == "m" || $letter == "y") {
if (strpos($d_format, $letter) === false) {
$d_format .= $letter;
}
}
}
$value = strtotime(localdatetime2db($value, $d_format));
}
// $value = PHPExcel_Shared_Date::ExcelToPHP($value);
$time = localtime($value, true);
$val = $time["tm_year"] + 1900 . "-" . ($time["tm_mon"] + 1) . "-" . $time["tm_mday"] . " " . $time["tm_hour"] . ":" . $time["tm_min"] . ":" . $time["tm_sec"];
} else {
$val = NULL;
}
} else {
$error_handler = set_error_handler("empty_error_handler");
$val = PHPExcel_Shared_String::ConvertEncoding($cell->getValue(), $cCharset, 'UTF-8');
if (is_a($val, 'PHPExcel_RichText')) {
$val = $val->getPlainText();
}
if ($error_handler) {
set_error_handler($error_handler);
}
}
$arr[$fields[$col]] = $val;
}
$ret = InsertRecord($arr, $row - 2);
$total_records++;
}
break;
}
}
/**
* @dataProvider provider
*/
public function testCurrency($value, $valueBinded, $format, $thousandsSeparator, $decimalSeparator, $currencyCode)
{
$sheet = $this->getMock('PHPExcel_Worksheet', array('getStyle', 'getNumberFormat', 'setFormatCode'));
$sheet->expects($this->once())->method('getStyle')->will($this->returnSelf());
$sheet->expects($this->once())->method('getNumberFormat')->will($this->returnSelf());
$sheet->expects($this->once())->method('setFormatCode')->with($format)->will($this->returnSelf());
PHPExcel_Shared_String::setCurrencyCode($currencyCode);
PHPExcel_Shared_String::setDecimalSeparator($decimalSeparator);
PHPExcel_Shared_String::setThousandsSeparator($thousandsSeparator);
$cell = new PHPExcel_Cell('A', 1, null, PHPExcel_Cell_DataType::TYPE_STRING, $sheet);
$binder = new PHPExcel_Cell_AdvancedValueBinder();
$binder->bindValue($cell, $value);
$this->assertEquals($valueBinded, $cell->getValue());
}
/**
* Bind value to a cell, preserving possible leading zeros
* See http://stackoverflow.com/questions/12457610/reading-numbers-as-text-format-with-phpexcel
*
* @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_String::SanitizeUTF8($value);
}
// Preserve numeric string, including leading zeros, if it is a text format
$format = $cell->getStyle()->getNumberFormat()->getFormatCode();
if ($format == PHPExcel_Style_NumberFormat::FORMAT_TEXT) {
$cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
return true;
}
// Not bound yet? Use default value parent...
return parent::bindValue($cell, $value);
}
/**
* @dataProvider provider
*/
public function testCurrency($value, $valueBinded, $format, $thousandsSeparator, $decimalSeparator, $currencyCode)
{
$sheet = $this->getMock('PHPExcel_Worksheet', array('getStyle', 'getNumberFormat', 'setFormatCode', 'getCellCacheController'));
$cache = $this->getMockBuilder('PHPExcel_CachedObjectStorage_Memory')->disableOriginalConstructor()->getMock();
$cache->expects($this->any())->method('getParent')->will($this->returnValue($sheet));
$sheet->expects($this->once())->method('getStyle')->will($this->returnSelf());
$sheet->expects($this->once())->method('getNumberFormat')->will($this->returnSelf());
$sheet->expects($this->once())->method('setFormatCode')->with($format)->will($this->returnSelf());
$sheet->expects($this->any())->method('getCellCacheController')->will($this->returnValue($cache));
PHPExcel_Shared_String::setCurrencyCode($currencyCode);
PHPExcel_Shared_String::setDecimalSeparator($decimalSeparator);
PHPExcel_Shared_String::setThousandsSeparator($thousandsSeparator);
$cell = new PHPExcel_Cell(NULL, PHPExcel_Cell_DataType::TYPE_STRING, $sheet);
$binder = new PHPExcel_Cell_AdvancedValueBinder();
$binder->bindValue($cell, $value);
$this->assertEquals($valueBinded, $cell->getValue());
}
/**
* 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_String::SanitizeUTF8($value);
} elseif (is_object($value)) {
// Handle any objects that might be injected
if ($value instanceof DateTime) {
$value = $value->format('Y-m-d H:i:s');
} elseif (!$value instanceof PHPExcel_RichText) {
$value = (string) $value;
}
}
// Set value explicit
$cell->setValueExplicit($value, self::dataTypeForValue($value));
// Done!
return true;
}
private function _setMargins(PHPExcel_Worksheet $pSheet)
{
$htmlPage = '@page { ';
$htmlBody = 'body { ';
$left = PHPExcel_Shared_String::FormatNumber($pSheet->getPageMargins()->getLeft()) . 'in; ';
$htmlPage .= 'left-margin: ' . $left;
$htmlBody .= 'left-margin: ' . $left;
$right = PHPExcel_Shared_String::FormatNumber($pSheet->getPageMargins()->getRight()) . 'in; ';
$htmlPage .= 'right-margin: ' . $right;
$htmlBody .= 'right-margin: ' . $right;
$top = PHPExcel_Shared_String::FormatNumber($pSheet->getPageMargins()->getTop()) . 'in; ';
$htmlPage .= 'top-margin: ' . $top;
$htmlBody .= 'top-margin: ' . $top;
$bottom = PHPExcel_Shared_String::FormatNumber($pSheet->getPageMargins()->getBottom()) . 'in; ';
$htmlPage .= 'bottom-margin: ' . $bottom;
$htmlBody .= 'bottom-margin: ' . $bottom;
$htmlPage .= "}\n";
$htmlBody .= "}\n";
return "<style>\n" . $htmlPage . $htmlBody . "</style>\n";
}
/**
* Get whether iconv extension is available
*
* @return boolean
*/
public static function getIsIconvEnabled()
{
if (isset(self::$_isIconvEnabled)) {
return self::$_isIconvEnabled;
}
self::$_isIconvEnabled = function_exists('iconv') ? true : false;
return self::$_isIconvEnabled;
}
$size = 7 + $subSize;
$data = $this->_getFormulaFromData(substr($formulaData, 7, $subSize));
break;
case 0x29:
// Variable reference sub-expression
/**
* Convert a string token to ptgStr
*
* @access private
* @param string $string A string for conversion to its ptg value.
* @return mixed the converted token on success
*/
function _convertString($string)
{
// chop away beggining and ending quotes
$string = substr($string, 1, strlen($string) - 2);
if (strlen($string) > 255) {
throw new Exception("String is too long");
}
if ($this->_BIFF_version == 0x500) {
return pack("CC", $this->ptg['ptgStr'], strlen($string)) . $string;
} elseif ($this->_BIFF_version == 0x600) {
return pack('C', $this->ptg['ptgStr']) . PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($string);
}
}
/**
* Store the DATAVALIDATIONS and DATAVALIDATION records.
*/
private function _writeDataValidity()
{
// Datavalidation collection
$dataValidationCollection = $this->_phpSheet->getDataValidationCollection();
// Write data validations?
if (!empty($dataValidationCollection)) {
// DATAVALIDATIONS record
$record = 0x1b2;
// Record identifier
$length = 0x12;
// Bytes to follow
$grbit = 0x0;
// Prompt box at cell, no cached validity data at DV records
$horPos = 0x0;
// Horizontal position of prompt box, if fixed position
$verPos = 0x0;
// Vertical position of prompt box, if fixed position
$objId = 0xffffffff;
// Object identifier of drop down arrow object, or -1 if not visible
$header = pack('vv', $record, $length);
$data = pack('vVVVV', $grbit, $horPos, $verPos, $objId, count($dataValidationCollection));
$this->_append($header . $data);
// DATAVALIDATION records
$record = 0x1be;
// Record identifier
foreach ($dataValidationCollection as $cellCoordinate => $dataValidation) {
// initialize record data
$data = '';
// options
$options = 0x0;
// data type
$type = $dataValidation->getType();
switch ($type) {
case PHPExcel_Cell_DataValidation::TYPE_NONE:
$type = 0x0;
break;
case PHPExcel_Cell_DataValidation::TYPE_WHOLE:
$type = 0x1;
break;
case PHPExcel_Cell_DataValidation::TYPE_DECIMAL:
$type = 0x2;
break;
case PHPExcel_Cell_DataValidation::TYPE_LIST:
$type = 0x3;
break;
case PHPExcel_Cell_DataValidation::TYPE_DATE:
$type = 0x4;
break;
case PHPExcel_Cell_DataValidation::TYPE_TIME:
$type = 0x5;
break;
case PHPExcel_Cell_DataValidation::TYPE_TEXTLENGTH:
$type = 0x6;
break;
case PHPExcel_Cell_DataValidation::TYPE_CUSTOM:
$type = 0x7;
break;
}
$options |= $type << 0;
// error style
$errorStyle = $dataValidation->getType();
switch ($errorStyle) {
case PHPExcel_Cell_DataValidation::STYLE_STOP:
$errorStyle = 0x0;
break;
case PHPExcel_Cell_DataValidation::STYLE_WARNING:
$errorStyle = 0x1;
break;
case PHPExcel_Cell_DataValidation::STYLE_INFORMATION:
$errorStyle = 0x2;
break;
}
$options |= $errorStyle << 4;
// explicit formula?
if ($type == 0x3 && preg_match('/^\\".*\\"$/', $dataValidation->getFormula1())) {
$options |= 0x1 << 7;
}
// empty cells allowed
$options |= $dataValidation->getAllowBlank() << 8;
// show drop down
$options |= !$dataValidation->getShowDropDown() << 9;
// show input message
$options |= $dataValidation->getShowInputMessage() << 18;
// show error message
$options |= $dataValidation->getShowErrorMessage() << 19;
// condition operator
$operator = $dataValidation->getOperator();
switch ($operator) {
case PHPExcel_Cell_DataValidation::OPERATOR_BETWEEN:
$operator = 0x0;
break;
case PHPExcel_Cell_DataValidation::OPERATOR_NOTBETWEEN:
$operator = 0x1;
break;
case PHPExcel_Cell_DataValidation::OPERATOR_EQUAL:
$operator = 0x2;
break;
case PHPExcel_Cell_DataValidation::OPERATOR_NOTEQUAL:
$operator = 0x3;
break;
case PHPExcel_Cell_DataValidation::OPERATOR_GREATERTHAN:
$operator = 0x4;
break;
case PHPExcel_Cell_DataValidation::OPERATOR_LESSTHAN:
$operator = 0x5;
break;
case PHPExcel_Cell_DataValidation::OPERATOR_GREATERTHANOREQUAL:
$operator = 0x6;
break;
case PHPExcel_Cell_DataValidation::OPERATOR_LESSTHANOREQUAL:
$operator = 0x7;
break;
}
$options |= $operator << 20;
$data = pack('V', $options);
// prompt title
$promptTitle = $dataValidation->getPromptTitle() !== '' ? $dataValidation->getPromptTitle() : chr(0);
$data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($promptTitle);
// error title
$errorTitle = $dataValidation->getErrorTitle() !== '' ? $dataValidation->getErrorTitle() : chr(0);
$data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($errorTitle);
// prompt text
$prompt = $dataValidation->getPrompt() !== '' ? $dataValidation->getPrompt() : chr(0);
$data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($prompt);
// error text
$error = $dataValidation->getError() !== '' ? $dataValidation->getError() : chr(0);
$data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($error);
// formula 1
try {
$formula1 = $dataValidation->getFormula1();
if ($type == 0x3) {
// list type
$formula1 = str_replace(',', chr(0), $formula1);
}
$this->_parser->parse($formula1);
$formula1 = $this->_parser->toReversePolish();
$sz1 = strlen($formula1);
} catch (Exception $e) {
$sz1 = 0;
$formula1 = '';
}
$data .= pack('vv', $sz1, 0x0);
$data .= $formula1;
// formula 2
try {
$formula2 = $dataValidation->getFormula2();
if ($formula2 === '') {
throw new Exception('No formula2');
}
$this->_parser->parse($formula2);
$formula2 = $this->_parser->toReversePolish();
$sz2 = strlen($formula2);
} catch (Exception $e) {
$sz2 = 0;
$formula2 = '';
}
$data .= pack('vv', $sz2, 0x0);
$data .= $formula2;
// cell range address list
$data .= pack('v', 0x1);
$data .= $this->_writeBIFF8CellRangeAddressFixed($cellCoordinate);
$length = strlen($data);
$header = pack("vv", $record, $length);
$this->_append($header . $data);
}
}
}
/**
* Write Cell
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Worksheet $pSheet Worksheet
* @param PHPExcel_Cell $pCell Cell
* @param string[] $pStringTable String table
* @param string[] $pFlippedStringTable String table (flipped), for faster index searching
* @throws Exception
*/
private function _writeCell(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, PHPExcel_Cell $pCell = null, $pStringTable = null, $pFlippedStringTable = null)
{
if (is_array($pStringTable) && is_array($pFlippedStringTable)) {
// Cell
$objWriter->startElement('c');
$objWriter->writeAttribute('r', $pCell->getCoordinate());
// Sheet styles
if ($pCell->getXfIndex() != '') {
$objWriter->writeAttribute('s', $pCell->getXfIndex());
}
// If cell value is supplied, write cell value
if (is_object($pCell->getValue()) || $pCell->getValue() !== '') {
// Map type
$mappedType = $pCell->getDataType();
// Write data type depending on its type
switch (strtolower($mappedType)) {
case 'inlinestr':
// Inline string
$objWriter->writeAttribute('t', $mappedType);
break;
case 's':
// String
$objWriter->writeAttribute('t', $mappedType);
break;
case 'b':
// Boolean
$objWriter->writeAttribute('t', $mappedType);
break;
case 'f':
// Formula
$calculatedValue = null;
if ($this->getParentWriter()->getPreCalculateFormulas()) {
$calculatedValue = $pCell->getCalculatedValue();
} else {
$calculatedValue = $pCell->getValue();
}
if (is_string($calculatedValue)) {
$objWriter->writeAttribute('t', 'str');
}
break;
case 'e':
// Error
$objWriter->writeAttribute('t', $mappedType);
}
// Write data depending on its type
switch (strtolower($mappedType)) {
case 'inlinestr':
// Inline string
if (!$pCell->getValue() instanceof PHPExcel_RichText) {
$objWriter->writeElement('t', PHPExcel_Shared_String::ControlCharacterPHP2OOXML(htmlspecialchars($pCell->getValue())));
} else {
if ($pCell->getValue() instanceof PHPExcel_RichText) {
$objWriter->startElement('is');
$this->getParentWriter()->getWriterPart('stringtable')->writeRichText($objWriter, $pCell->getValue());
$objWriter->endElement();
}
}
break;
case 's':
// String
if (!$pCell->getValue() instanceof PHPExcel_RichText) {
if (isset($pFlippedStringTable[$pCell->getValue()])) {
$objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()]);
}
} else {
if ($pCell->getValue() instanceof PHPExcel_RichText) {
$objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()->getHashCode()]);
}
}
break;
case 'f':
// Formula
$objWriter->writeElement('f', substr($pCell->getValue(), 1));
if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
if ($this->getParentWriter()->getPreCalculateFormulas()) {
$calculatedValue = $pCell->getCalculatedValue();
if (!is_array($calculatedValue) && substr($calculatedValue, 0, 1) != '#') {
$v = PHPExcel_Shared_String::FormatNumber($calculatedValue);
$objWriter->writeElement('v', $v);
} else {
$objWriter->writeElement('v', '0');
}
} else {
$objWriter->writeElement('v', '0');
}
}
break;
case 'n':
// Numeric
// force point as decimal separator in case current locale uses comma
$v = str_replace(',', '.', $pCell->getValue());
$objWriter->writeElement('v', $v);
break;
case 'b':
// Boolean
$objWriter->writeElement('v', $pCell->getValue() ? '1' : '0');
break;
case 'e':
// Error
if (substr($pCell->getValue(), 0, 1) == '=') {
$objWriter->writeElement('f', substr($pCell->getValue(), 1));
$objWriter->writeElement('v', substr($pCell->getValue(), 1));
} else {
$objWriter->writeElement('v', $pCell->getValue());
}
break;
}
}
$objWriter->endElement();
} else {
throw new Exception("Invalid parameters passed.");
}
}
/**
* DATE
*
* The DATE function returns a value that represents a particular date.
*
* NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date
* format of your regional settings. PHPExcel does not change cell formatting in this way.
*
* Excel Function:
* DATE(year,month,day)
*
* @access public
* @category Date/Time Functions
* @param integer $year The value of the year argument can include one to four digits.
* Excel interprets the year argument according to the configured
* date system: 1900 or 1904.
* If year is between 0 (zero) and 1899 (inclusive), Excel adds that
* value to 1900 to calculate the year. For example, DATE(108,1,2)
* returns January 2, 2008 (1900+108).
* If year is between 1900 and 9999 (inclusive), Excel uses that
* value as the year. For example, DATE(2008,1,2) returns January 2,
* 2008.
* If year is less than 0 or is 10000 or greater, Excel returns the
* #NUM! error value.
* @param integer $month A positive or negative integer representing the month of the year
* from 1 to 12 (January to December).
* If month is greater than 12, month adds that number of months to
* the first month in the year specified. For example, DATE(2008,14,2)
* returns the serial number representing February 2, 2009.
* If month is less than 1, month subtracts the magnitude of that
* number of months, plus 1, from the first month in the year
* specified. For example, DATE(2008,-3,2) returns the serial number
* representing September 2, 2007.
* @param integer $day A positive or negative integer representing the day of the month
* from 1 to 31.
* If day is greater than the number of days in the month specified,
* day adds that number of days to the first day in the month. For
* example, DATE(2008,1,35) returns the serial number representing
* February 4, 2008.
* If day is less than 1, day subtracts the magnitude that number of
* days, plus one, from the first day of the month specified. For
* example, DATE(2008,1,-15) returns the serial number representing
* December 16, 2007.
* @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
* depending on the value of the ReturnDateType flag
*/
public static function DATE($year = 0, $month = 1, $day = 1)
{
$year = PHPExcel_Calculation_Functions::flattenSingleValue($year);
$month = PHPExcel_Calculation_Functions::flattenSingleValue($month);
$day = PHPExcel_Calculation_Functions::flattenSingleValue($day);
$year = $year !== NULL ? PHPExcel_Shared_String::testStringAsNumeric($year) : 0;
$month = $month !== NULL ? PHPExcel_Shared_String::testStringAsNumeric($month) : 0;
$day = $day !== NULL ? PHPExcel_Shared_String::testStringAsNumeric($day) : 0;
if (!is_numeric($year) || !is_numeric($month) || !is_numeric($day)) {
return PHPExcel_Calculation_Functions::VALUE();
}
$year = (int) $year;
$month = (int) $month;
$day = (int) $day;
$baseYear = PHPExcel_Shared_Date::getExcelCalendar();
// Validate parameters
if ($year < $baseYear - 1900) {
return PHPExcel_Calculation_Functions::NaN();
}
if ($baseYear - 1900 != 0 && $year < $baseYear && $year >= 1900) {
return PHPExcel_Calculation_Functions::NaN();
}
if ($year < $baseYear && $year >= $baseYear - 1900) {
$year += 1900;
}
if ($month < 1) {
// Handle year/month adjustment if month < 1
--$month;
$year += ceil($month / 12) - 1;
$month = 13 - abs($month % 12);
} elseif ($month > 12) {
// Handle year/month adjustment if month > 12
$year += floor($month / 12);
$month = $month % 12;
}
// Re-validate the year parameter after adjustments
if ($year < $baseYear || $year >= 10000) {
return PHPExcel_Calculation_Functions::NaN();
}
// Execute function
$excelDateValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day);
switch (PHPExcel_Calculation_Functions::getReturnDateType()) {
case PHPExcel_Calculation_Functions::RETURNDATE_EXCEL:
return (double) $excelDateValue;
case PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC:
return (int) PHPExcel_Shared_Date::ExcelToPHP($excelDateValue);
case PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT:
return PHPExcel_Shared_Date::ExcelToPHPObject($excelDateValue);
}
}
/**
* Set title
*
* @param string $pValue String containing the dimension of this worksheet
* @param string $updateFormulaCellReferences boolean Flag indicating whether cell references in formulae should
* be updated to reflect the new sheet name.
* This should be left as the default true, unless you are
* certain that no formula cells on any worksheet contain
* references to this worksheet
*
* @return PHPExcel_Worksheet
*/
public function setTitle($pValue = 'Worksheet', $updateFormulaCellReferences = true)
{
// Is this a 'rename' or not?
if ($this->getTitle() == $pValue) {
return $this;
}
// Syntax check
self::_checkSheetTitle($pValue);
// Old title
$oldTitle = $this->getTitle();
if ($this->_parent) {
// Is there already such sheet name?
if ($this->_parent->sheetNameExists($pValue)) {
// Use name, but append with lowest possible integer
if (PHPExcel_Shared_String::CountCharacters($pValue) > 29) {
$pValue = PHPExcel_Shared_String::Substring($pValue, 0, 29);
}
$i = 1;
while ($this->_parent->sheetNameExists($pValue . ' ' . $i)) {
++$i;
if ($i == 10) {
if (PHPExcel_Shared_String::CountCharacters($pValue) > 28) {
$pValue = PHPExcel_Shared_String::Substring($pValue, 0, 28);
}
} elseif ($i == 100) {
if (PHPExcel_Shared_String::CountCharacters($pValue) > 27) {
$pValue = PHPExcel_Shared_String::Substring($pValue, 0, 27);
}
}
}
$altTitle = $pValue . ' ' . $i;
return $this->setTitle($altTitle, $updateFormulaCellReferences);
}
}
// Set title
$this->_title = $pValue;
$this->_dirty = true;
if ($this->_parent) {
// New title
$newTitle = $this->getTitle();
PHPExcel_Calculation::getInstance($this->_parent)->renameCalculationCacheForWorksheet($oldTitle, $newTitle);
if ($updateFormulaCellReferences) {
PHPExcel_ReferenceHelper::getInstance()->updateNamedFormulas($this->_parent, $oldTitle, $newTitle);
}
}
return $this;
}
/**
* Write CFRule Record
* @param PHPExcel_Style_Conditional $conditional
*/
private function _writeCFRule(PHPExcel_Style_Conditional $conditional)
{
$record = 0x1b1;
// Record identifier
// $type : Type of the CF
// $operatorType : Comparison operator
if ($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_EXPRESSION) {
$type = 0x2;
$operatorType = 0x0;
} else {
if ($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CELLIS) {
$type = 0x1;
switch ($conditional->getOperatorType()) {
case PHPExcel_Style_Conditional::OPERATOR_NONE:
$operatorType = 0x0;
break;
case PHPExcel_Style_Conditional::OPERATOR_EQUAL:
$operatorType = 0x3;
break;
case PHPExcel_Style_Conditional::OPERATOR_GREATERTHAN:
$operatorType = 0x5;
break;
case PHPExcel_Style_Conditional::OPERATOR_GREATERTHANOREQUAL:
$operatorType = 0x7;
break;
case PHPExcel_Style_Conditional::OPERATOR_LESSTHAN:
$operatorType = 0x6;
break;
case PHPExcel_Style_Conditional::OPERATOR_LESSTHANOREQUAL:
$operatorType = 0x8;
break;
case PHPExcel_Style_Conditional::OPERATOR_NOTEQUAL:
$operatorType = 0x4;
break;
case PHPExcel_Style_Conditional::OPERATOR_BETWEEN:
$operatorType = 0x1;
break;
// not OPERATOR_NOTBETWEEN 0x02
}
}
}
// $szValue1 : size of the formula data for first value or formula
// $szValue2 : size of the formula data for second value or formula
$arrConditions = $conditional->getConditions();
$numConditions = sizeof($arrConditions);
if ($numConditions == 1) {
$szValue1 = $arrConditions[0] <= 65535 ? 3 : 0x0;
$szValue2 = 0x0;
$operand1 = pack('Cv', 0x1e, $arrConditions[0]);
$operand2 = null;
} else {
if ($numConditions == 2 && $conditional->getOperatorType() == PHPExcel_Style_Conditional::OPERATOR_BETWEEN) {
$szValue1 = $arrConditions[0] <= 65535 ? 3 : 0x0;
$szValue2 = $arrConditions[1] <= 65535 ? 3 : 0x0;
$operand1 = pack('Cv', 0x1e, $arrConditions[0]);
$operand2 = pack('Cv', 0x1e, $arrConditions[1]);
} else {
$szValue1 = 0x0;
$szValue2 = 0x0;
$operand1 = null;
$operand2 = null;
}
}
// $flags : Option flags
// Alignment
$bAlignHz = $conditional->getStyle()->getAlignment()->getHorizontal() == null ? 1 : 0;
$bAlignVt = $conditional->getStyle()->getAlignment()->getVertical() == null ? 1 : 0;
$bAlignWrapTx = $conditional->getStyle()->getAlignment()->getWrapText() == false ? 1 : 0;
$bTxRotation = $conditional->getStyle()->getAlignment()->getTextRotation() == null ? 1 : 0;
$bIndent = $conditional->getStyle()->getAlignment()->getIndent() == 0 ? 1 : 0;
$bShrinkToFit = $conditional->getStyle()->getAlignment()->getShrinkToFit() == false ? 1 : 0;
if ($bAlignHz == 0 || $bAlignVt == 0 || $bAlignWrapTx == 0 || $bTxRotation == 0 || $bIndent == 0 || $bShrinkToFit == 0) {
$bFormatAlign = 1;
} else {
$bFormatAlign = 0;
}
// Protection
$bProtLocked = $conditional->getStyle()->getProtection()->getLocked() == null ? 1 : 0;
$bProtHidden = $conditional->getStyle()->getProtection()->getHidden() == null ? 1 : 0;
if ($bProtLocked == 0 || $bProtHidden == 0) {
$bFormatProt = 1;
} else {
$bFormatProt = 0;
}
// Border
$bBorderLeft = $conditional->getStyle()->getBorders()->getLeft()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK && $conditional->getStyle()->getBorders()->getLeft()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0;
$bBorderRight = $conditional->getStyle()->getBorders()->getRight()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK && $conditional->getStyle()->getBorders()->getRight()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0;
$bBorderTop = $conditional->getStyle()->getBorders()->getTop()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK && $conditional->getStyle()->getBorders()->getTop()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0;
$bBorderBottom = $conditional->getStyle()->getBorders()->getBottom()->getColor()->getARGB() == PHPExcel_Style_Color::COLOR_BLACK && $conditional->getStyle()->getBorders()->getBottom()->getBorderStyle() == PHPExcel_Style_Border::BORDER_NONE ? 1 : 0;
if ($bBorderLeft == 0 || $bBorderRight == 0 || $bBorderTop == 0 || $bBorderBottom == 0) {
$bFormatBorder = 1;
} else {
$bFormatBorder = 0;
}
// Pattern
$bFillStyle = $conditional->getStyle()->getFill()->getFillType() == null ? 0 : 1;
$bFillColor = $conditional->getStyle()->getFill()->getStartColor()->getARGB() == null ? 0 : 1;
$bFillColorBg = $conditional->getStyle()->getFill()->getEndColor()->getARGB() == null ? 0 : 1;
if ($bFillStyle == 0 || $bFillColor == 0 || $bFillColorBg == 0) {
$bFormatFill = 1;
} else {
$bFormatFill = 0;
}
// Font
if ($conditional->getStyle()->getFont()->getName() != null || $conditional->getStyle()->getFont()->getSize() != null || $conditional->getStyle()->getFont()->getBold() != null || $conditional->getStyle()->getFont()->getItalic() != null || $conditional->getStyle()->getFont()->getSuperScript() != null || $conditional->getStyle()->getFont()->getSubScript() != null || $conditional->getStyle()->getFont()->getUnderline() != null || $conditional->getStyle()->getFont()->getStrikethrough() != null || $conditional->getStyle()->getFont()->getColor()->getARGB() != null) {
$bFormatFont = 1;
} else {
$bFormatFont = 0;
}
// Alignment
$flags = 0;
$flags |= 1 == $bAlignHz ? 0x1 : 0;
$flags |= 1 == $bAlignVt ? 0x2 : 0;
$flags |= 1 == $bAlignWrapTx ? 0x4 : 0;
$flags |= 1 == $bTxRotation ? 0x8 : 0;
// Justify last line flag
$flags |= 1 == 1 ? 0x10 : 0;
$flags |= 1 == $bIndent ? 0x20 : 0;
$flags |= 1 == $bShrinkToFit ? 0x40 : 0;
// Default
$flags |= 1 == 1 ? 0x80 : 0;
// Protection
$flags |= 1 == $bProtLocked ? 0x100 : 0;
$flags |= 1 == $bProtHidden ? 0x200 : 0;
// Border
$flags |= 1 == $bBorderLeft ? 0x400 : 0;
$flags |= 1 == $bBorderRight ? 0x800 : 0;
$flags |= 1 == $bBorderTop ? 0x1000 : 0;
$flags |= 1 == $bBorderBottom ? 0x2000 : 0;
$flags |= 1 == 1 ? 0x4000 : 0;
// Top left to Bottom right border
$flags |= 1 == 1 ? 0x8000 : 0;
// Bottom left to Top right border
// Pattern
$flags |= 1 == $bFillStyle ? 0x10000 : 0;
$flags |= 1 == $bFillColor ? 0x20000 : 0;
$flags |= 1 == $bFillColorBg ? 0x40000 : 0;
$flags |= 1 == 1 ? 0x380000 : 0;
// Font
$flags |= 1 == $bFormatFont ? 0x4000000 : 0;
// Alignment :
$flags |= 1 == $bFormatAlign ? 0x8000000 : 0;
// Border
$flags |= 1 == $bFormatBorder ? 0x10000000 : 0;
// Pattern
$flags |= 1 == $bFormatFill ? 0x20000000 : 0;
// Protection
$flags |= 1 == $bFormatProt ? 0x40000000 : 0;
// Text direction
$flags |= 1 == 0 ? 0x80000000 : 0;
// Data Blocks
if ($bFormatFont == 1) {
// Font Name
if ($conditional->getStyle()->getFont()->getName() == null) {
$dataBlockFont = pack('VVVVVVVV', 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0);
$dataBlockFont .= pack('VVVVVVVV', 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0);
} else {
$dataBlockFont = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($conditional->getStyle()->getFont()->getName());
}
// Font Size
if ($conditional->getStyle()->getFont()->getSize() == null) {
$dataBlockFont .= pack('V', 20 * 11);
} else {
$dataBlockFont .= pack('V', 20 * $conditional->getStyle()->getFont()->getSize());
}
// Font Options
$dataBlockFont .= pack('V', 0);
// Font weight
if ($conditional->getStyle()->getFont()->getBold() == true) {
$dataBlockFont .= pack('v', 0x2bc);
} else {
$dataBlockFont .= pack('v', 0x190);
}
// Escapement type
if ($conditional->getStyle()->getFont()->getSubScript() == true) {
$dataBlockFont .= pack('v', 0x2);
$fontEscapement = 0;
} else {
if ($conditional->getStyle()->getFont()->getSuperScript() == true) {
$dataBlockFont .= pack('v', 0x1);
$fontEscapement = 0;
} else {
$dataBlockFont .= pack('v', 0x0);
$fontEscapement = 1;
}
}
// Underline type
switch ($conditional->getStyle()->getFont()->getUnderline()) {
case PHPExcel_Style_Font::UNDERLINE_NONE:
$dataBlockFont .= pack('C', 0x0);
$fontUnderline = 0;
break;
case PHPExcel_Style_Font::UNDERLINE_DOUBLE:
$dataBlockFont .= pack('C', 0x2);
$fontUnderline = 0;
break;
case PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING:
$dataBlockFont .= pack('C', 0x22);
$fontUnderline = 0;
break;
case PHPExcel_Style_Font::UNDERLINE_SINGLE:
$dataBlockFont .= pack('C', 0x1);
$fontUnderline = 0;
break;
case PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING:
$dataBlockFont .= pack('C', 0x21);
$fontUnderline = 0;
break;
default:
$dataBlockFont .= pack('C', 0x0);
$fontUnderline = 1;
break;
}
// Not used (3)
$dataBlockFont .= pack('vC', 0x0, 0x0);
// Font color index
switch ($conditional->getStyle()->getFont()->getColor()->getRGB()) {
case '000000':
$colorIdx = 0x8;
break;
case 'FFFFFF':
$colorIdx = 0x9;
break;
case 'FF0000':
$colorIdx = 0xa;
break;
case '00FF00':
$colorIdx = 0xb;
break;
case '0000FF':
$colorIdx = 0xc;
break;
case 'FFFF00':
$colorIdx = 0xd;
break;
case 'FF00FF':
$colorIdx = 0xe;
break;
case '00FFFF':
$colorIdx = 0xf;
break;
case '800000':
$colorIdx = 0x10;
break;
case '008000':
$colorIdx = 0x11;
break;
case '000080':
$colorIdx = 0x12;
break;
case '808000':
$colorIdx = 0x13;
break;
case '800080':
$colorIdx = 0x14;
break;
case '008080':
$colorIdx = 0x15;
break;
case 'C0C0C0':
$colorIdx = 0x16;
break;
case '808080':
$colorIdx = 0x17;
break;
case '9999FF':
$colorIdx = 0x18;
break;
case '993366':
$colorIdx = 0x19;
break;
case 'FFFFCC':
$colorIdx = 0x1a;
break;
case 'CCFFFF':
$colorIdx = 0x1b;
break;
case '660066':
$colorIdx = 0x1c;
break;
case 'FF8080':
$colorIdx = 0x1d;
break;
case '0066CC':
$colorIdx = 0x1e;
break;
case 'CCCCFF':
$colorIdx = 0x1f;
break;
case '000080':
$colorIdx = 0x20;
break;
case 'FF00FF':
$colorIdx = 0x21;
break;
case 'FFFF00':
$colorIdx = 0x22;
break;
case '00FFFF':
$colorIdx = 0x23;
break;
case '800080':
$colorIdx = 0x24;
break;
case '800000':
$colorIdx = 0x25;
break;
case '008080':
$colorIdx = 0x26;
break;
case '0000FF':
$colorIdx = 0x27;
break;
case '00CCFF':
$colorIdx = 0x28;
break;
case 'CCFFFF':
$colorIdx = 0x29;
break;
case 'CCFFCC':
$colorIdx = 0x2a;
break;
case 'FFFF99':
$colorIdx = 0x2b;
break;
case '99CCFF':
$colorIdx = 0x2c;
break;
case 'FF99CC':
$colorIdx = 0x2d;
break;
case 'CC99FF':
$colorIdx = 0x2e;
break;
case 'FFCC99':
$colorIdx = 0x2f;
break;
case '3366FF':
$colorIdx = 0x30;
break;
case '33CCCC':
$colorIdx = 0x31;
break;
case '99CC00':
$colorIdx = 0x32;
break;
case 'FFCC00':
$colorIdx = 0x33;
break;
case 'FF9900':
$colorIdx = 0x34;
break;
case 'FF6600':
$colorIdx = 0x35;
break;
case '666699':
$colorIdx = 0x36;
break;
case '969696':
$colorIdx = 0x37;
break;
case '003366':
$colorIdx = 0x38;
break;
case '339966':
$colorIdx = 0x39;
break;
case '003300':
$colorIdx = 0x3a;
break;
case '333300':
$colorIdx = 0x3b;
break;
case '993300':
$colorIdx = 0x3c;
break;
case '993366':
$colorIdx = 0x3d;
break;
case '333399':
$colorIdx = 0x3e;
break;
case '333333':
$colorIdx = 0x3f;
break;
default:
$colorIdx = 0x0;
break;
}
$dataBlockFont .= pack('V', $colorIdx);
// Not used (4)
$dataBlockFont .= pack('V', 0x0);
// Options flags for modified font attributes
$optionsFlags = 0;
$optionsFlagsBold = $conditional->getStyle()->getFont()->getBold() == null ? 1 : 0;
$optionsFlags |= 1 == $optionsFlagsBold ? 0x2 : 0;
$optionsFlags |= 1 == 1 ? 0x8 : 0;
$optionsFlags |= 1 == 1 ? 0x10 : 0;
$optionsFlags |= 1 == 0 ? 0x20 : 0;
$optionsFlags |= 1 == 1 ? 0x80 : 0;
$dataBlockFont .= pack('V', $optionsFlags);
// Escapement type
$dataBlockFont .= pack('V', $fontEscapement);
// Underline type
$dataBlockFont .= pack('V', $fontUnderline);
// Always
$dataBlockFont .= pack('V', 0x0);
// Always
$dataBlockFont .= pack('V', 0x0);
// Not used (8)
$dataBlockFont .= pack('VV', 0x0, 0x0);
// Always
$dataBlockFont .= pack('v', 0x1);
}
if ($bFormatAlign == 1) {
$blockAlign = 0;
// Alignment and text break
switch ($conditional->getStyle()->getAlignment()->getHorizontal()) {
case PHPExcel_Style_Alignment::HORIZONTAL_GENERAL:
$blockAlign = 0;
break;
case PHPExcel_Style_Alignment::HORIZONTAL_LEFT:
$blockAlign = 1;
break;
case PHPExcel_Style_Alignment::HORIZONTAL_RIGHT:
$blockAlign = 3;
break;
case PHPExcel_Style_Alignment::HORIZONTAL_CENTER:
$blockAlign = 2;
break;
case PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS:
$blockAlign = 6;
break;
case PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY:
$blockAlign = 5;
break;
}
if ($conditional->getStyle()->getAlignment()->getWrapText() == true) {
$blockAlign |= 1 << 3;
} else {
$blockAlign |= 0 << 3;
}
switch ($conditional->getStyle()->getAlignment()->getVertical()) {
case PHPExcel_Style_Alignment::VERTICAL_BOTTOM:
$blockAlign = 2 << 4;
break;
case PHPExcel_Style_Alignment::VERTICAL_TOP:
$blockAlign = 0 << 4;
break;
case PHPExcel_Style_Alignment::VERTICAL_CENTER:
$blockAlign = 1 << 4;
break;
case PHPExcel_Style_Alignment::VERTICAL_JUSTIFY:
$blockAlign = 3 << 4;
break;
}
$blockAlign |= 0 << 7;
// Text rotation angle
$blockRotation = $conditional->getStyle()->getAlignment()->getTextRotation();
// Indentation
$blockIndent = $conditional->getStyle()->getAlignment()->getIndent();
if ($conditional->getStyle()->getAlignment()->getShrinkToFit() == true) {
$blockIndent |= 1 << 4;
} else {
$blockIndent |= 0 << 4;
}
$blockIndent |= 0 << 6;
// Relative indentation
$blockIndentRelative = 255;
$dataBlockAlign = pack('CCvvv', $blockAlign, $blockRotation, $blockIndent, $blockIndentRelative, 0x0);
}
if ($bFormatBorder == 1) {
$blockLineStyle = 0;
switch ($conditional->getStyle()->getBorders()->getLeft()->getBorderStyle()) {
case PHPExcel_Style_Border::BORDER_NONE:
$blockLineStyle |= 0x0;
break;
case PHPExcel_Style_Border::BORDER_THIN:
$blockLineStyle |= 0x1;
break;
case PHPExcel_Style_Border::BORDER_MEDIUM:
$blockLineStyle |= 0x2;
break;
case PHPExcel_Style_Border::BORDER_DASHED:
$blockLineStyle |= 0x3;
break;
case PHPExcel_Style_Border::BORDER_DOTTED:
$blockLineStyle |= 0x4;
break;
case PHPExcel_Style_Border::BORDER_THICK:
$blockLineStyle |= 0x5;
break;
case PHPExcel_Style_Border::BORDER_DOUBLE:
$blockLineStyle |= 0x6;
break;
case PHPExcel_Style_Border::BORDER_HAIR:
$blockLineStyle |= 0x7;
break;
case PHPExcel_Style_Border::BORDER_MEDIUMDASHED:
$blockLineStyle |= 0x8;
break;
case PHPExcel_Style_Border::BORDER_DASHDOT:
$blockLineStyle |= 0x9;
break;
case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT:
$blockLineStyle |= 0xa;
break;
case PHPExcel_Style_Border::BORDER_DASHDOTDOT:
$blockLineStyle |= 0xb;
break;
case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT:
$blockLineStyle |= 0xc;
break;
case PHPExcel_Style_Border::BORDER_SLANTDASHDOT:
$blockLineStyle |= 0xd;
break;
}
switch ($conditional->getStyle()->getBorders()->getRight()->getBorderStyle()) {
case PHPExcel_Style_Border::BORDER_NONE:
$blockLineStyle |= 0x0 << 4;
break;
case PHPExcel_Style_Border::BORDER_THIN:
$blockLineStyle |= 0x1 << 4;
break;
case PHPExcel_Style_Border::BORDER_MEDIUM:
$blockLineStyle |= 0x2 << 4;
break;
case PHPExcel_Style_Border::BORDER_DASHED:
$blockLineStyle |= 0x3 << 4;
break;
case PHPExcel_Style_Border::BORDER_DOTTED:
$blockLineStyle |= 0x4 << 4;
break;
case PHPExcel_Style_Border::BORDER_THICK:
$blockLineStyle |= 0x5 << 4;
break;
case PHPExcel_Style_Border::BORDER_DOUBLE:
$blockLineStyle |= 0x6 << 4;
break;
case PHPExcel_Style_Border::BORDER_HAIR:
$blockLineStyle |= 0x7 << 4;
break;
case PHPExcel_Style_Border::BORDER_MEDIUMDASHED:
$blockLineStyle |= 0x8 << 4;
break;
case PHPExcel_Style_Border::BORDER_DASHDOT:
$blockLineStyle |= 0x9 << 4;
break;
case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT:
$blockLineStyle |= 0xa << 4;
break;
case PHPExcel_Style_Border::BORDER_DASHDOTDOT:
$blockLineStyle |= 0xb << 4;
break;
case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT:
$blockLineStyle |= 0xc << 4;
break;
case PHPExcel_Style_Border::BORDER_SLANTDASHDOT:
$blockLineStyle |= 0xd << 4;
break;
}
switch ($conditional->getStyle()->getBorders()->getTop()->getBorderStyle()) {
case PHPExcel_Style_Border::BORDER_NONE:
$blockLineStyle |= 0x0 << 8;
break;
case PHPExcel_Style_Border::BORDER_THIN:
$blockLineStyle |= 0x1 << 8;
break;
case PHPExcel_Style_Border::BORDER_MEDIUM:
$blockLineStyle |= 0x2 << 8;
break;
case PHPExcel_Style_Border::BORDER_DASHED:
$blockLineStyle |= 0x3 << 8;
break;
case PHPExcel_Style_Border::BORDER_DOTTED:
$blockLineStyle |= 0x4 << 8;
break;
case PHPExcel_Style_Border::BORDER_THICK:
$blockLineStyle |= 0x5 << 8;
break;
case PHPExcel_Style_Border::BORDER_DOUBLE:
$blockLineStyle |= 0x6 << 8;
break;
case PHPExcel_Style_Border::BORDER_HAIR:
$blockLineStyle |= 0x7 << 8;
break;
case PHPExcel_Style_Border::BORDER_MEDIUMDASHED:
$blockLineStyle |= 0x8 << 8;
break;
case PHPExcel_Style_Border::BORDER_DASHDOT:
$blockLineStyle |= 0x9 << 8;
break;
case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT:
$blockLineStyle |= 0xa << 8;
break;
case PHPExcel_Style_Border::BORDER_DASHDOTDOT:
$blockLineStyle |= 0xb << 8;
break;
case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT:
$blockLineStyle |= 0xc << 8;
break;
case PHPExcel_Style_Border::BORDER_SLANTDASHDOT:
$blockLineStyle |= 0xd << 8;
break;
}
switch ($conditional->getStyle()->getBorders()->getBottom()->getBorderStyle()) {
case PHPExcel_Style_Border::BORDER_NONE:
$blockLineStyle |= 0x0 << 12;
break;
case PHPExcel_Style_Border::BORDER_THIN:
$blockLineStyle |= 0x1 << 12;
break;
case PHPExcel_Style_Border::BORDER_MEDIUM:
$blockLineStyle |= 0x2 << 12;
break;
case PHPExcel_Style_Border::BORDER_DASHED:
$blockLineStyle |= 0x3 << 12;
break;
case PHPExcel_Style_Border::BORDER_DOTTED:
$blockLineStyle |= 0x4 << 12;
break;
case PHPExcel_Style_Border::BORDER_THICK:
$blockLineStyle |= 0x5 << 12;
break;
case PHPExcel_Style_Border::BORDER_DOUBLE:
$blockLineStyle |= 0x6 << 12;
break;
case PHPExcel_Style_Border::BORDER_HAIR:
$blockLineStyle |= 0x7 << 12;
break;
case PHPExcel_Style_Border::BORDER_MEDIUMDASHED:
$blockLineStyle |= 0x8 << 12;
break;
case PHPExcel_Style_Border::BORDER_DASHDOT:
$blockLineStyle |= 0x9 << 12;
break;
case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT:
$blockLineStyle |= 0xa << 12;
break;
case PHPExcel_Style_Border::BORDER_DASHDOTDOT:
$blockLineStyle |= 0xb << 12;
break;
case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT:
$blockLineStyle |= 0xc << 12;
break;
case PHPExcel_Style_Border::BORDER_SLANTDASHDOT:
$blockLineStyle |= 0xd << 12;
break;
}
//@todo _writeCFRule() => $blockLineStyle => Index Color for left line
//@todo _writeCFRule() => $blockLineStyle => Index Color for right line
//@todo _writeCFRule() => $blockLineStyle => Top-left to bottom-right on/off
//@todo _writeCFRule() => $blockLineStyle => Bottom-left to top-right on/off
$blockColor = 0;
//@todo _writeCFRule() => $blockColor => Index Color for top line
//@todo _writeCFRule() => $blockColor => Index Color for bottom line
//@todo _writeCFRule() => $blockColor => Index Color for diagonal line
switch ($conditional->getStyle()->getBorders()->getDiagonal()->getBorderStyle()) {
case PHPExcel_Style_Border::BORDER_NONE:
$blockColor |= 0x0 << 21;
break;
case PHPExcel_Style_Border::BORDER_THIN:
$blockColor |= 0x1 << 21;
break;
case PHPExcel_Style_Border::BORDER_MEDIUM:
$blockColor |= 0x2 << 21;
break;
case PHPExcel_Style_Border::BORDER_DASHED:
$blockColor |= 0x3 << 21;
break;
case PHPExcel_Style_Border::BORDER_DOTTED:
$blockColor |= 0x4 << 21;
break;
case PHPExcel_Style_Border::BORDER_THICK:
$blockColor |= 0x5 << 21;
break;
case PHPExcel_Style_Border::BORDER_DOUBLE:
$blockColor |= 0x6 << 21;
break;
case PHPExcel_Style_Border::BORDER_HAIR:
$blockColor |= 0x7 << 21;
break;
case PHPExcel_Style_Border::BORDER_MEDIUMDASHED:
$blockColor |= 0x8 << 21;
break;
case PHPExcel_Style_Border::BORDER_DASHDOT:
$blockColor |= 0x9 << 21;
break;
case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT:
$blockColor |= 0xa << 21;
break;
case PHPExcel_Style_Border::BORDER_DASHDOTDOT:
$blockColor |= 0xb << 21;
break;
case PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT:
$blockColor |= 0xc << 21;
break;
case PHPExcel_Style_Border::BORDER_SLANTDASHDOT:
$blockColor |= 0xd << 21;
break;
}
$dataBlockBorder = pack('vv', $blockLineStyle, $blockColor);
}
if ($bFormatFill == 1) {
// Fill Patern Style
$blockFillPatternStyle = 0;
switch ($conditional->getStyle()->getFill()->getFillType()) {
case PHPExcel_Style_Fill::FILL_NONE:
$blockFillPatternStyle = 0x0;
break;
case PHPExcel_Style_Fill::FILL_SOLID:
$blockFillPatternStyle = 0x1;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY:
$blockFillPatternStyle = 0x2;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY:
$blockFillPatternStyle = 0x3;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY:
$blockFillPatternStyle = 0x4;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL:
$blockFillPatternStyle = 0x5;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL:
$blockFillPatternStyle = 0x6;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN:
$blockFillPatternStyle = 0x7;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_DARKUP:
$blockFillPatternStyle = 0x8;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID:
$blockFillPatternStyle = 0x9;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS:
$blockFillPatternStyle = 0xa;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL:
$blockFillPatternStyle = 0xb;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL:
$blockFillPatternStyle = 0xc;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN:
$blockFillPatternStyle = 0xd;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP:
$blockFillPatternStyle = 0xe;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID:
$blockFillPatternStyle = 0xf;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS:
$blockFillPatternStyle = 0x10;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_GRAY125:
$blockFillPatternStyle = 0x11;
break;
case PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625:
$blockFillPatternStyle = 0x12;
break;
case PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR:
$blockFillPatternStyle = 0x0;
break;
// does not exist in BIFF8
// does not exist in BIFF8
case PHPExcel_Style_Fill::FILL_GRADIENT_PATH:
$blockFillPatternStyle = 0x0;
break;
// does not exist in BIFF8
// does not exist in BIFF8
default:
$blockFillPatternStyle = 0x0;
break;
}
// Color
switch ($conditional->getStyle()->getFill()->getStartColor()->getRGB()) {
case '000000':
$colorIdxBg = 0x8;
break;
case 'FFFFFF':
$colorIdxBg = 0x9;
break;
case 'FF0000':
$colorIdxBg = 0xa;
break;
case '00FF00':
$colorIdxBg = 0xb;
break;
case '0000FF':
$colorIdxBg = 0xc;
break;
case 'FFFF00':
$colorIdxBg = 0xd;
break;
case 'FF00FF':
$colorIdxBg = 0xe;
break;
case '00FFFF':
$colorIdxBg = 0xf;
break;
case '800000':
$colorIdxBg = 0x10;
break;
case '008000':
$colorIdxBg = 0x11;
break;
case '000080':
$colorIdxBg = 0x12;
break;
case '808000':
$colorIdxBg = 0x13;
break;
case '800080':
$colorIdxBg = 0x14;
break;
case '008080':
$colorIdxBg = 0x15;
break;
case 'C0C0C0':
$colorIdxBg = 0x16;
break;
case '808080':
$colorIdxBg = 0x17;
break;
case '9999FF':
$colorIdxBg = 0x18;
break;
case '993366':
$colorIdxBg = 0x19;
break;
case 'FFFFCC':
$colorIdxBg = 0x1a;
break;
case 'CCFFFF':
$colorIdxBg = 0x1b;
break;
case '660066':
$colorIdxBg = 0x1c;
break;
case 'FF8080':
$colorIdxBg = 0x1d;
break;
case '0066CC':
$colorIdxBg = 0x1e;
break;
case 'CCCCFF':
$colorIdxBg = 0x1f;
break;
case '000080':
$colorIdxBg = 0x20;
break;
case 'FF00FF':
$colorIdxBg = 0x21;
break;
case 'FFFF00':
$colorIdxBg = 0x22;
break;
case '00FFFF':
$colorIdxBg = 0x23;
break;
case '800080':
$colorIdxBg = 0x24;
break;
case '800000':
$colorIdxBg = 0x25;
break;
case '008080':
$colorIdxBg = 0x26;
break;
case '0000FF':
$colorIdxBg = 0x27;
break;
case '00CCFF':
$colorIdxBg = 0x28;
break;
case 'CCFFFF':
$colorIdxBg = 0x29;
break;
case 'CCFFCC':
$colorIdxBg = 0x2a;
break;
case 'FFFF99':
$colorIdxBg = 0x2b;
break;
case '99CCFF':
$colorIdxBg = 0x2c;
break;
case 'FF99CC':
$colorIdxBg = 0x2d;
break;
case 'CC99FF':
$colorIdxBg = 0x2e;
break;
case 'FFCC99':
$colorIdxBg = 0x2f;
break;
case '3366FF':
$colorIdxBg = 0x30;
break;
case '33CCCC':
$colorIdxBg = 0x31;
break;
case '99CC00':
$colorIdxBg = 0x32;
break;
case 'FFCC00':
$colorIdxBg = 0x33;
break;
case 'FF9900':
$colorIdxBg = 0x34;
break;
case 'FF6600':
$colorIdxBg = 0x35;
break;
case '666699':
$colorIdxBg = 0x36;
break;
case '969696':
$colorIdxBg = 0x37;
break;
case '003366':
$colorIdxBg = 0x38;
break;
case '339966':
$colorIdxBg = 0x39;
break;
case '003300':
$colorIdxBg = 0x3a;
break;
case '333300':
$colorIdxBg = 0x3b;
break;
case '993300':
$colorIdxBg = 0x3c;
break;
case '993366':
$colorIdxBg = 0x3d;
break;
case '333399':
$colorIdxBg = 0x3e;
break;
case '333333':
$colorIdxBg = 0x3f;
break;
default:
$colorIdxBg = 0x41;
break;
}
// Fg Color
switch ($conditional->getStyle()->getFill()->getEndColor()->getRGB()) {
case '000000':
$colorIdxFg = 0x8;
break;
case 'FFFFFF':
$colorIdxFg = 0x9;
break;
case 'FF0000':
$colorIdxFg = 0xa;
break;
case '00FF00':
$colorIdxFg = 0xb;
break;
case '0000FF':
$colorIdxFg = 0xc;
break;
case 'FFFF00':
$colorIdxFg = 0xd;
break;
case 'FF00FF':
$colorIdxFg = 0xe;
break;
case '00FFFF':
$colorIdxFg = 0xf;
break;
case '800000':
$colorIdxFg = 0x10;
break;
case '008000':
$colorIdxFg = 0x11;
break;
case '000080':
$colorIdxFg = 0x12;
break;
case '808000':
$colorIdxFg = 0x13;
break;
case '800080':
$colorIdxFg = 0x14;
break;
case '008080':
$colorIdxFg = 0x15;
break;
case 'C0C0C0':
$colorIdxFg = 0x16;
break;
case '808080':
$colorIdxFg = 0x17;
break;
case '9999FF':
$colorIdxFg = 0x18;
break;
case '993366':
$colorIdxFg = 0x19;
break;
case 'FFFFCC':
$colorIdxFg = 0x1a;
break;
case 'CCFFFF':
$colorIdxFg = 0x1b;
break;
case '660066':
$colorIdxFg = 0x1c;
break;
case 'FF8080':
$colorIdxFg = 0x1d;
break;
case '0066CC':
$colorIdxFg = 0x1e;
break;
case 'CCCCFF':
$colorIdxFg = 0x1f;
break;
case '000080':
$colorIdxFg = 0x20;
break;
case 'FF00FF':
$colorIdxFg = 0x21;
break;
case 'FFFF00':
$colorIdxFg = 0x22;
break;
case '00FFFF':
$colorIdxFg = 0x23;
break;
case '800080':
$colorIdxFg = 0x24;
break;
case '800000':
$colorIdxFg = 0x25;
break;
case '008080':
$colorIdxFg = 0x26;
break;
case '0000FF':
$colorIdxFg = 0x27;
break;
case '00CCFF':
$colorIdxFg = 0x28;
break;
case 'CCFFFF':
$colorIdxFg = 0x29;
break;
case 'CCFFCC':
$colorIdxFg = 0x2a;
break;
case 'FFFF99':
$colorIdxFg = 0x2b;
break;
case '99CCFF':
$colorIdxFg = 0x2c;
break;
case 'FF99CC':
$colorIdxFg = 0x2d;
break;
case 'CC99FF':
$colorIdxFg = 0x2e;
break;
case 'FFCC99':
$colorIdxFg = 0x2f;
break;
case '3366FF':
$colorIdxFg = 0x30;
break;
case '33CCCC':
$colorIdxFg = 0x31;
break;
case '99CC00':
$colorIdxFg = 0x32;
break;
case 'FFCC00':
$colorIdxFg = 0x33;
break;
case 'FF9900':
$colorIdxFg = 0x34;
break;
case 'FF6600':
$colorIdxFg = 0x35;
break;
case '666699':
$colorIdxFg = 0x36;
break;
case '969696':
$colorIdxFg = 0x37;
break;
case '003366':
$colorIdxFg = 0x38;
break;
case '339966':
$colorIdxFg = 0x39;
break;
case '003300':
$colorIdxFg = 0x3a;
break;
case '333300':
$colorIdxFg = 0x3b;
break;
case '993300':
$colorIdxFg = 0x3c;
break;
case '993366':
$colorIdxFg = 0x3d;
break;
case '333399':
$colorIdxFg = 0x3e;
break;
case '333333':
$colorIdxFg = 0x3f;
break;
default:
$colorIdxFg = 0x40;
break;
}
$dataBlockFill = pack('v', $blockFillPatternStyle);
$dataBlockFill .= pack('v', $colorIdxFg | $colorIdxBg << 7);
}
if ($bFormatProt == 1) {
$dataBlockProtection = 0;
if ($conditional->getStyle()->getProtection()->getLocked() == PHPExcel_Style_Protection::PROTECTION_PROTECTED) {
$dataBlockProtection = 1;
}
if ($conditional->getStyle()->getProtection()->getHidden() == PHPExcel_Style_Protection::PROTECTION_PROTECTED) {
$dataBlockProtection = 1 << 1;
}
}
$data = pack('CCvvVv', $type, $operatorType, $szValue1, $szValue2, $flags, 0x0);
if ($bFormatFont == 1) {
// Block Formatting : OK
$data .= $dataBlockFont;
}
if ($bFormatAlign == 1) {
$data .= $dataBlockAlign;
}
if ($bFormatBorder == 1) {
$data .= $dataBlockBorder;
}
if ($bFormatFill == 1) {
// Block Formatting : OK
$data .= $dataBlockFill;
}
if ($bFormatProt == 1) {
$data .= $dataBlockProtection;
}
if (!is_null($operand1)) {
$data .= $operand1;
}
if (!is_null($operand2)) {
$data .= $operand2;
}
$header = pack('vv', $record, strlen($data));
$this->_append($header . $data);
}
*
* @category PHPExcel
* @package PHPExcel
* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
* @version ##VERSION##, ##DATE##
*/
PHPExcel_Autoloader::Register();
// As we always try to run the autoloader before anything else, we can use it to do a few
// simple checks and initialisations
//PHPExcel_Shared_ZipStreamWrapper::register();
// check mbstring.func_overload
if (ini_get('mbstring.func_overload') & 2) {
throw new PHPExcel_Exception('Multibyte function overloading in PHP must be disabled for string functions (2).');
}
PHPExcel_Shared_String::buildCharacterSets();
/**
* PHPExcel_Autoloader
*
* @category PHPExcel
* @package PHPExcel
* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
*/
class PHPExcel_Autoloader
{
/**
* Register the Autoloader with SPL
*
*/
public static function Register()
{
/**
* Write Rich Text
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_RichText $pRichText Rich text
* @throws Exception
*/
public function writeRichText(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_RichText $pRichText = null)
{
// Loop trough rich text elements
$elements = $pRichText->getRichTextElements();
foreach ($elements as $element) {
// r
$objWriter->startElement('r');
// rPr
if ($element instanceof PHPExcel_RichText_Run) {
// rPr
$objWriter->startElement('rPr');
// rFont
$objWriter->startElement('rFont');
$objWriter->writeAttribute('val', $element->getFont()->getName());
$objWriter->endElement();
// Bold
$objWriter->startElement('b');
$objWriter->writeAttribute('val', $element->getFont()->getBold() ? 'true' : 'false');
$objWriter->endElement();
// Italic
$objWriter->startElement('i');
$objWriter->writeAttribute('val', $element->getFont()->getItalic() ? 'true' : 'false');
$objWriter->endElement();
// Striketrough
$objWriter->startElement('strike');
$objWriter->writeAttribute('val', $element->getFont()->getStriketrough() ? 'true' : 'false');
$objWriter->endElement();
// Color
$objWriter->startElement('color');
$objWriter->writeAttribute('rgb', $element->getFont()->getColor()->getARGB());
$objWriter->endElement();
// Size
$objWriter->startElement('sz');
$objWriter->writeAttribute('val', $element->getFont()->getSize());
$objWriter->endElement();
// Underline
$objWriter->startElement('u');
$objWriter->writeAttribute('val', $element->getFont()->getUnderline());
$objWriter->endElement();
$objWriter->endElement();
}
// t
$objWriter->startElement('t');
$objWriter->writeAttribute('xml:space', 'preserve');
$objWriter->writeRaw(PHPExcel_Shared_String::ControlCharacterPHP2OOXML($element->getText()));
$objWriter->endElement();
$objWriter->endElement();
}
}
private function _validateBinaryOperand($cellID, &$operand, &$stack)
{
if (is_array($operand)) {
if (count($operand, COUNT_RECURSIVE) - count($operand) == 1) {
do {
$operand = array_pop($operand);
} while (is_array($operand));
}
}
// Numbers, matrices and booleans can pass straight through, as they're already valid
if (is_string($operand)) {
// We only need special validations for the operand if it is a string
// Start by stripping off the quotation marks we use to identify true excel string values internally
if ($operand > '' && $operand[0] == '"') {
$operand = self::_unwrapResult($operand);
}
// If the string is a numeric value, we treat it as a numeric, so no further testing
if (!is_numeric($operand)) {
// If not a numeric, test to see if the value is an Excel error, and so can't be used in normal binary operations
if ($operand > '' && $operand[0] == '#') {
$stack->push('Value', $operand);
$this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($operand));
return FALSE;
} elseif (!PHPExcel_Shared_String::convertToNumberIfFraction($operand)) {
// If not a numeric or a fraction, then it's a text string, and so can't be used in mathematical binary operations
$stack->push('Value', '#VALUE!');
$this->_debugLog->writeDebugLog('Evaluation Result is a ', $this->_showTypeDetails('#VALUE!'));
return FALSE;
}
}
}
// return a true if the value of the operand is one that we can use in normal binary operations
return TRUE;
}
/**
* Writes Excel FORMAT record for non "built-in" numerical formats.
*
* @param string $format Custom format string
* @param integer $ifmt Format index code
*/
private function writeNumberFormat($format, $ifmt)
{
$record = 0x41e;
// Record identifier
$numberFormatString = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($format);
$length = 2 + strlen($numberFormatString);
// Number of bytes to follow
$header = pack("vv", $record, $length);
$data = pack("v", $ifmt) . $numberFormatString;
$this->append($header . $data);
}
/**
* Get font record data
*
* @return string
*/
public function writeFont()
{
$font_outline = 0;
$font_shadow = 0;
$icv = $this->_colorIndex;
// Index to color palette
if ($this->_font->getSuperScript()) {
$sss = 1;
} else {
if ($this->_font->getSubScript()) {
$sss = 2;
} else {
$sss = 0;
}
}
$bFamily = 0;
// Font family
$bCharSet = PHPExcel_Shared_Font::getCharsetFromFontName($this->_font->getName());
// Character set
$record = 0x31;
// Record identifier
$reserved = 0x0;
// Reserved
$grbit = 0x0;
// Font attributes
if ($this->_font->getItalic()) {
$grbit |= 0x2;
}
if ($this->_font->getStrikethrough()) {
$grbit |= 0x8;
}
if ($font_outline) {
$grbit |= 0x10;
}
if ($font_shadow) {
$grbit |= 0x20;
}
if ($this->_BIFFVersion == 0x500) {
$data = pack("vvvvvCCCCC", $this->_font->getSize() * 20, $grbit, $icv, $this->_mapBold($this->_font->getBold()), $sss, $this->_mapUnderline($this->_font->getUnderline()), $bFamily, $bCharSet, $reserved, strlen($this->_font->getName()));
$data .= $this->_font->getName();
} elseif ($this->_BIFFVersion == 0x600) {
$data = pack("vvvvvCCCC", $this->_font->getSize() * 20, $grbit, $icv, $this->_mapBold($this->_font->getBold()), $sss, $this->_mapUnderline($this->_font->getUnderline()), $bFamily, $bCharSet, $reserved);
$data .= PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($this->_font->getName());
}
$length = strlen($data);
$header = pack("vv", $record, $length);
return $header . $data;
}
/**
* Set the currency code. Only used by PHPExcel_Style_NumberFormat::toFormattedString()
* to format output by PHPExcel_Writer_HTML and PHPExcel_Writer_PDF
*
* @param string $pValue Character for currency code
*/
public static function setCurrencyCode($pValue = '$')
{
self::$_currencyCode = $pValue;
}
/**
* Define the code name of the sheet
*
* @param null|string Same rule as Title minus space not allowed (but, like Excel, change silently space to underscore)
* @return objWorksheet
* @throws PHPExcel_Exception
*/
public function setCodeName($pValue = null)
{
// Is this a 'rename' or not?
if ($this->getCodeName() == $pValue) {
return $this;
}
$pValue = str_replace(' ', '_', $pValue);
//Excel does this automatically without flinching, we are doing the same
// Syntax check
// throw an exception if not valid
self::_checkSheetCodeName($pValue);
// We use the same code that setTitle to find a valid codeName else not using a space (Excel don't like) but a '_'
if ($this->getParent()) {
// Is there already such sheet name?
if ($this->getParent()->sheetCodeNameExists($pValue)) {
// Use name, but append with lowest possible integer
if (PHPExcel_Shared_String::CountCharacters($pValue) > 29) {
$pValue = PHPExcel_Shared_String::Substring($pValue, 0, 29);
}
$i = 1;
while ($this->getParent()->sheetCodeNameExists($pValue . '_' . $i)) {
++$i;
if ($i == 10) {
if (PHPExcel_Shared_String::CountCharacters($pValue) > 28) {
$pValue = PHPExcel_Shared_String::Substring($pValue, 0, 28);
}
} elseif ($i == 100) {
if (PHPExcel_Shared_String::CountCharacters($pValue) > 27) {
$pValue = PHPExcel_Shared_String::Substring($pValue, 0, 27);
}
}
}
$pValue = $pValue . '_' . $i;
// ok, we have a valid name
//codeName is'nt used in formula : no need to call for an update
//return $this->setTitle($altTitle,$updateFormulaCellReferences);
}
}
$this->_codeName = $pValue;
return $this;
}
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param PHPExcel $objPHPExcel
* @return PHPExcel
* @throws Exception
*/
public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
// Create new PHPExcel
while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) {
$objPHPExcel->createSheet();
}
$objPHPExcel->setActiveSheetIndex($this->_sheetIndex);
$fromFormats = array('\\-', '\\ ');
$toFormats = array('-', ' ');
// Open file
$fileHandle = fopen($pFilename, 'r');
if ($fileHandle === false) {
throw new Exception("Could not open file {$pFilename} for reading.");
}
// 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_String::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);
foreach ($temp as $key => &$value) {
// Only count/replace in alternate array entries
if ($key % 2 == 0) {
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);
$objPHPExcel->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($this->_formats[$formatStyle]);
}
if (count($styleData) > 0 && $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;
}
/**
* power
*
* A = A ^ B
* @param mixed $B Matrix/Array
* @return Matrix Sum
*/
public function power()
{
if (func_num_args() > 0) {
$args = func_get_args();
$match = implode(",", array_map('gettype', $args));
switch ($match) {
case 'object':
if ($args[0] instanceof PHPExcel_Shared_JAMA_Matrix) {
$M = $args[0];
} else {
throw new PHPExcel_Calculation_Exception(self::ARGUMENT_TYPE_EXCEPTION);
}
break;
case 'array':
$M = new PHPExcel_Shared_JAMA_Matrix($args[0]);
break;
default:
throw new PHPExcel_Calculation_Exception(self::POLYMORPHIC_ARGUMENT_EXCEPTION);
break;
}
$this->checkMatrixDimensions($M);
for ($i = 0; $i < $this->m; ++$i) {
for ($j = 0; $j < $this->n; ++$j) {
$validValues = true;
$value = $M->get($i, $j);
if (is_string($this->A[$i][$j]) && strlen($this->A[$i][$j]) > 0 && !is_numeric($this->A[$i][$j])) {
$this->A[$i][$j] = trim($this->A[$i][$j], '"');
$validValues &= PHPExcel_Shared_String::convertToNumberIfFraction($this->A[$i][$j]);
}
if (is_string($value) && strlen($value) > 0 && !is_numeric($value)) {
$value = trim($value, '"');
$validValues &= PHPExcel_Shared_String::convertToNumberIfFraction($value);
}
if ($validValues) {
$this->A[$i][$j] = pow($this->A[$i][$j], $value);
} else {
$this->A[$i][$j] = PHPExcel_Calculation_Functions::NaN();
}
}
}
return $this;
} else {
throw new PHPExcel_Calculation_Exception(self::POLYMORPHIC_ARGUMENT_EXCEPTION);
}
}
/**
* Write Value Axis
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Chart_PlotArea $plotArea
* @param PHPExcel_Chart_Title $yAxisLabel
* @param string $groupType Chart type
* @param string $id1
* @param string $id2
* @param boolean $isMultiLevelSeries
* @throws PHPExcel_Writer_Exception
*/
private function _writeValAx($objWriter, PHPExcel_Chart_PlotArea $plotArea, $yAxisLabel, $groupType, $id1, $id2, $isMultiLevelSeries)
{
$objWriter->startElement('c:valAx');
if ($id2 > 0) {
$objWriter->startElement('c:axId');
$objWriter->writeAttribute('val', $id2);
$objWriter->endElement();
}
$objWriter->startElement('c:scaling');
$objWriter->startElement('c:orientation');
$objWriter->writeAttribute('val', "minMax");
$objWriter->endElement();
$objWriter->endElement();
$objWriter->startElement('c:delete');
$objWriter->writeAttribute('val', 0);
$objWriter->endElement();
$objWriter->startElement('c:axPos');
$objWriter->writeAttribute('val', "l");
$objWriter->endElement();
$objWriter->startElement('c:majorGridlines');
$objWriter->endElement();
if (!is_null($yAxisLabel)) {
$objWriter->startElement('c:title');
$objWriter->startElement('c:tx');
$objWriter->startElement('c:rich');
$objWriter->startElement('a:bodyPr');
$objWriter->endElement();
$objWriter->startElement('a:lstStyle');
$objWriter->endElement();
$objWriter->startElement('a:p');
$objWriter->startElement('a:r');
$caption = $yAxisLabel->getCaption();
if (is_array($caption)) {
$caption = $caption[0];
}
$objWriter->startElement('a:t');
// $objWriter->writeAttribute('xml:space', 'preserve');
$objWriter->writeRawData(PHPExcel_Shared_String::ControlCharacterPHP2OOXML($caption));
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
if ($groupType !== PHPExcel_Chart_DataSeries::TYPE_BUBBLECHART) {
$layout = $yAxisLabel->getLayout();
$this->_writeLayout($layout, $objWriter);
}
$objWriter->startElement('c:overlay');
$objWriter->writeAttribute('val', 0);
$objWriter->endElement();
$objWriter->endElement();
}
$objWriter->startElement('c:numFmt');
$objWriter->writeAttribute('formatCode', "General");
$objWriter->writeAttribute('sourceLinked', 1);
$objWriter->endElement();
$objWriter->startElement('c:majorTickMark');
$objWriter->writeAttribute('val', "out");
$objWriter->endElement();
$objWriter->startElement('c:minorTickMark');
$objWriter->writeAttribute('val', "none");
$objWriter->endElement();
$objWriter->startElement('c:tickLblPos');
$objWriter->writeAttribute('val', "nextTo");
$objWriter->endElement();
if ($id1 > 0) {
$objWriter->startElement('c:crossAx');
$objWriter->writeAttribute('val', $id2);
$objWriter->endElement();
$objWriter->startElement('c:crosses');
$objWriter->writeAttribute('val', "autoZero");
$objWriter->endElement();
$objWriter->startElement('c:crossBetween');
$objWriter->writeAttribute('val', "midCat");
$objWriter->endElement();
}
if ($isMultiLevelSeries) {
if ($groupType !== PHPExcel_Chart_DataSeries::TYPE_BUBBLECHART) {
$objWriter->startElement('c:noMultiLvlLbl');
$objWriter->writeAttribute('val', 0);
$objWriter->endElement();
}
}
$objWriter->endElement();
}
private function _parseRichText($is = null) {
$value = new PHPExcel_RichText();
if (isset($is->t)) {
$value->createText( PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $is->t ) );
} else {
foreach ($is->r as $run) {
if (!isset($run->rPr)) {
$objText = $value->createText( PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $run->t ) );
} else {
$objText = $value->createTextRun( PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $run->t ) );
if (isset($run->rPr->rFont["val"])) {
$objText->getFont()->setName((string) $run->rPr->rFont["val"]);
}
if (isset($run->rPr->sz["val"])) {
$objText->getFont()->setSize((string) $run->rPr->sz["val"]);
}
if (isset($run->rPr->color)) {
$objText->getFont()->setColor( new PHPExcel_Style_Color( $this->_readColor($run->rPr->color) ) );
}
if ( (isset($run->rPr->b["val"]) && ((string) $run->rPr->b["val"] == 'true' || (string) $run->rPr->b["val"] == '1'))
|| (isset($run->rPr->b) && !isset($run->rPr->b["val"])) ) {
$objText->getFont()->setBold(true);
}
if ( (isset($run->rPr->i["val"]) && ((string) $run->rPr->i["val"] == 'true' || (string) $run->rPr->i["val"] == '1'))
|| (isset($run->rPr->i) && !isset($run->rPr->i["val"])) ) {
$objText->getFont()->setItalic(true);
}
if (isset($run->rPr->vertAlign) && isset($run->rPr->vertAlign["val"])) {
$vertAlign = strtolower((string)$run->rPr->vertAlign["val"]);
if ($vertAlign == 'superscript') {
$objText->getFont()->setSuperScript(true);
}
if ($vertAlign == 'subscript') {
$objText->getFont()->setSubScript(true);
}
}
if (isset($run->rPr->u) && !isset($run->rPr->u["val"])) {
$objText->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
} else if (isset($run->rPr->u) && isset($run->rPr->u["val"])) {
$objText->getFont()->setUnderline((string)$run->rPr->u["val"]);
}
if ( (isset($run->rPr->strike["val"]) && ((string) $run->rPr->strike["val"] == 'true' || (string) $run->rPr->strike["val"] == '1'))
|| (isset($run->rPr->strike) && !isset($run->rPr->strike["val"])) ) {
$objText->getFont()->setStrikethrough(true);
}
}
}
}
return $value;
}
/**
* Compare two strings in the same way as strcmp() except that lowercase come before uppercase letters
* @param string $str1 First string value for the comparison
* @param string $str2 Second string value for the comparison
* @return integer
*/
private function strcmpLowercaseFirst($str1, $str2)
{
$inversedStr1 = PHPExcel_Shared_String::StrCaseReverse($str1);
$inversedStr2 = PHPExcel_Shared_String::StrCaseReverse($str2);
return strcmp($inversedStr1, $inversedStr2);
}
