﻿Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Data
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports System.Windows.Forms
Imports Spread.Common.Features
Imports Spread.Common.DataStore
Imports System.IO
Imports GrapeCity.AnnualFinancialReport.Properties
Imports FarPoint.Win.Spread
Imports FarPoint.Win
Imports FarPoint.Win.Spread.CellType
Imports FarPoint.Win.Spread.Model
Imports FarPoint.Win.Spread.Chart
Imports Spread.Common.Features.Spread.Common.Features.Sample


Partial Public Class AnnualFinancialReport
    Inherits BaseSampleControl
    Public Sub New()
        InitializeComponent()
        SetupComponents()
        InitFinancialDataInput(_svFinancialDataInput)
        InitKeyMetricsSettings(_svKeyMetricSettings)
        InitCalculations(_svCalculations)
        InitFinancialReport(_svFinancialReport)
    End Sub
    Private Sub SetupComponents()
        'Load FpSpread mappings
        Using stream As New MemoryStream(My.Resources.ExcelCompatibility)
            fpSpread.LoadXmlInputMapFile(stream)
        End Using

        'Attach namebox and formulabox to spread
        Me.nameBox.Attach(Me.fpSpread)
        Me.formulaTextBox1.Attach(Me.fpSpread)

        'Perform actions on MenuItem clicks
        AddHandler _miConditionalFormats.Click, Sub(s, e)
                                                    ConditionalFormattingUtils.ShowConditionalFormattingManagerForm(Me.ParentForm, fpSpread)

                                                End Sub
        AddHandler _miNameManager.Click, Sub(s, e)
                                             fpSpread.ShowListCustomNameForm(Me.ParentForm)

                                         End Sub
    End Sub
    Private Sub InitFinancialDataInput(dataInput As SheetView)
        dataInput.HorizontalGridLine = New GridLine(GridLineType.None)
        dataInput.VerticalGridLine = New GridLine(GridLineType.None)
        dataInput.SetColumnWidth(0, 15)
        dataInput.SetColumnWidth(1, 150)
        dataInput.SetMultipleColumnWidths(2, 7, 121)
        dataInput.SetColumnWidth(9, 15)
        dataInput.SetRowHeight(0, 11)
        dataInput.SetRowHeight(1, 51)
        dataInput.SetRowHeight(2, 24)
        dataInput.SetMultipleRowHeights(3, 2, 34)
        dataInput.SetMultipleRowHeights(5, 25, 26)
        dataInput.Cells(1, 1).Value = "INPUT YOUR FINANCIAL DATA"
        dataInput.Cells(1, 1).ForeColor = Color.FromArgb(255, 38, 94, 53)
        dataInput.Cells(1, 1).Font = New Font("Trebuchet MS", 24)
        dataInput.Cells(2, 1).Value = "YOU CAN DEFINE UP TO 25 KEY METRICS FOR 7 YEARS"
        dataInput.Cells(2, 1).ForeColor = Color.FromArgb(255, 89, 89, 89)
        dataInput.Cells(2, 1).Font = New Font("Ariel", 14)
        dataInput.SetArray(4, 1, New Object(,) {{"METRIC NAME", 2010, 2011, 2012, 2013, 2014,
            2015, 2016}, {"REVENUES", 125000, 134137.45, 142728.38, 150687.46, 165044.56,
            180026.63, 180583.88}, {"OPERATING EXPENSES", 65000, 70962.31, 75924.86, 78901.27, 81674.37,
            80883.33, 94419.45}, {"OPERATING PROFIT", 60000.0, 64207.3, 68857.69, 75643.25, 76755.26,
            77317.83, 73425.99}, {"DEPRECIATION", 4500.0, 4517.77, 4656.92, 4974.21, 5024.11,
            5068.42, 5546.88}, {"INTEREST", 2500.0, 2745.82, 2893.11, 3136.12, 3148.53,
            3338.3, 3789.47},
            {"NET PROFIT", 54000.0, 54761.08, 55860.81, 59747.95, 61483.59,
            66272.1, 67474.85}, {"TAX", 22000.0, 23920.54, 25576.74, 27498.86, 28335.67,
            29424.53, 31408.25}, {"PROFIT AFTER TAX", 32000.0, 34943.49, 38418.53, 39895.05, 40607.73,
            42438.2, 50247.68}, {"METRIC 1", 12.8, 12.81, 13.78, 14.29, 15.57,
            16.78, 19.96}, {"METRIC 2", 18.2, 18.59, 19.22, 20.17, 20.48,
            21.84, 26.01}, {"METRIC 3", 19.1, 20.55, 21.87, 23.19, 24.67,
            26.39, 31.08},
            {"METRIC 4", 12.1, 12.21, 12.59, 13.7, 13.76,
            14.59, 14.92}, {"METRIC 5", 0.75, 0.79, 0.85, 0.89, 0.91,
            1.0, 1.03}, {"METRIC 6", 0.23, 0.25, 0.27, 0.28, 0.29,
            0.3, 0.34}})
        dataInput.Cells(4, 1, 4, 8).ForeColor = Color.White
        dataInput.Cells(4, 1, 4, 8).BackColor = Color.FromArgb(255, 38, 94, 53)
        dataInput.Cells(4, 1, 4, 8).Font = New Font("Ariel", 11)
        dataInput.FrozenRowCount = 5
        Dim sideThin As New ComplexBorderSide(ComplexBorderSideStyle.ThinLine, System.Drawing.Color.Black)
        dataInput.Rows(4).Border = New ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, sideThin)
        Dim formatTable As New FormulaConditionalFormattingRule("MOD(ROW(),2)=0")
        formatTable.BackColor = Color.FromArgb(255, 242, 242, 242)
        Dim sideDarkGray As New ComplexBorderSide(System.Drawing.Color.FromArgb(255, 217, 217, 217))
        formatTable.Border = New ComplexBorder(ComplexBorderSide.Empty, sideDarkGray, ComplexBorderSide.Empty, sideDarkGray)
        dataInput.SetConditionalFormatting(New CellRange() {New CellRange(5, 1, 25, 8)}, New IConditionalFormattingRule() {formatTable})
        Dim currencyCell As New CurrencyCellType()
        currencyCell.ShowSeparator = True
        dataInput.Cells(5, 2, 29, 8).CellType = currencyCell
        dataInput.DefaultStyle.Locked = True
        dataInput.Cells(5, 1, 29, 8).Locked = False
        dataInput.Protect = True
    End Sub

    Private Sub InitKeyMetricsSettings(keyMetricSettings As SheetView)
        keyMetricSettings.HorizontalGridLine = New GridLine(GridLineType.None)
        keyMetricSettings.VerticalGridLine = New GridLine(GridLineType.None)
        keyMetricSettings.SetColumnWidth(-1, 64)
        keyMetricSettings.SetColumnWidth(0, 15)
        keyMetricSettings.SetColumnWidth(1, 30)
        keyMetricSettings.SetColumnWidth(2, 168)
        keyMetricSettings.SetColumnWidth(3, 28)
        keyMetricSettings.SetMultipleColumnWidths(4, 2, 127)
        keyMetricSettings.SetRowHeight(-1, 26)
        keyMetricSettings.SetRowHeight(0, 11)
        keyMetricSettings.SetRowHeight(1, 51)
        keyMetricSettings.SetRowHeight(2, 34)
        keyMetricSettings.SetRowHeight(3, 31)
        keyMetricSettings.Cells(1, 1).Value = "DEFINE KEY METRICS HERE"
        keyMetricSettings.Cells(1, 1).ForeColor = Color.FromArgb(255, 38, 94, 53)
        keyMetricSettings.Cells(1, 1).Font = New Font("Trebuchet MS", 24)
        keyMetricSettings.Cells(2, 1).Value = "SELECT UP TO 5 KEY METRICS TO SHOW AT THE TOP OF THE REPORT"
        keyMetricSettings.Cells(2, 1).ForeColor = Color.FromArgb(255, 89, 89, 89)
        keyMetricSettings.Cells(2, 1).Font = New Font("Ariel", 14)
        keyMetricSettings.SetArray(4, 1, New Object(,) {{1, "REVENUES"}, {2, "NET PROFIT"}, {3, "INTEREST"}, {4, "DEPRECIATION"}, {5, "OPERATING PROFIT"}})
        keyMetricSettings.Cells(4, 1, 8, 1).Font = New Font("Trebuchet MS", 11)
        keyMetricSettings.Cells(4, 2, 8, 2).Font = New Font("Trebuchet MS", 10)
        keyMetricSettings.AddCustomName("lstMetrics", "OFFSET('Financial Data Input'!$B$6:$B$30,0,0,COUNTA('Financial Data Input'!$B$6:$B$30),1)", 0, 0)
        Dim lstMetrics As GrapeCity.Spreadsheet.DataValidation = keyMetricSettings.Validations.Add(New GrapeCity.CalcEngine.RangeReference(4, 2, 8, 2), GrapeCity.Spreadsheet.DataValidationType.List, GrapeCity.Spreadsheet.DataValidationErrorStyle.[Stop], GrapeCity.Spreadsheet.DataValidationOperator.Equal, "=lstMetrics")
        lstMetrics.ErrorMessage = "A metric from your financial data needs to be entered for Key Metrics to work correctly. Click Cancel and enter a different metric or add the metric to the Financial Data Input sheet. "
        lstMetrics.ErrorTitle = "Whoops!"
        Dim formatTableL As New FormulaConditionalFormattingRule("MOD(ROW(),2)=0")
        formatTableL.BackColor = Color.FromArgb(255, 242, 242, 242)
        formatTableL.Border = New ComplexBorder(New ComplexBorderSide(ComplexBorderSideStyle.ThickLine, System.Drawing.Color.FromArgb(255, 166, 166, 166)), New ComplexBorderSide(System.Drawing.Color.FromArgb(255, 217, 217, 217)), ComplexBorderSide.Empty, New ComplexBorderSide(System.Drawing.Color.FromArgb(255, 217, 217, 217)))
        keyMetricSettings.SetConditionalFormatting(New CellRange() {New CellRange(4, 1, 5, 1)}, New IConditionalFormattingRule() {formatTableL})
        Dim formatTableR As New FormulaConditionalFormattingRule("MOD(ROW(),2)=0")
        formatTableR.BackColor = Color.FromArgb(255, 242, 242, 242)
        formatTableR.Border = New ComplexBorder(ComplexBorderSide.Empty, New ComplexBorderSide(System.Drawing.Color.FromArgb(255, 217, 217, 217)), New ComplexBorderSide(ComplexBorderSideStyle.ThickLine, System.Drawing.Color.FromArgb(255, 166, 166, 166)), New ComplexBorderSide(System.Drawing.Color.FromArgb(255, 217, 217, 217)))
        keyMetricSettings.SetConditionalFormatting(New CellRange() {New CellRange(4, 2, 5, 1)}, New IConditionalFormattingRule() {formatTableR})
        keyMetricSettings.SetOutlineBorder(New CellRange(4, 1, 5, 2), New ComplexBorder(New ComplexBorderSide(ComplexBorderSideStyle.ThickLine, System.Drawing.Color.FromArgb(255, 166, 166, 166))))
        keyMetricSettings.Cells(4, 4, 8, 4).Font = New Font("Ariel", 11)
        keyMetricSettings.Cells(4, 4, 8, 4).ForeColor = Color.FromArgb(255, 128, 128, 128)
        keyMetricSettings.Cells(4, 4, 8, 4).Formula = "IF(ISBLANK(C5),""← Please select a value from drop-down"",IF(COUNTIF($C$5:C5,C5)>1,""You have selected ""&C5&"" more than once."",""""))"
        keyMetricSettings.DefaultStyle.Locked = True
        keyMetricSettings.Cells(4, 2, 8, 8).Locked = False
        keyMetricSettings.Protect = True
        keyMetricSettings.SetActiveCell(4, 2)
    End Sub
    Private Sub InitCalculations(calculations As SheetView)
        calculations.SetColumnWidth(-1, 64)
        calculations.SetColumnWidth(1, 230)
        calculations.SetRowHeight(0, 46)
        calculations.SetMultipleRowHeights(2, 10, 26)
        calculations.SetRowHeight(12, 21)
        calculations.SetMultipleRowHeights(13, 26, 26)
        calculations.DefaultStyle.Font = New Font("Trebuchet MS", 10)
        calculations.DefaultStyle.ForeColor = Color.FromArgb(255, 89, 89, 89)
        calculations.DefaultStyle.VerticalAlignment = CellVerticalAlignment.Center
        calculations.Cells(0, 0).Value = "This worksheet is used for the Financial Report calculations and should remain hidden."
        calculations.Cells(0, 0).ForeColor = Color.FromArgb(255, 57, 140, 80)
        calculations.Cells(0, 0).Font = New Font("Ariel", 11)
        calculations.Cells(1, 3).Value = "Position"
        calculations.AddCustomName("lstYears", "OFFSET('Financial Data Input'!$B$5:$I$5,0,1,1,COUNTA('Financial Data Input'!$B$5:$I$5)-1)", 0, 0)
        calculations.AddCustomName("SelectedYear", "'Financial Report'!$K$2", 0, 0)
        calculations.Cells(2, 1).Value = "This year"
        calculations.Cells(2, 2).Formula = "SelectedYear"
        calculations.Cells(2, 3).Formula = "MATCH(C3,lstYears,0)+1"
        calculations.Cells(3, 1).Value = "Previous Year"
        calculations.Cells(3, 2).Formula = "C3-1"
        calculations.Cells(3, 3).Formula = "MATCH(C4,lstYears,0)+1"
        calculations.Cells(5, 1).Value = "Position"
        calculations.Cells(5, 2, 5, 6).Formula = "MATCH(C7,lstYears,0)+1"
        calculations.Cells(5, 8).Formula = "COUNT(C6:G6)"
        calculations.AddCustomName("Years", "Calculations!$I$6", 0, 0)
        calculations.SetOutlineBorder(New CellRange(5, 1, 1, 6), New ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, New ComplexBorderSide(ComplexBorderSideStyle.ThickLine, System.Drawing.Color.FromArgb(255, 166, 166, 166))))
        calculations.Cells(6, 1).Value = "Key Metrics"
        calculations.Cells(6, 2, 6, 5).Formula = "D7-1"
        calculations.Cells(6, 6).Formula = "C3"
        calculations.Cells(6, 1, 6, 6).Font = New Font("Trebuchet MS", 14)
        calculations.SetOutlineBorder(New CellRange(6, 1, 1, 6), New ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, New ComplexBorderSide(ComplexBorderSideStyle.ThickLine, System.Drawing.Color.FromArgb(255, 166, 166, 166))))
        calculations.Cells(7, 0, 11, 0).Formula = "MATCH(B8,'Financial Data Input'!$B$6:$B$30,0)"
        calculations.Cells(7, 1, 11, 1).Formula = "IF('Key Metric Settings'!C5="""","""",'Key Metric Settings'!C5)"
        calculations.Cells(7, 2, 11, 6).Formula = "IFERROR(INDEX('Financial Data Input'!$B$6:$I$30,$A8,C$6),NA())"
        calculations.Cells(7, 7, 11, 7).Formula = "IFERROR(G8/F8-1,"""")"
        Dim percentCell As New PercentCellType()
        percentCell.DecimalPlaces = 0
        calculations.Cells(7, 7, 11, 7).CellType = percentCell
        calculations.SetOutlineBorder(New CellRange(12, 1, 1, 6), New ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, New ComplexBorderSide(ComplexBorderSideStyle.ThickLine, System.Drawing.Color.FromArgb(255, 166, 166, 166))))
        calculations.Cells(13, 1).Value = "All Metrics (works up to 25 metrics)"
        calculations.Cells(13, 1).Font = New Font("Trebuchet MS", 14)
        calculations.SetOutlineBorder(New CellRange(13, 1, 1, 6), New ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, New ComplexBorderSide(ComplexBorderSideStyle.ThickLine, System.Drawing.Color.FromArgb(255, 166, 166, 166))))
        calculations.Cells(14, 0, 38, 0).Formula = "ROWS($B$15:B15)"
        calculations.Cells(14, 1, 38, 1).Formula = "IF('Financial Data Input'!B6=0,"""",'Financial Data Input'!B6)"
        calculations.Cells(14, 2, 38, 6).Formula = "IF($B15="""",NA(),IFERROR(INDEX('Financial Data Input'!$B$6:$I$30,$A15,C$6),NA()))"
    End Sub
    Private Sub InitFinancialReport(report As SheetView)
        Dim r As Integer, c As Integer
        report.HorizontalGridLine = New GridLine(GridLineType.None)
        report.VerticalGridLine = New GridLine(GridLineType.None)
        report.SetColumnWidth(-1, 64)
        report.SetColumnWidth(0, 14)
        For c = 1 To 8 Step 2
            report.SetColumnWidth(c, 185)
            report.SetColumnWidth(c + 1, 19)
        Next
        For c = 9 To 12 Step 2
            report.SetColumnWidth(c, 86)
            report.SetColumnWidth(c + 1, 13)
        Next
        report.SetColumnWidth(13, 125)
        report.SetRowHeight(-1, 25)
        report.SetRowHeight(0, 11)
        report.SetRowHeight(1, 51)
        report.SetRowHeight(2, 85)
        report.SetRowHeight(3, 9)
        report.SetRowHeight(4, 32)
        report.SetRowHeight(5, 25)
        report.SetRowHeight(6, 30)
        report.SetRowHeight(7, 56)
        report.SetRowHeight(12, 32)
        report.Cells(1, 1).Font = New Font("Trebuchet MS", 24)
        report.Cells(1, 1).ForeColor = Color.FromArgb(255, 38, 94, 53)
        report.Cells(1, 1).Value = "ANNUAL FINANCIAL REPORT"
        Dim sideMediumGray As New ComplexBorderSide(ComplexBorderSideStyle.MediumLine, System.Drawing.Color.FromArgb(255, 166, 166, 166))
        report.Cells(1, 10).Border = New ComplexBorder(ComplexBorderSide.Empty, sideMediumGray, ComplexBorderSide.Empty, sideMediumGray)
        report.Cells(1, 10).ColumnSpan = 2
        report.Cells(1, 10).Font = New Font("Trebuchet MS", 20)
        report.Cells(1, 10).HorizontalAlignment = CellHorizontalAlignment.Center
        report.Cells(1, 10).Value = 2016
        report.Cells(1, 13).BackColor = Color.FromArgb(255, 38, 94, 53)
        Dim sideGreen As New ComplexBorderSide(Color.FromArgb(255, 57, 140, 80))
        report.Cells(1, 13).Border = New ComplexBorder(sideGreen)
        report.Cells(1, 13).Font = New Font("Ariel", 10, FontStyle.Bold)
        report.Cells(1, 13).ForeColor = Color.White
        report.Cells(1, 13).HorizontalAlignment = CellHorizontalAlignment.Center
        report.Cells(1, 13).VerticalAlignment = CellVerticalAlignment.Center
        report.Cells(1, 13).Value = "Select  report year in Cell K2"
        report.Cells(2, 13).BackColor = Color.FromArgb(255, 38, 94, 53)
        report.Cells(2, 13).Border = New ComplexBorder(sideGreen)
        report.Cells(2, 13).Font = New Font("Ariel", 10, FontStyle.Bold)
        report.Cells(2, 13).ForeColor = Color.White
        report.Cells(2, 13).HorizontalAlignment = CellHorizontalAlignment.Center
        report.Cells(2, 13).VerticalAlignment = CellVerticalAlignment.Center
        report.Cells(2, 13).Value = "To edit data, select the Financial Data Input sheet"
        Dim textCell As New TextCellType()
        textCell.WordWrap = True
        report.Cells(1, 13, 2, 13).CellType = textCell
        Dim lstYears As GrapeCity.Spreadsheet.DataValidation = report.Validations.Add(New GrapeCity.CalcEngine.RangeReference(1, 10, 1, 10), Spreadsheet.DataValidationType.List, GrapeCity.Spreadsheet.DataValidationErrorStyle.[Stop], GrapeCity.Spreadsheet.DataValidationOperator.Equal, "=lstYears")
        lstYears.ErrorMessage = "A year included in your financial data needs to be entered for this report to work correctly. Click Cancel and enter a different year or add information for the year on the Financial Data Input sheet."
        lstYears.ErrorTitle = "Whoops!"
        lstYears.ShowError = True
        report.Cells(2, 1).ForeColor = Color.FromArgb(255, 89, 89, 89)
        report.Cells(2, 1).Font = New Font("Ariel", 18)
        report.Cells(2, 1).Value = "YOUR COMPANY NAME"
        report.SetOutlineBorder(New CellRange(3, 1, 1, 11), New ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, sideMediumGray))
        report.Cells(4, 1).ForeColor = Color.FromArgb(255, 89, 89, 89)
        report.Cells(4, 1).Font = New Font("Trebuchet MS", 14)
        report.Cells(4, 1).Value = "KEY METRICS"
        report.SetOutlineBorder(New CellRange(4, 1, 1, 11), New ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, sideMediumGray))
        Dim currencyCell As New CurrencyCellType()
        currencyCell.DecimalPlaces = 0
        currencyCell.ShowSeparator = True
        Dim percentCell As New PercentCellType()
        percentCell.DecimalPlaces = 0
        Dim sideMediumGreen As New ComplexBorderSide(ComplexBorderSideStyle.MediumLine, System.Drawing.Color.FromArgb(255, 38, 94, 53))
        Dim sideDashed As New ComplexBorderSide(ComplexBorderSideStyle.Dashed, System.Drawing.Color.FromArgb(255, 89, 89, 89))
        For c = 1 To 9 Step 2
            report.Cells(6, c).BackColor = Color.FromArgb(255, 38, 94, 53)
            report.Cells(6, c).Border = New ComplexBorder(sideMediumGreen)
            report.Cells(6, c).Font = New Font("Ariel", 9, FontStyle.Bold)
            report.Cells(6, c).ForeColor = Color.White
            report.Cells(6, c).Formula = $"Calculations!B{8 + (c - 1) / 2}"
            report.Cells(6, c).HorizontalAlignment = CellHorizontalAlignment.Center
            report.Cells(7, c).Border = New ComplexBorder(sideMediumGreen, ComplexBorderSide.Empty, sideMediumGreen, sideDashed)
            report.Cells(7, c).CellType = currencyCell
            report.Cells(7, c).Font = New Font("Ariel", 20)
            report.Cells(7, c).ForeColor = Color.FromArgb(255, 89, 89, 89)
            report.Cells(7, c).Formula = $"IFERROR(Calculations!G{8 + (c - 1) / 2},"""")"
            report.Cells(7, c).HorizontalAlignment = CellHorizontalAlignment.Center
            report.Cells(8, c).Border = New ComplexBorder(sideMediumGreen, ComplexBorderSide.Empty, sideMediumGreen, ComplexBorderSide.Empty)
            report.Cells(8, c).CellType = percentCell
            report.Cells(8, c).Font = New Font("Ariel", 12)
            report.Cells(8, c).ForeColor = Color.FromArgb(255, 89, 89, 89)
            report.Cells(8, c).Formula = $"Calculations!H{8 + (c - 1) / 2}"
            report.Cells(8, c).HorizontalAlignment = CellHorizontalAlignment.Left
      report.Cells(8, c).TextIndent = 8
      report.Cells(9, c).Border = New ComplexBorder(sideMediumGreen, ComplexBorderSide.Empty, sideMediumGreen, ComplexBorderSide.Empty)
            If c < 9 Then
                report.Cells(10, c).Border = New ComplexBorder(sideMediumGreen, ComplexBorderSide.Empty, sideMediumGreen, sideMediumGreen)
            Else
                report.SetOutlineBorder(New CellRange(10, c, 1, 3), New ComplexBorder(sideMediumGreen, ComplexBorderSide.Empty, sideMediumGreen, sideMediumGreen))
            End If
        Next
        Dim iconRule As New IconSetConditionalFormattingRule(ConditionalFormattingIconSetStyle.ThreeColoredArrows)
        iconRule.IconRuleSet.SetItem(0, New FarPoint.Win.Spread.ConditionalFormattingIconValue(0.005, FarPoint.Win.Spread.ConditionalFormattingValueType.Number, False, New FarPoint.Win.Spread.ConditionalFormattingIconSetIndex(FarPoint.Win.Spread.ConditionalFormattingIconSetStyle.ThreeColoredArrows, 2)))
        iconRule.IconRuleSet.SetItem(1, New FarPoint.Win.Spread.ConditionalFormattingIconValue(-0.005, FarPoint.Win.Spread.ConditionalFormattingValueType.Number, True, New FarPoint.Win.Spread.ConditionalFormattingIconSetIndex(FarPoint.Win.Spread.ConditionalFormattingIconSetStyle.ThreeColoredArrows, 1)))
        iconRule.IconRuleSet.SetItem(2, New FarPoint.Win.Spread.ConditionalFormattingIconValue(0.0, FarPoint.Win.Spread.ConditionalFormattingValueType.Number, True, New FarPoint.Win.Spread.ConditionalFormattingIconSetIndex(FarPoint.Win.Spread.ConditionalFormattingIconSetStyle.ThreeColoredArrows, 0)))
        Dim iconCells As New List(Of CellRange)()
        For c = 1 To 9 Step 2
            iconCells.Add(New CellRange(8, c, 1, 1))
        Next
        For r = 15 To 39
            iconCells.Add(New CellRange(r, 7, 1, 1))
        Next
        report.SetConditionalFormatting(iconCells.ToArray(), New IConditionalFormattingRule() {iconRule})
        Dim sparklineSettingMetrics As New ExcelSparklineSetting()
        sparklineSettingMetrics.ShowMarkers = True
        sparklineSettingMetrics.MarkersColor = Color.FromArgb(255, 38, 94, 53)
        sparklineSettingMetrics.SeriesColor = System.Drawing.Color.FromArgb(255, 166, 166, 166)
        Dim sparklineMetricsDataCells As New List(Of SheetCellRange)()
        For r = 7 To 11
            sparklineMetricsDataCells.Add(New SheetCellRange("Calculations", r, 2, 1, 5))
        Next
        Dim sparklineMetricsCells As New List(Of CellRange)()
        For c = 1 To 9 Step 2
            sparklineMetricsCells.Add(New CellRange(9, c, 1, 1))
        Next
        Dim sparklineGroupKeyMetrics As ExcelSparklineGroup = report.AddSparkline(sparklineMetricsDataCells.ToArray(), sparklineMetricsCells.ToArray(), SparklineType.Line, sparklineSettingMetrics)
        report.Cells(6, 9).ColumnSpan = 3
        report.Cells(7, 9).ColumnSpan = 3
        report.Cells(8, 9).ColumnSpan = 3
        report.Cells(9, 9).ColumnSpan = 3
        report.SetOutlineBorder(New CellRange(11, 1, 1, 11), New ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, sideMediumGray))
        report.Cells(12, 1).Value = "ALL METRICS"
        report.Cells(12, 1).Font = New Font("Trebuchet MS", 14)
        report.Cells(12, 1).ForeColor = Color.FromArgb(255, 89, 89, 89)
        report.SetOutlineBorder(New CellRange(12, 1, 1, 11), New ComplexBorder(ComplexBorderSide.Empty, ComplexBorderSide.Empty, ComplexBorderSide.Empty, sideMediumGray))
        report.Cells(14, 1, 14, 11).BackColor = Color.FromArgb(255, 38, 94, 53)
        report.Cells(14, 1, 14, 11).Border = New ComplexBorder(sideMediumGreen)
        report.Cells(14, 1, 14, 11).Font = New Font("Ariel", 11, FontStyle.Bold)
        report.Cells(14, 1, 14, 11).ForeColor = Color.White
        report.Cells(14, 1, 14, 11).TextIndent = 8
        report.Cells(14, 1).Value = "METRIC"
        report.Cells(14, 3).Formula = """REPORT YEAR (""&SelectedYear&"")"""
        report.Cells(14, 5).Formula = """PREVIOUS YEAR (""&SelectedYear-1&"")"""
        report.Cells(14, 7).HorizontalAlignment = CellHorizontalAlignment.Center
        report.Cells(14, 7).Value = "% CHANGE"
        For r = 14 To 39
            report.Cells(r, 8, r, 8).ColumnSpan = 4
        Next
        report.Cells(14, 8).Formula = "CONCATENATE(Years,"" YEAR TREND"")"
        Dim formatTable As New FormulaConditionalFormattingRule("MOD(ROW(),2)=0")
        formatTable.BackColor = Color.FromArgb(255, 242, 242, 242)
        Dim sideLightGray As New ComplexBorderSide(System.Drawing.Color.FromArgb(255, 217, 217, 217))
        formatTable.Border = New ComplexBorder(ComplexBorderSide.Empty, sideLightGray, ComplexBorderSide.Empty, sideLightGray)
        report.SetConditionalFormatting(New CellRange() {New CellRange(15, 1, 25, 11)}, New IConditionalFormattingRule() {formatTable})
        report.Cells(15, 1, 39, 1).Formula = "Calculations!B15"
        report.Cells(15, 3, 39, 3).CellType = currencyCell
        report.Cells(15, 3, 39, 3).Formula = "IF($B16="""","""",Calculations!G15)"
        report.Cells(15, 5, 39, 5).CellType = currencyCell
        report.Cells(15, 5, 39, 5).Formula = "IF($B16="""","""",Calculations!F15)"
        report.Cells(15, 7, 39, 7).CellType = percentCell
        report.Cells(15, 7, 39, 7).Formula = "IFERROR($D16/$F16-1,"""")"
        report.Cells(15, 7, 39, 7).HorizontalAlignment = CellHorizontalAlignment.Center
        report.Cells(15, 1, 39, 9).Font = New Font("Trebuchet MS", 10)
        report.Cells(15, 1, 39, 9).ForeColor = Color.FromArgb(255, 89, 89, 89)
        report.Cells(15, 1, 39, 9).TextIndent = 11
        Dim sparklineSettingTrends As New ExcelSparklineSetting()
        sparklineSettingTrends.ShowMarkers = True
        sparklineSettingTrends.MarkersColor = Color.FromArgb(255, 38, 94, 53)
        sparklineSettingTrends.SeriesColor = System.Drawing.Color.FromArgb(255, 166, 166, 166)
        Dim sparklineAllMetricsDataCells As New List(Of SheetCellRange)()
        For r = 14 To 38
            sparklineAllMetricsDataCells.Add(New SheetCellRange("Calculations", r, 2, 1, 5))
        Next
        Dim trendRanges As New List(Of CellRange)()
        For r = 15 To 39
            trendRanges.Add(New CellRange(r, 8, 1, 1))
        Next
        Dim sparklineAllMetrics As ExcelSparklineGroup = report.AddSparkline(sparklineAllMetricsDataCells.ToArray(), trendRanges.ToArray(), SparklineType.Line, sparklineSettingTrends)
        report.DefaultStyle.Locked = True
        report.Cells(1, 10).Locked = False
        report.Protect = True
        report.SetActiveCell(1, 10)
    End Sub
