private static string GetArea3D(bool R1C1, TArea3dToken Token, int CellRow, int CellCol, TWorkbookGlobals Globals, bool IsErr, bool WritingXlsx)
{
if (Globals == null)
{
return(" <Ref> ");
}
int ExternSheet = Token.FExternSheet;
if (ExternSheet == 0xFFFF)
{
return(TFormulaMessages.ErrString(TFlxFormulaErrorValue.ErrRef));
}
int Row1 = IsErr ? -1 : Token.GetRow1(CellRow) + 1;
int Row2 = IsErr ? -1 : Token.GetRow2(CellRow) + 1;
int Col1 = IsErr ? -1 : Token.GetCol1(CellCol) + 1;
int Col2 = IsErr ? -1 : Token.GetCol2(CellCol) + 1;
if (Col1 == 1 && Col2 == FlxConsts.Max_Columns + 1)
{
return(GetSheetName(ExternSheet, Globals, WritingXlsx) + GetRowRange(R1C1, Row1, Row2, CellRow, Token.RowAbs1, Token.RowAbs2));
}
if (Row1 == 1 && Row2 == FlxConsts.Max_Rows + 1)
{
return(GetSheetName(ExternSheet, Globals, WritingXlsx) + GetColRange(R1C1, Col1, Col2, CellCol, Token.ColAbs1, Token.ColAbs2));
}
string RestOfArea = IsErr? String.Empty: fts(TFormulaToken.fmRangeSep) + Get1Ref(R1C1, Row2, Col2, CellRow, CellCol, Token.RowAbs2, Token.ColAbs2);
return(GetSheetName(ExternSheet, Globals, WritingXlsx) + Get1Ref(R1C1, Row1, Col1, CellRow, CellCol, Token.RowAbs1, Token.ColAbs1) + RestOfArea);
}
private static string Get1Ref(bool R1C1, int Row, int Col, int CellRow, int CellCol, bool RowAbs, bool ColAbs)
{
if (Row < 1 || Col < 1 || Row > FlxConsts.Max_Rows + 1 || Col > FlxConsts.Max_Columns + 1)
{
return(TFormulaMessages.ErrString(TFlxFormulaErrorValue.ErrRef));
}
if (R1C1)
{
return(Get1R1C1Ref(Row, Col, CellRow, CellCol, RowAbs, ColAbs));
}
string Result = ""; //We won't use a string builder here as it would probably have more overhead.
if (ColAbs)
{
Result = fts(TFormulaToken.fmAbsoluteRef);
}
Result = Result + TCellAddress.EncodeColumn(Col);
if (RowAbs)
{
Result = Result + fts(TFormulaToken.fmAbsoluteRef);
}
return(Result + Row.ToString());
}
private static string GetSheetName(int ExternSheet, TWorkbookGlobals Globals, bool WritingXlsx)
{
if (ExternSheet == 0xFFFF)
{
return(TFormulaMessages.ErrString(TFlxFormulaErrorValue.ErrRef));
}
return(Globals.References.GetSheetName(ExternSheet, Globals, WritingXlsx));
}
internal override void SaveToXlsx(TOpenXmlWriter DataStream, int Row, TCellList CellList, bool Dates1904)
{
if (ErrFlag == 0)
{
DataStream.WriteAtt("t", "b");
DataStream.WriteElement("v", BoolErr == 1);
}
else
{
DataStream.WriteAtt("t", "e");
DataStream.WriteElement("v", TFormulaMessages.ErrString(ErrCodeToFlxFormulaErrorValue(BoolErr)));
}
}
private static string GetRef3D(bool R1C1, TRef3dToken Token, int CellRow, int CellCol, TWorkbookGlobals Globals, bool IsErr, bool WritingXlsx)
{
if (Globals == null)
{
return(" <Ref> ");
}
int ExternSheet = Token.FExternSheet;
if (ExternSheet == 0xFFFF)
{
return(TFormulaMessages.ErrString(TFlxFormulaErrorValue.ErrRef));
}
int Row = IsErr ? -1 : Token.GetRow1(CellRow) + 1;
int Col = IsErr? -1: Token.GetCol1(CellCol) + 1;
return(GetSheetName(ExternSheet, Globals, WritingXlsx) + Get1Ref(R1C1, Row, Col, CellRow, CellCol, Token.RowAbs, Token.ColAbs));
}
private static void Evaluate(TBaseParsedToken Token, bool R1C1, int CellRow, int CellCol, ptg BaseToken, TParsedTokenList RPN, ICellList CellList, TWorkbookGlobals Globals, TFormulaStack ParsedStack, ref string StartFormula, int MaxStringConstantLen, bool WritingXlsx)
{
string s1; string s2; string s3;
switch (BaseToken)
{
case ptg.Exp: AddArray(Token, CellRow, CellCol, CellList, Globals, ParsedStack, MaxStringConstantLen, WritingXlsx);
StartFormula = String.Empty;
break;
case ptg.Tbl: AddTable(R1C1, Token, CellList, ParsedStack);
StartFormula = String.Empty;
break;
case ptg.Add: s2 = ParsedStack.Pop(); s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmPlus) + s2); break;
case ptg.Sub: s2 = ParsedStack.Pop(); s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmMinus) + s2); break;
case ptg.Mul: s2 = ParsedStack.Pop(); s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmMul) + s2); break;
case ptg.Div: s2 = ParsedStack.Pop(); s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmDiv) + s2); break;
case ptg.Power: s2 = ParsedStack.Pop(); s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmPower) + s2); break;
case ptg.Concat: s2 = ParsedStack.Pop(); s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmAnd) + s2); break;
case ptg.LT: s2 = ParsedStack.Pop(); s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmLT) + s2); break;
case ptg.LE: s2 = ParsedStack.Pop(); s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmLE) + s2); break;
case ptg.EQ: s2 = ParsedStack.Pop(); s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmEQ) + s2); break;
case ptg.GE: s2 = ParsedStack.Pop(); s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmGE) + s2); break;
case ptg.GT: s2 = ParsedStack.Pop(); s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmGT) + s2); break;
case ptg.NE: s2 = ParsedStack.Pop(); s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmNE) + s2); break;
case ptg.Isect: s2 = ParsedStack.Pop(); s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmIntersect) + s2); break;
case ptg.Union: s2 = ParsedStack.Pop(); s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmUnion) + s2); break;
case ptg.Range: s2 = ParsedStack.Pop(); s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmRangeSep) + s2); break;
case ptg.Uplus: s1 = ParsedStack.Pop(); ParsedStack.Push(ParsedStack.FmSpaces + fts(TFormulaToken.fmPlus) + s1); break;
case ptg.Uminus: s1 = ParsedStack.Pop(); ParsedStack.Push(ParsedStack.FmSpaces + fts(TFormulaToken.fmMinus) + s1); break;
case ptg.Percent: s1 = ParsedStack.Pop(); ParsedStack.Push(s1 + ParsedStack.FmSpaces + fts(TFormulaToken.fmPercent)); break;
case ptg.Paren: s1 = ParsedStack.Pop(); ParsedStack.Push(ParsedStack.FmPreSpaces + fts(TFormulaToken.fmOpenParen) + s1 + ParsedStack.FmPostSpaces + fts(TFormulaToken.fmCloseParen)); break;
case ptg.MissArg: ParsedStack.Push(ParsedStack.FmSpaces); break;
case ptg.Str:
ParsedStack.Push(ParsedStack.FmSpaces + fts(TFormulaToken.fmStr)
+ GetString(((TStrDataToken)Token).GetData(), MaxStringConstantLen) + fts(TFormulaToken.fmStr));
break;
case ptg.Attr: ProcessAttr(Token, ParsedStack); break;
case ptg.Sheet: break;
case ptg.EndSheet: break;
case ptg.Err: ParsedStack.Push(ParsedStack.FmSpaces + GetErrorText(((TErrDataToken)Token).GetData())); break;
case ptg.Bool: ParsedStack.Push(ParsedStack.FmSpaces + GetBoolText(((TBoolDataToken)Token).GetData())); break;
case ptg.Int: ParsedStack.Push(ParsedStack.FmSpaces + TFormulaMessages.FloatToString(((TIntDataToken)Token).GetData())); break;
case ptg.Num: ParsedStack.Push(ParsedStack.FmSpaces + TFormulaMessages.FloatToString(((TNumDataToken)Token).GetData())); break;
case ptg.Array: ParsedStack.Push(ParsedStack.FmSpaces + GetArrayText(((TArrayDataToken)Token).GetData, MaxStringConstantLen)); break;
case ptg.Func:
case ptg.FuncVar:
int ArgCount;
bool IsAddin;
StringBuilder sb = new StringBuilder();
TBaseFunctionToken FunctionTk = Token as TBaseFunctionToken;
s3 = ParsedStack.FmSpaces + GetFuncName(FunctionTk, out ArgCount, WritingXlsx, out IsAddin);
if (ArgCount > 0)
{
sb.Append(ParsedStack.Pop());
}
for (int i = 2; i <= ArgCount; i++)
{
s1 = ParsedStack.Pop();
sb.Insert(0, s1 + fts(TFormulaToken.fmFunctionSep));
}
if (IsAddin)
{
s3 += ConvertInternalFunctionName(Globals, ParsedStack.Pop());
}
ParsedStack.Push(s3 + fts(TFormulaToken.fmOpenParen) + ParsedStack.FmPreSpaces + sb.ToString() + ParsedStack.FmPostSpaces + fts(TFormulaToken.fmCloseParen));
break;
case ptg.Name: ParsedStack.Push(ParsedStack.FmSpaces + GetName(((TNameToken)Token).NameIndex, -1, Globals, WritingXlsx)); break;
case ptg.RefN:
case ptg.Ref: ParsedStack.Push(ParsedStack.FmSpaces + GetRef(R1C1, (TRefToken)Token, CellRow, CellCol)); break;
case ptg.AreaN:
case ptg.Area: ParsedStack.Push(ParsedStack.FmSpaces + GetArea(R1C1, (TAreaToken)Token, CellRow, CellCol)); break;
case ptg.MemArea: break;
case ptg.MemErr: break;
case ptg.MemNoMem: break;
case ptg.MemFunc: break;
case ptg.RefErr: ParsedStack.Push(ParsedStack.FmSpaces + TFormulaMessages.ErrString(TFlxFormulaErrorValue.ErrRef)); break;
case ptg.AreaErr: ParsedStack.Push(ParsedStack.FmSpaces + TFormulaMessages.ErrString(TFlxFormulaErrorValue.ErrRef)); break;
case ptg.MemAreaN: break;
case ptg.MemNoMemN: break;
case ptg.NameX: ParsedStack.Push(ParsedStack.FmSpaces + GetNameX((TNameXToken)Token, Globals, WritingXlsx)); break;
case ptg.Ref3d: ParsedStack.Push(ParsedStack.FmSpaces + GetRef3D(R1C1, (TRef3dToken)Token, CellRow, CellCol, Globals, false, WritingXlsx)); break;
case ptg.Area3d: ParsedStack.Push(ParsedStack.FmSpaces + GetArea3D(R1C1, (TArea3dToken)Token, CellRow, CellCol, Globals, false, WritingXlsx)); break;
case ptg.Ref3dErr: ParsedStack.Push(ParsedStack.FmSpaces + GetRef3D(R1C1, (TRef3dToken)Token, -1, -1, Globals, true, WritingXlsx)); break;
case ptg.Area3dErr: ParsedStack.Push(ParsedStack.FmSpaces + GetArea3D(R1C1, (TArea3dToken)Token, CellRow, CellCol, Globals, true, WritingXlsx)); break;
default: XlsMessages.ThrowException(XlsErr.ErrBadToken, Token); break;
}
}
private static string GetErrorText(TFlxFormulaErrorValue err)
{
return(TFormulaMessages.ErrString(err));
}
private void AnalizeFile(string FileName, int Row, int Col)
{
XlsFile xls = new XlsFile();
xls.Open(FileName);
int XF = 0;
MessageBox.Show("Active sheet is \"" + xls.ActiveSheetByName + "\"");
object v = xls.GetCellValue(Row, Col, ref XF);
if (v == null)
{
MessageBox.Show("Cell A1 is empty");
return;
}
//Here we have all the kind of objects FlexCel can return.
switch (Type.GetTypeCode(v.GetType()))
{
case TypeCode.Boolean:
MessageBox.Show("Cell A1 is a boolean: " + (bool)v);
return;
case TypeCode.Double: //Remember, dates are doubles with date format.
TUIColor CellColor = Color.Empty;
bool HasDate, HasTime;
String CellValue = TFlxNumberFormat.FormatValue(v, xls.GetFormat(XF).Format, ref CellColor, xls, out HasDate, out HasTime).ToString();
if (HasDate || HasTime)
{
MessageBox.Show("Cell A1 is a DateTime value: " + FlxDateTime.FromOADate((double)v, xls.OptionsDates1904).ToString() + "\n" +
"The value is displayed as: " + CellValue);
}
else
{
MessageBox.Show("Cell A1 is a double: " + (double)v + "\n" +
"The value is displayed as: " + CellValue + "\n");
}
return;
case TypeCode.String:
MessageBox.Show("Cell A1 is a string: " + v.ToString());
return;
}
TFormula Fmla = v as TFormula;
if (Fmla != null)
{
MessageBox.Show("Cell A1 is a formula: " + Fmla.Text + " Value: " + Convert.ToString(Fmla.Result));
return;
}
TRichString RSt = v as TRichString;
if (RSt != null)
{
MessageBox.Show("Cell A1 is a formatted string: " + RSt.Value);
return;
}
if (v is TFlxFormulaErrorValue)
{
MessageBox.Show("Cell A1 is an error: " + TFormulaMessages.ErrString((TFlxFormulaErrorValue)v));
return;
}
throw new Exception("Unexpected value on cell");
}