protected override void OnSelectionChanged(GridSelectionChangedEventArgs e)
{
base.OnSelectionChanged(e);
if (e.Reason == GridSelectionReason.MouseDown || e.Reason == GridSelectionReason.SetCurrentCell || e.Reason == GridSelectionReason.MouseMove || e.Reason == GridSelectionReason.SelectRange || e.Reason == GridSelectionReason.MouseUp)
{
this.InvalidateCell(GridRangeInfo.Row(0));
this.InvalidateCell(GridRangeInfo.Col(0));
GridRangeInfo range = e.Range;
if (e.Range.IsCols)
{
range = GetExpandedRange(range);
}
if (e.Range.IsRows)
{
range = GetExpandedRange(range);
}
if (e.Range.IsTable)
{
range = GetExpandedRange(range);
}
if (e.Range == null || e.Range.IsEmpty)
{
CellLocationText = "";
}
else if ((e.Range.Height == 1 && e.Range.Width == 1) || e.Reason == GridSelectionReason.MouseUp)
{
CellLocationText = string.Format("{0}{1}", GridRangeInfo.GetAlphaLabel(range.Left), range.Top);
}
else
{
CellLocationText = string.Format("{0}R x {1}C", range.Height, range.Width);
}
}
}
private void PlaceTextInCell(bool resetSelection, GridRangeInfo activeRange)
{
if (activeRange.IsEmpty)
{
return;
}
string range = GridRangeInfo.GetAlphaLabel(activeRange.Left) + GridRangeInfo.GetNumericLabel(activeRange.Top);
if (activeRange.Top != activeRange.Bottom || activeRange.Left != activeRange.Right)
{
range += ':' + GridRangeInfo.GetAlphaLabel(activeRange.Right) + GridRangeInfo.GetNumericLabel(activeRange.Bottom);
}
TextBox gtb = grid.CurrentCell.Renderer.CurrentCellUIElement as TextBox;
if (gtb != null)
{
int start = gtb.SelectionStart;
gtb.SelectedText = range;
if (resetSelection)
{
gtb.SelectionStart = start + range.ToString().Length;
gtb.SelectionLength = 0;
}
else
{
gtb.SelectionStart = start;
gtb.SelectionLength = range.ToString().Length;
}
placeTextDone = true;
currentCellModified = true;
InvalidateHeaders();
}
}
private void SetExcelLikeUI(GridQueryCellInfoEventArgs e)
{
if (e.Style.RowIndex == 0 && e.Style.ColumnIndex == 0)
{
#if !GraphicCellDomo
e.Style.Background = new SolidColorBrush(Colors.White);
e.Style.Font.FontWeight = FontWeights.Bold;
#endif
return;
}
else if (e.Style.RowIndex == 0)
{
e.Style.CellValue = GridRangeInfo.GetAlphaLabel(e.Cell.ColumnIndex);
#if !GraphicCellDomo
e.Style.Background = new SolidColorBrush(Colors.White);
#endif
e.Style.HorizontalAlignment = System.Windows.HorizontalAlignment.Center;
e.Style.VerticalAlignment = VerticalAlignment.Center;
return;
}
else if (e.Style.ColumnIndex == 0)
{
e.Style.CellValue = e.Style.RowIndex;
#if !GraphicCellDomo
e.Style.Background = new SolidColorBrush(Colors.White);
#endif
e.Style.Foreground = new SolidColorBrush(Colors.Black);
e.Style.HorizontalAlignment = System.Windows.HorizontalAlignment.Center;
}
}
void Model_QueryCellInfo(object sender, GridQueryCellInfoEventArgs e)
{
var dataContext = AssociatedObject.DataContext as ExcelImportViewModel;
if (dataContext != null)
{
SetExcelLikeUI(e);
if (e.Cell.RowIndex <= 0 || e.Cell.ColumnIndex <= 0)
{
return;
}
GridModel gridModel = sender as GridModel;
if (!e.Style.IsChanged && e.Style.IsEmpty)
{
int index = dataContext.ActiveTabIndex;
IWorksheet sheet = dataContext.Workbook.Worksheets[index];
if (sheet != null)
{
IRange range = sheet.Range;
if (e.Cell.RowIndex >= range.Row && e.Cell.ColumnIndex >= range.Column)
{
IRange rangeToConvert = sheet.Range[e.Cell.RowIndex, e.Cell.ColumnIndex];
GridModelImportExtensions.ConvertExcelRangeToVirtualGrid(e.Style, sheet, rangeToConvert, null);
if (gridModel != null && e.Style.FormulaTag == null)
{
string s = e.Style.Text;
if (s.Length > 0 && s[0] == '=')
{
s = s.Substring(1);
try
{
gridModel.FormulaEngine.FormulaContextCell = GridRangeInfo.GetAlphaLabel(e.Cell.ColumnIndex) + e.Cell.RowIndex.ToString();
e.Style.FormulaTag = new GridFormulaTag(gridModel.FormulaEngine.Parse(s), null, e.Cell.RowIndex, e.Cell.ColumnIndex);
string cellvalue = rangeToConvert.DisplayText;
e.Style.FormulaTag.Text = cellvalue;
}
catch (Exception ex)
{
e.Style.FormulaTag = new GridFormulaTag(ex.Message, ex.Message, e.Cell.RowIndex, e.Cell.ColumnIndex);
}
}
}
gridModel.Data[e.Cell.RowIndex, e.Cell.ColumnIndex] = e.Style.Store;
}
}
}
else
{
// To save the modified value.
int index = dataContext.ActiveTabIndex;
IWorksheet changedSheet = dataContext.Workbook.Worksheets[index];
if (changedSheet != null)
{
IRange range = changedSheet.Range[e.Cell.RowIndex, e.Cell.ColumnIndex];
SetAlignment(range, e.Style);
}
}
}
}
void Model_QueryCellInfo(object sender, GridQueryCellInfoEventArgs e)
{
if (e.Cell.RowIndex == 0 && e.Cell.ColumnIndex > 0)
{
e.Style.Text = GridRangeInfo.GetAlphaLabel(e.Cell.ColumnIndex);
}
else if (e.Cell.RowIndex > 0 && e.Cell.ColumnIndex == 0)
{
e.Style.Text = e.Cell.RowIndex.ToString();
}
}
void Model_QueryCellInfo(object sender, Syncfusion.Windows.Controls.Grid.GridQueryCellInfoEventArgs e)
{
if (e.Cell.RowIndex == 0 && e.Cell.ColumnIndex > 0)
{
e.Style.Text = GridRangeInfo.GetAlphaLabel(e.Cell.ColumnIndex);
e.Style.HorizontalAlignment = HorizontalAlignment.Center;
e.Style.VerticalAlignment = VerticalAlignment.Center;
}
else if (e.Cell.RowIndex > 0 && e.Cell.ColumnIndex == 0)
{
e.Style.Text = e.Cell.RowIndex.ToString();
e.Style.HorizontalAlignment = HorizontalAlignment.Center;
e.Style.VerticalAlignment = VerticalAlignment.Center;
}
}
private void Model_QueryCellInfo(object sender, Syncfusion.UI.Xaml.CellGrid.Styles.GridQueryCellInfoEventArgs e)
{
if (e.Cell.RowIndex == 0 && e.Cell.ColumnIndex == 0)
{
return;
}
else if (e.Cell.RowIndex == 0)
{
e.Style.CellValue = GridRangeInfo.GetAlphaLabel(e.Cell.ColumnIndex);
e.Style.HorizontalAlignment = HorizontalAlignment.Center;
}
else if (e.Cell.ColumnIndex == 0)
{
e.Style.CellValue = e.Cell.RowIndex;
e.Style.HorizontalAlignment = HorizontalAlignment.Center;
}
}
void Model_QueryCellInfo(object sender, GridQueryCellInfoEventArgs e)
{
if (e.Cell.RowIndex == 0 && e.Cell.ColumnIndex == 0)
{
return;
}
if (e.Cell.RowIndex == 0)
{
e.Style.CellValue = GridRangeInfo.GetAlphaLabel(e.Cell.ColumnIndex);
e.Style.VerticalAlignment = VerticalAlignment.Center;
e.Style.HorizontalAlignment = HorizontalAlignment.Center;
}
else if (e.Cell.ColumnIndex == 0)
{
e.Style.CellValue = e.Cell.RowIndex;
e.Style.VerticalAlignment = VerticalAlignment.Center;
e.Style.HorizontalAlignment = HorizontalAlignment.Center;
}
}
void Model_QueryCellInfo(object sender, GridQueryCellInfoEventArgs e)
{
if (e.Cell.RowIndex == 0 && e.Cell.ColumnIndex > 0)
{
//column header
e.Style.CellValue = GridRangeInfo.GetAlphaLabel(e.Cell.ColumnIndex);
e.Style.HorizontalAlignment = HorizontalAlignment.Center;
}
else if (e.Cell.ColumnIndex == 0 && e.Cell.RowIndex > 0)
{
//row header
e.Style.CellValue = e.Cell.RowIndex.ToString();
e.Style.HorizontalAlignment = HorizontalAlignment.Center;
}
else if (e.Cell.RowIndex == 0 && e.Cell.ColumnIndex == 0)
{
//TopLeft Cell
}
else if (e.Cell.RowIndex == 1 && e.Cell.ColumnIndex > 0)
{
//row 1
e.Style.Font.FontWeight = FontWeights.Bold;
}
else if (e.Cell.RowIndex > 1 && e.Cell.ColumnIndex > 0)
{
string s2 = this.grid.Model[e.Cell.RowIndex, 2].Text.Trim();
string s3 = this.grid.Model[e.Cell.RowIndex, 3].Text.Trim();
if (this.formulas.ContainsKey(s2.ToUpper()) && !s3.StartsWith("="))
{
e.Style.Font.FontWeight = FontWeights.Bold;
e.Style.Background = formulaNameBrush;
}
else
{
s2 = this.grid.Model[e.Cell.RowIndex, 1].Text;
if (s2.Length > 0)
{
e.Style.Background = rowBrush;
}
}
}
}
void Model_QueryCellInfo(object sender, GridQueryCellInfoEventArgs e)
{
if (e.Cell.RowIndex == 0 && e.Cell.ColumnIndex > 0)
{
e.Style.Text = GridRangeInfo.GetAlphaLabel(e.Cell.ColumnIndex);
e.Style.HorizontalAlignment = HorizontalAlignment.Center;
e.Style.VerticalAlignment = VerticalAlignment.Center;
}
else if (e.Cell.ColumnIndex == 0 && e.Cell.RowIndex > 0)
{
e.Style.Text = e.Cell.RowIndex.ToString();
e.Style.HorizontalAlignment = HorizontalAlignment.Center;
e.Style.VerticalAlignment = VerticalAlignment.Center;
}
else if (e.Style.CellValueType == typeof(double) ||
e.Style.CellValueType == typeof(int) ||
e.Style.CellValueType == typeof(float) ||
e.Style.CellValueType == typeof(decimal))
{
e.Style.HorizontalAlignment = HorizontalAlignment.Right;
}
}
protected override void OnQueryCellInfo(GridQueryCellInfoEventArgs e)
{
base.OnQueryCellInfo(e);
if (e.Style.RowIndex == 0 && e.Style.ColumnIndex == 0)
{
e.Style.Background = new SolidColorBrush(Colors.White);
return;
}
else if (e.Style.RowIndex == 0)
{
e.Style.CellValue = GridRangeInfo.GetAlphaLabel(e.Cell.ColumnIndex);
e.Style.Background = new SolidColorBrush(Colors.White);
e.Style.Foreground = Brushes.Black;
e.Style.HorizontalAlignment = HorizontalAlignment.Center;
e.Style.VerticalAlignment = VerticalAlignment.Center;
}
else if (e.Style.ColumnIndex == 0)
{
e.Style.CellValue = e.Style.RowIndex;
e.Style.Background = new SolidColorBrush(Colors.White);
e.Style.Foreground = Brushes.Black;
e.Style.HorizontalAlignment = HorizontalAlignment.Center;
}
}
private void SetUpCalcSheet()
{
//
int row = 1;
int col = 3;
string colWithdrawal = GridRangeInfo.GetAlphaLabel(col);
string colBalance = GridRangeInfo.GetAlphaLabel(col + 1);
string colInvRate = GridRangeInfo.GetAlphaLabel(col + 2);
string colYear = GridRangeInfo.GetAlphaLabel(col + 3);
string colAge1 = GridRangeInfo.GetAlphaLabel(col + 4);
string colAge2 = GridRangeInfo.GetAlphaLabel(col + 5);
string colSS1 = GridRangeInfo.GetAlphaLabel(col + 6);
string colSS2 = GridRangeInfo.GetAlphaLabel(col + 7);
string colBroke = GridRangeInfo.GetAlphaLabel(col + 8);
string colRand = GridRangeInfo.GetAlphaLabel(col + 9);
//row 1
this.gridCalculations[row, col].Text = "Withdrawal Jan 1";
this.gridCalculations[row, col + 1].Text = "Balance Jan 2";
this.gridCalculations[row, col + 2].Text = "Inv Return Rate";
this.gridCalculations[row, col + 3].Text = "Year";
this.gridCalculations[row, col + 4].Text = "Age spouse 1";
this.gridCalculations[row, col + 5].Text = "Age spouse 2";
this.gridCalculations[row, col + 6].Text = "SS spouse 1";
this.gridCalculations[row, col + 7].Text = "SS spouse 2";
this.gridCalculations[row, col + 8].Text = "Broke Year";
this.gridCalculations[row, col + 9].Text = "Random #";
//row 2
row += 1;
this.gridCalculations[row, col].Text = "0.00"; //assumes spouse1 is not retired at the start
this.gridCalculations[row, col + 1].Text = "= InitialSavingsBalance";
this.gridCalculations[row, col + 2].Text = string.Format("= (100+ReturnPercentage1+(1-2*{2}{1})*ReturnVolatility1)/100 * ({0}{1} < Spouse1Retires ) + (100+ReturnPercentage2+(1-2*{2}{1})*ReturnVolatility2)/100 * ({0}{1} >= Spouse1Retires ) ", colAge1, row, colRand);
this.gridCalculations[row, col + 3].Text = "= InitialYear";
this.gridCalculations[row, col + 4].Text = "= InitialAgeSpouse1";
this.gridCalculations[row, col + 5].Text = "= InitialAgeSpouse2";
this.gridCalculations[row, col + 6].Text = "0.00";
this.gridCalculations[row, col + 7].Text = "0.00";
this.gridCalculations[row, col + 8].Text = "0.00";
this.gridCalculations[row, col + 9].Text = "= TriggerCell + (1-RandomBehavior)/2"; //always zero but used to trigger new random run
this.SetUpNamedRanges();
for (int i = row + 1; i < this.gridCalculations.RowCount; ++i)
{
if (engine.NamedRanges.ContainsKey("EXPENSECOLA"))
{
this.gridCalculations[i, col].Text = string.Format("= (100+(1-2*{4}{1})+ExpenseCOLA)/100 * {0}{1} + ({2}{3}=Spouse1Retires) * InitialAnnualDraw", colWithdrawal, i - 1, colAge1, i, colRand);
}
if (engine.NamedRanges.ContainsKey("SPOUSE1DIES"))
{
this.gridCalculations[i, col + 1].Text = string.Format("= {0}{1} * {2}{1} - {3}{4} + ({5}{1} < Spouse1Dies) * {6}{1} + {7}{1}", colBalance, i - 1, colInvRate, colWithdrawal, i, colAge1, colSS1, colSS2);
}
if (engine.NamedRanges.ContainsKey("RETURNPERCENTAGE1"))
{
this.gridCalculations[i, col + 2].Text = string.Format("= (100+ReturnPercentage1+(1-2*{2}{1})*ReturnVolatility1)/100 * ({0}{1} < Spouse1Retires ) + (100+ReturnPercentage2+(1-2*{2}{1})*ReturnVolatility2)/100 * ({0}{1} >= Spouse1Retires ) ", colAge1, i, colRand);
}
this.gridCalculations[i, col + 3].Text = string.Format("= {0}{1} + 1", colYear, i - 1);
this.gridCalculations[i, col + 4].Text = string.Format("= {0}{1} + 1", colAge1, i - 1);
this.gridCalculations[i, col + 5].Text = string.Format("= {0}{1} + 1", colAge2, i - 1);
if (engine.NamedRanges.ContainsKey("INCOMECOLA"))
{
this.gridCalculations[i, col + 6].Text = string.Format("= (100+IncomeCOLA+(1-2*{3}{1}))/100 * {0}{1} + ({2}{1}=SSAgeSpouse1) * 12 * SSInitialAmountSpouse1", colSS1, i - 1, colAge1, colRand);
}
this.gridCalculations[i, col + 7].Text = string.Format("= (100+IncomeCOLA+(1-2*{5}{1}))/100 * {0}{4} + ({2}{1}=SSAgeSpouse2) * SSInitialPercentageSpouse2/100 * {3}{1}", colSS2, i, colAge2, colSS1, i - 1, colRand);
this.gridCalculations[i, col + 8].Text = string.Format("= ({0}{1}<0) * ({0}{2}>=0) * {3}{1}", colBalance, i, i - 1, colYear);
if (engine.NamedRanges.ContainsKey("RANDOMBEHAVIOR"))
{
this.gridCalculations[i, col + 9].Text = string.Format("= RandomBehavior * Rand() + ${0}${1}", colRand, 2); //make setting row 2 trigger new Rand's
}
//this.gridCalculations[i, 1].Text = string.Format("= d{0}-d{1}", i, i - 1);
}
//make all cells potential formula cells
this.gridCalculations.TableStyle.CellType = "FormulaCell";
this.gridCalculations.TableStyle.Format = "#,##0.00";
//years & age
this.gridCalculations.ColStyles[col + 3].Format = "F0";
this.gridCalculations.ColStyles[col + 4].Format = "F0";
this.gridCalculations.ColStyles[col + 5].Format = "F0";
//some answers
this.gridCalculations[2, 1].Text = string.Format("= Max({0}2:{0}{1})", colBroke, this.gridCalculations.RowCount - 1);
this.gridCalculations[3, 1].Text = string.Format("= SumIf({2}2:{2}{1}, \">0\", {0}2:{0}{1})", colAge1, this.gridCalculations.RowCount - 1, colBroke);
this.gridCalculations[4, 1].Text = "= Max(0, A3 - InitialAgeSpouse1 + InitialAgeSpouse2)";
}
