﻿Imports FarPoint.Win.Spread
Imports GrapeCity.Spreadsheet
Imports Spread.Common.Features.Spread.Common.Features.Sample
Imports System
Imports System.Windows.Forms

Partial Public Class GoalSeek
    Inherits BaseSampleControl
    Public Sub New()
        InitializeComponent()
        Name = "Goal Seek"
        AddHandler _fpSpread.ActiveSheetChanged, AddressOf OnActiveSheetChanged
        SetupProfitGoalSeekSheet(_fpSpread)
        SetupLoanRepaymentGoalSeekSheet(_fpSpread)
        SetupGoalSeekUI()
    End Sub

    Private Sub SetupLoanRepaymentGoalSeekSheet(ByVal spread As FpSpread)
        Dim workSheet As IWorksheet = spread.AsWorkbook().Worksheets.Add("Loan Repayment Goal Seek")

        ' Styling
        spread.Sheets(1).Cells("A2:A5").BackColor = Drawing.Color.SkyBlue
        spread.Sheets(1).Cells("A2:B6").Border = New FarPoint.Win.LineBorder(Drawing.Color.Black, 1)
        spread.Sheets(1).Cells("A6").BackColor = Drawing.Color.Orange
        workSheet.Columns("A").ColumnWidth = 150
        workSheet.Columns("B").ColumnWidth = 250
        workSheet.Cells("A2:A6").Font.Bold = True
        workSheet.Cells("B6").Font.Bold = True

        ' Data
        workSheet.Cells("A2").Value = "Amount"
        workSheet.Cells("A3").Value = "Rate of Interest"
        workSheet.Cells("A4").Value = "Number of Periods"
        workSheet.Cells("A5").Value = "EMI"
        workSheet.Cells("A6").Value = "Total Amount Repaid"
        workSheet.Cells("B2").Value = 18000
        workSheet.Cells("B3").Value = 0.1
        workSheet.Cells("B3").NumberFormat = "0.00%"
        workSheet.Cells("B4").Value = 45
        workSheet.Cells("B5").Formula = "PMT($B3/12,$B4,-$B2, 0, 0)"
        workSheet.Cells("B6").Formula = "$B5*$B4"
        workSheet.Cells("B2").NumberFormat = "$###,###.00"
        workSheet.Cells("B4:B6").NumberFormat = "$###,###.00"
    End Sub

    Private Sub SetupProfitGoalSeekSheet(ByVal spread As FpSpread)
        Dim workSheet As IWorksheet = spread.Sheets(0).AsWorksheet()

        ' Styling
        spread.Sheets(0).Cells("A2:A7").BackColor = Drawing.Color.SkyBlue
        spread.Sheets(0).Cells("A2:C8").Border = New FarPoint.Win.LineBorder(Drawing.Color.Black, 1)
        spread.Sheets(0).Cells("A8").BackColor = Drawing.Color.Orange
        workSheet.Name = "Profit Goal Seek"
        workSheet.Columns("A").ColumnWidth = 150
        workSheet.Columns("B").ColumnWidth = 250
        workSheet.Columns("C").ColumnWidth = 200
        workSheet.Cells("A2:A8").Font.Bold = True
        workSheet.Cells("C8").Font.Bold = True

        ' Data
        workSheet.Cells("A2").Value = "Price"
        workSheet.Cells("A3").Value = "Quantity"
        workSheet.Cells("A4").Value = "Variable Cost"
        workSheet.Cells("A5").Value = "Fixed Cost"
        workSheet.Cells("A6").Value = "Total Revenue"
        workSheet.Cells("A7").Value = "Total Cost"
        workSheet.Cells("A8").Value = "Profit"
        workSheet.Cells("B4").Value = "(Price * 10%)"
        workSheet.Cells("B6").Value = "(Price * Quantity)"
        workSheet.Cells("B7").Value = "Fixed Cost + (Variable Cost * Quantity)"
        workSheet.Cells("B8").Value = "Total Revenue - Total Cost"
        workSheet.Cells("C2").Value = 18000
        workSheet.Cells("C3").Value = 100
        workSheet.Cells("C4").Formula = "ROUND($C2*10%, 0)"
        workSheet.Cells("C5").Value = 3000000
        workSheet.Cells("C6").Formula = "ROUND($C2*$C3, 2)"
        workSheet.Cells("C7").Formula = "ROUND($C5+($C4*$C3), 2)"
        workSheet.Cells("C8").Formula = "ROUND($C6-$C7, 2)"
        workSheet.Cells("C2").NumberFormat = "$###,###.00"
        workSheet.Cells("C4:C8").NumberFormat = "$###,###.00"
    End Sub

    Private Sub OnGoalSeekClick(ByVal sender As Object, ByVal e As EventArgs) Handles _btnGoalSeek.Click
        Dim worksheet As IWorksheet = _fpSpread.ActiveSheet.AsWorksheet()
        Dim goal As Double = Nothing
        If Not Double.TryParse(_txtToValue.Text, goal) Then MessageBox.Show("Please provide a valid numeric value", "Invalid input", MessageBoxButtons.OK, MessageBoxIcon.Error)

        ' Goal Seek
        worksheet.Cells(_txtSetCell.Text).GoalSeek(goal, worksheet.Cells(_txtChangingCell.Text))
    End Sub

    Private Sub OnActiveSheetChanged(ByVal sender As Object, ByVal e As EventArgs)
        SetupGoalSeekUI()
    End Sub

    Private Sub SetupGoalSeekUI()
        If _fpSpread.ActiveSheetIndex = 0 Then
            _txtSetCell.Text = "$C8"
            _txtToValue.Text = "50000"
            _txtChangingCell.Text = "$C3"
            _lblValue.Text = "To value: (Goal Profit)"
        Else
            _txtSetCell.Text = "$B6"
            _txtToValue.Text = "25000"
            _txtChangingCell.Text = "$B4"
            _lblValue.Text = "To value: (Goal Repay Amount)"
        End If

        _txtSetCell.Enabled = False
        _txtChangingCell.Enabled = False
    End Sub

    Private Sub OnGoalSeekDialogMenuItemClick(sender As Object, e As EventArgs) Handles GoalSeekDialogToolStripMenuItem.Click
        Dim goalSeekDialog As Form = FarPoint.Win.Spread.Dialogs.BuiltInDialogs.GoalSeek(_fpSpread)
        goalSeekDialog.ShowDialog()
    End Sub
