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"); }