Geen omschrijving

Calculation.php 172KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180218121822183218421852186218721882189219021912192219321942195219621972198219922002201220222032204220522062207220822092210221122122213221422152216221722182219222022212222222322242225222622272228222922302231223222332234223522362237223822392240224122422243224422452246224722482249225022512252225322542255225622572258225922602261226222632264226522662267226822692270227122722273227422752276227722782279228022812282228322842285228622872288228922902291229222932294229522962297229822992300230123022303230423052306230723082309231023112312231323142315231623172318231923202321232223232324232523262327232823292330233123322333233423352336233723382339234023412342234323442345234623472348234923502351235223532354235523562357235823592360236123622363236423652366236723682369237023712372237323742375237623772378237923802381238223832384238523862387238823892390239123922393239423952396239723982399240024012402240324042405240624072408240924102411241224132414241524162417241824192420242124222423242424252426242724282429243024312432243324342435243624372438243924402441244224432444244524462447244824492450245124522453245424552456245724582459246024612462246324642465246624672468246924702471247224732474247524762477247824792480248124822483248424852486248724882489249024912492249324942495249624972498249925002501250225032504250525062507250825092510251125122513251425152516251725182519252025212522252325242525252625272528252925302531253225332534253525362537253825392540254125422543254425452546254725482549255025512552255325542555255625572558255925602561256225632564256525662567256825692570257125722573257425752576257725782579258025812582258325842585258625872588258925902591259225932594259525962597259825992600260126022603260426052606260726082609261026112612261326142615261626172618261926202621262226232624262526262627262826292630263126322633263426352636263726382639264026412642264326442645264626472648264926502651265226532654265526562657265826592660266126622663266426652666266726682669267026712672267326742675267626772678267926802681268226832684268526862687268826892690269126922693269426952696269726982699270027012702270327042705270627072708270927102711271227132714271527162717271827192720272127222723272427252726272727282729273027312732273327342735273627372738273927402741274227432744274527462747274827492750275127522753275427552756275727582759276027612762276327642765276627672768276927702771277227732774277527762777277827792780278127822783278427852786278727882789279027912792279327942795279627972798279928002801280228032804280528062807280828092810281128122813281428152816281728182819282028212822282328242825282628272828282928302831283228332834283528362837283828392840284128422843284428452846284728482849285028512852285328542855285628572858285928602861286228632864286528662867286828692870287128722873287428752876287728782879288028812882288328842885288628872888288928902891289228932894289528962897289828992900290129022903290429052906290729082909291029112912291329142915291629172918291929202921292229232924292529262927292829292930293129322933293429352936293729382939294029412942294329442945294629472948294929502951295229532954295529562957295829592960296129622963296429652966296729682969297029712972297329742975297629772978297929802981298229832984298529862987298829892990299129922993299429952996299729982999300030013002300330043005300630073008300930103011301230133014301530163017301830193020302130223023302430253026302730283029303030313032303330343035303630373038303930403041304230433044304530463047304830493050305130523053305430553056305730583059306030613062306330643065306630673068306930703071307230733074307530763077307830793080308130823083308430853086308730883089309030913092309330943095309630973098309931003101310231033104310531063107310831093110311131123113311431153116311731183119312031213122312331243125312631273128312931303131313231333134313531363137313831393140314131423143314431453146314731483149315031513152315331543155315631573158315931603161316231633164316531663167316831693170317131723173317431753176317731783179318031813182318331843185318631873188318931903191319231933194319531963197319831993200320132023203320432053206320732083209321032113212321332143215321632173218321932203221322232233224322532263227322832293230323132323233323432353236323732383239324032413242324332443245324632473248324932503251325232533254325532563257325832593260326132623263326432653266326732683269327032713272327332743275327632773278327932803281328232833284328532863287328832893290329132923293329432953296329732983299330033013302330333043305330633073308330933103311331233133314331533163317331833193320332133223323332433253326332733283329333033313332333333343335333633373338333933403341334233433344334533463347334833493350335133523353335433553356335733583359336033613362336333643365336633673368336933703371337233733374337533763377337833793380338133823383338433853386338733883389339033913392339333943395339633973398339934003401340234033404340534063407340834093410341134123413341434153416341734183419342034213422342334243425342634273428342934303431343234333434343534363437343834393440344134423443344434453446344734483449345034513452345334543455345634573458345934603461346234633464346534663467346834693470347134723473347434753476347734783479348034813482348334843485348634873488348934903491349234933494349534963497349834993500350135023503350435053506350735083509351035113512351335143515351635173518351935203521352235233524352535263527352835293530353135323533353435353536353735383539354035413542354335443545354635473548354935503551355235533554355535563557355835593560356135623563356435653566356735683569357035713572357335743575357635773578357935803581358235833584358535863587358835893590359135923593359435953596359735983599360036013602360336043605360636073608360936103611361236133614361536163617361836193620362136223623362436253626362736283629363036313632363336343635363636373638363936403641364236433644364536463647364836493650365136523653365436553656365736583659366036613662366336643665366636673668366936703671367236733674367536763677367836793680368136823683368436853686368736883689369036913692369336943695369636973698369937003701370237033704370537063707370837093710371137123713371437153716371737183719372037213722372337243725372637273728372937303731373237333734373537363737373837393740374137423743374437453746374737483749375037513752375337543755375637573758375937603761376237633764376537663767376837693770377137723773377437753776377737783779378037813782378337843785378637873788378937903791379237933794379537963797379837993800380138023803380438053806380738083809381038113812381338143815381638173818381938203821382238233824382538263827382838293830383138323833383438353836383738383839384038413842384338443845384638473848384938503851385238533854385538563857385838593860386138623863386438653866386738683869387038713872387338743875387638773878387938803881388238833884388538863887388838893890389138923893389438953896389738983899390039013902390339043905390639073908390939103911391239133914391539163917391839193920392139223923392439253926392739283929393039313932393339343935393639373938393939403941394239433944394539463947394839493950395139523953
  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2014 PHPExcel
  6. *
  7. * This library is free software; you can redistribute it and/or
  8. * modify it under the terms of the GNU Lesser General Public
  9. * License as published by the Free Software Foundation; either
  10. * version 2.1 of the License, or (at your option) any later version.
  11. *
  12. * This library is distributed in the hope that it will be useful,
  13. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  15. * Lesser General Public License for more details.
  16. *
  17. * You should have received a copy of the GNU Lesser General Public
  18. * License along with this library; if not, write to the Free Software
  19. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
  20. *
  21. * @category PHPExcel
  22. * @package PHPExcel_Calculation
  23. * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  25. * @version ##VERSION##, ##DATE##
  26. */
  27. /** PHPExcel root directory */
  28. if (!defined('PHPEXCEL_ROOT')) {
  29. /**
  30. * @ignore
  31. */
  32. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../');
  33. require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
  34. }
  35. if (!defined('CALCULATION_REGEXP_CELLREF')) {
  36. // Test for support of \P (multibyte options) in PCRE
  37. if(defined('PREG_BAD_UTF8_ERROR')) {
  38. // Cell reference (cell or range of cells, with or without a sheet reference)
  39. define('CALCULATION_REGEXP_CELLREF','((([^\s,!&%^\/\*\+<>=-]*)|(\'[^\']*\')|(\"[^\"]*\"))!)?\$?([a-z]{1,3})\$?(\d{1,7})');
  40. // Named Range of cells
  41. define('CALCULATION_REGEXP_NAMEDRANGE','((([^\s,!&%^\/\*\+<>=-]*)|(\'[^\']*\')|(\"[^\"]*\"))!)?([_A-Z][_A-Z0-9\.]*)');
  42. } else {
  43. // Cell reference (cell or range of cells, with or without a sheet reference)
  44. define('CALCULATION_REGEXP_CELLREF','(((\w*)|(\'[^\']*\')|(\"[^\"]*\"))!)?\$?([a-z]{1,3})\$?(\d+)');
  45. // Named Range of cells
  46. define('CALCULATION_REGEXP_NAMEDRANGE','(((\w*)|(\'.*\')|(\".*\"))!)?([_A-Z][_A-Z0-9\.]*)');
  47. }
  48. }
  49. /**
  50. * PHPExcel_Calculation (Multiton)
  51. *
  52. * @category PHPExcel
  53. * @package PHPExcel_Calculation
  54. * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  55. */
  56. class PHPExcel_Calculation {
  57. /** Constants */
  58. /** Regular Expressions */
  59. // Numeric operand
  60. const CALCULATION_REGEXP_NUMBER = '[-+]?\d*\.?\d+(e[-+]?\d+)?';
  61. // String operand
  62. const CALCULATION_REGEXP_STRING = '"(?:[^"]|"")*"';
  63. // Opening bracket
  64. const CALCULATION_REGEXP_OPENBRACE = '\(';
  65. // Function (allow for the old @ symbol that could be used to prefix a function, but we'll ignore it)
  66. const CALCULATION_REGEXP_FUNCTION = '@?([A-Z][A-Z0-9\.]*)[\s]*\(';
  67. // Cell reference (cell or range of cells, with or without a sheet reference)
  68. const CALCULATION_REGEXP_CELLREF = CALCULATION_REGEXP_CELLREF;
  69. // Named Range of cells
  70. const CALCULATION_REGEXP_NAMEDRANGE = CALCULATION_REGEXP_NAMEDRANGE;
  71. // Error
  72. const CALCULATION_REGEXP_ERROR = '\#[A-Z][A-Z0_\/]*[!\?]?';
  73. /** constants */
  74. const RETURN_ARRAY_AS_ERROR = 'error';
  75. const RETURN_ARRAY_AS_VALUE = 'value';
  76. const RETURN_ARRAY_AS_ARRAY = 'array';
  77. private static $returnArrayAsType = self::RETURN_ARRAY_AS_VALUE;
  78. /**
  79. * Instance of this class
  80. *
  81. * @access private
  82. * @var PHPExcel_Calculation
  83. */
  84. private static $_instance;
  85. /**
  86. * Instance of the workbook this Calculation Engine is using
  87. *
  88. * @access private
  89. * @var PHPExcel
  90. */
  91. private $_workbook;
  92. /**
  93. * List of instances of the calculation engine that we've instantiated for individual workbooks
  94. *
  95. * @access private
  96. * @var PHPExcel_Calculation[]
  97. */
  98. private static $_workbookSets;
  99. /**
  100. * Calculation cache
  101. *
  102. * @access private
  103. * @var array
  104. */
  105. private $_calculationCache = array ();
  106. /**
  107. * Calculation cache enabled
  108. *
  109. * @access private
  110. * @var boolean
  111. */
  112. private $_calculationCacheEnabled = TRUE;
  113. /**
  114. * List of operators that can be used within formulae
  115. * The true/false value indicates whether it is a binary operator or a unary operator
  116. *
  117. * @access private
  118. * @var array
  119. */
  120. private static $_operators = array('+' => TRUE, '-' => TRUE, '*' => TRUE, '/' => TRUE,
  121. '^' => TRUE, '&' => TRUE, '%' => FALSE, '~' => FALSE,
  122. '>' => TRUE, '<' => TRUE, '=' => TRUE, '>=' => TRUE,
  123. '<=' => TRUE, '<>' => TRUE, '|' => TRUE, ':' => TRUE
  124. );
  125. /**
  126. * List of binary operators (those that expect two operands)
  127. *
  128. * @access private
  129. * @var array
  130. */
  131. private static $_binaryOperators = array('+' => TRUE, '-' => TRUE, '*' => TRUE, '/' => TRUE,
  132. '^' => TRUE, '&' => TRUE, '>' => TRUE, '<' => TRUE,
  133. '=' => TRUE, '>=' => TRUE, '<=' => TRUE, '<>' => TRUE,
  134. '|' => TRUE, ':' => TRUE
  135. );
  136. /**
  137. * The debug log generated by the calculation engine
  138. *
  139. * @access private
  140. * @var PHPExcel_CalcEngine_Logger
  141. *
  142. */
  143. private $debugLog;
  144. /**
  145. * Flag to determine how formula errors should be handled
  146. * If true, then a user error will be triggered
  147. * If false, then an exception will be thrown
  148. *
  149. * @access public
  150. * @var boolean
  151. *
  152. */
  153. public $suppressFormulaErrors = FALSE;
  154. /**
  155. * Error message for any error that was raised/thrown by the calculation engine
  156. *
  157. * @access public
  158. * @var string
  159. *
  160. */
  161. public $formulaError = NULL;
  162. /**
  163. * An array of the nested cell references accessed by the calculation engine, used for the debug log
  164. *
  165. * @access private
  166. * @var array of string
  167. *
  168. */
  169. private $_cyclicReferenceStack;
  170. private $_cellStack = array();
  171. /**
  172. * Current iteration counter for cyclic formulae
  173. * If the value is 0 (or less) then cyclic formulae will throw an exception,
  174. * otherwise they will iterate to the limit defined here before returning a result
  175. *
  176. * @var integer
  177. *
  178. */
  179. private $_cyclicFormulaCount = 1;
  180. private $_cyclicFormulaCell = '';
  181. /**
  182. * Number of iterations for cyclic formulae
  183. *
  184. * @var integer
  185. *
  186. */
  187. public $cyclicFormulaCount = 1;
  188. /**
  189. * Precision used for calculations
  190. *
  191. * @var integer
  192. *
  193. */
  194. private $_savedPrecision = 14;
  195. /**
  196. * The current locale setting
  197. *
  198. * @var string
  199. *
  200. */
  201. private static $_localeLanguage = 'en_us'; // US English (default locale)
  202. /**
  203. * List of available locale settings
  204. * Note that this is read for the locale subdirectory only when requested
  205. *
  206. * @var string[]
  207. *
  208. */
  209. private static $_validLocaleLanguages = array( 'en' // English (default language)
  210. );
  211. /**
  212. * Locale-specific argument separator for function arguments
  213. *
  214. * @var string
  215. *
  216. */
  217. private static $_localeArgumentSeparator = ',';
  218. private static $_localeFunctions = array();
  219. /**
  220. * Locale-specific translations for Excel constants (True, False and Null)
  221. *
  222. * @var string[]
  223. *
  224. */
  225. public static $_localeBoolean = array( 'TRUE' => 'TRUE',
  226. 'FALSE' => 'FALSE',
  227. 'NULL' => 'NULL'
  228. );
  229. /**
  230. * Excel constant string translations to their PHP equivalents
  231. * Constant conversion from text name/value to actual (datatyped) value
  232. *
  233. * @var string[]
  234. *
  235. */
  236. private static $_ExcelConstants = array('TRUE' => TRUE,
  237. 'FALSE' => FALSE,
  238. 'NULL' => NULL
  239. );
  240. // PHPExcel functions
  241. private static $_PHPExcelFunctions = array( // PHPExcel functions
  242. 'ABS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  243. 'functionCall' => 'abs',
  244. 'argumentCount' => '1'
  245. ),
  246. 'ACCRINT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  247. 'functionCall' => 'PHPExcel_Calculation_Financial::ACCRINT',
  248. 'argumentCount' => '4-7'
  249. ),
  250. 'ACCRINTM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  251. 'functionCall' => 'PHPExcel_Calculation_Financial::ACCRINTM',
  252. 'argumentCount' => '3-5'
  253. ),
  254. 'ACOS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  255. 'functionCall' => 'acos',
  256. 'argumentCount' => '1'
  257. ),
  258. 'ACOSH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  259. 'functionCall' => 'acosh',
  260. 'argumentCount' => '1'
  261. ),
  262. 'ADDRESS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  263. 'functionCall' => 'PHPExcel_Calculation_LookupRef::CELL_ADDRESS',
  264. 'argumentCount' => '2-5'
  265. ),
  266. 'AMORDEGRC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  267. 'functionCall' => 'PHPExcel_Calculation_Financial::AMORDEGRC',
  268. 'argumentCount' => '6,7'
  269. ),
  270. 'AMORLINC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  271. 'functionCall' => 'PHPExcel_Calculation_Financial::AMORLINC',
  272. 'argumentCount' => '6,7'
  273. ),
  274. 'AND' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOGICAL,
  275. 'functionCall' => 'PHPExcel_Calculation_Logical::LOGICAL_AND',
  276. 'argumentCount' => '1+'
  277. ),
  278. 'AREAS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  279. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  280. 'argumentCount' => '1'
  281. ),
  282. 'ASC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  283. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  284. 'argumentCount' => '1'
  285. ),
  286. 'ASIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  287. 'functionCall' => 'asin',
  288. 'argumentCount' => '1'
  289. ),
  290. 'ASINH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  291. 'functionCall' => 'asinh',
  292. 'argumentCount' => '1'
  293. ),
  294. 'ATAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  295. 'functionCall' => 'atan',
  296. 'argumentCount' => '1'
  297. ),
  298. 'ATAN2' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  299. 'functionCall' => 'PHPExcel_Calculation_MathTrig::ATAN2',
  300. 'argumentCount' => '2'
  301. ),
  302. 'ATANH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  303. 'functionCall' => 'atanh',
  304. 'argumentCount' => '1'
  305. ),
  306. 'AVEDEV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  307. 'functionCall' => 'PHPExcel_Calculation_Statistical::AVEDEV',
  308. 'argumentCount' => '1+'
  309. ),
  310. 'AVERAGE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  311. 'functionCall' => 'PHPExcel_Calculation_Statistical::AVERAGE',
  312. 'argumentCount' => '1+'
  313. ),
  314. 'AVERAGEA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  315. 'functionCall' => 'PHPExcel_Calculation_Statistical::AVERAGEA',
  316. 'argumentCount' => '1+'
  317. ),
  318. 'AVERAGEIF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  319. 'functionCall' => 'PHPExcel_Calculation_Statistical::AVERAGEIF',
  320. 'argumentCount' => '2,3'
  321. ),
  322. 'AVERAGEIFS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  323. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  324. 'argumentCount' => '3+'
  325. ),
  326. 'BAHTTEXT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  327. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  328. 'argumentCount' => '1'
  329. ),
  330. 'BESSELI' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  331. 'functionCall' => 'PHPExcel_Calculation_Engineering::BESSELI',
  332. 'argumentCount' => '2'
  333. ),
  334. 'BESSELJ' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  335. 'functionCall' => 'PHPExcel_Calculation_Engineering::BESSELJ',
  336. 'argumentCount' => '2'
  337. ),
  338. 'BESSELK' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  339. 'functionCall' => 'PHPExcel_Calculation_Engineering::BESSELK',
  340. 'argumentCount' => '2'
  341. ),
  342. 'BESSELY' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  343. 'functionCall' => 'PHPExcel_Calculation_Engineering::BESSELY',
  344. 'argumentCount' => '2'
  345. ),
  346. 'BETADIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  347. 'functionCall' => 'PHPExcel_Calculation_Statistical::BETADIST',
  348. 'argumentCount' => '3-5'
  349. ),
  350. 'BETAINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  351. 'functionCall' => 'PHPExcel_Calculation_Statistical::BETAINV',
  352. 'argumentCount' => '3-5'
  353. ),
  354. 'BIN2DEC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  355. 'functionCall' => 'PHPExcel_Calculation_Engineering::BINTODEC',
  356. 'argumentCount' => '1'
  357. ),
  358. 'BIN2HEX' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  359. 'functionCall' => 'PHPExcel_Calculation_Engineering::BINTOHEX',
  360. 'argumentCount' => '1,2'
  361. ),
  362. 'BIN2OCT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  363. 'functionCall' => 'PHPExcel_Calculation_Engineering::BINTOOCT',
  364. 'argumentCount' => '1,2'
  365. ),
  366. 'BINOMDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  367. 'functionCall' => 'PHPExcel_Calculation_Statistical::BINOMDIST',
  368. 'argumentCount' => '4'
  369. ),
  370. 'CEILING' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  371. 'functionCall' => 'PHPExcel_Calculation_MathTrig::CEILING',
  372. 'argumentCount' => '2'
  373. ),
  374. 'CELL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  375. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  376. 'argumentCount' => '1,2'
  377. ),
  378. 'CHAR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  379. 'functionCall' => 'PHPExcel_Calculation_TextData::CHARACTER',
  380. 'argumentCount' => '1'
  381. ),
  382. 'CHIDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  383. 'functionCall' => 'PHPExcel_Calculation_Statistical::CHIDIST',
  384. 'argumentCount' => '2'
  385. ),
  386. 'CHIINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  387. 'functionCall' => 'PHPExcel_Calculation_Statistical::CHIINV',
  388. 'argumentCount' => '2'
  389. ),
  390. 'CHITEST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  391. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  392. 'argumentCount' => '2'
  393. ),
  394. 'CHOOSE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  395. 'functionCall' => 'PHPExcel_Calculation_LookupRef::CHOOSE',
  396. 'argumentCount' => '2+'
  397. ),
  398. 'CLEAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  399. 'functionCall' => 'PHPExcel_Calculation_TextData::TRIMNONPRINTABLE',
  400. 'argumentCount' => '1'
  401. ),
  402. 'CODE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  403. 'functionCall' => 'PHPExcel_Calculation_TextData::ASCIICODE',
  404. 'argumentCount' => '1'
  405. ),
  406. 'COLUMN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  407. 'functionCall' => 'PHPExcel_Calculation_LookupRef::COLUMN',
  408. 'argumentCount' => '-1',
  409. 'passByReference' => array(TRUE)
  410. ),
  411. 'COLUMNS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  412. 'functionCall' => 'PHPExcel_Calculation_LookupRef::COLUMNS',
  413. 'argumentCount' => '1'
  414. ),
  415. 'COMBIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  416. 'functionCall' => 'PHPExcel_Calculation_MathTrig::COMBIN',
  417. 'argumentCount' => '2'
  418. ),
  419. 'COMPLEX' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  420. 'functionCall' => 'PHPExcel_Calculation_Engineering::COMPLEX',
  421. 'argumentCount' => '2,3'
  422. ),
  423. 'CONCATENATE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  424. 'functionCall' => 'PHPExcel_Calculation_TextData::CONCATENATE',
  425. 'argumentCount' => '1+'
  426. ),
  427. 'CONFIDENCE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  428. 'functionCall' => 'PHPExcel_Calculation_Statistical::CONFIDENCE',
  429. 'argumentCount' => '3'
  430. ),
  431. 'CONVERT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  432. 'functionCall' => 'PHPExcel_Calculation_Engineering::CONVERTUOM',
  433. 'argumentCount' => '3'
  434. ),
  435. 'CORREL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  436. 'functionCall' => 'PHPExcel_Calculation_Statistical::CORREL',
  437. 'argumentCount' => '2'
  438. ),
  439. 'COS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  440. 'functionCall' => 'cos',
  441. 'argumentCount' => '1'
  442. ),
  443. 'COSH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  444. 'functionCall' => 'cosh',
  445. 'argumentCount' => '1'
  446. ),
  447. 'COUNT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  448. 'functionCall' => 'PHPExcel_Calculation_Statistical::COUNT',
  449. 'argumentCount' => '1+'
  450. ),
  451. 'COUNTA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  452. 'functionCall' => 'PHPExcel_Calculation_Statistical::COUNTA',
  453. 'argumentCount' => '1+'
  454. ),
  455. 'COUNTBLANK' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  456. 'functionCall' => 'PHPExcel_Calculation_Statistical::COUNTBLANK',
  457. 'argumentCount' => '1'
  458. ),
  459. 'COUNTIF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  460. 'functionCall' => 'PHPExcel_Calculation_Statistical::COUNTIF',
  461. 'argumentCount' => '2'
  462. ),
  463. 'COUNTIFS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  464. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  465. 'argumentCount' => '2'
  466. ),
  467. 'COUPDAYBS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  468. 'functionCall' => 'PHPExcel_Calculation_Financial::COUPDAYBS',
  469. 'argumentCount' => '3,4'
  470. ),
  471. 'COUPDAYS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  472. 'functionCall' => 'PHPExcel_Calculation_Financial::COUPDAYS',
  473. 'argumentCount' => '3,4'
  474. ),
  475. 'COUPDAYSNC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  476. 'functionCall' => 'PHPExcel_Calculation_Financial::COUPDAYSNC',
  477. 'argumentCount' => '3,4'
  478. ),
  479. 'COUPNCD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  480. 'functionCall' => 'PHPExcel_Calculation_Financial::COUPNCD',
  481. 'argumentCount' => '3,4'
  482. ),
  483. 'COUPNUM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  484. 'functionCall' => 'PHPExcel_Calculation_Financial::COUPNUM',
  485. 'argumentCount' => '3,4'
  486. ),
  487. 'COUPPCD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  488. 'functionCall' => 'PHPExcel_Calculation_Financial::COUPPCD',
  489. 'argumentCount' => '3,4'
  490. ),
  491. 'COVAR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  492. 'functionCall' => 'PHPExcel_Calculation_Statistical::COVAR',
  493. 'argumentCount' => '2'
  494. ),
  495. 'CRITBINOM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  496. 'functionCall' => 'PHPExcel_Calculation_Statistical::CRITBINOM',
  497. 'argumentCount' => '3'
  498. ),
  499. 'CUBEKPIMEMBER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_CUBE,
  500. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  501. 'argumentCount' => '?'
  502. ),
  503. 'CUBEMEMBER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_CUBE,
  504. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  505. 'argumentCount' => '?'
  506. ),
  507. 'CUBEMEMBERPROPERTY' => array('category' => PHPExcel_Calculation_Function::CATEGORY_CUBE,
  508. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  509. 'argumentCount' => '?'
  510. ),
  511. 'CUBERANKEDMEMBER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_CUBE,
  512. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  513. 'argumentCount' => '?'
  514. ),
  515. 'CUBESET' => array('category' => PHPExcel_Calculation_Function::CATEGORY_CUBE,
  516. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  517. 'argumentCount' => '?'
  518. ),
  519. 'CUBESETCOUNT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_CUBE,
  520. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  521. 'argumentCount' => '?'
  522. ),
  523. 'CUBEVALUE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_CUBE,
  524. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  525. 'argumentCount' => '?'
  526. ),
  527. 'CUMIPMT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  528. 'functionCall' => 'PHPExcel_Calculation_Financial::CUMIPMT',
  529. 'argumentCount' => '6'
  530. ),
  531. 'CUMPRINC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  532. 'functionCall' => 'PHPExcel_Calculation_Financial::CUMPRINC',
  533. 'argumentCount' => '6'
  534. ),
  535. 'DATE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  536. 'functionCall' => 'PHPExcel_Calculation_DateTime::DATE',
  537. 'argumentCount' => '3'
  538. ),
  539. 'DATEDIF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  540. 'functionCall' => 'PHPExcel_Calculation_DateTime::DATEDIF',
  541. 'argumentCount' => '2,3'
  542. ),
  543. 'DATEVALUE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  544. 'functionCall' => 'PHPExcel_Calculation_DateTime::DATEVALUE',
  545. 'argumentCount' => '1'
  546. ),
  547. 'DAVERAGE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
  548. 'functionCall' => 'PHPExcel_Calculation_Database::DAVERAGE',
  549. 'argumentCount' => '3'
  550. ),
  551. 'DAY' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  552. 'functionCall' => 'PHPExcel_Calculation_DateTime::DAYOFMONTH',
  553. 'argumentCount' => '1'
  554. ),
  555. 'DAYS360' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  556. 'functionCall' => 'PHPExcel_Calculation_DateTime::DAYS360',
  557. 'argumentCount' => '2,3'
  558. ),
  559. 'DB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  560. 'functionCall' => 'PHPExcel_Calculation_Financial::DB',
  561. 'argumentCount' => '4,5'
  562. ),
  563. 'DCOUNT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
  564. 'functionCall' => 'PHPExcel_Calculation_Database::DCOUNT',
  565. 'argumentCount' => '3'
  566. ),
  567. 'DCOUNTA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
  568. 'functionCall' => 'PHPExcel_Calculation_Database::DCOUNTA',
  569. 'argumentCount' => '3'
  570. ),
  571. 'DDB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  572. 'functionCall' => 'PHPExcel_Calculation_Financial::DDB',
  573. 'argumentCount' => '4,5'
  574. ),
  575. 'DEC2BIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  576. 'functionCall' => 'PHPExcel_Calculation_Engineering::DECTOBIN',
  577. 'argumentCount' => '1,2'
  578. ),
  579. 'DEC2HEX' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  580. 'functionCall' => 'PHPExcel_Calculation_Engineering::DECTOHEX',
  581. 'argumentCount' => '1,2'
  582. ),
  583. 'DEC2OCT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  584. 'functionCall' => 'PHPExcel_Calculation_Engineering::DECTOOCT',
  585. 'argumentCount' => '1,2'
  586. ),
  587. 'DEGREES' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  588. 'functionCall' => 'rad2deg',
  589. 'argumentCount' => '1'
  590. ),
  591. 'DELTA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  592. 'functionCall' => 'PHPExcel_Calculation_Engineering::DELTA',
  593. 'argumentCount' => '1,2'
  594. ),
  595. 'DEVSQ' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  596. 'functionCall' => 'PHPExcel_Calculation_Statistical::DEVSQ',
  597. 'argumentCount' => '1+'
  598. ),
  599. 'DGET' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
  600. 'functionCall' => 'PHPExcel_Calculation_Database::DGET',
  601. 'argumentCount' => '3'
  602. ),
  603. 'DISC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  604. 'functionCall' => 'PHPExcel_Calculation_Financial::DISC',
  605. 'argumentCount' => '4,5'
  606. ),
  607. 'DMAX' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
  608. 'functionCall' => 'PHPExcel_Calculation_Database::DMAX',
  609. 'argumentCount' => '3'
  610. ),
  611. 'DMIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
  612. 'functionCall' => 'PHPExcel_Calculation_Database::DMIN',
  613. 'argumentCount' => '3'
  614. ),
  615. 'DOLLAR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  616. 'functionCall' => 'PHPExcel_Calculation_TextData::DOLLAR',
  617. 'argumentCount' => '1,2'
  618. ),
  619. 'DOLLARDE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  620. 'functionCall' => 'PHPExcel_Calculation_Financial::DOLLARDE',
  621. 'argumentCount' => '2'
  622. ),
  623. 'DOLLARFR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  624. 'functionCall' => 'PHPExcel_Calculation_Financial::DOLLARFR',
  625. 'argumentCount' => '2'
  626. ),
  627. 'DPRODUCT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
  628. 'functionCall' => 'PHPExcel_Calculation_Database::DPRODUCT',
  629. 'argumentCount' => '3'
  630. ),
  631. 'DSTDEV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
  632. 'functionCall' => 'PHPExcel_Calculation_Database::DSTDEV',
  633. 'argumentCount' => '3'
  634. ),
  635. 'DSTDEVP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
  636. 'functionCall' => 'PHPExcel_Calculation_Database::DSTDEVP',
  637. 'argumentCount' => '3'
  638. ),
  639. 'DSUM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
  640. 'functionCall' => 'PHPExcel_Calculation_Database::DSUM',
  641. 'argumentCount' => '3'
  642. ),
  643. 'DURATION' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  644. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  645. 'argumentCount' => '5,6'
  646. ),
  647. 'DVAR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
  648. 'functionCall' => 'PHPExcel_Calculation_Database::DVAR',
  649. 'argumentCount' => '3'
  650. ),
  651. 'DVARP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATABASE,
  652. 'functionCall' => 'PHPExcel_Calculation_Database::DVARP',
  653. 'argumentCount' => '3'
  654. ),
  655. 'EDATE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  656. 'functionCall' => 'PHPExcel_Calculation_DateTime::EDATE',
  657. 'argumentCount' => '2'
  658. ),
  659. 'EFFECT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  660. 'functionCall' => 'PHPExcel_Calculation_Financial::EFFECT',
  661. 'argumentCount' => '2'
  662. ),
  663. 'EOMONTH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  664. 'functionCall' => 'PHPExcel_Calculation_DateTime::EOMONTH',
  665. 'argumentCount' => '2'
  666. ),
  667. 'ERF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  668. 'functionCall' => 'PHPExcel_Calculation_Engineering::ERF',
  669. 'argumentCount' => '1,2'
  670. ),
  671. 'ERFC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  672. 'functionCall' => 'PHPExcel_Calculation_Engineering::ERFC',
  673. 'argumentCount' => '1'
  674. ),
  675. 'ERROR.TYPE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  676. 'functionCall' => 'PHPExcel_Calculation_Functions::ERROR_TYPE',
  677. 'argumentCount' => '1'
  678. ),
  679. 'EVEN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  680. 'functionCall' => 'PHPExcel_Calculation_MathTrig::EVEN',
  681. 'argumentCount' => '1'
  682. ),
  683. 'EXACT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  684. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  685. 'argumentCount' => '2'
  686. ),
  687. 'EXP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  688. 'functionCall' => 'exp',
  689. 'argumentCount' => '1'
  690. ),
  691. 'EXPONDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  692. 'functionCall' => 'PHPExcel_Calculation_Statistical::EXPONDIST',
  693. 'argumentCount' => '3'
  694. ),
  695. 'FACT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  696. 'functionCall' => 'PHPExcel_Calculation_MathTrig::FACT',
  697. 'argumentCount' => '1'
  698. ),
  699. 'FACTDOUBLE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  700. 'functionCall' => 'PHPExcel_Calculation_MathTrig::FACTDOUBLE',
  701. 'argumentCount' => '1'
  702. ),
  703. 'FALSE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOGICAL,
  704. 'functionCall' => 'PHPExcel_Calculation_Logical::FALSE',
  705. 'argumentCount' => '0'
  706. ),
  707. 'FDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  708. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  709. 'argumentCount' => '3'
  710. ),
  711. 'FIND' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  712. 'functionCall' => 'PHPExcel_Calculation_TextData::SEARCHSENSITIVE',
  713. 'argumentCount' => '2,3'
  714. ),
  715. 'FINDB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  716. 'functionCall' => 'PHPExcel_Calculation_TextData::SEARCHSENSITIVE',
  717. 'argumentCount' => '2,3'
  718. ),
  719. 'FINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  720. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  721. 'argumentCount' => '3'
  722. ),
  723. 'FISHER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  724. 'functionCall' => 'PHPExcel_Calculation_Statistical::FISHER',
  725. 'argumentCount' => '1'
  726. ),
  727. 'FISHERINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  728. 'functionCall' => 'PHPExcel_Calculation_Statistical::FISHERINV',
  729. 'argumentCount' => '1'
  730. ),
  731. 'FIXED' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  732. 'functionCall' => 'PHPExcel_Calculation_TextData::FIXEDFORMAT',
  733. 'argumentCount' => '1-3'
  734. ),
  735. 'FLOOR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  736. 'functionCall' => 'PHPExcel_Calculation_MathTrig::FLOOR',
  737. 'argumentCount' => '2'
  738. ),
  739. 'FORECAST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  740. 'functionCall' => 'PHPExcel_Calculation_Statistical::FORECAST',
  741. 'argumentCount' => '3'
  742. ),
  743. 'FREQUENCY' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  744. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  745. 'argumentCount' => '2'
  746. ),
  747. 'FTEST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  748. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  749. 'argumentCount' => '2'
  750. ),
  751. 'FV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  752. 'functionCall' => 'PHPExcel_Calculation_Financial::FV',
  753. 'argumentCount' => '3-5'
  754. ),
  755. 'FVSCHEDULE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  756. 'functionCall' => 'PHPExcel_Calculation_Financial::FVSCHEDULE',
  757. 'argumentCount' => '2'
  758. ),
  759. 'GAMMADIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  760. 'functionCall' => 'PHPExcel_Calculation_Statistical::GAMMADIST',
  761. 'argumentCount' => '4'
  762. ),
  763. 'GAMMAINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  764. 'functionCall' => 'PHPExcel_Calculation_Statistical::GAMMAINV',
  765. 'argumentCount' => '3'
  766. ),
  767. 'GAMMALN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  768. 'functionCall' => 'PHPExcel_Calculation_Statistical::GAMMALN',
  769. 'argumentCount' => '1'
  770. ),
  771. 'GCD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  772. 'functionCall' => 'PHPExcel_Calculation_MathTrig::GCD',
  773. 'argumentCount' => '1+'
  774. ),
  775. 'GEOMEAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  776. 'functionCall' => 'PHPExcel_Calculation_Statistical::GEOMEAN',
  777. 'argumentCount' => '1+'
  778. ),
  779. 'GESTEP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  780. 'functionCall' => 'PHPExcel_Calculation_Engineering::GESTEP',
  781. 'argumentCount' => '1,2'
  782. ),
  783. 'GETPIVOTDATA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  784. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  785. 'argumentCount' => '2+'
  786. ),
  787. 'GROWTH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  788. 'functionCall' => 'PHPExcel_Calculation_Statistical::GROWTH',
  789. 'argumentCount' => '1-4'
  790. ),
  791. 'HARMEAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  792. 'functionCall' => 'PHPExcel_Calculation_Statistical::HARMEAN',
  793. 'argumentCount' => '1+'
  794. ),
  795. 'HEX2BIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  796. 'functionCall' => 'PHPExcel_Calculation_Engineering::HEXTOBIN',
  797. 'argumentCount' => '1,2'
  798. ),
  799. 'HEX2DEC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  800. 'functionCall' => 'PHPExcel_Calculation_Engineering::HEXTODEC',
  801. 'argumentCount' => '1'
  802. ),
  803. 'HEX2OCT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  804. 'functionCall' => 'PHPExcel_Calculation_Engineering::HEXTOOCT',
  805. 'argumentCount' => '1,2'
  806. ),
  807. 'HLOOKUP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  808. 'functionCall' => 'PHPExcel_Calculation_LookupRef::HLOOKUP',
  809. 'argumentCount' => '3,4'
  810. ),
  811. 'HOUR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  812. 'functionCall' => 'PHPExcel_Calculation_DateTime::HOUROFDAY',
  813. 'argumentCount' => '1'
  814. ),
  815. 'HYPERLINK' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  816. 'functionCall' => 'PHPExcel_Calculation_LookupRef::HYPERLINK',
  817. 'argumentCount' => '1,2',
  818. 'passCellReference'=> TRUE
  819. ),
  820. 'HYPGEOMDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  821. 'functionCall' => 'PHPExcel_Calculation_Statistical::HYPGEOMDIST',
  822. 'argumentCount' => '4'
  823. ),
  824. 'IF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOGICAL,
  825. 'functionCall' => 'PHPExcel_Calculation_Logical::STATEMENT_IF',
  826. 'argumentCount' => '1-3'
  827. ),
  828. 'IFERROR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOGICAL,
  829. 'functionCall' => 'PHPExcel_Calculation_Logical::IFERROR',
  830. 'argumentCount' => '2'
  831. ),
  832. 'IMABS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  833. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMABS',
  834. 'argumentCount' => '1'
  835. ),
  836. 'IMAGINARY' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  837. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMAGINARY',
  838. 'argumentCount' => '1'
  839. ),
  840. 'IMARGUMENT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  841. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMARGUMENT',
  842. 'argumentCount' => '1'
  843. ),
  844. 'IMCONJUGATE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  845. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMCONJUGATE',
  846. 'argumentCount' => '1'
  847. ),
  848. 'IMCOS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  849. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMCOS',
  850. 'argumentCount' => '1'
  851. ),
  852. 'IMDIV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  853. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMDIV',
  854. 'argumentCount' => '2'
  855. ),
  856. 'IMEXP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  857. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMEXP',
  858. 'argumentCount' => '1'
  859. ),
  860. 'IMLN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  861. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMLN',
  862. 'argumentCount' => '1'
  863. ),
  864. 'IMLOG10' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  865. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMLOG10',
  866. 'argumentCount' => '1'
  867. ),
  868. 'IMLOG2' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  869. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMLOG2',
  870. 'argumentCount' => '1'
  871. ),
  872. 'IMPOWER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  873. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMPOWER',
  874. 'argumentCount' => '2'
  875. ),
  876. 'IMPRODUCT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  877. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMPRODUCT',
  878. 'argumentCount' => '1+'
  879. ),
  880. 'IMREAL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  881. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMREAL',
  882. 'argumentCount' => '1'
  883. ),
  884. 'IMSIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  885. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMSIN',
  886. 'argumentCount' => '1'
  887. ),
  888. 'IMSQRT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  889. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMSQRT',
  890. 'argumentCount' => '1'
  891. ),
  892. 'IMSUB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  893. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMSUB',
  894. 'argumentCount' => '2'
  895. ),
  896. 'IMSUM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  897. 'functionCall' => 'PHPExcel_Calculation_Engineering::IMSUM',
  898. 'argumentCount' => '1+'
  899. ),
  900. 'INDEX' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  901. 'functionCall' => 'PHPExcel_Calculation_LookupRef::INDEX',
  902. 'argumentCount' => '1-4'
  903. ),
  904. 'INDIRECT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  905. 'functionCall' => 'PHPExcel_Calculation_LookupRef::INDIRECT',
  906. 'argumentCount' => '1,2',
  907. 'passCellReference'=> TRUE
  908. ),
  909. 'INFO' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  910. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  911. 'argumentCount' => '1'
  912. ),
  913. 'INT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  914. 'functionCall' => 'PHPExcel_Calculation_MathTrig::INT',
  915. 'argumentCount' => '1'
  916. ),
  917. 'INTERCEPT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  918. 'functionCall' => 'PHPExcel_Calculation_Statistical::INTERCEPT',
  919. 'argumentCount' => '2'
  920. ),
  921. 'INTRATE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  922. 'functionCall' => 'PHPExcel_Calculation_Financial::INTRATE',
  923. 'argumentCount' => '4,5'
  924. ),
  925. 'IPMT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  926. 'functionCall' => 'PHPExcel_Calculation_Financial::IPMT',
  927. 'argumentCount' => '4-6'
  928. ),
  929. 'IRR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  930. 'functionCall' => 'PHPExcel_Calculation_Financial::IRR',
  931. 'argumentCount' => '1,2'
  932. ),
  933. 'ISBLANK' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  934. 'functionCall' => 'PHPExcel_Calculation_Functions::IS_BLANK',
  935. 'argumentCount' => '1'
  936. ),
  937. 'ISERR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  938. 'functionCall' => 'PHPExcel_Calculation_Functions::IS_ERR',
  939. 'argumentCount' => '1'
  940. ),
  941. 'ISERROR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  942. 'functionCall' => 'PHPExcel_Calculation_Functions::IS_ERROR',
  943. 'argumentCount' => '1'
  944. ),
  945. 'ISEVEN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  946. 'functionCall' => 'PHPExcel_Calculation_Functions::IS_EVEN',
  947. 'argumentCount' => '1'
  948. ),
  949. 'ISLOGICAL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  950. 'functionCall' => 'PHPExcel_Calculation_Functions::IS_LOGICAL',
  951. 'argumentCount' => '1'
  952. ),
  953. 'ISNA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  954. 'functionCall' => 'PHPExcel_Calculation_Functions::IS_NA',
  955. 'argumentCount' => '1'
  956. ),
  957. 'ISNONTEXT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  958. 'functionCall' => 'PHPExcel_Calculation_Functions::IS_NONTEXT',
  959. 'argumentCount' => '1'
  960. ),
  961. 'ISNUMBER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  962. 'functionCall' => 'PHPExcel_Calculation_Functions::IS_NUMBER',
  963. 'argumentCount' => '1'
  964. ),
  965. 'ISODD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  966. 'functionCall' => 'PHPExcel_Calculation_Functions::IS_ODD',
  967. 'argumentCount' => '1'
  968. ),
  969. 'ISPMT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  970. 'functionCall' => 'PHPExcel_Calculation_Financial::ISPMT',
  971. 'argumentCount' => '4'
  972. ),
  973. 'ISREF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  974. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  975. 'argumentCount' => '1'
  976. ),
  977. 'ISTEXT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  978. 'functionCall' => 'PHPExcel_Calculation_Functions::IS_TEXT',
  979. 'argumentCount' => '1'
  980. ),
  981. 'JIS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  982. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  983. 'argumentCount' => '1'
  984. ),
  985. 'KURT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  986. 'functionCall' => 'PHPExcel_Calculation_Statistical::KURT',
  987. 'argumentCount' => '1+'
  988. ),
  989. 'LARGE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  990. 'functionCall' => 'PHPExcel_Calculation_Statistical::LARGE',
  991. 'argumentCount' => '2'
  992. ),
  993. 'LCM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  994. 'functionCall' => 'PHPExcel_Calculation_MathTrig::LCM',
  995. 'argumentCount' => '1+'
  996. ),
  997. 'LEFT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  998. 'functionCall' => 'PHPExcel_Calculation_TextData::LEFT',
  999. 'argumentCount' => '1,2'
  1000. ),
  1001. 'LEFTB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1002. 'functionCall' => 'PHPExcel_Calculation_TextData::LEFT',
  1003. 'argumentCount' => '1,2'
  1004. ),
  1005. 'LEN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1006. 'functionCall' => 'PHPExcel_Calculation_TextData::STRINGLENGTH',
  1007. 'argumentCount' => '1'
  1008. ),
  1009. 'LENB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1010. 'functionCall' => 'PHPExcel_Calculation_TextData::STRINGLENGTH',
  1011. 'argumentCount' => '1'
  1012. ),
  1013. 'LINEST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1014. 'functionCall' => 'PHPExcel_Calculation_Statistical::LINEST',
  1015. 'argumentCount' => '1-4'
  1016. ),
  1017. 'LN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1018. 'functionCall' => 'log',
  1019. 'argumentCount' => '1'
  1020. ),
  1021. 'LOG' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1022. 'functionCall' => 'PHPExcel_Calculation_MathTrig::LOG_BASE',
  1023. 'argumentCount' => '1,2'
  1024. ),
  1025. 'LOG10' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1026. 'functionCall' => 'log10',
  1027. 'argumentCount' => '1'
  1028. ),
  1029. 'LOGEST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1030. 'functionCall' => 'PHPExcel_Calculation_Statistical::LOGEST',
  1031. 'argumentCount' => '1-4'
  1032. ),
  1033. 'LOGINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1034. 'functionCall' => 'PHPExcel_Calculation_Statistical::LOGINV',
  1035. 'argumentCount' => '3'
  1036. ),
  1037. 'LOGNORMDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1038. 'functionCall' => 'PHPExcel_Calculation_Statistical::LOGNORMDIST',
  1039. 'argumentCount' => '3'
  1040. ),
  1041. 'LOOKUP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  1042. 'functionCall' => 'PHPExcel_Calculation_LookupRef::LOOKUP',
  1043. 'argumentCount' => '2,3'
  1044. ),
  1045. 'LOWER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1046. 'functionCall' => 'PHPExcel_Calculation_TextData::LOWERCASE',
  1047. 'argumentCount' => '1'
  1048. ),
  1049. 'MATCH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  1050. 'functionCall' => 'PHPExcel_Calculation_LookupRef::MATCH',
  1051. 'argumentCount' => '2,3'
  1052. ),
  1053. 'MAX' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1054. 'functionCall' => 'PHPExcel_Calculation_Statistical::MAX',
  1055. 'argumentCount' => '1+'
  1056. ),
  1057. 'MAXA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1058. 'functionCall' => 'PHPExcel_Calculation_Statistical::MAXA',
  1059. 'argumentCount' => '1+'
  1060. ),
  1061. 'MAXIF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1062. 'functionCall' => 'PHPExcel_Calculation_Statistical::MAXIF',
  1063. 'argumentCount' => '2+'
  1064. ),
  1065. 'MDETERM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1066. 'functionCall' => 'PHPExcel_Calculation_MathTrig::MDETERM',
  1067. 'argumentCount' => '1'
  1068. ),
  1069. 'MDURATION' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1070. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  1071. 'argumentCount' => '5,6'
  1072. ),
  1073. 'MEDIAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1074. 'functionCall' => 'PHPExcel_Calculation_Statistical::MEDIAN',
  1075. 'argumentCount' => '1+'
  1076. ),
  1077. 'MEDIANIF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1078. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  1079. 'argumentCount' => '2+'
  1080. ),
  1081. 'MID' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1082. 'functionCall' => 'PHPExcel_Calculation_TextData::MID',
  1083. 'argumentCount' => '3'
  1084. ),
  1085. 'MIDB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1086. 'functionCall' => 'PHPExcel_Calculation_TextData::MID',
  1087. 'argumentCount' => '3'
  1088. ),
  1089. 'MIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1090. 'functionCall' => 'PHPExcel_Calculation_Statistical::MIN',
  1091. 'argumentCount' => '1+'
  1092. ),
  1093. 'MINA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1094. 'functionCall' => 'PHPExcel_Calculation_Statistical::MINA',
  1095. 'argumentCount' => '1+'
  1096. ),
  1097. 'MINIF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1098. 'functionCall' => 'PHPExcel_Calculation_Statistical::MINIF',
  1099. 'argumentCount' => '2+'
  1100. ),
  1101. 'MINUTE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  1102. 'functionCall' => 'PHPExcel_Calculation_DateTime::MINUTEOFHOUR',
  1103. 'argumentCount' => '1'
  1104. ),
  1105. 'MINVERSE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1106. 'functionCall' => 'PHPExcel_Calculation_MathTrig::MINVERSE',
  1107. 'argumentCount' => '1'
  1108. ),
  1109. 'MIRR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1110. 'functionCall' => 'PHPExcel_Calculation_Financial::MIRR',
  1111. 'argumentCount' => '3'
  1112. ),
  1113. 'MMULT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1114. 'functionCall' => 'PHPExcel_Calculation_MathTrig::MMULT',
  1115. 'argumentCount' => '2'
  1116. ),
  1117. 'MOD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1118. 'functionCall' => 'PHPExcel_Calculation_MathTrig::MOD',
  1119. 'argumentCount' => '2'
  1120. ),
  1121. 'MODE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1122. 'functionCall' => 'PHPExcel_Calculation_Statistical::MODE',
  1123. 'argumentCount' => '1+'
  1124. ),
  1125. 'MONTH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  1126. 'functionCall' => 'PHPExcel_Calculation_DateTime::MONTHOFYEAR',
  1127. 'argumentCount' => '1'
  1128. ),
  1129. 'MROUND' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1130. 'functionCall' => 'PHPExcel_Calculation_MathTrig::MROUND',
  1131. 'argumentCount' => '2'
  1132. ),
  1133. 'MULTINOMIAL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1134. 'functionCall' => 'PHPExcel_Calculation_MathTrig::MULTINOMIAL',
  1135. 'argumentCount' => '1+'
  1136. ),
  1137. 'N' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  1138. 'functionCall' => 'PHPExcel_Calculation_Functions::N',
  1139. 'argumentCount' => '1'
  1140. ),
  1141. 'NA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  1142. 'functionCall' => 'PHPExcel_Calculation_Functions::NA',
  1143. 'argumentCount' => '0'
  1144. ),
  1145. 'NEGBINOMDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1146. 'functionCall' => 'PHPExcel_Calculation_Statistical::NEGBINOMDIST',
  1147. 'argumentCount' => '3'
  1148. ),
  1149. 'NETWORKDAYS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  1150. 'functionCall' => 'PHPExcel_Calculation_DateTime::NETWORKDAYS',
  1151. 'argumentCount' => '2+'
  1152. ),
  1153. 'NOMINAL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1154. 'functionCall' => 'PHPExcel_Calculation_Financial::NOMINAL',
  1155. 'argumentCount' => '2'
  1156. ),
  1157. 'NORMDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1158. 'functionCall' => 'PHPExcel_Calculation_Statistical::NORMDIST',
  1159. 'argumentCount' => '4'
  1160. ),
  1161. 'NORMINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1162. 'functionCall' => 'PHPExcel_Calculation_Statistical::NORMINV',
  1163. 'argumentCount' => '3'
  1164. ),
  1165. 'NORMSDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1166. 'functionCall' => 'PHPExcel_Calculation_Statistical::NORMSDIST',
  1167. 'argumentCount' => '1'
  1168. ),
  1169. 'NORMSINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1170. 'functionCall' => 'PHPExcel_Calculation_Statistical::NORMSINV',
  1171. 'argumentCount' => '1'
  1172. ),
  1173. 'NOT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOGICAL,
  1174. 'functionCall' => 'PHPExcel_Calculation_Logical::NOT',
  1175. 'argumentCount' => '1'
  1176. ),
  1177. 'NOW' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  1178. 'functionCall' => 'PHPExcel_Calculation_DateTime::DATETIMENOW',
  1179. 'argumentCount' => '0'
  1180. ),
  1181. 'NPER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1182. 'functionCall' => 'PHPExcel_Calculation_Financial::NPER',
  1183. 'argumentCount' => '3-5'
  1184. ),
  1185. 'NPV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1186. 'functionCall' => 'PHPExcel_Calculation_Financial::NPV',
  1187. 'argumentCount' => '2+'
  1188. ),
  1189. 'OCT2BIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  1190. 'functionCall' => 'PHPExcel_Calculation_Engineering::OCTTOBIN',
  1191. 'argumentCount' => '1,2'
  1192. ),
  1193. 'OCT2DEC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  1194. 'functionCall' => 'PHPExcel_Calculation_Engineering::OCTTODEC',
  1195. 'argumentCount' => '1'
  1196. ),
  1197. 'OCT2HEX' => array('category' => PHPExcel_Calculation_Function::CATEGORY_ENGINEERING,
  1198. 'functionCall' => 'PHPExcel_Calculation_Engineering::OCTTOHEX',
  1199. 'argumentCount' => '1,2'
  1200. ),
  1201. 'ODD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1202. 'functionCall' => 'PHPExcel_Calculation_MathTrig::ODD',
  1203. 'argumentCount' => '1'
  1204. ),
  1205. 'ODDFPRICE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1206. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  1207. 'argumentCount' => '8,9'
  1208. ),
  1209. 'ODDFYIELD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1210. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  1211. 'argumentCount' => '8,9'
  1212. ),
  1213. 'ODDLPRICE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1214. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  1215. 'argumentCount' => '7,8'
  1216. ),
  1217. 'ODDLYIELD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1218. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  1219. 'argumentCount' => '7,8'
  1220. ),
  1221. 'OFFSET' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  1222. 'functionCall' => 'PHPExcel_Calculation_LookupRef::OFFSET',
  1223. 'argumentCount' => '3,5',
  1224. 'passCellReference'=> TRUE,
  1225. 'passByReference' => array(TRUE)
  1226. ),
  1227. 'OR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOGICAL,
  1228. 'functionCall' => 'PHPExcel_Calculation_Logical::LOGICAL_OR',
  1229. 'argumentCount' => '1+'
  1230. ),
  1231. 'PEARSON' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1232. 'functionCall' => 'PHPExcel_Calculation_Statistical::CORREL',
  1233. 'argumentCount' => '2'
  1234. ),
  1235. 'PERCENTILE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1236. 'functionCall' => 'PHPExcel_Calculation_Statistical::PERCENTILE',
  1237. 'argumentCount' => '2'
  1238. ),
  1239. 'PERCENTRANK' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1240. 'functionCall' => 'PHPExcel_Calculation_Statistical::PERCENTRANK',
  1241. 'argumentCount' => '2,3'
  1242. ),
  1243. 'PERMUT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1244. 'functionCall' => 'PHPExcel_Calculation_Statistical::PERMUT',
  1245. 'argumentCount' => '2'
  1246. ),
  1247. 'PHONETIC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1248. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  1249. 'argumentCount' => '1'
  1250. ),
  1251. 'PI' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1252. 'functionCall' => 'pi',
  1253. 'argumentCount' => '0'
  1254. ),
  1255. 'PMT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1256. 'functionCall' => 'PHPExcel_Calculation_Financial::PMT',
  1257. 'argumentCount' => '3-5'
  1258. ),
  1259. 'POISSON' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1260. 'functionCall' => 'PHPExcel_Calculation_Statistical::POISSON',
  1261. 'argumentCount' => '3'
  1262. ),
  1263. 'POWER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1264. 'functionCall' => 'PHPExcel_Calculation_MathTrig::POWER',
  1265. 'argumentCount' => '2'
  1266. ),
  1267. 'PPMT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1268. 'functionCall' => 'PHPExcel_Calculation_Financial::PPMT',
  1269. 'argumentCount' => '4-6'
  1270. ),
  1271. 'PRICE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1272. 'functionCall' => 'PHPExcel_Calculation_Financial::PRICE',
  1273. 'argumentCount' => '6,7'
  1274. ),
  1275. 'PRICEDISC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1276. 'functionCall' => 'PHPExcel_Calculation_Financial::PRICEDISC',
  1277. 'argumentCount' => '4,5'
  1278. ),
  1279. 'PRICEMAT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1280. 'functionCall' => 'PHPExcel_Calculation_Financial::PRICEMAT',
  1281. 'argumentCount' => '5,6'
  1282. ),
  1283. 'PROB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1284. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  1285. 'argumentCount' => '3,4'
  1286. ),
  1287. 'PRODUCT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1288. 'functionCall' => 'PHPExcel_Calculation_MathTrig::PRODUCT',
  1289. 'argumentCount' => '1+'
  1290. ),
  1291. 'PROPER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1292. 'functionCall' => 'PHPExcel_Calculation_TextData::PROPERCASE',
  1293. 'argumentCount' => '1'
  1294. ),
  1295. 'PV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1296. 'functionCall' => 'PHPExcel_Calculation_Financial::PV',
  1297. 'argumentCount' => '3-5'
  1298. ),
  1299. 'QUARTILE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1300. 'functionCall' => 'PHPExcel_Calculation_Statistical::QUARTILE',
  1301. 'argumentCount' => '2'
  1302. ),
  1303. 'QUOTIENT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1304. 'functionCall' => 'PHPExcel_Calculation_MathTrig::QUOTIENT',
  1305. 'argumentCount' => '2'
  1306. ),
  1307. 'RADIANS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1308. 'functionCall' => 'deg2rad',
  1309. 'argumentCount' => '1'
  1310. ),
  1311. 'RAND' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1312. 'functionCall' => 'PHPExcel_Calculation_MathTrig::RAND',
  1313. 'argumentCount' => '0'
  1314. ),
  1315. 'RANDBETWEEN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1316. 'functionCall' => 'PHPExcel_Calculation_MathTrig::RAND',
  1317. 'argumentCount' => '2'
  1318. ),
  1319. 'RANK' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1320. 'functionCall' => 'PHPExcel_Calculation_Statistical::RANK',
  1321. 'argumentCount' => '2,3'
  1322. ),
  1323. 'RATE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1324. 'functionCall' => 'PHPExcel_Calculation_Financial::RATE',
  1325. 'argumentCount' => '3-6'
  1326. ),
  1327. 'RECEIVED' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1328. 'functionCall' => 'PHPExcel_Calculation_Financial::RECEIVED',
  1329. 'argumentCount' => '4-5'
  1330. ),
  1331. 'REPLACE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1332. 'functionCall' => 'PHPExcel_Calculation_TextData::REPLACE',
  1333. 'argumentCount' => '4'
  1334. ),
  1335. 'REPLACEB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1336. 'functionCall' => 'PHPExcel_Calculation_TextData::REPLACE',
  1337. 'argumentCount' => '4'
  1338. ),
  1339. 'REPT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1340. 'functionCall' => 'str_repeat',
  1341. 'argumentCount' => '2'
  1342. ),
  1343. 'RIGHT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1344. 'functionCall' => 'PHPExcel_Calculation_TextData::RIGHT',
  1345. 'argumentCount' => '1,2'
  1346. ),
  1347. 'RIGHTB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1348. 'functionCall' => 'PHPExcel_Calculation_TextData::RIGHT',
  1349. 'argumentCount' => '1,2'
  1350. ),
  1351. 'ROMAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1352. 'functionCall' => 'PHPExcel_Calculation_MathTrig::ROMAN',
  1353. 'argumentCount' => '1,2'
  1354. ),
  1355. 'ROUND' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1356. 'functionCall' => 'round',
  1357. 'argumentCount' => '2'
  1358. ),
  1359. 'ROUNDDOWN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1360. 'functionCall' => 'PHPExcel_Calculation_MathTrig::ROUNDDOWN',
  1361. 'argumentCount' => '2'
  1362. ),
  1363. 'ROUNDUP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1364. 'functionCall' => 'PHPExcel_Calculation_MathTrig::ROUNDUP',
  1365. 'argumentCount' => '2'
  1366. ),
  1367. 'ROW' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  1368. 'functionCall' => 'PHPExcel_Calculation_LookupRef::ROW',
  1369. 'argumentCount' => '-1',
  1370. 'passByReference' => array(TRUE)
  1371. ),
  1372. 'ROWS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  1373. 'functionCall' => 'PHPExcel_Calculation_LookupRef::ROWS',
  1374. 'argumentCount' => '1'
  1375. ),
  1376. 'RSQ' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1377. 'functionCall' => 'PHPExcel_Calculation_Statistical::RSQ',
  1378. 'argumentCount' => '2'
  1379. ),
  1380. 'RTD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  1381. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  1382. 'argumentCount' => '1+'
  1383. ),
  1384. 'SEARCH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1385. 'functionCall' => 'PHPExcel_Calculation_TextData::SEARCHINSENSITIVE',
  1386. 'argumentCount' => '2,3'
  1387. ),
  1388. 'SEARCHB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1389. 'functionCall' => 'PHPExcel_Calculation_TextData::SEARCHINSENSITIVE',
  1390. 'argumentCount' => '2,3'
  1391. ),
  1392. 'SECOND' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  1393. 'functionCall' => 'PHPExcel_Calculation_DateTime::SECONDOFMINUTE',
  1394. 'argumentCount' => '1'
  1395. ),
  1396. 'SERIESSUM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1397. 'functionCall' => 'PHPExcel_Calculation_MathTrig::SERIESSUM',
  1398. 'argumentCount' => '4'
  1399. ),
  1400. 'SIGN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1401. 'functionCall' => 'PHPExcel_Calculation_MathTrig::SIGN',
  1402. 'argumentCount' => '1'
  1403. ),
  1404. 'SIN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1405. 'functionCall' => 'sin',
  1406. 'argumentCount' => '1'
  1407. ),
  1408. 'SINH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1409. 'functionCall' => 'sinh',
  1410. 'argumentCount' => '1'
  1411. ),
  1412. 'SKEW' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1413. 'functionCall' => 'PHPExcel_Calculation_Statistical::SKEW',
  1414. 'argumentCount' => '1+'
  1415. ),
  1416. 'SLN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1417. 'functionCall' => 'PHPExcel_Calculation_Financial::SLN',
  1418. 'argumentCount' => '3'
  1419. ),
  1420. 'SLOPE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1421. 'functionCall' => 'PHPExcel_Calculation_Statistical::SLOPE',
  1422. 'argumentCount' => '2'
  1423. ),
  1424. 'SMALL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1425. 'functionCall' => 'PHPExcel_Calculation_Statistical::SMALL',
  1426. 'argumentCount' => '2'
  1427. ),
  1428. 'SQRT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1429. 'functionCall' => 'sqrt',
  1430. 'argumentCount' => '1'
  1431. ),
  1432. 'SQRTPI' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1433. 'functionCall' => 'PHPExcel_Calculation_MathTrig::SQRTPI',
  1434. 'argumentCount' => '1'
  1435. ),
  1436. 'STANDARDIZE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1437. 'functionCall' => 'PHPExcel_Calculation_Statistical::STANDARDIZE',
  1438. 'argumentCount' => '3'
  1439. ),
  1440. 'STDEV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1441. 'functionCall' => 'PHPExcel_Calculation_Statistical::STDEV',
  1442. 'argumentCount' => '1+'
  1443. ),
  1444. 'STDEVA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1445. 'functionCall' => 'PHPExcel_Calculation_Statistical::STDEVA',
  1446. 'argumentCount' => '1+'
  1447. ),
  1448. 'STDEVP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1449. 'functionCall' => 'PHPExcel_Calculation_Statistical::STDEVP',
  1450. 'argumentCount' => '1+'
  1451. ),
  1452. 'STDEVPA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1453. 'functionCall' => 'PHPExcel_Calculation_Statistical::STDEVPA',
  1454. 'argumentCount' => '1+'
  1455. ),
  1456. 'STEYX' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1457. 'functionCall' => 'PHPExcel_Calculation_Statistical::STEYX',
  1458. 'argumentCount' => '2'
  1459. ),
  1460. 'SUBSTITUTE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1461. 'functionCall' => 'PHPExcel_Calculation_TextData::SUBSTITUTE',
  1462. 'argumentCount' => '3,4'
  1463. ),
  1464. 'SUBTOTAL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1465. 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUBTOTAL',
  1466. 'argumentCount' => '2+'
  1467. ),
  1468. 'SUM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1469. 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUM',
  1470. 'argumentCount' => '1+'
  1471. ),
  1472. 'SUMIF' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1473. 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUMIF',
  1474. 'argumentCount' => '2,3'
  1475. ),
  1476. 'SUMIFS' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1477. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  1478. 'argumentCount' => '?'
  1479. ),
  1480. 'SUMPRODUCT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1481. 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUMPRODUCT',
  1482. 'argumentCount' => '1+'
  1483. ),
  1484. 'SUMSQ' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1485. 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUMSQ',
  1486. 'argumentCount' => '1+'
  1487. ),
  1488. 'SUMX2MY2' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1489. 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUMX2MY2',
  1490. 'argumentCount' => '2'
  1491. ),
  1492. 'SUMX2PY2' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1493. 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUMX2PY2',
  1494. 'argumentCount' => '2'
  1495. ),
  1496. 'SUMXMY2' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1497. 'functionCall' => 'PHPExcel_Calculation_MathTrig::SUMXMY2',
  1498. 'argumentCount' => '2'
  1499. ),
  1500. 'SYD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1501. 'functionCall' => 'PHPExcel_Calculation_Financial::SYD',
  1502. 'argumentCount' => '4'
  1503. ),
  1504. 'T' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1505. 'functionCall' => 'PHPExcel_Calculation_TextData::RETURNSTRING',
  1506. 'argumentCount' => '1'
  1507. ),
  1508. 'TAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1509. 'functionCall' => 'tan',
  1510. 'argumentCount' => '1'
  1511. ),
  1512. 'TANH' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1513. 'functionCall' => 'tanh',
  1514. 'argumentCount' => '1'
  1515. ),
  1516. 'TBILLEQ' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1517. 'functionCall' => 'PHPExcel_Calculation_Financial::TBILLEQ',
  1518. 'argumentCount' => '3'
  1519. ),
  1520. 'TBILLPRICE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1521. 'functionCall' => 'PHPExcel_Calculation_Financial::TBILLPRICE',
  1522. 'argumentCount' => '3'
  1523. ),
  1524. 'TBILLYIELD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1525. 'functionCall' => 'PHPExcel_Calculation_Financial::TBILLYIELD',
  1526. 'argumentCount' => '3'
  1527. ),
  1528. 'TDIST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1529. 'functionCall' => 'PHPExcel_Calculation_Statistical::TDIST',
  1530. 'argumentCount' => '3'
  1531. ),
  1532. 'TEXT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1533. 'functionCall' => 'PHPExcel_Calculation_TextData::TEXTFORMAT',
  1534. 'argumentCount' => '2'
  1535. ),
  1536. 'TIME' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  1537. 'functionCall' => 'PHPExcel_Calculation_DateTime::TIME',
  1538. 'argumentCount' => '3'
  1539. ),
  1540. 'TIMEVALUE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  1541. 'functionCall' => 'PHPExcel_Calculation_DateTime::TIMEVALUE',
  1542. 'argumentCount' => '1'
  1543. ),
  1544. 'TINV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1545. 'functionCall' => 'PHPExcel_Calculation_Statistical::TINV',
  1546. 'argumentCount' => '2'
  1547. ),
  1548. 'TODAY' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  1549. 'functionCall' => 'PHPExcel_Calculation_DateTime::DATENOW',
  1550. 'argumentCount' => '0'
  1551. ),
  1552. 'TRANSPOSE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  1553. 'functionCall' => 'PHPExcel_Calculation_LookupRef::TRANSPOSE',
  1554. 'argumentCount' => '1'
  1555. ),
  1556. 'TREND' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1557. 'functionCall' => 'PHPExcel_Calculation_Statistical::TREND',
  1558. 'argumentCount' => '1-4'
  1559. ),
  1560. 'TRIM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1561. 'functionCall' => 'PHPExcel_Calculation_TextData::TRIMSPACES',
  1562. 'argumentCount' => '1'
  1563. ),
  1564. 'TRIMMEAN' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1565. 'functionCall' => 'PHPExcel_Calculation_Statistical::TRIMMEAN',
  1566. 'argumentCount' => '2'
  1567. ),
  1568. 'TRUE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOGICAL,
  1569. 'functionCall' => 'PHPExcel_Calculation_Logical::TRUE',
  1570. 'argumentCount' => '0'
  1571. ),
  1572. 'TRUNC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_MATH_AND_TRIG,
  1573. 'functionCall' => 'PHPExcel_Calculation_MathTrig::TRUNC',
  1574. 'argumentCount' => '1,2'
  1575. ),
  1576. 'TTEST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1577. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  1578. 'argumentCount' => '4'
  1579. ),
  1580. 'TYPE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  1581. 'functionCall' => 'PHPExcel_Calculation_Functions::TYPE',
  1582. 'argumentCount' => '1'
  1583. ),
  1584. 'UPPER' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1585. 'functionCall' => 'PHPExcel_Calculation_TextData::UPPERCASE',
  1586. 'argumentCount' => '1'
  1587. ),
  1588. 'USDOLLAR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1589. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  1590. 'argumentCount' => '2'
  1591. ),
  1592. 'VALUE' => array('category' => PHPExcel_Calculation_Function::CATEGORY_TEXT_AND_DATA,
  1593. 'functionCall' => 'PHPExcel_Calculation_TextData::VALUE',
  1594. 'argumentCount' => '1'
  1595. ),
  1596. 'VAR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1597. 'functionCall' => 'PHPExcel_Calculation_Statistical::VARFunc',
  1598. 'argumentCount' => '1+'
  1599. ),
  1600. 'VARA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1601. 'functionCall' => 'PHPExcel_Calculation_Statistical::VARA',
  1602. 'argumentCount' => '1+'
  1603. ),
  1604. 'VARP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1605. 'functionCall' => 'PHPExcel_Calculation_Statistical::VARP',
  1606. 'argumentCount' => '1+'
  1607. ),
  1608. 'VARPA' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1609. 'functionCall' => 'PHPExcel_Calculation_Statistical::VARPA',
  1610. 'argumentCount' => '1+'
  1611. ),
  1612. 'VDB' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1613. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  1614. 'argumentCount' => '5-7'
  1615. ),
  1616. 'VERSION' => array('category' => PHPExcel_Calculation_Function::CATEGORY_INFORMATION,
  1617. 'functionCall' => 'PHPExcel_Calculation_Functions::VERSION',
  1618. 'argumentCount' => '0'
  1619. ),
  1620. 'VLOOKUP' => array('category' => PHPExcel_Calculation_Function::CATEGORY_LOOKUP_AND_REFERENCE,
  1621. 'functionCall' => 'PHPExcel_Calculation_LookupRef::VLOOKUP',
  1622. 'argumentCount' => '3,4'
  1623. ),
  1624. 'WEEKDAY' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  1625. 'functionCall' => 'PHPExcel_Calculation_DateTime::DAYOFWEEK',
  1626. 'argumentCount' => '1,2'
  1627. ),
  1628. 'WEEKNUM' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  1629. 'functionCall' => 'PHPExcel_Calculation_DateTime::WEEKOFYEAR',
  1630. 'argumentCount' => '1,2'
  1631. ),
  1632. 'WEIBULL' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1633. 'functionCall' => 'PHPExcel_Calculation_Statistical::WEIBULL',
  1634. 'argumentCount' => '4'
  1635. ),
  1636. 'WORKDAY' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  1637. 'functionCall' => 'PHPExcel_Calculation_DateTime::WORKDAY',
  1638. 'argumentCount' => '2+'
  1639. ),
  1640. 'XIRR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1641. 'functionCall' => 'PHPExcel_Calculation_Financial::XIRR',
  1642. 'argumentCount' => '2,3'
  1643. ),
  1644. 'XNPV' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1645. 'functionCall' => 'PHPExcel_Calculation_Financial::XNPV',
  1646. 'argumentCount' => '3'
  1647. ),
  1648. 'YEAR' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  1649. 'functionCall' => 'PHPExcel_Calculation_DateTime::YEAR',
  1650. 'argumentCount' => '1'
  1651. ),
  1652. 'YEARFRAC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_DATE_AND_TIME,
  1653. 'functionCall' => 'PHPExcel_Calculation_DateTime::YEARFRAC',
  1654. 'argumentCount' => '2,3'
  1655. ),
  1656. 'YIELD' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1657. 'functionCall' => 'PHPExcel_Calculation_Functions::DUMMY',
  1658. 'argumentCount' => '6,7'
  1659. ),
  1660. 'YIELDDISC' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1661. 'functionCall' => 'PHPExcel_Calculation_Financial::YIELDDISC',
  1662. 'argumentCount' => '4,5'
  1663. ),
  1664. 'YIELDMAT' => array('category' => PHPExcel_Calculation_Function::CATEGORY_FINANCIAL,
  1665. 'functionCall' => 'PHPExcel_Calculation_Financial::YIELDMAT',
  1666. 'argumentCount' => '5,6'
  1667. ),
  1668. 'ZTEST' => array('category' => PHPExcel_Calculation_Function::CATEGORY_STATISTICAL,
  1669. 'functionCall' => 'PHPExcel_Calculation_Statistical::ZTEST',
  1670. 'argumentCount' => '2-3'
  1671. )
  1672. );
  1673. // Internal functions used for special control purposes
  1674. private static $_controlFunctions = array(
  1675. 'MKMATRIX' => array('argumentCount' => '*',
  1676. 'functionCall' => 'self::_mkMatrix'
  1677. )
  1678. );
  1679. private function __construct(PHPExcel $workbook = NULL) {
  1680. $setPrecision = (PHP_INT_SIZE == 4) ? 14 : 16;
  1681. $this->_savedPrecision = ini_get('precision');
  1682. if ($this->_savedPrecision < $setPrecision) {
  1683. ini_set('precision',$setPrecision);
  1684. }
  1685. $this->delta = 1 * pow(10, -$setPrecision);
  1686. if ($workbook !== NULL) {
  1687. self::$_workbookSets[$workbook->getID()] = $this;
  1688. }
  1689. $this->_workbook = $workbook;
  1690. $this->_cyclicReferenceStack = new PHPExcel_CalcEngine_CyclicReferenceStack();
  1691. $this->_debugLog = new PHPExcel_CalcEngine_Logger($this->_cyclicReferenceStack);
  1692. } // function __construct()
  1693. public function __destruct() {
  1694. if ($this->_savedPrecision != ini_get('precision')) {
  1695. ini_set('precision',$this->_savedPrecision);
  1696. }
  1697. }
  1698. private static function _loadLocales() {
  1699. $localeFileDirectory = PHPEXCEL_ROOT.'PHPExcel/locale/';
  1700. foreach (glob($localeFileDirectory.'/*',GLOB_ONLYDIR) as $filename) {
  1701. $filename = substr($filename,strlen($localeFileDirectory)+1);
  1702. if ($filename != 'en') {
  1703. self::$_validLocaleLanguages[] = $filename;
  1704. }
  1705. }
  1706. }
  1707. /**
  1708. * Get an instance of this class
  1709. *
  1710. * @access public
  1711. * @param PHPExcel $workbook Injected workbook for working with a PHPExcel object,
  1712. * or NULL to create a standalone claculation engine
  1713. * @return PHPExcel_Calculation
  1714. */
  1715. public static function getInstance(PHPExcel $workbook = NULL) {
  1716. if ($workbook !== NULL) {
  1717. if (isset(self::$_workbookSets[$workbook->getID()])) {
  1718. return self::$_workbookSets[$workbook->getID()];
  1719. }
  1720. return new PHPExcel_Calculation($workbook);
  1721. }
  1722. if (!isset(self::$_instance) || (self::$_instance === NULL)) {
  1723. self::$_instance = new PHPExcel_Calculation();
  1724. }
  1725. return self::$_instance;
  1726. } // function getInstance()
  1727. /**
  1728. * Unset an instance of this class
  1729. *
  1730. * @access public
  1731. * @param PHPExcel $workbook Injected workbook identifying the instance to unset
  1732. */
  1733. public static function unsetInstance(PHPExcel $workbook = NULL) {
  1734. if ($workbook !== NULL) {
  1735. if (isset(self::$_workbookSets[$workbook->getID()])) {
  1736. unset(self::$_workbookSets[$workbook->getID()]);
  1737. }
  1738. }
  1739. }
  1740. /**
  1741. * Flush the calculation cache for any existing instance of this class
  1742. * but only if a PHPExcel_Calculation instance exists
  1743. *
  1744. * @access public
  1745. * @return null
  1746. */
  1747. public function flushInstance() {
  1748. $this->clearCalculationCache();
  1749. } // function flushInstance()
  1750. /**
  1751. * Get the debuglog for this claculation engine instance
  1752. *
  1753. * @access public
  1754. * @return PHPExcel_CalcEngine_Logger
  1755. */
  1756. public function getDebugLog() {
  1757. return $this->_debugLog;
  1758. }
  1759. /**
  1760. * __clone implementation. Cloning should not be allowed in a Singleton!
  1761. *
  1762. * @access public
  1763. * @throws PHPExcel_Calculation_Exception
  1764. */
  1765. public final function __clone() {
  1766. throw new PHPExcel_Calculation_Exception ('Cloning the calculation engine is not allowed!');
  1767. } // function __clone()
  1768. /**
  1769. * Return the locale-specific translation of TRUE
  1770. *
  1771. * @access public
  1772. * @return string locale-specific translation of TRUE
  1773. */
  1774. public static function getTRUE() {
  1775. return self::$_localeBoolean['TRUE'];
  1776. }
  1777. /**
  1778. * Return the locale-specific translation of FALSE
  1779. *
  1780. * @access public
  1781. * @return string locale-specific translation of FALSE
  1782. */
  1783. public static function getFALSE() {
  1784. return self::$_localeBoolean['FALSE'];
  1785. }
  1786. /**
  1787. * Set the Array Return Type (Array or Value of first element in the array)
  1788. *
  1789. * @access public
  1790. * @param string $returnType Array return type
  1791. * @return boolean Success or failure
  1792. */
  1793. public static function setArrayReturnType($returnType) {
  1794. if (($returnType == self::RETURN_ARRAY_AS_VALUE) ||
  1795. ($returnType == self::RETURN_ARRAY_AS_ERROR) ||
  1796. ($returnType == self::RETURN_ARRAY_AS_ARRAY)) {
  1797. self::$returnArrayAsType = $returnType;
  1798. return TRUE;
  1799. }
  1800. return FALSE;
  1801. } // function setArrayReturnType()
  1802. /**
  1803. * Return the Array Return Type (Array or Value of first element in the array)
  1804. *
  1805. * @access public
  1806. * @return string $returnType Array return type
  1807. */
  1808. public static function getArrayReturnType() {
  1809. return self::$returnArrayAsType;
  1810. } // function getArrayReturnType()
  1811. /**
  1812. * Is calculation caching enabled?
  1813. *
  1814. * @access public
  1815. * @return boolean
  1816. */
  1817. public function getCalculationCacheEnabled() {
  1818. return $this->_calculationCacheEnabled;
  1819. } // function getCalculationCacheEnabled()
  1820. /**
  1821. * Enable/disable calculation cache
  1822. *
  1823. * @access public
  1824. * @param boolean $pValue
  1825. */
  1826. public function setCalculationCacheEnabled($pValue = TRUE) {
  1827. $this->_calculationCacheEnabled = $pValue;
  1828. $this->clearCalculationCache();
  1829. } // function setCalculationCacheEnabled()
  1830. /**
  1831. * Enable calculation cache
  1832. */
  1833. public function enableCalculationCache() {
  1834. $this->setCalculationCacheEnabled(TRUE);
  1835. } // function enableCalculationCache()
  1836. /**
  1837. * Disable calculation cache
  1838. */
  1839. public function disableCalculationCache() {
  1840. $this->setCalculationCacheEnabled(FALSE);
  1841. } // function disableCalculationCache()
  1842. /**
  1843. * Clear calculation cache
  1844. */
  1845. public function clearCalculationCache() {
  1846. $this->_calculationCache = array();
  1847. } // function clearCalculationCache()
  1848. /**
  1849. * Clear calculation cache for a specified worksheet
  1850. *
  1851. * @param string $worksheetName
  1852. */
  1853. public function clearCalculationCacheForWorksheet($worksheetName) {
  1854. if (isset($this->_calculationCache[$worksheetName])) {
  1855. unset($this->_calculationCache[$worksheetName]);
  1856. }
  1857. } // function clearCalculationCacheForWorksheet()
  1858. /**
  1859. * Rename calculation cache for a specified worksheet
  1860. *
  1861. * @param string $fromWorksheetName
  1862. * @param string $toWorksheetName
  1863. */
  1864. public function renameCalculationCacheForWorksheet($fromWorksheetName, $toWorksheetName) {
  1865. if (isset($this->_calculationCache[$fromWorksheetName])) {
  1866. $this->_calculationCache[$toWorksheetName] = &$this->_calculationCache[$fromWorksheetName];
  1867. unset($this->_calculationCache[$fromWorksheetName]);
  1868. }
  1869. } // function renameCalculationCacheForWorksheet()
  1870. /**
  1871. * Get the currently defined locale code
  1872. *
  1873. * @return string
  1874. */
  1875. public function getLocale() {
  1876. return self::$_localeLanguage;
  1877. } // function getLocale()
  1878. /**
  1879. * Set the locale code
  1880. *
  1881. * @param string $locale The locale to use for formula translation
  1882. * @return boolean
  1883. */
  1884. public function setLocale($locale = 'en_us') {
  1885. // Identify our locale and language
  1886. $language = $locale = strtolower($locale);
  1887. if (strpos($locale,'_') !== FALSE) {
  1888. list($language) = explode('_',$locale);
  1889. }
  1890. if (count(self::$_validLocaleLanguages) == 1)
  1891. self::_loadLocales();
  1892. // Test whether we have any language data for this language (any locale)
  1893. if (in_array($language,self::$_validLocaleLanguages)) {
  1894. // initialise language/locale settings
  1895. self::$_localeFunctions = array();
  1896. self::$_localeArgumentSeparator = ',';
  1897. self::$_localeBoolean = array('TRUE' => 'TRUE', 'FALSE' => 'FALSE', 'NULL' => 'NULL');
  1898. // Default is English, if user isn't requesting english, then read the necessary data from the locale files
  1899. if ($locale != 'en_us') {
  1900. // Search for a file with a list of function names for locale
  1901. $functionNamesFile = PHPEXCEL_ROOT . 'PHPExcel'.DIRECTORY_SEPARATOR.'locale'.DIRECTORY_SEPARATOR.str_replace('_',DIRECTORY_SEPARATOR,$locale).DIRECTORY_SEPARATOR.'functions';
  1902. if (!file_exists($functionNamesFile)) {
  1903. // If there isn't a locale specific function file, look for a language specific function file
  1904. $functionNamesFile = PHPEXCEL_ROOT . 'PHPExcel'.DIRECTORY_SEPARATOR.'locale'.DIRECTORY_SEPARATOR.$language.DIRECTORY_SEPARATOR.'functions';
  1905. if (!file_exists($functionNamesFile)) {
  1906. return FALSE;
  1907. }
  1908. }
  1909. // Retrieve the list of locale or language specific function names
  1910. $localeFunctions = file($functionNamesFile,FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
  1911. foreach ($localeFunctions as $localeFunction) {
  1912. list($localeFunction) = explode('##',$localeFunction); // Strip out comments
  1913. if (strpos($localeFunction,'=') !== FALSE) {
  1914. list($fName,$lfName) = explode('=',$localeFunction);
  1915. $fName = trim($fName);
  1916. $lfName = trim($lfName);
  1917. if ((isset(self::$_PHPExcelFunctions[$fName])) && ($lfName != '') && ($fName != $lfName)) {
  1918. self::$_localeFunctions[$fName] = $lfName;
  1919. }
  1920. }
  1921. }
  1922. // Default the TRUE and FALSE constants to the locale names of the TRUE() and FALSE() functions
  1923. if (isset(self::$_localeFunctions['TRUE'])) { self::$_localeBoolean['TRUE'] = self::$_localeFunctions['TRUE']; }
  1924. if (isset(self::$_localeFunctions['FALSE'])) { self::$_localeBoolean['FALSE'] = self::$_localeFunctions['FALSE']; }
  1925. $configFile = PHPEXCEL_ROOT . 'PHPExcel'.DIRECTORY_SEPARATOR.'locale'.DIRECTORY_SEPARATOR.str_replace('_',DIRECTORY_SEPARATOR,$locale).DIRECTORY_SEPARATOR.'config';
  1926. if (!file_exists($configFile)) {
  1927. $configFile = PHPEXCEL_ROOT . 'PHPExcel'.DIRECTORY_SEPARATOR.'locale'.DIRECTORY_SEPARATOR.$language.DIRECTORY_SEPARATOR.'config';
  1928. }
  1929. if (file_exists($configFile)) {
  1930. $localeSettings = file($configFile,FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
  1931. foreach ($localeSettings as $localeSetting) {
  1932. list($localeSetting) = explode('##',$localeSetting); // Strip out comments
  1933. if (strpos($localeSetting,'=') !== FALSE) {
  1934. list($settingName,$settingValue) = explode('=',$localeSetting);
  1935. $settingName = strtoupper(trim($settingName));
  1936. switch ($settingName) {
  1937. case 'ARGUMENTSEPARATOR' :
  1938. self::$_localeArgumentSeparator = trim($settingValue);
  1939. break;
  1940. }
  1941. }
  1942. }
  1943. }
  1944. }
  1945. self::$functionReplaceFromExcel = self::$functionReplaceToExcel =
  1946. self::$functionReplaceFromLocale = self::$functionReplaceToLocale = NULL;
  1947. self::$_localeLanguage = $locale;
  1948. return TRUE;
  1949. }
  1950. return FALSE;
  1951. } // function setLocale()
  1952. public static function _translateSeparator($fromSeparator,$toSeparator,$formula,&$inBraces) {
  1953. $strlen = mb_strlen($formula);
  1954. for ($i = 0; $i < $strlen; ++$i) {
  1955. $chr = mb_substr($formula,$i,1);
  1956. switch ($chr) {
  1957. case '{' : $inBraces = TRUE;
  1958. break;
  1959. case '}' : $inBraces = FALSE;
  1960. break;
  1961. case $fromSeparator :
  1962. if (!$inBraces) {
  1963. $formula = mb_substr($formula,0,$i).$toSeparator.mb_substr($formula,$i+1);
  1964. }
  1965. }
  1966. }
  1967. return $formula;
  1968. }
  1969. private static function _translateFormula($from,$to,$formula,$fromSeparator,$toSeparator) {
  1970. // Convert any Excel function names to the required language
  1971. if (self::$_localeLanguage !== 'en_us') {
  1972. $inBraces = FALSE;
  1973. // If there is the possibility of braces within a quoted string, then we don't treat those as matrix indicators
  1974. if (strpos($formula,'"') !== FALSE) {
  1975. // So instead we skip replacing in any quoted strings by only replacing in every other array element after we've exploded
  1976. // the formula
  1977. $temp = explode('"',$formula);
  1978. $i = FALSE;
  1979. foreach($temp as &$value) {
  1980. // Only count/replace in alternating array entries
  1981. if ($i = !$i) {
  1982. $value = preg_replace($from,$to,$value);
  1983. $value = self::_translateSeparator($fromSeparator,$toSeparator,$value,$inBraces);
  1984. }
  1985. }
  1986. unset($value);
  1987. // Then rebuild the formula string
  1988. $formula = implode('"',$temp);
  1989. } else {
  1990. // If there's no quoted strings, then we do a simple count/replace
  1991. $formula = preg_replace($from,$to,$formula);
  1992. $formula = self::_translateSeparator($fromSeparator,$toSeparator,$formula,$inBraces);
  1993. }
  1994. }
  1995. return $formula;
  1996. }
  1997. private static $functionReplaceFromExcel = NULL;
  1998. private static $functionReplaceToLocale = NULL;
  1999. public function _translateFormulaToLocale($formula) {
  2000. if (self::$functionReplaceFromExcel === NULL) {
  2001. self::$functionReplaceFromExcel = array();
  2002. foreach(array_keys(self::$_localeFunctions) as $excelFunctionName) {
  2003. self::$functionReplaceFromExcel[] = '/(@?[^\w\.])'.preg_quote($excelFunctionName).'([\s]*\()/Ui';
  2004. }
  2005. foreach(array_keys(self::$_localeBoolean) as $excelBoolean) {
  2006. self::$functionReplaceFromExcel[] = '/(@?[^\w\.])'.preg_quote($excelBoolean).'([^\w\.])/Ui';
  2007. }
  2008. }
  2009. if (self::$functionReplaceToLocale === NULL) {
  2010. self::$functionReplaceToLocale = array();
  2011. foreach(array_values(self::$_localeFunctions) as $localeFunctionName) {
  2012. self::$functionReplaceToLocale[] = '$1'.trim($localeFunctionName).'$2';
  2013. }
  2014. foreach(array_values(self::$_localeBoolean) as $localeBoolean) {
  2015. self::$functionReplaceToLocale[] = '$1'.trim($localeBoolean).'$2';
  2016. }
  2017. }
  2018. return self::_translateFormula(self::$functionReplaceFromExcel,self::$functionReplaceToLocale,$formula,',',self::$_localeArgumentSeparator);
  2019. } // function _translateFormulaToLocale()
  2020. private static $functionReplaceFromLocale = NULL;
  2021. private static $functionReplaceToExcel = NULL;
  2022. public function _translateFormulaToEnglish($formula) {
  2023. if (self::$functionReplaceFromLocale === NULL) {
  2024. self::$functionReplaceFromLocale = array();
  2025. foreach(array_values(self::$_localeFunctions) as $localeFunctionName) {
  2026. self::$functionReplaceFromLocale[] = '/(@?[^\w\.])'.preg_quote($localeFunctionName).'([\s]*\()/Ui';
  2027. }
  2028. foreach(array_values(self::$_localeBoolean) as $excelBoolean) {
  2029. self::$functionReplaceFromLocale[] = '/(@?[^\w\.])'.preg_quote($excelBoolean).'([^\w\.])/Ui';
  2030. }
  2031. }
  2032. if (self::$functionReplaceToExcel === NULL) {
  2033. self::$functionReplaceToExcel = array();
  2034. foreach(array_keys(self::$_localeFunctions) as $excelFunctionName) {
  2035. self::$functionReplaceToExcel[] = '$1'.trim($excelFunctionName).'$2';
  2036. }
  2037. foreach(array_keys(self::$_localeBoolean) as $excelBoolean) {
  2038. self::$functionReplaceToExcel[] = '$1'.trim($excelBoolean).'$2';
  2039. }
  2040. }
  2041. return self::_translateFormula(self::$functionReplaceFromLocale,self::$functionReplaceToExcel,$formula,self::$_localeArgumentSeparator,',');
  2042. } // function _translateFormulaToEnglish()
  2043. public static function _localeFunc($function) {
  2044. if (self::$_localeLanguage !== 'en_us') {
  2045. $functionName = trim($function,'(');
  2046. if (isset(self::$_localeFunctions[$functionName])) {
  2047. $brace = ($functionName != $function);
  2048. $function = self::$_localeFunctions[$functionName];
  2049. if ($brace) { $function .= '('; }
  2050. }
  2051. }
  2052. return $function;
  2053. }
  2054. /**
  2055. * Wrap string values in quotes
  2056. *
  2057. * @param mixed $value
  2058. * @return mixed
  2059. */
  2060. public static function _wrapResult($value) {
  2061. if (is_string($value)) {
  2062. // Error values cannot be "wrapped"
  2063. if (preg_match('/^'.self::CALCULATION_REGEXP_ERROR.'$/i', $value, $match)) {
  2064. // Return Excel errors "as is"
  2065. return $value;
  2066. }
  2067. // Return strings wrapped in quotes
  2068. return '"'.$value.'"';
  2069. // Convert numeric errors to NaN error
  2070. } else if((is_float($value)) && ((is_nan($value)) || (is_infinite($value)))) {
  2071. return PHPExcel_Calculation_Functions::NaN();
  2072. }
  2073. return $value;
  2074. } // function _wrapResult()
  2075. /**
  2076. * Remove quotes used as a wrapper to identify string values
  2077. *
  2078. * @param mixed $value
  2079. * @return mixed
  2080. */
  2081. public static function _unwrapResult($value) {
  2082. if (is_string($value)) {
  2083. if ((isset($value{0})) && ($value{0} == '"') && (substr($value,-1) == '"')) {
  2084. return substr($value,1,-1);
  2085. }
  2086. // Convert numeric errors to NaN error
  2087. } else if((is_float($value)) && ((is_nan($value)) || (is_infinite($value)))) {
  2088. return PHPExcel_Calculation_Functions::NaN();
  2089. }
  2090. return $value;
  2091. } // function _unwrapResult()
  2092. /**
  2093. * Calculate cell value (using formula from a cell ID)
  2094. * Retained for backward compatibility
  2095. *
  2096. * @access public
  2097. * @param PHPExcel_Cell $pCell Cell to calculate
  2098. * @return mixed
  2099. * @throws PHPExcel_Calculation_Exception
  2100. */
  2101. public function calculate(PHPExcel_Cell $pCell = NULL) {
  2102. try {
  2103. return $this->calculateCellValue($pCell);
  2104. } catch (PHPExcel_Exception $e) {
  2105. throw new PHPExcel_Calculation_Exception($e->getMessage());
  2106. }
  2107. } // function calculate()
  2108. /**
  2109. * Calculate the value of a cell formula
  2110. *
  2111. * @access public
  2112. * @param PHPExcel_Cell $pCell Cell to calculate
  2113. * @param Boolean $resetLog Flag indicating whether the debug log should be reset or not
  2114. * @return mixed
  2115. * @throws PHPExcel_Calculation_Exception
  2116. */
  2117. public function calculateCellValue(PHPExcel_Cell $pCell = NULL, $resetLog = TRUE) {
  2118. if ($pCell === NULL) {
  2119. return NULL;
  2120. }
  2121. $returnArrayAsType = self::$returnArrayAsType;
  2122. if ($resetLog) {
  2123. // Initialise the logging settings if requested
  2124. $this->formulaError = null;
  2125. $this->_debugLog->clearLog();
  2126. $this->_cyclicReferenceStack->clear();
  2127. $this->_cyclicFormulaCount = 1;
  2128. self::$returnArrayAsType = self::RETURN_ARRAY_AS_ARRAY;
  2129. }
  2130. // Execute the calculation for the cell formula
  2131. $this->_cellStack[] = array(
  2132. 'sheet' => $pCell->getWorksheet()->getTitle(),
  2133. 'cell' => $pCell->getCoordinate(),
  2134. );
  2135. try {
  2136. $result = self::_unwrapResult($this->_calculateFormulaValue($pCell->getValue(), $pCell->getCoordinate(), $pCell));
  2137. $cellAddress = array_pop($this->_cellStack);
  2138. $this->_workbook->getSheetByName($cellAddress['sheet'])->getCell($cellAddress['cell']);
  2139. } catch (PHPExcel_Exception $e) {
  2140. $cellAddress = array_pop($this->_cellStack);
  2141. $this->_workbook->getSheetByName($cellAddress['sheet'])->getCell($cellAddress['cell']);
  2142. throw new PHPExcel_Calculation_Exception($e->getMessage());
  2143. }
  2144. if ((is_array($result)) && (self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY)) {
  2145. self::$returnArrayAsType = $returnArrayAsType;
  2146. $testResult = PHPExcel_Calculation_Functions::flattenArray($result);
  2147. if (self::$returnArrayAsType == self::RETURN_ARRAY_AS_ERROR) {
  2148. return PHPExcel_Calculation_Functions::VALUE();
  2149. }
  2150. // If there's only a single cell in the array, then we allow it
  2151. if (count($testResult) != 1) {
  2152. // If keys are numeric, then it's a matrix result rather than a cell range result, so we permit it
  2153. $r = array_keys($result);
  2154. $r = array_shift($r);
  2155. if (!is_numeric($r)) { return PHPExcel_Calculation_Functions::VALUE(); }
  2156. if (is_array($result[$r])) {
  2157. $c = array_keys($result[$r]);
  2158. $c = array_shift($c);
  2159. if (!is_numeric($c)) {
  2160. return PHPExcel_Calculation_Functions::VALUE();
  2161. }
  2162. }
  2163. }
  2164. $result = array_shift($testResult);
  2165. }
  2166. self::$returnArrayAsType = $returnArrayAsType;
  2167. if ($result === NULL) {
  2168. return 0;
  2169. } elseif((is_float($result)) && ((is_nan($result)) || (is_infinite($result)))) {
  2170. return PHPExcel_Calculation_Functions::NaN();
  2171. }
  2172. return $result;
  2173. } // function calculateCellValue(
  2174. /**
  2175. * Validate and parse a formula string
  2176. *
  2177. * @param string $formula Formula to parse
  2178. * @return array
  2179. * @throws PHPExcel_Calculation_Exception
  2180. */
  2181. public function parseFormula($formula) {
  2182. // Basic validation that this is indeed a formula
  2183. // We return an empty array if not
  2184. $formula = trim($formula);
  2185. if ((!isset($formula{0})) || ($formula{0} != '=')) return array();
  2186. $formula = ltrim(substr($formula,1));
  2187. if (!isset($formula{0})) return array();
  2188. // Parse the formula and return the token stack
  2189. return $this->_parseFormula($formula);
  2190. } // function parseFormula()
  2191. /**
  2192. * Calculate the value of a formula
  2193. *
  2194. * @param string $formula Formula to parse
  2195. * @param string $cellID Address of the cell to calculate
  2196. * @param PHPExcel_Cell $pCell Cell to calculate
  2197. * @return mixed
  2198. * @throws PHPExcel_Calculation_Exception
  2199. */
  2200. public function calculateFormula($formula, $cellID=NULL, PHPExcel_Cell $pCell = NULL) {
  2201. // Initialise the logging settings
  2202. $this->formulaError = null;
  2203. $this->_debugLog->clearLog();
  2204. $this->_cyclicReferenceStack->clear();
  2205. // Disable calculation cacheing because it only applies to cell calculations, not straight formulae
  2206. // But don't actually flush any cache
  2207. $resetCache = $this->getCalculationCacheEnabled();
  2208. $this->_calculationCacheEnabled = FALSE;
  2209. // Execute the calculation
  2210. try {
  2211. $result = self::_unwrapResult($this->_calculateFormulaValue($formula, $cellID, $pCell));
  2212. } catch (PHPExcel_Exception $e) {
  2213. throw new PHPExcel_Calculation_Exception($e->getMessage());
  2214. }
  2215. // Reset calculation cacheing to its previous state
  2216. $this->_calculationCacheEnabled = $resetCache;
  2217. return $result;
  2218. } // function calculateFormula()
  2219. public function getValueFromCache($cellReference, &$cellValue) {
  2220. // Is calculation cacheing enabled?
  2221. // Is the value present in calculation cache?
  2222. $this->_debugLog->writeDebugLog('Testing cache value for cell ', $cellReference);
  2223. if (($this->_calculationCacheEnabled) && (isset($this->_calculationCache[$cellReference]))) {
  2224. $this->_debugLog->writeDebugLog('Retrieving value for cell ', $cellReference, ' from cache');
  2225. // Return the cached result
  2226. $cellValue = $this->_calculationCache[$cellReference];
  2227. return TRUE;
  2228. }
  2229. return FALSE;
  2230. }
  2231. public function saveValueToCache($cellReference, $cellValue) {
  2232. if ($this->_calculationCacheEnabled) {
  2233. $this->_calculationCache[$cellReference] = $cellValue;
  2234. }
  2235. }
  2236. /**
  2237. * Parse a cell formula and calculate its value
  2238. *
  2239. * @param string $formula The formula to parse and calculate
  2240. * @param string $cellID The ID (e.g. A3) of the cell that we are calculating
  2241. * @param PHPExcel_Cell $pCell Cell to calculate
  2242. * @return mixed
  2243. * @throws PHPExcel_Calculation_Exception
  2244. */
  2245. public function _calculateFormulaValue($formula, $cellID=null, PHPExcel_Cell $pCell = null) {
  2246. $cellValue = null;
  2247. // Basic validation that this is indeed a formula
  2248. // We simply return the cell value if not
  2249. $formula = trim($formula);
  2250. if ($formula{0} != '=') return self::_wrapResult($formula);
  2251. $formula = ltrim(substr($formula, 1));
  2252. if (!isset($formula{0})) return self::_wrapResult($formula);
  2253. $pCellParent = ($pCell !== NULL) ? $pCell->getWorksheet() : NULL;
  2254. $wsTitle = ($pCellParent !== NULL) ? $pCellParent->getTitle() : "\x00Wrk";
  2255. $wsCellReference = $wsTitle . '!' . $cellID;
  2256. if (($cellID !== NULL) && ($this->getValueFromCache($wsCellReference, $cellValue))) {
  2257. return $cellValue;
  2258. }
  2259. if (($wsTitle{0} !== "\x00") && ($this->_cyclicReferenceStack->onStack($wsCellReference))) {
  2260. if ($this->cyclicFormulaCount <= 0) {
  2261. $this->_cyclicFormulaCell = '';
  2262. return $this->_raiseFormulaError('Cyclic Reference in Formula');
  2263. } elseif ($this->_cyclicFormulaCell === $wsCellReference) {
  2264. ++$this->_cyclicFormulaCount;
  2265. if ($this->_cyclicFormulaCount >= $this->cyclicFormulaCount) {
  2266. $this->_cyclicFormulaCell = '';
  2267. return $cellValue;
  2268. }
  2269. } elseif ($this->_cyclicFormulaCell == '') {
  2270. if ($this->_cyclicFormulaCount >= $this->cyclicFormulaCount) {
  2271. return $cellValue;
  2272. }
  2273. $this->_cyclicFormulaCell = $wsCellReference;
  2274. }
  2275. }
  2276. // Parse the formula onto the token stack and calculate the value
  2277. $this->_cyclicReferenceStack->push($wsCellReference);
  2278. $cellValue = $this->_processTokenStack($this->_parseFormula($formula, $pCell), $cellID, $pCell);
  2279. $this->_cyclicReferenceStack->pop();
  2280. // Save to calculation cache
  2281. if ($cellID !== NULL) {
  2282. $this->saveValueToCache($wsCellReference, $cellValue);
  2283. }
  2284. // Return the calculated value
  2285. return $cellValue;
  2286. } // function _calculateFormulaValue()
  2287. /**
  2288. * Ensure that paired matrix operands are both matrices and of the same size
  2289. *
  2290. * @param mixed &$operand1 First matrix operand
  2291. * @param mixed &$operand2 Second matrix operand
  2292. * @param integer $resize Flag indicating whether the matrices should be resized to match
  2293. * and (if so), whether the smaller dimension should grow or the
  2294. * larger should shrink.
  2295. * 0 = no resize
  2296. * 1 = shrink to fit
  2297. * 2 = extend to fit
  2298. */
  2299. private static function _checkMatrixOperands(&$operand1,&$operand2,$resize = 1) {
  2300. // Examine each of the two operands, and turn them into an array if they aren't one already
  2301. // Note that this function should only be called if one or both of the operand is already an array
  2302. if (!is_array($operand1)) {
  2303. list($matrixRows,$matrixColumns) = self::_getMatrixDimensions($operand2);
  2304. $operand1 = array_fill(0,$matrixRows,array_fill(0,$matrixColumns,$operand1));
  2305. $resize = 0;
  2306. } elseif (!is_array($operand2)) {
  2307. list($matrixRows,$matrixColumns) = self::_getMatrixDimensions($operand1);
  2308. $operand2 = array_fill(0,$matrixRows,array_fill(0,$matrixColumns,$operand2));
  2309. $resize = 0;
  2310. }
  2311. list($matrix1Rows,$matrix1Columns) = self::_getMatrixDimensions($operand1);
  2312. list($matrix2Rows,$matrix2Columns) = self::_getMatrixDimensions($operand2);
  2313. if (($matrix1Rows == $matrix2Columns) && ($matrix2Rows == $matrix1Columns)) {
  2314. $resize = 1;
  2315. }
  2316. if ($resize == 2) {
  2317. // Given two matrices of (potentially) unequal size, convert the smaller in each dimension to match the larger
  2318. self::_resizeMatricesExtend($operand1,$operand2,$matrix1Rows,$matrix1Columns,$matrix2Rows,$matrix2Columns);
  2319. } elseif ($resize == 1) {
  2320. // Given two matrices of (potentially) unequal size, convert the larger in each dimension to match the smaller
  2321. self::_resizeMatricesShrink($operand1,$operand2,$matrix1Rows,$matrix1Columns,$matrix2Rows,$matrix2Columns);
  2322. }
  2323. return array( $matrix1Rows,$matrix1Columns,$matrix2Rows,$matrix2Columns);
  2324. } // function _checkMatrixOperands()
  2325. /**
  2326. * Read the dimensions of a matrix, and re-index it with straight numeric keys starting from row 0, column 0
  2327. *
  2328. * @param mixed &$matrix matrix operand
  2329. * @return array An array comprising the number of rows, and number of columns
  2330. */
  2331. public static function _getMatrixDimensions(&$matrix) {
  2332. $matrixRows = count($matrix);
  2333. $matrixColumns = 0;
  2334. foreach($matrix as $rowKey => $rowValue) {
  2335. $matrixColumns = max(count($rowValue),$matrixColumns);
  2336. if (!is_array($rowValue)) {
  2337. $matrix[$rowKey] = array($rowValue);
  2338. } else {
  2339. $matrix[$rowKey] = array_values($rowValue);
  2340. }
  2341. }
  2342. $matrix = array_values($matrix);
  2343. return array($matrixRows,$matrixColumns);
  2344. } // function _getMatrixDimensions()
  2345. /**
  2346. * Ensure that paired matrix operands are both matrices of the same size
  2347. *
  2348. * @param mixed &$matrix1 First matrix operand
  2349. * @param mixed &$matrix2 Second matrix operand
  2350. * @param integer $matrix1Rows Row size of first matrix operand
  2351. * @param integer $matrix1Columns Column size of first matrix operand
  2352. * @param integer $matrix2Rows Row size of second matrix operand
  2353. * @param integer $matrix2Columns Column size of second matrix operand
  2354. */
  2355. private static function _resizeMatricesShrink(&$matrix1,&$matrix2,$matrix1Rows,$matrix1Columns,$matrix2Rows,$matrix2Columns) {
  2356. if (($matrix2Columns < $matrix1Columns) || ($matrix2Rows < $matrix1Rows)) {
  2357. if ($matrix2Rows < $matrix1Rows) {
  2358. for ($i = $matrix2Rows; $i < $matrix1Rows; ++$i) {
  2359. unset($matrix1[$i]);
  2360. }
  2361. }
  2362. if ($matrix2Columns < $matrix1Columns) {
  2363. for ($i = 0; $i < $matrix1Rows; ++$i) {
  2364. for ($j = $matrix2Columns; $j < $matrix1Columns; ++$j) {
  2365. unset($matrix1[$i][$j]);
  2366. }
  2367. }
  2368. }
  2369. }
  2370. if (($matrix1Columns < $matrix2Columns) || ($matrix1Rows < $matrix2Rows)) {
  2371. if ($matrix1Rows < $matrix2Rows) {
  2372. for ($i = $matrix1Rows; $i < $matrix2Rows; ++$i) {
  2373. unset($matrix2[$i]);
  2374. }
  2375. }
  2376. if ($matrix1Columns < $matrix2Columns) {
  2377. for ($i = 0; $i < $matrix2Rows; ++$i) {
  2378. for ($j = $matrix1Columns; $j < $matrix2Columns; ++$j) {
  2379. unset($matrix2[$i][$j]);
  2380. }
  2381. }
  2382. }
  2383. }
  2384. } // function _resizeMatricesShrink()
  2385. /**
  2386. * Ensure that paired matrix operands are both matrices of the same size
  2387. *
  2388. * @param mixed &$matrix1 First matrix operand
  2389. * @param mixed &$matrix2 Second matrix operand
  2390. * @param integer $matrix1Rows Row size of first matrix operand
  2391. * @param integer $matrix1Columns Column size of first matrix operand
  2392. * @param integer $matrix2Rows Row size of second matrix operand
  2393. * @param integer $matrix2Columns Column size of second matrix operand
  2394. */
  2395. private static function _resizeMatricesExtend(&$matrix1,&$matrix2,$matrix1Rows,$matrix1Columns,$matrix2Rows,$matrix2Columns) {
  2396. if (($matrix2Columns < $matrix1Columns) || ($matrix2Rows < $matrix1Rows)) {
  2397. if ($matrix2Columns < $matrix1Columns) {
  2398. for ($i = 0; $i < $matrix2Rows; ++$i) {
  2399. $x = $matrix2[$i][$matrix2Columns-1];
  2400. for ($j = $matrix2Columns; $j < $matrix1Columns; ++$j) {
  2401. $matrix2[$i][$j] = $x;
  2402. }
  2403. }
  2404. }
  2405. if ($matrix2Rows < $matrix1Rows) {
  2406. $x = $matrix2[$matrix2Rows-1];
  2407. for ($i = 0; $i < $matrix1Rows; ++$i) {
  2408. $matrix2[$i] = $x;
  2409. }
  2410. }
  2411. }
  2412. if (($matrix1Columns < $matrix2Columns) || ($matrix1Rows < $matrix2Rows)) {
  2413. if ($matrix1Columns < $matrix2Columns) {
  2414. for ($i = 0; $i < $matrix1Rows; ++$i) {
  2415. $x = $matrix1[$i][$matrix1Columns-1];
  2416. for ($j = $matrix1Columns; $j < $matrix2Columns; ++$j) {
  2417. $matrix1[$i][$j] = $x;
  2418. }
  2419. }
  2420. }
  2421. if ($matrix1Rows < $matrix2Rows) {
  2422. $x = $matrix1[$matrix1Rows-1];
  2423. for ($i = 0; $i < $matrix2Rows; ++$i) {
  2424. $matrix1[$i] = $x;
  2425. }
  2426. }
  2427. }
  2428. } // function _resizeMatricesExtend()
  2429. /**
  2430. * Format details of an operand for display in the log (based on operand type)
  2431. *
  2432. * @param mixed $value First matrix operand
  2433. * @return mixed
  2434. */
  2435. private function _showValue($value) {
  2436. if ($this->_debugLog->getWriteDebugLog()) {
  2437. $testArray = PHPExcel_Calculation_Functions::flattenArray($value);
  2438. if (count($testArray) == 1) {
  2439. $value = array_pop($testArray);
  2440. }
  2441. if (is_array($value)) {
  2442. $returnMatrix = array();
  2443. $pad = $rpad = ', ';
  2444. foreach($value as $row) {
  2445. if (is_array($row)) {
  2446. $returnMatrix[] = implode($pad,array_map(array($this,'_showValue'),$row));
  2447. $rpad = '; ';
  2448. } else {
  2449. $returnMatrix[] = $this->_showValue($row);
  2450. }
  2451. }
  2452. return '{ '.implode($rpad,$returnMatrix).' }';
  2453. } elseif(is_string($value) && (trim($value,'"') == $value)) {
  2454. return '"'.$value.'"';
  2455. } elseif(is_bool($value)) {
  2456. return ($value) ? self::$_localeBoolean['TRUE'] : self::$_localeBoolean['FALSE'];
  2457. }
  2458. }
  2459. return PHPExcel_Calculation_Functions::flattenSingleValue($value);
  2460. } // function _showValue()
  2461. /**
  2462. * Format type and details of an operand for display in the log (based on operand type)
  2463. *
  2464. * @param mixed $value First matrix operand
  2465. * @return mixed
  2466. */
  2467. private function _showTypeDetails($value) {
  2468. if ($this->_debugLog->getWriteDebugLog()) {
  2469. $testArray = PHPExcel_Calculation_Functions::flattenArray($value);
  2470. if (count($testArray) == 1) {
  2471. $value = array_pop($testArray);
  2472. }
  2473. if ($value === NULL) {
  2474. return 'a NULL value';
  2475. } elseif (is_float($value)) {
  2476. $typeString = 'a floating point number';
  2477. } elseif(is_int($value)) {
  2478. $typeString = 'an integer number';
  2479. } elseif(is_bool($value)) {
  2480. $typeString = 'a boolean';
  2481. } elseif(is_array($value)) {
  2482. $typeString = 'a matrix';
  2483. } else {
  2484. if ($value == '') {
  2485. return 'an empty string';
  2486. } elseif ($value{0} == '#') {
  2487. return 'a '.$value.' error';
  2488. } else {
  2489. $typeString = 'a string';
  2490. }
  2491. }
  2492. return $typeString.' with a value of '.$this->_showValue($value);
  2493. }
  2494. } // function _showTypeDetails()
  2495. private function _convertMatrixReferences($formula) {
  2496. static $matrixReplaceFrom = array('{',';','}');
  2497. static $matrixReplaceTo = array('MKMATRIX(MKMATRIX(','),MKMATRIX(','))');
  2498. // Convert any Excel matrix references to the MKMATRIX() function
  2499. if (strpos($formula,'{') !== FALSE) {
  2500. // If there is the possibility of braces within a quoted string, then we don't treat those as matrix indicators
  2501. if (strpos($formula,'"') !== FALSE) {
  2502. // So instead we skip replacing in any quoted strings by only replacing in every other array element after we've exploded
  2503. // the formula
  2504. $temp = explode('"',$formula);
  2505. // Open and Closed counts used for trapping mismatched braces in the formula
  2506. $openCount = $closeCount = 0;
  2507. $i = FALSE;
  2508. foreach($temp as &$value) {
  2509. // Only count/replace in alternating array entries
  2510. if ($i = !$i) {
  2511. $openCount += substr_count($value,'{');
  2512. $closeCount += substr_count($value,'}');
  2513. $value = str_replace($matrixReplaceFrom,$matrixReplaceTo,$value);
  2514. }
  2515. }
  2516. unset($value);
  2517. // Then rebuild the formula string
  2518. $formula = implode('"',$temp);
  2519. } else {
  2520. // If there's no quoted strings, then we do a simple count/replace
  2521. $openCount = substr_count($formula,'{');
  2522. $closeCount = substr_count($formula,'}');
  2523. $formula = str_replace($matrixReplaceFrom,$matrixReplaceTo,$formula);
  2524. }
  2525. // Trap for mismatched braces and trigger an appropriate error
  2526. if ($openCount < $closeCount) {
  2527. if ($openCount > 0) {
  2528. return $this->_raiseFormulaError("Formula Error: Mismatched matrix braces '}'");
  2529. } else {
  2530. return $this->_raiseFormulaError("Formula Error: Unexpected '}' encountered");
  2531. }
  2532. } elseif ($openCount > $closeCount) {
  2533. if ($closeCount > 0) {
  2534. return $this->_raiseFormulaError("Formula Error: Mismatched matrix braces '{'");
  2535. } else {
  2536. return $this->_raiseFormulaError("Formula Error: Unexpected '{' encountered");
  2537. }
  2538. }
  2539. }
  2540. return $formula;
  2541. } // function _convertMatrixReferences()
  2542. private static function _mkMatrix() {
  2543. return func_get_args();
  2544. } // function _mkMatrix()
  2545. // Binary Operators
  2546. // These operators always work on two values
  2547. // Array key is the operator, the value indicates whether this is a left or right associative operator
  2548. private static $_operatorAssociativity = array(
  2549. '^' => 0, // Exponentiation
  2550. '*' => 0, '/' => 0, // Multiplication and Division
  2551. '+' => 0, '-' => 0, // Addition and Subtraction
  2552. '&' => 0, // Concatenation
  2553. '|' => 0, ':' => 0, // Intersect and Range
  2554. '>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0 // Comparison
  2555. );
  2556. // Comparison (Boolean) Operators
  2557. // These operators work on two values, but always return a boolean result
  2558. private static $_comparisonOperators = array('>' => TRUE, '<' => TRUE, '=' => TRUE, '>=' => TRUE, '<=' => TRUE, '<>' => TRUE);
  2559. // Operator Precedence
  2560. // This list includes all valid operators, whether binary (including boolean) or unary (such as %)
  2561. // Array key is the operator, the value is its precedence
  2562. private static $_operatorPrecedence = array(
  2563. ':' => 8, // Range
  2564. '|' => 7, // Intersect
  2565. '~' => 6, // Negation
  2566. '%' => 5, // Percentage
  2567. '^' => 4, // Exponentiation
  2568. '*' => 3, '/' => 3, // Multiplication and Division
  2569. '+' => 2, '-' => 2, // Addition and Subtraction
  2570. '&' => 1, // Concatenation
  2571. '>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0 // Comparison
  2572. );
  2573. // Convert infix to postfix notation
  2574. private function _parseFormula($formula, PHPExcel_Cell $pCell = NULL) {
  2575. if (($formula = $this->_convertMatrixReferences(trim($formula))) === FALSE) {
  2576. return FALSE;
  2577. }
  2578. // If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent worksheet),
  2579. // so we store the parent worksheet so that we can re-attach it when necessary
  2580. $pCellParent = ($pCell !== NULL) ? $pCell->getWorksheet() : NULL;
  2581. $regexpMatchString = '/^('.self::CALCULATION_REGEXP_FUNCTION.
  2582. '|'.self::CALCULATION_REGEXP_CELLREF.
  2583. '|'.self::CALCULATION_REGEXP_NUMBER.
  2584. '|'.self::CALCULATION_REGEXP_STRING.
  2585. '|'.self::CALCULATION_REGEXP_OPENBRACE.
  2586. '|'.self::CALCULATION_REGEXP_NAMEDRANGE.
  2587. '|'.self::CALCULATION_REGEXP_ERROR.
  2588. ')/si';
  2589. // Start with initialisation
  2590. $index = 0;
  2591. $stack = new PHPExcel_Calculation_Token_Stack;
  2592. $output = array();
  2593. $expectingOperator = FALSE; // We use this test in syntax-checking the expression to determine when a
  2594. // - is a negation or + is a positive operator rather than an operation
  2595. $expectingOperand = FALSE; // We use this test in syntax-checking the expression to determine whether an operand
  2596. // should be null in a function call
  2597. // The guts of the lexical parser
  2598. // Loop through the formula extracting each operator and operand in turn
  2599. while(TRUE) {
  2600. //echo 'Assessing Expression '.substr($formula, $index),PHP_EOL;
  2601. $opCharacter = $formula{$index}; // Get the first character of the value at the current index position
  2602. //echo 'Initial character of expression block is '.$opCharacter,PHP_EOL;
  2603. if ((isset(self::$_comparisonOperators[$opCharacter])) && (strlen($formula) > $index) && (isset(self::$_comparisonOperators[$formula{$index+1}]))) {
  2604. $opCharacter .= $formula{++$index};
  2605. //echo 'Initial character of expression block is comparison operator '.$opCharacter.PHP_EOL;
  2606. }
  2607. // Find out if we're currently at the beginning of a number, variable, cell reference, function, parenthesis or operand
  2608. $isOperandOrFunction = preg_match($regexpMatchString, substr($formula, $index), $match);
  2609. //echo '$isOperandOrFunction is '.(($isOperandOrFunction) ? 'True' : 'False').PHP_EOL;
  2610. //var_dump($match);
  2611. if ($opCharacter == '-' && !$expectingOperator) { // Is it a negation instead of a minus?
  2612. //echo 'Element is a Negation operator',PHP_EOL;
  2613. $stack->push('Unary Operator','~'); // Put a negation on the stack
  2614. ++$index; // and drop the negation symbol
  2615. } elseif ($opCharacter == '%' && $expectingOperator) {
  2616. //echo 'Element is a Percentage operator',PHP_EOL;
  2617. $stack->push('Unary Operator','%'); // Put a percentage on the stack
  2618. ++$index;
  2619. } elseif ($opCharacter == '+' && !$expectingOperator) { // Positive (unary plus rather than binary operator plus) can be discarded?
  2620. //echo 'Element is a Positive number, not Plus operator',PHP_EOL;
  2621. ++$index; // Drop the redundant plus symbol
  2622. } elseif ((($opCharacter == '~') || ($opCharacter == '|')) && (!$isOperandOrFunction)) { // We have to explicitly deny a tilde or pipe, because they are legal
  2623. return $this->_raiseFormulaError("Formula Error: Illegal character '~'"); // on the stack but not in the input expression
  2624. } elseif ((isset(self::$_operators[$opCharacter]) or $isOperandOrFunction) && $expectingOperator) { // Are we putting an operator on the stack?
  2625. //echo 'Element with value '.$opCharacter.' is an Operator',PHP_EOL;
  2626. while($stack->count() > 0 &&
  2627. ($o2 = $stack->last()) &&
  2628. isset(self::$_operators[$o2['value']]) &&
  2629. @(self::$_operatorAssociativity[$opCharacter] ? self::$_operatorPrecedence[$opCharacter] < self::$_operatorPrecedence[$o2['value']] : self::$_operatorPrecedence[$opCharacter] <= self::$_operatorPrecedence[$o2['value']])) {
  2630. $output[] = $stack->pop(); // Swap operands and higher precedence operators from the stack to the output
  2631. }
  2632. $stack->push('Binary Operator',$opCharacter); // Finally put our current operator onto the stack
  2633. ++$index;
  2634. $expectingOperator = FALSE;
  2635. } elseif ($opCharacter == ')' && $expectingOperator) { // Are we expecting to close a parenthesis?
  2636. //echo 'Element is a Closing bracket',PHP_EOL;
  2637. $expectingOperand = FALSE;
  2638. while (($o2 = $stack->pop()) && $o2['value'] != '(') { // Pop off the stack back to the last (
  2639. if ($o2 === NULL) return $this->_raiseFormulaError('Formula Error: Unexpected closing brace ")"');
  2640. else $output[] = $o2;
  2641. }
  2642. $d = $stack->last(2);
  2643. if (preg_match('/^'.self::CALCULATION_REGEXP_FUNCTION.'$/i', $d['value'], $matches)) { // Did this parenthesis just close a function?
  2644. $functionName = $matches[1]; // Get the function name
  2645. //echo 'Closed Function is '.$functionName,PHP_EOL;
  2646. $d = $stack->pop();
  2647. $argumentCount = $d['value']; // See how many arguments there were (argument count is the next value stored on the stack)
  2648. //if ($argumentCount == 0) {
  2649. // echo 'With no arguments',PHP_EOL;
  2650. //} elseif ($argumentCount == 1) {
  2651. // echo 'With 1 argument',PHP_EOL;
  2652. //} else {
  2653. // echo 'With '.$argumentCount.' arguments',PHP_EOL;
  2654. //}
  2655. $output[] = $d; // Dump the argument count on the output
  2656. $output[] = $stack->pop(); // Pop the function and push onto the output
  2657. if (isset(self::$_controlFunctions[$functionName])) {
  2658. //echo 'Built-in function '.$functionName,PHP_EOL;
  2659. $expectedArgumentCount = self::$_controlFunctions[$functionName]['argumentCount'];
  2660. $functionCall = self::$_controlFunctions[$functionName]['functionCall'];
  2661. } elseif (isset(self::$_PHPExcelFunctions[$functionName])) {
  2662. //echo 'PHPExcel function '.$functionName,PHP_EOL;
  2663. $expectedArgumentCount = self::$_PHPExcelFunctions[$functionName]['argumentCount'];
  2664. $functionCall = self::$_PHPExcelFunctions[$functionName]['functionCall'];
  2665. } else { // did we somehow push a non-function on the stack? this should never happen
  2666. return $this->_raiseFormulaError("Formula Error: Internal error, non-function on stack");
  2667. }
  2668. // Check the argument count
  2669. $argumentCountError = FALSE;
  2670. if (is_numeric($expectedArgumentCount)) {
  2671. if ($expectedArgumentCount < 0) {
  2672. //echo '$expectedArgumentCount is between 0 and '.abs($expectedArgumentCount),PHP_EOL;
  2673. if ($argumentCount > abs($expectedArgumentCount)) {
  2674. $argumentCountError = TRUE;
  2675. $expectedArgumentCountString = 'no more than '.abs($expectedArgumentCount);
  2676. }
  2677. } else {
  2678. //echo '$expectedArgumentCount is numeric '.$expectedArgumentCount,PHP_EOL;
  2679. if ($argumentCount != $expectedArgumentCount) {
  2680. $argumentCountError = TRUE;
  2681. $expectedArgumentCountString = $expectedArgumentCount;
  2682. }
  2683. }
  2684. } elseif ($expectedArgumentCount != '*') {
  2685. $isOperandOrFunction = preg_match('/(\d*)([-+,])(\d*)/',$expectedArgumentCount,$argMatch);
  2686. //print_r($argMatch);
  2687. //echo PHP_EOL;
  2688. switch ($argMatch[2]) {
  2689. case '+' :
  2690. if ($argumentCount < $argMatch[1]) {
  2691. $argumentCountError = TRUE;
  2692. $expectedArgumentCountString = $argMatch[1].' or more ';
  2693. }
  2694. break;
  2695. case '-' :
  2696. if (($argumentCount < $argMatch[1]) || ($argumentCount > $argMatch[3])) {
  2697. $argumentCountError = TRUE;
  2698. $expectedArgumentCountString = 'between '.$argMatch[1].' and '.$argMatch[3];
  2699. }
  2700. break;
  2701. case ',' :
  2702. if (($argumentCount != $argMatch[1]) && ($argumentCount != $argMatch[3])) {
  2703. $argumentCountError = TRUE;
  2704. $expectedArgumentCountString = 'either '.$argMatch[1].' or '.$argMatch[3];
  2705. }
  2706. break;
  2707. }
  2708. }
  2709. if ($argumentCountError) {
  2710. return $this->_raiseFormulaError("Formula Error: Wrong number of arguments for $functionName() function: $argumentCount given, ".$expectedArgumentCountString." expected");
  2711. }
  2712. }
  2713. ++$index;
  2714. } elseif ($opCharacter == ',') { // Is this the separator for function arguments?
  2715. //echo 'Element is a Function argument separator',PHP_EOL;
  2716. while (($o2 = $stack->pop()) && $o2['value'] != '(') { // Pop off the stack back to the last (
  2717. if ($o2 === NULL) return $this->_raiseFormulaError("Formula Error: Unexpected ,");
  2718. else $output[] = $o2; // pop the argument expression stuff and push onto the output
  2719. }
  2720. // If we've a comma when we're expecting an operand, then what we actually have is a null operand;
  2721. // so push a null onto the stack
  2722. if (($expectingOperand) || (!$expectingOperator)) {
  2723. $output[] = array('type' => 'NULL Value', 'value' => self::$_ExcelConstants['NULL'], 'reference' => NULL);
  2724. }
  2725. // make sure there was a function
  2726. $d = $stack->last(2);
  2727. if (!preg_match('/^'.self::CALCULATION_REGEXP_FUNCTION.'$/i', $d['value'], $matches))
  2728. return $this->_raiseFormulaError("Formula Error: Unexpected ,");
  2729. $d = $stack->pop();
  2730. $stack->push($d['type'],++$d['value'],$d['reference']); // increment the argument count
  2731. $stack->push('Brace', '('); // put the ( back on, we'll need to pop back to it again
  2732. $expectingOperator = FALSE;
  2733. $expectingOperand = TRUE;
  2734. ++$index;
  2735. } elseif ($opCharacter == '(' && !$expectingOperator) {
  2736. // echo 'Element is an Opening Bracket<br />';
  2737. $stack->push('Brace', '(');
  2738. ++$index;
  2739. } elseif ($isOperandOrFunction && !$expectingOperator) { // do we now have a function/variable/number?
  2740. $expectingOperator = TRUE;
  2741. $expectingOperand = FALSE;
  2742. $val = $match[1];
  2743. $length = strlen($val);
  2744. // echo 'Element with value '.$val.' is an Operand, Variable, Constant, String, Number, Cell Reference or Function<br />';
  2745. if (preg_match('/^'.self::CALCULATION_REGEXP_FUNCTION.'$/i', $val, $matches)) {
  2746. $val = preg_replace('/\s/u','',$val);
  2747. // echo 'Element '.$val.' is a Function<br />';
  2748. if (isset(self::$_PHPExcelFunctions[strtoupper($matches[1])]) || isset(self::$_controlFunctions[strtoupper($matches[1])])) { // it's a function
  2749. $stack->push('Function', strtoupper($val));
  2750. $ax = preg_match('/^\s*(\s*\))/ui', substr($formula, $index+$length), $amatch);
  2751. if ($ax) {
  2752. $stack->push('Operand Count for Function '.strtoupper($val).')', 0);
  2753. $expectingOperator = TRUE;
  2754. } else {
  2755. $stack->push('Operand Count for Function '.strtoupper($val).')', 1);
  2756. $expectingOperator = FALSE;
  2757. }
  2758. $stack->push('Brace', '(');
  2759. } else { // it's a var w/ implicit multiplication
  2760. $output[] = array('type' => 'Value', 'value' => $matches[1], 'reference' => NULL);
  2761. }
  2762. } elseif (preg_match('/^'.self::CALCULATION_REGEXP_CELLREF.'$/i', $val, $matches)) {
  2763. // echo 'Element '.$val.' is a Cell reference<br />';
  2764. // Watch for this case-change when modifying to allow cell references in different worksheets...
  2765. // Should only be applied to the actual cell column, not the worksheet name
  2766. // If the last entry on the stack was a : operator, then we have a cell range reference
  2767. $testPrevOp = $stack->last(1);
  2768. if ($testPrevOp['value'] == ':') {
  2769. // If we have a worksheet reference, then we're playing with a 3D reference
  2770. if ($matches[2] == '') {
  2771. // Otherwise, we 'inherit' the worksheet reference from the start cell reference
  2772. // The start of the cell range reference should be the last entry in $output
  2773. $startCellRef = $output[count($output)-1]['value'];
  2774. preg_match('/^'.self::CALCULATION_REGEXP_CELLREF.'$/i', $startCellRef, $startMatches);
  2775. if ($startMatches[2] > '') {
  2776. $val = $startMatches[2].'!'.$val;
  2777. }
  2778. } else {
  2779. return $this->_raiseFormulaError("3D Range references are not yet supported");
  2780. }
  2781. }
  2782. $output[] = array('type' => 'Cell Reference', 'value' => $val, 'reference' => $val);
  2783. // $expectingOperator = FALSE;
  2784. } else { // it's a variable, constant, string, number or boolean
  2785. // echo 'Element is a Variable, Constant, String, Number or Boolean<br />';
  2786. // If the last entry on the stack was a : operator, then we may have a row or column range reference
  2787. $testPrevOp = $stack->last(1);
  2788. if ($testPrevOp['value'] == ':') {
  2789. $startRowColRef = $output[count($output)-1]['value'];
  2790. $rangeWS1 = '';
  2791. if (strpos('!',$startRowColRef) !== FALSE) {
  2792. list($rangeWS1,$startRowColRef) = explode('!',$startRowColRef);
  2793. }
  2794. if ($rangeWS1 != '') $rangeWS1 .= '!';
  2795. $rangeWS2 = $rangeWS1;
  2796. if (strpos('!',$val) !== FALSE) {
  2797. list($rangeWS2,$val) = explode('!',$val);
  2798. }
  2799. if ($rangeWS2 != '') $rangeWS2 .= '!';
  2800. if ((is_integer($startRowColRef)) && (ctype_digit($val)) &&
  2801. ($startRowColRef <= 1048576) && ($val <= 1048576)) {
  2802. // Row range
  2803. $endRowColRef = ($pCellParent !== NULL) ? $pCellParent->getHighestColumn() : 'XFD'; // Max 16,384 columns for Excel2007
  2804. $output[count($output)-1]['value'] = $rangeWS1.'A'.$startRowColRef;
  2805. $val = $rangeWS2.$endRowColRef.$val;
  2806. } elseif ((ctype_alpha($startRowColRef)) && (ctype_alpha($val)) &&
  2807. (strlen($startRowColRef) <= 3) && (strlen($val) <= 3)) {
  2808. // Column range
  2809. $endRowColRef = ($pCellParent !== NULL) ? $pCellParent->getHighestRow() : 1048576; // Max 1,048,576 rows for Excel2007
  2810. $output[count($output)-1]['value'] = $rangeWS1.strtoupper($startRowColRef).'1';
  2811. $val = $rangeWS2.$val.$endRowColRef;
  2812. }
  2813. }
  2814. $localeConstant = FALSE;
  2815. if ($opCharacter == '"') {
  2816. // echo 'Element is a String<br />';
  2817. // UnEscape any quotes within the string
  2818. $val = self::_wrapResult(str_replace('""','"',self::_unwrapResult($val)));
  2819. } elseif (is_numeric($val)) {
  2820. // echo 'Element is a Number<br />';
  2821. if ((strpos($val,'.') !== FALSE) || (stripos($val,'e') !== FALSE) || ($val > PHP_INT_MAX) || ($val < -PHP_INT_MAX)) {
  2822. // echo 'Casting '.$val.' to float<br />';
  2823. $val = (float) $val;
  2824. } else {
  2825. // echo 'Casting '.$val.' to integer<br />';
  2826. $val = (integer) $val;
  2827. }
  2828. } elseif (isset(self::$_ExcelConstants[trim(strtoupper($val))])) {
  2829. $excelConstant = trim(strtoupper($val));
  2830. // echo 'Element '.$excelConstant.' is an Excel Constant<br />';
  2831. $val = self::$_ExcelConstants[$excelConstant];
  2832. } elseif (($localeConstant = array_search(trim(strtoupper($val)), self::$_localeBoolean)) !== FALSE) {
  2833. // echo 'Element '.$localeConstant.' is an Excel Constant<br />';
  2834. $val = self::$_ExcelConstants[$localeConstant];
  2835. }
  2836. $details = array('type' => 'Value', 'value' => $val, 'reference' => NULL);
  2837. if ($localeConstant) { $details['localeValue'] = $localeConstant; }
  2838. $output[] = $details;
  2839. }
  2840. $index += $length;
  2841. } elseif ($opCharacter == '$') { // absolute row or column range
  2842. ++$index;
  2843. } elseif ($opCharacter == ')') { // miscellaneous error checking
  2844. if ($expectingOperand) {
  2845. $output[] = array('type' => 'NULL Value', 'value' => self::$_ExcelConstants['NULL'], 'reference' => NULL);
  2846. $expectingOperand = FALSE;
  2847. $expectingOperator = TRUE;
  2848. } else {
  2849. return $this->_raiseFormulaError("Formula Error: Unexpected ')'");
  2850. }
  2851. } elseif (isset(self::$_operators[$opCharacter]) && !$expectingOperator) {
  2852. return $this->_raiseFormulaError("Formula Error: Unexpected operator '$opCharacter'");
  2853. } else { // I don't even want to know what you did to get here
  2854. return $this->_raiseFormulaError("Formula Error: An unexpected error occured");
  2855. }
  2856. // Test for end of formula string
  2857. if ($index == strlen($formula)) {
  2858. // Did we end with an operator?.
  2859. // Only valid for the % unary operator
  2860. if ((isset(self::$_operators[$opCharacter])) && ($opCharacter != '%')) {
  2861. return $this->_raiseFormulaError("Formula Error: Operator '$opCharacter' has no operands");
  2862. } else {
  2863. break;
  2864. }
  2865. }
  2866. // Ignore white space
  2867. while (($formula{$index} == "\n") || ($formula{$index} == "\r")) {
  2868. ++$index;
  2869. }
  2870. if ($formula{$index} == ' ') {
  2871. while ($formula{$index} == ' ') {
  2872. ++$index;
  2873. }
  2874. // If we're expecting an operator, but only have a space between the previous and next operands (and both are
  2875. // Cell References) then we have an INTERSECTION operator
  2876. // echo 'Possible Intersect Operator<br />';
  2877. if (($expectingOperator) && (preg_match('/^'.self::CALCULATION_REGEXP_CELLREF.'.*/Ui', substr($formula, $index), $match)) &&
  2878. ($output[count($output)-1]['type'] == 'Cell Reference')) {
  2879. // echo 'Element is an Intersect Operator<br />';
  2880. while($stack->count() > 0 &&
  2881. ($o2 = $stack->last()) &&
  2882. isset(self::$_operators[$o2['value']]) &&
  2883. @(self::$_operatorAssociativity[$opCharacter] ? self::$_operatorPrecedence[$opCharacter] < self::$_operatorPrecedence[$o2['value']] : self::$_operatorPrecedence[$opCharacter] <= self::$_operatorPrecedence[$o2['value']])) {
  2884. $output[] = $stack->pop(); // Swap operands and higher precedence operators from the stack to the output
  2885. }
  2886. $stack->push('Binary Operator','|'); // Put an Intersect Operator on the stack
  2887. $expectingOperator = FALSE;
  2888. }
  2889. }
  2890. }
  2891. while (($op = $stack->pop()) !== NULL) { // pop everything off the stack and push onto output
  2892. if ((is_array($op) && $op['value'] == '(') || ($op === '('))
  2893. return $this->_raiseFormulaError("Formula Error: Expecting ')'"); // if there are any opening braces on the stack, then braces were unbalanced
  2894. $output[] = $op;
  2895. }
  2896. return $output;
  2897. } // function _parseFormula()
  2898. private static function _dataTestReference(&$operandData)
  2899. {
  2900. $operand = $operandData['value'];
  2901. if (($operandData['reference'] === NULL) && (is_array($operand))) {
  2902. $rKeys = array_keys($operand);
  2903. $rowKey = array_shift($rKeys);
  2904. $cKeys = array_keys(array_keys($operand[$rowKey]));
  2905. $colKey = array_shift($cKeys);
  2906. if (ctype_upper($colKey)) {
  2907. $operandData['reference'] = $colKey.$rowKey;
  2908. }
  2909. }
  2910. return $operand;
  2911. }
  2912. // evaluate postfix notation
  2913. private function _processTokenStack($tokens, $cellID = NULL, PHPExcel_Cell $pCell = NULL) {
  2914. if ($tokens == FALSE) return FALSE;
  2915. // If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent cell collection),
  2916. // so we store the parent cell collection so that we can re-attach it when necessary
  2917. $pCellWorksheet = ($pCell !== NULL) ? $pCell->getWorksheet() : NULL;
  2918. $pCellParent = ($pCell !== NULL) ? $pCell->getParent() : null;
  2919. $stack = new PHPExcel_Calculation_Token_Stack;
  2920. // Loop through each token in turn
  2921. foreach ($tokens as $tokenData) {
  2922. // print_r($tokenData);
  2923. // echo '<br />';
  2924. $token = $tokenData['value'];
  2925. // echo '<b>Token is '.$token.'</b><br />';
  2926. // if the token is a binary operator, pop the top two values off the stack, do the operation, and push the result back on the stack
  2927. if (isset(self::$_binaryOperators[$token])) {
  2928. // echo 'Token is a binary operator<br />';
  2929. // We must have two operands, error if we don't
  2930. if (($operand2Data = $stack->pop()) === NULL) return $this->_raiseFormulaError('Internal error - Operand value missing from stack');
  2931. if (($operand1Data = $stack->pop()) === NULL) return $this->_raiseFormulaError('Internal error - Operand value missing from stack');
  2932. $operand1 = self::_dataTestReference($operand1Data);
  2933. $operand2 = self::_dataTestReference($operand2Data);
  2934. // Log what we're doing
  2935. if ($token == ':') {
  2936. $this->_debugLog->writeDebugLog('Evaluating Range ', $this->_showValue($operand1Data['reference']), ' ', $token, ' ', $this->_showValue($operand2Data['reference']));
  2937. } else {
  2938. $this->_debugLog->writeDebugLog('Evaluating ', $this->_showValue($operand1), ' ', $token, ' ', $this->_showValue($operand2));
  2939. }
  2940. // Process the operation in the appropriate manner
  2941. switch ($token) {
  2942. // Comparison (Boolean) Operators
  2943. case '>' : // Greater than
  2944. case '<' : // Less than
  2945. case '>=' : // Greater than or Equal to
  2946. case '<=' : // Less than or Equal to
  2947. case '=' : // Equality
  2948. case '<>' : // Inequality
  2949. $this->_executeBinaryComparisonOperation($cellID,$operand1,$operand2,$token,$stack);
  2950. break;
  2951. // Binary Operators
  2952. case ':' : // Range
  2953. $sheet1 = $sheet2 = '';
  2954. if (strpos($operand1Data['reference'],'!') !== FALSE) {
  2955. list($sheet1,$operand1Data['reference']) = explode('!',$operand1Data['reference']);
  2956. } else {
  2957. $sheet1 = ($pCellParent !== NULL) ? $pCellWorksheet->getTitle() : '';
  2958. }
  2959. if (strpos($operand2Data['reference'],'!') !== FALSE) {
  2960. list($sheet2,$operand2Data['reference']) = explode('!',$operand2Data['reference']);
  2961. } else {
  2962. $sheet2 = $sheet1;
  2963. }
  2964. if ($sheet1 == $sheet2) {
  2965. if ($operand1Data['reference'] === NULL) {
  2966. if ((trim($operand1Data['value']) != '') && (is_numeric($operand1Data['value']))) {
  2967. $operand1Data['reference'] = $pCell->getColumn().$operand1Data['value'];
  2968. } elseif (trim($operand1Data['reference']) == '') {
  2969. $operand1Data['reference'] = $pCell->getCoordinate();
  2970. } else {
  2971. $operand1Data['reference'] = $operand1Data['value'].$pCell->getRow();
  2972. }
  2973. }
  2974. if ($operand2Data['reference'] === NULL) {
  2975. if ((trim($operand2Data['value']) != '') && (is_numeric($operand2Data['value']))) {
  2976. $operand2Data['reference'] = $pCell->getColumn().$operand2Data['value'];
  2977. } elseif (trim($operand2Data['reference']) == '') {
  2978. $operand2Data['reference'] = $pCell->getCoordinate();
  2979. } else {
  2980. $operand2Data['reference'] = $operand2Data['value'].$pCell->getRow();
  2981. }
  2982. }
  2983. $oData = array_merge(explode(':',$operand1Data['reference']),explode(':',$operand2Data['reference']));
  2984. $oCol = $oRow = array();
  2985. foreach($oData as $oDatum) {
  2986. $oCR = PHPExcel_Cell::coordinateFromString($oDatum);
  2987. $oCol[] = PHPExcel_Cell::columnIndexFromString($oCR[0]) - 1;
  2988. $oRow[] = $oCR[1];
  2989. }
  2990. $cellRef = PHPExcel_Cell::stringFromColumnIndex(min($oCol)).min($oRow).':'.PHPExcel_Cell::stringFromColumnIndex(max($oCol)).max($oRow);
  2991. if ($pCellParent !== NULL) {
  2992. $cellValue = $this->extractCellRange($cellRef, $this->_workbook->getSheetByName($sheet1), FALSE);
  2993. } else {
  2994. return $this->_raiseFormulaError('Unable to access Cell Reference');
  2995. }
  2996. $stack->push('Cell Reference',$cellValue,$cellRef);
  2997. } else {
  2998. $stack->push('Error',PHPExcel_Calculation_Functions::REF(),NULL);
  2999. }
  3000. break;
  3001. case '+' : // Addition
  3002. $this->_executeNumericBinaryOperation($cellID,$operand1,$operand2,$token,'plusEquals',$stack);
  3003. break;
  3004. case '-' : // Subtraction
  3005. $this->_executeNumericBinaryOperation($cellID,$operand1,$operand2,$token,'minusEquals',$stack);
  3006. break;
  3007. case '*' : // Multiplication
  3008. $this->_executeNumericBinaryOperation($cellID,$operand1,$operand2,$token,'arrayTimesEquals',$stack);
  3009. break;
  3010. case '/' : // Division
  3011. $this->_executeNumericBinaryOperation($cellID,$operand1,$operand2,$token,'arrayRightDivide',$stack);
  3012. break;
  3013. case '^' : // Exponential
  3014. $this->_executeNumericBinaryOperation($cellID,$operand1,$operand2,$token,'power',$stack);
  3015. break;
  3016. case '&' : // Concatenation
  3017. // If either of the operands is a matrix, we need to treat them both as matrices
  3018. // (converting the other operand to a matrix if need be); then perform the required
  3019. // matrix operation
  3020. if (is_bool($operand1)) {
  3021. $operand1 = ($operand1) ? self::$_localeBoolean['TRUE'] : self::$_localeBoolean['FALSE'];
  3022. }
  3023. if (is_bool($operand2)) {
  3024. $operand2 = ($operand2) ? self::$_localeBoolean['TRUE'] : self::$_localeBoolean['FALSE'];
  3025. }
  3026. if ((is_array($operand1)) || (is_array($operand2))) {
  3027. // Ensure that both operands are arrays/matrices
  3028. self::_checkMatrixOperands($operand1,$operand2,2);
  3029. try {
  3030. // Convert operand 1 from a PHP array to a matrix
  3031. $matrix = new PHPExcel_Shared_JAMA_Matrix($operand1);
  3032. // Perform the required operation against the operand 1 matrix, passing in operand 2
  3033. $matrixResult = $matrix->concat($operand2);
  3034. $result = $matrixResult->getArray();
  3035. } catch (PHPExcel_Exception $ex) {
  3036. $this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
  3037. $result = '#VALUE!';
  3038. }
  3039. } else {
  3040. $result = '"'.str_replace('""','"',self::_unwrapResult($operand1,'"').self::_unwrapResult($operand2,'"')).'"';
  3041. }
  3042. $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result));
  3043. $stack->push('Value',$result);
  3044. break;
  3045. case '|' : // Intersect
  3046. $rowIntersect = array_intersect_key($operand1,$operand2);
  3047. $cellIntersect = $oCol = $oRow = array();
  3048. foreach(array_keys($rowIntersect) as $row) {
  3049. $oRow[] = $row;
  3050. foreach($rowIntersect[$row] as $col => $data) {
  3051. $oCol[] = PHPExcel_Cell::columnIndexFromString($col) - 1;
  3052. $cellIntersect[$row] = array_intersect_key($operand1[$row],$operand2[$row]);
  3053. }
  3054. }
  3055. $cellRef = PHPExcel_Cell::stringFromColumnIndex(min($oCol)).min($oRow).':'.PHPExcel_Cell::stringFromColumnIndex(max($oCol)).max($oRow);
  3056. $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($cellIntersect));
  3057. $stack->push('Value',$cellIntersect,$cellRef);
  3058. break;
  3059. }
  3060. // if the token is a unary operator, pop one value off the stack, do the operation, and push it back on
  3061. } elseif (($token === '~') || ($token === '%')) {
  3062. // echo 'Token is a unary operator<br />';
  3063. if (($arg = $stack->pop()) === NULL) return $this->_raiseFormulaError('Internal error - Operand value missing from stack');
  3064. $arg = $arg['value'];
  3065. if ($token === '~') {
  3066. // echo 'Token is a negation operator<br />';
  3067. $this->_debugLog->writeDebugLog('Evaluating Negation of ', $this->_showValue($arg));
  3068. $multiplier = -1;
  3069. } else {
  3070. // echo 'Token is a percentile operator<br />';
  3071. $this->_debugLog->writeDebugLog('Evaluating Percentile of ', $this->_showValue($arg));
  3072. $multiplier = 0.01;
  3073. }
  3074. if (is_array($arg)) {
  3075. self::_checkMatrixOperands($arg,$multiplier,2);
  3076. try {
  3077. $matrix1 = new PHPExcel_Shared_JAMA_Matrix($arg);
  3078. $matrixResult = $matrix1->arrayTimesEquals($multiplier);
  3079. $result = $matrixResult->getArray();
  3080. } catch (PHPExcel_Exception $ex) {
  3081. $this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
  3082. $result = '#VALUE!';
  3083. }
  3084. $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result));
  3085. $stack->push('Value',$result);
  3086. } else {
  3087. $this->_executeNumericBinaryOperation($cellID,$multiplier,$arg,'*','arrayTimesEquals',$stack);
  3088. }
  3089. } elseif (preg_match('/^'.self::CALCULATION_REGEXP_CELLREF.'$/i', $token, $matches)) {
  3090. $cellRef = NULL;
  3091. // echo 'Element '.$token.' is a Cell reference<br />';
  3092. if (isset($matches[8])) {
  3093. // echo 'Reference is a Range of cells<br />';
  3094. if ($pCell === NULL) {
  3095. // We can't access the range, so return a REF error
  3096. $cellValue = PHPExcel_Calculation_Functions::REF();
  3097. } else {
  3098. $cellRef = $matches[6].$matches[7].':'.$matches[9].$matches[10];
  3099. if ($matches[2] > '') {
  3100. $matches[2] = trim($matches[2],"\"'");
  3101. if ((strpos($matches[2],'[') !== FALSE) || (strpos($matches[2],']') !== FALSE)) {
  3102. // It's a Reference to an external workbook (not currently supported)
  3103. return $this->_raiseFormulaError('Unable to access External Workbook');
  3104. }
  3105. $matches[2] = trim($matches[2],"\"'");
  3106. // echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />';
  3107. $this->_debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in worksheet ', $matches[2]);
  3108. if ($pCellParent !== NULL) {
  3109. $cellValue = $this->extractCellRange($cellRef, $this->_workbook->getSheetByName($matches[2]), FALSE);
  3110. } else {
  3111. return $this->_raiseFormulaError('Unable to access Cell Reference');
  3112. }
  3113. $this->_debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->_showTypeDetails($cellValue));
  3114. // $cellRef = $matches[2].'!'.$cellRef;
  3115. } else {
  3116. // echo '$cellRef='.$cellRef.' in current worksheet<br />';
  3117. $this->_debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in current worksheet');
  3118. if ($pCellParent !== NULL) {
  3119. $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, FALSE);
  3120. } else {
  3121. return $this->_raiseFormulaError('Unable to access Cell Reference');
  3122. }
  3123. $this->_debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' is ', $this->_showTypeDetails($cellValue));
  3124. }
  3125. }
  3126. } else {
  3127. // echo 'Reference is a single Cell<br />';
  3128. if ($pCell === NULL) {
  3129. // We can't access the cell, so return a REF error
  3130. $cellValue = PHPExcel_Calculation_Functions::REF();
  3131. } else {
  3132. $cellRef = $matches[6].$matches[7];
  3133. if ($matches[2] > '') {
  3134. $matches[2] = trim($matches[2],"\"'");
  3135. if ((strpos($matches[2],'[') !== FALSE) || (strpos($matches[2],']') !== FALSE)) {
  3136. // It's a Reference to an external workbook (not currently supported)
  3137. return $this->_raiseFormulaError('Unable to access External Workbook');
  3138. }
  3139. // echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />';
  3140. $this->_debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in worksheet ', $matches[2]);
  3141. if ($pCellParent !== NULL) {
  3142. $cellSheet = $this->_workbook->getSheetByName($matches[2]);
  3143. if ($cellSheet && $cellSheet->cellExists($cellRef)) {
  3144. $cellValue = $this->extractCellRange($cellRef, $this->_workbook->getSheetByName($matches[2]), FALSE);
  3145. $pCell->attach($pCellParent);
  3146. } else {
  3147. $cellValue = NULL;
  3148. }
  3149. } else {
  3150. return $this->_raiseFormulaError('Unable to access Cell Reference');
  3151. }
  3152. $this->_debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->_showTypeDetails($cellValue));
  3153. // $cellRef = $matches[2].'!'.$cellRef;
  3154. } else {
  3155. // echo '$cellRef='.$cellRef.' in current worksheet<br />';
  3156. $this->_debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in current worksheet');
  3157. if ($pCellParent->isDataSet($cellRef)) {
  3158. $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, FALSE);
  3159. $pCell->attach($pCellParent);
  3160. } else {
  3161. $cellValue = NULL;
  3162. }
  3163. $this->_debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' is ', $this->_showTypeDetails($cellValue));
  3164. }
  3165. }
  3166. }
  3167. $stack->push('Value',$cellValue,$cellRef);
  3168. // if the token is a function, pop arguments off the stack, hand them to the function, and push the result back on
  3169. } elseif (preg_match('/^'.self::CALCULATION_REGEXP_FUNCTION.'$/i', $token, $matches)) {
  3170. // echo 'Token is a function<br />';
  3171. $functionName = $matches[1];
  3172. $argCount = $stack->pop();
  3173. $argCount = $argCount['value'];
  3174. if ($functionName != 'MKMATRIX') {
  3175. $this->_debugLog->writeDebugLog('Evaluating Function ', self::_localeFunc($functionName), '() with ', (($argCount == 0) ? 'no' : $argCount), ' argument', (($argCount == 1) ? '' : 's'));
  3176. }
  3177. if ((isset(self::$_PHPExcelFunctions[$functionName])) || (isset(self::$_controlFunctions[$functionName]))) { // function
  3178. if (isset(self::$_PHPExcelFunctions[$functionName])) {
  3179. $functionCall = self::$_PHPExcelFunctions[$functionName]['functionCall'];
  3180. $passByReference = isset(self::$_PHPExcelFunctions[$functionName]['passByReference']);
  3181. $passCellReference = isset(self::$_PHPExcelFunctions[$functionName]['passCellReference']);
  3182. } elseif (isset(self::$_controlFunctions[$functionName])) {
  3183. $functionCall = self::$_controlFunctions[$functionName]['functionCall'];
  3184. $passByReference = isset(self::$_controlFunctions[$functionName]['passByReference']);
  3185. $passCellReference = isset(self::$_controlFunctions[$functionName]['passCellReference']);
  3186. }
  3187. // get the arguments for this function
  3188. // echo 'Function '.$functionName.' expects '.$argCount.' arguments<br />';
  3189. $args = $argArrayVals = array();
  3190. for ($i = 0; $i < $argCount; ++$i) {
  3191. $arg = $stack->pop();
  3192. $a = $argCount - $i - 1;
  3193. if (($passByReference) &&
  3194. (isset(self::$_PHPExcelFunctions[$functionName]['passByReference'][$a])) &&
  3195. (self::$_PHPExcelFunctions[$functionName]['passByReference'][$a])) {
  3196. if ($arg['reference'] === NULL) {
  3197. $args[] = $cellID;
  3198. if ($functionName != 'MKMATRIX') { $argArrayVals[] = $this->_showValue($cellID); }
  3199. } else {
  3200. $args[] = $arg['reference'];
  3201. if ($functionName != 'MKMATRIX') { $argArrayVals[] = $this->_showValue($arg['reference']); }
  3202. }
  3203. } else {
  3204. $args[] = self::_unwrapResult($arg['value']);
  3205. if ($functionName != 'MKMATRIX') { $argArrayVals[] = $this->_showValue($arg['value']); }
  3206. }
  3207. }
  3208. // Reverse the order of the arguments
  3209. krsort($args);
  3210. if (($passByReference) && ($argCount == 0)) {
  3211. $args[] = $cellID;
  3212. $argArrayVals[] = $this->_showValue($cellID);
  3213. }
  3214. // echo 'Arguments are: ';
  3215. // print_r($args);
  3216. // echo '<br />';
  3217. if ($functionName != 'MKMATRIX') {
  3218. if ($this->_debugLog->getWriteDebugLog()) {
  3219. krsort($argArrayVals);
  3220. $this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', implode(self::$_localeArgumentSeparator.' ',PHPExcel_Calculation_Functions::flattenArray($argArrayVals)), ' )');
  3221. }
  3222. }
  3223. // Process each argument in turn, building the return value as an array
  3224. // if (($argCount == 1) && (is_array($args[1])) && ($functionName != 'MKMATRIX')) {
  3225. // $operand1 = $args[1];
  3226. // $this->_debugLog->writeDebugLog('Argument is a matrix: ', $this->_showValue($operand1));
  3227. // $result = array();
  3228. // $row = 0;
  3229. // foreach($operand1 as $args) {
  3230. // if (is_array($args)) {
  3231. // foreach($args as $arg) {
  3232. // $this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', $this->_showValue($arg), ' )');
  3233. // $r = call_user_func_array($functionCall,$arg);
  3234. // $this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($r));
  3235. // $result[$row][] = $r;
  3236. // }
  3237. // ++$row;
  3238. // } else {
  3239. // $this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', $this->_showValue($args), ' )');
  3240. // $r = call_user_func_array($functionCall,$args);
  3241. // $this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($r));
  3242. // $result[] = $r;
  3243. // }
  3244. // }
  3245. // } else {
  3246. // Process the argument with the appropriate function call
  3247. if ($passCellReference) {
  3248. $args[] = $pCell;
  3249. }
  3250. if (strpos($functionCall,'::') !== FALSE) {
  3251. $result = call_user_func_array(explode('::',$functionCall),$args);
  3252. } else {
  3253. foreach($args as &$arg) {
  3254. $arg = PHPExcel_Calculation_Functions::flattenSingleValue($arg);
  3255. }
  3256. unset($arg);
  3257. $result = call_user_func_array($functionCall,$args);
  3258. }
  3259. // }
  3260. if ($functionName != 'MKMATRIX') {
  3261. $this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($result));
  3262. }
  3263. $stack->push('Value',self::_wrapResult($result));
  3264. }
  3265. } else {
  3266. // if the token is a number, boolean, string or an Excel error, push it onto the stack
  3267. if (isset(self::$_ExcelConstants[strtoupper($token)])) {
  3268. $excelConstant = strtoupper($token);
  3269. // echo 'Token is a PHPExcel constant: '.$excelConstant.'<br />';
  3270. $stack->push('Constant Value',self::$_ExcelConstants[$excelConstant]);
  3271. $this->_debugLog->writeDebugLog('Evaluating Constant ', $excelConstant, ' as ', $this->_showTypeDetails(self::$_ExcelConstants[$excelConstant]));
  3272. } elseif ((is_numeric($token)) || ($token === NULL) || (is_bool($token)) || ($token == '') || ($token{0} == '"') || ($token{0} == '#')) {
  3273. // echo 'Token is a number, boolean, string, null or an Excel error<br />';
  3274. $stack->push('Value',$token);
  3275. // if the token is a named range, push the named range name onto the stack
  3276. } elseif (preg_match('/^'.self::CALCULATION_REGEXP_NAMEDRANGE.'$/i', $token, $matches)) {
  3277. // echo 'Token is a named range<br />';
  3278. $namedRange = $matches[6];
  3279. // echo 'Named Range is '.$namedRange.'<br />';
  3280. $this->_debugLog->writeDebugLog('Evaluating Named Range ', $namedRange);
  3281. $cellValue = $this->extractNamedRange($namedRange, ((NULL !== $pCell) ? $pCellWorksheet : NULL), FALSE);
  3282. $pCell->attach($pCellParent);
  3283. $this->_debugLog->writeDebugLog('Evaluation Result for named range ', $namedRange, ' is ', $this->_showTypeDetails($cellValue));
  3284. $stack->push('Named Range',$cellValue,$namedRange);
  3285. } else {
  3286. return $this->_raiseFormulaError("undefined variable '$token'");
  3287. }
  3288. }
  3289. }
  3290. // when we're out of tokens, the stack should have a single element, the final result
  3291. if ($stack->count() != 1) return $this->_raiseFormulaError("internal error");
  3292. $output = $stack->pop();
  3293. $output = $output['value'];
  3294. // if ((is_array($output)) && (self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY)) {
  3295. // return array_shift(PHPExcel_Calculation_Functions::flattenArray($output));
  3296. // }
  3297. return $output;
  3298. } // function _processTokenStack()
  3299. private function _validateBinaryOperand($cellID, &$operand, &$stack) {
  3300. if (is_array($operand)) {
  3301. if ((count($operand, COUNT_RECURSIVE) - count($operand)) == 1) {
  3302. do {
  3303. $operand = array_pop($operand);
  3304. } while (is_array($operand));
  3305. }
  3306. }
  3307. // Numbers, matrices and booleans can pass straight through, as they're already valid
  3308. if (is_string($operand)) {
  3309. // We only need special validations for the operand if it is a string
  3310. // Start by stripping off the quotation marks we use to identify true excel string values internally
  3311. if ($operand > '' && $operand{0} == '"') { $operand = self::_unwrapResult($operand); }
  3312. // If the string is a numeric value, we treat it as a numeric, so no further testing
  3313. if (!is_numeric($operand)) {
  3314. // If not a numeric, test to see if the value is an Excel error, and so can't be used in normal binary operations
  3315. if ($operand > '' && $operand{0} == '#') {
  3316. $stack->push('Value', $operand);
  3317. $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($operand));
  3318. return FALSE;
  3319. } elseif (!PHPExcel_Shared_String::convertToNumberIfFraction($operand)) {
  3320. // If not a numeric or a fraction, then it's a text string, and so can't be used in mathematical binary operations
  3321. $stack->push('Value', '#VALUE!');
  3322. $this->_debugLog->writeDebugLog('Evaluation Result is a ', $this->_showTypeDetails('#VALUE!'));
  3323. return FALSE;
  3324. }
  3325. }
  3326. }
  3327. // return a true if the value of the operand is one that we can use in normal binary operations
  3328. return TRUE;
  3329. } // function _validateBinaryOperand()
  3330. private function _executeBinaryComparisonOperation($cellID, $operand1, $operand2, $operation, &$stack, $recursingArrays=FALSE) {
  3331. // If we're dealing with matrix operations, we want a matrix result
  3332. if ((is_array($operand1)) || (is_array($operand2))) {
  3333. $result = array();
  3334. if ((is_array($operand1)) && (!is_array($operand2))) {
  3335. foreach($operand1 as $x => $operandData) {
  3336. $this->_debugLog->writeDebugLog('Evaluating Comparison ', $this->_showValue($operandData), ' ', $operation, ' ', $this->_showValue($operand2));
  3337. $this->_executeBinaryComparisonOperation($cellID,$operandData,$operand2,$operation,$stack);
  3338. $r = $stack->pop();
  3339. $result[$x] = $r['value'];
  3340. }
  3341. } elseif ((!is_array($operand1)) && (is_array($operand2))) {
  3342. foreach($operand2 as $x => $operandData) {
  3343. $this->_debugLog->writeDebugLog('Evaluating Comparison ', $this->_showValue($operand1), ' ', $operation, ' ', $this->_showValue($operandData));
  3344. $this->_executeBinaryComparisonOperation($cellID,$operand1,$operandData,$operation,$stack);
  3345. $r = $stack->pop();
  3346. $result[$x] = $r['value'];
  3347. }
  3348. } else {
  3349. if (!$recursingArrays) { self::_checkMatrixOperands($operand1,$operand2,2); }
  3350. foreach($operand1 as $x => $operandData) {
  3351. $this->_debugLog->writeDebugLog('Evaluating Comparison ', $this->_showValue($operandData), ' ', $operation, ' ', $this->_showValue($operand2[$x]));
  3352. $this->_executeBinaryComparisonOperation($cellID,$operandData,$operand2[$x],$operation,$stack,TRUE);
  3353. $r = $stack->pop();
  3354. $result[$x] = $r['value'];
  3355. }
  3356. }
  3357. // Log the result details
  3358. $this->_debugLog->writeDebugLog('Comparison Evaluation Result is ', $this->_showTypeDetails($result));
  3359. // And push the result onto the stack
  3360. $stack->push('Array',$result);
  3361. return TRUE;
  3362. }
  3363. // Simple validate the two operands if they are string values
  3364. if (is_string($operand1) && $operand1 > '' && $operand1{0} == '"') { $operand1 = self::_unwrapResult($operand1); }
  3365. if (is_string($operand2) && $operand2 > '' && $operand2{0} == '"') { $operand2 = self::_unwrapResult($operand2); }
  3366. // Use case insensitive comparaison if not OpenOffice mode
  3367. if (PHPExcel_Calculation_Functions::getCompatibilityMode() != PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE)
  3368. {
  3369. if (is_string($operand1)) {
  3370. $operand1 = strtoupper($operand1);
  3371. }
  3372. if (is_string($operand2)) {
  3373. $operand2 = strtoupper($operand2);
  3374. }
  3375. }
  3376. $useLowercaseFirstComparison = is_string($operand1) && is_string($operand2) && PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE;
  3377. // execute the necessary operation
  3378. switch ($operation) {
  3379. // Greater than
  3380. case '>':
  3381. if ($useLowercaseFirstComparison) {
  3382. $result = $this->strcmpLowercaseFirst($operand1, $operand2) > 0;
  3383. } else {
  3384. $result = ($operand1 > $operand2);
  3385. }
  3386. break;
  3387. // Less than
  3388. case '<':
  3389. if ($useLowercaseFirstComparison) {
  3390. $result = $this->strcmpLowercaseFirst($operand1, $operand2) < 0;
  3391. } else {
  3392. $result = ($operand1 < $operand2);
  3393. }
  3394. break;
  3395. // Equality
  3396. case '=':
  3397. if (is_numeric($operand1) && is_numeric($operand2)) {
  3398. $result = (abs($operand1 - $operand2) < $this->delta);
  3399. } else {
  3400. $result = strcmp($operand1, $operand2) == 0;
  3401. }
  3402. break;
  3403. // Greater than or equal
  3404. case '>=':
  3405. if (is_numeric($operand1) && is_numeric($operand2)) {
  3406. $result = ((abs($operand1 - $operand2) < $this->delta) || ($operand1 > $operand2));
  3407. } elseif ($useLowercaseFirstComparison) {
  3408. $result = $this->strcmpLowercaseFirst($operand1, $operand2) >= 0;
  3409. } else {
  3410. $result = strcmp($operand1, $operand2) >= 0;
  3411. }
  3412. break;
  3413. // Less than or equal
  3414. case '<=':
  3415. if (is_numeric($operand1) && is_numeric($operand2)) {
  3416. $result = ((abs($operand1 - $operand2) < $this->delta) || ($operand1 < $operand2));
  3417. } elseif ($useLowercaseFirstComparison) {
  3418. $result = $this->strcmpLowercaseFirst($operand1, $operand2) <= 0;
  3419. } else {
  3420. $result = strcmp($operand1, $operand2) <= 0;
  3421. }
  3422. break;
  3423. // Inequality
  3424. case '<>':
  3425. if (is_numeric($operand1) && is_numeric($operand2)) {
  3426. $result = (abs($operand1 - $operand2) > 1E-14);
  3427. } else {
  3428. $result = strcmp($operand1, $operand2) != 0;
  3429. }
  3430. break;
  3431. }
  3432. // Log the result details
  3433. $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result));
  3434. // And push the result onto the stack
  3435. $stack->push('Value',$result);
  3436. return true;
  3437. }
  3438. /**
  3439. * Compare two strings in the same way as strcmp() except that lowercase come before uppercase letters
  3440. * @param string $str1 First string value for the comparison
  3441. * @param string $str2 Second string value for the comparison
  3442. * @return integer
  3443. */
  3444. private function strcmpLowercaseFirst($str1, $str2)
  3445. {
  3446. $inversedStr1 = PHPExcel_Shared_String::StrCaseReverse($str1);
  3447. $inversedStr2 = PHPExcel_Shared_String::StrCaseReverse($str2);
  3448. return strcmp($inversedStr1, $inversedStr2);
  3449. }
  3450. private function _executeNumericBinaryOperation($cellID,$operand1,$operand2,$operation,$matrixFunction,&$stack) {
  3451. // Validate the two operands
  3452. if (!$this->_validateBinaryOperand($cellID,$operand1,$stack)) return FALSE;
  3453. if (!$this->_validateBinaryOperand($cellID,$operand2,$stack)) return FALSE;
  3454. // If either of the operands is a matrix, we need to treat them both as matrices
  3455. // (converting the other operand to a matrix if need be); then perform the required
  3456. // matrix operation
  3457. if ((is_array($operand1)) || (is_array($operand2))) {
  3458. // Ensure that both operands are arrays/matrices of the same size
  3459. self::_checkMatrixOperands($operand1, $operand2, 2);
  3460. try {
  3461. // Convert operand 1 from a PHP array to a matrix
  3462. $matrix = new PHPExcel_Shared_JAMA_Matrix($operand1);
  3463. // Perform the required operation against the operand 1 matrix, passing in operand 2
  3464. $matrixResult = $matrix->$matrixFunction($operand2);
  3465. $result = $matrixResult->getArray();
  3466. } catch (PHPExcel_Exception $ex) {
  3467. $this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
  3468. $result = '#VALUE!';
  3469. }
  3470. } else {
  3471. if ((PHPExcel_Calculation_Functions::getCompatibilityMode() != PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) &&
  3472. ((is_string($operand1) && !is_numeric($operand1) && strlen($operand1)>0) ||
  3473. (is_string($operand2) && !is_numeric($operand2) && strlen($operand2)>0))) {
  3474. $result = PHPExcel_Calculation_Functions::VALUE();
  3475. } else {
  3476. // If we're dealing with non-matrix operations, execute the necessary operation
  3477. switch ($operation) {
  3478. // Addition
  3479. case '+':
  3480. $result = $operand1 + $operand2;
  3481. break;
  3482. // Subtraction
  3483. case '-':
  3484. $result = $operand1 - $operand2;
  3485. break;
  3486. // Multiplication
  3487. case '*':
  3488. $result = $operand1 * $operand2;
  3489. break;
  3490. // Division
  3491. case '/':
  3492. if ($operand2 == 0) {
  3493. // Trap for Divide by Zero error
  3494. $stack->push('Value','#DIV/0!');
  3495. $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails('#DIV/0!'));
  3496. return FALSE;
  3497. } else {
  3498. $result = $operand1 / $operand2;
  3499. }
  3500. break;
  3501. // Power
  3502. case '^':
  3503. $result = pow($operand1, $operand2);
  3504. break;
  3505. }
  3506. }
  3507. }
  3508. // Log the result details
  3509. $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result));
  3510. // And push the result onto the stack
  3511. $stack->push('Value',$result);
  3512. return TRUE;
  3513. } // function _executeNumericBinaryOperation()
  3514. // trigger an error, but nicely, if need be
  3515. protected function _raiseFormulaError($errorMessage) {
  3516. $this->formulaError = $errorMessage;
  3517. $this->_cyclicReferenceStack->clear();
  3518. if (!$this->suppressFormulaErrors) throw new PHPExcel_Calculation_Exception($errorMessage);
  3519. trigger_error($errorMessage, E_USER_ERROR);
  3520. } // function _raiseFormulaError()
  3521. /**
  3522. * Extract range values
  3523. *
  3524. * @param string &$pRange String based range representation
  3525. * @param PHPExcel_Worksheet $pSheet Worksheet
  3526. * @param boolean $resetLog Flag indicating whether calculation log should be reset or not
  3527. * @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned.
  3528. * @throws PHPExcel_Calculation_Exception
  3529. */
  3530. public function extractCellRange(&$pRange = 'A1', PHPExcel_Worksheet $pSheet = NULL, $resetLog = TRUE) {
  3531. // Return value
  3532. $returnValue = array ();
  3533. // echo 'extractCellRange('.$pRange.')',PHP_EOL;
  3534. if ($pSheet !== NULL) {
  3535. $pSheetName = $pSheet->getTitle();
  3536. // echo 'Passed sheet name is '.$pSheetName.PHP_EOL;
  3537. // echo 'Range reference is '.$pRange.PHP_EOL;
  3538. if (strpos ($pRange, '!') !== false) {
  3539. // echo '$pRange reference includes sheet reference',PHP_EOL;
  3540. list($pSheetName,$pRange) = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
  3541. // echo 'New sheet name is '.$pSheetName,PHP_EOL;
  3542. // echo 'Adjusted Range reference is '.$pRange,PHP_EOL;
  3543. $pSheet = $this->_workbook->getSheetByName($pSheetName);
  3544. }
  3545. // Extract range
  3546. $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
  3547. $pRange = $pSheetName.'!'.$pRange;
  3548. if (!isset($aReferences[1])) {
  3549. // Single cell in range
  3550. sscanf($aReferences[0],'%[A-Z]%d', $currentCol, $currentRow);
  3551. $cellValue = NULL;
  3552. if ($pSheet->cellExists($aReferences[0])) {
  3553. $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
  3554. } else {
  3555. $returnValue[$currentRow][$currentCol] = NULL;
  3556. }
  3557. } else {
  3558. // Extract cell data for all cells in the range
  3559. foreach ($aReferences as $reference) {
  3560. // Extract range
  3561. sscanf($reference,'%[A-Z]%d', $currentCol, $currentRow);
  3562. $cellValue = NULL;
  3563. if ($pSheet->cellExists($reference)) {
  3564. $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
  3565. } else {
  3566. $returnValue[$currentRow][$currentCol] = NULL;
  3567. }
  3568. }
  3569. }
  3570. }
  3571. // Return
  3572. return $returnValue;
  3573. } // function extractCellRange()
  3574. /**
  3575. * Extract range values
  3576. *
  3577. * @param string &$pRange String based range representation
  3578. * @param PHPExcel_Worksheet $pSheet Worksheet
  3579. * @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned.
  3580. * @param boolean $resetLog Flag indicating whether calculation log should be reset or not
  3581. * @throws PHPExcel_Calculation_Exception
  3582. */
  3583. public function extractNamedRange(&$pRange = 'A1', PHPExcel_Worksheet $pSheet = NULL, $resetLog = TRUE) {
  3584. // Return value
  3585. $returnValue = array ();
  3586. // echo 'extractNamedRange('.$pRange.')<br />';
  3587. if ($pSheet !== NULL) {
  3588. $pSheetName = $pSheet->getTitle();
  3589. // echo 'Current sheet name is '.$pSheetName.'<br />';
  3590. // echo 'Range reference is '.$pRange.'<br />';
  3591. if (strpos ($pRange, '!') !== false) {
  3592. // echo '$pRange reference includes sheet reference',PHP_EOL;
  3593. list($pSheetName,$pRange) = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
  3594. // echo 'New sheet name is '.$pSheetName,PHP_EOL;
  3595. // echo 'Adjusted Range reference is '.$pRange,PHP_EOL;
  3596. $pSheet = $this->_workbook->getSheetByName($pSheetName);
  3597. }
  3598. // Named range?
  3599. $namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
  3600. if ($namedRange !== NULL) {
  3601. $pSheet = $namedRange->getWorksheet();
  3602. // echo 'Named Range '.$pRange.' (';
  3603. $pRange = $namedRange->getRange();
  3604. $splitRange = PHPExcel_Cell::splitRange($pRange);
  3605. // Convert row and column references
  3606. if (ctype_alpha($splitRange[0][0])) {
  3607. $pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow();
  3608. } elseif(ctype_digit($splitRange[0][0])) {
  3609. $pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1];
  3610. }
  3611. // echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />';
  3612. // if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) {
  3613. // if (!$namedRange->getLocalOnly()) {
  3614. // $pSheet = $namedRange->getWorksheet();
  3615. // } else {
  3616. // return $returnValue;
  3617. // }
  3618. // }
  3619. } else {
  3620. return PHPExcel_Calculation_Functions::REF();
  3621. }
  3622. // Extract range
  3623. $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
  3624. // var_dump($aReferences);
  3625. if (!isset($aReferences[1])) {
  3626. // Single cell (or single column or row) in range
  3627. list($currentCol,$currentRow) = PHPExcel_Cell::coordinateFromString($aReferences[0]);
  3628. $cellValue = NULL;
  3629. if ($pSheet->cellExists($aReferences[0])) {
  3630. $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
  3631. } else {
  3632. $returnValue[$currentRow][$currentCol] = NULL;
  3633. }
  3634. } else {
  3635. // Extract cell data for all cells in the range
  3636. foreach ($aReferences as $reference) {
  3637. // Extract range
  3638. list($currentCol,$currentRow) = PHPExcel_Cell::coordinateFromString($reference);
  3639. // echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />';
  3640. $cellValue = NULL;
  3641. if ($pSheet->cellExists($reference)) {
  3642. $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
  3643. } else {
  3644. $returnValue[$currentRow][$currentCol] = NULL;
  3645. }
  3646. }
  3647. }
  3648. // print_r($returnValue);
  3649. // echo '<br />';
  3650. }
  3651. // Return
  3652. return $returnValue;
  3653. } // function extractNamedRange()
  3654. /**
  3655. * Is a specific function implemented?
  3656. *
  3657. * @param string $pFunction Function Name
  3658. * @return boolean
  3659. */
  3660. public function isImplemented($pFunction = '') {
  3661. $pFunction = strtoupper ($pFunction);
  3662. if (isset(self::$_PHPExcelFunctions[$pFunction])) {
  3663. return (self::$_PHPExcelFunctions[$pFunction]['functionCall'] != 'PHPExcel_Calculation_Functions::DUMMY');
  3664. } else {
  3665. return FALSE;
  3666. }
  3667. } // function isImplemented()
  3668. /**
  3669. * Get a list of all implemented functions as an array of function objects
  3670. *
  3671. * @return array of PHPExcel_Calculation_Function
  3672. */
  3673. public function listFunctions() {
  3674. // Return value
  3675. $returnValue = array();
  3676. // Loop functions
  3677. foreach(self::$_PHPExcelFunctions as $functionName => $function) {
  3678. if ($function['functionCall'] != 'PHPExcel_Calculation_Functions::DUMMY') {
  3679. $returnValue[$functionName] = new PHPExcel_Calculation_Function($function['category'],
  3680. $functionName,
  3681. $function['functionCall']
  3682. );
  3683. }
  3684. }
  3685. // Return
  3686. return $returnValue;
  3687. } // function listFunctions()
  3688. /**
  3689. * Get a list of all Excel function names
  3690. *
  3691. * @return array
  3692. */
  3693. public function listAllFunctionNames() {
  3694. return array_keys(self::$_PHPExcelFunctions);
  3695. } // function listAllFunctionNames()
  3696. /**
  3697. * Get a list of implemented Excel function names
  3698. *
  3699. * @return array
  3700. */
  3701. public function listFunctionNames() {
  3702. // Return value
  3703. $returnValue = array();
  3704. // Loop functions
  3705. foreach(self::$_PHPExcelFunctions as $functionName => $function) {
  3706. if ($function['functionCall'] != 'PHPExcel_Calculation_Functions::DUMMY') {
  3707. $returnValue[] = $functionName;
  3708. }
  3709. }
  3710. // Return
  3711. return $returnValue;
  3712. } // function listFunctionNames()
  3713. } // class PHPExcel_Calculation