/**
* Bind value to a cell
*
* @param Cell $cell Cell to bind value to
* @param mixed $value Value to bind in cell
* @return boolean
*/
public function bindValue(Cell $cell, $value = null)
{
// sanitize UTF-8 strings
if (is_string($value)) {
$value = Shared_String::SanitizeUTF8($value);
}
// Set value explicit
$cell->setValueExplicit($value, Cell_DataType::dataTypeForValue($value));
// Done!
return true;
}
/**
* 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 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 = 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;
}
/**
* 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 = 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 .= Shared_String::UTF8toBIFF8UnicodeShort($this->_font->getName());
}
$length = strlen($data);
$header = pack("vv", $record, $length);
return $header . $data;
}
private function _parseRichText($is = null)
{
$value = new RichText();
if (isset($is->t)) {
$value->createText(Shared_String::ControlCharacterOOXML2PHP((string) $is->t));
} else {
foreach ($is->r as $run) {
if (!isset($run->rPr)) {
$objText = $value->createText(Shared_String::ControlCharacterOOXML2PHP((string) $run->t));
} else {
$objText = $value->createTextRun(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 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(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;
}
private function _validateBinaryOperand($cellID, &$operand, &$stack)
{
// 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->_writeDebug('Evaluation Result is ' . self::_showTypeDetails($operand));
return false;
} elseif (!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->_writeDebug('Evaluation Result is a ' . self::_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;
}
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @access public
* @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);
// Open file
$fileHandle = fopen($pFilename, 'r');
if ($fileHandle === false) {
throw new Exception("Could not open file {$pFilename} for reading.");
}
// Skip BOM, if any
switch ($this->_inputEncoding) {
case 'UTF-8':
fgets($fileHandle, 4) == "" ? fseek($fileHandle, 3) : fseek($fileHandle, 0);
break;
default:
break;
}
// Loop through file
$currentRow = 0;
$rowData = array();
while (($rowData = fgetcsv($fileHandle, 0, $this->_delimiter, $this->_enclosure)) !== FALSE) {
++$currentRow;
$rowDataCount = count($rowData);
for ($i = 0; $i < $rowDataCount; ++$i) {
$columnLetter = Cell::stringFromColumnIndex($i);
if ($rowData[$i] != '' && $this->_readFilter->readCell($columnLetter, $currentRow)) {
// Unescape enclosures
$rowData[$i] = str_replace("\\" . $this->_enclosure, $this->_enclosure, $rowData[$i]);
$rowData[$i] = str_replace($this->_enclosure . $this->_enclosure, $this->_enclosure, $rowData[$i]);
// Convert encoding if necessary
if ($this->_inputEncoding !== 'UTF-8') {
$rowData[$i] = Shared_String::ConvertEncoding($rowData[$i], 'UTF-8', $this->_inputEncoding);
}
// Set cell value
$objPHPExcel->getActiveSheet()->getCell($columnLetter . $currentRow)->setValue($rowData[$i]);
}
}
}
// Close file
fclose($fileHandle);
// Return
return $objPHPExcel;
}
/**
* Convert string to UTF-8. Only used for BIFF5.
*
* @param string $string
* @return string
*/
private function _decodeCodepage($string)
{
$result = Shared_String::ConvertEncoding($string, 'UTF-8', $this->_codepage);
return $result;
}
/**
* 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 = '', $format = '', $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 === 'General') {
return $value;
}
// Get the sections, there can be up to four sections
$sections = explode(';', $format);
// Fetch 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;
}
// 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
if (preg_match('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])*[hmsdy]/i', $format)) {
// datetime format
// dvc: convert Excel formats to PHP date formats
// strip off first part containing e.g. [$-F800] or [$USD-409]
// general syntax: [$<Currency string>-<language info>]
// language info is in hexadecimal
$format = preg_replace('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])/i', '', $format);
// OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case
$format = strtolower($format);
$format = strtr($format, self::$_dateFormatReplacements);
if (!strpos($format, 'A')) {
// 24-hour time format
$format = strtr($format, self::$_dateFormatReplacements24);
} else {
// 12-hour time format
$format = strtr($format, self::$_dateFormatReplacements12);
}
$dateObj = Shared_Date::ExcelToPHPObject($value);
$value = $dateObj->format($format);
} else {
if (preg_match('/%$/', $format)) {
// % number format
if ($format === self::FORMAT_PERCENTAGE) {
$value = round(100 * $value, 0) . '%';
} else {
if (preg_match('/\\.[#0]+/i', $format, $m)) {
$s = substr($m[0], 0, 1) . (strlen($m[0]) - 1);
$format = str_replace($m[0], $s, $format);
}
if (preg_match('/^[#0]+/', $format, $m)) {
$format = str_replace($m[0], strlen($m[0]), $format);
}
$format = '%' . str_replace('%', 'f%%', $format);
$value = sprintf($format, 100 * $value);
}
} else {
if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) {
$value = 'EUR ' . sprintf('%1.2f', $value);
} else {
// In Excel formats, "_" is used to add spacing, which we can't do in HTML
$format = preg_replace('/_./', '', $format);
// Some non-number characters are escaped with \, which we don't need
$format = preg_replace("/\\\\/", '', $format);
// Some non-number strings are quoted, so we'll get rid of the quotes
$format = preg_replace('/"/', '', $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) {
$sign = $value < 0 ? '-' : '';
$integerPart = floor(abs($value));
$decimalPart = trim(fmod(abs($value), 1), '0.');
$decimalLength = strlen($decimalPart);
$decimalDivisor = pow(10, $decimalLength);
$GCD = Calculation_Functions::GCD($decimalPart, $decimalDivisor);
$adjustedDecimalPart = $decimalPart / $GCD;
$adjustedDecimalDivisor = $decimalDivisor / $GCD;
if (strpos($format, '0') !== false || substr($format, 0, 3) == '? ?') {
if ($integerPart == 0) {
$integerPart = '';
}
$value = "{$sign}{$integerPart} {$adjustedDecimalPart}/{$adjustedDecimalDivisor}";
} else {
$adjustedDecimalPart += $integerPart * $adjustedDecimalDivisor;
$value = "{$sign}{$adjustedDecimalPart}/{$adjustedDecimalDivisor}";
}
}
} else {
// Handle the number itself
// scale number
$value = $value / $scale;
// Strip #
$format = preg_replace('/\\#/', '', $format);
$number_regex = "/(0+)(\\.?)(0*)/";
$matches = array();
if (preg_match($number_regex, $format, $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), Shared_String::getDecimalSeparator(), Shared_String::getThousandsSeparator());
} else {
$sprintf_pattern = "%0{$minWidth}." . strlen($right) . "f";
$value = sprintf($sprintf_pattern, $value);
}
$value = preg_replace($number_regex, $value, $format);
}
}
}
}
}
// Additional formatting provided by callback function
if ($callBack !== null) {
list($writerInstance, $function) = $callBack;
$value = $writerInstance->{$function}($value, $formatColor);
}
return $value;
}
/**
* Get approximate width in pixels for a string of text in a certain font at a certain rotation angle
*
* @param string $columnText
* @param Style_Font $font
* @param int $rotation
* @return int Text width in pixels (no padding added)
*/
public static function getTextWidthPixelsApprox($columnText, Style_Font $font = null, $rotation = 0)
{
$fontName = $font->getName();
$fontSize = $font->getSize();
// Calculate column width in pixels. We assume fixed glyph width. Result varies with font name and size.
switch ($fontName) {
case 'Calibri':
// value 8.26 was found via interpolation by inspecting real Excel files with Calibri 11 font.
$columnWidth = (int) (8.26 * Shared_String::CountCharacters($columnText));
$columnWidth = $columnWidth * $fontSize / 11;
// extrapolate from font size
break;
case 'Arial':
// value 7 was found via interpolation by inspecting real Excel files with Arial 10 font.
$columnWidth = (int) (7 * Shared_String::CountCharacters($columnText));
$columnWidth = $columnWidth * $fontSize / 10;
// extrapolate from font size
break;
case 'Verdana':
// value 8 was found via interpolation by inspecting real Excel files with Verdana 10 font.
$columnWidth = (int) (8 * Shared_String::CountCharacters($columnText));
$columnWidth = $columnWidth * $fontSize / 10;
// extrapolate from font size
break;
default:
// just assume Calibri
$columnWidth = (int) (8.26 * Shared_String::CountCharacters($columnText));
$columnWidth = $columnWidth * $fontSize / 11;
// extrapolate from font size
break;
}
// Calculate approximate rotated column width
if ($rotation !== 0) {
if ($rotation == -165) {
// stacked text
$columnWidth = 4;
// approximation
} else {
// rotated text
$columnWidth = $columnWidth * cos(deg2rad($rotation)) + $fontSize * abs(sin(deg2rad($rotation))) / 5;
// approximation
}
}
// pixel width is an integer
$columnWidth = (int) $columnWidth;
return $columnWidth;
}
/**
* Check sheet title for valid Excel syntax
*
* @param string $pValue The string to check
* @return string The valid string
* @throws Exception
*/
private static function _checkSheetTitle($pValue)
{
// Some of the printable ASCII characters are invalid: * : / \ ? [ ]
if (str_replace(self::$_invalidCharacters, '', $pValue) !== $pValue) {
throw new Exception('Invalid character found in sheet title');
}
// Maximum 31 characters allowed for sheet title
if (Shared_String::CountCharacters($pValue) > 31) {
throw new Exception('Maximum 31 characters allowed in sheet title.');
}
return $pValue;
}
/**
* Set the thousands separator. Only used by Style_NumberFormat::toFormattedString()
* to format output by Writer_HTML and Writer_PDF
*
* @param string $pValue Character for thousands separator
*/
public static function setThousandsSeparator($pValue = ',')
{
self::$_thousandsSeparator = $pValue;
}
/**
* Bind value to a cell
*
* @param Cell $cell Cell to bind value to
* @param mixed $value Value to bind in cell
* @return boolean
*/
public function bindValue(Cell $cell, $value = null)
{
// sanitize UTF-8 strings
if (is_string($value)) {
$value = Shared_String::SanitizeUTF8($value);
}
// Find out data type
$dataType = parent::dataTypeForValue($value);
// Style logic - strings
if ($dataType === Cell_DataType::TYPE_STRING && !$value instanceof RichText) {
// Check for percentage
if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) {
// Convert value to number
$cell->setValueExplicit((double) str_replace('%', '', $value) / 100, Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(Style_NumberFormat::FORMAT_PERCENTAGE);
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)) {
list($h, $m) = explode(':', $value);
$days = $h / 24 + $m / 1440;
// Convert value to number
$cell->setValueExplicit($days, Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(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)) {
list($h, $m, $s) = explode(':', $value);
$days = $h / 24 + $m / 1440 + $s / 86400;
// Convert value to number
$cell->setValueExplicit($days, Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(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 (($v = Shared_Date::stringToExcel($value)) !== false) {
// Convert value to Excel date
$cell->setValueExplicit($v, Cell_DataType::TYPE_NUMERIC);
// Set 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->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode($formatCode);
return true;
}
// Check for newline character "\n"
if (strpos($value, "\n") !== false) {
$value = Shared_String::SanitizeUTF8($value);
$cell->setValueExplicit($value, Cell_DataType::TYPE_STRING);
// Set style
$cell->getParent()->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 _writeNumFormat($format, $ifmt)
{
$record = 0x41e;
// Record identifier
if ($this->_BIFF_version == 0x600) {
$numberFormatString = Shared_String::UTF8toBIFF8UnicodeLong($format);
$length = 2 + strlen($numberFormatString);
// Number of bytes to follow
} elseif ($this->_BIFF_version == 0x500) {
$length = 3 + strlen($format);
// Number of bytes to follow
}
$header = pack("vv", $record, $length);
if ($this->_BIFF_version == 0x600) {
$data = pack("v", $ifmt) . $numberFormatString;
$this->_append($header . $data);
} elseif ($this->_BIFF_version == 0x500) {
$cch = strlen($format);
// Length of format string
$data = pack("vC", $ifmt, $cch);
$this->_append($header . $data . $format);
}
}
/**
* Write Cell
*
* @param Shared_XMLWriter $objWriter XML Writer
* @param Worksheet $pSheet Worksheet
* @param Cell $pCell Cell
* @param string[] $pStringTable String table
* @param string[] $pFlippedStringTable String table (flipped), for faster index searching
* @throws Exception
*/
private function _writeCell(Shared_XMLWriter $objWriter = null, Worksheet $pSheet = null, $pCellAddress = null, $pStringTable = null, $pFlippedStringTable = null)
{
$pCell = $pSheet->getCell($pCellAddress);
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()) {
$pCell->attach($pSheet);
$calculatedValue = $pCell->getCalculatedValue();
} else {
$pCell->attach($pSheet);
$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 RichText) {
$objWriter->writeElement('t', Shared_String::ControlCharacterPHP2OOXML(htmlspecialchars($pCell->getValue())));
} else {
if ($pCell->getValue() instanceof RichText) {
$objWriter->startElement('is');
$this->getParentWriter()->getWriterPart('stringtable')->writeRichText($objWriter, $pCell->getValue());
$objWriter->endElement();
}
}
break;
case 's':
// String
if (!$pCell->getValue() instanceof RichText) {
if (isset($pFlippedStringTable[$pCell->getValue()])) {
$objWriter->writeElement('v', $pFlippedStringTable[$pCell->getValue()]);
}
} else {
if ($pCell->getValue() instanceof 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 = 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.");
}
}
/**
* 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 Exception(JAMAError(ArgumentTypeException));
}
break;
case 'array':
$M = new Matrix($args[0]);
break;
default:
throw new Exception(JAMAError(PolymorphicArgumentException));
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 &= Shared_String::convertToNumberIfFraction($this->A[$i][$j]);
}
if (is_string($value) && strlen($value) > 0 && !is_numeric($value)) {
$value = trim($value, '"');
$validValues &= Shared_String::convertToNumberIfFraction($value);
}
if ($validValues) {
$this->A[$i][$j] = pow($this->A[$i][$j], $value);
} else {
$this->A[$i][$j] = Calculation_Functions::NaN();
}
}
}
return $this;
} else {
throw new Exception(JAMAError(PolymorphicArgumentException));
}
}
/**
* Store the DATAVALIDATIONS and DATAVALIDATION records.
*/
private function _writeDataValidity()
{
// Datavalidation collection
$dataValidationCollection = $this->_phpSheet->getDataValidationCollection();
// Write data validations?
if (count($dataValidationCollection) > 0) {
// 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 Cell_DataValidation::TYPE_NONE:
$type = 0x0;
break;
case Cell_DataValidation::TYPE_WHOLE:
$type = 0x1;
break;
case Cell_DataValidation::TYPE_DECIMAL:
$type = 0x2;
break;
case Cell_DataValidation::TYPE_LIST:
$type = 0x3;
break;
case Cell_DataValidation::TYPE_DATE:
$type = 0x4;
break;
case Cell_DataValidation::TYPE_TIME:
$type = 0x5;
break;
case Cell_DataValidation::TYPE_TEXTLENGTH:
$type = 0x6;
break;
case Cell_DataValidation::TYPE_CUSTOM:
$type = 0x7;
break;
}
$options |= $type << 0;
// error style
$errorStyle = $dataValidation->getType();
switch ($errorStyle) {
case Cell_DataValidation::STYLE_STOP:
$errorStyle = 0x0;
break;
case Cell_DataValidation::STYLE_WARNING:
$errorStyle = 0x1;
break;
case 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 Cell_DataValidation::OPERATOR_BETWEEN:
$operator = 0x0;
break;
case Cell_DataValidation::OPERATOR_NOTBETWEEN:
$operator = 0x1;
break;
case Cell_DataValidation::OPERATOR_EQUAL:
$operator = 0x2;
break;
case Cell_DataValidation::OPERATOR_NOTEQUAL:
$operator = 0x3;
break;
case Cell_DataValidation::OPERATOR_GREATERTHAN:
$operator = 0x4;
break;
case Cell_DataValidation::OPERATOR_LESSTHAN:
$operator = 0x5;
break;
case Cell_DataValidation::OPERATOR_GREATERTHANOREQUAL:
$operator = 0x6;
break;
case Cell_DataValidation::OPERATOR_LESSTHANOREQUAL:
$operator = 0x7;
break;
}
$options |= $operator << 20;
$data = pack('V', $options);
// prompt title
$promptTitle = $dataValidation->getPromptTitle() !== '' ? $dataValidation->getPromptTitle() : chr(0);
$data .= Shared_String::UTF8toBIFF8UnicodeLong($promptTitle);
// error title
$errorTitle = $dataValidation->getErrorTitle() !== '' ? $dataValidation->getErrorTitle() : chr(0);
$data .= Shared_String::UTF8toBIFF8UnicodeLong($errorTitle);
// prompt text
$prompt = $dataValidation->getPrompt() !== '' ? $dataValidation->getPrompt() : chr(0);
$data .= Shared_String::UTF8toBIFF8UnicodeLong($prompt);
// error text
$error = $dataValidation->getError() !== '' ? $dataValidation->getError() : chr(0);
$data .= 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);
}
}
}
/**
* 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 = 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'] = Style_Border::BORDER_THIN;
break;
case 'B':
$formatArray['borders']['bottom']['style'] = Style_Border::BORDER_THIN;
break;
case 'L':
$formatArray['borders']['left']['style'] = Style_Border::BORDER_THIN;
break;
case 'R':
$formatArray['borders']['right']['style'] = 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 = 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 = Cell::stringFromColumnIndex($column - 1);
$cellData = Calculation::_unwrapResult($cellData);
// Set cell value
$objPHPExcel->getActiveSheet()->getCell($columnLetter . $row)->setValue($hasCalculatedValue ? $cellDataFormula : $cellData);
if ($hasCalculatedValue) {
$cellData = 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'] = Style_Border::BORDER_THIN;
break;
case 'B':
$styleData['borders']['bottom']['style'] = Style_Border::BORDER_THIN;
break;
case 'L':
$styleData['borders']['left']['style'] = Style_Border::BORDER_THIN;
break;
case 'R':
$styleData['borders']['right']['style'] = Style_Border::BORDER_THIN;
break;
}
}
break;
}
}
if ($formatStyle > '' && $column > '' && $row > '') {
$columnLetter = Cell::stringFromColumnIndex($column - 1);
$objPHPExcel->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($this->_formats[$formatStyle]);
}
if (count($styleData) > 0 && $column > '' && $row > '') {
$columnLetter = Cell::stringFromColumnIndex($column - 1);
$objPHPExcel->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($styleData);
}
if ($columnWidth > '') {
if ($startCol == $endCol) {
$startCol = Cell::stringFromColumnIndex($startCol - 1);
$objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
} else {
$startCol = Cell::stringFromColumnIndex($startCol - 1);
$endCol = 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;
}
/**
* Write Rich Text
*
* @param Shared_XMLWriter $objWriter XML Writer
* @param RichText $pRichText Rich text
* @throws Exception
*/
public function writeRichText(Shared_XMLWriter $objWriter = null, RichText $pRichText = null)
{
// Loop through rich text elements
$elements = $pRichText->getRichTextElements();
foreach ($elements as $element) {
// r
$objWriter->startElement('r');
// rPr
if ($element instanceof 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();
// Superscript / subscript
if ($element->getFont()->getSuperScript() || $element->getFont()->getSubScript()) {
$objWriter->startElement('vertAlign');
if ($element->getFont()->getSuperScript()) {
$objWriter->writeAttribute('val', 'superscript');
} else {
if ($element->getFont()->getSubScript()) {
$objWriter->writeAttribute('val', 'subscript');
}
}
$objWriter->endElement();
}
// Strikethrough
$objWriter->startElement('strike');
$objWriter->writeAttribute('val', $element->getFont()->getStrikethrough() ? '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(Shared_String::ControlCharacterPHP2OOXML($element->getText()));
$objWriter->endElement();
$objWriter->endElement();
}
}
