static void ImportTest() { SyntaxErrorListener errorListener = new SyntaxErrorListener(); string formula = "=MAX(B6-40;0)"; AntlrInputStream inputStream = new AntlrInputStream((string)formula); ExcelFormulaLexer spreadsheetLexer = new ExcelFormulaLexer(inputStream); spreadsheetLexer.AddErrorListener(errorListener); CommonTokenStream commonTokenStream = new CommonTokenStream(spreadsheetLexer); ExcelFormulaParser excelFormulaParser = new ExcelFormulaParser(commonTokenStream); ExcelFormulaParser.ExcelExprContext context = excelFormulaParser.excelExpr(); if (errorListener.HasError) { Logger.DebugLine($"Found Lexer Error - Dont processing formula {(string)formula}", 10); return; } if (excelFormulaParser.NumberOfSyntaxErrors > 0) { Logger.DebugLine($"Found Syntax Error - Dont processing formula {(string)formula}", 10); return; } ExcelFormulaVisitor visitor = new ExcelFormulaVisitor(); string formulaText = visitor.Visit(context); Logger.DebugLine($"FormulaText: {formulaText}", 10); }
private void compareArrays(object[,] valueArray, object[,] formulaArray) { var x = valueArray.GetLength(0); var y = valueArray.GetLength(1); var xF = formulaArray.GetLength(0); var yF = formulaArray.GetLength(1); if (x != xF || y != yF) { Logger.DebugLine("Compare Arrays: x and y dimensions dont match between arrays", 1); Logger.DebugLine($"x: {x}, xF: {xF}, y: {y}, yF: {yF}", 1); } for (int i = 1; i <= x; i++) { for (int j = 1; j <= y; j++) { var value = valueArray[i, j]; var formula = formulaArray[i, j]; var equals = false; if (value != null && formula != null) { if (value is double) { string valueString = ((double)value).ToString("G", CultureInfo.InvariantCulture); equals = ((string)formula).Equals(valueString); var valueStringComma = valueString.Replace(".", ","); var valueStringDot = valueString.Replace(",", "."); var formulaStringComma = ((string)formula).Replace(".", ","); var formulaStringDot = ((string)formula).Replace(",", "."); Logger.DebugLine($"Value is double, value: {valueString}, formula: {(string)formula}, equals: {equals}", 1); if (!equals) { equals = ((string)formula).Equals(valueStringComma) || ((string)formula).Equals(valueStringDot); Logger.DebugLine($"Dot Comma conversion check, valueStringComma: {valueStringComma}, valueStringDot: {valueStringDot}, formula: {(string)formula}, equals: {equals}", 1); } if (!equals) { string valueStringInstalled = ((double)value).ToString("G", CultureInfo.InstalledUICulture); equals = ((string)formula).Equals(valueStringInstalled); Logger.DebugLine($"Installed UI Culture ({CultureInfo.InstalledUICulture}) Equal check, value: {valueStringInstalled}, formula: {(string)formula}, equals: {equals}", 1); } if (!equals) { var culture = CultureInfo.CreateSpecificCulture("de-DE"); string valueStringInstalled = ((double)value).ToString("G", culture); equals = ((string)formula).Equals(valueStringInstalled); Logger.DebugLine($"Specific Culture ({culture}) Equal check, value: {valueStringInstalled}, formula: {(string)formula}, equals: {equals}", 1); } if (!equals) { string valueStringDoubleFixedPoint = ((double)value).ToString(FormatStrings.DoubleFixedPoint, CultureInfo.InvariantCulture); equals = ((string)formula).Equals(valueStringDoubleFixedPoint); Logger.DebugLine($"Format DoubleFixedPoint with Culture ({CultureInfo.InvariantCulture}) Equal check, value: {valueStringDoubleFixedPoint}, formula: {(string)formula}, equals: {equals}", 1); } if (!equals && double.TryParse((string)formula, out double parsedFormula)) { equals = IsDoubleEqual((double)value, parsedFormula); Logger.DebugLine($"Parsed formula to double, value: {(double)value}, parsedFormula: {parsedFormula}, equals: {equals}", 1); } if (!equals && (double.TryParse(formulaStringComma, out parsedFormula) || double.TryParse(formulaStringDot, out parsedFormula))) { equals = IsDoubleEqual((double)value, parsedFormula); Logger.DebugLine($"Parsed formula dot comma to double, value: {(double)value}, parsedFormula: {parsedFormula}, equals: {equals}", 1); } } else if (value is DateTime) { Logger.DebugLine($"Value is Datetime, value: {value.ToString()}, formula: {(string)formula}", 1); if (int.TryParse((string)formula, out int result)) { DateTime date = new DateTime(1900, 1, 1).AddDays(result); equals = true; Logger.DebugLine($"Formula is int, date: {date.ToString()}, value date: {(DateTime)value}", 1); } else if (DateTime.TryParse((string)formula, out DateTime resultDate)) { equals = true; Logger.DebugLine($"Formula is datetime, resultDate: {resultDate.ToString()}, value date: {(DateTime)value}", 1); } else if (double.TryParse((string)formula, out double resultDouble)) { equals = true; //DateTime date = new DateTime(1900, 1, 1).AddDays(resultDouble); Logger.DebugLine($"Formula is double, resultDouble: {resultDouble.ToString()}, value date: {(DateTime)value}", 1); } else { Logger.DebugLine($"Couldnt compare DateTime, value: {value.ToString()}, formula: {(string)formula}", 2); equals = false; } } else { equals = formula.Equals(value.ToString()); } } if (equals) { Logger.DebugLine($"{j}, {i}: Value equals Formula"); addConvertedValue(value, i, j); } else if (value == formula) { Logger.DebugLine($"{j}, {i}: Value == Formula"); addConvertedValue(value, i, j); } else if (value == null && string.IsNullOrEmpty((string)formula)) { Logger.DebugLine($"{j}, {i}: Value equals Formula: Both empty"); } else { printObject(value, "Value"); Logger.Debug("----"); printObject(formula, "Formula"); Logger.DebugLine(""); Logger.DebugLine($"{j}, {i}: Value does not equal Formula", 1); SyntaxErrorListener errorListener = new SyntaxErrorListener(); AntlrInputStream inputStream = new AntlrInputStream((string)formula); ExcelFormulaLexer spreadsheetLexer = new ExcelFormulaLexer(inputStream); spreadsheetLexer.RemoveErrorListeners(); spreadsheetLexer.AddErrorListener(errorListener); CommonTokenStream commonTokenStream = new CommonTokenStream(spreadsheetLexer); ExcelFormulaParser excelFormulaParser = new ExcelFormulaParser(commonTokenStream); ExcelFormulaParser.ExcelExprContext context = excelFormulaParser.excelExpr(); if (errorListener.HasError) { Logger.DebugLine($"Found Lexer Error - Dont process formula at {i}, {j} : {(string)formula}", 5); if (GlobalSettings.ImportStopAtSyntaxError) { Console.WriteLine($"Lexer Error - Enter to continue", 10); Console.ReadLine(); } continue; } if (excelFormulaParser.NumberOfSyntaxErrors > 0) { Logger.DebugLine($"Found Syntax Error - Dont process formula at {i}, {j} : {(string)formula}", 5); if (GlobalSettings.ImportStopAtSyntaxError) { Console.WriteLine($"Syntax Error - Enter to continue", 10); Console.ReadLine(); } continue; } ExcelFormulaVisitor visitor = new ExcelFormulaVisitor(); string formulaText = visitor.Visit(context); if (visitor.Error) { continue; } Logger.DebugLine($"FormulaText: {formulaText}", 1); convertedText += $"C[{j},{i}] = {{{formulaText}}} ;\n"; } } } }