Imports System.Collections.Generic
Imports System.Drawing
Imports FarPoint.Win.Spread
Imports Spread.Common.Features.Spread.Common.Features.Sample

Namespace Features
    Public Class BoxPlotSparkline
        Inherits BaseSampleControl

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

            Dim colors As List(Of Color) = New List(Of Color) From
            {
                Color.White,
                Color.Black,
                Color.Orange,
                Color.LightGray,
                Color.Gray,
                Color.DarkGray,
                Color.Green
            }
            comboSchemeColor.DataSource = colors
            comboSchemeColor.SelectedItem = Color.LightGray

            txtStartScope.Text = "0"
            txtEndScope.Text = "100"
            txtStartExpectedScope.Text = "20"
            txtEndExpectedScope.Text = "80"

            InitSpread()

            AddHandler btnChangeSettings.Click, AddressOf BtnChangeSettings_Click
        End Sub

#Region "Private method"
        Private Sub InitSpread()
            fpSpread1.Sheets.Count = 2
            fpSpread1.TabStripInsertTab = False
            InitHorizontalSparkline(fpSpread1.Sheets(0), "Horizontal")
            InitVerticalSparkline(fpSpread1.Sheets(1), "Vertical")
        End Sub

        Private Sub InitHorizontalSparkline(ByVal sheetView As SheetView, ByVal name As String)
            Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = sheetView.AsWorksheet()
            worksheet.Name = name
            worksheet.Cells("B2:O2").Merge()
            worksheet.Cells("B2").Value = "The Company Sales in 2014"
            worksheet.Cells("B2").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 16
            })
            worksheet.Cells("B2").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            sheetView.Cells("B2").ForeColor = Color.White
            sheetView.Cells("B2").BackColor = ColorTranslator.FromHtml("#999999")
            worksheet.Cells("B3:O3").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("B3:O3").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 12,
                .Bold = True
            })
            sheetView.Cells("B3:O3").BackColor = ColorTranslator.FromHtml("#efefef")
            worksheet.Cells("B3").Value = "Region"
            worksheet.Cells("O3").Value = "Actual Sales (mn)"

            For i As Integer = 2 To 14 - 1
                Dim columnChar As Char = Convert.ToChar(i + 65)
                worksheet.Cells($"{columnChar}3").Value = New DateTime(2014, i - 1, 1)
                worksheet.Cells($"{columnChar}3").NumberFormat = "mm"
            Next

            worksheet.SetValue(3, 1, New Object(,) {
            {"Alabama", 68, 81, 21, 69, 39, 28, 63, 73, 80, 95, 46, 37},
            {"Alaska", 37, 57, 99, 47, 95, 86, 44, 82, 89, 61, 45, 75},
            {"Arizona", 61, 42, 97, 12, 95, 25, 29, 44, 64, 39, 59, 35},
            {"Idaho", 16, 51, 45, 82, 72, 51, 100, 98, 92, 89, 44, 68},
            {"Indiana", 26, 46, 74, 58, 40, 32, 58, 75, 39, 58, 97, 39},
            {"Ohio", 37, 68, 20, 35, 65, 87, 39, 25, 79, 15, 91, 42},
            {"Oklahoma", 36, 43, 31, 63, 95, 26, 19, 88, 25, 78, 98, 53},
            {"Oregon", 28, 71, 68, 48, 19, 56, 88, 54, 28, 25, 92, 75},
            {"Vermon", 85, 25, 31, 34, 75, 82, 50, 27, 76, 72, 25, 100}})

            For index As Integer = 3 To 12 - 1
                worksheet.Cells($"O{index + 1}").Formula2 = "BOXPLOTSPARKLINE($C" & (index + 1) & ":$N" & (index + 1) & ",""5ns"",true,0,100,20,80,""#D3D3D3"",0,false)"
            Next

            worksheet.Rows("2:12").RowHeight = 30
            worksheet.Columns("O").ColumnWidth = 250
        End Sub

        Private Sub InitVerticalSparkline(ByVal sheetView As SheetView, ByVal name As String)
            Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = sheetView.AsWorksheet()
            worksheet.Name = name
            worksheet.Cells("B2:E2").Merge()
            worksheet.Cells("B2").Value = "Scores by Teaching Method"
            worksheet.Cells("B2").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("B2").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 11,
                .Bold = True
            })
            sheetView.Cells("B2").ForeColor = Color.White
            sheetView.Cells("B2").BackColor = ColorTranslator.FromHtml("#999999")
            worksheet.Cells("B3:E3").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Cells("B3:E3").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 11,
                .Bold = True
            })
            sheetView.Cells("B3:E3").BackColor = ColorTranslator.FromHtml("#efefef")
            worksheet.Cells("B3").Value = "Student ID"
            worksheet.Cells("C3").Value = "Method 1"
            worksheet.Cells("D3").Value = "Method 2"
            worksheet.Cells("E3").Value = "Method 3"
            worksheet.Cells("G24").Value = "Method 1"
            worksheet.Cells("H24").Value = "Method 2"
            worksheet.Cells("I24").Value = "Method 3"
            worksheet.Cells("G24:I24").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 9,
                .Bold = True
            })
            Dim scores As List(Of Object) = New List(Of Object) From
            {
                New With {.Id = 1, .MethodOne = 65, .MethodTwo = 60, .MethodThree = 98},
                New With {.Id = 2, .MethodOne = 91, .MethodTwo = 70, .MethodThree = 99},
                New With {.Id = 3, .MethodOne = 70, .MethodTwo = 86, .MethodThree = 92},
                New With {.Id = 4, .MethodOne = 62, .MethodTwo = 62, .MethodThree = 78},
                New With {.Id = 5, .MethodOne = 98, .MethodTwo = 79, .MethodThree = 71},
                New With {.Id = 6, .MethodOne = 89, .MethodTwo = 99, .MethodThree = 68},
                New With {.Id = 7, .MethodOne = 85, .MethodTwo = 100, .MethodThree = 88},
                New With {.Id = 8, .MethodOne = 65, .MethodTwo = 78, .MethodThree = 74},
                New With {.Id = 9, .MethodOne = 65, .MethodTwo = 66, .MethodThree = 85},
                New With {.Id = 10, .MethodOne = 65, .MethodTwo = 84, .MethodThree = 80},
                New With {.Id = 11, .MethodOne = 65, .MethodTwo = 98, .MethodThree = 79},
                New With {.Id = 12, .MethodOne = 65, .MethodTwo = 87, .MethodThree = 50},
                New With {.Id = 13, .MethodOne = 51, .MethodTwo = 68, .MethodThree = 64},
                New With {.Id = 14, .MethodOne = 45, .MethodTwo = 78, .MethodThree = 73},
                New With {.Id = 15, .MethodOne = 67, .MethodTwo = 81, .MethodThree = 88},
                New With {.Id = 16, .MethodOne = 87, .MethodTwo = 83, .MethodThree = 85},
                New With {.Id = 17, .MethodOne = 72, .MethodTwo = 84, .MethodThree = 84},
                New With {.Id = 18, .MethodOne = 74, .MethodTwo = 82, .MethodThree = 86},
                New With {.Id = 19, .MethodOne = 85, .MethodTwo = 45, .MethodThree = 92},
                New With {.Id = 20, .MethodOne = 65, .MethodTwo = 60, .MethodThree = 69}
            }


            For i As Integer = 0 To scores.Count - 1
                Dim student As Object = scores(i)
                worksheet.Cells($"B{i + 4}").Value = student.Id
                worksheet.Cells($"C{i + 4}").Value = student.MethodOne
                worksheet.Cells($"D{i + 4}").Value = student.MethodTwo
                worksheet.Cells($"E{i + 4}").Value = student.MethodThree
            Next

            worksheet.Columns("B:E").ColumnWidth = 100
            worksheet.Cells("G4:G23").Merge()
            worksheet.Cells("H4:H23").Merge()
            worksheet.Cells("I4:I23").Merge()
            worksheet.Cells("G4").Formula2 = "BOXPLOTSPARKLINE(C4:C23,""5ns"",true,0,100,20,80,""#DDDDDD"",0,true)"
            worksheet.Cells("H4").Formula2 = "BOXPLOTSPARKLINE(D4:D23,""5ns"",true,0,100,20,80,""#DDDDDD"",0,true)"
            worksheet.Cells("I4").Formula2 = "BOXPLOTSPARKLINE(E4:E23,""5ns"",true,0,100,20,80,""#DDDDDD"",0,true)"
        End Sub
