private static string ReplaceNamedGroup(string input, Match m)
{
var capt = m.Groups["Range"].Captures.OfType <Capture>().FirstOrDefault();
if (capt == null)
{
return(m.Value);
}
var col1 = SSColumns.Parse(m.Groups["Column1"].Value);
var row1 = int.Parse(m.Groups["Row1"].Value);
var col2 = SSColumns.Parse(m.Groups["Column2"].Value);
var row2 = int.Parse(m.Groups["Row2"].Value);
var colFrom = col1 < col2 ? col1 : col2;
var colTo = colFrom == col1 ? col2 : col1;
var rowFrom = row1 < row2 ? row1 : row2;
var rowTo = rowFrom == row1 ? row2 : row1;
StringBuilder extendedRange = new StringBuilder();
for (int i = colFrom; i <= colTo; i++)
{
for (int j = rowFrom; j <= rowTo; j++)
{
extendedRange.Append(SSColumns.ToString(i) + j);
if (i != colTo || j != rowTo)
{
extendedRange.Append(",");
}
}
}
var sb = new StringBuilder(input);
sb.Remove(capt.Index, capt.Length);
sb.Insert(capt.Index, extendedRange.ToString());
return(sb.ToString());
}
private double EvaluateExpression(string expression, DataTable dataTable)
{
// replace B1:B7 => B1,B2,B3,B4,B5,B6,B7
expression = RegexReplacer.ReplaceRangeOperators(expression);
// Creating list of variables specified
List <VariableValue> variables = new List <VariableValue>();
const string patternForMatchVariables = @"(?<Cell>(?<Column>[a-zA-Z]{1,3})(?<Row>\d{1,3}))";
foreach (Match match in Regex.Matches(expression, patternForMatchVariables))
{
var cellName = match.Groups["Cell"].Value;
var col = SSColumns.Parse(match.Groups["Column"].Value);
var row = int.Parse(match.Groups["Row"].Value);
var spreadsheetCell = dataTable.GetSpreadsheetCell(row, col);
this.CheckForRecursion(spreadsheetCell, dataTable, this.CellName.FullName);
double cellValue;
if (spreadsheetCell != null)
{
if (!this.DependentCells.Contains(spreadsheetCell.Tag.CellName))
{
this.DependentCells.Add(spreadsheetCell.Tag.CellName);
Logger.WriteLogInfo(string.Format("Added [{0}] cell to dependent list of cell [{1}]",
spreadsheetCell.Tag.CellName.FullName, this.CellName.FullName));
}
if (this.ConsequentialCells.Contains(spreadsheetCell.Tag.CellName))
{
throw new RecursiveCellCallException("Infinity recursive loop detected!");
}
if (!spreadsheetCell.Tag.ConsequentialCells.Contains(this.CellName))
{
spreadsheetCell.Tag.ConsequentialCells.Add(this.CellName);
Logger.WriteLogInfo(string.Format("Added [{0}] cell to consequential list of cell [{1}]",
this.CellName.FullName, spreadsheetCell.Tag.CellName.FullName));
}
if (spreadsheetCell.Content == string.Empty)
{
cellValue = 0;
}
else
{
if (!double.TryParse(spreadsheetCell.Content, out cellValue))
{
throw new CellContensIsNotNumericException(string.Format("Cell [{0}] content must be numeric for using in formulas", cellName));
}
}
}
else
{
throw new CellIndexIsOutOfRange(string.Format("Cell [{0}] is not present in table", cellName));
}
if (!variables.Any(v => v.VariableName == cellName))
{
variables.Add(new VariableValue(cellValue, cellName));
}
}
return(this.EvaluateExpressionWithVariables(expression, variables));
}
