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); }
/// <summary> /// Resize table by creating new and copying old values /// </summary> /// <param name="exGrid"></param> /// <param name="oldDataTable"></param> /// <param name="rowsNum"></param> /// <param name="colsNum"></param> private void ResizeTable(ExtendedDataGrid exGrid, DataTable oldDataTable, int rowsNum, int colsNum) { int boundRows = oldDataTable.Rows.Count < rowsNum ? oldDataTable.Rows.Count : rowsNum; int boundCols = oldDataTable.Columns.Count < colsNum ? oldDataTable.Columns.Count : colsNum; DataTable newDataTable = new DataTable(oldDataTable.TableName); for (int i = 0; i < colsNum; i++) { newDataTable.Columns.Add(new DataColumn(SSColumns.ToString(i), typeof(SpreadsheetCell))); } for (int i = 0; i < rowsNum; i++) { var row = newDataTable.NewRow(); newDataTable.Rows.Add(row); for (int j = 0; j < colsNum; j++) { string cellName = SSColumns.ToString(j) + i; row[j] = new SpreadsheetCell(string.Empty, newDataTable, new SpreadsheetCellCustomInfo(new CellName(cellName, i, j))); } } // copy data for (int i = 0; i < boundRows; i++) { for (int j = 0; j < boundCols; j++) { SpreadsheetCell cell = newDataTable.GetSpreadsheetCell(i, j); SpreadsheetCell oldCell = oldDataTable.GetSpreadsheetCell(i, j); cell.Content = oldCell.Content; cell.Tag = oldCell.Tag; } } this.DataTables[this.CurrentSheetNumber] = newDataTable; // VERY VERY DIRTY HACK because nulling throws exception, dunno why try { exGrid.ItemsSource = null; } catch { } exGrid.ItemsSource = newDataTable.DefaultView; }
private void CheckForRecursion(SpreadsheetCell cell, DataTable dataTable, string cellNameToCheck) { if (cell.Tag.CellName.FullName == cellNameToCheck) { throw new RecursiveCellCallException("Infinity recursive loop detected!"); } foreach (var consCellName in cell.Tag.DependentCells) { var consCell = dataTable.GetSpreadsheetCell(consCellName.Row, consCellName.Col); CheckForRecursion(consCell, dataTable, cellNameToCheck); } }