End Class

//
// GoalSeek.Designer.vb
//

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

        ''' <summary> 
        ''' Clean up any resources being used.
        ''' </summary>
        ''' <paramname="disposing">true if managed resources should be disposed; otherwise, false.</param>
        Protected Sub Dispose(ByVal 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 resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(GoalSeek))
        Me.splitContainer1 = New System.Windows.Forms.SplitContainer()
        Me.splitContainer2 = New System.Windows.Forms.SplitContainer()
        Me.nameBox1 = New FarPoint.Win.Spread.NameBox()
        Me._fpSpread = New FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None, CType(resources.GetObject("splitContainer2.Panel1.Controls"), Object))
        Me._fpSpread_Sheet1 = Me._fpSpread.GetSheet(0)
        Me.panel1 = New System.Windows.Forms.Panel()
        Me.formulaTextBox1 = New FarPoint.Win.Spread.FormulaTextBox()
        Me.label1 = New System.Windows.Forms.Label()
        Me._lblValue = New System.Windows.Forms.Label()
        Me.label3 = New System.Windows.Forms.Label()
        Me._txtSetCell = New System.Windows.Forms.TextBox()
        Me._btnGoalSeek = New System.Windows.Forms.Button()
        Me._txtToValue = New System.Windows.Forms.TextBox()
        Me._txtChangingCell = New System.Windows.Forms.TextBox()
        Me.MenuStrip1 = New System.Windows.Forms.MenuStrip()
        Me.GoalSeekDialogToolStripMenuItem = New System.Windows.Forms.ToolStripMenuItem()
        Me.pnlMain.SuspendLayout()
        Me.pnlControls.SuspendLayout()
        CType(Me.splitContainer1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.splitContainer1.Panel1.SuspendLayout()
        Me.splitContainer1.Panel2.SuspendLayout()
        Me.splitContainer1.SuspendLayout()
        CType(Me.splitContainer2, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.splitContainer2.Panel1.SuspendLayout()
        Me.splitContainer2.Panel2.SuspendLayout()
        Me.splitContainer2.SuspendLayout()
        CType(Me._fpSpread, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.panel1.SuspendLayout()
        Me.MenuStrip1.SuspendLayout()
        Me.SuspendLayout()
        '
        'pnlMain
        '
        Me.pnlMain.Controls.Add(Me.splitContainer1)
        Me.pnlMain.Controls.Add(Me.MenuStrip1)
        '
        'pnlControls
        '
        Me.pnlControls.Controls.Add(Me._txtChangingCell)
        Me.pnlControls.Controls.Add(Me._txtToValue)
        Me.pnlControls.Controls.Add(Me._btnGoalSeek)
        Me.pnlControls.Controls.Add(Me._txtSetCell)
        Me.pnlControls.Controls.Add(Me.label3)
        Me.pnlControls.Controls.Add(Me._lblValue)
        Me.pnlControls.Controls.Add(Me.label1)
        Me.pnlControls.Size = New System.Drawing.Size(210, 599)
        '
        'splitContainer1
        '
        Me.splitContainer1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.splitContainer1.IsSplitterFixed = True
        Me.splitContainer1.Location = New System.Drawing.Point(0, 28)
        Me.splitContainer1.Name = "splitContainer1"
        Me.splitContainer1.Orientation = System.Windows.Forms.Orientation.Horizontal
        '
        'splitContainer1.Panel1
        '
        Me.splitContainer1.Panel1.Controls.Add(Me.splitContainer2)
        '
        'splitContainer1.Panel2
        '
        Me.splitContainer1.Panel2.Controls.Add(Me._fpSpread)
        Me.splitContainer1.Size = New System.Drawing.Size(636, 571)
        Me.splitContainer1.SplitterDistance = 25
        Me.splitContainer1.TabIndex = 0
        '
        'splitContainer2
        '
        Me.splitContainer2.Dock = System.Windows.Forms.DockStyle.Fill
        Me.splitContainer2.Location = New System.Drawing.Point(0, 0)
        Me.splitContainer2.Name = "splitContainer2"
        '
        'splitContainer2.Panel1
        '
        Me.splitContainer2.Panel1.Controls.Add(Me.nameBox1)
        '
        'splitContainer2.Panel2
        '
        Me.splitContainer2.Panel2.Controls.Add(Me.panel1)
        Me.splitContainer2.Size = New System.Drawing.Size(636, 25)
        Me.splitContainer2.SplitterDistance = 105
        Me.splitContainer2.TabIndex = 0
        '
        'nameBox1
        '
        Me.nameBox1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.nameBox1.Location = New System.Drawing.Point(0, 0)
        Me.nameBox1.MaxLength = 255
        Me.nameBox1.Name = "nameBox1"
        Me.nameBox1.Size = New System.Drawing.Size(105, 24)
        Me.nameBox1.TabIndex = 0
        'Attach nameBox1 to _fpSpread
        Me.nameBox1.Attach(Me._fpSpread)
        '
        '_fpSpread
        '
        Me._fpSpread.AccessibleDescription = "Book1, Sheet1, Row 0, Column 0"
        Me._fpSpread.Dock = System.Windows.Forms.DockStyle.Fill
        Me._fpSpread.Font = New System.Drawing.Font("Calibri", 11.0!)
        Me._fpSpread.Location = New System.Drawing.Point(0, 0)
        Me._fpSpread.Name = "_fpSpread"
        Me._fpSpread.Size = New System.Drawing.Size(636, 542)
        Me._fpSpread.TabIndex = 0
        '
        'panel1
        '
        Me.panel1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.panel1.Controls.Add(Me.formulaTextBox1)
        Me.panel1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.panel1.Location = New System.Drawing.Point(0, 0)
        Me.panel1.Name = "panel1"
        Me.panel1.Size = New System.Drawing.Size(527, 25)
        Me.panel1.TabIndex = 0
        '
        'formulaTextBox1
        '
        Me.formulaTextBox1.AcceptsTab = True
        Me.formulaTextBox1.BorderStyle = System.Windows.Forms.BorderStyle.None
        Me.formulaTextBox1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.formulaTextBox1.Location = New System.Drawing.Point(0, 0)
        Me.formulaTextBox1.Margin = New System.Windows.Forms.Padding(0)
        Me.formulaTextBox1.Name = "formulaTextBox1"
        Me.formulaTextBox1.RightToLeft = System.Windows.Forms.RightToLeft.No
        Me.formulaTextBox1.Size = New System.Drawing.Size(525, 23)
        Me.formulaTextBox1.TabIndex = 0
        'Attach formulaTextBox1 to _fpSpread
        Me.formulaTextBox1.Attach(Me._fpSpread)
        '
        'label1
        '
        Me.label1.AutoSize = True
        Me.label1.Location = New System.Drawing.Point(7, 29)
        Me.label1.Name = "label1"
        Me.label1.Size = New System.Drawing.Size(54, 16)
        Me.label1.TabIndex = 0
        Me.label1.Text = "Set cell:"
        '
        '_lblValue
        '
        Me._lblValue.AutoSize = True
        Me._lblValue.Location = New System.Drawing.Point(7, 93)
        Me._lblValue.Name = "_lblValue"
        Me._lblValue.Size = New System.Drawing.Size(63, 16)
        Me._lblValue.TabIndex = 1
        Me._lblValue.Text = "To value:"
        '
        'label3
        '
        Me.label3.AutoSize = True
        Me.label3.Location = New System.Drawing.Point(7, 156)
        Me.label3.Name = "label3"
        Me.label3.Size = New System.Drawing.Size(108, 16)
        Me.label3.TabIndex = 2
        Me.label3.Text = "By changing cell:"
        '
        '_txtSetCell
        '
        Me._txtSetCell.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) _
            Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me._txtSetCell.Location = New System.Drawing.Point(10, 59)
        Me._txtSetCell.Name = "_txtSetCell"
        Me._txtSetCell.Size = New System.Drawing.Size(182, 22)
        Me._txtSetCell.TabIndex = 3
        '
        'button1
        '
        Me._btnGoalSeek.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) _
            Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me._btnGoalSeek.Location = New System.Drawing.Point(10, 225)
        Me._btnGoalSeek.Name = "button1"
        Me._btnGoalSeek.Size = New System.Drawing.Size(182, 30)
        Me._btnGoalSeek.TabIndex = 6
        Me._btnGoalSeek.Text = "Goal Seek"
        Me._btnGoalSeek.UseVisualStyleBackColor = True
        '
        '_txtToValue
        '
        Me._txtToValue.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) _
            Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me._txtToValue.Location = New System.Drawing.Point(10, 122)
        Me._txtToValue.Name = "_txtToValue"
        Me._txtToValue.Size = New System.Drawing.Size(182, 22)
        Me._txtToValue.TabIndex = 7
        '
        '_txtChangingCell
        '
        Me._txtChangingCell.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) _
            Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me._txtChangingCell.Location = New System.Drawing.Point(10, 185)
        Me._txtChangingCell.Name = "_txtChangingCell"
        Me._txtChangingCell.Size = New System.Drawing.Size(182, 22)
        Me._txtChangingCell.TabIndex = 8
        '
        'MenuStrip1
        '
        Me.MenuStrip1.ImageScalingSize = New System.Drawing.Size(20, 20)
        Me.MenuStrip1.Items.AddRange(New System.Windows.Forms.ToolStripItem() {Me.GoalSeekDialogToolStripMenuItem})
        Me.MenuStrip1.Location = New System.Drawing.Point(0, 0)
        Me.MenuStrip1.Name = "MenuStrip1"
        Me.MenuStrip1.Size = New System.Drawing.Size(636, 28)
        Me.MenuStrip1.TabIndex = 1
        Me.MenuStrip1.Text = "MenuStrip1"
        '
        'GoalSeekDialogToolStripMenuItem
        '
        Me.GoalSeekDialogToolStripMenuItem.Name = "GoalSeekDialogToolStripMenuItem"
        Me.GoalSeekDialogToolStripMenuItem.Size = New System.Drawing.Size(138, 24)
        Me.GoalSeekDialogToolStripMenuItem.Text = "Goal Seek Dialog"
        '
        'GoalSeek
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(8.0!, 16.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.Name = "GoalSeek"
        Me.pnlMain.ResumeLayout(False)
        Me.pnlMain.PerformLayout()
        Me.pnlControls.ResumeLayout(False)
        Me.pnlControls.PerformLayout()
        Me.splitContainer1.Panel1.ResumeLayout(False)
        Me.splitContainer1.Panel2.ResumeLayout(False)
        CType(Me.splitContainer1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.splitContainer1.ResumeLayout(False)
        Me.splitContainer2.Panel1.ResumeLayout(False)
        Me.splitContainer2.Panel2.ResumeLayout(False)
        CType(Me.splitContainer2, System.ComponentModel.ISupportInitialize).EndInit()
        Me.splitContainer2.ResumeLayout(False)
        CType(Me._fpSpread, System.ComponentModel.ISupportInitialize).EndInit()
        Me.panel1.ResumeLayout(False)
        Me.MenuStrip1.ResumeLayout(False)
        Me.MenuStrip1.PerformLayout()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private splitContainer1 As Windows.Forms.SplitContainer
    Private splitContainer2 As Windows.Forms.SplitContainer
    Private nameBox1 As FarPoint.Win.Spread.NameBox
    Private panel1 As Windows.Forms.Panel
    Private formulaTextBox1 As FarPoint.Win.Spread.FormulaTextBox
    Private _fpSpread As FarPoint.Win.Spread.FpSpread
    Private _fpSpread_Sheet1 As FarPoint.Win.Spread.SheetView
    Private label1 As Windows.Forms.Label
    Private _lblValue As Windows.Forms.Label
    Private label3 As Windows.Forms.Label
    Private _txtSetCell As Windows.Forms.TextBox
    Private WithEvents _btnGoalSeek As Windows.Forms.Button
    Private _txtToValue As Windows.Forms.TextBox
        Private _txtChangingCell As Windows.Forms.TextBox
    Friend WithEvents MenuStrip1 As Windows.Forms.MenuStrip
    Friend WithEvents GoalSeekDialogToolStripMenuItem As Windows.Forms.ToolStripMenuItem
End Class