void GenerateTableHeader()
{
Range formattedRange = worksheet["B11:K11"];
SpreadsheetFormatting formatting = formattedRange.BeginUpdateFormatting();
try {
formatting.Alignment.Horizontal = DevExpress.Spreadsheet.SpreadsheetHorizontalAlignment.Center;
formatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
formatting.Alignment.WrapText = true;
formatting.Fill.BackgroundColor = headerColor;
formatting.Font.Color = Color.White;
formatting.Font.Size = 10;
}
finally {
formattedRange.EndUpdateFormatting(formatting);
}
cells["B11"].Value = "No.";
cells["C11"].Value = "Payment Date";
cells["D11"].Value = "Beginning Balance";
cells["E11"].Value = "Scheduled Payment";
cells["F11"].Value = "Extra Payment";
cells["G11"].Value = "Total Payment";
cells["H11"].Value = "Principal";
cells["I11"].Value = "Interest";
cells["J11"].Value = "Ending Balance";
cells["K11"].Value = "Cumulative Interest";
}
void GenerateTitle()
{
cells["B2"].Value = "Loan Amortization Schedule";
worksheet["B2:K2"].Merge();
Range formattedRange = worksheet["B2:K2"];
SpreadsheetFormatting formatting = formattedRange.BeginUpdateFormatting();
try {
formatting.Font.Size = 26;
formatting.Font.Color = titleColor;
formatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Left;
formatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
}
finally {
formattedRange.EndUpdateFormatting(formatting);
}
}
void GenerateFieldsForDataEntry()
{
worksheet["B4:D4"].Merge();
worksheet["B5:D5"].Merge();
worksheet["B6:D6"].Merge();
worksheet["B7:D7"].Merge();
worksheet["B8:D8"].Merge();
worksheet["B9:D9"].Merge();
Range formattedRange = worksheet["B4:D9"];
SpreadsheetFormatting formatting = formattedRange.BeginUpdateFormatting();
try {
formatting.Alignment.Indent = 1;
formatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Right;
formatting.Font.Size = 10;
}
finally {
formattedRange.EndUpdateFormatting(formatting);
}
formattedRange = worksheet["E4:E9"];
formatting = formattedRange.BeginUpdateFormatting();
try {
formatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Left;
formatting.Font.Size = 10;
}
finally {
formattedRange.EndUpdateFormatting(formatting);
}
cells["B4"].Value = "Loan amount:";
cells["B5"].Value = "Annual interest rate:";
cells["B6"].Value = "Loan period in years:";
cells["B7"].Value = "Number of payments per year:";
cells["B8"].Value = "Start date of loan:";
cells["B9"].Value = "Optional extra payments:";
cells["E4"].NumberFormat = SummaryAccounting;
cells["E5"].NumberFormat = "0.00%";
cells["E8"].NumberFormat = DateFormat;
cells["E9"].NumberFormat = SummaryAccounting;
}
void GenerateFieldsForCalculationResults()
{
worksheet["F4:H4"].Merge();
worksheet["F5:H5"].Merge();
worksheet["F6:H6"].Merge();
worksheet["F7:H7"].Merge();
worksheet["F8:H8"].Merge();
Range formattedRange = worksheet["F4:H8"];
SpreadsheetFormatting formatting = formattedRange.BeginUpdateFormatting();
try {
formatting.Alignment.Indent = 1;
formatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Right;
formatting.Font.Size = 10;
}
finally {
formattedRange.EndUpdateFormatting(formatting);
}
formattedRange = worksheet["I4:I8"];
formatting = formattedRange.BeginUpdateFormatting();
try {
formatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Left;
formatting.Font.Size = 10;
}
finally {
formattedRange.EndUpdateFormatting(formatting);
}
cells["F4"].Value = "First scheduled payment:";
cells["F5"].Value = "Scheduled number of payments:";
cells["F6"].Value = "Actual number of payments:";
cells["F7"].Value = "Total early payments:";
cells["F8"].Value = "Total interest:";
cells["I4"].NumberFormat = SummaryAccounting;
cells["I7"].NumberFormat = SummaryAccounting;
cells["I8"].NumberFormat = SummaryAccounting;
}
void GenerateTableGrid()
{
workbook.Calculate();
Range formattedRange;
for (int i = 1; i < ActualNumberOfPayments; i += 2)
{
formattedRange = worksheet.Range.FromLTRB(1, 11 + i, 10, 11 + i);
formattedRange.Fill.BackgroundColor = Color.FromArgb(217, 217, 217);
}
formattedRange = worksheet["B11:K" + ActualLastRow];
SpreadsheetFormatting formatting = formattedRange.BeginUpdateFormatting();
try {
formatting.Borders.InsideVerticalBorders.LineStyle = BorderLineStyle.Thin;
formatting.Borders.InsideVerticalBorders.Color = Color.White;
formatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
}
finally {
formattedRange.EndUpdateFormatting(formatting);
}
formattedRange = worksheet["B12:C" + ActualLastRow];
formattedRange.RowHeight = 15;
formatting = formattedRange.BeginUpdateFormatting();
try {
formatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Right;
}
finally {
formattedRange.EndUpdateFormatting(formatting);
}
formattedRange = worksheet["C11:C" + ActualLastRow];
formattedRange.NumberFormat = DateFormat;
formattedRange = worksheet["D11:K" + ActualLastRow];
formattedRange.NumberFormat = Accounting;
}