private void FillIfListView(TreeNode IfNode)
{
string strOpenParen = TFormulaMessages.TokenString(TFormulaToken.fmOpenParen);
string strCloseParen = TFormulaMessages.TokenString(TFormulaToken.fmCloseParen);
TImplementedFunctionList fil = new TImplementedFunctionList();
foreach (TImplementedFunction fi in fil.Values)
{
TreeNode n = IfNode.Nodes.Add(fi.FunctionName.ToLower());
string ParamList = string.Empty;
int MinParamCount = fi.MinArgCount;
if (MinParamCount <= 0)
{
MinParamCount = 1;
}
int MaxParamCount = fi.MaxArgCount;
string Optional = MaxParamCount > MinParamCount ? "..." : "";
ParamList = strOpenParen.PadRight(strOpenParen.Length + MinParamCount - 1, TFormulaMessages.TokenChar(TFormulaToken.fmFunctionSep)) + Optional + strCloseParen;
string[] sc = { n.Text + ParamList };
n.ImageIndex = imgOneExtra;
n.SelectedImageIndex = n.ImageIndex;
n.Tag = sc;
}
}
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 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 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)));
}
}
internal TRPNExpression(string Condition, ExcelFile Xls, TBand CurrentBand, FlexCelReport fr, TStackData Stack)
{
if (Condition != null && !Condition.StartsWith(TFormulaMessages.TokenString(TFormulaToken.fmStartFormula)))
{
Condition = TFormulaMessages.TokenString(TFormulaToken.fmStartFormula) + Condition;
}
TFormulaConvertTextWithTagsToInternal Parser = new TFormulaConvertTextWithTagsToInternal(Xls, Condition, CurrentBand, fr, Stack);
wi = new TWorkbookInfo(Xls, Xls.ActiveSheet, 0, 0, 0, 0, 0, 0, false);
Parser.Parse();
Fmla = Parser.GetTokens();
}
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 string ReadCachedValueTxt(object obj, int MaxStringConstantLen)
{
if (obj is double)
{
return(TFormulaMessages.FloatToString((double)obj));
}
if (obj is string)
{
string s = fts(TFormulaToken.fmStr) + GetString(FlxConvert.ToString(obj), MaxStringConstantLen) + fts(TFormulaToken.fmStr); return(s);
}
if (obj is bool)
{
return(GetBoolText((bool)obj));
}
if (obj is TFlxFormulaErrorValue)
{
return(GetErrorText((TFlxFormulaErrorValue)obj));
}
XlsMessages.ThrowException(XlsErr.ErrBadToken, FlxConvert.ToString(obj));
return(String.Empty);
}
protected void CalcBounds(TOneCellValue aRange)
{
TValueAndXF val = new TValueAndXF();
val.Workbook = aRange.Workbook;
aRange.Evaluate(0, 0, 0, 0, val);
TXlsNamedRange XlsRange = val.Workbook.GetNamedRange(FlxConvert.ToString(val.Value), -1, val.Workbook.ActiveSheet);
if (XlsRange == null)
{
XlsRange = val.Workbook.GetNamedRange(FlxConvert.ToString(val.Value), -1, 0);
}
if (XlsRange != null)
{
FTop = XlsRange.Top;
FLeft = XlsRange.Left;
Bottom = XlsRange.Bottom;
Right = XlsRange.Right;
Sheet1 = XlsRange.SheetIndex;
Sheet2 = XlsRange.SheetIndex;
RowAbs1 = false; ColAbs1 = false; RowAbs2 = false; ColAbs2 = false;
return;
}
string[] Addresses = FlxConvert.ToString(val.Value).Split(TFormulaMessages.TokenChar(TFormulaToken.fmRangeSep));
if (Addresses == null || (Addresses.Length != 2 && Addresses.Length != 1))
{
FlxMessages.ThrowException(FlxErr.ErrInvalidRef, FlxConvert.ToString(val.Value));
}
TCellAddress FirstCell = new TCellAddress(Addresses[0]);
if (FirstCell.Sheet == null || FirstCell.Sheet.Length == 0)
{
Sheet1 = -1;
}
else
{
Sheet1 = aRange.Workbook.GetSheetIndex(FirstCell.Sheet);
}
FTop = FirstCell.Row;
FLeft = FirstCell.Col;
RowAbs1 = FirstCell.RowAbsolute;
ColAbs1 = FirstCell.ColAbsolute;
if (Addresses.Length > 1)
{
FirstCell = new TCellAddress(Addresses[1]);
}
if (FirstCell.Sheet == null || FirstCell.Sheet.Length == 0)
{
Sheet2 = Sheet1;
}
else
{
Sheet2 = aRange.Workbook.GetSheetIndex(FirstCell.Sheet);
}
Bottom = FirstCell.Row;
Right = FirstCell.Col;
RowAbs2 = FirstCell.RowAbsolute;
ColAbs2 = FirstCell.ColAbsolute;
}
internal override bool OnFound(ExcelFile xls, object oldval, int row, int col)
{
TFormula oldFmla = oldval as TFormula;
if (!FSearchInFormulas && oldFmla != null)
{
return(false); //do not replace if it is a formula.
}
if (oldval == null)
{
return(false);
}
TRichString OldStr = oldval as TRichString;
if (OldStr == null)
{
OldStr = new TRichString(FlxConvert.ToString(oldval));
}
TRichString newStr = OldStr.Replace(SearchToStr, NewValue.ToString(), FCaseInsensitive);
if (newStr != null && newStr.Value != null && newStr.Length > 0 && newStr.Value.StartsWith(TFormulaMessages.TokenString(TFormulaToken.fmStartFormula)))
{
TFormulaSpan Span = oldFmla != null ? oldFmla.Span : new TFormulaSpan();
xls.SetCellValue(row, col, new TFormula(newStr.Value, null, Span));
}
else
{
if (oldFmla != null && !oldFmla.Span.IsOneCell)
{
return(false); //can't replace a shared formula with simple text.
}
xls.SetCellFromString(row, col, newStr);
}
ReplaceCount++;
return(false);
}
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 fts(TFormulaToken t)
{
return(TFormulaMessages.TokenString(t));
}
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");
}
