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