private function parseRichText($is = null)
{
$value = new \PHPExcel\RichText();
if (isset($is->t)) {
$value->createText(\PHPExcel\Shared\StringHelper::controlCharacterOOXML2PHP((string) $is->t));
} else {
if (is_object($is->r)) {
foreach ($is->r as $run) {
if (!isset($run->rPr)) {
$objText = $value->createText(\PHPExcel\Shared\StringHelper::controlCharacterOOXML2PHP((string) $run->t));
} else {
$objText = $value->createTextRun(\PHPExcel\Shared\StringHelper::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(self::readColor($run->rPr->color)));
}
if (isset($run->rPr->b["val"]) && self::boolean((string) $run->rPr->b["val"]) || isset($run->rPr->b) && !isset($run->rPr->b["val"])) {
$objText->getFont()->setBold(true);
}
if (isset($run->rPr->i["val"]) && self::boolean((string) $run->rPr->i["val"]) || 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);
} elseif (isset($run->rPr->u) && isset($run->rPr->u["val"])) {
$objText->getFont()->setUnderline((string) $run->rPr->u["val"]);
}
if (isset($run->rPr->strike["val"]) && self::boolean((string) $run->rPr->strike["val"]) || isset($run->rPr->strike) && !isset($run->rPr->strike["val"])) {
$objText->getFont()->setStrikethrough(true);
}
}
}
}
}
return $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) {
// TODO: Sanitize Rich-Text string (max. character count is 32,767)
return $pValue;
}
// string must never be longer than 32,767 characters, truncate if necessary
$pValue = \PHPExcel\Shared\StringHelper::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\StringHelper::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;
}
/**
* 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);
}
/**
* 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\StringHelper::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);
}
/**
* 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)
*
* PHPExcel is a lot more forgiving than MS Excel when passing non numeric values to this function.
* A Month name or abbreviation (English only at this point) such as 'January' or 'Jan' will still be accepted,
* as will a day value with a suffix (e.g. '21st' rather than simply 21); again only English language.
*
* @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 = Functions::flattenSingleValue($year);
$month = Functions::flattenSingleValue($month);
$day = Functions::flattenSingleValue($day);
if ($month !== null && !is_numeric($month)) {
$month = \PHPExcel\Shared\Date::monthStringToNumber($month);
}
if ($day !== null && !is_numeric($day)) {
$day = \PHPExcel\Shared\Date::dayStringToNumber($day);
}
$year = $year !== null ? \PHPExcel\Shared\StringHelper::testStringAsNumeric($year) : 0;
$month = $month !== null ? \PHPExcel\Shared\StringHelper::testStringAsNumeric($month) : 0;
$day = $day !== null ? \PHPExcel\Shared\StringHelper::testStringAsNumeric($day) : 0;
if (!is_numeric($year) || !is_numeric($month) || !is_numeric($day)) {
return Functions::VALUE();
}
$year = (int) $year;
$month = (int) $month;
$day = (int) $day;
$baseYear = \PHPExcel\Shared\Date::getExcelCalendar();
// Validate parameters
if ($year < $baseYear - 1900) {
return Functions::NAN();
}
if ($baseYear - 1900 != 0 && $year < $baseYear && $year >= 1900) {
return 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 Functions::NAN();
}
// Execute function
$excelDateValue = \PHPExcel\Shared\Date::formattedPHPToExcel($year, $month, $day);
switch (Functions::getReturnDateType()) {
case Functions::RETURNDATE_EXCEL:
return (double) $excelDateValue;
case Functions::RETURNDATE_PHP_NUMERIC:
return (int) \PHPExcel\Shared\Date::excelToPHP($excelDateValue);
case Functions::RETURNDATE_PHP_OBJECT:
return \PHPExcel\Shared\Date::excelToPHPObject($excelDateValue);
}
}
/**
* 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;
} elseif ($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;
} elseif ($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\StringHelper::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;
} elseif ($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);
}
/**
* Convert a value in a pre-defined format to a PHP string
*
* @param mixed $value Value to format
* @param string $format Format code
* @param array $callBack Callback function for additional formatting of string
* @return string Formatted string
*/
public static function toFormattedString($value = '0', $format = NumberFormat::FORMAT_GENERAL, $callBack = null)
{
// For now we do not treat strings although section 4 of a format code affects strings
if (!is_numeric($value)) {
return $value;
}
// For 'General' format code, we just pass the value although this is not entirely the way Excel does it,
// it seems to round numbers to a total of 10 digits.
if ($format === NumberFormat::FORMAT_GENERAL || $format === NumberFormat::FORMAT_TEXT) {
return $value;
}
// Convert any other escaped characters to quoted strings, e.g. (\T to "T")
$format = preg_replace('/(\\\\(.))(?=(?:[^"]|"[^"]*")*$)/u', '"${2}"', $format);
// Get the sections, there can be up to four sections, separated with a semi-colon (but only if not a quoted literal)
$sections = preg_split('/(;)(?=(?:[^"]|"[^"]*")*$)/u', $format);
// Extract the relevant section depending on whether number is positive, negative, or zero?
// Text not supported yet.
// Here is how the sections apply to various values in Excel:
// 1 section: [POSITIVE/NEGATIVE/ZERO/TEXT]
// 2 sections: [POSITIVE/ZERO/TEXT] [NEGATIVE]
// 3 sections: [POSITIVE/TEXT] [NEGATIVE] [ZERO]
// 4 sections: [POSITIVE] [NEGATIVE] [ZERO] [TEXT]
switch (count($sections)) {
case 1:
$format = $sections[0];
break;
case 2:
$format = $value >= 0 ? $sections[0] : $sections[1];
$value = abs($value);
// Use the absolute value
break;
case 3:
$format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]);
$value = abs($value);
// Use the absolute value
break;
case 4:
$format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]);
$value = abs($value);
// Use the absolute value
break;
default:
// something is wrong, just use first section
$format = $sections[0];
break;
}
// In Excel formats, "_" is used to add spacing,
// The following character indicates the size of the spacing, which we can't do in HTML, so we just use a standard space
$format = preg_replace('/_./', ' ', $format);
// Save format with color information for later use below
$formatColor = $format;
// Strip color information
$color_regex = '/^\\[[a-zA-Z]+\\]/';
$format = preg_replace($color_regex, '', $format);
// Let's begin inspecting the format and converting the value to a formatted string
// Check for date/time characters (not inside quotes)
if (preg_match('/(\\[\\$[A-Z]*-[0-9A-F]*\\])*[hmsdy](?=(?:[^"]|"[^"]*")*$)/miu', $format, $matches)) {
// datetime format
self::formatAsDate($value, $format);
} elseif (preg_match('/%$/', $format)) {
// % number format
self::formatAsPercentage($value, $format);
} else {
if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) {
$value = 'EUR ' . sprintf('%1.2f', $value);
} else {
// Some non-number strings are quoted, so we'll get rid of the quotes, likewise any positional * symbols
$format = str_replace(array('"', '*'), '', $format);
// Find out if we need thousands separator
// This is indicated by a comma enclosed by a digit placeholder:
// #,# or 0,0
$useThousands = preg_match('/(#,#|0,0)/', $format);
if ($useThousands) {
$format = preg_replace('/0,0/', '00', $format);
$format = preg_replace('/#,#/', '##', $format);
}
// Scale thousands, millions,...
// This is indicated by a number of commas after a digit placeholder:
// #, or 0.0,,
$scale = 1;
// same as no scale
$matches = array();
if (preg_match('/(#|0)(,+)/', $format, $matches)) {
$scale = pow(1000, strlen($matches[2]));
// strip the commas
$format = preg_replace('/0,+/', '0', $format);
$format = preg_replace('/#,+/', '#', $format);
}
if (preg_match('/#?.*\\?\\/\\?/', $format, $m)) {
//echo 'Format mask is fractional '.$format.' <br />';
if ($value != (int) $value) {
self::formatAsFraction($value, $format);
}
} else {
// Handle the number itself
// scale number
$value = $value / $scale;
// Strip #
$format = preg_replace('/\\#/', '0', $format);
$n = "/\\[[^\\]]+\\]/";
$m = preg_replace($n, '', $format);
$number_regex = "/(0+)(\\.?)(0*)/";
if (preg_match($number_regex, $m, $matches)) {
$left = $matches[1];
$dec = $matches[2];
$right = $matches[3];
// minimun width of formatted number (including dot)
$minWidth = strlen($left) + strlen($dec) + strlen($right);
if ($useThousands) {
$value = number_format($value, strlen($right), \PHPExcel\Shared\StringHelper::getDecimalSeparator(), \PHPExcel\Shared\StringHelper::getThousandsSeparator());
$value = preg_replace($number_regex, $value, $format);
} else {
if (preg_match('/[0#]E[+-]0/i', $format)) {
// Scientific format
$value = sprintf('%5.2E', $value);
} elseif (preg_match('/0([^\\d\\.]+)0/', $format)) {
$value = self::complexNumberFormatMask($value, $format);
} else {
$sprintf_pattern = "%0{$minWidth}." . strlen($right) . "f";
$value = sprintf($sprintf_pattern, $value);
$value = preg_replace($number_regex, $value, $format);
}
}
}
}
if (preg_match('/\\[\\$(.*)\\]/u', $format, $m)) {
// Currency or Accounting
$currencyFormat = $m[0];
$currencyCode = $m[1];
list($currencyCode) = explode('-', $currencyCode);
if ($currencyCode == '') {
$currencyCode = \PHPExcel\Shared\StringHelper::getCurrencyCode();
}
$value = preg_replace('/\\[\\$([^\\]]*)\\]/u', $currencyCode, $value);
}
}
}
// Escape any escaped slashes to a single slash
$format = preg_replace("/\\\\/u", '\\', $format);
// Additional formatting provided by callback function
if ($callBack !== null) {
list($writerInstance, $function) = $callBack;
$value = $writerInstance->{$function}($value, $formatColor);
}
return $value;
}
/**
* Write Rich Text
*
* @param \PHPExcel\Shared\XMLWriter $objWriter XML Writer
* @param string|\PHPExcel\RichText $pRichText text string or Rich text
* @param string $prefix Optional Namespace prefix
* @throws \PHPExcel\Writer\Exception
*/
public function writeRichTextForCharts(\PHPExcel\Shared\XMLWriter $objWriter = null, $pRichText = null, $prefix = null)
{
if (!$pRichText instanceof \PHPExcel\RichText) {
$textRun = $pRichText;
$pRichText = new \PHPExcel\RichText();
$pRichText->createTextRun($textRun);
}
if ($prefix !== null) {
$prefix .= ':';
}
// Loop through rich text elements
$elements = $pRichText->getRichTextElements();
foreach ($elements as $element) {
// r
$objWriter->startElement($prefix . 'r');
// rPr
$objWriter->startElement($prefix . 'rPr');
// Bold
$objWriter->writeAttribute('b', $element->getFont()->getBold() ? 1 : 0);
// Italic
$objWriter->writeAttribute('i', $element->getFont()->getItalic() ? 1 : 0);
// Underline
$underlineType = $element->getFont()->getUnderline();
switch ($underlineType) {
case 'single':
$underlineType = 'sng';
break;
case 'double':
$underlineType = 'dbl';
break;
}
$objWriter->writeAttribute('u', $underlineType);
// Strikethrough
$objWriter->writeAttribute('strike', $element->getFont()->getStrikethrough() ? 'sngStrike' : 'noStrike');
// rFont
$objWriter->startElement($prefix . 'latin');
$objWriter->writeAttribute('typeface', $element->getFont()->getName());
$objWriter->endElement();
// Superscript / subscript
// if ($element->getFont()->getSuperScript() || $element->getFont()->getSubScript()) {
// $objWriter->startElement($prefix.'vertAlign');
// if ($element->getFont()->getSuperScript()) {
// $objWriter->writeAttribute('val', 'superscript');
// } elseif ($element->getFont()->getSubScript()) {
// $objWriter->writeAttribute('val', 'subscript');
// }
// $objWriter->endElement();
// }
//
$objWriter->endElement();
// t
$objWriter->startElement($prefix . 't');
// $objWriter->writeAttribute('xml:space', 'preserve'); // Excel2010 accepts, Excel2007 complains
$objWriter->writeRawData(\PHPExcel\Shared\StringHelper::controlCharacterPHP2OOXML($element->getText()));
$objWriter->endElement();
$objWriter->endElement();
}
}
/**
* 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;
} elseif ($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;
}
$data = pack("vvvvvCCCC", $this->font->getSize() * 20, $grbit, $icv, self::mapBold($this->font->getBold()), $sss, self::mapUnderline($this->font->getUnderline()), $bFamily, $bCharSet, $reserved);
$data .= \PHPExcel\Shared\StringHelper::UTF8toBIFF8UnicodeShort($this->font->getName());
$length = strlen($data);
$header = pack("vv", $record, $length);
return $header . $data;
}
/**
* 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 writeValueAxis($objWriter, PlotArea $plotArea, $yAxisLabel, $groupType, $id1, $id2, $isMultiLevelSeries, $xAxis, $yAxis, $majorGridlines, $minorGridlines)
{
$objWriter->startElement('c:valAx');
if ($id2 > 0) {
$objWriter->startElement('c:axId');
$objWriter->writeAttribute('val', $id2);
$objWriter->endElement();
}
$objWriter->startElement('c:scaling');
if (!is_null($xAxis->getAxisOptionsProperty('maximum'))) {
$objWriter->startElement('c:max');
$objWriter->writeAttribute('val', $xAxis->getAxisOptionsProperty('maximum'));
$objWriter->endElement();
}
if (!is_null($xAxis->getAxisOptionsProperty('minimum'))) {
$objWriter->startElement('c:min');
$objWriter->writeAttribute('val', $xAxis->getAxisOptionsProperty('minimum'));
$objWriter->endElement();
}
$objWriter->startElement('c:orientation');
$objWriter->writeAttribute('val', $xAxis->getAxisOptionsProperty('orientation'));
$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->startElement('c:spPr');
if (!is_null($majorGridlines->getLineColorProperty('value'))) {
$objWriter->startElement('a:ln');
$objWriter->writeAttribute('w', $majorGridlines->getLineStyleProperty('width'));
$objWriter->startElement('a:solidFill');
$objWriter->startElement("a:{$majorGridlines->getLineColorProperty('type')}");
$objWriter->writeAttribute('val', $majorGridlines->getLineColorProperty('value'));
$objWriter->startElement('a:alpha');
$objWriter->writeAttribute('val', $majorGridlines->getLineColorProperty('alpha'));
$objWriter->endElement();
//end alpha
$objWriter->endElement();
//end srgbClr
$objWriter->endElement();
//end solidFill
$objWriter->startElement('a:prstDash');
$objWriter->writeAttribute('val', $majorGridlines->getLineStyleProperty('dash'));
$objWriter->endElement();
if ($majorGridlines->getLineStyleProperty('join') == 'miter') {
$objWriter->startElement('a:miter');
$objWriter->writeAttribute('lim', '800000');
$objWriter->endElement();
} else {
$objWriter->startElement('a:bevel');
$objWriter->endElement();
}
if (!is_null($majorGridlines->getLineStyleProperty(array('arrow', 'head', 'type')))) {
$objWriter->startElement('a:headEnd');
$objWriter->writeAttribute('type', $majorGridlines->getLineStyleProperty(array('arrow', 'head', 'type')));
$objWriter->writeAttribute('w', $majorGridlines->getLineStyleArrowParameters('head', 'w'));
$objWriter->writeAttribute('len', $majorGridlines->getLineStyleArrowParameters('head', 'len'));
$objWriter->endElement();
}
if (!is_null($majorGridlines->getLineStyleProperty(array('arrow', 'end', 'type')))) {
$objWriter->startElement('a:tailEnd');
$objWriter->writeAttribute('type', $majorGridlines->getLineStyleProperty(array('arrow', 'end', 'type')));
$objWriter->writeAttribute('w', $majorGridlines->getLineStyleArrowParameters('end', 'w'));
$objWriter->writeAttribute('len', $majorGridlines->getLineStyleArrowParameters('end', 'len'));
$objWriter->endElement();
}
$objWriter->endElement();
//end ln
}
$objWriter->startElement('a:effectLst');
if (!is_null($majorGridlines->getGlowSize())) {
$objWriter->startElement('a:glow');
$objWriter->writeAttribute('rad', $majorGridlines->getGlowSize());
$objWriter->startElement("a:{$majorGridlines->getGlowColor('type')}");
$objWriter->writeAttribute('val', $majorGridlines->getGlowColor('value'));
$objWriter->startElement('a:alpha');
$objWriter->writeAttribute('val', $majorGridlines->getGlowColor('alpha'));
$objWriter->endElement();
//end alpha
$objWriter->endElement();
//end schemeClr
$objWriter->endElement();
//end glow
}
if (!is_null($majorGridlines->getShadowProperty('presets'))) {
$objWriter->startElement("a:{$majorGridlines->getShadowProperty('effect')}");
if (!is_null($majorGridlines->getShadowProperty('blur'))) {
$objWriter->writeAttribute('blurRad', $majorGridlines->getShadowProperty('blur'));
}
if (!is_null($majorGridlines->getShadowProperty('distance'))) {
$objWriter->writeAttribute('dist', $majorGridlines->getShadowProperty('distance'));
}
if (!is_null($majorGridlines->getShadowProperty('direction'))) {
$objWriter->writeAttribute('dir', $majorGridlines->getShadowProperty('direction'));
}
if (!is_null($majorGridlines->getShadowProperty('algn'))) {
$objWriter->writeAttribute('algn', $majorGridlines->getShadowProperty('algn'));
}
if (!is_null($majorGridlines->getShadowProperty(array('size', 'sx')))) {
$objWriter->writeAttribute('sx', $majorGridlines->getShadowProperty(array('size', 'sx')));
}
if (!is_null($majorGridlines->getShadowProperty(array('size', 'sy')))) {
$objWriter->writeAttribute('sy', $majorGridlines->getShadowProperty(array('size', 'sy')));
}
if (!is_null($majorGridlines->getShadowProperty(array('size', 'kx')))) {
$objWriter->writeAttribute('kx', $majorGridlines->getShadowProperty(array('size', 'kx')));
}
if (!is_null($majorGridlines->getShadowProperty('rotWithShape'))) {
$objWriter->writeAttribute('rotWithShape', $majorGridlines->getShadowProperty('rotWithShape'));
}
$objWriter->startElement("a:{$majorGridlines->getShadowProperty(array('color', 'type'))}");
$objWriter->writeAttribute('val', $majorGridlines->getShadowProperty(array('color', 'value')));
$objWriter->startElement('a:alpha');
$objWriter->writeAttribute('val', $majorGridlines->getShadowProperty(array('color', 'alpha')));
$objWriter->endElement();
//end alpha
$objWriter->endElement();
//end color:type
$objWriter->endElement();
//end shadow
}
if (!is_null($majorGridlines->getSoftEdgesSize())) {
$objWriter->startElement('a:softEdge');
$objWriter->writeAttribute('rad', $majorGridlines->getSoftEdgesSize());
$objWriter->endElement();
//end softEdge
}
$objWriter->endElement();
//end effectLst
$objWriter->endElement();
//end spPr
$objWriter->endElement();
//end majorGridLines
if ($minorGridlines->getObjectState()) {
$objWriter->startElement('c:minorGridlines');
$objWriter->startElement('c:spPr');
if (!is_null($minorGridlines->getLineColorProperty('value'))) {
$objWriter->startElement('a:ln');
$objWriter->writeAttribute('w', $minorGridlines->getLineStyleProperty('width'));
$objWriter->startElement('a:solidFill');
$objWriter->startElement("a:{$minorGridlines->getLineColorProperty('type')}");
$objWriter->writeAttribute('val', $minorGridlines->getLineColorProperty('value'));
$objWriter->startElement('a:alpha');
$objWriter->writeAttribute('val', $minorGridlines->getLineColorProperty('alpha'));
$objWriter->endElement();
//end alpha
$objWriter->endElement();
//end srgbClr
$objWriter->endElement();
//end solidFill
$objWriter->startElement('a:prstDash');
$objWriter->writeAttribute('val', $minorGridlines->getLineStyleProperty('dash'));
$objWriter->endElement();
if ($minorGridlines->getLineStyleProperty('join') == 'miter') {
$objWriter->startElement('a:miter');
$objWriter->writeAttribute('lim', '800000');
$objWriter->endElement();
} else {
$objWriter->startElement('a:bevel');
$objWriter->endElement();
}
if (!is_null($minorGridlines->getLineStyleProperty(array('arrow', 'head', 'type')))) {
$objWriter->startElement('a:headEnd');
$objWriter->writeAttribute('type', $minorGridlines->getLineStyleProperty(array('arrow', 'head', 'type')));
$objWriter->writeAttribute('w', $minorGridlines->getLineStyleArrowParameters('head', 'w'));
$objWriter->writeAttribute('len', $minorGridlines->getLineStyleArrowParameters('head', 'len'));
$objWriter->endElement();
}
if (!is_null($minorGridlines->getLineStyleProperty(array('arrow', 'end', 'type')))) {
$objWriter->startElement('a:tailEnd');
$objWriter->writeAttribute('type', $minorGridlines->getLineStyleProperty(array('arrow', 'end', 'type')));
$objWriter->writeAttribute('w', $minorGridlines->getLineStyleArrowParameters('end', 'w'));
$objWriter->writeAttribute('len', $minorGridlines->getLineStyleArrowParameters('end', 'len'));
$objWriter->endElement();
}
$objWriter->endElement();
//end ln
}
$objWriter->startElement('a:effectLst');
if (!is_null($minorGridlines->getGlowSize())) {
$objWriter->startElement('a:glow');
$objWriter->writeAttribute('rad', $minorGridlines->getGlowSize());
$objWriter->startElement("a:{$minorGridlines->getGlowColor('type')}");
$objWriter->writeAttribute('val', $minorGridlines->getGlowColor('value'));
$objWriter->startElement('a:alpha');
$objWriter->writeAttribute('val', $minorGridlines->getGlowColor('alpha'));
$objWriter->endElement();
//end alpha
$objWriter->endElement();
//end schemeClr
$objWriter->endElement();
//end glow
}
if (!is_null($minorGridlines->getShadowProperty('presets'))) {
$objWriter->startElement("a:{$minorGridlines->getShadowProperty('effect')}");
if (!is_null($minorGridlines->getShadowProperty('blur'))) {
$objWriter->writeAttribute('blurRad', $minorGridlines->getShadowProperty('blur'));
}
if (!is_null($minorGridlines->getShadowProperty('distance'))) {
$objWriter->writeAttribute('dist', $minorGridlines->getShadowProperty('distance'));
}
if (!is_null($minorGridlines->getShadowProperty('direction'))) {
$objWriter->writeAttribute('dir', $minorGridlines->getShadowProperty('direction'));
}
if (!is_null($minorGridlines->getShadowProperty('algn'))) {
$objWriter->writeAttribute('algn', $minorGridlines->getShadowProperty('algn'));
}
if (!is_null($minorGridlines->getShadowProperty(array('size', 'sx')))) {
$objWriter->writeAttribute('sx', $minorGridlines->getShadowProperty(array('size', 'sx')));
}
if (!is_null($minorGridlines->getShadowProperty(array('size', 'sy')))) {
$objWriter->writeAttribute('sy', $minorGridlines->getShadowProperty(array('size', 'sy')));
}
if (!is_null($minorGridlines->getShadowProperty(array('size', 'kx')))) {
$objWriter->writeAttribute('kx', $minorGridlines->getShadowProperty(array('size', 'kx')));
}
if (!is_null($minorGridlines->getShadowProperty('rotWithShape'))) {
$objWriter->writeAttribute('rotWithShape', $minorGridlines->getShadowProperty('rotWithShape'));
}
$objWriter->startElement("a:{$minorGridlines->getShadowProperty(array('color', 'type'))}");
$objWriter->writeAttribute('val', $minorGridlines->getShadowProperty(array('color', 'value')));
$objWriter->startElement('a:alpha');
$objWriter->writeAttribute('val', $minorGridlines->getShadowProperty(array('color', 'alpha')));
$objWriter->endElement();
//end alpha
$objWriter->endElement();
//end color:type
$objWriter->endElement();
//end shadow
}
if (!is_null($minorGridlines->getSoftEdgesSize())) {
$objWriter->startElement('a:softEdge');
$objWriter->writeAttribute('rad', $minorGridlines->getSoftEdgesSize());
$objWriter->endElement();
//end softEdge
}
$objWriter->endElement();
//end effectLst
$objWriter->endElement();
//end spPr
$objWriter->endElement();
//end minorGridLines
}
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\StringHelper::controlCharacterPHP2OOXML($caption));
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
if ($groupType !== 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', $xAxis->getAxisNumberFormat());
$objWriter->writeAttribute('sourceLinked', $xAxis->getAxisNumberSourceLinked());
$objWriter->endElement();
$objWriter->startElement('c:majorTickMark');
$objWriter->writeAttribute('val', $xAxis->getAxisOptionsProperty('major_tick_mark'));
$objWriter->endElement();
$objWriter->startElement('c:minorTickMark');
$objWriter->writeAttribute('val', $xAxis->getAxisOptionsProperty('minor_tick_mark'));
$objWriter->endElement();
$objWriter->startElement('c:tickLblPos');
$objWriter->writeAttribute('val', $xAxis->getAxisOptionsProperty('axis_labels'));
$objWriter->endElement();
$objWriter->startElement('c:spPr');
if (!is_null($xAxis->getFillProperty('value'))) {
$objWriter->startElement('a:solidFill');
$objWriter->startElement("a:" . $xAxis->getFillProperty('type'));
$objWriter->writeAttribute('val', $xAxis->getFillProperty('value'));
$objWriter->startElement('a:alpha');
$objWriter->writeAttribute('val', $xAxis->getFillProperty('alpha'));
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
}
$objWriter->startElement('a:ln');
$objWriter->writeAttribute('w', $xAxis->getLineStyleProperty('width'));
$objWriter->writeAttribute('cap', $xAxis->getLineStyleProperty('cap'));
$objWriter->writeAttribute('cmpd', $xAxis->getLineStyleProperty('compound'));
if (!is_null($xAxis->getLineProperty('value'))) {
$objWriter->startElement('a:solidFill');
$objWriter->startElement("a:" . $xAxis->getLineProperty('type'));
$objWriter->writeAttribute('val', $xAxis->getLineProperty('value'));
$objWriter->startElement('a:alpha');
$objWriter->writeAttribute('val', $xAxis->getLineProperty('alpha'));
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
}
$objWriter->startElement('a:prstDash');
$objWriter->writeAttribute('val', $xAxis->getLineStyleProperty('dash'));
$objWriter->endElement();
if ($xAxis->getLineStyleProperty('join') == 'miter') {
$objWriter->startElement('a:miter');
$objWriter->writeAttribute('lim', '800000');
$objWriter->endElement();
} else {
$objWriter->startElement('a:bevel');
$objWriter->endElement();
}
if (!is_null($xAxis->getLineStyleProperty(array('arrow', 'head', 'type')))) {
$objWriter->startElement('a:headEnd');
$objWriter->writeAttribute('type', $xAxis->getLineStyleProperty(array('arrow', 'head', 'type')));
$objWriter->writeAttribute('w', $xAxis->getLineStyleArrowWidth('head'));
$objWriter->writeAttribute('len', $xAxis->getLineStyleArrowLength('head'));
$objWriter->endElement();
}
if (!is_null($xAxis->getLineStyleProperty(array('arrow', 'end', 'type')))) {
$objWriter->startElement('a:tailEnd');
$objWriter->writeAttribute('type', $xAxis->getLineStyleProperty(array('arrow', 'end', 'type')));
$objWriter->writeAttribute('w', $xAxis->getLineStyleArrowWidth('end'));
$objWriter->writeAttribute('len', $xAxis->getLineStyleArrowLength('end'));
$objWriter->endElement();
}
$objWriter->endElement();
$objWriter->startElement('a:effectLst');
if (!is_null($xAxis->getGlowProperty('size'))) {
$objWriter->startElement('a:glow');
$objWriter->writeAttribute('rad', $xAxis->getGlowProperty('size'));
$objWriter->startElement("a:{$xAxis->getGlowProperty(array('color', 'type'))}");
$objWriter->writeAttribute('val', $xAxis->getGlowProperty(array('color', 'value')));
$objWriter->startElement('a:alpha');
$objWriter->writeAttribute('val', $xAxis->getGlowProperty(array('color', 'alpha')));
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
}
if (!is_null($xAxis->getShadowProperty('presets'))) {
$objWriter->startElement("a:{$xAxis->getShadowProperty('effect')}");
if (!is_null($xAxis->getShadowProperty('blur'))) {
$objWriter->writeAttribute('blurRad', $xAxis->getShadowProperty('blur'));
}
if (!is_null($xAxis->getShadowProperty('distance'))) {
$objWriter->writeAttribute('dist', $xAxis->getShadowProperty('distance'));
}
if (!is_null($xAxis->getShadowProperty('direction'))) {
$objWriter->writeAttribute('dir', $xAxis->getShadowProperty('direction'));
}
if (!is_null($xAxis->getShadowProperty('algn'))) {
$objWriter->writeAttribute('algn', $xAxis->getShadowProperty('algn'));
}
if (!is_null($xAxis->getShadowProperty(array('size', 'sx')))) {
$objWriter->writeAttribute('sx', $xAxis->getShadowProperty(array('size', 'sx')));
}
if (!is_null($xAxis->getShadowProperty(array('size', 'sy')))) {
$objWriter->writeAttribute('sy', $xAxis->getShadowProperty(array('size', 'sy')));
}
if (!is_null($xAxis->getShadowProperty(array('size', 'kx')))) {
$objWriter->writeAttribute('kx', $xAxis->getShadowProperty(array('size', 'kx')));
}
if (!is_null($xAxis->getShadowProperty('rotWithShape'))) {
$objWriter->writeAttribute('rotWithShape', $xAxis->getShadowProperty('rotWithShape'));
}
$objWriter->startElement("a:{$xAxis->getShadowProperty(array('color', 'type'))}");
$objWriter->writeAttribute('val', $xAxis->getShadowProperty(array('color', 'value')));
$objWriter->startElement('a:alpha');
$objWriter->writeAttribute('val', $xAxis->getShadowProperty(array('color', 'alpha')));
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
}
if (!is_null($xAxis->getSoftEdgesSize())) {
$objWriter->startElement('a:softEdge');
$objWriter->writeAttribute('rad', $xAxis->getSoftEdgesSize());
$objWriter->endElement();
}
$objWriter->endElement();
//effectList
$objWriter->endElement();
//end spPr
if ($id1 > 0) {
$objWriter->startElement('c:crossAx');
$objWriter->writeAttribute('val', $id2);
$objWriter->endElement();
if (!is_null($xAxis->getAxisOptionsProperty('horizontal_crosses_value'))) {
$objWriter->startElement('c:crossesAt');
$objWriter->writeAttribute('val', $xAxis->getAxisOptionsProperty('horizontal_crosses_value'));
$objWriter->endElement();
} else {
$objWriter->startElement('c:crosses');
$objWriter->writeAttribute('val', $xAxis->getAxisOptionsProperty('horizontal_crosses'));
$objWriter->endElement();
}
$objWriter->startElement('c:crossBetween');
$objWriter->writeAttribute('val', "midCat");
$objWriter->endElement();
if (!is_null($xAxis->getAxisOptionsProperty('major_unit'))) {
$objWriter->startElement('c:majorUnit');
$objWriter->writeAttribute('val', $xAxis->getAxisOptionsProperty('major_unit'));
$objWriter->endElement();
}
if (!is_null($xAxis->getAxisOptionsProperty('minor_unit'))) {
$objWriter->startElement('c:minorUnit');
$objWriter->writeAttribute('val', $xAxis->getAxisOptionsProperty('minor_unit'));
$objWriter->endElement();
}
}
if ($isMultiLevelSeries) {
if ($groupType !== DataSeries::TYPE_BUBBLECHART) {
$objWriter->startElement('c:noMultiLvlLbl');
$objWriter->writeAttribute('val', 0);
$objWriter->endElement();
}
}
$objWriter->endElement();
}
/**
* 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;
}
/**
* 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 Matrix) {
$M = $args[0];
} else {
throw new \PHPExcel\Calculation\Exception(self::ARGUMENT_TYPE_EXCEPTION);
}
break;
case 'array':
$M = new 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\StringHelper::convertToNumberIfFraction($this->A[$i][$j]);
}
if (is_string($value) && strlen($value) > 0 && !is_numeric($value)) {
$value = trim($value, '"');
$validValues &= \PHPExcel\Shared\StringHelper::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);
}
}
private function setMargins(\PHPExcel\Worksheet $pSheet)
{
$htmlPage = '@page { ';
$htmlBody = 'body { ';
$left = StringHelper::formatNumber($pSheet->getPageMargins()->getLeft()) . 'in; ';
$htmlPage .= 'margin-left: ' . $left;
$htmlBody .= 'margin-left: ' . $left;
$right = StringHelper::formatNumber($pSheet->getPageMargins()->getRight()) . 'in; ';
$htmlPage .= 'margin-right: ' . $right;
$htmlBody .= 'margin-right: ' . $right;
$top = StringHelper::formatNumber($pSheet->getPageMargins()->getTop()) . 'in; ';
$htmlPage .= 'margin-top: ' . $top;
$htmlBody .= 'margin-top: ' . $top;
$bottom = StringHelper::formatNumber($pSheet->getPageMargins()->getBottom()) . 'in; ';
$htmlPage .= 'margin-bottom: ' . $bottom;
$htmlBody .= 'margin-bottom: ' . $bottom;
$htmlPage .= "}\n";
$htmlBody .= "}\n";
return "<style>\n" . $htmlPage . $htmlBody . "</style>\n";
}
/**
* VALUE
*
* @param mixed $value Value to check
* @return boolean
*/
public static function VALUE($value = '')
{
$value = Functions::flattenSingleValue($value);
if (!is_numeric($value)) {
$numberValue = str_replace(\PHPExcel\Shared\StringHelper::getThousandsSeparator(), '', trim($value, " \t\n\r\v" . \PHPExcel\Shared\StringHelper::getCurrencyCode()));
if (is_numeric($numberValue)) {
return (double) $numberValue;
}
$dateSetting = Functions::getReturnDateType();
Functions::setReturnDateType(Functions::RETURNDATE_EXCEL);
if (strpos($value, ':') !== false) {
$timeValue = DateTime::TIMEVALUE($value);
if ($timeValue !== Functions::VALUE()) {
Functions::setReturnDateType($dateSetting);
return $timeValue;
}
}
$dateValue = DateTime::DATEVALUE($value);
if ($dateValue !== Functions::VALUE()) {
Functions::setReturnDateType($dateSetting);
return $dateValue;
}
Functions::setReturnDateType($dateSetting);
return Functions::VALUE();
}
return (double) $value;
}
protected static function convertStringEncoding($string, $charset)
{
if ($charset != 'UTF-8') {
return \PHPExcel\Shared\StringHelper::convertEncoding($string, 'UTF-8', $charset);
}
return $string;
}
/**
* Write Cell
*
* @param \PHPExcel\Shared\XMLWriter $objWriter XML Writer
* @param \PHPExcel\Worksheet $pSheet Worksheet
* @param \PHPExcel\Cell $pCellAddress Cell Address
* @param string[] $pStringTable String table
* @param string[] $pFlippedStringTable String table (flipped), for faster index searching
* @throws \PHPExcel\Writer\Exception
*/
private function writeCell(\PHPExcel\Shared\XMLWriter $objWriter = null, \PHPExcel\Worksheet $pSheet = null, $pCellAddress = null, $pStringTable = null, $pFlippedStringTable = null)
{
if (is_array($pStringTable) && is_array($pFlippedStringTable)) {
// Cell
$pCell = $pSheet->getCell($pCellAddress);
$objWriter->startElement('c');
$objWriter->writeAttribute('r', $pCellAddress);
// Sheet styles
if ($pCell->getXfIndex() != '') {
$objWriter->writeAttribute('s', $pCell->getXfIndex());
}
// If cell value is supplied, write cell value
$cellValue = $pCell->getValue();
if (is_object($cellValue) || $cellValue !== '') {
// Map type
$mappedType = $pCell->getDataType();
// Write data type depending on its type
switch (strtolower($mappedType)) {
case 'inlinestr':
// Inline string
// Inline string
case 's':
// String
// String
case 'b':
// Boolean
$objWriter->writeAttribute('t', $mappedType);
break;
case 'f':
// Formula
$calculatedValue = $this->getParentWriter()->getPreCalculateFormulas() ? $pCell->getCalculatedValue() : $cellValue;
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 (!$cellValue instanceof \PHPExcel\RichText) {
$objWriter->writeElement('t', \PHPExcel\Shared\StringHelper::controlCharacterPHP2OOXML(htmlspecialchars($cellValue)));
} elseif ($cellValue instanceof \PHPExcel\RichText) {
$objWriter->startElement('is');
$this->getParentWriter()->getWriterPart('stringtable')->writeRichText($objWriter, $cellValue);
$objWriter->endElement();
}
break;
case 's':
// String
if (!$cellValue instanceof \PHPExcel\RichText) {
if (isset($pFlippedStringTable[$cellValue])) {
$objWriter->writeElement('v', $pFlippedStringTable[$cellValue]);
}
} elseif ($cellValue instanceof \PHPExcel\RichText) {
$objWriter->writeElement('v', $pFlippedStringTable[$cellValue->getHashCode()]);
}
break;
case 'f':
// Formula
$attributes = $pCell->getFormulaAttributes();
if ($attributes['t'] == 'array') {
$objWriter->startElement('f');
$objWriter->writeAttribute('t', 'array');
$objWriter->writeAttribute('ref', $pCellAddress);
$objWriter->writeAttribute('aca', '1');
$objWriter->writeAttribute('ca', '1');
$objWriter->text(substr($cellValue, 1));
$objWriter->endElement();
} else {
$objWriter->writeElement('f', substr($cellValue, 1));
}
if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
if ($this->getParentWriter()->getPreCalculateFormulas()) {
// $calculatedValue = $pCell->getCalculatedValue();
if (!is_array($calculatedValue) && substr($calculatedValue, 0, 1) != '#') {
$objWriter->writeElement('v', \PHPExcel\Shared\StringHelper::formatNumber($calculatedValue));
} 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
$objWriter->writeElement('v', str_replace(',', '.', $cellValue));
break;
case 'b':
// Boolean
$objWriter->writeElement('v', $cellValue ? '1' : '0');
break;
case 'e':
// Error
if (substr($cellValue, 0, 1) == '=') {
$objWriter->writeElement('f', substr($cellValue, 1));
$objWriter->writeElement('v', substr($cellValue, 1));
} else {
$objWriter->writeElement('v', $cellValue);
}
break;
}
}
$objWriter->endElement();
} else {
throw new \PHPExcel\Writer\Exception("Invalid parameters passed.");
}
}
