Imports FarPoint.Win.Spread
Imports GrapeCity.Spreadsheet
Imports Spread.Common.Features.Spread.Common.Features.Sample

Namespace Features
    Public Class VariSparkline
        Inherits BaseSampleControl

        Public Sub New()
            InitializeComponent()
            Name = "Vari"

            InitSpread()
        End Sub

#Region "Private methods"
        Private Sub InitSpread()
            Dim worksheet As IWorksheet = fpSpread1.ActiveSheet.AsWorksheet()

            Dim table As ITable = worksheet.Tables.Add("A1:E13", YesNoGuess.Yes, FarPoint.Win.Spread.TableStyle.TableStyleMedium4, "Table1")
            table.ShowAutoFilter = False

            worksheet.SetValue(0, 0, New Object(,) {
            {"Sales", "Budget", "Actual", "Variance", "Variance %"},
            {"Jan", 305554, 336431, Nothing, Nothing},
            {"Feb", 280311, 370582, Nothing, Nothing},
            {"Mar", 309347, 322689, Nothing, Nothing},
            {"Apr", 502688, 386790, Nothing, Nothing},
            {"May", 351267, 363122, Nothing, Nothing},
            {"Jun", 351922, 314589, Nothing, Nothing},
            {"Jul", 364770, 342515, Nothing, Nothing},
            {"Aug", 373980, 317154, Nothing, Nothing},
            {"Sep", 397717, 360083, Nothing, Nothing},
            {"Oct", 275849, 370909, Nothing, Nothing},
            {"Nov", 426031, 376791, Nothing, Nothing},
            {"Dec", 267710, 398198, Nothing, Nothing}})

            For i As Integer = 2 To 14 - 1
                worksheet.Cells($"D{i}").Formula = "(C" & i & "-B" & i & ")"
                worksheet.Cells($"E{i}").Formula2 = "VARISPARKLINE(ROUND((D" & i & ")/(B" & i & "),2),0,-1,1,,0.2,TRUE)"
            Next

            worksheet.Cells("B2:D13").HorizontalAlignment = HorizontalAlignment.Left
            worksheet.Cells("B2:D13").NumberFormat = "$#,000;$-#,000"
            worksheet.Cells("E2:E13").HorizontalAlignment = HorizontalAlignment.Left
            worksheet.Cells("E2:E13").NumberFormat = "0%"
            worksheet.Columns("B:D").ColumnWidth = 120
            worksheet.Columns("E").ColumnWidth = 200
        End Sub
#End Region

    End Class
End Namespace


'
'VariSparkline.Designer.vb
'


Namespace Features
    Partial Class VariSparkline
        ''' <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 Shadows Sub Dispose(disposing As Boolean)
            If disposing AndAlso (components IsNot Nothing) Then
                components.Dispose()
            End If
            MyBase.Dispose(disposing)
        End Sub

        'NOTE: The following procedure is required by the Windows Form Designer
        'It can be modified using the Windows Form Designer.  
        'Do not modify it using the code editor.
        <System.Diagnostics.DebuggerStepThrough()>
        Private Sub InitializeComponent()
            Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(VariSparkline))
            Me.fpSpread1 = New FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None, CType(resources.GetObject("pnlMain.Controls"), Object))
            Me.fpSpread1_Sheet1 = Me.fpSpread1.GetSheet(0)
            Me.pnlMain.SuspendLayout()
            CType(Me.fpSpread1, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.SuspendLayout()
            '
            'pnlMain
            '
            Me.pnlMain.Controls.Add(Me.fpSpread1)
            '
            'pnlControls
            '
            Me.pnlControls.Size = New System.Drawing.Size(210, 599)
            '
            'fpSpread1
            '
            Me.fpSpread1.AccessibleDescription = "Book1, Sheet1, Row 0, Column 0"
            Me.fpSpread1.Dock = System.Windows.Forms.DockStyle.Fill
            Me.fpSpread1.Font = New System.Drawing.Font("Calibri", 11.0!)
            Me.fpSpread1.Location = New System.Drawing.Point(0, 0)
            Me.fpSpread1.Name = "fpSpread1"
            Me.fpSpread1.Size = New System.Drawing.Size(636, 599)
            Me.fpSpread1.TabIndex = 4
            '
            'VariSparkline
            '
            Me.AutoScaleDimensions = New System.Drawing.SizeF(8.0!, 16.0!)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.Name = "VariSparkline"
            Me.pnlMain.ResumeLayout(False)
            CType(Me.fpSpread1, System.ComponentModel.ISupportInitialize).EndInit()
            Me.ResumeLayout(False)

        End Sub
        Private WithEvents fpSpread1 As FarPoint.Win.Spread.FpSpread
        Friend WithEvents fpSpread1_Sheet1 As FarPoint.Win.Spread.SheetView
    End Class
End Namespace
