/** * 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(); } }