void AddDefinedNamesForAnnuity()
{
string sheetName = GetSheetNameWrappedInQuotes();
DefinedNameCollection definedNames = workbook.DefinedNames;
definedNames.Clear();
definedNames.Add("Loan_Amount", sheetName + "!$E$4");
definedNames.Add("Interest_Rate", sheetName + "!$E$5");
definedNames.Add("Loan_Years", sheetName + "!$E$6");
definedNames.Add("Number_of_Payments_Per_Year", sheetName + "!$E$7");
definedNames.Add("Loan_Start", sheetName + "!$E$8");
definedNames.Add("Extra_Payments", sheetName + "!$E$9");
definedNames.Add("Scheduled_payment", sheetName + "!$I$4");
definedNames.Add("Scheduled_Number_Payments", sheetName + "!$I$5");
definedNames.Add("Interest_Rate_Per_Month", "=Interest_Rate/Number_of_Payments_Per_Year");
definedNames.Add("Actual_Number_Payments", "=NPER(Interest_Rate_Per_Month, " + sheetName + "!$I$4+Extra_Payments, -Loan_Amount)");
}
void AddDefinedNamesForAnnuityPayments()
{
string sheetName = "'" + Sheet.Name + "'";
char separator = workbook.Options.Culture.TextInfo.ListSeparator[0];
DefinedNameCollection definedNames = workbook.DefinedNames;
definedNames.Add("Loan_Amount", sheetName + "!$E$4");
definedNames.Add("Interest_Rate", sheetName + "!$E$5");
definedNames.Add("Loan_Years", sheetName + "!$E$6");
definedNames.Add("Number_of_Payments_Per_Year", sheetName + "!$E$7");
definedNames.Add("Loan_Start", sheetName + "!$E$8");
definedNames.Add("Extra_Payments", sheetName + "!$E$9");
definedNames.Add("Scheduled_payment", sheetName + "!$I$4");
definedNames.Add("Scheduled_Number_Payments", sheetName + "!$I$5");
definedNames.Add("Interest_Rate_Per_Month", "=Interest_Rate/Number_of_Payments_Per_Year");
definedNames.Add("Actual_Number_Payments", "=NPER(Interest_Rate_Per_Month" + separator + " " + sheetName + "!$I$4+Extra_Payments" + separator + " -Loan_Amount)");
}
void AddDefinedNamesForScaled()
{
string sheetName = GetSheetNameWrappedInQuotes();
DefinedNameCollection definedNames = workbook.DefinedNames;
definedNames.Clear();
definedNames.Add("Values_Entered", "IF(Loan_Amount*Interest_Rate*Loan_Years*Loan_Start>0,1,0)");
definedNames.Add("Full_Print", sheetName + "!$A:$K");
definedNames.Add("Loan_Amount", sheetName + "!$E$4");
definedNames.Add("Interest_Rate", sheetName + "!$E$5");
definedNames.Add("Loan_Years", sheetName + "!$E$6");
definedNames.Add("Number_of_Payments_Per_Year", sheetName + "!$E$7");
definedNames.Add("Loan_Start", sheetName + "!$E$8");
definedNames.Add("Extra_Payments", sheetName + "!$E$9");
definedNames.Add("Scheduled_Monthly_Payment", "Loan_Amount/(Loan_Years*Number_of_Payments_Per_Year)");
definedNames.Add("Scheduled_Number_Payments", sheetName + "!$I$5");
definedNames.Add("Real_Number_Payments", sheetName + "!$I$6");
definedNames.Add("Total_Early_Payments", sheetName + "!$I$7");
definedNames.Add("Total_Interest", sheetName + "!$I$8");
definedNames.Add("Beg_Bal", sheetName + "!$D$12:$D$" + ScheduledLastRow);
definedNames.Add("Cum_Int", sheetName + "!$K$12:$K$" + ScheduledLastRow);
definedNames.Add("Data", sheetName + "!$B$12:$K$" + ScheduledLastRow);
definedNames.Add("End_Bal", sheetName + "!$J$12:$J$" + ScheduledLastRow);
definedNames.Add("Extra_Pay", sheetName + "!$F$12:$F$" + ScheduledLastRow);
definedNames.Add("Header_Row", "ROW(" + sheetName + "!$17:$17)");
definedNames.Add("Int", sheetName + "!$I$12:$I$" + ScheduledLastRow);
definedNames.Add("Last_Row", "IF(Values_Entered,Header_Row+Number_of_Payments,Header_Row)");
definedNames.Add("Number_of_Payments", "=MATCH(0.01,End_Bal,-1)+1");
definedNames.Add("Pay_Date", sheetName + "!$C$12:$C$" + ScheduledLastRow);
definedNames.Add("Pay_Num", sheetName + "!$B$12:$B$" + ScheduledLastRow);
definedNames.Add("Payment_Date", "DATE(YEAR(Loan_Start),MONTH(Loan_Start)+Payment_Number,DAY(Loan_Start))");
definedNames.Add("Princ", sheetName + "!$H$12:$H$" + ScheduledLastRow);
definedNames.Add("Print_Area_Reset", "OFFSET(Full_Print,0,0,Last_Row)");
definedNames.Add("Sched_Pay", sheetName + "!$E$12:$E$" + ScheduledLastRow);
definedNames.Add("Total_Pay", sheetName + "!$G$12:$G$" + ScheduledLastRow);
definedNames.Add("Total_Payment", "Scheduled_Payment+Extra_Payment");
definedNames.Add("Payment_Number", "ROW()-Header_Row");
definedNames.Add("Loan_Not_Paid", "IF(Payment_Number<=Number_of_Payments,1,0)");
}
public Workbook()
{
Sheets = new SheetCollection();
DefaultFont = new CellFont();
DefinedNames = new DefinedNameCollection();
}
public Sheet(String sheetName)
{
SheetName = sheetName;
data = new SheetData();
Columns = new SheetColumnCollection();
RowBreaks = new List<int>();
ColumnBreaks = new List<int>();
Page = new PageSetup();
ShowGridLines = true;
//ConditionalFormatting = new List<ConditionalFormatting>();
ConditionalFormatting = new List<ConditionalFormatting>();
DefinedNames = new DefinedNameCollection();
}
protected AreaFormulaValidatorBase(DefinedNameCollection definedNames)
{
this.definedNames = definedNames;
}
public Triangle3AreaFormulaValidator(DefinedNameCollection definedNames)
: base(definedNames)
{
}
public CustomDrawModule()
{
InitializeComponent();
spreadsheetControl1.BeginUpdate();
book = spreadsheetControl1.Document;
generator = new CustomDrawDocumentGenerator(book);
book = generator.Generate(DemoUtils.GetRelativePath("CalculatorOfArea_template.xlsx"));
definedNames = book.DefinedNames;
FillAreaFormulaValidators();
spreadsheetControl1.CustomDrawCell += DrawFormulaTip;
spreadsheetControl1.CustomDrawCellBackground +=spreadsheetControl1_CustomDrawCellBackground;
spreadsheetControl1.EndUpdate();
spreadsheetControl1.Document.History.Clear();
}
