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