End Class

'
'
'

Partial Class AnnualFinancialReport
	''' <summary> 
	''' Required designer variable.
	''' </summary>
	Private components As System.ComponentModel.IContainer = Nothing

    ''' <summary> 
    ''' Clean up any resources being used.
    ''' </summary>
    ''' <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
    Protected Overloads Sub Dispose(disposing As Boolean)
        If disposing AndAlso (components IsNot Nothing) Then
            components.Dispose()
        End If
        MyBase.Dispose(disposing)
    End Sub

#Region "Component Designer generated code"

    ''' <summary> 
    ''' Required method for Designer support - do not modify 
    ''' the contents of this method with the code editor.
    ''' </summary>
    Private Sub InitializeComponent()
		Dim flatScrollBarRenderer1 As New FarPoint.Win.Spread.FlatScrollBarRenderer()
		Dim flatScrollBarRenderer2 As New FarPoint.Win.Spread.FlatScrollBarRenderer()
		Me.fpSpread = New FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None)
		Me._svFinancialReport = New FarPoint.Win.Spread.SheetView()
		Me._svFinancialDataInput = New FarPoint.Win.Spread.SheetView()
		Me._svKeyMetricSettings = New FarPoint.Win.Spread.SheetView()
		Me._svCalculations = New FarPoint.Win.Spread.SheetView()
		Me.menuStrip1 = New System.Windows.Forms.MenuStrip()
		Me.viewToolStripMenuItem = New System.Windows.Forms.ToolStripMenuItem()
		Me._miConditionalFormats = New System.Windows.Forms.ToolStripMenuItem()
		Me._miNameManager = New System.Windows.Forms.ToolStripMenuItem()
		Me.nameBox = New FarPoint.Win.Spread.NameBox()
		Me.formulaTextBox1 = New FarPoint.Win.Spread.FormulaTextBox()
		Me.pnlMain.SuspendLayout()
		Me.pnlControls.SuspendLayout()
		DirectCast(Me.fpSpread, System.ComponentModel.ISupportInitialize).BeginInit()
		DirectCast(Me._svFinancialReport, System.ComponentModel.ISupportInitialize).BeginInit()
		DirectCast(Me._svFinancialDataInput, System.ComponentModel.ISupportInitialize).BeginInit()
		DirectCast(Me._svKeyMetricSettings, System.ComponentModel.ISupportInitialize).BeginInit()
		DirectCast(Me._svCalculations, System.ComponentModel.ISupportInitialize).BeginInit()
		Me.menuStrip1.SuspendLayout()
		Me.SuspendLayout()
		' 
		' pnlMain
		' 
		Me.pnlMain.Controls.Add(Me.fpSpread)
		' 
		' pnlControls
		' 
		Me.pnlControls.Controls.Add(Me.formulaTextBox1)
		Me.pnlControls.Controls.Add(Me.nameBox)
		Me.pnlControls.Controls.Add(Me.menuStrip1)
		Me.pnlControls.Padding = New System.Windows.Forms.Padding(5)
		' 
		' fpSpread
		' 
		Me.fpSpread.AccessibleDescription = "fpSpread, Calculations, Row 0, Column 0"
		Me.fpSpread.AllowCellOverflow = True
		Me.fpSpread.AllowDragDrop = True
		Me.fpSpread.AllowDragFill = True
		Me.fpSpread.AllowEditOverflow = True
		Me.fpSpread.Dock = System.Windows.Forms.DockStyle.Fill
		Me.fpSpread.DragFillDataOnly = True
		Me.fpSpread.HorizontalScrollBar.Buttons = New FarPoint.Win.Spread.FpScrollBarButtonCollection("BackwardLineButton,ThumbTrack,ForwardLineButton")
		Me.fpSpread.HorizontalScrollBar.Name = ""
		flatScrollBarRenderer1.ArrowColor = System.Drawing.Color.FromArgb(CInt(CByte(121)), CInt(CByte(121)), CInt(CByte(121)))
		flatScrollBarRenderer1.BackColor = System.Drawing.Color.FromArgb(CInt(CByte(255)), CInt(CByte(255)), CInt(CByte(255)))
		flatScrollBarRenderer1.BorderActiveColor = System.Drawing.Color.FromArgb(CInt(CByte(171)), CInt(CByte(171)), CInt(CByte(171)))
		flatScrollBarRenderer1.TrackBarBackColor = System.Drawing.Color.FromArgb(CInt(CByte(219)), CInt(CByte(219)), CInt(CByte(219)))
		Me.fpSpread.HorizontalScrollBar.Renderer = flatScrollBarRenderer1
		Me.fpSpread.Location = New System.Drawing.Point(0, 0)
		Me.fpSpread.Name = "fpSpread"
		Me.fpSpread.RangeDragFillMode = FarPoint.Win.Spread.DragFillMode.Series
		Me.fpSpread.Sheets.AddRange(New FarPoint.Win.Spread.SheetView() {Me._svFinancialReport, Me._svFinancialDataInput, Me._svKeyMetricSettings, Me._svCalculations})
		Me.fpSpread.Size = New System.Drawing.Size(477, 487)
		Me.fpSpread.TabIndex = 0
		Me.fpSpread.VerticalScrollBar.Buttons = New FarPoint.Win.Spread.FpScrollBarButtonCollection("BackwardLineButton,ThumbTrack,ForwardLineButton")
		Me.fpSpread.VerticalScrollBar.Name = ""
		flatScrollBarRenderer2.ArrowColor = System.Drawing.Color.FromArgb(CInt(CByte(121)), CInt(CByte(121)), CInt(CByte(121)))
		flatScrollBarRenderer2.BackColor = System.Drawing.Color.FromArgb(CInt(CByte(255)), CInt(CByte(255)), CInt(CByte(255)))
		flatScrollBarRenderer2.BorderActiveColor = System.Drawing.Color.FromArgb(CInt(CByte(171)), CInt(CByte(171)), CInt(CByte(171)))
		flatScrollBarRenderer2.TrackBarBackColor = System.Drawing.Color.FromArgb(CInt(CByte(219)), CInt(CByte(219)), CInt(CByte(219)))
		Me.fpSpread.VerticalScrollBar.Renderer = flatScrollBarRenderer2
		' 
		' _svFinancialReport
		' 
		Me._svFinancialReport.Reset()
		Me._svFinancialReport.SheetName = "Financial Report"
		' Formulas and custom names must be loaded with R1C1 reference style
		Me._svFinancialReport.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
		Me._svFinancialReport.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me._svFinancialReport.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me._svFinancialReport.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty
		Me._svFinancialReport.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty
		Me._svFinancialReport.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me._svFinancialReport.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me._svFinancialReport.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me._svFinancialReport.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me._svFinancialReport.SheetCornerStyle.BackColor = System.Drawing.Color.Empty
		Me._svFinancialReport.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty
		Me._svFinancialReport.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
		' 
		' _svFinancialDataInput
		' 
		Me._svFinancialDataInput.Reset()
		Me._svFinancialDataInput.SheetName = "Financial Data Input"
		' Formulas and custom names must be loaded with R1C1 reference style
		Me._svFinancialDataInput.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
		Me._svFinancialDataInput.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me._svFinancialDataInput.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me._svFinancialDataInput.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty
		Me._svFinancialDataInput.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty
		Me._svFinancialDataInput.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me._svFinancialDataInput.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me._svFinancialDataInput.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me._svFinancialDataInput.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me._svFinancialDataInput.SheetCornerStyle.BackColor = System.Drawing.Color.Empty
		Me._svFinancialDataInput.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty
		Me._svFinancialDataInput.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
		' 
		' _svKeyMetricSettings
		' 
		Me._svKeyMetricSettings.Reset()
		Me._svKeyMetricSettings.SheetName = "Key Metric Settings"
		' Formulas and custom names must be loaded with R1C1 reference style
		Me._svKeyMetricSettings.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
		Me._svKeyMetricSettings.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me._svKeyMetricSettings.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me._svKeyMetricSettings.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty
		Me._svKeyMetricSettings.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty
		Me._svKeyMetricSettings.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me._svKeyMetricSettings.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me._svKeyMetricSettings.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me._svKeyMetricSettings.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me._svKeyMetricSettings.SheetCornerStyle.BackColor = System.Drawing.Color.Empty
		Me._svKeyMetricSettings.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty
		Me._svKeyMetricSettings.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
		' 
		' _svCalculations
		' 
		Me._svCalculations.Reset()
		Me._svCalculations.SheetName = "Calculations"
		' Formulas and custom names must be loaded with R1C1 reference style
		Me._svCalculations.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
		Me._svCalculations.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me._svCalculations.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me._svCalculations.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty
		Me._svCalculations.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty
		Me._svCalculations.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me._svCalculations.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me._svCalculations.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty
		Me._svCalculations.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty
		Me._svCalculations.SheetCornerStyle.BackColor = System.Drawing.Color.Empty
		Me._svCalculations.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty
		Me._svCalculations.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
		' 
		' menuStrip1
		' 
		Me.menuStrip1.Items.AddRange(New System.Windows.Forms.ToolStripItem() {Me.viewToolStripMenuItem})
		Me.menuStrip1.Location = New System.Drawing.Point(5, 5)
		Me.menuStrip1.Name = "menuStrip1"
		Me.menuStrip1.Size = New System.Drawing.Size(147, 24)
		Me.menuStrip1.TabIndex = 0
		Me.menuStrip1.Text = "menuStrip1"
		' 
		' viewToolStripMenuItem
		' 
		Me.viewToolStripMenuItem.DropDownItems.AddRange(New System.Windows.Forms.ToolStripItem() {Me._miConditionalFormats, Me._miNameManager})
		Me.viewToolStripMenuItem.Name = "viewToolStripMenuItem"
		Me.viewToolStripMenuItem.Size = New System.Drawing.Size(44, 20)
		Me.viewToolStripMenuItem.Text = "View"
		' 
		' _miConditionalFormats
		' 
		Me._miConditionalFormats.Name = "_miConditionalFormats"
		Me._miConditionalFormats.Size = New System.Drawing.Size(182, 22)
		Me._miConditionalFormats.Text = "Conditional Formats"
		' 
		' _miNameManager
		' 
		Me._miNameManager.Name = "_miNameManager"
		Me._miNameManager.Size = New System.Drawing.Size(182, 22)
		Me._miNameManager.Text = "Name Manager"
		' 
		' nameBox
		' 
		Me.nameBox.Dock = System.Windows.Forms.DockStyle.Top
		Me.nameBox.Location = New System.Drawing.Point(5, 29)
		Me.nameBox.MaxLength = 255
		Me.nameBox.Name = "nameBox"
		Me.nameBox.Size = New System.Drawing.Size(147, 21)
		Me.nameBox.TabIndex = 1
		' 
		' formulaTextBox1
		' 
		Me.formulaTextBox1.Dock = System.Windows.Forms.DockStyle.Top
		Me.formulaTextBox1.Location = New System.Drawing.Point(5, 50)
		Me.formulaTextBox1.Multiline = False
		Me.formulaTextBox1.Name = "formulaTextBox1"
		Me.formulaTextBox1.Size = New System.Drawing.Size(147, 23)
		Me.formulaTextBox1.TabIndex = 2
		' 
		' AnnualFinancialReport
		' 
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.Name = "AnnualFinancialReport"
		Me.pnlMain.ResumeLayout(False)
		Me.pnlControls.ResumeLayout(False)
		Me.pnlControls.PerformLayout()
		DirectCast(Me.fpSpread, System.ComponentModel.ISupportInitialize).EndInit()
		DirectCast(Me._svFinancialReport, System.ComponentModel.ISupportInitialize).EndInit()
		DirectCast(Me._svFinancialDataInput, System.ComponentModel.ISupportInitialize).EndInit()
		DirectCast(Me._svKeyMetricSettings, System.ComponentModel.ISupportInitialize).EndInit()
		DirectCast(Me._svCalculations, System.ComponentModel.ISupportInitialize).EndInit()
		Me.menuStrip1.ResumeLayout(False)
		Me.menuStrip1.PerformLayout()
		Me.ResumeLayout(False)

	End Sub

	#End Region

	Private fpSpread As FarPoint.Win.Spread.FpSpread
	Private _svFinancialReport As FarPoint.Win.Spread.SheetView
	Private _svFinancialDataInput As FarPoint.Win.Spread.SheetView
	Private _svKeyMetricSettings As FarPoint.Win.Spread.SheetView
	Private _svCalculations As FarPoint.Win.Spread.SheetView
	Private menuStrip1 As System.Windows.Forms.MenuStrip
	Private viewToolStripMenuItem As System.Windows.Forms.ToolStripMenuItem
	Private _miConditionalFormats As System.Windows.Forms.ToolStripMenuItem
	Private _miNameManager As System.Windows.Forms.ToolStripMenuItem
	Private formulaTextBox1 As FarPoint.Win.Spread.FormulaTextBox
	Private nameBox As FarPoint.Win.Spread.NameBox
End Class
