private void SetUpNamedRanges()
{
//just so we can list the ranges (in order) at the end of the method
ArrayList tempList = new ArrayList();
string col = "Display!B";
int row = 6;
engine.AddNamedRange("InitialYear", string.Format("{0}{1}", col, row));
tempList.Add("InitialYear");
row += 1;
engine.AddNamedRange("InitialAgeSpouse1", string.Format("{0}{1}", col, row));
tempList.Add("InitialAgeSpouse1");
row += 1;
engine.AddNamedRange("InitialAgeSpouse2", string.Format("{0}{1}", col, row));
tempList.Add("InitialAgeSpouse2");
row += 1;
engine.AddNamedRange("InitialSavingsBalance", string.Format("{0}{1}", col, row));
tempList.Add("InitialSavingsBalance");
row += 1;
engine.AddNamedRange("ReturnPercentage1", string.Format("{0}{1}", col, row));
tempList.Add("ReturnPercentage1");
row += 1;
engine.AddNamedRange("ReturnVolatility1", string.Format("{0}{1}", col, row));
tempList.Add("ReturnVolatility1");
row += 1;
engine.AddNamedRange("ReturnPercentage2", string.Format("{0}{1}", col, row));
tempList.Add("ReturnPercentage2");
row += 1;
engine.AddNamedRange("ReturnVolatility2", string.Format("{0}{1}", col, row));
tempList.Add("ReturnVolatility2");
row += 1;
engine.AddNamedRange("IncomeCOLA", string.Format("{0}{1}", col, row));
tempList.Add("IncomeCOLA");
row += 1;
engine.AddNamedRange("ExpenseCOLA", string.Format("{0}{1}", col, row));
tempList.Add("ExpenseCOLA");
row += 1;
engine.AddNamedRange("SSAgeSpouse1", string.Format("{0}{1}", col, row));
tempList.Add("SSAgeSpouse1");
row += 1;
engine.AddNamedRange("SSAgeSpouse2", string.Format("{0}{1}", col, row));
tempList.Add("SSAgeSpouse2");
row += 1;
engine.AddNamedRange("SSInitialAmountSpouse1", string.Format("{0}{1}", col, row));
tempList.Add("SSInitialAmountSpouse1");
row += 1;
engine.AddNamedRange("SSInitialPercentageSpouse2", string.Format("{0}{1}", col, row));
tempList.Add("SSInitialPercentageSpouse2");
row += 1;
engine.AddNamedRange("Spouse1Dies", string.Format("{0}{1}", col, row));
tempList.Add("Spouse1Dies");
row += 1;
engine.AddNamedRange("Spouse1Retires", string.Format("{0}{1}", col, row));
tempList.Add("Spouse1Retires");
row += 1;
engine.AddNamedRange("InitialAnnualDraw", string.Format("{0}{1}", col, row));
tempList.Add("InitialAnnualDraw");
row += 1;
engine.AddNamedRange("RandomBehavior", string.Format("{0}{1}", col, row));
tempList.Add("RandomBehavior");
//the trigger cell used in simulation run
engine.AddNamedRange("TriggerCell", string.Format("Display!C11", col, row));
//just display named values
//row = 2;
//foreach(string key in tempList)
//{
// this.gridCalculations[row, 1].Text = key;
// this.gridCalculations[row, 2].Text = string.Format("= {0}", engine.NamedRanges[key.ToUpper()]);
// row++;
//}
}
/// <summary>
/// Displays a collection editor dialog for editing NamedRanges.
/// </summary>
/// <param name="engine">The GridFormulaEngine instance whose NamedRanges are being edited.</param>
public static void ShowNamedRangesDialog(GridFormulaEngine engine)
{
GridFormulaNamedRangesEditerHelper editHelper = new GridFormulaNamedRangesEditerHelper(engine);
CollectionEditor editor1 = new CollectionEditor(typeof(NamedRangeList));
WindowsFormsEditorServiceContainer esc = new WindowsFormsEditorServiceContainer(null);
////Subscribe to the event to change Dialog settings.
esc.ShowingDialog += new ControlEventHandler(ServiceContainerShowingDialog);
PropertyDescriptor pd = TypeDescriptor.GetProperties(editHelper)["List"];
TypeDescriptorContext tdc = new TypeDescriptorContext(editHelper, pd);
tdc.ServiceProvider = esc;
GridFormulaNamedRangesEditerHelper.NamedRangeList oldList = pd.GetValue(editHelper) as GridFormulaNamedRangesEditerHelper.NamedRangeList;
GridFormulaNamedRangesEditerHelper.NamedRangeList newList = editor1.EditValue(tdc, esc, oldList) as GridFormulaNamedRangesEditerHelper.NamedRangeList;
esc.ShowingDialog -= new ControlEventHandler(ServiceContainerShowingDialog);
if (newList != null && esc.DialogResult == DialogResult.OK)
{
ArrayList needUpdating = new ArrayList();
ArrayList needDeleting = new ArrayList();
Hashtable namedRanges = (Hashtable)engine.NamedRanges.Clone();
List <string> lst = new List <string>();
foreach (string key in namedRanges.Keys)
{
lst.Add(key);
}
lst.Sort();
engine.NamedRanges.Clear();
engine.NamedRangesOriginalNames.Clear();
int i = 0;
foreach (NamedRange range in newList)
{
string s = range.Key.ToUpper(CultureInfo.InvariantCulture);
if (namedRanges.Contains(range.Key) && !string.IsNullOrEmpty(range.Key) && namedRanges[lst[i]].Equals(range.Value))
{
engine.AddNamedRange(range.Key, range.Value);
}
else if (namedRanges.Contains(range.Key) && !string.IsNullOrEmpty(range.Key))
{
engine.AddNamedRange(range.Key, namedRanges[lst[i]].ToString());
}
else if (!namedRanges.Contains(range.Key) && !string.IsNullOrEmpty(range.Key) && !namedRanges.ContainsKey(range.Key))
{
engine.AddNamedRange(range.Key, range.Value);
}
if (namedRanges.ContainsKey(s))
{
if (namedRanges[s].Equals(range.Value))
{
needUpdating.Add(s);
}
}
i++;
}
foreach (string key in namedRanges.Keys)
{
if (!engine.NamedRanges.ContainsKey(key))
{
needDeleting.Add(key);
}
}
if (needDeleting.Count > 0 || needUpdating.Count > 0)
{
foreach (string s in needUpdating)
{
engine.UpdateDependentNamedRangeCell(s);
////Console.WriteLine("changed: " + s);
}
foreach (string s in needDeleting)
{
if (engine.NamedRanges.ContainsKey(s))
{
engine.UpdateDependentNamedRangeCell(s);
engine.RemoveNamedRangeDependency(s);
////Console.WriteLine("deleted: " + s);
}
}
}
engine.AdjustNameRangesForSize();
}
}