*/
protected static Ptg calcCell(Ptg[] operands)
throws FunctionNotSupportedException {
String type= operands[0].getValue().toString().toLowerCase();
PtgRef ref= null;
BiffRec cell= null;
if (operands.length > 1) {
ref= (PtgRef) operands[1];
try {
cell= ref.getParentRec().getWorkBook().getCell(ref.getLocationWithSheet());
} catch (CellNotFoundException e) {
try {
String sh= null;
try {
sh= ref.getSheetName();
} catch (WorkSheetNotFoundException we) {; }
if (sh==null) sh= ref.getParentRec().getSheet().getSheetName();
cell= ref.getParentRec().getWorkBook().getWorkSheetByName(sh).addValue(null, ref.getLocation());
} catch (Exception ex) {
return new PtgErr(PtgErr.ERROR_VALUE);
}
} catch (Exception e) {
return new PtgErr(PtgErr.ERROR_VALUE);
}
// If ref param is omitted, the information specified in the info_type argument
// is returned for the last cell that was changed
} else if (!type.equals("filename"))// no ref was passed in and option is not "filename"
// We cannot determine which is the "last cell" they are referencing;
throw new FunctionNotSupportedException("Worsheet function CELL with no reference parameter is not supported");
else // filename option can use any biffrec ...
cell= operands[0].getParentRec();
// at this point both ref (PtgRef) and r (BiffRec) should be valid
try {
if (type.equals("address")) {
PtgRef newref= ref;
newref.clearLocationCache();
newref.fColRel= false; // make absolute
newref.fRwRel= false;
return new PtgStr(newref.getLocation());
} else if (type.equals("col")) {
return new PtgNumber(ref.getIntLocation()[1]+1);
} else if (type.equals("color")) { // The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).
String s= cell.getFormatPattern();
if (s.indexOf(";[Red")>-1)
return new PtgNumber(1);
return new PtgNumber(0);
} else if (type.equals("contents")) {// Value of the upper-left cell in reference; not a formula.
return new PtgStr(cell.getStringVal());
} else if (type.equals("filename")) {
String f= cell.getWorkBook().getFileName();
String sh= cell.getSheet().getSheetName();
int i= f.lastIndexOf(java.io.File.separatorChar);
f= f.substring(0, i+1)+ "[" + f.substring(i+1);
f+= "]" + sh;
return new PtgStr(f);
} 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.
String s= cell.getFormatPattern();
String ret= "G"; // default?
if (s.equals("General") ||
s.equals("# ?/?") ||
s.equals("# ??/??")) {
ret= "G";
} else if (s.equals("0")) {
ret= "F0";
} else if (s.equals("#,##0")) {
ret= ",0";
} else if (s.equals("0.00")) {
ret= "F2";
} else if (s.equals("#,##0.00")) {
ret= ", 2";
} else if (s.equals("$#,##0_);($#,##0)")) {
ret= "C0";
} else if (s.equals("$#,##0_);[Red]($#,##0)")) {
ret= "C0-";
} else if (s.equals("$#,##0.00_);($#,##0.00)")) {
ret= "C2";
} else if (s.equals("$#,##0.00_);[Red]($#,##0.00)")) {
ret= "C2-";
} else if (s.equals("0%")) {
ret= "P0";
} else if (s.equals("0.00%")) {
ret= "P2";
} else if (s.equals("0.00E+00")) {
ret= "S2";
// m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
} else if (s.equals("m/d/yy") ||
s.equals("m/d/yy h:mm") ||
s.equals("mm/dd/yy") ||
s.equals("mm-dd-yy")) { // added last to accomodate Excel's regional short date setting (format #14)
ret= "D4";
} else if (s.equals("d-mmm-yy") ||
s.equals("dd-mmm-yy")) {
ret= "D1";
} else if (s.equals("d-mmm") ||
s.equals("dd-mmm")) {
ret= "D2";
} else if (s.equals("mmm-yy")) {
ret= "D3";
} else if (s.equals("mm/dd")) {
ret= "D5";
} else if (s.equals("h:mm AM/PM")) {
ret= "D7";
} else if (s.equals("h:mm:ss AM/PM")) {
ret= "D6";
} else if (s.equals("h:mm")) {
ret= "D9";
} else if (s.equals("h:mm:ss")) {
ret= "D8";
}
return new PtgStr(ret);
} else if (type.equals("parentheses")) {
String s= cell.getFormatPattern();
if (s.startsWith("("))
return new PtgNumber(1);
return new PtgNumber(0);
} else if (type.equals("prefix")) {
// TODO: THIS IS NOT CORRECT - EITHER INFORM USER OR ??
// DOESN'T APPEAR TO MATCH EXCEL
//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.
int al= cell.getXfRec().getHorizontalAlignment();
if(al==FormatConstants.ALIGN_LEFT)
return new PtgStr("'");
if(al==FormatConstants.ALIGN_CENTER)
return new PtgStr("^");
if(al==FormatConstants.ALIGN_RIGHT)
return new PtgStr("\"");
if(al==FormatConstants.ALIGN_FILL)
return new PtgStr("\\");
return new PtgStr("");
} else if (type.equals("protect")) {
if (cell.getXfRec().isLocked())
return new PtgNumber(1);
return new PtgNumber(0);
} else if (type.equals("row")) {
return new PtgNumber(ref.getIntLocation()[0]+1);
} else if (type.equals("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.
if (((XLSRecord) cell).isBlank)
return new PtgStr("b");
if (cell instanceof Labelsst)
return new PtgStr("l");
return new PtgStr("v");
} else if (type.equals("width")) {
int n= 0;
n= cell.getSheet().getColInfo(cell.getColNumber()).getColWidthInChars();
return new PtgNumber(n);
}
} catch (Exception e) {
Logger.logWarn("CELL: unable to calculate: " + e.toString());
}