PageRenderTime 66ms CodeModel.GetById 34ms RepoModel.GetById 1ms app.codeStats 0ms

/src/main/java/com/extentech/formats/XLS/formulas/InformationCalculator.java

https://bitbucket.org/openxls/openxls
Java | 719 lines | 439 code | 26 blank | 254 comment | 149 complexity | 8d984351509e585cfa751c6fec407073 MD5 | raw file
    

  1. /*

    2. 

  2.  * --------- BEGIN COPYRIGHT NOTICE ---------

    3. 

  3.  * Copyright 2002-2012 Extentech Inc.

    4. 

  4.  * Copyright 2013 Infoteria America Corp.

    5. 

  5.  * 

    6. 

  6.  * This file is part of OpenXLS.

    7. 

  7.  * 

    8. 

  8.  * OpenXLS is free software: you can redistribute it and/or modify

    9. 

  9.  * it under the terms of the GNU Lesser General Public License as

    10. 

  10.  * published by the Free Software Foundation, either version 3 of

    11. 

  11.  * the License, or (at your option) any later version.

    12. 

  12.  * 

    13. 

  13.  * OpenXLS is distributed in the hope that it will be useful,

    14. 

  14.  * but WITHOUT ANY WARRANTY; without even the implied warranty of

    15. 

  15.  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

    16. 

  16.  * GNU Lesser General Public License for more details.

    17. 

  17.  * 

    18. 

  18.  * You should have received a copy of the GNU Lesser General Public

    19. 

  19.  * License along with OpenXLS.  If not, see

    20. 

  20.  * <http://www.gnu.org/licenses/>.

    21. 

  21.  * ---------- END COPYRIGHT NOTICE ----------

    22. 

  22.  */

    23. 

  23. package com.extentech.formats.XLS.formulas;

    24. 

  24. 

    25. 

  25. import java.text.DecimalFormat;

    26. 

  26. 

    27. 

  27. import com.extentech.formats.XLS.BiffRec;

    28. 

  28. import com.extentech.formats.XLS.CellNotFoundException;

    29. 

  29. import com.extentech.formats.XLS.FormatConstants;

    30. 

  30. import com.extentech.formats.XLS.FunctionNotSupportedException;

    31. 

  31. import com.extentech.formats.XLS.Labelsst;

    32. 

  32. import com.extentech.formats.XLS.WorkSheetNotFoundException;

    33. 

  33. import com.extentech.formats.XLS.XLSRecord;

    34. 

  34. import com.extentech.toolkit.Logger;

    35. 

  35. 

    36. 

  36. 

    37. 

  37. 

    38. 

  38. /*

    39. 

  39.     InformationCalculator is a collection of static methods that operate

    40. 

  40.     as the Microsoft Excel function calls do.

    41. 

  41. 

    42. 

  42.     All methods are called with an array of ptg's, which are then

    43. 

  43.     acted upon.  A Ptg of the type that makes sense (ie boolean, number)

    44. 

  44.     is returned after each method.

    45. 

  45. */

    46. 

  46. 

    47. 

  47. public class InformationCalculator

    48. 

  48. {

    49. 

  49.     

    50. 

  50.         

    51. 

  51. 	 /**

    52. 

  52. 	  * 

    53. 

  53. 		 CELL

    54. 

  54. 		 Returns information about the formatting, location, or contents of a cell

    55. 

  55. 		 The CELL function returns information about the formatting, location, or contents of a cell. 

    56. 

  56. 

    57. 

  57. 		 CELL(info_type, [reference])

    58. 

  58. 		 info_type  Required. A text value that specifies what type of cell information you want to return. The following list shows the possible values of the info_type argument and the corresponding results.info_type Returns 

    59. 

  59. 			"address" Reference of the first cell in reference, as text. 

    60. 

  60. 			"col" Column number of the cell in reference. 

    61. 

  61. 			"color" The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero). 

    62. 

  62. 			"contents" Value of the upper-left cell in reference; not a formula. 

    63. 

  63. 			"filename" Filename (including full path) of the file that contains reference, as text. Returns empty text ("") if the worksheet that contains reference has not yet been saved. 

    64. 

  64. 			"format" Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns "-" at the end of the text value if the cell is formatted in color for negative values. Returns "()" at the end of the text value if the cell is formatted with parentheses for positive or all values. 

    65. 

  65. 			"parentheses" The value 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0. 

    66. 

  66. 			"prefix" Text value corresponding to the "label prefix" of the cell. Returns single quotation mark (') if the cell contains left-aligned text, double quotation mark (") if the cell contains right-aligned text, caret (^) if the cell contains centered text, backslash (\) if the cell contains fill-aligned text, and empty text ("") if the cell contains anything else. 

    67. 

  67. 			"protect" The value 0 if the cell is not locked; otherwise returns 1 if the cell is locked. 

    68. 

  68. 			"row" Row number of the cell in reference. 

    69. 

  69. 			"type" Text value corresponding to the type of data in the cell. Returns "b" for blank if the cell is empty, "l" for label if the cell contains a text constant, and "v" for value if the cell contains anything else. 

    70. 

  70. 			"width" Column width of the cell, rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size. 

    71. 

  71. 

    72. 

  72. 		 reference  Optional. The cell that you want information about. If omitted, the information specified in the info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper left cell of the range.		 

    73. 

  73. 	  */

    74. 

  74. 	   protected static Ptg calcCell(Ptg[] operands) 

    75. 

  75. 	   throws FunctionNotSupportedException {

    76. 

  76. 		   String type= operands[0].getValue().toString().toLowerCase();

    77. 

  77. 		   PtgRef ref= null;

    78. 

  78. 		   BiffRec cell= null;

    79. 

  79. 		   if (operands.length > 1) {

    80. 

  80. 			   ref= (PtgRef) operands[1];

    81. 

  81. 			   try {

    82. 

  82. 				   cell= ref.getParentRec().getWorkBook().getCell(ref.getLocationWithSheet());

    83. 

  83. 			   } catch (CellNotFoundException e) {

    84. 

  84. 				   try {

    85. 

  85.  					   String sh= null;

    86. 

  86. 					   try {

    87. 

  87. 						   sh= ref.getSheetName();

    88. 

  88. 					   } catch (WorkSheetNotFoundException we) {; }

    89. 

  89. 					   if (sh==null) sh= ref.getParentRec().getSheet().getSheetName();					   

    90. 

  90. 					   cell= ref.getParentRec().getWorkBook().getWorkSheetByName(sh).addValue(null, ref.getLocation());

    91. 

  91. 				   } catch (Exception ex) {

    92. 

  92. 					   return new PtgErr(PtgErr.ERROR_VALUE);

    93. 

  93. 				   }

    94. 

  94. 			   } catch (Exception e) {

    95. 

  95. 				   return new PtgErr(PtgErr.ERROR_VALUE);

    96. 

  96. 			   }

    97. 

  97. 		   //  If ref param is omitted, the information specified in the info_type argument 

    98. 

  98. 		   // is returned for the last cell that was changed 

    99. 

  99. 		   } else if (!type.equals("filename"))// no ref was passed in and option is not "filename"

    100. 

  100. 			   // We cannot determine which is the "last cell" they are referencing; 

    101. 

  101. 			   	throw new FunctionNotSupportedException("Worsheet function CELL with no reference parameter is not supported");

    102. 

  102. 		   else	// filename option can use any biffrec ...

    103. 

  103. 				cell= operands[0].getParentRec();

    104. 

  104. 		   

    105. 

  105. 		   // at this point both ref (PtgRef) and r (BiffRec) should be valid

    106. 

  106. 		   try {

    107. 

  107. 			   if (type.equals("address")) {

    108. 

  108. 				   PtgRef newref= ref;

    109. 

  109. 				   newref.clearLocationCache();

    110. 

  110. 				   newref.fColRel= false;		// make absolute

    111. 

  111. 				   newref.fRwRel= false;

    112. 

  112. 				   return new PtgStr(newref.getLocation());

    113. 

  113. 			   } else if (type.equals("col")) {

    114. 

  114. 				   return new PtgNumber(ref.getIntLocation()[1]+1);

    115. 

  115. 			   } else if (type.equals("color")) {	// The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).			   

    116. 

  116. 				   String s= cell.getFormatPattern();

    117. 

  117. 				   if (s.indexOf(";[Red")>-1)

    118. 

  118. 					   return new PtgNumber(1);

    119. 

  119. 				   return new PtgNumber(0);

    120. 

  120. 			   } else if (type.equals("contents")) {// Value of the upper-left cell in reference; not a formula.

    121. 

  121. 				   return new PtgStr(cell.getStringVal());

    122. 

  122. 			   } else if (type.equals("filename")) {

    123. 

  123. 				   String f= cell.getWorkBook().getFileName();

    124. 

  124. 				   String sh= cell.getSheet().getSheetName();

    125. 

  125. 				   int i= f.lastIndexOf(java.io.File.separatorChar);

    126. 

  126. 					   f= f.substring(0, i+1)+ "[" + f.substring(i+1);

    127. 

  127. 				   f+= "]" + sh;

    128. 

  128. 				   return new PtgStr(f);

    129. 

  129. 			   } else if (type.equals("format")) {	// Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns "-" at the end of the text value if the cell is formatted in color for negative values. Returns "()" at the end of the text value if the cell is formatted with parentheses for positive or all values.

    130. 

  130. 				   String s= cell.getFormatPattern();

    131. 

  131. 				   String ret= "G";	// default?

    132. 

  132. 				   if (s.equals("General") ||

    133. 

  133. 						   s.equals("# ?/?") ||

    134. 

  134. 						   s.equals("# ??/??")) {

    135. 

  135. 					   ret= "G";

    136. 

  136. 				   } else if (s.equals("0")) {

    137. 

  137. 						ret= "F0"; 				   

    138. 

  138. 				   } else if (s.equals("#,##0")) {

    139. 

  139. 					   ret= ",0";

    140. 

  140. 				   } else if (s.equals("0.00")) {

    141. 

  141. 					   ret= "F2";

    142. 

  142. 				   } else if (s.equals("#,##0.00")) {

    143. 

  143. 					   ret= ", 2";

    144. 

  144. 				   } else if (s.equals("$#,##0_);($#,##0)")) {

    145. 

  145. 					   ret= "C0";

    146. 

  146. 				   } else if (s.equals("$#,##0_);[Red]($#,##0)")) {

    147. 

  147. 					   ret= "C0-";

    148. 

  148. 				   } else if (s.equals("$#,##0.00_);($#,##0.00)")) {

    149. 

  149. 					   ret= "C2";

    150. 

  150. 				   } else if (s.equals("$#,##0.00_);[Red]($#,##0.00)")) {

    151. 

  151. 					   ret= "C2-";

    152. 

  152. 				   } else if (s.equals("0%")) {

    153. 

  153. 					   ret= "P0";

    154. 

  154. 				   } else if (s.equals("0.00%")) {

    155. 

  155. 					   ret= "P2";

    156. 

  156. 				   } else if (s.equals("0.00E+00")) {

    157. 

  157. 					   ret= "S2";

    158. 

  158. //					   m/d/yy or m/d/yy h:mm or mm/dd/yy 	"D4"					   

    159. 

  159. 				   } else if (s.equals("m/d/yy") ||

    160. 

  160. 						   s.equals("m/d/yy h:mm") ||

    161. 

  161. 						   s.equals("mm/dd/yy") ||

    162. 

  162. 						   s.equals("mm-dd-yy")) {		// added last to accomodate Excel's regional short date setting (format #14)

    163. 

  163. 					   ret= "D4";

    164. 

  164. 				   } else if (s.equals("d-mmm-yy") ||

    165. 

  165. 						   s.equals("dd-mmm-yy")) {

    166. 

  166. 					   ret= "D1";

    167. 

  167. 				   } else if (s.equals("d-mmm") ||

    168. 

  168. 						   s.equals("dd-mmm")) {

    169. 

  169. 					   ret= "D2";

    170. 

  170. 				   } else if (s.equals("mmm-yy")) {

    171. 

  171. 					   ret= "D3";

    172. 

  172. 				   } else if (s.equals("mm/dd")) {

    173. 

  173. 					   ret= "D5";

    174. 

  174. 				   } else if (s.equals("h:mm AM/PM")) {

    175. 

  175. 					   ret= "D7";

    176. 

  176. 				   } else if (s.equals("h:mm:ss AM/PM")) {

    177. 

  177. 					   ret= "D6";

    178. 

  178. 				   } else if (s.equals("h:mm")) {

    179. 

  179. 					   ret= "D9";

    180. 

  180. 				   } else if (s.equals("h:mm:ss")) {

    181. 

  181. 					   ret= "D8";

    182. 

  182. 				   }

    183. 

  183. 				   return new PtgStr(ret);

    184. 

  184. 			   } else if (type.equals("parentheses")) {

    185. 

  185. 				   String s= cell.getFormatPattern();

    186. 

  186. 				   if (s.startsWith("("))

    187. 

  187. 					   return new PtgNumber(1);

    188. 

  188. 				   return new PtgNumber(0);

    189. 

  189. 			   } else if (type.equals("prefix")) {

    190. 

  190. 				   // TODO: THIS IS NOT CORRECT - EITHER INFORM USER OR ??

    191. 

  191. // DOESN'T APPEAR TO MATCH EXCEL				   

    192. 

  192. 				   //Text value corresponding to the "label prefix" of the cell. 

    193. 

  193. 				   // Returns single quotation mark (') if the cell contains left-aligned text, double quotation mark (") if the cell contains right-aligned text, 

    194. 

  194. 				   // caret (^) if the cell contains centered text, backslash (\) if the cell contains fill-aligned text, and empty text ("") if the cell contains anything else.

    195. 

  195.  				   int al= cell.getXfRec().getHorizontalAlignment();

    196. 

  196. 				   if(al==FormatConstants.ALIGN_LEFT)

    197. 

  197. 					   return new PtgStr("'");

    198. 

  198. 				   if(al==FormatConstants.ALIGN_CENTER)

    199. 

  199. 					   return new PtgStr("^");

    200. 

  200. 				   if(al==FormatConstants.ALIGN_RIGHT)

    201. 

  201. 					   return new PtgStr("\"");

    202. 

  202. 				   if(al==FormatConstants.ALIGN_FILL)

    203. 

  203. 					   return new PtgStr("\\");

    204. 

  204. 				   return new PtgStr("");

    205. 

  205. 			   } else if (type.equals("protect")) {

    206. 

  206. 				   if (cell.getXfRec().isLocked())

    207. 

  207. 					   return new PtgNumber(1);

    208. 

  208. 				   return new PtgNumber(0);

    209. 

  209. 			   } else if (type.equals("row")) {

    210. 

  210. 				   return new PtgNumber(ref.getIntLocation()[0]+1);

    211. 

  211. 			   } else if (type.equals("type")) { 

    212. 

  212. 				   //Text value corresponding to the type of data in the cell. 

    213. 

  213. 				   // Returns "b" for blank if the cell is empty, 

    214. 

  214. 				   //"l" for label if the cell contains a text constant, and 

    215. 

  215. 				   // "v" for value if the cell contains anything else.

    216. 

  216. 				   if (((XLSRecord) cell).isBlank)

    217. 

  217. 					   return new PtgStr("b");

    218. 

  218. 				   if (cell instanceof Labelsst)

    219. 

  219. 					   return new PtgStr("l");

    220. 

  220. 				   return new PtgStr("v");

    221. 

  221. 			   } else if (type.equals("width")) {

    222. 

  222. 				   int n= 0;

    223. 

  223. 				   n= cell.getSheet().getColInfo(cell.getColNumber()).getColWidthInChars();

    224. 

  224. 				   return new PtgNumber(n);

    225. 

  225. 			   }

    226. 

  226. 		   } catch (Exception e) {

    227. 

  227. 			   Logger.logWarn("CELL: unable to calculate: " + e.toString());

    228. 

  228. 		   }

    229. 

  229. 		   return new PtgErr(PtgErr.ERROR_VALUE);

    230. 

  230. 	   }

    231. 

  231. 	 

    232. 

  232. 	/**

    233. 

  233. 	 *  ERROR.TYPE

    234. 

  234.   	Returns a number corresponding to an error type

    235. 

  235.  	If error_val is

    236. 

  236. 	 ERROR.TYPE returns

    237. 

  237. 	 

    238. 

  238. 	 #NULL!	 1

    239. 

  239. 	 #DIV/0! 2

    240. 

  240. 	 #VALUE! 3

    241. 

  241. 	 #REF!	 4

    242. 

  242. 	 #NAME?	 5

    243. 

  243. 	 #NUM!	 6

    244. 

  244. 	 #N/A	 7

    245. 

  245. 	 Anything else	 #N/A

    246. 

  246.    */

    247. 

  247.    protected static Ptg calcErrorType(Ptg[] operands){

    248. 

  248.    		Object o = operands[0].getValue();

    249. 

  249.    		String s = o.toString();

    250. 

  250.    		if (s.equalsIgnoreCase("#NULL!")) return new PtgInt(1);

    251. 

  251. 		if (s.equalsIgnoreCase("#DIV/0!")) return new PtgInt(2);

    252. 

  252. 		if (s.equalsIgnoreCase("#VALUE!")) return new PtgInt(3);

    253. 

  253. 		if (s.equalsIgnoreCase("#REF!")) return new PtgInt(4);

    254. 

  254. 		if (s.equalsIgnoreCase("#NAME?")) return new PtgInt(5);

    255. 

  255. 		if (s.equalsIgnoreCase("#NUM!")) return new PtgInt(6);

    256. 

  256. 		if (s.equalsIgnoreCase("#N/A")) return new PtgInt(7);

    257. 

  257. 		return new PtgErr(PtgErr.ERROR_NA);

    258. 

  258.    }

    259. 

  259.    

    260. 

  260.    /**

    261. 

  261.    INFO

    262. 

  262.     Returns information about the current operating environment

    263. 

  263. 	INFO(type_text)

    264. 

  264. 	

    265. 

  265. 	NOTE: Several options are incomplete:	

    266. 

  266. 	"osversion"	-- only valid for Windows versions

    267. 

  267. 	"system" 	-- only valid for Windows and Mac

    268. 

  268. 	"release"	-- incomplete

    269. 

  269. 	"origin"	-- does not return R1C1 format	

    270. 

  270.   */

    271. 

  271.    protected static Ptg calcInfo(Ptg[] operands) {

    272. 

  272. 	   // validate

    273. 

  273. 	   if (operands==null || operands.length==0 || operands[0].getParentRec()==null)

    274. 

  274. 		   return new PtgErr(PtgErr.ERROR_VALUE);

    275. 

  275. 	   String type_text= operands[0].getString();

    276. 

  276. 	   String ret= "";

    277. 

  277. 	   if (type_text.equals("directory")) 		// Path of the current directory or folder

    278. 

  278. 		   return new PtgStr(System.getProperty("user.dir").toLowerCase() + "\\");

    279. 

  279. 	   else if (type_text.equals("numfile"))	// number of active worksheets in the current workbook

    280. 

  280. // TODO: what is correct definition of "Active Worksheets"  - hidden state doesn't seem to affect"		   

    281. 

  281. 		   return new PtgNumber(operands[0].getParentRec().getWorkBook().getNumWorkSheets());

    282. 

  282. 	   else if (type_text.equals("origin")) {		/* Returns the absolute cell reference of the top and leftmost 

    283. 

  283. 	   											    cell visible in the window, based on the current scrolling 

    284. 

  284. 	   											    position, as text prepended with "$A:". 

    285. 

  285. 	   											    This value is intended for for Lotus 1-2-3 release 3.x compatibility. 

    286. 

  286. 	   											    The actual value returned depends on the current reference 

    287. 

  287. 	   											    style setting. Using D9 as an example, the return value would be: 

    288. 

  288. 	   											    A1 reference style   "$A:$D$9". 

    289. 

  289. 													R1C1 reference style  "$A:R9C4"

    290. 

  290. 	    										*/

    291. 

  291. // TODO: FINISH R1C1 reference style

    292. 

  292. 		   String cell= operands[0].getParentRec().getSheet().getWindow2().getTopLeftCell();

    293. 

  293. 		   for (int i= cell.length()-1; i>=0; i--) {

    294. 

  294. 			   if (!Character.isDigit(cell.charAt(i))) {

    295. 

  295. 				   cell= cell.substring(0, i+1) + "$" + cell.substring(i+1);

    296. 

  296. 				   break;

    297. 

  297. 			   }

    298. 

  298. 		   }

    299. 

  299. 		   cell="$A:$" + cell;		  

    300. 

  300. 		   return new PtgStr(cell);

    301. 

  301. 	   } else if (type_text.equals("osversion")){	//Current operating system version, as text.

    302. 

  302. 		   // see end of file for os info

    303. 

  303. 		   String osversion = System.getProperty("os.version");

    304. 

  304. 		   String n= System.getProperty("os.name");	// Windows Vista

    305. 

  305. 		   String os= "";

    306. 

  306. 		   // TODO:  need a list of osversions to compare to!  have know idea for mac, linux ... 		   

    307. 

  307. 		   if (n.startsWith("Windows")) {

    308. 

  308. 			   double v= new Double(osversion);

    309. 

  309. 			   os= "Windows (32-bit) ";

    310. 

  310. 			   if (v>=5)

    311. 

  311. 				   os+="NT ";

    312. 

  312. 			   DecimalFormat df = new DecimalFormat("##.00");

    313. 

  313. 			   os+= df.format(v);

    314. 

  314. 		   } // otherwise have NO idea as cannot find any info on net

    315. 

  315. 		   else os += osversion;

    316. 

  316. 		   return new PtgStr(os);

    317. 

  317. 	   } else if (type_text.equals("recalc"))	{	//Current recalculation mode; returns "Automatic" or "Manual".

    318. 

  318. 		   if (operands[0].getParentRec().getWorkBook().getRecalculationMode()==0) // manual			   

    319. 

  319. 			   return new PtgStr("Manual");

    320. 

  320. 		   return new PtgStr("Automatic");

    321. 

  321. 	   }else if (type_text.equals("release")){	//Version of Microsoft Excel, as text.

    322. 

  322. 		   // TODO: Finish!  97= 8.0, 2000= 9.0, 2002 (XP)= 10.0, 2003= 11.0, 2007= 12.0		   

    323. 

  323. 		   Logger.logWarn("Worksheet Function INFO(\"release\") is not supported");

    324. 

  324. 		   return new PtgStr("");

    325. 

  325. 	   }else if (type_text.equals("system"))		// Name of the operating environment: Macintosh = "mac" Windows = "pcdos"	   

    326. 

  326. 		   // TODO: linux?  ****************

    327. 

  327. 		   if (System.getProperty("os.name").indexOf("Windows")>=0)

    328. 

  328. 			   return new PtgStr("pcdos");

    329. 

  329. 		   else

    330. 

  330. 			   return new PtgStr("mac");

    331. 

  331. 	   // In previous versions of Microsoft Office Excel, the "memavail", "memused", and "totmem" type_text values, returned memory information. 

    332. 

  332. 	   // These type_text values are no longer supported and now return a #N/A error value. 

    333. 

  333. 	   else if (type_text.equals("memavail") ||

    334. 

  334. 			   type_text.equals("memused") || 

    335. 

  335. 			   type_text.equals("totmem"))

    336. 

  336. 		   return new PtgErr(PtgErr.ERROR_NA);

    337. 

  337. 	   return new PtgErr(PtgErr.ERROR_VALUE);

    338. 

  338.    }

    339. 

  339.    

    340. 

  340. 	/**

    341. 

  341. 	 * ISBLANK

    342. 

  342. 	 * ISBLANK determines if the cell referenced is blank, and returns

    343. 

  343. 	 * a boolean ptg based off that

    344. 

  344. 	 */

    345. 

  345. 	protected static Ptg calcIsBlank(Ptg[] operands){

    346. 

  346. 		Ptg[] allops = PtgCalculator.getAllComponents(operands);

    347. 

  347. 		for (int i=0;i<allops.length;i++){

    348. 

  348. 			// 20081120 KSC: blanks are handled differently now as Excel counts blank cells as 0's

    349. 

  349. 			/*Object o = allops[i].getValue();

    350. 

  350. 			if (o != null) return new PtgBool(false);

    351. 

  351. 			*/

    352. 

  352. 			if (!allops[i].isBlank())

    353. 

  353. 				return new PtgBool(false);

    354. 

  354. 		}

    355. 

  355. 		return new PtgBool(true);

    356. 

  356. 	}	

    357. 

  357. 	

    358. 

  358.     /**

    359. 

  359.      * ISERROR

    360. 

  360.         Value refers to any error value 

    361. 

  361.         (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

    362. 

  362.         Usage@ ISERROR(value)

    363. 

  363.         Return@ PtgBool

    364. 

  364.     */

    365. 

  365.     protected static Ptg calcIserror(Ptg[] operands){

    366. 

  366. 		if (operands[0] instanceof PtgErr){return new PtgBool(true);}

    367. 

  367. 		String[] errorstr = {"#N/A", "#VALUE!", "#REF!", "#DIV/0!", "#NUM!", "#NAME?", "#NULL!"	};

    368. 

  368. 		Object o = operands[0].getValue();

    369. 

  369. 		String opval = o.toString();

    370. 

  370. 		for (int i=0;i<errorstr.length;i++){

    371. 

  371. 			if (opval.equalsIgnoreCase(errorstr[i])){

    372. 

  372. 				return new PtgBool(true);

    373. 

  373. 			}

    374. 

  374. 		}

    375. 

  375. 		return new PtgBool(false);

    376. 

  376.     }

    377. 

  377.     

    378. 

  378.     /**

    379. 

  379.      *  ISERR

    380. 

  380.   		Returns TRUE if the value is any error value except #N/A

    381. 

  381.      */

    382. 

  382. 	protected static Ptg calcIserr(Ptg[] operands){

    383. 

  383. 		String[] errorstr = {"#VALUE!", "#REF!", "#DIV/0!", "#NUM!", "#NAME?", "#NULL!"	};

    384. 

  384. 		if (operands.length!=1) return new PtgErr(PtgErr.ERROR_VALUE);

    385. 

  385. 		Object o = operands[0].getValue();

    386. 

  386. 		String opval = o.toString();

    387. 

  387. 		for (int i=0;i<errorstr.length;i++){

    388. 

  388. 			if (opval.equalsIgnoreCase(errorstr[i])){

    389. 

  389. 				return new PtgBool(true);

    390. 

  390. 			}

    391. 

  391. 		}

    392. 

  392. 		return new PtgBool(false);

    393. 

  393. 	}

    394. 

  394. 	

    395. 

  395. 	/**

    396. 

  396. 	ISEVEN(number)

    397. 

  397. 	

    398. 

  398. 	Number   is the value to test. If number is not an integer, it is truncated.

    399. 

  399. 	

    400. 

  400. 	Remarks

    401. 

  401. 	If number is nonnumeric, ISEVEN returns the #VALUE! error value.

    402. 

  402. 	Examples

    403. 

  403. 	ISEVEN(-1) equals FALSE

    404. 

  404. 	ISEVEN(2.5) equals TRUE

    405. 

  405. 	ISEVEN(5) equals FALSE

    406. 

  406. 	

    407. 

  407. 	author: John

    408. 

  408. */

    409. 

  409. 	protected static Ptg calcIsEven(Ptg[] operands){

    410. 

  410. 		if (operands.length < 1) return new PtgErr(PtgErr.ERROR_VALUE);

    411. 

  411. 		Ptg[] allops = PtgCalculator.getAllComponents(operands);

    412. 

  412. 		if (allops.length > 1) return new PtgBool(false);

    413. 

  413. 		Object o = operands[0].getValue();

    414. 

  414. 		if (o != null){

    415. 

  415. 			try{	// KSC: mod for different number types + mod typo

    416. 

  416. 				if (o instanceof Integer) {

    417. 

  417. 					int s = ((Integer)o).intValue();

    418. 

  418. 					if(s<0)return new PtgBool(false);

    419. 

  419. 					return new PtgBool((s%2==0));

    420. 

  420. 				} else if (o instanceof Float) {

    421. 

  421. 					float s = ((Float)o).floatValue();

    422. 

  422. 					if(s<0)return new PtgBool(false);

    423. 

  423. 					return new PtgBool((s%2==0));

    424. 

  424. 				} else if (o instanceof Double) {

    425. 

  425. 					double s = ((Double)o).doubleValue();

    426. 

  426. 					if(s<0)return new PtgBool(false);

    427. 

  427. 					return new PtgBool((s%2==0));

    428. 

  428. 				}

    429. 

  429. 			}catch(Exception e){ 	}

    430. 

  430. 		}

    431. 

  431. 		return new PtgErr(PtgErr.ERROR_VALUE);

    432. 

  432. 	}

    433. 

  433. 	

    434. 

  434. 	/**

    435. 

  435. 	 *  ISLOGICAL

    436. 

  436.  		 Returns TRUE if the value is a logical value 

    437. 

  437. 	 */

    438. 

  438. 	protected static Ptg calcIsLogical(Ptg[] operands){

    439. 

  439. 		Ptg[] allops = PtgCalculator.getAllComponents(operands);

    440. 

  440. 		if (allops.length > 1) return new PtgBool(false);

    441. 

  441. 		Object o = operands[0].getValue();

    442. 

  442. 		// unfortunately we need to know the difference between 

    443. 

  443. 		// "true" and true, if it's a reference this can be difficult

    444. 

  444. 		try{

    445. 

  445. 			Boolean b = (Boolean)o;

    446. 

  446. 			return new PtgBool(true);

    447. 

  447. 		}catch(ClassCastException e){};

    448. 

  448. 		return new PtgBool(false);

    449. 

  449. 	}

    450. 

  450. 	 

    451. 

  451. 	  /** 

    452. 

  452. 	  ISNUMBER

    453. 

  453. 	  Returns TRUE if the value is a number

    454. 

  454. 	  */

    455. 

  455. 	protected static Ptg calcIsNumber(Ptg[] operands){

    456. 

  456. 		Ptg[] allops = PtgCalculator.getAllComponents(operands);

    457. 

  457. 		if (allops.length > 1) return new PtgBool(false);

    458. 

  458. 		Object o = operands[0].getValue();

    459. 

  459. 		try{

    460. 

  460. 			Float f= (Float) o;

    461. 

  461. 			return new PtgBool(true);

    462. 

  462. 		}catch (ClassCastException e) {

    463. 

  463. 			try { 

    464. 

  464. 				Double d= (Double) o;

    465. 

  465. 				return new PtgBool(true);

    466. 

  466. 			}catch(ClassCastException ee){

    467. 

  467. 				try { 

    468. 

  468. 					Integer ii= (Integer) o;

    469. 

  469. 					return new PtgBool(true);

    470. 

  470. 				}catch(ClassCastException eee){ };

    471. 

  471. 			};

    472. 

  472. 		}

    473. 

  473. 		return new PtgBool(false);

    474. 

  474. 	}

    475. 

  475. 

    476. 

  476. 	/**

    477. 

  477. 	 ISNONTEXT

    478. 

  478. 	  Returns TRUE if the value is not text	

    479. 

  479. 	 */

    480. 

  480. 	protected static Ptg calcIsNonText(Ptg[] operands){

    481. 

  481. 		Ptg[] allops = PtgCalculator.getAllComponents(operands);

    482. 

  482. 		if (allops.length > 1) return new PtgBool(false);

    483. 

  483. 		// blanks return true for this test

    484. 

  484. 		if (allops[0].isBlank()) 

    485. 

  485. 			return new PtgBool(true);

    486. 

  486. 		Object o = operands[0].getValue();

    487. 

  487. 		if (o != null){

    488. 

  488. 			try{

    489. 

  489. 				String s = (String)o;

    490. 

  490. 				return new PtgBool(false);

    491. 

  491. 			}catch(ClassCastException e){};

    492. 

  492. 		}

    493. 

  493. 		return new PtgBool(true);

    494. 

  494. 	}

    495. 

  495. 

    496. 

  496.     /**

    497. 

  497.      * ISNA

    498. 

  498.         Value refers to the #N/A 

    499. 

  499.         (value not available) error value.

    500. 

  500.         

    501. 

  501.         usage@ ISNA(value)

    502. 

  502.         return@ PtgBool

    503. 

  503.     */

    504. 

  504.     protected static Ptg calcIsna(Ptg[] operands){

    505. 

  505.         if (operands.length != 1) return PtgCalculator.getError();

    506. 

  506.         if (operands[0] instanceof PtgErr){

    507. 

  507.         	PtgErr per = (PtgErr) operands[0];

    508. 

  508.         	if (per.getErrorType() == PtgErr.ERROR_NA){

    509. 

  509.         		return new PtgBool(true);

    510. 

  510.         	}

    511. 

  511.         }else if (operands[0].getIsReference()){

    512. 

  512.         	Object o = operands[0].getValue();

    513. 

  513.         	if (o.toString().equalsIgnoreCase(new PtgErr(PtgErr.ERROR_NA).toString())){

    514. 

  514.         		return new PtgBool(true);

    515. 

  515.         	}

    516. 

  516.         }

    517. 

  517.         return new PtgBool(false);

    518. 

  518.     }

    519. 

  519. 

    520. 

  520. 	/**

    521. 

  521. 		 NA

    522. 

  522. 	  Returns the error value #N/A

    523. 

  523. 	 */

    524. 

  524. 	 protected static Ptg calcNa(Ptg[] operands){

    525. 

  525. 	 	return new PtgErr(PtgErr.ERROR_NA);

    526. 

  526. 	 }

    527. 

  527. 	/**

    528. 

  528. 	 ISTEXT

    529. 

  529. 	  Returns TRUE if the value is text

    530. 

  530. 	 */

    531. 

  531. 	protected static Ptg calcIsText(Ptg[] operands){

    532. 

  532. 		Ptg[] allops = PtgCalculator.getAllComponents(operands);

    533. 

  533. 		if (allops.length > 1) return new PtgBool(false);

    534. 

  534. 		Object o = operands[0].getValue();

    535. 

  535. 		if (o != null){

    536. 

  536. 			try{

    537. 

  537. 				String s = (String)o;

    538. 

  538. 				return new PtgBool(true);

    539. 

  539. 			}catch(ClassCastException e){};

    540. 

  540. 		}

    541. 

  541. 		return new PtgBool(false);

    542. 

  542. 	}

    543. 

  543. 	 	  

    544. 

  544.  

    545. 

  545. /**

    546. 

  546. 	 ISODD

    547. 

  547. 	  Returns TRUE if the number is odd

    548. 

  548. 	

    549. 

  549. 	author: John

    550. 

  550. */

    551. 

  551. 	protected static Ptg calcIsOdd(Ptg[] operands){

    552. 

  552. 		if (operands.length < 1) return new PtgErr(PtgErr.ERROR_VALUE);

    553. 

  553. 		Ptg[] allops = PtgCalculator.getAllComponents(operands);

    554. 

  554. 		if (allops.length > 1) return new PtgBool(false);

    555. 

  555. 		Object o = operands[0].getValue();

    556. 

  556. 		if (o != null){

    557. 

  557. 			try{	// KSC: mod for different number types + mod typo

    558. 

  558. 				if (o instanceof Integer) {

    559. 

  559. 					int s = ((Integer)o).intValue();

    560. 

  560. 					if(s<0)return new PtgBool(false);

    561. 

  561. 					return new PtgBool((s%2!=0));

    562. 

  562. 				} else if (o instanceof Float) {

    563. 

  563. 					float s = ((Float)o).floatValue();

    564. 

  564. 					if(s<0)return new PtgBool(false);

    565. 

  565. 					return new PtgBool((s%2!=0));

    566. 

  566. 				} else if (o instanceof Double) {

    567. 

  567. 					double s = ((Double)o).doubleValue();

    568. 

  568. 					if(s<0)return new PtgBool(false);

    569. 

  569. 					return new PtgBool((s%2!=0));

    570. 

  570. 				}

    571. 

  571. 			} catch (Exception e) {}

    572. 

  572. 		}

    573. 

  573. 		return new PtgErr(PtgErr.ERROR_VALUE);

    574. 

  574. 	}

    575. 

  575. 

    576. 

  576. 	/**

    577. 

  577. 	 ISREF

    578. 

  578. 	  Returns TRUE if the value is a reference

    579. 

  579. 	 */

    580. 

  580. 	 protected static Ptg calcIsRef(Ptg[] operands){

    581. 

  581. 	 	if (operands[0].getIsReference()){

    582. 

  582. 	 		return new PtgBool(true);

    583. 

  583. 	 	}

    584. 

  584. 	 	return new PtgBool(false);

    585. 

  585. 	 }

    586. 

  586. 	 /**

    587. 

  587. 		N

    588. 

  588. 		Returns a value converted to a number.

    589. 

  589. 		

    590. 

  590. 		Syntax

    591. 

  591. 		

    592. 

  592. 		N(value)

    593. 

  593. 		

    594. 

  594. 		Value   is the value you want converted. N converts values listed in the following table.

    595. 

  595. 		

    596. 

  596. 		If value is or refers to

    597. 

  597. 		 N returns

    598. 

  598. 		 

    599. 

  599. 		A number

    600. 

  600. 		 That number

    601. 

  601. 		 

    602. 

  602. 		A date, in one of the built-in date formats available in Microsoft Excel

    603. 

  603. 		 The serial number of that date --- Note that to us, this is just a number, the date

    604. 

  604. 		 format is just that, a format.

    605. 

  605. 		 

    606. 

  606. 		TRUE

    607. 

  607. 		 1

    608. 

  608. 		 

    609. 

  609. 		Anything else

    610. 

  610. 		 0

    611. 

  611. 	 */

    612. 

  612. 	 protected static Ptg calcN(Ptg[] operands){

    613. 

  613. 		Object o = operands[0].getValue();

    614. 

  614. 		if (o instanceof Double || o instanceof Integer || o instanceof Float || o instanceof Long){

    615. 

  615. 			Double d = new Double(o.toString());

    616. 

  616. 			return new PtgNumber(d.doubleValue());

    617. 

  617. 		}

    618. 

  618. 		if (o instanceof Boolean){

    619. 

  619. 			Boolean b = (Boolean)o;

    620. 

  620. 			boolean bo = b.booleanValue();

    621. 

  621. 			if (bo) return new PtgInt(1);

    622. 

  622. 		}

    623. 

  623. 		return new PtgInt(0);

    624. 

  624. 	 }

    625. 

  625.  

    626. 

  626. 	 /**

    627. 

  627. 	 TYPE

    628. 

  628. 	  Returns a number indicating the data type of a value

    629. 

  629. 	  Value   can be any Microsoft Excel value, such as a number, text, logical value, and so on.

    630. 

  630. 		If value is TYPE returns 

    631. 

  631. 		Number 1 

    632. 

  632. 		Text 2 

    633. 

  633. 		Logical value 4 

    634. 

  634. 		Error value 16 

    635. 

  635. 		Array 64   

    636. 

  636. 	 */

    637. 

  637. 	 protected static Ptg calcType(Ptg[] operands) {

    638. 

  638. 		 if (operands[0] instanceof PtgArray)

    639. 

  639. 			 return new PtgNumber(64);	// avoid value calc for arrays

    640. 

  640. 		 else if (operands[0] instanceof PtgErr)

    641. 

  641. 			 return new PtgNumber(16);	

    642. 

  642. 		 

    643. 

  643. 		 // otherwise, test value of operand

    644. 

  644. 		 Object value = operands[0].getValue();

    645. 

  645. 		 int type= 0;

    646. 

  646. 		 if (value instanceof String)

    647. 

  647. 			 type= 2;

    648. 

  648. 		 else if (value instanceof Number)

    649. 

  649. 			 type= 1;

    650. 

  650. 		 else if (value instanceof Boolean)

    651. 

  651. 			 type= 4;

    652. 

  652. 		 return new PtgNumber(type);	 

    653. 

  653. 	 }

    654. 

  654. 

    655. 

  655. }

    656. 

  656. 

    657. 

  657. /*

    658. 

  658.  * known INFO function operating systems: 

    659. 

  659.  * TODO: need complete list

    660. 

  660. Windows Vista   Windows (32-bit) NT 6.00

    661. 

  661. Windows XP 		Windows (32-bit) NT 5.01

    662. 

  662. Windows2000 	Windows (32-bit) NT 5.00

    663. 

  663. Windows98 		Windows (32-bit) 4.10

    664. 

  664. Windows95 		Windows (32-bit) 4.00

    665. 

  665. */

    666. 

  666. 

    667. 

  667. /*

    668. 

  668. Linux 	2.0.31 	x86 	IBM Java 1.3

    669. 

  669. Linux 	(*) 	i386 	Sun Java 1.3.1, 1.4 or Blackdown Java; (*) os.version depends on Linux Kernel version

    670. 

  670. Linux 	(*) 	x86_64 	Blackdown Java; note x86_64 might change to amd64; (*) os.version depends on Linux Kernel version

    671. 

  671. Linux 	(*) 	sparc 	Blackdown Java; (*) os.version depends on Linux Kernel version

    672. 

  672. Linux 	(*) 	ppc 	Blackdown Java; (*) os.version depends on Linux Kernel version

    673. 

  673. Linux 	(*) 	armv41 	Blackdown Java; (*) os.version depends on Linux Kernel version

    674. 

  674. Linux 	(*) 	i686 	GNU Java Compiler (GCJ); (*) os.version depends on Linux Kernel version

    675. 

  675. Linux 	(*) 	ppc64 	IBM Java 1.3; (*) os.version depends on Linux Kernel version

    676. 

  676. Mac OS 	7.5.1 	PowerPC 	

    677. 

  677. Mac OS 	8.1 	PowerPC 	

    678. 

  678. Mac OS 	9.0, 9.2.2 	PowerPC 	MacOS 9.0: java.version=1.1.8, mrj.version=2.2.5; MacOS 9.2.2: java.version=1.1.8 mrj.version=2.2.5

    679. 

  679. Mac OS X 	10.1.3 	ppc 	

    680. 

  680. Mac OS X 	10.2.6 	ppc 	Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1_01-39)

    681. 

  681. Java HotSpot(TM) Client VM (build 1.4.1_01-14, mixed mode)

    682. 

  682. Mac OS X 	10.2.8 	ppc 	using 1.3 JVM: java.vm.version=1.3.1_03-74, mrj.version=3.3.2; using 1.4 JVM: java.vm.version=1.4.1_01-24, mrj.version=69.1

    683. 

  683. Mac OS X 	10.3.1, 10.3.2, 10.3.3, 10.3.4 	ppc 	JDK 1.4.x

    684. 

  684. Mac OS X 	10.3.8 	ppc 	Mac OS X 10.3.8 Server; using 1.3 JVM: java.vm.version=1.3.1_03-76, mrj.version=3.3.3; using 1.4 JVM: java.vm.version=1.4.2-38; mrj.version=141.3

    685. 

  685. Windows 95 	4.0 	x86 	

    686. 

  686. Windows 98 	4.10 	x86 	Note, that if you run Sun JDK 1.2.1 or 1.2.2 Windows 98 identifies itself as Windows 95.

    687. 

  687. Windows Me 	4.90 	x86 	

    688. 

  688. Windows NT 	4.0 	x86 	

    689. 

  689. Windows 2000 	5.0 	x86 	

    690. 

  690. Windows XP 	5.1 	x86 	Note, that if you run older Java runtimes Windows XP identifies itself as Windows 2000.

    691. 

  691. Windows 2003 	5.2 	x86 	java.vm.version=1.4.2_06-b03; Note, that Windows Server 2003 identifies itself only as Windows 2003.

    692. 

  692. Windows CE 	3.0 build 11171 	arm 	Compaq iPAQ 3950 (PocketPC 2002)

    693. 

  693. OS/2 	20.40 	x86 	

    694. 

  694. Solaris 	2.x 	sparc 	

    695. 

  695. SunOS 	5.7 	sparc 	Sun Ultra 5 running Solaris 2.7

    696. 

  696. SunOS 	5.8 	sparc 	Sun Ultra 2 running Solaris 8

    697. 

  697. SunOS 	5.9 	sparc 	Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.0_01-b03)

    698. 

  698. Java HotSpot(TM) Client VM (build 1.4.0_01-b03, mixed mode)

    699. 

  699. MPE/iX 	C.55.00 	PA-RISC 	

    700. 

  700. HP-UX 	B.10.20 	PA-RISC 	JDK 1.1.x

    701. 

  701. HP-UX 	B.11.00 	PA-RISC 	JDK 1.1.x

    702. 

  702. HP-UX 	B.11.11 	PA-RISC 	JDK 1.1.x

    703. 

  703. HP-UX 	B.11.11 	PA_RISC 	JDK 1.2.x/1.3.x; note Java 2 returns PA_RISC and Java 1 returns PA-RISC

    704. 

  704. HP-UX 	B.11.00 	PA_RISC 	JDK 1.2.x/1.3.x

    705. 

  705. HP-UX 	B.11.23 	IA64N 	JDK 1.4.x

    706. 

  706. HP-UX 	B.11.11 	PA_RISC2.0 	JDK 1.3.x or JDK 1.4.x, when run on a PA-RISC 2.0 system

    707. 

  707. HP-UX 	B.11.11 	PA_RISC 	JDK 1.2.x, even when run on a PA-RISC 2.0 system

    708. 

  708. HP-UX 	B.11.11 	PA-RISC 	JDK 1.1.x, even when run on a PA-RISC 2.0 system

    709. 

  709. AIX 	5.2 	ppc64 	sun.arch.data.model=64

    710. 

  710. AIX 	4.3 	Power 	

    711. 

  711. AIX 	4.1 	POWER_RS 	

    712. 

  712. OS/390 	390 	02.10.00 	J2RE 1.3.1 IBM OS/390 Persistent Reusable VM

    713. 

  713. FreeBSD 	2.2.2-RELEASE 	x86 	

    714. 

  714. Irix 	6.3 	mips 	

    715. 

  715. Digital Unix 	4.0 	alpha 	

    716. 

  716. NetWare 4.11 	4.11 	x86 	

    717. 

  717. OSF1 	V5.1 	alpha 	Java 1.3.1 on Compaq (now HP) Tru64 Unix V5.1

    718. 

  718. OpenVMS 	V7.2-1 	alpha 	Java 1.3.1_1 on OpenVMS 7.2

    719. 

  719. */
    720.