/** * 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)); } }
/** * Extract range values * * @param string &$pRange String based range representation * @param Worksheet $pSheet Worksheet * @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned. * @throws Exception */ public function extractNamedRange(&$pRange = 'A1', Worksheet $pSheet = null, $resetLog = true) { // Return value $returnValue = array(); // echo 'extractNamedRange('.$pRange.')<br />'; if (!is_null($pSheet)) { // echo 'Current sheet name is '.$pSheet->getTitle().'<br />'; // echo 'Range reference is '.$pRange.'<br />'; if (strpos($pRange, '!') !== false) { // echo '$pRange reference includes sheet reference<br />'; $worksheetReference = Worksheet::extractSheetTitle($pRange, true); $pSheet = $pSheet->getParent()->getSheetByName($worksheetReference[0]); // echo 'New sheet name is '.$pSheet->getTitle().'<br />'; $pRange = $worksheetReference[1]; // echo 'Adjusted Range reference is '.$pRange.'<br />'; } // Named range? $namedRange = NamedRange::resolveRange($pRange, $pSheet); if (!is_null($namedRange)) { $pSheet = $namedRange->getWorksheet(); //// echo 'Named Range '.$pRange.' ('; $pRange = $namedRange->getRange(); //// echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />'; // if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) { // if (!$namedRange->getLocalOnly()) { // $pSheet = $namedRange->getWorksheet(); // } else { // return $returnValue; // } // } } else { return Calculation_Functions::REF(); } // Extract range $aReferences = Cell::extractAllCellReferencesInRange($pRange); if (count($aReferences) == 1) { list($currentCol, $currentRow) = Cell::coordinateFromString($aReferences[0]); if ($pSheet->cellExists($aReferences[0])) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = NULL; } } else { // Extract cell data foreach ($aReferences as $reference) { // Extract range list($currentCol, $currentRow) = Cell::coordinateFromString($reference); // echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />'; if ($pSheet->cellExists($reference)) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = NULL; } } } // print_r($returnValue); // echo '<br />'; } // Return return $returnValue; }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { if (!is_null($this->_spreadSheet)) { // garbage collect $this->_spreadSheet->garbageCollect(); // If $pFilename is php://output or php://stdout, make it a temporary file... $originalFilename = $pFilename; if (strtolower($pFilename) == 'php://output' || strtolower($pFilename) == 'php://stdout') { $pFilename = @tempnam('./', 'phpxltmp'); if ($pFilename == '') { $pFilename = $originalFilename; } } $saveDateReturnType = Calculation_Functions::getReturnDateType(); Calculation_Functions::setReturnDateType(Calculation_Functions::RETURNDATE_EXCEL); // Create string lookup table $this->_stringTable = array(); for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) { $this->_stringTable = $this->getWriterPart('StringTable')->createStringTable($this->_spreadSheet->getSheet($i), $this->_stringTable); } // Create styles dictionaries $this->_stylesConditionalHashTable->addFromSource($this->getWriterPart('Style')->allConditionalStyles($this->_spreadSheet)); $this->_fillHashTable->addFromSource($this->getWriterPart('Style')->allFills($this->_spreadSheet)); $this->_fontHashTable->addFromSource($this->getWriterPart('Style')->allFonts($this->_spreadSheet)); $this->_bordersHashTable->addFromSource($this->getWriterPart('Style')->allBorders($this->_spreadSheet)); $this->_numFmtHashTable->addFromSource($this->getWriterPart('Style')->allNumberFormats($this->_spreadSheet)); // Create drawing dictionary $this->_drawingHashTable->addFromSource($this->getWriterPart('Drawing')->allDrawings($this->_spreadSheet)); // Create new ZIP file and open it for writing $objZip = new ZipArchive(); // Try opening the ZIP file if ($objZip->open($pFilename, ZIPARCHIVE::OVERWRITE) !== true) { if ($objZip->open($pFilename, ZIPARCHIVE::CREATE) !== true) { throw new Exception("Could not open " . $pFilename . " for writing."); } } // Add [Content_Types].xml to ZIP file $objZip->addFromString('[Content_Types].xml', $this->getWriterPart('ContentTypes')->writeContentTypes($this->_spreadSheet)); // Add relationships to ZIP file $objZip->addFromString('_rels/.rels', $this->getWriterPart('Rels')->writeRelationships($this->_spreadSheet)); $objZip->addFromString('xl/_rels/workbook.xml.rels', $this->getWriterPart('Rels')->writeWorkbookRelationships($this->_spreadSheet)); // Add document properties to ZIP file $objZip->addFromString('docProps/app.xml', $this->getWriterPart('DocProps')->writeDocPropsApp($this->_spreadSheet)); $objZip->addFromString('docProps/core.xml', $this->getWriterPart('DocProps')->writeDocPropsCore($this->_spreadSheet)); // Add theme to ZIP file $objZip->addFromString('xl/theme/theme1.xml', $this->getWriterPart('Theme')->writeTheme($this->_spreadSheet)); // Add string table to ZIP file $objZip->addFromString('xl/sharedStrings.xml', $this->getWriterPart('StringTable')->writeStringTable($this->_stringTable)); // Add styles to ZIP file $objZip->addFromString('xl/styles.xml', $this->getWriterPart('Style')->writeStyles($this->_spreadSheet)); // Add workbook to ZIP file $objZip->addFromString('xl/workbook.xml', $this->getWriterPart('Workbook')->writeWorkbook($this->_spreadSheet)); // Add worksheets for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) { $objZip->addFromString('xl/worksheets/sheet' . ($i + 1) . '.xml', $this->getWriterPart('Worksheet')->writeWorksheet($this->_spreadSheet->getSheet($i), $this->_stringTable)); } // Add worksheet relationships (drawings, ...) for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); ++$i) { // Add relationships $objZip->addFromString('xl/worksheets/_rels/sheet' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeWorksheetRelationships($this->_spreadSheet->getSheet($i), $i + 1)); // Add drawing relationship parts if ($this->_spreadSheet->getSheet($i)->getDrawingCollection()->count() > 0) { // Drawing relationships $objZip->addFromString('xl/drawings/_rels/drawing' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeDrawingRelationships($this->_spreadSheet->getSheet($i))); // Drawings $objZip->addFromString('xl/drawings/drawing' . ($i + 1) . '.xml', $this->getWriterPart('Drawing')->writeDrawings($this->_spreadSheet->getSheet($i))); } // Add comment relationship parts if (count($this->_spreadSheet->getSheet($i)->getComments()) > 0) { // VML Comments $objZip->addFromString('xl/drawings/vmlDrawing' . ($i + 1) . '.vml', $this->getWriterPart('Comments')->writeVMLComments($this->_spreadSheet->getSheet($i))); // Comments $objZip->addFromString('xl/comments' . ($i + 1) . '.xml', $this->getWriterPart('Comments')->writeComments($this->_spreadSheet->getSheet($i))); } // Add header/footer relationship parts if (count($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages()) > 0) { // VML Drawings $objZip->addFromString('xl/drawings/vmlDrawingHF' . ($i + 1) . '.vml', $this->getWriterPart('Drawing')->writeVMLHeaderFooterImages($this->_spreadSheet->getSheet($i))); // VML Drawing relationships $objZip->addFromString('xl/drawings/_rels/vmlDrawingHF' . ($i + 1) . '.vml.rels', $this->getWriterPart('Rels')->writeHeaderFooterDrawingRelationships($this->_spreadSheet->getSheet($i))); // Media foreach ($this->_spreadSheet->getSheet($i)->getHeaderFooter()->getImages() as $image) { $objZip->addFromString('xl/media/' . $image->getIndexedFilename(), file_get_contents($image->getPath())); } } } // Add media for ($i = 0; $i < $this->getDrawingHashTable()->count(); ++$i) { if ($this->getDrawingHashTable()->getByIndex($i) instanceof Worksheet_Drawing) { $imageContents = null; $imagePath = $this->getDrawingHashTable()->getByIndex($i)->getPath(); if (strpos($imagePath, 'zip://') !== false) { $imagePath = substr($imagePath, 6); $imagePathSplitted = explode('#', $imagePath); $imageZip = new ZipArchive(); $imageZip->open($imagePathSplitted[0]); $imageContents = $imageZip->getFromName($imagePathSplitted[1]); $imageZip->close(); unset($imageZip); } else { $imageContents = file_get_contents($imagePath); } $objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents); } else { if ($this->getDrawingHashTable()->getByIndex($i) instanceof Worksheet_MemoryDrawing) { ob_start(); call_user_func($this->getDrawingHashTable()->getByIndex($i)->getRenderingFunction(), $this->getDrawingHashTable()->getByIndex($i)->getImageResource()); $imageContents = ob_get_contents(); ob_end_clean(); $objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents); } } } Calculation_Functions::setReturnDateType($saveDateReturnType); // Close file if ($objZip->close() === false) { throw new Exception("Could not close zip file {$pFilename}."); } // If a temporary file was used, copy it to the correct file stream if ($originalFilename != $pFilename) { if (copy($pFilename, $originalFilename) === false) { throw new Exception("Could not copy temporary zip file {$pFilename} to {$originalFilename}."); } @unlink($pFilename); } } else { throw new Exception("PHPExcel object unassigned."); } }
/** * TRIMNONPRINTABLE * * @param mixed $value Value to check * @return string */ public static function TRIMNONPRINTABLE($stringValue = '') { $stringValue = self::flattenSingleValue($stringValue); if (is_bool($stringValue)) { $stringValue = $stringValue ? 'TRUE' : 'FALSE'; } if (self::$_invalidChars == Null) { self::$_invalidChars = range(chr(0), chr(31)); } if (is_string($stringValue) || is_numeric($stringValue)) { return str_replace(self::$_invalidChars, '', trim($stringValue, "..")); } return Null; }
/** * 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; }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { // garbage collect $this->_phpExcel->garbageCollect(); $saveDateReturnType = Calculation_Functions::getReturnDateType(); Calculation_Functions::setReturnDateType(Calculation_Functions::RETURNDATE_EXCEL); // initialize colors array $this->_colors = array(); // Initialise workbook writer $this->_writerWorkbook = new Writer_Excel5_Workbook($this->_phpExcel, $this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser); // add 15 identical cell style Xfs // for now, we use the first cellXf instead of cellStyleXf $cellXfCollection = $this->_phpExcel->getCellXfCollection(); for ($i = 0; $i < 15; ++$i) { $this->_writerWorkbook->addXfWriter($cellXfCollection[0], true); } // add all the cell Xfs foreach ($this->_phpExcel->getCellXfCollection() as $style) { $this->_writerWorkbook->addXfWriter($style, false); } // initialize OLE file $workbookStreamName = $this->_BIFF_version == 0x600 ? 'Workbook' : 'Book'; $OLE = new Shared_OLE_PPS_File(Shared_OLE::Asc2Ucs($workbookStreamName)); // Initialise worksheet writers $countSheets = $this->_phpExcel->getSheetCount(); // Write the worksheet streams before the global workbook stream, // because the byte sizes of these are needed in the global workbook stream $worksheetSizes = array(); for ($i = 0; $i < $countSheets; ++$i) { $this->_writerWorksheets[$i] = new Writer_Excel5_Worksheet($this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_preCalculateFormulas, $this->_phpExcel->getSheet($i)); $this->_writerWorksheets[$i]->close(); $worksheetSizes[] = $this->_writerWorksheets[$i]->_datasize; } // add binary data for global workbook stream $OLE->append($this->_writerWorkbook->writeWorkbook($worksheetSizes)); // add binary data for sheet streams for ($i = 0; $i < $countSheets; ++$i) { $OLE->append($this->_writerWorksheets[$i]->getData()); } $root = new Shared_OLE_PPS_Root(time(), time(), array($OLE)); // save the OLE file $res = $root->save($pFilename); Calculation_Functions::setReturnDateType($saveDateReturnType); }