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; }