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

Namespace Features
    Public Class SpreadSparkline
        Inherits BaseSampleControl

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

            InitSpread()
        End Sub

#Region "Private methods"
        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 IWorksheet = sheetView.AsWorksheet()
            worksheet.Name = name
            worksheet.Cells("A1").Value = "USA State-Wide Weather Averages"
            worksheet.Cells("A1").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 20
            })

            worksheet.Cells("A1").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.SetValue(1, 0, New Object(,) {
            {"State", "Temperature", "Precipitation", "Snow (days)", "Sun (clear days)", "Morning Humidity", "Afternoon Humidity"}})

            worksheet.Columns("A:G").ColumnWidth = 140
            worksheet.Rows("1").RowHeight = 40
            worksheet.Rows("4").RowHeight = 40

            worksheet.SetValue(2, 1, New Object(,) {
             {"Style: Stacked (1)", "Style: Spread (2)", "Style: Jitter (3)", "Style: Poles (4)", "Style: StackedDots (5)", "Style: Stripe (6)"}})

            worksheet.SetValue(4, 0, New Object(,) {
            {"Alabama", 62.8, 58.3, 0.8, 99, 0.84, 0.52},
            {"Alaska", 26.6, 22.5, 48.0, 61, 0.77, 0.64},
            {"Arizona", 60.3, 13.6, 0.3, 193, 0.53, 0.25},
            {"Arkansas", 60.5, 50.6, 3.8, 123, 0.85, 0.49},
            {"California", 59.4, 22.2, 0.0, 146, 0.76, 0.62},
            {"Colorado", 45.2, 15.9, 19.1, 136, 0.6, 0.35},
            {"Connecticut", 49.1, 50.3, 20.1, 82, 0.79, 0.52},
            {"Delaware", 55.3, 45.7, 11.8, 97, 0.79, 0.54},
            {"Florida", 70.8, 54.5, 0.0, 101, 0.87, 0.57},
            {"Georgia", 63.6, 50.7, 0.7, 112, 0.86, 0.5},
            {"Hawaii", 70.0, 63.7, 0.0, 90, 0.71, 0.56},
            {"Idaho", 44.4, 18.9, 19.4, 120, 0.68, 0.41},
            {"Illinois", 51.8, 39.2, 19.9, 95, 0.83, 0.58},
            {"Indiana", 51.7, 41.7, 23.5, 88, 0.83, 0.58},
            {"Iowa", 47.8, 34.0, 26.2, 105, 0.78, 0.56},
            {"Kansas", 54.3, 28.9, 10.0, 128, 0.8, 0.5},
            {"Kentucky", 55.6, 48.9, 11.8, 93, 0.79, 0.55},
            {"Louisiana", 66.4, 60.1, 0.1, 101, 0.87, 0.61},
            {"Maine", 41.0, 42.2, 27.8, 101, 0.82, 0.61},
            {"Maryland", 54.2, 44.5, 9.6, 105, 0.77, 0.52},
            {"Massachusetts", 47.9, 47.7, 22.4, 98, 0.75, 0.59},
            {"Michigan", 44.5, 32.8, 44.7, 71, 0.84, 0.61},
            {"Minnesota", 41.2, 27.3, 37.3, 95, 0.78, 0.55},
            {"Mississippi", 63.4, 59.0, 0.6, 111, 0.91, 0.54},
            {"Missouri", 54.5, 42.2, 11.5, 115, 0.82, 0.53},
            {"Montana", 42.7, 15.3, 32.9, 82, 0.71, 0.45},
            {"Nebraska", 48.8, 23.6, 18.3, 117, 0.82, 0.53},
            {"Nevada", 49.9, 9.5, 16.3, 158, 0.71, 0.32},
            {"New Hampshire", 43.8, 43.4, 29.8, 90, 0.84, 0.53},
            {"New Jersey", 52.7, 47.1, 8.7, 94, 0.83, 0.59},
            {"New Mexico", 53.4, 14.6, 9.5, 167, 0.6, 0.29},
            {"New York", 45.4, 41.8, 66.4, 63, 0.82, 0.61},
            {"North Carolina", 59.1, 50.3, 3.9, 109, 0.83, 0.52},
            {"North Dakota", 40.5, 17.8, 45.2, 93, 0.8, 0.51},
            {"Ohio", 50.8, 39.1, 30.4, 72, 0.8, 0.57},
            {"Oklahoma", 59.6, 36.5, 5.6, 139, 0.79, 0.48},
            {"Oregon", 48.4, 27.4, 2.2, 68, 0.85, 0.59},
            {"Pennsylvania", 48.8, 42.9, 19.4, 87, 0.77, 0.54},
            {"Rhode Island", 50.1, 47.9, 18.7, 98, 0.78, 0.57},
            {"South Carolina", 62.5, 49.8, 0.3, 115, 0.86, 0.49},
            {"South Dakota", 45.2, 20.1, 30.1, 104, 0.83, 0.53},
            {"Tennessee", 57.7, 54.2, 6.2, 102, 0.84, 0.53},
            {"Texas", 64.9, 28.9, 1.3, 135, 0.82, 0.49},
            {"Utah", 48.6, 12.2, 34.8, 125, 0.67, 0.43},
            {"Vermont", 42.9, 42.7, 54.4, 58, 0.77, 0.58},
            {"Virginia", 55.2, 44.3, 6.1, 100, 0.84, 0.52},
            {"Washington", 48.3, 38.4, 3.0, 58, 0.83, 0.62},
            {"West Virginia", 51.8, 45.2, 37.7, 60, 0.83, 0.59},
            {"Wisconsin", 43.2, 32.6, 38.7, 89, 0.84, 0.58},
            {"Wyoming", 42.0, 12.9, 36.7, 114, 0.63, 0.43}})

            For c As Integer = 1 To 6
                Dim columnChar As Char = ChrW((65 + c))
                Dim styleType As Integer = c
                worksheet.Cells($"{columnChar}4").Formula2 = "SPREADSPARKLINE(" & columnChar & "5:" & columnChar & "54, TRUE,,," & styleType & ",""#82BC00"")"
            Next

            worksheet.Cells("B5:B54").NumberFormat = "0 °F"
            worksheet.Cells("C5:C54").NumberFormat = "0 in"
            worksheet.Cells("F5:F54").NumberFormat = "0 %"
            worksheet.Cells("G5:G54").NumberFormat = "0 %"

            worksheet.InsertColumns(6, 1)
            worksheet.InsertColumns(5, 1)
            worksheet.InsertColumns(4, 1)
            worksheet.InsertColumns(3, 1)
            worksheet.InsertColumns(2, 1)
            worksheet.InsertColumns(1, 1)

            worksheet.Cells("A1:M1").Merge()
            sheetView.Cells("A2:M2").ForeColor = System.Drawing.Color.White
            sheetView.Cells("A2:M2").BackColor = ColorTranslator.FromHtml("#999999")
            worksheet.Cells("A2:M2").HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center
            worksheet.Cells("A2:M2").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center

            For c As Integer = 66 To 78 - 1 Step 2
                Dim columnChar As Char = Convert.ToChar(c)
                worksheet.Columns($"{columnChar}").ColumnWidth = 15
            Next
        End Sub

        Private Sub InitVerticalSparkline(ByVal sheetView As SheetView, ByVal name As String)
            Dim worksheet As IWorksheet = sheetView.AsWorksheet()
            worksheet.Name = name
            worksheet.Cells("A1").Value = "USA State-Wide Weather Averages"
            worksheet.Cells("A1").Font.ApplyFont(New GrapeCity.Spreadsheet.Font() With {
                .Name = "Arial",
                .Size = 20
            })

            worksheet.Cells("A1").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
            worksheet.Columns("A:F").ColumnWidth = 140
            worksheet.Rows("1").RowHeight = 40
            worksheet.Rows("4").RowHeight = 100

            worksheet.SetValue(1, 0, New Object(,) {
            {"Temperature", "Precipitation", "Snow (days)", "Sun (clear days)", "Morning Humidity", "Afternoon Humidity"},
            {"Style: Stacked (1)", "Style: Spread (2)", "Style: Jitter (3)", "Style: Poles (4)", "Style: StackedDots (5)", "Style: Stripe (6)"}})

            worksheet.Cells("A4").Formula2 = "SPREADSPARKLINE(Horizontal!C5:C54,TRUE,,,1,""#82BC00"",TRUE)"
            worksheet.Cells("B4").Formula2 = "SPREADSPARKLINE(Horizontal!E5:E54,TRUE,,,2,""#82BC00"",TRUE)"
            worksheet.Cells("C4").Formula2 = "SPREADSPARKLINE(Horizontal!G5:G54,TRUE,,,3,""#82BC00"",TRUE)"
            worksheet.Cells("D4").Formula2 = "SPREADSPARKLINE(Horizontal!I5:I54,TRUE,,,4,""#82BC00"",TRUE)"
            worksheet.Cells("E4").Formula2 = "SPREADSPARKLINE(Horizontal!K5:K54,TRUE,,,5,""#82BC00"",TRUE)"
            worksheet.Cells("F4").Formula2 = "SPREADSPARKLINE(Horizontal!M5:M54,TRUE,,,6,""#82BC00"",TRUE)"

            worksheet.InsertColumns(5, 1)
            worksheet.InsertColumns(4, 1)
            worksheet.InsertColumns(3, 1)
            worksheet.InsertColumns(2, 1)
            worksheet.InsertColumns(1, 1)

            For c As Integer = 66 To 76 - 1 Step 2
                Dim column As Char = Convert.ToChar(c)
                worksheet.Columns($"{column}").ColumnWidth = 15
            Next

            worksheet.Cells("A1:K1").Merge()
            sheetView.Cells("A2:K2").ForeColor = System.Drawing.Color.White
            sheetView.Cells("A2:K2").BackColor = ColorTranslator.FromHtml("#999999")
            worksheet.Cells("A2:K2").HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center
            worksheet.Cells("A2:K2").VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center
        End Sub
#End Region

    End Class
End Namespace


'
'SpreadSparkline.Designer.vb
'


Namespace Features
    Partial Class SpreadSparkline
        ''' <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(SpreadSparkline))
            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 = 2
            '
            'SpreadSparkline
            '
            Me.AutoScaleDimensions = New System.Drawing.SizeF(8.0!, 16.0!)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.Name = "SpreadSparkline"
            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