#End Region

#Region "Event handlers"
        Private Sub BtnChangeSettings_Click(ByVal sender As Object, ByVal e As EventArgs)
            Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = fpSpread1.ActiveSheet.AsWorksheet()

            If fpSpread1.ActiveSheetIndex = 0 Then

                For index As Integer = 3 To 12 - 1
                    worksheet.Cells($"O{index + 1}").Formula2 = "BOXPLOTSPARKLINE($C" & (index + 1) & ":$N" & (index + 1) & ",""5ns"",true," & txtStartScope.Text & "," + txtEndScope.Text & "," + txtStartExpectedScope.Text & "," + txtEndExpectedScope.Text & ",""" + (CType(comboSchemeColor.SelectedItem, Color)).Name & """,0,FALSE)"
                Next
            ElseIf fpSpread1.ActiveSheetIndex = 1 Then
                worksheet.Cells("G4").Formula2 = "BOXPLOTSPARKLINE(C4:C23,""5ns"",true," & txtStartScope.Text & "," +
                    txtEndScope.Text & "," + txtStartExpectedScope.Text & "," + txtEndExpectedScope.Text & ",""" +
                    (CType(comboSchemeColor.SelectedItem, Color)).Name & """,0,TRUE)"

                worksheet.Cells("H4").Formula2 = "BOXPLOTSPARKLINE(D4:D23,""5ns"",true," & txtStartScope.Text & "," +
                    txtEndScope.Text & "," + txtStartExpectedScope.Text & "," + txtEndExpectedScope.Text & ",""" +
                    (CType(comboSchemeColor.SelectedItem, Color)).Name & """,0,TRUE)"

                worksheet.Cells("I4").Formula2 = "BOXPLOTSPARKLINE(E4:E23,""5ns"",true," & txtStartScope.Text & "," +
                    txtEndScope.Text & "," + txtStartExpectedScope.Text & "," + txtEndExpectedScope.Text & ",""" +
                    (CType(comboSchemeColor.SelectedItem, Color)).Name & """,0,TRUE)"
            End If
        End Sub
#End Region

    End Class
End Namespace




'
'BoxPlotSparkline.Designer.vb
'


Namespace Features
    Partial Class BoxPlotSparkline
        ''' <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(BoxPlotSparkline))
            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.groupBox1 = New System.Windows.Forms.GroupBox()
            Me.txtEndExpectedScope = New System.Windows.Forms.TextBox()
            Me.txtStartExpectedScope = New System.Windows.Forms.TextBox()
            Me.txtEndScope = New System.Windows.Forms.TextBox()
            Me.txtStartScope = New System.Windows.Forms.TextBox()
            Me.btnChangeSettings = New System.Windows.Forms.Button()
            Me.comboSchemeColor = New System.Windows.Forms.ComboBox()
            Me.label5 = New System.Windows.Forms.Label()
            Me.label4 = New System.Windows.Forms.Label()
            Me.label3 = New System.Windows.Forms.Label()
            Me.label2 = New System.Windows.Forms.Label()
            Me.label1 = New System.Windows.Forms.Label()
            Me.pnlMain.SuspendLayout()
            Me.pnlControls.SuspendLayout()
            CType(Me.fpSpread1, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.groupBox1.SuspendLayout()
            Me.SuspendLayout()
            '
            'pnlMain
            '
            Me.pnlMain.Controls.Add(Me.fpSpread1)
            '
            'pnlControls
            '
            Me.pnlControls.Controls.Add(Me.groupBox1)
            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 = 3
            '
            'groupBox1
            '
            Me.groupBox1.Controls.Add(Me.txtEndExpectedScope)
            Me.groupBox1.Controls.Add(Me.txtStartExpectedScope)
            Me.groupBox1.Controls.Add(Me.txtEndScope)
            Me.groupBox1.Controls.Add(Me.txtStartScope)
            Me.groupBox1.Controls.Add(Me.btnChangeSettings)
            Me.groupBox1.Controls.Add(Me.comboSchemeColor)
            Me.groupBox1.Controls.Add(Me.label5)
            Me.groupBox1.Controls.Add(Me.label4)
            Me.groupBox1.Controls.Add(Me.label3)
            Me.groupBox1.Controls.Add(Me.label2)
            Me.groupBox1.Controls.Add(Me.label1)
            Me.groupBox1.Dock = System.Windows.Forms.DockStyle.Top
            Me.groupBox1.Location = New System.Drawing.Point(0, 0)
            Me.groupBox1.Name = "groupBox1"
            Me.groupBox1.Size = New System.Drawing.Size(210, 367)
            Me.groupBox1.TabIndex = 2
            Me.groupBox1.TabStop = False
            Me.groupBox1.Text = "Settings"
            '
            'txtEndExpectedScope
            '
            Me.txtEndExpectedScope.Location = New System.Drawing.Point(12, 229)
            Me.txtEndExpectedScope.Name = "txtEndExpectedScope"
            Me.txtEndExpectedScope.Size = New System.Drawing.Size(146, 22)
            Me.txtEndExpectedScope.TabIndex = 14
            '
            'txtStartExpectedScope
            '
            Me.txtStartExpectedScope.Location = New System.Drawing.Point(12, 172)
            Me.txtStartExpectedScope.Name = "txtStartExpectedScope"
            Me.txtStartExpectedScope.Size = New System.Drawing.Size(146, 22)
            Me.txtStartExpectedScope.TabIndex = 13
            '
            'txtEndScope
            '
            Me.txtEndScope.Location = New System.Drawing.Point(12, 115)
            Me.txtEndScope.Name = "txtEndScope"
            Me.txtEndScope.Size = New System.Drawing.Size(146, 22)
            Me.txtEndScope.TabIndex = 12
            '
            'txtStartScope
            '
            Me.txtStartScope.Location = New System.Drawing.Point(12, 58)
            Me.txtStartScope.Name = "txtStartScope"
            Me.txtStartScope.Size = New System.Drawing.Size(146, 22)
            Me.txtStartScope.TabIndex = 11
            '
            'btnChangeSettings
            '
            Me.btnChangeSettings.AutoSize = True
            Me.btnChangeSettings.Location = New System.Drawing.Point(12, 321)
            Me.btnChangeSettings.Name = "btnChangeSettings"
            Me.btnChangeSettings.Size = New System.Drawing.Size(122, 27)
            Me.btnChangeSettings.TabIndex = 10
            Me.btnChangeSettings.Text = "Change Settings"
            Me.btnChangeSettings.UseVisualStyleBackColor = True
            '
            'comboSchemeColor
            '
            Me.comboSchemeColor.FormattingEnabled = True
            Me.comboSchemeColor.Location = New System.Drawing.Point(113, 271)
            Me.comboSchemeColor.Name = "comboSchemeColor"
            Me.comboSchemeColor.Size = New System.Drawing.Size(104, 24)
            Me.comboSchemeColor.TabIndex = 9
            '
            'label5
            '
            Me.label5.AutoSize = True
            Me.label5.Location = New System.Drawing.Point(9, 274)
            Me.label5.Name = "label5"
            Me.label5.Size = New System.Drawing.Size(98, 17)
            Me.label5.TabIndex = 8
            Me.label5.Text = "Color scheme:"
            '
            'label4
            '
            Me.label4.AutoSize = True
            Me.label4.Location = New System.Drawing.Point(9, 203)
            Me.label4.Name = "label4"
            Me.label4.Size = New System.Drawing.Size(186, 17)
            Me.label4.TabIndex = 6
            Me.label4.Text = "End scope of expected sale:"
            '
            'label3
            '
            Me.label3.AutoSize = True
            Me.label3.Location = New System.Drawing.Point(9, 146)
            Me.label3.Name = "label3"
            Me.label3.Size = New System.Drawing.Size(191, 17)
            Me.label3.TabIndex = 4
            Me.label3.Text = "Start scope of expected sale:"
            '
            'label2
            '
            Me.label2.AutoSize = True
            Me.label2.Location = New System.Drawing.Point(9, 89)
            Me.label2.Name = "label2"
            Me.label2.Size = New System.Drawing.Size(149, 17)
            Me.label2.TabIndex = 2
            Me.label2.Text = "End scope of the sale:"
            '
            'label1
            '
            Me.label1.AutoSize = True
            Me.label1.Location = New System.Drawing.Point(9, 32)
            Me.label1.Name = "label1"
            Me.label1.Size = New System.Drawing.Size(154, 17)
            Me.label1.TabIndex = 0
            Me.label1.Text = "Start scope of the sale:"
            '
            'BoxPlotSparkline
            '
            Me.AutoScaleDimensions = New System.Drawing.SizeF(8.0!, 16.0!)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.Name = "BoxPlotSparkline"
            Me.pnlMain.ResumeLayout(False)
            Me.pnlControls.ResumeLayout(False)
            CType(Me.fpSpread1, System.ComponentModel.ISupportInitialize).EndInit()
            Me.groupBox1.ResumeLayout(False)
            Me.groupBox1.PerformLayout()
            Me.ResumeLayout(False)

        End Sub
        Private WithEvents fpSpread1 As FarPoint.Win.Spread.FpSpread
        Private WithEvents groupBox1 As System.Windows.Forms.GroupBox
        Private WithEvents txtEndExpectedScope As System.Windows.Forms.TextBox
        Private WithEvents txtStartExpectedScope As System.Windows.Forms.TextBox
        Private WithEvents txtEndScope As System.Windows.Forms.TextBox
        Private WithEvents txtStartScope As System.Windows.Forms.TextBox
        Private WithEvents btnChangeSettings As System.Windows.Forms.Button
        Private WithEvents comboSchemeColor As System.Windows.Forms.ComboBox
        Private WithEvents label5 As System.Windows.Forms.Label
        Private WithEvents label4 As System.Windows.Forms.Label
        Private WithEvents label3 As System.Windows.Forms.Label
        Private WithEvents label2 As System.Windows.Forms.Label
        Private WithEvents label1 As System.Windows.Forms.Label
        Friend WithEvents fpSpread1_Sheet1 As FarPoint.Win.Spread.SheetView
    End Class
End Namespace
